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

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. ...

Packaging MS Office 2013 Professional Plus with Thinapp 4.7.3

I can't find the link to package MS Office 2013 but only MS Office 2010 as here . I'm packaging MS Office 2013 the same way MS office 2010 did and it works. Some notes to be taken:- Always run the thinapp with local administrator privilege. Make sure you modify the config.xml (located on proplusr.ww folder), save it and run the setup in command prompt, e.g.. setup.exe /config c:\tmp\config.xml I will choose to run thinapp with VMware Fusion/Workstation with SSD and snapshot. This will allow you to easily revert the snapshot anytime you like. Since the whole Office will be packaged, always select option "Run all from my computer" Always remember to run the application a couple times after installation Always remember to activate the license before proceeding with post-scan I've not installed .net framework as suggested by some blog and the Office 2013 is working. I will run the application and testing to see how it goes. So far, I've thinapp MS o...

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 ...

JKuan Blog!

Search This Blog