Trace flag 610 and High performance data loading

06 June,2016 by Jack Vamvas

The Data Performance data loading guide is one of the most comprehensive documents available when it comes to techniques for data loading performance.   One of the useful techniques is using trace flag 610.

Trace flag 610 sends an instruction to the server to minimally log INSERT and MERGE on new page allocations. If rows are being added on existing page allocations fully logged will persist.

This impacts minimally logged inserts into indexed tables.

 

-- enable trace flag 610
DBCC TRACEON (610);
-- disable trace flag 610
DBCC TRACEOFF(610); 
-- minimally logged               
MERGE INTO X1 USING Y1 ON       

Keep in mind, the trace flag 610 turned on is SQL Server instance wide. All connections to the SQL Server instance will be impacted.

Given the nature of the trace and the criteria required to be met :

  1. New page allocations
  2. Indexed tables

This is not a “catch-all” performance solution. But if proper analysis is completed and solid testing , there are some potential gains. I’ve used it with good results in certain large data loads.

 

Read More on SQL INSERT Performance

Data Loading Performance Guide (SQL Server DBA)

Bulk Insert CSV into a SQL Server table - SQL Server DBA

SQL Server architecting write-intensive databases - SQL Server DBA


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Trace flag 610 and High performance data loading


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer