Scripting Traces for Performance Monitoring on SQL Server - Page 3

Loading the Trace Script into a Table

Working with trace data in disk file is not easy. I suppose it is possible to load the data into Excel, SAS, or some other analysis tool. However, I find that the best place to work with the data is in a SQL table. The next step in the process loads the trace file on disk into a table for analysis.

If you ask SQL Profiler to create the trace into a table for you, it creates a table with just the columns that you have selected for the trace. Since SQL Profiler knows which columns it's recording, letting it create a table is sufficient for one time use. However, for reporting purposes, having different traces in different tables is inconvenient. The best approach is to use a table that can store all columns of any trace. Having a single table also helps because there might be additional columns recorded as the trace is modified over time. The next listing has the CREATE TABLE script for dbaTraceDetail that does just that.

CREATE TABLE dbo.dbaTraceDetail (
        dbaTraceID int NOT NULL ,
        RowNumber int IDENTITY (1, 1) NOT NULL ,
        StartTime datetime NULL ,
        EndTime datetime NULL ,
        EventClass int NULL ,
        EventSubClass int NULL ,
        TextData ntext NULL ,
        BinaryData image NULL ,
        Duration bigint NULL ,
        Reads bigint NULL ,
        Writes bigint NULL ,
        CPU int NULL ,
        DatabaseID int NULL ,
        DatabaseName nvarchar (128) NULL ,
        TransactionID bigint NULL ,
        SPID int NULL ,
        NTUserName nvarchar (128) NULL ,
        NTDomainName nvarchar (128) NULL ,
        HostName nvarchar (128) NULL ,
        ClientProcessID int NULL ,
        LoginName nvarchar (128) NULL ,
        DBUserName nvarchar (128) NULL ,
        ApplicationName nvarchar (128) NULL ,
        [Permissions] int NULL ,
        Severity int NULL ,
        Success int NULL ,
        IndexID int NULL ,
        IntegerData int NULL ,
        ServerName nvarchar (128) NULL ,
        ObjectType int NULL ,
        NestLevel int NULL ,
        State int NULL ,
        Error int NULL ,
        Mode int NULL ,
        Handle int NULL ,
        ObjectID int NULL ,
        ObjectName nvarchar (128) NULL ,
        FileName nvarchar (128) NULL ,
        OwnerName nvarchar (128) NULL ,
        RoleName nvarchar (128) NULL ,
        TargetUserName nvarchar (128) NULL ,
        LoginSid image NULL ,
        TargetLoginName nvarchar (128) NULL ,
        TargetLoginSid image NULL ,
        ColumnPermissions int NULL 
        , CONSTRAINT dbaTraceDetail_PK PRIMARY KEY CLUSTERED
            (dbaTraceID, RowNumber ) on [PRIMARY]
        , CONSTRAINT dbaTraceDetail_FK_dbaTrace FOREIGN KEY
            (dbaTraceID) REFERENCES dbo.dbaTrace (dbaTraceID)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

As you can see down in the shaded area near the bottom of the script, the table has a foreign key relationship with the dbaTrace table created in the previous section. By having the dbaTraceID column as the lead part of the key, many traces can be kept in the same table. Since the table has every column that could be included in a trace, any trace can be loaded into it.

Loading the table from the trace file relies on one of SQL Server's system user-defined functions. "System" and "user-defined" might sound like a contradiction, but they are functions, written in T-SQL using the CREATE FUNCTION statement, that SQL Server adds to itself during installation. The function in question is fn_trace_gettable. It is incorporated into the stored procedure dba_Trc_LoadFile that follows. The call to fn_get_sql is in the shaded line.

CREATE PROCEDURE dbo.dba_Trc_LoadFile 

    @FileName        nvarchar(256) -- File to load
  , @dbaTraceID         int  -- Id of dbaTrace use
  , @DeleteOldRows   BIT = 1 -- Should Old Rows be removed?
  , @RowsLoaded      int  OUTPUT -- Rows of trace data loaded
AS 
/*
* Loads a trace file from disk into the dbaTraceDetail table and
* updates the corresponding dbaTrace row to reflect that the file
* has bee loaded.
*
DECLARE @Rows int, @RC int
exec @RC = dbo.dba_Trc_LoadFile 'c:\ExampleTrace.trc'
                 , 1 , default, @Rows OUTPUT
PRINT 'Loaded ' + CONVERT(varchar(9), @Rows) + ' Rows   RC = ' 
                 + CONVERT (varchar(9), @RC)
****************************************************************/

DECLARE @myError         int        -- Local copy of @@Error
      , @myRowcount      int     -- Local copy of @@ROWCOUNT

SET NOCOUNT ON -- Don't report the count of effected rows

BEGIN TRANSACTION

-- Remove any old rows from the trace.
IF @DeleteOldRows = 1 BEGIN 
    DELETE FROM dbo.dbaTraceDetail WHERE dbaTraceID=@dbaTraceID
    SELECT @myError = @@Error, @myRowcount = @@Rowcount
    IF @myError <> 0 BEGIN
       ROLLBACK TRANSACTION   
       RETURN @myError
    END    
END

-- This statement loads the file
INSERT INTO dbo.dbaTraceDetail 
       (dbaTraceID, StartTime, EndTime, EventClass, EventSubClass
      , TextData, BinaryData, Duration, Reads, Writes, CPU, DatabaseID
      , DatabaseName, TransactionID, SPID, NTUserName, NTDomainName
      , HostName, ClientProcessID, LoginName, DBUserName
      , ApplicationName, [Permissions], Severity, Success, IndexID
      , IntegerData, ServerName, ObjectType, NestLevel, State, Error
      , Mode, Handle, ObjectID, ObjectName, [FileName], OwnerName
      , RoleName, TargetUserName, LoginSid
      , TargetLoginName, TargetLoginSid, ColumnPermissions)
   SELECT @dbaTraceID, StartTime, EndTime, EventClass, EventSubClass
      , TextData, BinaryData, Duration, Reads, Writes, CPU, DatabaseID
      , DatabaseName, TransactionID, SPID, NTUserName, NTDomainName
      , HostName, ClientProcessID, LoginName, DBUserName
      , ApplicationName, [Permissions], Severity, Success, IndexID
      , IntegerData, ServerName, ObjectType, NestLevel, State, Error
      , Mode, Handle, ObjectID, ObjectName, [FileName], OwnerName
      , RoleName, TargetUserName, LoginSid
      , TargetLoginName, TargetLoginSid, ColumnPermissions
     FROM ::fn_trace_gettable(@FileName, default)

SELECT @myError = @@Error, @myRowcount = @@Rowcount
IF @myError <> 0 BEGIN
   ROLLBACK TRANSACTION   
   RETURN @myError
END

SET @RowsLoaded = @myRowcount -- Save the count for output

-- Update the time when the file was loaded.
UPDATE dbo.dbaTrace
    SET FileLoadedDT = getdate()
    WHERE @dbaTraceID = dbaTraceID

SELECT @myError = @@Error, @myRowcount = @@Rowcount
IF @myError <> 0 BEGIN
    ROLLBACK TRANSACTION
    RETURN @myError   
END

COMMIT TRANSACTION 
SELECT @myError = @@Error

RETURN @myError -- Should be zero, unless there was an error on COMMIT

Unless I am really interested in seeing what's in the trace, I run this in a separate job that loads all the available files. It is scheduled to run in the early morning hours when system activity is low.

Conclusion

By using SQL Profiler's ability to create the T-SQL script for a trace, this article has shown how easy it is to create a stored procedure that can be run every day during times of peak activity. That is the best time to find out where SQL Server is using the most resources so any performance problems can be addressed. By shifting the effort of loading the data to an off peak time, the overhead of monitoring performance is moved out of peak hours.

Of course, what you have seen here is the process of gathering the performance data. Once you have it, there are various ways to analyze it. I usually run a variety of reports that show the longest running statements, the table scans, or any deadlocks that have occurred.

» See All Articles by Columnist Andrew Novick