3 Basic SQL 2000 Profiler Questions

1. Is it possible to capture only those events that are associated with a
particular table? When I try to filter on object name or id it appears to
have no effect.
2. I am trying to capture all stored procs calls without redundancy (e.g.,
so that the same call is not recorded twice). It apprears I can do this wit
h
just the SP:Completed event. But should I also use the RPC:Completed event?
In Query Analyzer SP:Completed works fine. But on the production box I thin
k
I probably need RPC:Completed. Can anyone give me an example that
distinguishes between these two events?
3. Is there a list of event codes and their descriptions (i.e., 43 =
spcompleted) stored soewhere in SQL Server?
Thanks
DaveHi Dave
1. Filtering on tables seems to be very problematic in SQL 2000 Profiler.
The best I usually can do is to filter on the table name in the text field
2. I'm not sure exactly what you mean by 'without redundancy'. When DO you
see the same call recorded twice?
3. No, this information is not stored in SQL Server 2000, but SQL 2005 does
have one of its new DMVs with this info. The mapping is in the
sp_trace_setevent documentation and you can take that info and build a
lookup table of your own, or, you can use this lookup table that I already
created:
USE master
GO
CREATE TABLE sp_EventID_Table (ID int, Description varchar(50) )
GO
SET NOCOUNT ON
GO
INSERT INTO sp_EventID_Table VALUES (0, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (1, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (2, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (3, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (4, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (5, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (6, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (7, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (8, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (9, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (10, 'RPC:Completed')
INSERT INTO sp_EventID_Table VALUES (11, 'RPC:Starting')
INSERT INTO sp_EventID_Table VALUES (12, 'SQL:BatchCompleted')
INSERT INTO sp_EventID_Table VALUES (13, 'SQL:BatchStarting')
INSERT INTO sp_EventID_Table VALUES (14, 'Login')
INSERT INTO sp_EventID_Table VALUES (15, 'Logout')
INSERT INTO sp_EventID_Table VALUES (16, 'Attention')
INSERT INTO sp_EventID_Table VALUES (17, 'ExistingConnection')
INSERT INTO sp_EventID_Table VALUES (18, 'ServiceControl')
INSERT INTO sp_EventID_Table VALUES (19, 'DTCTransaction')
INSERT INTO sp_EventID_Table VALUES (20, 'Login Failed')
INSERT INTO sp_EventID_Table VALUES (21, 'EventLog')
INSERT INTO sp_EventID_Table VALUES (22, 'ErrorLog')
INSERT INTO sp_EventID_Table VALUES (23, 'Lock:Released')
INSERT INTO sp_EventID_Table VALUES (24, 'Lock:Acquired')
INSERT INTO sp_EventID_Table VALUES (25, 'Lock:Deadlock')
INSERT INTO sp_EventID_Table VALUES (26, 'Lock:Cancel')
INSERT INTO sp_EventID_Table VALUES (27, 'Lock:Timeout')
INSERT INTO sp_EventID_Table VALUES (28, 'DOP Event')
INSERT INTO sp_EventID_Table VALUES (29, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (30, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (31, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (32, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (33, 'Exception')
INSERT INTO sp_EventID_Table VALUES (34, 'SP:CacheMiss')
INSERT INTO sp_EventID_Table VALUES (35, 'SP:CacheInsert')
INSERT INTO sp_EventID_Table VALUES (36, 'SP:CacheRemove')
INSERT INTO sp_EventID_Table VALUES (37, 'SP:Recompile')
INSERT INTO sp_EventID_Table VALUES (38, 'SP:CacheHit')
INSERT INTO sp_EventID_Table VALUES (39, 'SP:ExecContextHit')
INSERT INTO sp_EventID_Table VALUES (40, 'SQL:StmtStarting')
INSERT INTO sp_EventID_Table VALUES (41, 'SQL:StmtCompleted')
INSERT INTO sp_EventID_Table VALUES (42, 'SP:Starting')
INSERT INTO sp_EventID_Table VALUES (43, 'SP:Completed')
INSERT INTO sp_EventID_Table VALUES (44, 'SP:StmtStarting')
INSERT INTO sp_EventID_Table VALUES (45, 'SP:StmtCompleted')
INSERT INTO sp_EventID_Table VALUES (46, 'Object:Created')
INSERT INTO sp_EventID_Table VALUES (47, 'Object:Deleted')
INSERT INTO sp_EventID_Table VALUES (48, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (49, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (50, 'SQL Transaction')
INSERT INTO sp_EventID_Table VALUES (51, 'Scan:Started')
INSERT INTO sp_EventID_Table VALUES (52, 'Scan:Stopped')
INSERT INTO sp_EventID_Table VALUES (53, 'CursorOpen')
INSERT INTO sp_EventID_Table VALUES (54, 'Transaction Log')
INSERT INTO sp_EventID_Table VALUES (55, 'Hash Warning')
INSERT INTO sp_EventID_Table VALUES (56, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (57, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (58, 'Auto Update Stats')
INSERT INTO sp_EventID_Table VALUES (59, 'Lock:Deadlock Chain')
INSERT INTO sp_EventID_Table VALUES (60, 'Lock:Escalation')
INSERT INTO sp_EventID_Table VALUES (61, 'OLE DB Errors')
INSERT INTO sp_EventID_Table VALUES (62, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (63, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (64, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (65, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (66, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (67, 'Execution Warnings')
INSERT INTO sp_EventID_Table VALUES (68, 'Execution Plan')
INSERT INTO sp_EventID_Table VALUES (69, 'Sort Warnings')
INSERT INTO sp_EventID_Table VALUES (70, 'CursorPrepare')
INSERT INTO sp_EventID_Table VALUES (71, 'Prepare SQL')
INSERT INTO sp_EventID_Table VALUES (72, 'Exec Prepared SQL')
INSERT INTO sp_EventID_Table VALUES (73, 'Unprepare SQL')
INSERT INTO sp_EventID_Table VALUES (74, 'CursorExecute')
INSERT INTO sp_EventID_Table VALUES (75, 'CursorRecompile')
INSERT INTO sp_EventID_Table VALUES (76, 'CursorImplicitConversion')
INSERT INTO sp_EventID_Table VALUES (77, 'CursorUnprepare')
INSERT INTO sp_EventID_Table VALUES (78, 'CursorClose')
INSERT INTO sp_EventID_Table VALUES (79, 'Missing Column Statistics')
INSERT INTO sp_EventID_Table VALUES (80, 'Missing Join Predicate')
INSERT INTO sp_EventID_Table VALUES (81, 'Server Memory Change')
INSERT INTO sp_EventID_Table VALUES (82, 'User Configurable 0')
INSERT INTO sp_EventID_Table VALUES (83, 'User Configurable 1')
INSERT INTO sp_EventID_Table VALUES (84, 'User Configurable 2')
INSERT INTO sp_EventID_Table VALUES (85, 'User Configurable 3')
INSERT INTO sp_EventID_Table VALUES (86, 'User Configurable 4')
INSERT INTO sp_EventID_Table VALUES (87, 'User Configurable 5')
INSERT INTO sp_EventID_Table VALUES (88, 'User Configurable 6')
INSERT INTO sp_EventID_Table VALUES (89, 'User Configurable 7')
INSERT INTO sp_EventID_Table VALUES (90, 'User Configurable 8')
INSERT INTO sp_EventID_Table VALUES (91, 'User Configurable 9')
INSERT INTO sp_EventID_Table VALUES (92, 'Data File Auto Grow')
INSERT INTO sp_EventID_Table VALUES (93, 'Log File Auto Grow')
INSERT INTO sp_EventID_Table VALUES (94, 'Data File Auto Shrink')
INSERT INTO sp_EventID_Table VALUES (95, 'Log File Auto Shrink')
INSERT INTO sp_EventID_Table VALUES (96, 'Show Plan Text')
INSERT INTO sp_EventID_Table VALUES (97, 'Show Plan ALL')
INSERT INTO sp_EventID_Table VALUES (98, 'Show Plan Statistics')
INSERT INTO sp_EventID_Table VALUES (99, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (100, 'RPC Output Parameter')
INSERT INTO sp_EventID_Table VALUES (101, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (102, 'Audit Statement GDR')
INSERT INTO sp_EventID_Table VALUES (103, 'Audit Object GDR')
INSERT INTO sp_EventID_Table VALUES (104, 'Audit Add/Drop Login')
INSERT INTO sp_EventID_Table VALUES (105, 'Audit Login GDR')
INSERT INTO sp_EventID_Table VALUES (106, 'Audit Login Change Property')
INSERT INTO sp_EventID_Table VALUES (107, 'Audit Login Change Password')
INSERT INTO sp_EventID_Table VALUES (108, 'Audit Add Login to Server Role')
INSERT INTO sp_EventID_Table VALUES (109, 'Audit Add DB User')
INSERT INTO sp_EventID_Table VALUES (110, 'Audit Add Member to DB')
INSERT INTO sp_EventID_Table VALUES (111, 'Audit Add/Drop Role')
INSERT INTO sp_EventID_Table VALUES (112, 'App Role Pass Change')
INSERT INTO sp_EventID_Table VALUES (113, 'Audit Statement Permission')
INSERT INTO sp_EventID_Table VALUES (114, 'Audit Object Permission')
INSERT INTO sp_EventID_Table VALUES (115, 'Audit Backup/Restore')
INSERT INTO sp_EventID_Table VALUES (116, 'Audit DBCC')
INSERT INTO sp_EventID_Table VALUES (117, 'Audit Change Audit')
INSERT INTO sp_EventID_Table VALUES (118, 'Audit Object Derived
Permission')
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:3FFDDDC7-ADA7-4841-BFCE-03F8626B05D4@.microsoft.com...
> 1. Is it possible to capture only those events that are associated with a
> particular table? When I try to filter on object name or id it appears to
> have no effect.
> 2. I am trying to capture all stored procs calls without redundancy (e.g.,
> so that the same call is not recorded twice). It apprears I can do this
> with
> just the SP:Completed event. But should I also use the RPC:Completed
> event?
> In Query Analyzer SP:Completed works fine. But on the production box I
> think
> I probably need RPC:Completed. Can anyone give me an example that
> distinguishes between these two events?
> 3. Is there a list of event codes and their descriptions (i.e., 43 =
> spcompleted) stored soewhere in SQL Server?
> Thanks
> Dave
>|||Thank you very much Kalen.
One point of clarification. What type of stored proc calls will show up
under RPC:Completed vs SP:Completed? Query Analyzer calls appaer to be
priced up under SP:Completed. Would my middle tier apps be using
RPC:Completed to call?