-- 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 
*/
This page was last updated on May 01, 2006 04:28 PM.