ClearTrace Instructions
This software is still in BETA. This information is preliminary and incomplete. It should help you use the product though.
Launching
The first time you launch ClearTrace it will ask you 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.
Trace File Requirements
ClearTrace will summarize the CPU, Reads, Writes and Duration column if they are present. Please note that Duration from SQL Server 2005 traces is stored in microseconds (one-millionth of a second) while Duration from SQL Server 2000 is stored in milliseconds (one-thousandth of a second). CPU is always stored in milliseconds.
ClearTrace will group by TextData (the SQL statement), ApplicationName, LoginName and/or HostName if those columns are present in the trace. It uses EndTime rather than StartTime.
It only processes RPC:Completed and SQL:BatchCompleted events. All other events are skipped while reading the trace file. Certain SQL statements are ignored during the processing. These include sp_cursorclose, sp_cursorfetch and sp_reset_connection. Cursors are identified by prefixing their normalized SQL text with "{CURSOR}".
You can download three stored procedures that I've found useful in tracing. One launches a trace with all the columns that ClearTrace needs. The second stops all currently running traces. The third deletes all trace files older than the specified number of days.
Processing
Processing trace files is very CPU intensive. The application can process trace files at over 1MB per second depending on the computer.
Directories
ClearTrace "knows" about three directories. The first directory is the trace directory. This is the directory where the trace files are located when it starts processing. The second two directories are optional. They are the Work Directory and the Archive Directory. If the Work directory is specified the trace files are moved from their original directory to this directory prior to being processed. Any trace file that is currenty being written to will be skipped. If the Archive directory is specified the trace files are moved to this directory after processing is completed. If you plan to use these options please choose a naming scheme that will avoid duplicate file names.
Saving
The trace file is stored in a summarized format. Dimensions are broken out for application, host, login, text data and trace file name. This can results in a 10x reduction in storage size from a SQL Server 2000 trace file. SQL Server 2005 traces seem to store information more effeciently. All objects are prefixed with "CT" to identify them as ClearTrace objects. A view (CTTraceDetailView) is provided to query the tables. The information is also pre-summarized into a table (CTTraceSummary) for faster querying. This can be queried through a view called CTTraceSummaryView.