SQL Profiler trace file analysis

To read a profiler trace into a table

SELECT * INTO #trc
FROM fn_trace_gettable(‘c:\temp\trace_file_name.trc’, default)
Top time-consuming stored procedures in the trace
SELECT objectname,
SUM(Datediff(SECOND, starttime, endtime)) total_duration_seconds,
COUNT(*)  runcount,
MAX(Datediff(SECOND, starttime, endtime)) maxruntime_seconds
FROM #trc
WHERE eventclass = 43
GROUP BY objectname
ORDER BY SUM(Datediff(SECOND, starttime, endtime)) DESC

Top time-consuming SQL code in the trace

SELECT Substring(textdata, 1, 1000) ,
objectname,
SUM(Datediff(SECOND, starttime, endtime)) total_duration_seconds,
COUNT(*)  runcount,
MAX(Datediff(SECOND, starttime, endtime)) maxruntime_seconds
FROM #trc
WHERE eventclass = 45
AND Substring(textdata, 1, 4) <> ‘exec’
GROUP BY Substring(textdata, 1, 1000),
objectname
ORDER BY SUM(Datediff(SECOND, starttime, endtime)) DESC
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s