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:

  • 1 of type 0

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:

  • 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 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:

  • 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 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:

  • 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 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

New content:
  • Added a table for each of the four event subclasses for the Performance Statistics event class data columns.
Deleted content:
  • Deleted the single table for describing Performance Statistics event class data columns.