TL;dr
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