Session Waits
Overview
The Waits report shows the instance’s waits since it started, and Waits by Query shows statements, but neither says what one connection has been waiting on since it logged in. That is how a DBA proves that a particular application spends its time slow to read its results (ASYNC_NETWORK_IO) or blocked by other sessions.
The Session Waits report has two views:
- Sessions – one row per session with its total wait time since it connected and its top three waits, from
sys.dm_exec_session_wait_stats(SQL Server 2016 and later), and a chart that splits the selected session’s waits by wait category. - Open cursors – every cursor the sessions hold, from
sys.dm_exec_cursors, with its age, how long it has sat unused and its statement text. A cursor unused for more than 10 minutes is highlighted: the usual sign of an ORM or legacy application that opened an API cursor and forgot it, which still holds its worktable in tempdb and, for keyset and dynamic cursors, can hold locks.
The page reads the instance once when it opens, and again when you click Refresh.
Where to find it
| Route | How |
|---|---|
| Server tree | Right-click the server → Instance Level Reports → Session Waits |
| Instance reports navigator | Performance group |
| Related Links bar | From Sessions, Waits, What is Active and Workload by Application |
| Go to menu | Right-click a session on What is Active, Sessions and the other live session pages; Session Waits opens on that session |
| Report arrows | Previous is Sensitive Data by Database, next is Sessions |
The toolbar
| Control | What it does |
|---|---|
| Refresh | Reads the sessions, their waits and the cursors again |
| Sessions / Open cursors | Which view is shown. Switching does not query again |
| Program, host or login | Type text and press Enter to show only the sessions whose program, host or login contains it (ignoring case). The filter applies to both views |
| Clear | Removes the filter |
| System sessions | Includes the instance’s own background sessions. Off by default |
| Cancel | Shown while a read is running; stops it. What was on the page stays |
Reading the page
The heading says how many sessions (or cursors, and how many sessions hold them) are shown, and when the instance was read, in server time. The line under it says what is filtered or hidden: the filter text, that system sessions are hidden, that background waits and your ignore list are left out, and any part of the read that failed.
The cards across the top:
| Card | Meaning |
|---|---|
| SESSIONS SHOWN | Sessions on the page, of all those connected. Orange when the login has no VIEW SERVER STATE |
| SESSIONS THAT HAVE WAITED | Sessions shown with any wait worth reporting |
| LARGEST WAIT ACROSS THEM | The wait type with the most time across the sessions shown, with that time and its category |
| SESSION WAITS | Replaces the two cards above before SQL Server 2016: Not available |
| OPEN CURSORS | Open cursors shown, and how many are unused over 10 minutes. Orange when any are; Unknown when the cursors could not be read |
The Sessions view
The chart draws one session’s waits as a bar split by wait category (Network, Locking, IO, Transaction Log, Parallelism, CPU / Scheduler, Memory and the others the Waits pages use), in the same colors the Waits pages use, with a legend row per category naming its largest wait types. It shows the session selected in the grid, or, when none is selected, the session that has waited the longest. Hover a segment or legend row for each wait’s time, count and longest wait, and what the category usually means.
Under the title, one line says what the largest wait means for that session:
| Largest wait’s category | What the page says |
|---|---|
| Network | SQL Server had rows ready and the client was slow to take them. Look at the application reading the results, not the query |
| Locking | It has spent its time blocked by other sessions. Blocking Tree shows who holds the locks |
| IO | Reading pages from disk. Its queries read more than fits in memory, or storage is slow |
| Transaction Log | Committing to the transaction log. Many small transactions or a slow log drive |
| Parallelism | Parallel threads waiting on each other |
| CPU / Scheduler | Waiting for a CPU |
| Memory | Waiting for memory, usually a query memory grant |
The grid lists the sessions, most wait time first:
| Column | Meaning |
|---|---|
| Session | The session id |
| Login, Host, Program | Who the session is |
| Database | The session’s current database (SQL Server 2012 and later) |
| Status | The session status |
| Login Time | When the session connected |
| Total Wait ms | Wait time since it connected, on the waits worth reporting |
| Signal % | The share of that time spent runnable, waiting for a CPU |
| Cursors | Cursors the session holds. Orange when any of them is unused over 10 minutes |
| Top Waits | The three largest waits and their share, for example ASYNC_NETWORK_IO 72%, LCK_M_S 20%, PAGEIOLATCH_SH 5% |
Click a row to draw that session on the chart. Hover a row for the one line verdict, the time on waits that were left out, and the number of cursors.
The Open cursors view
The chart shows the ten cursors unused the longest as bars, each with how long it has been unused, when it was declared and its type. Bars for cursors unused more than 10 minutes are orange. Click a bar to select its row.
The grid lists every cursor, unused the longest first:
| Column | Meaning |
|---|---|
| Session, Login, Host, Program | The session holding the cursor |
| Cursor | The cursor name, or (API cursor n) for an API cursor, which has none |
| Type | API or TSQL and the cursor type, for example API Dynamic. A static cursor, which the DMV calls Snapshot, is shown as Static |
| Concurrency | Read only, optimistic and so on, from the cursor’s properties |
| Open | Whether the cursor is open |
| Age | How long ago it was declared |
| Unused For | How long since it was last used |
| Fetch Status | Last fetch succeeded, Fetch failed or past the end, Row missing, or Not fetched yet |
| CPU ms, Reads, Writes | The work the cursor has done |
| Declared | When it was declared |
| SQL Text | The cursor’s statement |
A cursor unused more than 10 minutes has its Cursor and Unused For shown in orange.
Right-click menu
- Go to leads to What is Active, Sessions and the other live pages about the session on the row, and to the pages about its login, host and database.
- Copy the cursor’s SQL text (Open cursors view).
- Copy the findings as text copies the heading, each session with its top waits, and the cursors unused the longest.
- Copy Chart, and the usual CSV and Excel export.
When another page sends you here for a session, the page clears a filter or shows system sessions if that is what it takes to show it, and selects it. If the session has disconnected since, a banner says so.
What is left out
- Background waits, the same benign and idle waits the Waits page leaves out, and the waits on your ignore list for this instance (the list the Waits page and the Historic Waits Ignore Waits dialog keep in DBHealthHistory). Their time is shown in the row’s tooltip.
- The report’s own session.
- Waits of zero ms.
The waits of a session are counted since it logged in. A pooled connection that is reused is reset, so its counters start again.
Permissions and versions
- The report reads
sys.dm_exec_sessions,sys.dm_exec_session_wait_stats,sys.dm_exec_cursorsandsys.dm_exec_sql_text, which need VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later) to see other sessions. Without it only your own sessions are visible, and the page says so. If the connection check found the permission missing, the menu item is muted and its tooltip says what it needs; it can still be opened. - Per session waits need SQL Server 2016 or later. On an older instance the page opens on Open cursors, which works on every version, and the Sessions view says why it is empty.
- Each of the three reads is separate: if one is refused, the page names it and shows the rest.
- The read is given 60 seconds and runs in the background with a Cancel button.