SQL Server 2005 Profiler Events

Question

Post reply

SQL Server 2005 Profiler Events

Jack Corbett

SSC Guru

Points: 184394
More actions
October 3, 2008 at 12:28 am

#191726

Comments posted to this topic are about the item SQL Server 2005 Profiler Events
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SQLPhil SSCarpal Tunnel Points: 4204 More actions · Answer 1

Here's a reference to all the available event classes:

http://msdn.microsoft.com/en-us/library/ms175481(SQL.90).aspx

This is for SQL 2005

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Thanks for the link Phil, but you still have to dig down to the individual event classes and count the events in each class to get a total number of events produced.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SQLPhil SSCarpal Tunnel Points: 4204 More actions · Answer 3

Granted your query is the easier way of finding out the answer to the question. I don't have that many fingers and toes to count all of the event classes listed in BOL! 😀

Anipaul SSC-Insane Points: 24681 More actions · Answer 4

Phil Melling (10/3/2008)
Here's a reference to all the available event classes:
http://msdn.microsoft.com/en-us/library/ms175481(SQL.90).aspx
This is for SQL 2005

Nice link.....:)

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

I normally use

http://msdn.microsoft.com/en-us/library/ms186265(SQL.90).aspx

sp_trace_setevent (Transact-SQL)

that lists events with the last event number 202 with 10 of them reserved, total number 171. The question was how many of the events are available using Profiler, so I was not sure if Profiler as a user interface lists all evants available for the traces. Since the question explanation is to query sys.trace_events then I think the trace events should be counted

Regards,Yelena Varsha

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Yelena Varshal (10/7/2008)
Since the question explanation is to query sys.trace_events then I think the trace events should be counted

Yelena,

Thanks for the link.

I'm not sure what you mean when you say you think the trace events should be counted. Are you saying that the answer should be 202?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

TomThomson SSC Guru Points: 104773 More actions · Answer 7

Interesting question.

I knew the number was pretty big, so guessed and got it right(? assuming 171 is actually right ?).

How does the list of event classes referenced by Phil relate? Assuming that the average number of events per class there is more than 1 there must be a few more than 171 events.

The page Yelena referenced has a list of 203 event ids (numbered 0 to 202) of which 30 are reserved (if I counted correctly) making 173 (non-reserved) events? How do these relate to Jack's 171 events?

Tom

Jack Corbett SSC Guru Points: 184394 More actions · Answer 8

Tom,

Like most things MS, there are some discrepancies in the documentation which is why I went by a query. In the article listed by Phil there are some events missing just in the first Category, Service Broker. Here's how I look at the list:

SELECT

TC.name AS category,

TE.name AS event_name

FROM

sys.trace_events AS TE JOIN

sys.trace_categories AS TC

ON TE.category_id = TC.category_id

ORDER BY

TC.name,

TE.name

Then for Yelena's list, the reserved event id's do not even exist in the sys.trace_events DMV so I didn't include reserved events. There are 32 reserved event id's I didn't count, I ran this query:

;WITH cteNumbers

AS (

SELECT

0 AS N

UNION ALL

SELECT TOP 200

ROW_NUMBER() OVER (ORDER BY C.column_id) AS N

FROM

sys.COLUMNS AS C

)

SELECT

N

FROM

cteNumbers LEFT JOIN

sys.trace_events AS TE

ON cteNumbers.N = TE.trace_event_id

WHERE

TE.trace_event_id IS NULL

Which shows just which ones aren't included in sys.trace_events. There are more in 2008 and I'm sure even more in Denali.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

TomThomson SSC Guru Points: 104773 More actions · Answer 9

Jack Corbett (12/7/2010)
Like most things MS, there are some discrepancies in the documentation which is why I went by a query.

Jack, I suspect that the only sensible way to get a number is as you did it, by a query. MS documentation is sometimes (often?) a bit adrift, or missing detail.

I guess you counted the "reserved" more acurately than I did on Yelena's list, so it gives 171 when those are discounted. Still not sure how Phil's list fits, maybe I should look again.

Thanks for responding to a comment so long after the question was posed - I'm drifting back gradually through old qotd from before I discovered sqlservercentral, and it's pretty rare to see any response to comments on old stuff.

Tom