Collecting SQL Trace Data to Monitor and Improve Performance in Load Tests
SQL tracing is a tool that you can use in your load tests to help you monitor and improve the performance of your Web applications. You should use SQL tracing only if your Web application uses SQL Server to store data.
Note
SQL tracing is not restricted to Web performance tests. Unit tests for applications that access SQL Server databases are also good candidates for SQL tracing.
You can collect SQL trace data during a load test run to analyze later. Collecting trace data lets you identify the slowest running queries and stored procedures in the SQL Server database being tested. You enable tracing by editing your load test in the Load Test Editor after you create it.
If SQL tracing is enabled, a file is created during the load test run that contains the trace data. This data is automatically saved in the Load Test Results Store at the end of the test run and the trace file is then deleted. You analyze the trace data in the SQL Trace table after your load test has completed. For more information, see The SQL Trace Data Table.
If SQL tracing is enabled, SQL trace data can be displayed in the Load Test Analyzer on the SQL Trace table that is available in the Tables View. To diagnose SQL performance problems, SLQ tracing is a fairly easy-to-use alternative to starting a separate SQL Profiler session while the load test is running. To enable this feature, the user who is running the load test must have the SQL privileges that are required to perform SQL tracing, and a directory (usually a share) where the trace file will be written must be specified. At the completion of the load test, the trace file data is imported into the load test repository and associated with the load test that was run so that it can be viewed at any later time using the Load Test Analyzer.
Note
To use SQL tracing in a load test that is run locally on a computer that is running Windows Vista, you must be a member of the sysadmin role on the SQL Server instance that is being traced. To fix this problem, a SQL Server administrator must add you to the sysadmin role.
Note
For a complete list of the run settings properties and their descriptions, see Load Test Run Setting Properties. Tasks
Requirements
-
Visual Studio Ultimate
How to: Integrate SQL trace data
To enable SQL tracing for a load test run
Open a load test in the Load Test Editor.
Right-click the active Run Settings node for your load test and then choose Properties.
The Properties window is displayed.
Set the SQL Tracing Enabled property. True indicates that SQL Tracing is enabled; False indicates that it is not.
Set the SQL Tracing Connect String property. Type the connection string of the SQL server you want to monitor, or choose the ellipsis button to open the Connection Properties dialog box.
Set the SQL Tracing Directory property. Type a folder in which to store the SQL trace data. The path must be accessible to the SQL Server and the client that is running Visual Studio Ultimate.
Set the Minimum Duration of Traced SQL Operation property. Type a value for the minimum duration of traced queries. For example, 500 indicates that all queries that take longer than 500 are traced. The units are in milliseconds.
Note
If you are using SQL Server 2005, the units of duration are in microseconds.
Save and run your test.
You can view the SQL Tracing data only after your load test has completed. For more information, see The SQL Trace Data Table.
Note
For a full list of the run settings properties and their descriptions, see Load Test Run Setting Properties.
See Also
Concepts
Managing Load Test Results in the Load Test Results Repository