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