SQL Server Process Status
Page description
This page shows the process status of an instance. It works the same way on any SQL Server on-premises, Azure SQL Database, Azure SQL Managed Instance, Fabric SQL Database, or Amazon RDS for SQL Server instance — no different configuration is needed depending on the instance type. It combines session activity, blocking, long queries, deadlocks and TempDB usage into a single view, with two ways of looking at the same data: an aggregated Trend over the selected time range, and a Snapshot with the exact detail of one specific instant.
A status header summarizes at a glance whether the instance needs attention, and an Activity Detail section lets you investigate session by session — including blocking chains and deadlock cycles rendered as navigable trees, not just flat lists.
Variables
| Variable | Description |
|---|---|
$Server_Name | Selects the instance to analyze — SQL Server on-premises, Azure SQL Database, Azure SQL Managed Instance, Fabric SQL Database, or Amazon RDS for SQL Server. Values correspond to the instances configured in the monitoring environment. |
Comments | Language (Spanish or English) of the AI suggestion texts shown in the AI Suggestions tab of the Activity Detail detail view: documented query, optimized query and recommendations. The original query text and the execution plan are not translated. |
| Time range | Defines the historical window that feeds the Trend section (aggregated KPIs and time series) and the set of available snapshots on the Time Selector. The Snapshot section doesn't depend directly on the range, but on the specific instant selected within it (or the most recent one, if none has been pinned). |
General Status
Status Header
- Description: Top bar showing the instance name and an overall status badge, computed from the blocking, long queries and deadlocks detected in the most recent activity.
Colors by value:
| Value | Color | Description |
|---|---|---|
| HEALTHY | No blocking, long queries or active deadlocks, with a recent check. | |
| TO CHECK | There are blocked processes and/or active long queries, but no deadlock. | |
| NO DATA | No recent snapshot has been received from the instance. | |
| WRONG (CRITICAL) | At least one deadlock has been detected. |
Practical recommendation
A CRITICAL status deserves immediate review on the Deadlocks tab of the Activity Detail; TO CHECK is a signal to watch closely, not necessarily to act on right away.
Trend
Section with the aggregated indicators and the evolution over time of all activity in the selected range — processes, TempDB and its internal composition.
Process Status · KPIs
- Description: Aggregated process activity metrics over the selected time range, across four cards.
Max Blocked — When a session needs a resource (a row, a page, a table) that another session already holds, it becomes "blocked" until that second session (the "blocker") releases it. This card shows the highest peak of blocked or blocking processes detected in a single snapshot within the range — in other words, the moment of worst blocking congestion.
Max Active (run + sus) — Runnable is a session ready to run that's only waiting for CPU; Suspended is a session waiting on any other resource (a lock, a disk page, I/O…), not necessarily because of another session. This card shows the peak of simultaneously active sessions combining both states, with the three highest peaks in the range — the green value is runnable sessions at that peak, the blue value is suspended sessions. Purely informational, no color threshold.
Long Queries — A session qualifies as a long query when it has been active (runnable or suspended) in more than 25% of the snapshots collected in the hour before a given instant: in other words, it has been running for a disproportionately long time compared to the server's normal activity. This card shows the highest peak of concurrent long queries detected in a single snapshot in the range.
Total Deadlocks — A deadlock happens when two or more sessions block each other in a circle (session A waits for a resource held by B, and B waits for one held by A), so neither can proceed; SQL Server detects this automatically and terminates one of the sessions (the "victim") to break the cycle. This card shows how many distinct deadlocks were detected across the whole selected range, and the time of the last one.
Colors by value:
| Card | Value | Color | Description |
|---|---|---|---|
| Max Blocked | Remaining values | No blocked or blocking processes at the range's peak. | |
| > 0 | At least one blocked or blocking process at the peak. | ||
| > 10 | The blocked/blocking peak exceeds 10 processes — likely severe user impact. | ||
| Max Active (run + sus) | — | No color threshold — purely informational indicator. | |
| Long Queries | Remaining values | No long queries detected at the range's peak. | |
| > 0 | 1 or more concurrent long queries at the peak. | ||
| > 5 | The concurrent long-query peak exceeds 5 — high concurrency of long-running queries. | ||
| Total Deadlocks | Remaining values | No deadlock detected across the whole selected range. | |
| > 0 | At least one deadlock detected in the range — no intermediate warning state. |
Peaks are clickable: each marked peak on a card (Max Blocked, Max Active, Long Queries, Total Deadlocks) can be clicked to pin that instant as the selected one — equivalent to clicking it on the Snapshot section's Time Selector. Clicking updates the whole Snapshot section (counters, TempDB and Activity Detail) to that exact moment, and automatically opens the matching Activity Detail tab (Max Blocked → Blocked tab, Max Active → All Process tab, Long Queries → Long Queries tab, Total Deadlocks → Deadlocks tab), so you can jump straight from the aggregated peak to the detail of what was happening at that instant.
Practical recommendation
For a high Max Blocked peak, check the Blocked tab of the Activity Detail to identify the root blocker before considering a KILL — killing the wrong session in a blocking chain doesn't solve the problem. For Long Queries, cross-check the session's execution plan (detail button on the row) before assuming the issue is only about duration. For Total Deadlocks, review the full cycle on the Deadlocks tab: it's almost always resolved by ensuring transactions always access the same resources in the same order.
Process Status · Trend
- Description: Time series showing process activity evolution over the selected range. Each data point represents a collected snapshot. Blocked, Long Queries and Deadlock are only visible when their value is greater than zero, to reduce visual noise when there are no incidents.
- Units: number of processes
Colors by value:
| Series | Color |
|---|---|
| Blocked | |
| Running | |
| Suspended | |
| Long Queries | |
| Deadlock |
TempDB Status · KPIs
- Description: Aggregated TempDB usage metrics over the selected time range, across four cards. TempDB is a system database shared by the whole instance, used for temporary tables, sorting, row versioning and other internal engine operations.
Max Total Size — Maximum total size reached by TempDB during the range: the capacity reserved for its files, combining free and used space. Purely informational, no color threshold, and serves as reference to interpret Max Used Size.
Max Used Size — Peak of actually occupied space in TempDB (Total Size minus free space) during the range: how much of that capacity was in use at the worst moment.
Max User Objects — Peak size of objects explicitly created by user sessions: temporary tables (#table), table variables and cursors. Informational, no color threshold.
Ver + Int Objects — Maximum jointly reached by two types of internal TempDB consumption: the version store (space SQL Server reserves to keep previous row versions, used by snapshot isolation and triggers) and internal objects (temporary space the engine itself uses during sort operations, hash joins and bulk operations). Informational, no color threshold.
Colors by value:
| Card | Value | Color | Description |
|---|---|---|---|
| Max Total Size | — | No color threshold — purely informational indicator. | |
| Max Used Size | Remaining values | Used space stays below 40% of Max Total Size. | |
| > 40% of Max Total Size | The used-space peak exceeds 40% of the maximum total size. | ||
| > 75% of Max Total Size | The used-space peak exceeds 75% of the maximum total size — risk of running out of TempDB. | ||
| Max User Objects | — | No color threshold — purely informational indicator. | |
| Ver + Int Objects | — | No color threshold — purely informational indicator. |
Peaks are clickable, just like in Process Status · KPIs: clicking the Max Total Size or Max Used Size peak pins that instant and updates the whole Snapshot section, automatically opening the TempDB Usage tab of the Activity Detail.
Practical recommendation
A red Max Used Size deserves immediate review — identify which sessions are consuming TempDB on the TempDB Usage tab of the Activity Detail. The most common causes are very large temp tables, excessive row versioning from snapshot isolation, or very long transactions holding onto version store. Pre-sizing TempDB in advance (avoiding constant autogrowth) also helps stabilize this indicator.
TempDB Status · Trend
- Description: Time series showing TempDB size evolution over the selected time range. Each data point represents a collected snapshot. Used Size is calculated as the sum of Version Store, Internal Objects and User Objects — see the TempDB Composition panel for the breakdown by object type.
- Units: megabytes
Colors by value:
| Series | Color |
|---|---|
| Total Size | |
| Used Size |
TempDB Composition · Trend
- Description: Breakdown of TempDB used size over the selected time range, showing which type of object consumes the space: user objects (temporary tables, table variables, cursors), version store (row versioning, snapshot isolation, triggers) and internal objects (sort operations, hash joins, spools, bulk operations).
- Units: megabytes
Colors by value:
| Series | Color |
|---|---|
| Version Store | |
| Internal Objects | |
| User Objects |
Snapshot
Section with the exact state of the instance at one specific instant, chosen with the Time Selector — instead of a range aggregate, it shows "the picture" at that moment.
Time Selector
- Description: Timeline of every snapshot in the selected time range. Clicking a point pins that instant, and the Snapshot section below shows the exact state at that moment. Gaps in the line mean no data was collected. If nothing is selected, the most recent snapshot is used.
Process Status · Snapshot
- Description: Process counters at the selected snapshot, mirroring Process Status · KPIs from the Trend section, but referring to that single instant. Long Queries counts the long-running sessions of the current range that were still active at this specific instant.
Colors by value:
| Card | Value | Color | Description |
|---|---|---|---|
| Blocked | Remaining values | No blocked or blocking processes at this instant. | |
| > 0 | At least one blocked or blocking process at this instant. | ||
| > 10 | More than 10 blocked/blocking processes at this instant — likely severe impact. | ||
| Running | — | No color threshold — purely informational indicator. | |
| Suspended | — | No color threshold — purely informational indicator. | |
| Long Queries | Remaining values | No active long queries at this instant. | |
| > 0 | 1 or more active long queries at this instant. | ||
| > 5 | More than 5 active long queries at this instant. | ||
| Deadlock | — | No color threshold — the value always shows in fixed red, with no additional border warning state. |
TempDB Status · Snapshot
- Description: TempDB space distribution at the selected snapshot, mirroring TempDB Status · KPIs from the Trend section. Free space is Total minus Used. A dash means no TempDB capture within 6 minutes of the selected instant (TempDB is collected on a different cadence than processes).
Colors by value:
| Card | Value | Color | Description |
|---|---|---|---|
| Total Size | — | No color threshold — purely informational indicator. | |
| Used Size | Remaining values | Used space stays below 40% of the total size at this instant. | |
| Used Size | > 40% of Total Size | Used space exceeds 40% of the total at this instant. | |
| Used Size | > 75% of Total Size | Used space exceeds 75% of the total at this instant — risk of running out of TempDB. | |
| User Objects | — | No color threshold — purely informational indicator. | |
| Version Store | — | No color threshold — purely informational indicator. | |
| Internal Objects | — | No color threshold — purely informational indicator. |
Activity Detail · Snapshot
- Description: Detailed, session-by-session view of activity at the selected snapshot, organized into five tabs (All Process, Blocked, Long Queries, Deadlocks, TempDB Usage). Each row represents a session and can be opened for its full detail, including the execution plan if one was captured.
Card Mode and Table Mode
To the right of the tabs there is a switch between two ways of viewing the same sessions:
- Card Mode (the default view): one card per session, with badges and the query preview.
- Table Mode: a classic table, with one row per session and one column per field. On Blocked, the first column draws the blocking tree.
In Table Mode, the ⎘ Copy table button copies the visible rows as text, so they can be exported to another tool.
Toolbar
Available on the three flat-list tabs (All Process, Long Queries, TempDB Usage) — it doesn't appear on Blocked or Deadlocks, because on those two the row order is the tree hierarchy itself and reordering them wouldn't make sense.
Search: free-text field. Filters rows by matching the typed text against SPID, Application, Login, Database, Hostname, Status and the query's full text — it's enough for it to appear in any one of those fields.
Status: a row of checkboxes, one for each status actually present in the current snapshot (not a fixed list) — for example, if there's no
sleepingsession in this snapshot, that checkbox doesn't appear. All start checked; unchecking one hides rows with that status. A status that's never been unchecked before always starts checked by default.Sort by: sort buttons, different per tab:
- All Process and Long Queries: CPU, Physical IO, Memory, Id, Login Time, Last Batch.
- TempDB Usage: Accumulated Size, Active Size, Id.
Clicking the same button again reverses the order (▼ descending, ▲ ascending). The field currently used for sorting is highlighted in green on every row, so its value is easy to spot at a glance.
All Process
Every active session in the snapshot, one row per SPID, as a flat list with no hierarchy. Sorted by CPU by default.
Each process row on All Process, Long Queries, Blocked and Deadlocks reads in three lines (on Blocked and Deadlocks, in its place in the tree):
- Session identity: the status and badges, followed by Application, Login, Database and Hostname, and the View Detail button.
- Usage and timing: CPU, Physical IO, Memory, Wait Type, Wait Time (in ms), Login Time and Last Batch.
- Query: a one-line preview of the text being executed (the full, formatted text is in View Detail).
Application, Login, Database and Hostname appear in this same order on every tab, so rows from different tabs can be compared without hunting for the field.
Blocked
Blocking chains rendered as a hierarchical tree: each "head" row (the root blocking session of a chain, with nothing above it) can be expanded or collapsed by clicking it, and the rows it blocks appear indented below, cascading further if the blocking propagates through several levels. The head row shows a badge with the total number of processes blocked anywhere in the chain, at any depth — not just the ones it blocks directly.
An isolated deadlock (one with no blocking relationship to any other visible process in this snapshot) has nothing to anchor to in the tree, so it appears as a standalone row in this same tab instead of being grouped under a header.
Long Queries
Only sessions active (runnable or suspended) in more than 25% of the last hour's snapshots — the same criterion that feeds the Long Queries KPI card.
Deadlocks
Each detected deadlock cycle is grouped under its own synthetic header "Deadlock 01", "Deadlock 02"… (one header per cycle, not a real session), with the same tree layout and the same collapse behavior as the Blocked tab — the sessions that took part in that cycle appear indented below its header.
TempDB Usage
Sessions currently consuming TempDB space, with their accumulated (Accumulated Size) and active (Active Size) size in MB. This table is captured on a different cadence than processes (roughly every 5 minutes), so it may not reflect exactly the same instant as the other tabs.
Badges
Besides the status color (see the table below), each row can carry one or more badges independently of each other:
| Badge | Color | When it appears |
|---|---|---|
| Status (Runnable, Suspended…) | per status, see the table below | Always — it's the session's current status. |
active X of last 60 min | The session meets the long-query criterion (active in more than 25% of the last hour's snapshots). | |
deadlock | The session took part in a detected deadlock cycle. | |
blocked for Xs | The row is genuinely blocked by another one (has a non-zero Blocked By) and has a measurable wait time — appears on any tab, not only Blocked. | |
N blocked | Only on the head row of a blocking chain: total number of sessions blocked anywhere in the chain. | |
primary blocker | The session isn't blocked by anyone, but directly blocks at least one other currently visible row — can appear on any tab, not only Blocked. | |
Execution Plan | An execution plan was captured for that session — appears on any of the five tabs. | |
AI Suggestions | The session's query already has AI-generated optimization suggestions. Appears on any of the five tabs. |
Colors by value (each session's status):
| Value | Color | Description |
|---|---|---|
| Runnable | Session ready to run, waiting for CPU. | |
| Suspended | Session waiting on a resource (lock, page, latch, I/O). | |
| Sleeping | Open connection with no pending work at this instant. | |
| Running / Dormant / Background / Rollback / Pending / Spinloop | Transitional or SQL Server internal-process states. |
View Detail
Every row has a ◈ View Detail button that opens a modal with that session's full information:
- Blocking relationships: if the session is the primary blocker, a "primary blocker" badge; if it's blocked by another one, a "Blocked by" chip with the blocking SPID; if it blocks others, a "Blocking" chip listing their SPIDs (up to 5, with a "+N" if there are more).
- General fields: a grid with every available field for that session — Application, Login, Database, Hostname, Status, Command, Wait Type, Wait Time (ms), CPU, Physical IO, Memory Usage, Login Time, Last Batch, and for TempDB Usage rows, Accumulated Size and Active Size. Empty fields are not shown.
- Two internal tabs:
- Query: the query's full text, formatted and indented (unlike the one-line preview shown on the row). On TempDB Usage rows, if the "Code Fragment" adds information different from the full text, it's shown separately too.
- Execution Plan: the query's graphical execution plan, with a button to copy the plan's XML. If no plan was captured for that session, it shows "No execution plan captured for this query."
- AI Suggestions: only appears when that session's query has AI suggestions (the same ones behind the
AI Suggestionsbadge). It shows, one below the other, the AI Documented Query (the query with comments explaining each part), the AI Optimized Query (the proposed version) and the Recommendations, grouped into what is already applied in the optimized query and what is still pending. Each block has its own ⎘ Copy button. The language is chosen with theCommentsvariable.
Practical recommendation
On the Blocked tab, always start with the row marked as primary blocker — it's the only one whose resolution frees the rest of the chain. On Deadlocks, open each cycle's detail to see the actual resource-acquisition order of every involved session; it's the most reliable way to decide which transaction to reorder so it doesn't happen again.