The Session Trace grid uses SQL Server trace functionality to show the SQL events and activity generated by the selected session.

The following events are traced:

RPC:Completed

Occurs when a remote procedure call (RPC) has completed.

SQL:BatchCompleted

Occurs when a Transact-SQL batch has completed.

Attention

Occurs when attention events, such as client-interrupt requests or broken client connections, happen.

Lock:Deadlock

Indicates that two concurrent transactions have deadlocked each other by trying to obtain incompatible locks on resources the other transaction owns.

SP:Recompile

Indicates that a stored procedure was recompiled.

SQL:StmtCompleted

Occurs when the Transact-SQL statement has completed.

SP:Completed

Indicates when the stored procedure has completed.

Lock:Deadlock Chain

Produced for each of the events leading up to the deadlock.

Lock:Escalation

Indicates that a finer-grained lock has been converted to a coarser-grained lock (for example, a page lock escalated or converted to a table lock).

OLE DB Errors

Indicates that an OLE DB error has occurred.

Execution Warnings

Indicates any warnings that occurred during the execution of a SQL Server statement or stored procedure.

For performance reasons, Spotlight limits the number of records that can be viewed in this grid to 500. When the limit is reached, the oldest records are discarded as new ones are collected.

The columns that are displayed in the grid are:

Start Time

The time that the class event started.

End Time

The time that the class event ended.

Event Class

The type of class that was traced.

Duration

The duration of the event class in microseconds.

TextData

Text value dependent on the event class that is captured in the trace.

Reads

The number of logical disk reads performed by the server on behalf of the event.

Writes

The number of physical disk writes performed by the server on behalf of the event.

CPU Usage

The amount of CPU time (in milliseconds) used by the event.

Database ID

ID of the current database.

Object Name

System-assigned ID of the object.

Index ID

ID for the index on the object affected by the event.

  • Opening the Session Trace page initiates a new session that does a trace on the selected session. The grid displays No Data until such time that the session runs some SQL. The grid is populated with the SQL that occurs from the time the Session Trace page is opened.
  • You can use SQL Optimizer (if installed) to tune the SQL of the selected session. To do this, right-click a row in the grid and select Optimize SQL.