Performance Statistics Event Class
The Performance Statistics event class can be used to monitor the performance of queries that are being executed. Each of the four event subclasses indicates an event in the lifetime of a query within the system. Using the combination of these event subclasses and the associated sys.dm_exec_query_stats dynamic management view (DMV), you can reconstitute the performance history of any given query.
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, and EventSubClass 3.
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 |
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. 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:
|
21 |
Yes |
IntegerData2 |
int |
NULL |
55 |
Yes |
ObjectID |
int |
NULL |
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 batch SQL text using the dm_exec_sql_text DMV. |
63 |
Yes |
StartTime |
datetime |
Time at which the event started, if available. |
14 |
Yes |
TextData |
ntext |
SQL text of the batch. |
1 |
Yes |
PlanHandle |
Image |
NULL |
65 |
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:
|
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 DMV. |
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 dm_exec_query_plan DMV. |
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:
|
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 DMV. |
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 DMV. |
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:
For stored procedures with n number of queries:
|
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 DMV. |
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 DMV. |
65 |
Yes |
See Also
Reference
Showplan XML For Query Compile Event Class
Other Resources
Monitoring Events
sp_trace_setevent (Transact-SQL)
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
12 December 2006 |
|