Read80Trace Simplifies Trace Analysis
As a DBA, you probably often find yourself striving to improve the performance of SQL Server queries. Typically, you do so by analyzing large SQL Server trace files, trying to discover the queries or batches that take too long to run, perform too many I/O requests, or use too many CPU cycles. Although you can open SQL Server traces in Profiler and sort them by different columns (e.g., duration, cpu, reads, writes), this process tends to be time-consuming and doesn't provide aggregated data. Alternatively, you could load the traces into SQL Server tables and run aggregate queries against these tables to analyze the worst-performing queries in the trace. The Read80Trace utility simplifies trace analysis by automatically creating an analysis database for the trace file(s) you provide. It also generates a graphical HTML output file that contains detailed information about the load captured in the trace files. Let's examine how to capture a SQL Server trace and use Read80Trace to analyze its contents.
Capturing a SQL Server Trace
Before you use Read80Trace, you must first capture a SQL Server trace to run the utility against. When you capture a Profiler trace for use with Read80Trace, you need to capture several events and columns. If you use the SQLProfilerStandard default Profiler template (SQLProfilerStandard.tdf) to capture trace information, make sure that you add the EndTime and DatabaseId columns to the template before capturing the trace. If you want statement-level analysis, you need to select statement-level events such as SP:StmtStarting, SP:StmtCompleted, and Showplan Statistics when you create the trace. You can find more information about the events and columns you need to capture in a trace in the Help file (read80trace.chm) under the topic Necessary Events And Columns For Performance Analysis.
I prefer to use the trace stored procedures (e.g., sp_trace_create, sp_trace_setevent) to capture the SQL trace instead of using the Profiler trace GUI utility because the stored procedures cause less overhead on the server than Profiler. An easy way to generate an SQL script that uses the trace procedures to run a trace is to use the Script Trace option in Profiler and run the generated script on your SQL Server system. Another option for collecting Profiler trace information is to use the PSSDiag diagnostic data-collection utility, which is documented in the Microsoft article "PSSDIAG data collection utility" at http://support.microsoft.com/?kbid=830232.
After you've obtained a SQL Server trace file that contains the collected events, run Read80Trace against the trace file by executing a command similar to this:
read80trace
-ic:\temp\test\sample_trace.trc
-ic:\temp\test\sample_trace.trc
-oc:\temp\test\output -f
-SServerName -E
The Read80Trace utility parses the specified trace file, creates an output.htm file in the specified output folder, and opens the file in your browser. The utility connects to a SQL Server system (by default, to the local server if no parameter is specified, or to a server name you specify with -S) and creates a database called PerfAnalysis (the default) or a nondefault database name, which you specify with the -d option. Read80Trace connects to the SQL Server system to perform extended analysis and aggregations on the trace file and generates a read80trace.log file. This log file records useful troubleshooting information about the utility, such as the server it connected to, the number of events it processed, and warnings. Because the trace analysis can be CPU-intensive, you should use a test (i.e., nonproduction) SQL Server system for Read80Trace to connect to.
Read80Trace Output
The output.htm file that Read80Trace generates contains helpful analysis information, such as rollups by batch duration, CPU, reads, and writes. If the trace captured statement-level events, the output file contains rollups by statement duration, CPU, reads, and writes. You can use this output to quickly identify slow-running and resource-intensive queries in the system.
The Read80Trace utility internally produces Replay Markup Language (RML) files named SQLnnnnn.rml (where nnnnn is a number that represents the server process ID—SPID—for each process) in the output directory. The RML files are XML files that contain detailed information about each SPID, such as connection and query information. You can use RML files to control how and when commands are submitted to a SQL Server system from a text document; RML also serves as a bridge between Read80Trace, OSTRESS, and related utilities. You can find more information about RML's design and the utilities associated with it in the utilities' Help files.
When you run the Read80Trace command, you can specify any of three options for displaying trace output:
- -M—This option breaks out the trace file by SPIDs, so you'll see multiple files (e.g., SPID00051.trc, SPID00052.trc) in the output directory. This option is useful when you need to analyze a problem from a specific trace and you know the SPID of the process involved.
- -r##—If you have a large number of trace files that you generated sequentially by using the TRACE_FILE_ROLLOVER option in the sp_trace_create procedure, they'll be named filename.trc, filename_1.trc, filename_2.trc, and so on. In this case, you can use the -r## option to start analyzing the traces sequentially. The number symbols (##) represent the number of trace files in the series following the first trace file you specified.
- -d databasename—You can use this option to specify a database name (instead of the default PerfAnalysis database) to store aggregate information. You might find this option useful when you're analyzing multiple trace files and want to store their information in separate databases.
The Read80Trace Help file provides more information about tables, views, and queries used to generate the trace analysis as well as a complete list of Read80Trace parameters. As I mentioned earlier, the Help file provides a design overview of the utility and also includes an entity relationship (ER) diagram of the tables in the PerfAnalysis database.
Advanced PerfAnalysis Queries
If you want a more detailed analysis of the trace information than what the output.htm file provides, you can run queries against the PerfAnalysis database and analyze the results of those queries. The PerfAnalysis database has multiple views that provide information about the queries in the trace file. Note that the generatexml.sql file in the output directory shows what queries are executed against this database to generate the aggregate information that's in the default output.htm file. For example, the query in Listing 3 joins two views in the database to generate the "Rollup_Batch_Duration" output.
You could write your own PerfAnalysis database queries and generate custom analysis information. For example, the query in Listing 4 generates a list of batches that received an attention signal or a query-cancellation notification during the time the trace was captured.
You can use the Read80Trace and OSTRESS tools in several other advanced stress-testing and analysis scenarios. For example, you can use the .rml files that the Read80Trace file produces as input to the OSTRESS utility to replay the commands in those files, as this sample command shows:
ostress -E -dpubs
-ic:\temp\test\output
\SQL00139.rml
-SServerName -n5
In this example, the .rml file that Read80Trace generated for SPID 139 is input to the OSTRESS utility, and the queries that SPID 139 generated are run over five concurrent connections.
Useful Analysis Tools
I've explained how you can use the OSTRESS tool to generate specific stress loads on your SQL Server system, which can reveal performance trouble spots such as slow-running queries, blocking, and deadlocking issues that you can detect and analyze before putting your application into production. I've also shown you how to use the Read80Trace tool to analyze trace statistics to tune database and query performance. You'll find these highly useful tools valuable additions to your DBA toolkit.
End of Article
Prev. page
1
[2]
next page -->
