SQL Server Magazine

SQL Server’s native tools make it easy to determine the speed of a query in isolation. However, it’s not always easy to test the performance of a query that’s being executed by multiple virtual users on the same server. To address this problem, SQL Server MVP Adam Machanic developed SQLQueryStress as a multi-threaded query benchmarking tool. SQLQueryStress lets you quickly and easily test the scalability and load of a query and its effect on overall system performance.

SQLQueryStress includes a simple GUI, which is shown in Web Figure 1 (www.sqlmag.com, InstantDoc ID 97906). Before running a load test, you must first set up a connection to the database by clicking Database, which launches the Database Connection dialog box. To test a query’s effect on system performance, enter a parameterized or non-parameterized query or stored procedure call into the Query box.

The SQLQueryStress GUI includes the following important features:

• The Number of Iterations drop-down box—You can use this drop-down box to define the number of times the query will be executed for each virtual user.
• The Number of Threads drop-down box—You can use this drop-down box to define the number of virtual users (up to 200) that the query will scale to.
• The GO button—Click the GO button to start the load test. The test’s progress is shown in both the Progress bar, which measures the number of tests completed against the number of tests to be run, and the Iterations Completed field. Although you can click Cancel to stop a test before it’s complete, it might take several seconds to take effect, especially in a test with many threads.
• Metrics—Several fields show query performance. The Client Seconds/Iteration field shows the average runtime for all iterations of the test as recorded by the client. The CPU Seconds/Iteration and Actual Seconds/Iteration fields show the average CPU time per iteration and the average total query time, respectively. The Logical Reads/ Iteration field reports the average number of logical buffer cache reads per iteration.
• Total Exceptions—This field tracks the exceptions and errors that occur during the test; you can view these in detail by clicking the […] button.
• Parameter Substitution—To supply values for the tool to use for parameters in the Query area, click Parameter Substitution. You can define each variable, its data type, and the value supplied for the variable. Values supplied for the variable can be associated with a query. For example, if you’re testing a query that uses employee IDs from the Employee table and enter

SELECT emp_id FROM employee

in the Parameter Query text box, SQLQueryStress will then substitute a unique value for the emp_id parameter with each test iteration. If the query that’s mapped to the parameter doesn’t provide enough values, then SQLQueryStress will loop back to the first value and reuse the values until the test is complete.

To save a test, click File, Save As. You can change some of the test’s parameters under File, Options. For example, you can change the Connection Timeout valve or enable or disable Connection Pooling to see the effect of creating and deleting a new connection with each test iteration. You can also collect CPU and Read statistics by selecting or clearing the Collect Time Statistics and Collect I/O Statistics check boxes, respectively. Disabling these settings makes the test use fewer system resources. In addition, selecting the Force Client Retrieval of Data check box makes SQLQueryStress return data to the client over the network, thus factoring the network and client response time into the test.

SQLQueryStress requires the Microsoft .NET Framework 2.0 and runs on Windows Vista, Windows Server 2003, Windows XP, and Windows 2000. It can run query tests against both SQL Server 2005 and 2000.