Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Minimal logging operation with Traceflag 610

Posted by Simon Cho on 12/05/2011

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


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: