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.
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.
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
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:
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."