Storage is important part of the IT infrastructure. In this article, I like to walk through 5 performance testing tool for the storage.
- SQLIO
- CrystalDiskMark
- IOMeter
- HD_Speed
- AS SSD Benchmark
SQLIO is the Microsoft utility to test the storage system performance. Even it has name “SQL”, but it has nothing to do with SQL Server or any database at all.
Step1: Configure the parameter file
C:\testfile.dat is the location and the name for the test file.
10240 is the size of the test file in MB. It needs to be large than the buffer size to get the realistic result. (The SQLIO has the parameter –B to decide whether it to use the disk/software cache, however according the SQLIO document, many of the SCSI controller would cache it anyway and ignore the parameter. That make this parameter would only work on the regular disk. Therefore, it is still better to set the size larger than the buffer cache.) In this example, I use 10GB.
Step2: Create the Test SQLIO bat file
-k: Write or Read mode
-t:: Number of the threads
-S: Number of secs the test is going to run.
-f: The test is done by random or sequential access
-b: Number of bytes to run for the test. Since SQL server is usually access by Extent. One extent is 8 pages and 1 page is 8KB. 8x8 =64. So I use 64kb for testing.
Step3: Run the bat file and redirect the output to the file
Here is the sample output. After the test complete, remember to remove the C:\testfile.dat
SQLServerpedia has example which can create the table to hold the output data then import it in the sql server. You can find the article here.
Jonathan Kehayias also has the powershell script to parse the sqlio output. You can find them here.
CrystalDiskMark is the free software for testing the Disk performance. I have done test for my 2 disks.
Below is the result from the M4 on my Windows2012. CrystalDiskinfo is the other software from the same company that can display the detail information of the Disk.
Click the Edit and select Copy, it would copy the test result into the text format.
-----------------------------------------------------------------------
CrystalDiskMark 3.0.2 x64 (C) 2007-2012 hiyohiyo
Crystal Dew World : http://crystalmark.info/
-----------------------------------------------------------------------
* MB/s = 1,000,000 byte/s [SATA/300 = 300,000,000 byte/s]
Sequential Read : 495.078 MB/s
Sequential Write : 273.351 MB/s
Random Read 512KB : 395.656 MB/s
Random Write 512KB : 277.595 MB/s
Random Read 4KB (QD=1) : 18.936 MB/s [ 4623.0 IOPS]
Random Write 4KB (QD=1) : 49.471 MB/s [ 12078.0 IOPS]
Random Read 4KB (QD=32) : 190.265 MB/s [ 46451.3 IOPS]
Random Write 4KB (QD=32) : 230.684 MB/s [ 56319.3 IOPS]
Test : 1000 MB [C: 34.5% (82.2/238.1 GB)] (x5)
Date : 2012/11/14 21:51:33
OS : Windows Server 2012 [6.2 Build 9200] (x64)
Here is the 2nd test for my WD Hard drive. Obviously, the performance is much slower than SSD.
-----------------------------------------------------------------------
CrystalDiskMark 3.0.2 x64 (C) 2007-2012 hiyohiyo
Crystal Dew World : http://crystalmark.info/
-----------------------------------------------------------------------
* MB/s = 1,000,000 byte/s [SATA/300 = 300,000,000 byte/s]
Sequential Read : 94.110 MB/s
Sequential Write : 91.595 MB/s
Random Read 512KB : 27.549 MB/s
Random Write 512KB : 42.813 MB/s
Random Read 4KB (QD=1) : 0.303 MB/s [ 74.1 IOPS]
Random Write 4KB (QD=1) : 0.887 MB/s [ 216.6 IOPS]
Random Read 4KB (QD=32) : 0.494 MB/s [ 120.5 IOPS]
Random Write 4KB (QD=32) : 0.891 MB/s [ 217.5 IOPS]
Test : 1000 MB [C: 68.9% (264.0/382.9 GB)] (x5)
Date : 2012/11/14 22:00:17
OS : Windows 7 Home Premium Edition SP1 [6.1 Build 7601] (x64)
IOMeter is the open source tool to test the disk performance. The best of it is cross platform and has a lot of option to configure. Another advantage for IOmeter is that you can run the test against remote computer.
When start the IOMeter, it would also start the background process “Dynamo”, it is the workload generate for the disk IO.
Step 1: Select the Disk
We also need to select the Maximum disk size for testing the load. The size is calculate base on sector.
Normally, the disk sector is 512bytes. Ideally, you want to set up the size bigger than the disk buffer, otherwise, the IOMeter would use the disk buffer to perform the load testing. We wont get the accurate result for the disk performance.
Step2: Select the test
Step3: Change the update frequency, so we can see the test result
Step4: Configure the Test
Configure how long we like the test to run
Then hit the 
then it would ask you where we want to save the result.csv file. 
Here is the sample out of the CSV file. We can import into the Excel or database for further analysis.
IOMeter would create the temporary file for testing. if you find your disk usage has been consume after you close out the IOMeter, it maybe because it does not clean up the file. On my Win2012, the temporary file location is at C:\Users\Username\AppData\Local\VirtualStore\iobw.tst
HD Speed is another utilities that can test the disk performance. No installation require. There is an option to test the Read+Write, however, when I select the option it gives the warming that “would destroy the content of the drive ”, since I don’t have spare disk laying around, I hesitate to test that feature.
Below figure is the test result from my WD hard drive on Win7.
Very similar tool and usage like CrystalDiskMark. Even it design for SSD but it work for regular Hard Drive too but very slow. It also check the Disk sector and show whether the disk is alignment or not.
It can also test the compression.
Other resource
- Storage Area Networks (SANs) 101: http://sqlserverpedia.com/blog/podcasts/storage-area-networks-sans-101/
- SAN Performance Tuning with SQLIO http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_SQLIO
0 comments:
Post a Comment