Performance Statistics Event Class
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.
Performance Statistics Event Class Data Columns
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 name | Data type | Description | Column ID | Filterable |
|---|---|---|---|---|
| BigintData1 | bigint |
NULL | 52 | Yes |
| BinaryData | image |
NULL | 2 | Yes |
| DatabaseID | int |
ID 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. | 3 | Yes |
| EventSequence | int |
Sequence of a given event within the request. | 51 | No |
| EventSubClass | int |
Type 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 |
21 | Yes |
| IntegerData2 | int |
NULL | 55 | Yes |
| ObjectID | int |
NULL | 22 | Yes |
| Offset | int |
NULL | 61 | Yes |
| PlanHandle | Image |
NULL | 65 | Yes |
| SessionLoginName | nvarchar |
Login 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. | 64 | Yes |
| SPID | int |
ID of the session on which the event occurred. | 12 | Yes |
| SqlHandle | image |
SQL handle that can be used to obtain the batch SQL text using the sys.dm_exec_sql_text dynamic management view. | 63 | Yes |
| StartTime | datetime |
Time at which the event started, if available. | 14 | Yes |
| TextData | ntext |
SQL text of the batch. | 1 | Yes |
EventSubClass 1
| Data column name | Data type | Description | Column ID | Filterable |
|---|---|---|---|---|
| BigintData1 | bigint |
The cumulative number of times this plan has been recompiled. | 52 | Yes |
| BinaryData | image |
The binary XML of the compiled plan. | 2 | Yes |
| DatabaseID | int |
ID 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. | 3 | Yes |
| EventSequence | int |
Sequence of a given event within the request. | 51 | No |
| SessionLoginName | nvarchar |
Login 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. | 64 | Yes |
| EventSubClass | int |
Type 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 |
21 | Yes |
| IntegerData2 | int |
End of the statement within the stored procedure. -1 for the end of the stored procedure. |
55 | Yes |
| ObjectID | int |
System-assigned ID of the object. | 22 | Yes |
| Offset | int |
Starting offset of the statement within the stored procedure or batch. | 61 | Yes |
| SPID | int |
ID of the session on which the event occurred. | 12 | Yes |
| SqlHandle | image |
SQL handle that can be used to obtain the SQL text of the stored procedure using the dm_exec_sql_text dynamic management view. | 63 | Yes |
| StartTime | datetime |
Time at which the event started, if available. | 14 | Yes |
| TextData | ntext |
NULL | 1 | Yes |
| PlanHandle | image |
The 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. | 65 | Yes |
| ObjectType | int |
A value that represents the type of object involved in the event. 8272 = stored procedure |
28 | Yes |
| BigintData2 | bigint |
Total memory, in kilobytes, used during compilation. | 53 | Yes |
| CPU | int |
Total CPU time, in milliseconds, spent during compilation. | 18 | Yes |
| Duration | int |
Total time, in microseconds, spent during compilation. | 13 | Yes |
| IntegerData | int |
The size, in kilobytes, of the compiled plan. | 25 | Yes |
EventSubClass 2
| Data column name | Data type | Description | Column ID | Filterable |
|---|---|---|---|---|
| BigintData1 | bigint |
The cumulative number of times this plan has been recompiled. | 52 | Yes |
| BinaryData | image |
The binary XML of the compiled plan. | 2 | Yes |
| DatabaseID | int |
ID 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. | 3 | Yes |
| EventSequence | int |
Sequence of a given event within the request. | 51 | No |
| SessionLoginName | nvarchar |
Login 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. | 64 | Yes |
| EventSubClass | int |
Type 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 |
21 | Yes |
| IntegerData2 | int |
End of the statement within the batch. -1 for the end of the batch. |
55 | Yes |
| ObjectID | int |
N/A | 22 | Yes |
| Offset | int |
Starting offset of the statement within the batch. 0 for the beginning of the batch. |
61 | Yes |
| SPID | int |
ID of the session on which the event occurred. | 12 | Yes |
| SqlHandle | image |
SQL handle. This can be used to obtain the batch SQL text using the dm_exec_sql_text dynamic management view. | 63 | Yes |
| StartTime | datetime |
Time at which the event started, if available. | 14 | Yes |
| TextData | ntext |
NULL | 1 | Yes |
| PlanHandle | image |
The 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. | 65 | Yes |
| BigintData2 | bigint |
Total memory, in kilobytes, used during compilation. | 53 | Yes |
| CPU | int |
Total CPU time, in microseconds, spent during compilation. | 18 | Yes |
| Duration | int |
Total time, in milliseconds, spent during compilation. | 13 | Yes |
| IntegerData | int |
The size, in kilobytes, of the compiled plan. | 25 | Yes |
EventSubClass 3
| Data column name | Data type | Description | Column ID | Filterable |
|---|---|---|---|---|
| BigintData1 | bigint |
The cumulative number of times this plan has been recompiled. | 52 | Yes |
| BinaryData | image |
NULL | 2 | Yes |
| DatabaseID | int |
ID 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. | 3 | Yes |
| EventSequence | int |
Sequence of a given event within the request. | 51 | No |
| SessionLoginName | nvarchar |
Login 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. | 64 | Yes |
| EventSubClass | int |
Type 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. |
21 | Yes |
| IntegerData2 | int |
End of the statement within the stored procedure or batch. -1 for the end of the stored procedure or batch. |
55 | Yes |
| ObjectID | int |
NULL | 22 | Yes |
| Offset | int |
Starting offset of the statement within the stored procedure or batch. 0 for the beginning of the stored procedure or batch. |
61 | Yes |
| SPID | int |
ID of the session on which the event occurred. | 12 | Yes |
| SqlHandle | image |
SQL handle that can be used to obtain the stored procedure or batch SQL text using the dm_exec_sql_text dynamic management view. | 63 | Yes |
| StartTime | datetime |
Time at which the event started, if available. | 14 | Yes |
| TextData | ntext |
QueryExecutionStats | 1 | Yes |
| PlanHandle | image |
The 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. | 65 | Yes |
| GroupID | int |
ID of the workload group where the SQL Trace event fires. | 66 | Yes |
EventSubClass 4
| Data column name | Data type | Description | Column ID | Filterable |
|---|---|---|---|---|
| BigintData1 | bigint |
NULL | 52 | Yes |
| BinaryData | image |
NULL | 2 | Yes |
| DatabaseID | int |
ID of the database in which the given stored procedure resides. | 3 | Yes |
| EventSequence | int |
Sequence of a given event within the request. | 51 | No |
| SessionLoginName | nvarchar |
Login 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. | 64 | Yes |
| EventSubClass | int |
Type 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. |
21 | Yes |
| IntegerData2 | int |
NULL | 55 | Yes |
| ObjectID | int |
ID of the stored procedure. This is same as the object_id column in sys.procedures. | 22 | Yes |
| Offset | int |
NULL | 61 | Yes |
| SPID | int |
ID of the session on which the event occurred. | 12 | Yes |
| SqlHandle | image |
SQL handle that can be used to obtain the stored procedure SQL text that was executed using the dm_exec_sql_text dynamic management view. | 63 | Yes |
| StartTime | datetime |
Time at which the event started, if available. | 14 | Yes |
| TextData | ntext |
ProcedureExecutionStats | 1 | Yes |
| PlanHandle | image |
The 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. | 65 | Yes |
| GroupID | int |
ID of the workload group where the SQL Trace event fires. | 66 | Yes |
EventSubClass 5
| Data column name | Data type | Description | Column ID | Filterable |
|---|---|---|---|---|
| BigintData1 | bigint |
NULL | 52 | Yes |
| BinaryData | image |
NULL | 2 | Yes |
| DatabaseID | int |
ID of the database in which the given trigger resides. | 3 | Yes |
| EventSequence | int |
Sequence of a given event within the request. | 51 | No |
| SessionLoginName | nvarchar |
Login 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. | 64 | Yes |
| EventSubClass | int |
Type 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. |
21 | Yes |
| IntegerData2 | int |
NULL | 55 | Yes |
| ObjectID | int |
ID of the trigger. This is same as the object_id column in sys.triggers/sys.server_triggers catalog views. | 22 | Yes |
| Offset | int |
NULL | 61 | Yes |
| SPID | int |
ID of the session on which the event occurred. | 12 | Yes |
| SqlHandle | image |
SQL handle that can be used to obtain the trigger's SQL text using the dm_exec_sql_text dynamic management view. | 63 | Yes |
| StartTime | datetime |
Time at which the event started, if available. | 14 | Yes |
| TextData | ntext |
TriggerExecutionStats | 1 | Yes |
| PlanHandle | image |
The 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. | 65 | Yes |
| GroupID | int |
ID of the workload group where the SQL Trace event fires. | 66 | Yes |
See Also
Extended Events
sp_trace_setevent (Transact-SQL)
Showplan XML for Query Compile Event Class
Dynamic Management Views and Functions (Transact-SQL)