Trace flag 610 and High performance data loading

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