The Sessions grid shows all SQL Server sessions, each row representing a single SQL Server session.

Tips to using this grid

  • Click a session in the grid for more details on the session.
  • Many applications create multiple connections to SQL Server.
  • When there are more than 2000 sessions only those sessions that are active (and those sessions that are blocked and blocking) are shown.
  • Show those sessions that are currently running when Active Sessions is selected.
  • Show system sessions when System Sessions is selected.
  • The Filtered by label displays the filter condition information using which list of sessions are filtered based on database name.
  • To close a SQL Server session, right click and select Kill this session. This option is available only to members of the Spotlight Diagnostic Administrators group.
  • The screen refreshes automatically when set to Auto Refresh Resume Refresh. When you wish the screen data to pause as is / freeze, click the icon to Auto Refresh Pause Refresh. Click the icon again to Auto Refresh Resume Refresh.
  • The status column of the grid is color coded for readability.
  • You can view more details for a session by clicking it in this grid. Additional pages are then displayed in the lower half of the drilldown.

General tips to using Spotlight grids

  • Some columns may be hidden by default. Right-click the grid headings and select the columns to show.
  • To filter the data displayed in this grid to restrict displays to a manageable or relevant set of data, right-click the grid and select View/Edit Filter.
  • To find a particular session, right-click and choose Find.

The columns of the grid include:

SPID

Number that SQL Server has assigned to uniquely identify the selected session.

SQL User

SQL Server login name for this session.

Status

Status of the session (runnable, sleeping, blocked etc).

Blocked By

Which SPID (if any) holds locks that this session is waiting on.

Last Cmd

Current or previous command that ran.

DB Name

Name of the Database that the session is in.

Thread Count

Display of thread count. Use to display a single row per session as an option.

Mem

Number of pages in the procedure cache that are currently allocated to this process. A negative number indicates that the process is freeing memory allocated by another process.

CPU ms/s

The CPU time (in milliseconds) the session used per second. Use this column to observe sessions with high recent CPU usage.

I/O per sec

The number of I/O requests serviced per second. Use this column to observe sessions with high I/O usage.

Logical Reads

Number of logical reads performed for each request.

Reads

Number of physical reads performed for each request.

Writes

Number of physical writes performed for each request.

Request Granted Memory

Number of pages allocated to the execution of a query on the request.

Total CPU

CPU-time consumed by the session since SQL Server restart. It is updated every time execution finishes. Units: Milliseconds.

Total I/O

The sum of Total Reads and Total Writes.

Total Logical Reads

Number of logical reads performed for the session.

Total Reads

Number of physical reads performed for the session.

Total Writes

Number of physical writes performed for the session.

Current Wait Time (ms)

Amount of time this session has been waiting. It shows 0 if the session is not currently waiting.

Last Wait Type

Describes the type of wait that this session last waited on (or is currently waiting on).

Last Wait Resource

Describes the resource that this session last waited for (or is currently waiting for). It shows no data if the session is not waiting.

Last Batch Time

Time the last batch started execution.

Time Since Last Batch

Elapsed time since the last batch started execution.

Tran Count

Number of open transactions which corresponds to the session’s @@trancount value.

Program

Program the user is running to access SQL Server.

Host Name

Name of the client computer.

Host Process

Workstation process id number.

Net Address

This is the IP address for the client computer’s network card.

Net Library

Network protocol being used to establish communication between SQL Server and the application.

Login Time

Time the session was created.

Request ID

Returns the ID of the current request within the current session.

Plan Handle

Identifier for the query plan. Note the plan handle is available only when the session is running.

BatchText

The full batch SQL for this session.

Query Plan

The session Query Plan.

Win Domain

Name of the Windows domain that the specified user belongs to.

Win User

Name of the Windows account under which the user is logged on.