Saving and Exporting Traces

You have multiple options for saving trace information. You can save the current contents of the trace window to a file or table, or, as described earlier, you can choose to save trace events to a file or table as the trace runs. This will direct the SQL Profiler to route all of the SQL statements executed during the trace to a file or table as well as to the trace window.

TIP

If you are executing a long-running trace, you should save to a file or table as the trace runs to be sure that you capture the entire trace. Saving to a file allows you to clear the current contents of the trace window and not lose the trace information.

Also, if you are saving to a trace file as the trace runs, and the trace is expected to be large, then you can utilize the Enable File Rollover option, which will create a new event file when the file reaches the size that you specify. This allows you to have smaller, more manageable trace files for your review.

If you save a trace to a table, the number of columns in the trace table depends on how many data columns you defined in the trace. Saving to a table is useful if you want to perform analysis on the trace with some other tool, such as Microsoft Access or Microsoft Query Analyzer. Because the data resides in a table, you can run more complex queries and reports on the trace data that include sorting, grouping, and more complex search conditions than are available through the SQL Profiler filters.

NOTE

Two distinct save operations are available in the SQL Profiler. You can save trace events to a file or table as just described, or you can save a trace definition in a template file. The Save As Trace Table and Save As Trace File options are for saving trace events to a file. The Save As Trace Template option saves the trace definition. Saving a trace template saves you the trouble of having to go through all the properties each time to set up the events, data columns, and filters for your favorite traces.

As an alternative to saving all the event data associated with a particular trace, you can select specific event rows from the SQL Profiler windows . You can capture all the trace information associated with a trace row by selecting a row in the trace output window of Profiler and choosing the Copy option from the Edit menu. Or, you can just copy the event text (typically a SQL statement) by selecting the row, highlighting the text in the lower windowpane, and using the Copy option.

This data can then be pasted into SQL Query Analyzer or the tool of your choice for further execution and more detailed analysis. This can be particularly useful during performance tuning. After you identify the long-running statement or procedure, you can copy the SQL, paste it into Query Analyzer, and display the Query Plan to determine why the query was running so long.

Importing Trace Files

A trace saved to a file or table can be read back into SQL Profiler at a later time for more detailed analysis or to replay the trace on the same SQL Server or another SQL Server instance. You can import data from a trace file or trace table by choosing the Open option in the File menu. Under the Open option, you will be able to choose either a trace file or trace table. If you choose to open a trace file, you will be presented with a dialog box to locate the trace file on the local machine. If you choose to import a trace table, you will first be presented with a connection dialog box to specify the SQL Server and login id and password to connect to it. Once successfully connected, you will be presented with a dialog box to specify the database and the name of the trace table you want to import from.

Once you've specified the trace file or trace table to import into Profiler, the entire contents of the file or table will be read in and displayed in a Profiler window. Sometimes the trace file or trace table can be very large and it can be difficult to analyze all the data at once, or you may just want to analyze events associated with a specific application or table, or specific types of queries.

To limit the amount of information displayed in the Profiler window, you can filter out the data displayed via the Properties dialog. You can choose which events and data columns you want to display and also specify conditions in the Filters tab to limit the rows displayed from the trace file or trace table. These options do not affect the information stored in the trace file or trace table, only what information is displayed in the Profiler window.

Importing a Trace File into a Trace Table

While you can load a trace file directly into Profiler for analysis, very large files can be difficult to analyze. Profiler will load the entire file, and if it's very large, this can take quite a while and the responsiveness of Profiler might not be the best. If the trace was split across multiple files, you'll have to open each file individually into separate Profiler windows, making an overall analysis difficult.

You can use the trace filters to limit which rows are displayed, but not which rows are imported into Profiler. The filters also don't filter out NULL values and you often end up with a bunch of rows displayed with no data in the columns you want to analyze. In addition, while the filters allow you to limit which rows are displayed, they don't really provide a means of running any more complex reports on the data like generating counts of events or displaying the average query duration.

Fortunately, SQL Server 2000 provides a way for you to import a trace file into a trace table. When importing a trace file into a trace table, you can filter the data before it goes into the table as well as combine multiple files into a single trace table. Once the data is in a trace table, you can load the trace table into Profiler, or write your own queries and reports against the trace table for more detailed analysis than is possible in Profiler.

Microsoft SQL Server includes some built-in user -defined functions for working with Profiler traces. The fn_trace_gettable function is used to import trace file data into a trace table. The following is the syntax for this function:

 fn_trace_gettable  (  [  @filename =  ]  filename  , [  @numfiles =  ]  number_files   )  

This function returns the contents of the specified file as a table result set. You can use the result set from this function just like you would a table. By default, the function returns all possible Profiler columns, even if no data was captured for the column in the trace. To limit the columns returned, specify the list of columns in the query. Table 7.3 lists the available columns in a Profiler trace. The column IDs are useful when creating a server-side trace, which is covered in the "Defining Server-Side Traces" section later in this chapter.

Table 7.3. Profiler Columns and Column IDs

