ClearTrace Instructions
These instructions apply to both the extended event and trace versions of ClearTrace. 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
Note: These requirements generally apply to extended event files also.
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 nearly 2MB 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.
Trace Groups
When you load a trace file(s), ClearTrace now allows you to change the “Trace Group”. This defaults to “(One Time Trace Analysis)”. Everytime you load a trace with this name is completely replaces what was already loaded. This is a very easy way review a trace file without permanently saving it.
A Trace Group is simply a way to group traces together. For example if you plan to save traces for a number of different servers you might enter the server name as the Trace Group. When you query the traces later you are asked to specify which Trace Group you’d like to query. If you run multiple traces on a single server you might name them ServerName-TraceName. For example, your Trace Group for high read queries might be named ServerName-HighReads.
File Names
Each trace file must have a unique name. Any time a trace file is imported for a specific Trace Group it replaces what is already there. This makes it very easy to rerun traces. It also means each trace file must be named uniquely. I suggest naming trace files as TraceName-YYMMDD-HHMMSS.trc. The timestamp is based on the start date of the trace. In fact I already have a script you can use that does this. The file includes three scripts: one to run a trace, one to stop all traces and one to delete trace files older than a specified number of days.
Extended event files have a unique name by default.
Querying the Traces
You are also able to query traces based on the date. After you choose a trace you can select the date you’d like to query. Or you can run your query across all dates.
Note: I don’t think this works anymore. The beta version adds the ability to summarize by time as well as by day. For example, to group your events into 10 minute blocks enter 10 in the Time Granularity field. To group your events into 60 minute blocks enter 60 in the Time Granularity field. The default is to leave the field blank which only groups by day. The interface doesn’t yet have the ability to use the time information in its queries. You an query the view CTTraceSummaryView in the database directly to use this information.