SQLIOStress and SQL Server 2005

Hi,

I came across this tool recently and was wondering whether MS recommends its use to stress test I/O operations for SQL Server 2005 (64-bit)

The following link http://support.microsoft.com/ id=231619 (applies to SQL Server 7 and 2000) mentions that:

"SQLIOStress creates separate data and log files to simulate the I/O patterns that SQL Server will generate to its data file (.mdf) and its log file (.ldf). SQLIOStress does not use the SQL Server engine to perform the stress activity so it can be used to exercise a computer before you install SQL Server."

I would assume this meant that SQLIOStress is independent of a SQL Server version except that it tests I/O operations of SQL Server 7 and 2000.

My next question would be to ask whether there have been changes to SQL Server 2005 I/O operations which might not be tested by SQLIOStress < xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thanks,

Priyanga

Answer this question

SQLIOStress and SQL Server 2005

doroshjt
Hi Suni,

Thanks for this information. I was actually referring to SQLIOStress tool (although we are also looking at using SQLIO). This tool uses SQL Server 7 and 2000 I/O simulations to test the subsystems.

Since i am looking to test SQL Server 2005 I/O, will it be valid to test against a SQL 2000 simulation Also, do you know if there is a native 64bit version of SQLIOStress as the one that i have seen runs on WOW64 and does not test scatter io on a 64-bit server Is this a limitation of the tool or one that's inforced by the 64-bit architecture.

Thanks for you help.

Cheers,
Priyanga
Yougewenti
http://support.microsoft.com/kb/231619

"The SQLIOSim utility replaces the SQLIOStress utility. The SQLIOSim utility has been upgraded from the SQLIOStress utility. The SQLIOSim utility more accurately simulates the I/O patterns of Microsoft SQL Server 2005, of SQL Server 2000, and of SQL Server 7.0. The I/O patterns of these versions of SQL Server resemble one another. The SQLIOStress utility has been used to test SQL Server 2005 I/O requirements for many years."

Major improvements to SQLIOSim compared to SQLIOStress include:

>4GB file support
sparse file support
alternate data stream support
instant file initialization support
Patrick.E
Im trying to figure out how to use the tool.
I have documentation at hand i understand all the -K etc...

sqlio -KW -S10 -Frandom -08 -b8 -LS -F Param.txt
The param.txt is as follows
c:\sqlio_test.dat 4 0x0 100 (default)

If my database files are on the SAN say Drive R how do i change the script

R:\mytestdatabase.mdf i do not have dat files. the 4 is the CPU and 0x0 is default

The 100 is (Size of test file in MB) Ideally this should be large enough so that the test file will be larger than any cache resident on the SAN (or raid controller). Two or four times teh size of any cache allocated is a good rule of thumb to follow:

(The 100 im not sure if this the size of mytestdatabase.mdf) or
and how to get dat file when the files are MDF

Thanks
Tyler Clendenin
This is tool is primarily used to base line your disk subsystem and is independent of SQL Server. Check with your hardware vendor if numbers generated by SQLIO are within what you will expect for a given configuration. If not, then your IO configuration is mis configured. IO subsystems can get very complicated and misconfigurations are likely.

If you expect to use your disk subsytem primarily for SQL Server, you can run the following that mimick SQL Server IOs. This should be valid for SQL2005 too.
SQLIO Sample Commands

::OLTP Type IO's

call time /T

call sqlio -kW -s60 -frandom -o32 -b8 -LS -F%1.txt

call sqlio -kR -s60 -frandom -o32 -b8 -LS -F%1.txt

::LOG TYPE IO's

call time /T

call sqlio -kW -s60 -fsequential -o2 -b4 -LS -F%1.txt

call sqlio -kW -s60 -fsequential -o2 -b8 -LS -F%1.txt

call sqlio -kW -s60 -fsequential -o2 -b16 -LS -F%1.txt

call sqlio -kW -s60 -fsequential -o2 -b32 -LS -F%1.txt

call sqlio -kW -s60 -fsequential -o2 -b64 -LS -F%1.txt

::RDW Type IO's(relational dataware house)

call sqlio -kR -s60 -fsequential -o8 -b64 -LS -F%1.txt

call sqlio -kR -s60 -fsequential -o8 -b128 -LS -F%1.txt

call sqlio -kR -s60 -fsequential -o8 -b256 -LS -F%1.txt

call sqlio -kW -s60 -fsequential -o8 -b64 -LS -F%1.txt

call sqlio -kW -s60 -fsequential -o8 -b128 -LS -F%1.txt

call sqlio -kW -s60 -fsequential -o8 -b256 -LS -F%1.txt

·         Once you have base lined your IO subsystem, now you can run your SQL Server workload and look at perfmon numbers for disk subsystem. If you are running into IO issues, you can diagnose the issue as

o        See if the IO numbers are outside of baseline then your IO subsystem is not right for the workload demands or the IO pattern by the work load is very different that what you had in the baseline.

o        If IO numbers are within the base line, then the analysis is more complicated. You can have, for example, data fragmentation. But this is outside of the scope of SQLIO

Thanks
Tony Harris
On the link that you had sent, it has the following info..so it has been updated to account for SQL2005 IO patterns

"The utility was formerly named SQL70IOStress, but it has been upgraded to handle SQL Server 7.0, SQL Server 2000, and SQL Server 2005 I/O patterns. It has also been renamed SQLIOStress. In fact, SQL Server 2005 I/O patterns are similar to SQL Server 7.0 I/O patterns and to SQL Server 2000 I/O patterns. SQLIOStress has been testing for SQL Server 2005 I/O needs for several years."

thanks
Amish Bandekar
Thanks for highlighting the change to the link.

Cheers,
Priyanga
SQLIOStress and SQL Server 2005

Answer this question