Working with profiler

Question

Working with profiler

New_to_being_DBA

SSCrazy

Points: 2064
More actions
October 25, 2005 at 10:45 am

#111972

I have a few questions about profiler. I just set up a trace for 10 minutes and grouped by duration >10000. I saved the trace to a table and now trying to query the table. How can I read it? My event classes have number in the actual table. How can I tell what event corresponds to what event class number? If I open the actual trace file the event class is a word like: RPC:started but in the table it's a number.
Also, how can I tell which events are causing a problem. Is it the ones with the highest number in the CPU column?
The textdata field in the table is only allowing a certain amount of data which is cutting off my query. How can I see what the whole query is and if it's causing a problem? Is there a way to put the pieces of the query together? So confused.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

The documentation on the EventClass numbers to descriptions is well hidden in Books OnLine and at http://www.microsoft.com. Below is the SQL to create a table with all of the EventClasses and then populate the table with data. You can then just join to the trace table on EventClass. I have also added the column EventClassName to allow grouping of events such as "account" which is assigned to all events that create,modify or delete logins.

I also have similar tables for these trace columns:

EventSubClass, ObjectType, ColumnPermissions and Permissions.

HTH

CREATE TABLE [dbo].[TraceEventClass] (

[EventClass] [int] NOT NULL ,

[EventClassName] [varchar] (255) NOT NULL ,

[EventName] [varchar] (255) NOT NULL ,

[EventDescription] [varchar] (4000) NOT NULL ,

CONSTRAINT [TraceEventClass_P] PRIMARY KEY CLUSTERED

([EventClass]))

