For years, Microsoft Product Support Services (PSS) has used two tools—OSTRESS and Read80Trace—to simulate scenarios and analyze large SQL Server trace files for its customers. At the Professional Association for SQL Server (PASS) 2004 conference, PSS released these tools to the public. SQL Server DBAs and developers will find OSTRESS useful when working on complex stress-testing scenarios and Read80Trace helpful for analyzing SQL Server trace files to troubleshoot performance issues. I'll provide some detailed usage scenarios for these tools and give you pointers for using them effectively.
Getting Started
First download the OSTRESS and Read80Trace utilities from the download link in the Microsoft article "Description of the SQL Server Performance Analysis Utilities Read80Trace and OSTRESS" at http://www.support.microsoft.com/?kbid =887057. By default, Windows installs the tools (ostress.exe and read80trace.exe) and the Help files (ostress.chm and read80trace.chm) in the C:\rml folder. Both tools' Help files provide useful documentation and design overviews. You can find additional information about using the tools at Microsoft's PSS Service Center Labs Web page at http://www.microsoft.com/downloads/details.aspx?familyid=aec18337-887f-4ec6-a858-81f84de8082f&displaylang=en.
To use OSTRESS to stress-test SQL Server, you need to specify a query or script file to run several times over multiple simultaneous connections. For example, to simulate a scenario of five simultaneous connections running the same query on the pubs database, you'd use the following syntax to connect to a local server and execute a query:
ostress -SServerName -E -dpubs
-Q"SELECT * FROM authors" -n5
(Commands wrap to multiple lines here because of space constraints; you should type the command on one line.) The parameters for OSTRESS are similar to those for the OSQL utility and are case-sensitive. You use the -S parameter to specify the server to connect to, -E to specify Windows authentication, -d to specify the database in which the query needs to be run, and -Q to specify the query to run. The -n5 parameter (-n specifies the number of threads that will be spawned concurrently to run each input file or query) tells OSTRESS to open five simultaneous connections to SQL Server and execute the query in each of them. The OSTRESS Help file contains a complete list and description of the command parameters.
Alternatively, you can specify a batch file as input to OSTRESS by using the -i parameter, like this:
ostress -SServerName -E -dpubs
-ic:\temp\test\batch1.sql
-n5 -r5
Here the batch file (batch1.sql) contains the statement
SELECT * FROM Authors
The -r5 parameter specifies that each connection will run the batch for five iterations. Therefore, this OSTRESS command would run the query 25 times (5 threads * 5 iterations). By using the -n and the -r parameters, you can immediately see the OSTRESS utility's usefulness in simulating a load on a SQL Server system.
Using OSTRESS to Simulate Random Timeouts
Another way you can use OSTRESS is to simulate query timeouts, which you do by specifying the -t parameter. This technique is useful in testing a scenario in which a user cancels a query or the query times out and leaves orphaned transactions in SQL Server—for example, when the application doesn't implement proper error handling for timeouts or query cancellation and transactions that weren't rolled back cause blocking and other concurrency problems. This sample command runs the batch file over 10 concurrent connections, five iterations per connection, with a 1-second query timeout:
ostress -SServerName -E -dpubs
-ic:\temp\test\batch1.sql
-n10 -r5 -t1
When you run this command, you'll notice that some timeouts in the output look similar to those in the output in Figure 1 shows.
You can also configure OSTRESS to randomly simulate timeouts or simulate timeouts a certain percentage of the time. You can find more information about these techniques under the Random Events topic in the OSTRESS Help file. To simulate timeouts a certain percentage of the time, use the CancelPct configuration value in the control file and pass the control file as the -c parameter to the OSTRESS command. The \rml directory contains a sample control file (sample.ini) that provides examples of setting advanced options. The following sample command demonstrates using the control file to set the behavior of the OSTRESS execution:
ostress -SServerName
-E -dpubs
-ic:\temp\test\batch1.sql
-csample.ini
The settings in a sample.ini file to cancel queries 10 percent of the time would look like this:
[Query Options]
CancelPct=10.00
You can find more documentation of the different configuration options under the Control File topic in the Help file.
Using OSTRESS to Reproduce Deadlock Scenarios
Because deadlock scenarios tend to be highly time-sensitive, trying to reproduce them on development or test machines with few users and scant data can be tricky. If you suspect which queries and procedures are deadlocking, you can create a batch file that includes those queries and procedures and try to simulate the deadlocking by running the batches through multiple connections and iterations. Figure 2 shows a small batch file that you can run from a command prompt to simulate a simple deadlocking scenario on the sample pubs database.
The sample scripts in Listings 1 and 2 deliberately simulate deadlocking. Listing 1 updates the Authors table, then the Titles table; Listing 2 performs these actions in reverse. When you run deadlock.bat, which starts two instances of OSTRESS, you'll notice that some of the connections generate error messages like the one that Figure 3 shows.
To troubleshoot the deadlocking issue quickly, you can run a SQL Server Profiler trace (or use the sp_trace_* procedure to run a server-side trace) at the same time you run the OSTRESS command or turn on the deadlocking trace flags by running the command
DBCC TRACEON(1204,3605,-1)
These deadlock trace flags will generate detailed deadlock information in the SQL Server error log file. You can disable the trace flags by running the DBCC TRACEOFF command.
Using OSTRESS to Reproduce RPC Events
Most applications submit queries to SQL Server as remote procedure call (RPC) events instead of SQL:Batch events. If you want to reproduce a similar behavior from within OSTRESS or OSQL, you can use the ODBC call syntax to do so (especially if you can reproduce a specific problem only through the affected application and not from Query Analyzer).
To capture queries as RPC events, you need to include the ODBC call escape-clause syntax in the batch file you provide to OSTRESS, as the following example shows:
{call pubs.dbo.byroyalty (10)}
When you run this statement in a tool such as Query Analyzer and view a Profiler trace of the query, you'll see that the query is recorded in the trace as an RPC event, as Figure 4 shows. The following query uses the regular EXEC syntax and is recorded in the trace as a SQL:Batch event, as Figure 5 shows.
EXEC pubs.dbo.byroyalty 10
For more information about using ODBC calls, see the SQL Server Books Online—>BOL—topic How to call stored procedures (ODBC).
Prev. page
[1]
2
next page 
