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:
- 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.
- 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.
- If you'hv a large buffer-pool and the I/O subsystem cannot keep up, the commit could take very long time.
- 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.
- 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.