Trace Flag 610
SQL Server 2008 introduces trace flag 610, which controls minimally logged inserts into indexed tables. The trace flag can be turned on by using one of the following methods;
- Adding to the SQL Server startup parameters.
- Running
- This enables the trace flag for a specific session. This is useful if you want to enable 610 for only a subset of load scenarios on the instance, and it applies only to the Transact-SQL connection that issues it.
- Use turns on the trace flag for all connections to the server until it is turned off or until the next server restart.
- For more information about using DBCC to enable trace flags, see (http://msdn.microsoft.com/en-us/library/ms187329.aspx) in SQL Server Books Online.
Before you start using this trace flag, be aware of the limitation described in the previous section.
Not every row inserted in a cluster index with trace flag 610 is minimally logged. When the bulk load operation causes a new page to be allocated, all of the rows sequentially filling that new page are minimally logged. Rows inserted into pages that are allocated before the bulk load operation occurs are still fully logged, as are rows that are moved as a result of page splits during the load. This means that for some tables, you may still get some fully logged inserts.
If trace flag 610 causes minimal logging to occur, you should generally see a performance improvement. But as always with trace flags, make sure you test for your specific environment and workload.
I/O Impact of Minimal Logging Under Trace Flag 610
When you commit a bulk load transaction that was minimally logged, all of the loaded pages must be flushed to disk before the commit completes. Any flushed pages not caught by an earlier checkpoint operation can create a great deal of random I/O. Contrast this with a fully logged operation, which creates sequential I/O on the log writes instead and does not require loaded pages to be flushed to disk at commit time.
If your load scenario is small insert operations on btrees that do not cross checkpoint boundaries, and you have a slow I/O system, using minimal logging can actually slow down insert speeds.
Summarizing Minimal Logging Conditions
To assist you in understanding which bulk load operations will be minimally logged and which will not, the following table lists the possible combinations.
| Table Indexes |
Rows in table |
Hints |
Without TF 610 |
With TF 610 |
Concurrent possible |
| Heap |
Any |
TABLOCK |
Minimal |
Minimal |
Yes |
| Heap |
Any |
None |
Full |
Full |
Yes |
| Heap + Index |
Any |
TABLOCK |
Full |
Depends (3) |
No |
| Cluster |
Empty |
TABLOCK, ORDER (1) |
Minimal |
Minimal |
No |
| Cluster |
Empty |
None |
Full |
Minimal |
Yes (2) |
| Cluster |
Any |
None |
Full |
Minimal |
Yes (2) |
| Cluster |
Any |
TABLOCK |
Full |
Minimal |
No |
| Cluster + Index |
Any |
None |
Full |
Depends (3) |
Yes (2) |
| Cluster + Index |
Any |
TABLOCK |
Full |
Depends (3) |
No |
Table 1: Summary of minimal logging conditions
(1) If you are using the INSERT … SELECT method, the ORDER hint does not have to be specified, but the rows must be in the same order as the clustered index. If using BULK INSERT the order hint must be used.
(2) Concurrent loads only possible under certain conditions. See “Bulk Loading with the Indexes in Place”. Also, only rows written to newly allocated pages are minimally logged.
(3) Depending on the plan chosen by the optimizer, the nonclustered index on the table may either be fully- or minimally logged.
Summarizing Insert Scenarios
The following flow chart helps you solve the different bulk load scenarios.
Figure 8: Bulk load method decision flowchart
FullDocument : http://msdn.microsoft.com/en-us/library/dd425070(v=sql.100).aspx
Simon's SQL 