Earlier on I was doing some CHECKDB-related work in our old SQL Server lab and I realized that the CHECKDB process was not really pushing the array hard. I then had one of those synapses that have seemingly become less frequent as I age — I remembered that there’s a way of making DBCC CHECKDB go much faster than it typically does, at the expense of running less in-depth checks. It’s called the PHYSICAL_ONLY option. And boy did it make a difference versus running the whole thing!
I also remembered that there are some trace flags documented here to improve performance of DBCC CHECKDB with PHYSICAL_ONLY, and Microsoft also indicates that you may see some gains on full CHECKDB if you use them (Narrator voice: I didn’t). So I tested them (both TFs enabled or disabled together, not individually – others may have done this elsewhere) and I noticed a small gain against this test ~1.7TB database.
Run times below for your delight.
|Scenario||Time to Complete|
|PHYSICAL_ONLY, No Trace Flags||54:03|
|PHYSICAL_ONLY, Trace Flags Enabled||51:24|
|Full, Trace Flags Enabled||1:27:29|
|Full, Trace Flags Disabled||1:27:24|
You can also note in the graph below the bandwidth utilization on the array. It’s a really old //m20 and the SQL Server box is connected to it using 2x 8Gbps Fibre Channel connections, so in your case YMMV when it comes to throughput.
Word of caution, though. This will make DBCC CHECKDB run faster, but it will also hit your array harder. Make sure you have the necessary performance capacity to do this without impacting concurrent workloads. A great way of doing this is using Pure1’s Workload Planner.
Until next time,