SQL Server Magazine

Ever have trouble making sense of all the data that a server-side trace or a SQL Server Profiler session produces? If so, check out ClearData Consulting's free ClearTrace tool. ClearTrace is a summary and graphical display tool for SQL Server 2005 and 2000 trace and Profiler files. In his work as an independent consultant, SQL Server MVP Bill Graziano, who also runs the http://www.sqlteam.com Web site, wanted to create a utility similar to the Microsoft SQL Server Product Support teams' Read80Trace command-line utility for processing SQL Server 2000 trace files but one that would run on SQL Server 2005 and would display its output graphically instead of as replay markup language (RML) files or a normalized database. ClearTrace is his solution for quickly getting valuable information from server-side trace files.

Functionality
ClearTrace cleanly summarizes the query performance data that traces and Profiler collect and improves the performance-tuning process by making the assessment of SQL Server query performance easier and less arcane.

One of ClearTrace's most important features is that it "normalizes" SQL statements, eliminating variables and transient differences so that you can look at the impact of a certain class of statements. Normalization lets you know which statements are essentially identical, except for the parameters. The goal is to identify the statements that consume the most resources in aggregate. A statement that runs once and issues 100,000 reads, for example, isn't as bad as one that runs 100,000 times but issues 1,000 reads each time.

ClearTrace also performs the following operations so that it can group related types of SQL statements together and show you their impact as a category on SQL Server performance:

• Converts all constants (numeric, string, and date) to placeholders
• Renders prepared SQL and dynamic SQL code created by using the sp_ executesql stored procedure as the actual statement that SQL Server executes
• Prefixes server-side cursors with "{CURSOR}" for easy identification
• Pulls stored procedures from the RPC:Completed trace event and displays their names

ClearTrace's second-most important feature is how it displays results. The product includes a simple query tool to graphically display the trace or Profiler performance data and groups the results by SQL text, application, host, and login. You can filter by application, host, or login values, and you can sort the result sets further by CPU, reads, writes, or duration of operation, as the Results tab in Web Figure 1 (http://www.sqlmag.com, InstantDoc ID 96133) shows.

ClearTrace also sequentially processes all trace files from a trace, making it easy to work with large trace sessions. It stores all the data it collects in a SQL Server database for later reporting and performance assessment. And it can automatically move trace files into an archive directory after it has processed them.

System Requirements
ClearTrace must run on a computer that has SQL Server 2005 Developer, Workgroup, Standard, or Enterprise Edition installed. If you're using SQL Server 2005 Express Edition, you need to manually install the required SQL Server 2005 Management Objects (SMO) libraries. (You can download these libraries at http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705aa0a-b477ba72a9cb&DisplayLang=en. Note: The SMO Feature Pack doesn't always install all the necessary features, so you might need to install SMO from the SQL Server product installation CD.)

You can download ClearTrace and find instructions, sample screens, and a video demo of the product at http://www.cleardata.biz/cleartrace/download.aspx. Give us your feedback about this and other products on the Tool Time forum at http://www.sqlmag.com/go/tooltime.

End of Article