Binaryworld - Analyze Trace File data using T-SQL. ... [ SQL Server -> Performance ]

Demonstrates the new system function fn_trace_gettable to read a capture trace file & analyze it using SQL.

-- This sample script shows how to analyze a capture trace file using the new Trace functions
-- For more help on the trace function, see BOL.
DECLARE @TraceCaptureFile nvarchar( 512 );
SET @TraceCaptureFile = 'C:\Documents and Settings\Umachandar Jayachand\' +
            'My Documents\SQL Server Workspace\webpub capture.trc'

-- Get calls made by the SQL Web Assistant only & filter out any calls made to FREEPROCACHE
SELECT t.EventClass , LEFT( CAST( t.Textdata AS varchar( 8000 ) ) , 30 ) AS SQLStmtStart ,
    LEFT( t.NTUserName , 25 ) AS NTUserName , t.Duration , t.CPU , t.Reads , t.Writes
FROM ::fn_trace_gettable ( @TraceCaptureFile , DEFAULT ) AS t
WHERE t.ApplicationName = 'xpweb70.dll';

/*
EventClass SQLStmtStart          NTUserName      Duration CPU Reads  Writes
----------- ------------------------------ -------------------- -------- ----- ------- ------
     10 sp_bindsession 'fQM---1---/_YI Umachandar Jayachand    30   0    9   0
     12 CREATE PROCEDURE [web_2000083 Umachandar Jayachand   110   10   177   7
     12 EXECUTE [web_2000083000495893 Umachandar Jayachand   410  121   1067   0
     12 DROP PROCEDURE [web_200008300 Umachandar Jayachand   160   0    51   0
     10 sp_bindsession 'XPM---1----^J4 Umachandar Jayachand    0   0    0   0
     12 EXECUTE [web_2000083000501491 Umachandar Jayachand   1123  250   2352   0
     12 DROP PROCEDURE [web_200008300 Umachandar Jayachand    60   10    46   1
*/

-- Find out all calls with more than 1000 reads + 10 cpu time. Exclude any DBCC FREEPROCCACHE statements
SELECT t.EventClass , LEFT( CAST( t.Textdata AS varchar( 8000 ) ) , 30 ) AS SQLStmtStart ,
    LEFT( t.NTUserName , 25 ) AS NTUserName , t.Duration , t.CPU , t.Reads , t.Writes
FROM ::fn_trace_gettable( @TraceCaptureFile , DEFAULT ) AS t
WHERE CAST( t.textdata AS nvarchar( 255 ) ) NOT LIKE N'DBCC FREEPROCCACHE%' And
   t.cpu > 10 and t.reads > 1000;
/*
EventClass SQLStmtStart          NTUserName      Duration CPU     Reads  Writes
----------- ------------------------------ -------------------- ---------- ----------- -------- ------
     17 NULL              Umachandar Jayachand 10536090    9102 179797  264
     15 NULL              Umachandar Jayachand 11147206    14752 186223  274
     12 exec CheckFilesForHTMLGenerati Umachandar Jayachand    8753    1372  12066  104
     12 exec CopyContentsFromFile i  Umachandar Jayachand    6460     401   1746   17
     17 NULL              Umachandar Jayachand 99039303     80  32963   84
     10 sp_MShelpcolumns N'dbo.vwResou Umachandar Jayachand    680     361   2155   5
     12 EXECUTE [web_2000083000495893 Umachandar Jayachand    410     121   1067   0
     15 NULL              Umachandar Jayachand    1754     310   2632   7
     15 NULL              Umachandar Jayachand 1549676     5089   5447   0
*/