ClearTrace - Free Tool to summarize SQL Trace Results

Let's say you capture the workload on your production SQL Server, by using SQL Profiler or SQL Trace to gather the events of query executions. In order to determine the most expensive queries within the workload, you need to summarize the trace results, group the events by query text, and order the summarized items by CPU or disk IO or duration. You also want to normalize the query text, such that common statements can be grouped together. ClearTrace is a free tool that can help, it can imports SQL Profiler and SQL Trace results, from SQL Server 2000 to the latest SQL Server version. Below are the steps demonstrating how to use ClearTrace:

1. Download ClearTrace from scalesql.com. You don't need to set it up on the database server, it can be executed in any computer which able to access the trace result files.
2. Extract the downloaded ClearTrace.XX.zip compressed file.
3. Execute the ClearTrace.exe executable file, which shows the ClearTrace GUI.
4. When you first time start ClearTrace in your computer, you will be asked to set the database in the Tools -> Options menu. If the database doesn’t exist you will be given the option to create it. If it does exist ClearTrace will create the necessary objects in the database. All tables and views are prefixed with “CT” so they are easy to identify.
    
   
    
   
5.  Select the first trace result file to import. Trace files in the same directory will also be imported in sequence. ClearTrace only processes RPC:Completed and SQL:BatchCompleted events. You can create the trace by yourself, including the required event columns: EventClass, TextData, HostName, ApplicationName, LoginName, SPID, Duration, EndTime, Reads Writes, and CPU. Or you can download the sql script from ClearTrace website. Anyway, I prefer creating it by myself.
    
   
   
    
   
6. Press Import Files button. ClearTrace will display the status. After the process completed, the summarized result will be shown. Then you can change the grouping and ordering of it.
    
   
7. You can also get the summarized data from the ClearTrace database, by querying the view CTTraceSummaryView.

Please be reminded that ClearTrace is still in BETA. But it still a handy tool for database performance tuning, and it's free.