Sql-server – Major Discrepancy Between SQLIO IOPS/Throughput Stats and DBCC CHECKDB Operations

dbccperformancesql serverssdtempdb

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 -s120 -o8 -frandom -b8 -BH -LS -Fparam.txt
sqlio -kR -t8 -s120 -o8 -frandom -b8 -BH -LS -Fparam.txt

The specs for the XtremeIO array

XtremIO - 1 Brick
Version: 2.2.3 build 25
Build id: 9585409:HEAD-release-2_2

The results for the SQLIO run

C:\SQLIO>sqlio -kW -t8 -s120 -o8 -fsequential -b64 -BH -LS -Fparam.txt
sqlio v1.5.SG
using system counter for latency timings, 2211143 counts per second
parameter file used: param.txt
file L:\testfile.dat with 8 threads (0-7) using mask 0x0 (0)
8 threads writing for 120 secs to file L:\testfile.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using specified size: 40000 MB for file: L:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 23118.54
MBs/sec: 1444.90
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 2
Max_Latency(ms): 9
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 5 7 46 41 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

C:\SQLIO>sqlio -kR -t8 -s120 -o8 -fsequential -b64 -BH -LS -Fparam.txt
sqlio v1.5.SG
using system counter for latency timings, 2211143 counts per second
parameter file used: param.txt
file L:\testfile.dat with 8 threads (0-7) using mask 0x0 (0)
8 threads reading for 120 secs from file L:\testfile.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using specified size: 40000 MB for file: L:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 25160.07
MBs/sec: 1572.50
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 2
Max_Latency(ms): 8
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 24 33 12 7 7 9 6 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

C:\SQLIO>sqlio -kW -t8 -s120 -o8 -frandom -b8 -BH -LS -Fparam.txt
sqlio v1.5.SG
using system counter for latency timings, 2211143 counts per second
parameter file used: param.txt
file L:\testfile.dat with 8 threads (0-7) using mask 0x0 (0)
8 threads writing for 120 secs to file L:\testfile.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using specified size: 40000 MB for file: L:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 153634.35
MBs/sec: 1200.26
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 0
Max_Latency(ms): 1
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 100 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

C:\SQLIO>sqlio -kR -t8 -s120 -o8 -frandom -b8 -BH -LS -Fparam.txt
sqlio v1.5.SG
using system counter for latency timings, 2211143 counts per second
parameter file used: param.txt
file L:\testfile.dat with 8 threads (0-7) using mask 0x0 (0)
8 threads reading for 120 secs from file L:\testfile.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using specified size: 40000 MB for file: L:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 181107.89
MBs/sec: 1414.90
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 0
Max_Latency(ms): 5
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 100 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Best Answer

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.

Related Question