Major Discrepancy Between SQLIO IOPS/Throughput Stats and DBCC CHECKDB Operations

I'm conducting time trials on a new SSD array running both SQLIO tests and a real-world workload of DB restores and DBCC CHECKDB calls. I'm seeing a major discrepancy between the IOPS and throughput generated with my SQLIO batches and what I'm observing with the workload, with the workload only requesting a fraction of what I was able to observe with SQLIO, usually in the 5,000 IOPS range and generating no more than 400 MB/s throughput.

Is there an inherent limitation as to how many resources DBCC CHECKDB will consume event if the hardware has more than sufficient capacity to handle the load? What settings can I experiment with to expand DBCC CHECKDBs usage of CPU and disk resources?

Here are the specifics...

From systeminfo


OS Name: Microsoft Windows Server 2012 R2 Standard
OS Version: 6.3.9600 N/A Build 9600
System Manufacturer: HP
System Model: ProLiant DL580 G7
System Type: x64-based PC
Processor(s): 4 Processor(s) Installed.
[01]: Intel64 Family 6 Model 46 Stepping 6 GenuineIntel ~1042 Mhz
Total Physical Memory: 131,062 MB
Network Card(s): 4 NIC(s) Installed.
[01]: HP NC375i Integrated Quad Port Multifunction Gigabit Server Adapter


SQL Server Info

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 (Build 9200: )

• User DB volume on 3 TB SSD LUN (Tlogs on same volume but only because its a DBCC box)

• System DBs (except tempdb) on C: volume using RAID 1 on 15k spindles

• TempDB data files on 1 TB SSD LUN (32 files totaling 80 GB)

• TempDB log file on 100 GB SSD LUN (one 10 GB file)

Test script using SQLIO where the param file is directed to 40 GB test file on a 3 TB XtremeIO Flash Array LUN

`
sqlio -kW -t8 -s120 -o8 -fsequential -b64 -BH -LS -Fparam.txt
sqlio -kR -t8 -s120 -o8 -fsequential -b64 -BH -LS -Fparam.txt
sqlio -kW -t8 -

DBCC CHECKDB isn't a good storage test. It does logical tests too, not just reads from disk - for example, it compares data between multiple indexes on the same table to make sure they all have the same values. These checks consume CPU cycles.

If you want a better pure storage test, consider setting an artificially low buffer pool number and running multiple simultaneous SELECT COUNT(*) queries across multiple large tables with no nonclustered indexes.

StackExchange Database Administrators Q#63596, answer score: 3