Performance Statistics Event Class

Performance Statistics Event Class

 

Applies To: SQL Server 2016

The Performance Statistics event class can be used to monitor the performance of queries, stored procedures, and triggers that are executing. Each of the six event subclasses indicates an event in the lifetime of queries, stored procedures, and triggers within the system. Using the combination of these event subclasses and the associated sys.dm_exec_query_stats, sys.dm_exec_procedure_stats and sys.dm_exec_trigger_stats dynamic management views, you can reconstitute the performance history of any given query, stored procedure, or trigger.

The following tables describe the event class data columns associated with each of the following event subclasses: EventSubClass 0, EventSubClass 1,EventSubClass 2,EventSubClass 3, EventSubClass 4, and EventSubClass 5.

EventSubClass 0

Data column nameData typeDescriptionColumn IDFilterable
BigintData1bigintNULL52Yes
BinaryDataimageNULL2Yes
DatabaseIDintID of the database specified by the USE database statement or the default database if no USE database statement has been issued for a given instance. SQL Server Profiler displays the name of the database if the ServerName data column is captured in the trace and the server is available. Determine the value for a database by using the DB_ID function.3Yes
EventSequenceintSequence of a given event within the request.51No
EventSubClassintType of event subclass.

0 = New batch SQL text that is not currently present in the cache.

The following EventSubClass types are generated in the trace for ad hoc batches.

For ad hoc batches with n number of queries:

1 of type 0
21Yes
IntegerData2intNULL55Yes
ObjectIDintNULL22Yes
OffsetintNULL61Yes
PlanHandleImageNULL65Yes
SessionLoginNamenvarcharLogin name of the user who originated the session. For example, if you connect to SQL Server using Login1 and execute a statement as Login2, SessionLoginName shows Login1 and LoginName shows Login2. This column displays both SQL Server and Windows logins.64Yes
SPIDintID of the session on which the event occurred.12Yes
SqlHandleimageSQL handle that can be used to obtain the batch SQL text using the sys.dm_exec_sql_text dynamic management view.63Yes
StartTimedatetimeTime at which the event started, if available.14Yes
TextDatantextSQL text of the batch.1Yes

EventSubClass 1

Data column nameData typeDescriptionColumn IDFilterable
BigintData1bigintThe cumulative number of times this plan has been recompiled.52Yes
BinaryDataimageThe binary XML of the compiled plan.2Yes
DatabaseIDintID of the database specified by the USE database statement or the default database if no USE database statement has been issued for a given instance. SQL Server Profiler displays the name of the database if the ServerName data column is captured in the trace and the server is available. Determine the value for a database by using the DB_ID function.3Yes
EventSequenceintSequence of a given event within the request.51No
SessionLoginNamenvarcharLogin name of the user who originated the session. For example, if you connect to SQL Server using Login1 and execute a statement as Login2, SessionLoginName shows Login1 and LoginName shows Login2. This column displays both SQL Server and Windows logins.64Yes
EventSubClassintType of event subclass.

1 = Queries within a stored procedure have been compiled.

The following EventSubClass types are generated in the trace for stored procedures.

For stored procedures with n number of queries:

 n number of type 1
21Yes
IntegerData2intEnd of the statement within the stored procedure.

-1 for the end of the stored procedure.
55Yes
ObjectIDintSystem-assigned ID of the object.22Yes
OffsetintStarting offset of the statement within the stored procedure or batch.61Yes
SPIDintID of the session on which the event occurred.12Yes
SqlHandleimageSQL handle that can be used to obtain the SQL text of the stored procedure using the dm_exec_sql_text dynamic management view.63Yes
StartTimedatetimeTime at which the event started, if available.14Yes
TextDatantextNULL1Yes
PlanHandleimageThe plan handle of the compiled plan for the stored procedure. This can be used to obtain the XML plan by using the sys.dm_exec_query_plan dynamic management view.65Yes
ObjectTypeintA value that represents the type of object involved in the event.

8272 = stored procedure
28Yes
BigintData2bigintTotal memory, in kilobytes, used during compilation.53Yes
CPUintTotal CPU time, in milliseconds, spent during compilation.18Yes
DurationintTotal time, in microseconds, spent during compilation.13Yes
IntegerDataintThe size, in kilobytes, of the compiled plan.25Yes

EventSubClass 2

Data column nameData typeDescriptionColumn IDFilterable
BigintData1bigintThe cumulative number of times this plan has been recompiled.52Yes
BinaryDataimageThe binary XML of the compiled plan.2Yes
DatabaseIDintID of the database specified by the USE database statement or the default database if no USE database statement has been issued for a given instance. SQL Server Profiler displays the name of the database if the ServerName data column is captured in the trace and the server is available. Determine the value for a database by using the DB_ID function.3Yes
EventSequenceintSequence of a given event within the request.51No
SessionLoginNamenvarcharLogin name of the user who originated the session. For example, if you connect to SQL Server using Login1 and execute a statement as Login2, SessionLoginName shows Login1 and LoginName shows Login2. This column displays both SQL Server and Windows logins.64Yes
EventSubClassintType of event subclass.

2 = Queries within an ad hoc SQL statement have been compiled.

The following EventSubClass types are generated in the trace for ad hoc batches.

For ad hoc batches with n number of queries:

 n number of type 2
21Yes
IntegerData2intEnd of the statement within the batch.

-1 for the end of the batch.
55Yes
ObjectIDintN/A22Yes
OffsetintStarting offset of the statement within the batch.

