Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2005 Profiler Events Expand / Collapse
Author
Message
Posted Friday, October 3, 2008 12:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 11,148, Visits: 12,889
Comments posted to this topic are about the item SQL Server 2005 Profiler Events



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #580078
Posted Friday, October 3, 2008 5:21 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 1,434, Visits: 721
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
Post #580186
Posted Friday, October 3, 2008 5:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 11,148, Visits: 12,889
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #580204
Posted Friday, October 3, 2008 5:55 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 1,434, Visits: 721
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! :D
Post #580205
Posted Tuesday, October 7, 2008 2:06 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,299, Visits: 1,378
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.....:)



Post #581577
Posted Tuesday, October 7, 2008 10:01 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:24 PM
Points: 3,475, Visits: 579
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 Varshal

Post #581953
Posted Tuesday, October 7, 2008 10:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 11,148, Visits: 12,889
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #581987
Posted Tuesday, December 7, 2010 1:24 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 5:53 PM
Points: 8,543, Visits: 9,030
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
Post #1031487
Posted Tuesday, December 7, 2010 2:07 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 11,148, Visits: 12,889
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1031516
Posted Tuesday, December 7, 2010 8:35 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 5:53 PM
Points: 8,543, Visits: 9,030
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
Post #1031621
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse