I/O and Load Testing tools

I/O and Load Testing tools

Kevin Kline
InstantDoc #47884
September 19, 2005

Load Testing SQL Server Databases
Most dba’s have experience with designing a well-tuned SQL Server database. A well-tuned design doesn’t always mean that your database will perform quickly once implemented. The best next step toward understanding the performance potential of your SQL Server database is to actual perform load testing on the database and application.
There are several free tools from Microsoft that can help. The most popular tool for performance tests against the IO subsystem with an IO stress tool to test system performance before deploying SQL Server is called SQLIO.exe. SQLIO.exe is an unsupported tool provided by Microsoft to accomplish this and is now available at
http://download.microsoft.com/download/f/3/f/f3f92f8b-b24e-4c2e-9e86-
d66df1f6f83b/SQLIO.msi.

There are also some great third-party tools such as Mercury Interactive’s LoadRunner and WinRunner and Quest Software’s Benchmark Factory.
One last note – true load testing requires a well-thought methodology. I recommend you take a look at the whitepaper "Centralized Application Performance Testing: Maturing Large IT Organizations" from LoadTester Incorporated (http://www.loadtester.com/). The whitepaper was written by my friend, Scott Moore, and incorporates many of his thoughts around a Performance Center of Excellence. It surprises my how many companies truly need to understand the Performance Center of Excellence before they embark on the load testing.
Have you done any load testing on your own SQL Server databases? How did you do it?

SQL Server 2000 I/O Basics

SQL Server 2000 I/O Basics
SQL Server I/O Basics Chapter 2 (I/O and SQL Server 2005)

Utilities
Microsoft provides a few utilities for testing the general properties of your system.
SQLIOStress.exe
SQLIOStress.exe simulates various patterns of SQL Server 2000 I/O behavior to ensure rudimentary I/O safety.
The SQLIOStress utility can be downloaded from the Microsoft Web site. See the following article.
•
How to Use the SQLIOStress Utility to Stress a Disk Subsystem such as SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;231619
Important The download contains a complete white paper with extended details about the utility.
SQLIO.exe
SQLIO.exe is a SQL Server 2000 I/O utility used to establish basic benchmark testing results.
The SQLIO utility can be downloaded from the Microsoft Web site. See the following:
•
SQLIO Performance Testing Tool (SQL Development) – Customer Available
http://download.microsoft.com/download/f/3/f/f3f92f8b-b24e-4c2e-9e86-d66df1f6f83b/SQLIO.msi


For detailed information about the use of caching controllers and SQL Server, see the following articles on the Microsoft Support Web site.
• SQL Server and Caching Disk Controllers
http://support.microsoft.com/default.aspx?scid=kb;en-us;86903
• Using Disk Drive Caching with SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;234656
• Using Hard Disk Controller Caching with SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;46091


Note: You can also read more about I/O and SQL Server 2005 in SQL Server I/O Basics Chapter 2.