What is Trace Flag 610

What is Trace Flag 610? When should we use it?

Trace Flag 610 (TF610) can be used to have minimal logging into a b tree, i.e. clustered table or an index on a heap, that already has data. TF610 can be used to get minimal logging in a non-empty B-Tree. When you insert a large amount of data, you don't want to create a lot of transaction log. So, initially the idea was to automatically do this in the engine, but because of bunch of issues this was put under a TraceFlag.


There are a few things one shoud be aware of:

1. When a Transaction COMMITs, it writes all pages that were minimally logged to disk. But there is possibilty that not all the pages can be written in time in case of a slow I/O subsystem. This can make the operation slower than full logging because for full logging, SQL only needs to write the commit log record and it's done. 
2. There is an issue in SQL 2008 where the transaction log grows very big due to log reservation - even though the log is never used. This is due to how rollback works for minimally logged pages. However, this bug was fixed in SQL 2008 R2.
3. If you'hv a large buffer-pool and the I/O subsystem cannot keep up, the commit could take very long time.
4. Don't make transactions too big. Even though the features is build to be used when inserting a large number of rows, it does not work very well if you make the transactions too big. Inserting data around 1TB in a single transaction may caused some issues.
5. The feature is NOT designed to make inserts go faster. It is mainly written to reduce the size of the transaction log.

I would recommendation to test this feature in a test environment - very similar I/O characteristics than the production system - and see if this really helps.

I would only use it if I have fast enough I/O to keep up with creation of the minimally logged pages.