Muokkaa

Jaa


Performance Statistics Event Class

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

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)