4

We have a ~10TB database with dozens of data files on SQL Server 2017 Enterprise CU21. We're seeing slower CHECKDB performance when all of the files are on a single volume as opposed to multiple volumes. (In both cases, the same high-end (Pure) storage is involved, same VM hosts, same storage network - just different numbers of volumes.)

Is there a documented advantage to spreading data files across multiple volumes, either for DBCC CHECKDB specifically, or for SQL Server generally?

The trace flag 2549 described in KB 2634571 tells CHECKDB to treat each database file as if it's on a unique drive. But is there any other official documentation outlining how SQL Server (or CHECKDB specifically) treats IO differently when there are multiple volumes involved?

5
  • Multiple volumes as multiple virtual disks? As these will be threated as different physical drives from the guest OS perspective i guess and depending on the storage i can imagine queue/buffer/other limitations (call it IO scheduling) for virtual disks and LUNs (and not just for the entire physical storage). – Peter Kiss Oct 30 '20 at 21:00
  • @PeterKiss right, that's the question, and I'm looking for documented proof. Thanks! – Brent Ozar Oct 31 '20 at 19:45
  • Well if I'm right around the virtual disks (all of them mounted as different devices) and we take into account the parallel corruption checking in EE then i guess it is proved and even documented as the MAXDOP setting is documented. More evidence can be gathered by running the check with different MAXDOP setting on different virtual disk setups while monitoring the activity on the OS and storage level. I guess without the MAXDOP parameter this number (how parallel it is) will be <= server level MAXDOP <= physical devices. – Peter Kiss Nov 3 '20 at 9:22
  • @PeterKiss if you have an answer, please post it as an answer rather than comments. Thanks! – Brent Ozar Nov 3 '20 at 11:50
  • No, i don't have an answer but I have an idea how things might be in the real. This is why I suggest some detailed tracing over the process & setup. Also we cannot really rely on documentation nowadays. – Peter Kiss Nov 11 '20 at 21:22
1

Is this what you're looking for?

https://techcommunity.microsoft.com/t5/sql-server-support/a-faster-checkdb-8211-part-ii/ba-p/316882

At the end of this blog Bob Ward says:

There are also some factors that can affect whether these changes actually help make your execution of CHECKDB faster: ... Spreading files across disks – As with the general performance of server queries, if we are spreading out our reads across separate physical disks for files, we can achieve better I/O performance.

2
  • Kinda, but that's physical disks. Here I'm just asking about different numbers of logical volumes. – Brent Ozar Oct 9 '20 at 1:16
  • Hmm. How interesting that it would be faster that way. – MSSQLServerDBA Oct 9 '20 at 12:13
0

Maybe this one is relevant?

KB2974455

If I'm understanding it correctly, the article describes how the files (named streams prior to 2014) for the CHECKDB hidden internal database snapshot are created using the paths for the database data file(s). If the database has multiple data files spread over multiple volumes, the snapshot files (and hence their IO) are also distributed over the same volumes.

1
  • Not really - it isn't clear that IO throughput is higher on multiple volumes by that alone. Thanks though! – Brent Ozar Oct 9 '20 at 1:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.