6

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

MattyZDBA
  • 1,955
  • 3
  • 20
  • 32

2 Answers2

3

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.

Brent Ozar
  • 43,325
  • 51
  • 233
  • 390
0

Yes, there are cases where the wait will be mostly 100% OLEDB and the hardware will seem idle. In my case, I tried to run a DBCC CHECKTABLE on a 26 GB table with a spatial index. It would run and run and run.... I moved it to my workstation (6 core Zeon, 16 GB, with 2 SDDs) with hopes of getting it to finish. It runs faster, but runs and runs.... I tried using SQL 2012, SP2, traces flags, etc. A DBCC on a normal table would finish about 7 times faster than in production and really worked my disks, so I know my hardware helps. The DBCC on the table with spatial index ran over a week before failing. (I didn't limit the memory and starved the OS. I had VMs and other stuff going too.) While it was running, my machine seem almost idle. I could not identify the bottle neck. It was not CPU or disk. I'm thinking to submit a bug report for this.

Perhaps you can use DBCC CHECKTABLE to see if there are a select group of tables that also have this behavior.

MSDN link for detail on my DBCC issue

Randy in Marin
  • 131
  • 2