Skip to main content

SQL Server IO - How well is the Disk I/O performing?

Microsoft has released a free tool - SQLIO (Download at http://www.microsoft.com/en-us/download/details.aspx?id=20163)

Intel initially has developed IOMeter which is now open source can be download at http://www.iometer.org/doc/downloads.html

Both tools are pretty easy to use. Of course, SQLIO provide more advance testing scenario by choosing the right parameter, such as number to threads, file size, read/write, random/sequential, block size, buffer cache, etc...

I've seen 2 good sites that explain pretty well with SQLIO command... pls see...
http://www.mssqltips.com/sqlservertip/2127/benchmarking-sql-server-io-with-sqlio/
http://www.sqlteam.com/article/benchmarking-disk-io-performance-size-matters

However, I still no no idea what's the actual number we should be look at...

I created the batch file as shown below.

@echo off
cls
:start
echo Testing IO...
SQLIO.exe -kW -s60 -frandom -o8 -b8 -LS -Fparam.txt timeout /T 60
SQLIO.exe -kW -s60 -frandom -o8 -b256 -LS -Fparam.txt timeout /T 60
SQLIO.exe -kR -s60 -frandom -o8 -b8 -LS -Fparam.txt timeout /T 60
SQLIO.exe -kR -s60 -frandom -o8 -b256 -LS -Fparam.txt timeout /T 60
timeout 1
goto start

==END==

param.txt file content -> change the last number to indicate file size

c:\testfile.dat 2 0x0 8000
#d:\testfile.dat 2 0x0 100

Result


read read read read write write write write
8k MB/s 256k MB/s 8k MB/s 256k MB/s
1GB file 2659 20 701 175 918 7.17 337 84
Chg to 8GB file 1410 11 527 131 931 7 182 45


When you indicate different file size to test, the IOPS and throughput come back differently. However, different read/write with different block size give different number as well. If we refer to the IOPS provided by disk (according to Wikipedia.org), SAS 15k RPM should provide 175-210 IOPS. I'm using 4 SAS disk with RAID 5, and running this test with just one VM.

Now, 4x200 IOPS, 800 IOPS... Where does this 800 IOPS fit in?




Comments

Post a Comment

Popular posts from this blog

VMware VRNI 3.5 NTP is working but not healthy

We have experienced this problem where some of the major services not started. You will not be able to connect to the vRNI via web -> https://<ip_address_of_vrni_platform>>. This probably caused by NTP insync. You need to manually start these services. Login with Consoleuser / and default password if not changed before via venter web console. Run services restart and wait for couple minutes (it took me 5-6 mins). Types of Users  User name  Password  Admin UI  admin@local Set this password in the Activate License window during installation. SSH User  support ark1nc0113ct0r CLI User  consoleuser ark1nc0ns0l3 Note It is recommended that the users change the default passwords immediately after the deployment.  Procedure 1 Navigate to  https://<vRealize Network Insight Platform IP address> .  2 Log in to the product UI with the corresponding user name and password. Troubleshoot NTP - https://kb.vmware.com
Post a Comment
Read more

vSphere Replication - checking the replication status

Enable SSH on ESXi host (you can do this using vCenter) Download putty or any SSH client and log in with root access vim-cmd vmsvc/getallvms ~ # vim-cmd hbrsvc Commands available under hbrsvc/: vmreplica.abort vmreplica.pause vmreplica.create vmreplica.queryReplicationState vmreplica.disable vmreplica.reconfig vmreplica.diskDisable vmreplica.resume vmreplica.diskEnable vmreplica.startOfflineInstance vmreplica.enable vmreplica.stopOfflineInstance vmreplica.getConfig vmreplica.sync vmreplica.getState  See more at: http://www.virtuallyghetto.com/2011/07/hbr-host-based-replication-cli-for-srm.html#sthash.mpqbyGmw.dpuf http://www.virtuallyghetto.com/2011/07/hbr-host-based-replication-cli-for-srm.html
Post a Comment
Read more

vRA 7 Software Component - application scripts

Adobe - CMD /C START C:\TMP\AcroRdrDC1501020056_en_US.exe /sAll IIS - Powershell Import-Module ServerManager Add-WindowsFeature Web-Server import - module servermanager add - windowsfeature web - server - includeallsubfeature For Windows 2012, install-windowsfeature web-server instead. MS SQL - C:\TMP\SQL2012\setup.exe /q /ACTION=Install /IAcceptSQLServerLicenseTerms /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /UpdateEnabled=0 VI Client CMD /C START C:\TMP\VMware-viclient-all-6.0.0-3249451.exe /S /v/qn To install vRA-agent - keeping the following URL for reference  http://www.virtualizationteam.com/cloud/vrealize-automation-7-bootstrap-installation.html http://www.virtualizationteam.com/cloud/vcac-guest-agent-installation.html Cert problem http://vmwarevcloudvirtualization.blogspot.co.id/2014/04/vcac-agent-ssl-issue.html. (Hopefully we don't need to do this!) ManagerServiceHost = IaaS -> Windows, not the vRA. Do not need to run winservice -i -h... u
2 comments
Read more