October 15, 2012 at 11:27 am
Hi,
I have enabled trace on my server running sql server 2005 with template "Tunning" with all default options.
Now I have data in a table, but don't know how to read that data to extract facts.
For example a query executed 10 times, how can I find that query ?
or from all the trace data I want to extract what are top (query executed most) query?
Is there some column where I can use group by to extract multiple existence of query?
October 15, 2012 at 12:11 pm
it's fairly easy.
first, doa SELECT * FROM sys.traces; you'll probably see traceid = 1(the default trace) plus any other traces you have created (2,3,4 etc)
select the trace id of the trace you want to review, and swap it out in this query:
with that in place, you can start adding a WHERE statement to limit the results to just what you need.
declare @TraceIDToReview int
declare @path varchar(255)
SET @TraceIDToReview = 1 --this is the trace you want to review!
SELECT @path = path from sys.traces WHERE id = @TraceIDToReview
SELECT
TE.name As EventClassDescrip,
v.subclass_name As EventSubClassDescrip,
T.*
FROM ::fn_trace_gettable(@path, default) T
LEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
LEFT OUTER JOIN sys.trace_subclass_values V
ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value
Lowell
October 15, 2012 at 12:34 pm
Lowell (10/15/2012)
it's fairly easy.first, doa SELECT * FROM sys.traces; you'll probably see traceid = 1(the default trace) plus any other traces you have created (2,3,4 etc)
select the trace id of the trace you want to review, and swap it out in this query:
with that in place, you can start adding a WHERE statement to limit the results to just what you need.
declare @TraceIDToReview int
declare @path varchar(255)
SET @TraceIDToReview = 1 --this is the trace you want to review!
SELECT @path = path from sys.traces WHERE id = @TraceIDToReview
SELECT
TE.name As EventClassDescrip,
v.subclass_name As EventSubClassDescrip,
T.*
FROM ::fn_trace_gettable(@path, default) T
LEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
LEFT OUTER JOIN sys.trace_subclass_values V
ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value
thanks Lowell, I have trace table as I get all the data in table.
I want to know how can I utilize that data? like from trace table data what query executed most ?
October 15, 2012 at 12:40 pm
well it's a table of data now, so you can do your normal queries with group by and stuff like that.
here's a crappy group by example i just tested: like i said, you'll want to customize the WHERE statement to match whetever you are analyzing.
declare @TraceIDToReview int
declare @path varchar(255)
SET @TraceIDToReview = 1 --this is the trace you want to review!
SELECT @path = path from sys.traces WHERE id = @TraceIDToReview
SELECT
TE.name As EventClassDescrip,
v.subclass_name As EventSubClassDescrip,
CONVERT(varchar(max),TextData) AS TextData,
COUNT(1) As Occurrences
FROM ::fn_trace_gettable(@path, default) T
LEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
LEFT OUTER JOIN sys.trace_subclass_values V
ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value
WHERE TextData IS NOT NULL
GROUP BY
TE.name,
v.subclass_name,
CONVERT(varchar(max),TextData)
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply