DBCC CHECKDB scalability and performance benchmarking on SSDs

Back in February I ran a bunch of performance tests of DBCC CHECKDB on SSDs, to evaluate the effects of degree-of-parallelism (DOP) and various options and traceflags, and now I’m finally getting around to presenting the results. Make sure to also read the recent post where I talk about the detrimental effect of computed-column indexes on DBCC CHECKDB performance.

[Edit: I’ve heard anecdotal evidence that reducing DOP for DBCC CHECKDB is even more important on 2012 and later versions. YMMV.]

The two variables I altered during my tests were:

  • DOP – 32, 16, 8, 4, 2, 1
  • Configuration – full DBCC CHECKDB, using WITH PHYSICAL_ONLY (PO), using PO and TF2549, using PO and TF2562, using PO and both trace flags

Trace flags 2549 and 2562 are described in KB 2634571, and can be summarized as:

  • 2549: treat each database file as if it’s on a separate physical drive for the purposes of driving readahead
  • 2562: perform the entire set of checks in a single ‘batch’ instead of multiple batches, and increase the efficient of per-thread calls to get a new list of pages to read

These two trace flags really only apply to DBCC CHECKDB when the WITH_PHYSICAL_ONLY option is used.

My test system is as follows:

  • DELL R720 with two 8-core E5-2670 CPUs and hyper-threading enabled
  • 64GB of memory
  • The test database is AdventureWorks that Jonathan expanded to 500GB for me using his cool scripts. The database is split over 8 data files stored on two 320GB Fusion-io drives, with tempdb and its log placed on two more 320GB Fusion-io drives. I set things up this way to remove I/O waits from the test.
  • There was no additional load running concurrently with DBCC CHECKDB.

Complete Results

The complete results are shown below:

CHECKDB-DOP1

There are some very interesting results from this graph, for the test I’m doing using SSDs:

  • For a full DBCC CHECKDB, there is a 70-80% performance gain from DOP 1 to DOP 2, from DOP 2 to DOP 4, and from DOP 4 to DOP 8.
  • For a full DBCC CHECKDB run, there is really no scalability gain in performance for a DOP greater than 8, and in fact a slight performance degradation for higher DOPs.
  • As you can see for the results of a full DBCC CHECKDB with DOP 8 and higher, DBCC CHECKDB on that server without I/O waits can process 0.5GB of the database per second. That’s pretty cool and is down to the fast SSDs (there were no PAGEIOLATCH_SH waits) and fast processors. Back in SQL Server 2000, our performance benchmark was about 1GB per minute. See here for some example SQL Server 2000 benchmark times that show around 3-5GB/minute, but not on SSDs.
  • The main boost in performance when running on SSDs comes from using the WITH PHYSICAL_ONLY option. This is, of course, to be expected as it cuts out all of the logical checks that make up the bulk of the CPU usage of DBCC CHECKDB.

For the scalability result running a full DBCC CHECKDB, some latch contention for structures inside DBCC CHECKDB starts to show up at DOP 8, but not enough to give zero scalability at higher DOPs. This comes from a much higher time spent waiting for CXPACKET waits, roughly doubling from DOP 8 to DOP 16, and again from DOP 16 to DOP 32. I blame the query processor for that :-) In all seriousness, I didn’t expect to see a scalability gain with DOP higher than 8, based on what I’ve seen in the past.

PHYSICAL_ONLY Results

The results without DOP 1 and DOP 2 are shown below, which allows us to focus on the PHYSICAL_ONLY results:

CHECKDB-DOP2

Again, for the test I’m doing using SSDs, there are some interesting results:

  • DOP 4 seems to give the best performance overall, apart from the use of both trace flags with DOP 8.
  • Higher DOPs get progressively worse performance. Analysis of wait and latch statistics show that this is caused by increasing amounts of latch contention at higher DOPs.
  • The two trace flags don’t have a pronounced effect on performance in my situation.

Summary

When running with a database on SSDs and very fast processors, full DBCC CHECKDB performance scales to DOP 8, and the main performance gain comes from using the WITH PHYSICAL_ONLY option. The two trace flags introduced in late 2011 don’t have a pronounced effect on PHYSICAL_ONLY performance. This is all to be expected, as the SSDs really remove the I/O component of DBCC CHECKDB performance and scalability from consideration.

Although these tests are not conclusive until I’ve run similar tests on a slower I/O subsystem, I’m tempted to recommend limiting DBCC CHECKDB‘s DOP to 8 if you can (maybe using Resource Governor) to limit its resource drain on your system.

I obviously also have to recommend using SSDs to vastly reduce or effectively eliminate (in my case) I/O waits.

During April and May I’ll perform the same set of tests using 15k SCSI drives and also 7.2k SATA drives to see if and how the results differ.

Hope you found this interesting!

10 thoughts on “DBCC CHECKDB scalability and performance benchmarking on SSDs

  1. I would be interested in seeing the suite run with Hyperthreading disabled too. See if increased context switching and various cache misses affect throughput or not.

  2. Fascinating. I was surprised by the DOP 8 thing, but it’s an interesting coincidence when taken with other advice to limit MAXDOP to 8 for physical cores greater than 8.

  3. Hi Paul,

    In this case database is split over 8 data files, does the best DOP = 8 relate to file number? Is DOP = 8 still the best value for 1, 4, 16 files?

    Cheers,
    Shiyang

  4. I’m also wondering if the best DOP for this test will vary directly with the number of files. Eight files, 8 DOP is an interesting coincidence. It will be good to see how that unfolds.

    Since you’ve provided a solid description of the test bed, I’m sure some others can replicate and then deviate to get those answers.

  5. The sweet point of 8 DOP, suspiciously correlates to NUMA, if you assume (only testing would prove) that hyperthreading is not ideal in this situation. Be interesting to run the same test with 6 core processors and see if 6 is the sweet spot for that system, sadly I don’t have the resources to do that.

  6. Hi Paul,

    Here you have only 2 drives, so this trace flag 2549 might be not be so effective…..

    As per BOL
    Trace flag 2549==> If the underlying disks are actually unique when the drive letters or not, the DBCC CHECKDB command would treat these as one disk. When this trace flag is enabled, each database file is assumed to be on a unique disk drive. Do not use this trace flag unless you know that each file is based on a unique physical disk.

    Regards,
    Maneesh Arora

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.