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]
GOAs you can see 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. 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. ConclusionBy 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. |
