Wednesday, September 23, 2020

SQL Server 2016++, checkdb/checktable, and trace flags 2549 & 2562


Trace flag 2549 is no longer required for checkdb or checktable in SQL Server 2016++.

Trace flag 2562 may still benefit checkdb on a system (depends on disk characteristics and tempdb) in SQL Server 2016++, but is not expected to benefit checktable due to the already-limited scope of checktable activity.


Trace flags 2549 and 2562 were introduced in kb2634571, linked below.

2549 - by default checkdb (and checktable) before SQL Server 2016 planned its concurrent disk IO per Windows drive letter, in order to achieve a reasonable pace without overloading disk subsystems.

The problem came in for systems using mount points.  One system might put SQL Server data files on 8 Windows volumes as eight separate drive letters. Another system could put the data files on 8 Windows volumes under a single drive letter, using mount points.

The system with eight separate drive letters would experience much higher concurrent disk traffic from checkdb/checktable than the system with a single drive letter.  And if that system could handle the higher disk IO pace, checkdb/checktable would perform better on that system.

Trace flag 2549 was introduced to even the playing field - increasing the pace of checkdb/checktable disk io for systems using mountpoints.  This was done by planning the disk IO per database file, rather than per drive letter. 

In SQL Server 2016, there were changes to components used by checkdb/checktable explained in the second link below that make trace flag 2549 no longer needed. The second link below describes MultiObjectScanner vs CheckScanner (introduced in SQL Server 2016).

2562 - by default, checkdb groups the heaps and b-tree indexes into batches of up to 512 for its activity.

With trace flag 2562 enabled, checkdb puts all heaps and b-tree indexes into a single batch.

This trace flag often speeds the performance of checkdb with physical_only on systems using hard drive storage (by minimizing disk head traffic and lowering average disk read service times).

However tempdb is a serious consideration for this trace flag when checkdb is running without physical_only (when logical checks are run).

Tempdb use for logical checks increases until the batch of heaps and b-tree indexes is complete.  Watching tempdb use by checkdb on a system with many batches, tempdb use can be seen to increase as each batch runs, then drop with the start of another batch.

If all heaps and b-tree indexes are in a single batch due to trace flag 2562, tempdb use increases until checkdb is complete.

Another consideration with trace flag 2562: a single nonpartitioned table will not require more than one batch, it will already fit in a single batch.

So trace flag 2562 is not expected to effect the operation of checktable.


Improvements for the DBCC CHECKDB command may result in faster performance when you use the PHYSICAL_ONLY option


SQL 2016 - It Just Runs Faster: DBCC Scales 7x Better

No comments:

Post a Comment