How to Set Up a SQL Server Stress Test Environment in 8 Steps: Lessons From the Field

Step 2: Analyze Requirements

Once you know how the application will be used by your business, these requirements must be analyzed, translated and matched with your database design. As a result, you'll be able to predict the technical impact of your business processes in a real-life context.

The type of questions you'll have an answer for after this analysis will be:

• How many inserts/updates/deletes/selects will be performed on the most important database objects when the database is averagely stressed? How much when there is a period of more than average usage?
• How large will your database core objects become (over 1 month, 1 year, 5 years)?
• How many concurrent database users are expected? (This is not equal to application users if connection pooling is implemented.)
• What should be regarded as acceptable database response times? For online requests/for reporting requests?

Because of error margin, add 20 to 40 percent to the expected workload, depending on how comfortable you feel with the answers you get from the business.

Step 3: Define Your Disk Subsystem Requirements

As long as your application is in the preproduction phase, the hardware reserved for production can be used as a stress test environment. You must use this time as efficiently as possible. One thing you can do in an early phase is testing your disk subsystem.

The disk subsystem is in many ways the most important subsystem you should concentrate on during performance tests.

• Adjustable: CPU's and memory can be adjusted once your server is up and running. Changing your disk configuration is much harder to do.
• Experience: From my experience disk IO are the most common performance bottlenecks.
• Timing: A thorough investigation of CPU or memory usage is often very application related, which means you can only start them after you have a working version of your application available. This is not required to test your disk subsystem; there are some very valuable tools available for benchmarking your disk subsystem even before SQL Server is installed.

SQLIO.exe is just such an IO throughput benchmarking tool and spending time experimenting with it can be very useful. Some qualities of SQLIO:

• It is a low-level tool that will help you to determine the capacity of your I/O subsystem.
• It tests your disk subsystem end-to-end (HBA/switches/disks).
• It will help you to define how to setup your partitions; database files in file groups for maximum parallel IO usage.
• It is a free download.

SQLIO is easy to use and it doesn't use the SQL Server storage engine (because SQL Server does not need to be installed already) but a minimal knowledge of the storage engine will help you to define the correct parameters so your results will simulate SQL Server behavior.

What you should know before you can setup useful I/O patterns for SQL Server using SQLIO:

• The majority (90 percent) of all data page requests are read requests; log records activity is for 99 percent write activity.
• Data is accessed per page (8 KB) or per extend (64 KB) where log records are written at <1 KB; index pages are mostly accessed per extend (64 KB).
• Data and index pages are accessed randomly; log records are sequentially written.
• The majority of data page writes are performed when checkpoints occur. These checkpoints can flush excessive volumes of 8 KB pages.

The most important drive you should investigate is the one where you want to store your log file because of its synchronous behavior.

A couple of things you must keep in mind when using SQLIO:

• Make sure the total size of the test files used in each test is significantly larger than the amount of cache on the storage area network (SAN) so not all the data is transferred from RAM to disk cache (> 2 GB > 64 GB for large SAN's).
• Make sure you understand your specific hardware configuration and the theoretical limits for the given hardware. Your SAN vendor must assist you in this process.
• Store all of the benchmark data in order to compare with the SQL Server I/O throughput numbers.

You will find other important concepts in the accompanied documentation when you download the tool.

To interpret SQLIO results, you need to know how many IO's your application will be using, this knowledge depends on having reliable business requirements.

Another tool you can use to test your disk IO subsystem before SQL Server installation is the SQLIOStress utility, freely downloadable at http://support.microsoft.com/?id=231619.

"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." (From SQLIOStress Readme.doc)