23 August,2011 by Jack Vamvas
SQL Server Performance testing a new disk subsystem from a SQL Server perspective is one of the fun aspects of DBA work. Firstly, it’s a great opportunity to influence the optimizations and give valuable feedback to the storage team. Secondly, it’s difficult to simulate the exact workloads and IO patterns. This is a good challenge to review and summarise database servers you’re managing.
SQLIO is a useful tool from Microsoft . Prior to doing SQL Server testing , I commit to generating some figures from SQLIO and working wit the storage guy. We focus on configurations to optimize various activity .
I prefer to set an IO optimal target – and see if SQLIO can verify the expectation. Some guidelines for setting optimal targets :
For tempdb aim for highest io/sec possible.
For data file , aim for high 8KB and 64KB Random Read and Random Write along with highest possible IO\sec
For log files aim for highest possible MB\sec throughput
For backup files , aim for high MB/sec Sequentail Read and Sequential Writes
The general steps I use are as follows
Install SQLIO on C:\
The default test file is normally not big enough for SQL Server testing – normally I create a 25 GB file.This is smaller then the cache – but is closer to some real performance testing.
Change the file size by adjusting the values in the SQLIO installation file. The default installation is on : C:\Program Files\SQLIO
The file to edit is “param.txt” . You’ll see a line entry :
c:\testfile.dat 2 0x0 100
Change it to the drive and size you prefer . The sizes are in MB. I’m changing it to test the E:\ with a 25 GB (25600 MB) file:
e:\perftest.dat 2 0x0 25600
For Sequential activity I focus on these IO sizes : 8KB, 64KB, 12KB, 256KB and 1024 KB
For random activity I focus on these IO sizes : 8KB
Create a Windows batch file called “perftest.bat” in the SQLIO directory. Place the code into the “perftest.bat”
The advice from Microsoft is to test each IO path individually.
In the example below I’m testing read and random , 8kb and 64 kb IO size for the E:\.
--execute the “perftest.bat” with one input parameter i.e an output file name
set outputfile=%1if "%1"=="" goto syntaxsqlio -kW -t8 -s120 -o8 -frandom -b8 -BH -LS E:\perftest.dat >> %outputfile%sqlio -kR -t8 -s120 -o8 -frandom -b8 -BH -LS E:\perftest.dat >> %outputfile%sqlio -kW -t8 -s120 -o8 -fsequential -b64 -BH -LS E:\perftest.dat >> %outputfile%sqlio -kR -t8 -s120 -o8 -fsequential -b64 -BH -LS E:\perftest.dat >> %outputfile%sqlio -kR -t8 -s120 -o8 -fsequential -b128 -BH -LS E:\perftest.dat >> %outputfile%sqlio -kR -t8 -s120 -o8 -fsequential -b256 -BH -LS E:\perftest.dat >> %outputfile%sqlio -kR -t8 -s120 -o8 -fsequential -b1024 -BH -LS E:\perftest.dat >> %outputfile%:syntaxecho IO performance test - include an outputfile name :end
-kW testing writes
-kR testing reads
-t8 testing with 8 threads
-o8 8 outstanding requests at once
-s240 length of test in seconds
-frandom testing random activity e.g data activity
-fsequential testing sequential activity e.g Lg files
From the command prompt type (check your permissions):
sqlio -kW –s20 -fsequential -t8 -o8 -b8 -LS -Fparam.txt timeout /T 20
The purpose of this short test , defined by the /T switch as 20 seconds, is to create the data file, as defined in the “param.txt” . It will take a few minutes, be patient.
Once the command is finished , you are ready to execute: perftest.bat <name_of_an_output_file>
An output example
sqlio v1.5.SGusing system counter for latency timings, 2001300000 counts per second8 threads reading for 240 secs from file E:\perftest.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 current size: 25600 MB for file: E:\perftest.datinitialization doneCUMULATIVE DATA:throughput metrics:IOs/sec: 1859.65MBs/sec: 14.52latency metrics:Min_Latency(ms): 1Avg_Latency(ms): 33Max_Latency(ms): 1725histogram: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+%: 0 0 0 0 0 0 0 0 0 1 1 2 3 3 3 3 3 3 3 3 3 3 3 3 59
Focus on : IOs/sec , MBs/sec , histogram
For sequential activity focus on MB/s (throughput) . for Random activity focus on IO/sec . This equates to OLTP v DSS.
This output example was for Random writes with 8KB IO Size .
At this point , I’ll make an assessment for performance. I was looking for better performance on this disk response – in the region of < 2 ms.
I’ll post tomorrow on continuing the analysis and what other steps to take to when analysing data from output.