Column Name	Column ID
TextData	1
BinaryData	2
DatabaseID	3
TransactionID	4
Reserved	5
NTUserName	6
NTDomainName	7
ClientHostName	8
ClientProcessID	9
ApplicationName	10
SQLSecurityLoginName	11
SPID	12
Duration	13
StartTime	14
EndTime	15
Reads	16
Writes	17
CPU	18
Permissions	19
Severity	20
EventSubClass	21
ObjectID	22
Success	23
IndexID	24
IntegerData	25
ServerName	26
EventClass	27
ObjectType	28
NestLevel	29
State	30
Error	31
Mode	32
Handle	33
ObjectName	34
DatabaseName	35
Filename	36
ObjectOwner	37
TargetRoleName	38
TargetUserName	39
DatabaseUserName	40
LoginSID	41
TargetLoginName	42
TargetLoginSID	43
ColumnPermissionsSet	44

If you want to limit the rows retrieved from the trace file, specify your search conditions in the WHERE clause. If your Profiler trace used rollover files to split the trace across multiple files, you can specify the number of files you want it to read in. You can specify the default value of default , or -1 , to have it read all rollover files for the trace. Listing 7.1 provides an example of creating and populating a trace table from a trace file using select into and then adding additional rows via insert . Note that the example limits the columns and rows returned by specifying a specific column list and search conditions in the WHERE clause.

Listing 7.1 Creating and Inserting Trace Data into a Trace Table from a Trace File

 /******************************************************************** ** NOTE - you may need to edit the path/filename on your system if **        you use this code to load your own trace files *********************************************************************/ select EventClass,        EventSubClass,        TextData = convert(varchar(8000), TextData),        BinaryData,        ApplicationName,        Duration,        StartTime,        EndTime,        Reads,        Writes,        CPU,        ObjectID,        IndexID,        NestLevel    into TraceTable    FROM ::fn_trace_gettable('c:\temp\sampletrace_20020826_0232.trc', default)    where TextData is not null       or EventClass in (16, --  Attention                         25, -- Lock:Deadlock                         27, -- Lock:Timeout                         33, -- Exception                         58, -- Auto Update Stats                         59, -- Lock:Deadlock Chain                         79, -- Missing Column Statistics                         80, -- Missing Join Predicate                         92, -- Data File Auto Grow                         93, -- Log File Auto Grow                         94, -- Data File Auto Shrink                         95) -- Log File Auto Shrink Insert into TraceTable (EventClass, EventSubClass,             TextData, BinaryData,             ApplicationName, Duration, StartTime, EndTime, Reads, Writes,             CPU, ObjectID, IndexID, nestlevel)      select EventClass, EventSubClass,             TextData = convert(varchar(7900), TextData), BinaryData,             ApplicationName, Duration, StartTime, EndTime, Reads, Writes,             CPU, ObjectID, IndexID, nestlevel         FROM ::fn_trace_gettable('c:\temp\sampletrace_20020826_0108.trc', -1)         where TextData is not null            or EventClass in (16, --  Attention                              25, -- Lock:Deadlock                              27, -- Lock:Timeout                              33, -- Exception                              58, -- Auto Update Stats                              59, -- Lock:Deadlock Chain                              79, -- Missing Column Statistics                              80, -- Missing Join Predicate                              92, -- Data File Auto Grow                              93, -- Log File Auto Grow                              94, -- Data File Auto Shrink                              95) -- Log File Auto Shrink go 

Once the trace file is imported into a trace table, you can open the trace table in Profiler, or run your own queries against the trace table. For example, the following query returns the number of lock timeouts encountered for each table during the period that the trace was running:

 select object_name(id), count(*)      from TraceTable     where EventClass = 27 -- Lock:Timout Event     group by object_name(id) go 

Analyzing Traces with the Index Tuning Wizard

In addition to being able to manually analyze traces in Profiler, you can also use the Index Tuning Wizard to analyze the queries captured in your trace and recommend changes to your indexing scheme. You can invoke the Index Tuning Wizard from the Tools menu in SQL Profiler. The Index Tuning Wizard can read in a trace that was previously saved to a table or a file. This allows you to capture a workload, tune the indexing scheme, and rerun the trace to determine if the index changes improved performance as expected.

Because the Index Tuning Wizard analyzes SQL statements, make sure that the trace includes one or more of the following events:

SP:StmtCompleted

SP:StmtStarting

SQL:BatchCompleted

SQL:BatchStarting

SQL:StmtCompleted

SQL:StmtStarting

One of each class (one SP: and one SQL: ) is sufficient to capture dynamic SQL statements and statements embedded in stored procedures. You should also make sure that the trace includes the Text data column, which contains the actual queries.

The Index Tuning Wizard analyzes the trace and gives you recommendations along with an estimated improvement in execution time. You can choose to create indexes now or at a later time or save the CREATE INDEX commands to a script file.

For more information on using the Index Tuning Wizard, see Chapter 34, "Indexes and Performance."