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



    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features
     
    ASPTreeView.com
     
    Evaluation has їРХЛexpired.
    Info...