GO

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(10,'DML','RPC:Completed','Occurs when a remote procedure call (RPC) has completed.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(11,'DML','RPC:Starting','Occurs when an RPC has started.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(12,'DML','SQL:BatchCompleted','Occurs when a Transact-SQL batch has completed.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(13,'DML','SQL:BatchStarting','Occurs when a Transact-SQL batch has started.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(14,'Connect','Login','Occurs when a user successfully logs in to SQL Server.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(15,'Connect','Logout','Occurs when a user logs out of SQL Server.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(16,'Error','Attention','Occurs when attention events, such as client-interrupt requests or broken client connections, happen.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(17,'Connect','ExistingConnection','Detects all activity by users connected to SQL Server before the trace started.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(18,'DBA','ServiceControl','Occurs when the SQL Server service state is modified.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(19,'ExecPlan','DTCTransaction','Tracks Microsoft Distributed Transaction Coordinator (MS DTC) coordinated transactions between two or more databases.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(20,'Connect','Login Failed','Indicates that a login attempt to SQL Server from a client failed.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(21,'Error','EventLog','Indicates that events have been logged in the Microsoft Windows NT® application log.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(22,'Error','ErrorLog','Indicates that error events have been logged in the SQL Server error log.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(23,'Lock','Lock:Released','Indicates that a lock on a resource, such as a page, has been released.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(24,'Lock','Lock:Acquired','Indicates acquisition of a lock on a resource, such as a data page.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(25,'Lock','Lockeadlock','Indicates that two concurrent transactions have deadlocked each other by trying to obtain incompatible locks on resources the other transaction owns.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(26,'Lock','Lock:Cancel','Indicates that the acquisition of a lock on a resource has been canceled (for example, due to a deadlock).')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(27,'Lock','Lock:Timeout','Indicates that a request for a lock on a resource, such as a page, has timed out due to another transaction holding a blocking lock on the required resource.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(28,'ExecPlan','DOP Event','Occurs before a SELECT, INSERT, or UPDATE statement is executed.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(33,'Error','Exception','Indicates that an exception has occurred in SQL Server.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(34,'Cache','SP:CacheMiss','Indicates when a stored procedure is not found in the procedure cache.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(35,'Cache','SP:CacheInsert','Indicates when an item is inserted into the procedure cache.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(36,'Cache','SP:CacheRemove','Indicates when an item is removed from the procedure cache.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(37,'Cache','SP:Recompile','Indicates that a stored procedure was recompiled.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(38,'Cache','SP:CacheHit','Indicates when a stored procedure is found in the procedure cache.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(39,'Cache','SP:ExecContextHit','Indicates when the execution version of a stored procedure has been found in the procedure cache.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(40,'DML','SQL:StmtStarting','Occurs when the Transact-SQL statement has started.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(41,'DML','SQL:StmtCompleted','Occurs when the Transact-SQL statement has completed.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(42,'DML','SP:Starting','Indicates when the stored procedure has started.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(43,'DML','SP:Completed','Indicates when the stored procedure has completed.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(46,'DDL','Object:Created','Indicates that an object has been created, such as for CREATE INDEX, CREATE TABLE, and CREATE DATABASE statements.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(47,'DDL','Objecteleted','Indicates that an object has been deleted, such as in DROP INDEX and DROP TABLE statements.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(50,'DDL','SQL Transaction','Tracks Transact-SQL BEGIN, COMMIT, SAVE, and ROLLBACK TRANSACTION statements.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(51,'ExecPlan','Scan:Started','Indicates when a table or index scan has started.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(52,'ExecPlan','Scan:Stopped','Indicates when a table or index scan has stopped.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(53,'ExecPlan','CursorOpen','Indicates when a cursor is opened on a Transact-SQL statement by ODBC, OLE DB, or DB-Library.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(54,'Files','Transaction Log','Tracks when transactions are written to the transaction log.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(55,'ExecPlan','Hash Warning','Indicates that a hashing operation (for example, hash join, hash aggregate, hash union, and hash distinct) that is not processing on a buffer partition has reverted to an alternate plan.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(58,'ExecPlan','Auto Update Stats','Indicates an automatic updating of index statistics has occurred.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(59,'Lock','Lockeadlock Chain','Produced for each of the events leading up to the deadlock.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(60,'Lock','Lock:Escalation','Indicates that a finer-grained lock has been converted to a coarser-grained lock (for example, a row lock escalated or converted to a page lock).')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(61,'Error','OLE DB Errors','Indicates that an OLE DB error has occurred.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(67,'DML','Execution Warnings','Indicates any warnings that occurred during the execution of a SQL Server statement or stored procedure.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(68,'ExecPlan','Execution Plan','Displays the plan tree of the Transact-SQL statement executed.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(69,'ExecPlan','Sort Warnings','Indicates sort operations that do not fit into memory. Does not include sort operations involving the creating of indexes; only sort operations within a query (such as an ORDER BY clause used in a SELECT statement).')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(70,'DML','CursorPrepare','Indicates when a cursor on a Transact-SQL statement is prepared for use by ODBC, OLE DB, or DB-Library.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(71,'DML','Prepare SQL','ODBC, OLE DB, or DB-Library has prepared a Transact-SQL statement or statements for use.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(72,'DML','Exec Prepared SQL','ODBC, OLE DB, or DB-Library has executed a prepared Transact-SQL statement or statements.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(73,'DML','Unprepare SQL','ODBC, OLE DB, or DB-Library has unprepared (deleted) a prepared Transact-SQL statement or statements.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(74,'DML','CursorExecute','A cursor previously prepared on a Transact-SQL statement by ODBC, OLE DB, or DB-Library is executed.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(75,'ExecPlan','CursorRecompile','A cursor opened on a Transact-SQL statement by ODBC or DB-Library has been recompiled either directly or due to a schema change.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(76,'DML','CursorImplicitConversion','A cursor on a Transact-SQL statement is converted by SQL Server from one type to another.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(77,'DML','CursorUnprepare','A prepared cursor on a Transact-SQL statement is unprepared (deleted) by ODBC, OLE DB, or DB-Library.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(78,'DML','CursorClose','A cursor previously opened on a Transact-SQL statement by ODBC, OLE DB, or DB-Library is closed.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(79,'ExecPlan','Missing Column Statistics','Column statistics that could have been useful for the optimizer are not available.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(80,'ExecPlan','Missing Join Predicate','Query that has no join predicate is being executed. This could result in a long-running query.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(81,'System','Server Memory Change','Microsoft SQL Server memory usage has increased or decreased by either 1 megabyte (MB) or 5 percent of the maximum server memory, whichever is greater.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(92,'Files','Data File Auto Grow','Indicates that a data file was extended automatically by the server.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(93,'Files','Log File Auto Grow','Indicates that a data file was extended automatically by the server.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(94,'Files','Data File Auto Shrink','Indicates that a data file was shrunk automatically by the server.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(95,'Files','Log File Auto Shrink','Indicates that a log file was shrunk automatically by the server.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(96,'ExecPlan','Show Plan Text','Displays the query plan tree of the SQL statement from the query optimizer.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(97,'ExecPlan','Show Plan ALL','Displays the query plan with full compile-time details of the SQL statement executed.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(98,'ExecPlan','Show Plan Statistics','Displays the query plan with full run-time details of the SQL statement executed.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(100,'DML','RPC Output Parameter','Produces output values of the parameters for every RPC.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(102,'Permission','Audit Statement GDR','Occurs every time a GRANT, DENY, REVOKE for a statement permission is issued by any user in SQL Server.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(103,'Permission','Audit Object GDR','Occurs every time a GRANT, DENY, REVOKE for an object permission is issued by any user in SQL Server.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(104,'Account','Audit Add/Drop Login','Occurs when a SQL Server login is added or removed; for sp_addlogin and sp_droplogin.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(105,'Account','Audit Login GDR','Occurs when a Microsoft Windows® login right is added or removed; for sp_grantlogin, sp_revokelogin, and sp_denylogin.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(106,'Account','Audit Login Change Property','Occurs when a property of a login, except passwords, is modified; for sp_defaultdb and sp_defaultlanguage.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(107,'Account','Audit Login Change Password','Occurs when a SQL Server login password is changed.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(108,'Account','Audit Add Login to Server Role','Occurs when a login is added or removed from a fixed server role; for sp_addsrvrolemember, and sp_dropsrvrolemember.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(109,'Account','Audit Add DB User','Occurs when a login is added or removed as a database user (Windows or SQL Server) to a database; for sp_grantdbaccess, sp_revokedbaccess, sp_adduser, and sp_dropuser.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(110,'Account','Audit Add Member to DB','Occurs when a login is added or removed as a database user (fixed or user-defined) to a database; for sp_addrolemember, sp_droprolemember, and sp_changegroup.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(111,'Account','Audit Add/Drop Role','Occurs when a login is added or removed as a database user to a database; for sp_addrole and sp_droprole.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(112,'Account','App Role Pass Change','Occurs when a password of an application role is changed.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(113,'Audit','Audit Statement Permission','Occurs when a statement permission (such as CREATE TABLE) is used.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(114,'Audit','Audit Object Permission','Occurs when an object permission (such as SELECT) is used, both successfully or unsuccessfully.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(115,'DBA','Audit Backup/Restore','Occurs when a BACKUP or RESTORE command is issued.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(116,'DBA','Audit DBCC','Occurs when DBCC commands are issued.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(117,'Audit','Audit Change Audit','Occurs when audit trace modifications are made.')

INSERT INTO [TraceEventClass] ([EventClass],[EventClassName],[EventName],[EventDescription])VALUES(118,'DDL','Audit Object Derived Permission','Occurs when a CREATE, ALTER, and DROP object commands are issued.')

SQL = Scarcely Qualifies as a Language

New_to_being_DBA SSCrazy Points: 2064 More actions · Answer 2

Thank you so much! THis helps a great deal. I found info about everything on profiler but this. So you are right. It is VERY hidden.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 3

For working the the trace, below are SQL statements to summarize Stored Procedure Executions and Select statement. You will need to change the trace table name as needed.

I usually look at Reads and Writes. Since duration is usually very dependent on the network load, I usually totally ignore this statistic. If the top SQL for Reads and Writes doesn't match the top SQL for CPU, I would take a look.

Summarize Stored Procedure Execution:

select substring(TextData,6,charindex(' ', textdata, charindex(' ', textdata) + 1 ) - 5 )

, count(*)

, SUM(Duration)

, SUM(READS)

, SUM(Writes)

, SUM(CPU)

, AVG(Duration)

, AVG(READS)

, AVG(Writes)

, AVG(CPU)

, MAX(Duration)

, MAX(READS)

, MAX(Writes)

, MAX(CPU)

from sqltrace

where sqltrace.EventClass = 10

and TextData like 'exec %'

and charindex(' ', textdata, charindex(' ', textdata) + 1 ) > 0

group by substring(TextData,6,charindex(' ', textdata, charindex(' ', textdata) + 1 ) - 5 )

order by SUM(READS)

Summarize Selects excluding the WHERE clause.

select top 30

SQL1

, Count(*)

, Avg(Reads)

, Avg(CPU)

, SUM(Reads)

, SUM(CPU)

FROM (select

SUBSTRING(SQLText

, charindex( 'select' , SQLText )

, charindex('where' , SQLText ) - charindex( 'select' , SQLText ) )

as SQL1

, Duration, Reads, CPU

from (select cast(TextData as varchar(8000) ) as SQLText

, Duration, Reads, CPU

from dbo.sqltrace

where TextData like '%select%where%'

) as X

) as Y

group by SQL1

order by SUM(Reads) desc

SQL = Scarcely Qualifies as a Language