0 for the beginning of the batch.
61Yes
SPIDintID of the session on which the event occurred.12Yes
SqlHandleimageSQL handle. This can be used to obtain the batch SQL text using the dm_exec_sql_text dynamic management view.63Yes
StartTimedatetimeTime at which the event started, if available.14Yes
TextDatantextNULL1Yes
PlanHandleimageThe plan handle of the compiled plan for the batch. This can be used to obtain the batch XML plan using the dm_exec_query_plan dynamic management view.65Yes
BigintData2bigintTotal memory, in kilobytes, used during compilation.53Yes
CPUintTotal CPU time, in microseconds, spent during compilation.18Yes
DurationintTotal time, in milliseconds, spent during compilation.13Yes
IntegerDataintThe size, in kilobytes, of the compiled plan.25Yes

EventSubClass 3

Data column nameData typeDescriptionColumn IDFilterable
BigintData1bigintThe cumulative number of times this plan has been recompiled.52Yes
BinaryDataimageNULL2Yes
DatabaseIDintID of the database specified by the USE database statement or the default database if no USE database statement has been issued for a given instance. SQL Server Profiler displays the name of the database if the ServerName data column is captured in the trace and the server is available. Determine the value for a database by using the DB_ID function.3Yes
EventSequenceintSequence of a given event within the request.51No
SessionLoginNamenvarcharLogin name of the user who originated the session. For example, if you connect to SQL Server using Login1 and execute a statement as Login2, SessionLoginName shows Login1 and LoginName shows Login2. This column displays both SQL Server and Windows logins.64Yes
EventSubClassintType of event subclass.

3 = A cached query has been destroyed and the historical performance data associated with the plan is about to be destroyed.

The following EventSubClass types are generated in the trace.

For ad hoc batches with n number of queries:

1 of type 3 when the query is flushed from the cache

For stored procedures with n number of queries:

1 of type 3 when the query is flushed from the cache.
21Yes
IntegerData2intEnd of the statement within the stored procedure or batch.

-1 for the end of the stored procedure or batch.
55Yes
ObjectIDintNULL22Yes
OffsetintStarting offset of the statement within the stored procedure or batch.

0 for the beginning of the stored procedure or batch.
61Yes
SPIDintID of the session on which the event occurred.12Yes
SqlHandleimageSQL handle that can be used to obtain the stored procedure or batch SQL text using the dm_exec_sql_text dynamic management view.63Yes
StartTimedatetimeTime at which the event started, if available.14Yes
TextDatantextQueryExecutionStats1Yes
PlanHandleimageThe plan handle of the compiled plan for the stored procedure or batch. This can be used to obtain the XML plan using the dm_exec_query_plan dynamic management view.65Yes
GroupIDintID of the workload group where the SQL Trace event fires.66Yes

EventSubClass 4

Data column nameData typeDescriptionColumn IDFilterable
BigintData1bigintNULL52Yes
BinaryDataimageNULL2Yes
DatabaseIDintID of the database in which the given stored procedure resides.3Yes
EventSequenceintSequence of a given event within the request.51No
SessionLoginNamenvarcharLogin name of the user who originated the session. For example, if you connect to SQL Server using Login1 and execute a statement as Login2, SessionLoginName shows Login1 and LoginName shows Login2. This column displays both SQL Server and Windows logins.64Yes
EventSubClassintType of event subclass.

4 = A cached stored procedure has been removed from the cache and the historical performance data associated with it is about to be destroyed.
21Yes
IntegerData2intNULL55Yes
ObjectIDintID of the stored procedure. This is same as the object_id column in sys.procedures.22Yes
OffsetintNULL61Yes
SPIDintID of the session on which the event occurred.12Yes
SqlHandleimageSQL handle that can be used to obtain the stored procedure SQL text that was executed using the dm_exec_sql_text dynamic management view.63Yes
StartTimedatetimeTime at which the event started, if available.14Yes
TextDatantextProcedureExecutionStats1Yes
PlanHandleimageThe plan handle of the compiled plan for the stored procedure. This can be used to obtain the XML plan using the dm_exec_query_plan dynamic management view.65Yes
GroupIDintID of the workload group where the SQL Trace event fires.66Yes

EventSubClass 5

Data column nameData typeDescriptionColumn IDFilterable
BigintData1bigintNULL52Yes
BinaryDataimageNULL2Yes
DatabaseIDintID of the database in which the given trigger resides.3Yes
EventSequenceintSequence of a given event within the request.51No
SessionLoginNamenvarcharLogin name of the user who originated the session. For example, if you connect to SQL Server using Login1 and execute a statement as Login2, SessionLoginName shows Login1 and LoginName shows Login2. This column displays both SQL Server and Windows logins.64Yes
EventSubClassintType of event subclass.

5 = A cached trigger has been removed from the cache and the historical performance data associated with it is about to be destroyed.
21Yes
IntegerData2intNULL55Yes
ObjectIDintID of the trigger. This is same as the object_id column in sys.triggers/sys.server_triggers catalog views.22Yes
OffsetintNULL61Yes
SPIDintID of the session on which the event occurred.12Yes
SqlHandleimageSQL handle that can be used to obtain the trigger's SQL text using the dm_exec_sql_text dynamic management view.63Yes
StartTimedatetimeTime at which the event started, if available.14Yes
TextDatantextTriggerExecutionStats1Yes
PlanHandleimageThe plan handle of the compiled plan for the trigger. This can be used to obtain the XML plan using the dm_exec_query_plan dynamic management view.65Yes
GroupIDintID of the workload group where the SQL Trace event fires.66Yes

Extended Events
sp_trace_setevent (Transact-SQL)
Showplan XML for Query Compile Event Class
Dynamic Management Views and Functions (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft