SQL Server Process Status
UID:
sql_server_process_statusVersion: 1.3.1 Panels: ~12 Datasource: MSSQL
Purpose
This dashboard monitors SQL Server process activity, showing:
- Running sessions
- Blocked processes and blocking chains
- Long-running queries
- TempDB usage
- Detected deadlocks
It helps identify performance bottlenecks, blocking chains, and concurrency issues that affect database stability.
Variables
| Variable | Description |
|---|---|
$Server_Name | SQL Server to monitor |
$DateFull | Specific date and time for point-in-time analysis |
Color Code
| Color | Code | Meaning |
|---|---|---|
| Green | #00db88 | Running - Processes executing normally |
| Yellow | #fade2a | Suspended - Suspended processes |
| Magenta | #ad21d8e8 | Blocked - Blocked processes |
| Red | #F2495C | Critical - Critical state |
Panels
Process Status Timeline
Process Status timeseries
Timeline of running process status. Only processes with Running, Suspended, and Blocked states are shown.
Process states:
| State | Color | Description |
|---|---|---|
| Running | Green | Actively executing process |
| Suspended | Yellow | Process waiting for resource |
| Blocked | Magenta | Process blocked by another |
Importance: Process status is fundamental to understanding the operational health of the server. A high number of processes in Suspended state indicates waits for resources (I/O, memory, CPU), while Blocked processes signal concurrency problems that can significantly degrade performance and user experience. Monitoring this metric allows detecting problems before they escalate to critical incidents.
TempDB Size
TempDB Size timeseries
Timeline of TempDB usage, separated by object type.
TempDB components:
| Component | Color | Description |
|---|---|---|
| Total Space MB | Green | Total TempDB space |
| Version Store MB | Magenta | Space for row versioning |
| Internal Objects MB | Orange | Internal objects (sorts, hashes) |
| User Objects MB | Cyan | User temporary tables |
Importance: TempDB is a critical database shared by all server connections. Its exhaustion can completely stop server operations, affecting all databases. The Version Store grows with long transactions when using Read Committed Snapshot Isolation (RCSI) or Snapshot Isolation. Internal Objects reflect sorting and hash operations that may indicate inefficient queries. Monitoring TempDB prevents catastrophic service interruptions.
TempDB Usage
A large Version Store may indicate long transactions with RCSI or Snapshot Isolation active. High Internal Objects suggests queries with heavy sorting/grouping operations.
Strategies for managing TempDB
- Configure multiple data files - Use one file per CPU core (up to 8) to reduce allocation page contention
- Pre-size the files - Avoid auto-growth during critical operations
- Enable Trace Flag 1118 - Reduces SGAM contention (in versions prior to SQL Server 2016)
- Monitor Version Store - Identify and terminate orphan transactions
- Place TempDB on fast storage - Dedicated SSD or NVMe if possible
Process Status Summary
Process Status Summary stat
Summary of process status for the selected date.
Metrics:
- Running: Executing processes
- Suspended: Suspended processes
- Blocked: Blocked processes
- Deadlock: Detected deadlocks
Importance: This summary provides a quick view of the server's concurrency status. Blocked and Deadlock counters are critical indicators of application design or query problems. Persistent non-zero values require immediate investigation as they directly impact user productivity and may indicate systemic problems in database or application design.
Thresholds:
| Metric | Value | Color |
|---|---|---|
| Blocked | 1-9 | Magenta |
| Blocked | >= 10 | Red |
| Deadlock | 1-2 | Magenta |
| Deadlock | >= 3 | Red |
Long Running Queries
Long Queries table
Long-running queries that may affect performance.
Columns:
- Process ID
- Query Text
- Duration
- Database
- Application
- Login
- Hostname
Importance: Long-running queries are frequently the root cause of locks, excessive resource consumption, and overall performance degradation. They hold locks for extended periods, consume memory and CPU, can fill TempDB, and block other operations. Identifying them allows proactive optimization and incident prevention.
Long Running Queries
Queries running for a long time can:
- Block other processes
- Consume excessive resources
- Indicate missing indexes
- Cause TempDB growth
How to address long-running queries
- Capture the execution plan - Use SET STATISTICS IO/TIME ON or Query Store
- Identify costly operations - Look for Table Scans, Key Lookups, and Sorts
- Review missing indexes - The execution plan suggests useful indexes
- Evaluate business logic - Determine if the query can be split or run during low-demand hours
- Consider Resource Governor - Limit resources for reporting queries
Blocking Details
Blocking Details table
Detail of the active blocking chain.
Information shown:
- Blocked process
- Blocking process
- Blocked process query
- Blocking process query
- Wait time
- Wait type
Importance: Understanding the blocking chain is essential for resolving concurrency problems. This information allows identifying the head blocker process that is causing the cascade effect. Without this visibility, administrators might terminate incorrect processes or fail to address the root cause. Wait time indicates the severity of impact on affected users.
Steps to resolve a blocking chain
- Identify the head blocker - It is the process that is not being blocked by anyone but blocks others
- Analyze the blocker's query - Determine if it can be optimized or if it is waiting for user input
- Evaluate if it can be terminated - Consider the impact of KILLing the process
- Contact the application owner - If it is a specific application, coordinate with the responsible team
- Document the incident - Record for later analysis and prevention
Deadlock Information
Deadlock Info table
Detailed information on detected deadlocks.
Information included:
- Deadlock ID
- Process ID
- Blocked By
- Query
- Application
- Login
- Database
- Wait Time
- Wait Type
Importance: Deadlocks represent complete transaction failures where SQL Server must sacrifice one of them (the victim). Although SQL Server resolves them automatically, each deadlock means a failed transaction, possible data loss or user work, and experience degradation. Recurring deadlocks indicate design problems that must be corrected in the application code.
Deadlocks
Deadlocks occur when two or more processes mutually block each other waiting for resources the other holds. SQL Server automatically terminates one of the processes (victim). They require analysis to prevent recurrence.
Strategy to eliminate recurring deadlocks
- Enable Trace Flag 1222 - Captures detailed deadlock information in the error log
- Analyze the deadlock graph - Identify resources and access order
- Establish consistent access order - All transactions must access tables in the same order
- Reduce transaction duration - Shorter transactions = lower conflict probability
- Use the appropriate isolation level - Consider READ COMMITTED SNAPSHOT
- Add covering indexes - Reduce the amount of locks needed
Interpretation Guide
Blocking Analysis
| Scenario | Interpretation | Action |
|---|---|---|
| Blocked > 0 | Active blocking exists | Identify blocker |
| High and sustained Blocked | Concurrency problem | Review queries and transactions |
| Blocked spikes | Specific operations | Analyze load patterns |
TempDB Analysis
| High Component | Possible Cause | Action |
|---|---|---|
| Version Store | Long transactions with RCSI | Reduce transaction duration |
| Internal Objects | Queries with sorts/hashes | Add indexes, optimize queries |
| User Objects | Many temporary tables | Review #temp table usage |
Best Practices to Avoid Blocking
Database Design
- Normalize appropriately - Avoid redundancy that causes multiple updates
- Use narrow primary keys - Small clustered indexes reduce blocking
- Design indexes strategically - Covering indexes avoid lookups and reduce blocking
- Partition large tables - Allows parallel operations without conflicts
Application Development
- Short transactions - Do the minimum necessary work within the transaction
- Ordered access to objects - All transactions must access tables in the same alphabetical or logical order
- Avoid user interaction in transactions - Never wait for user input with an open transaction
- Use appropriate isolation level - READ COMMITTED SNAPSHOT eliminates read locks
- Implement retries - Applications must handle deadlocks with retry logic
Operations and Maintenance
- Schedule heavy operations outside peak hours - Reindexing, statistics, backups
- Monitor blocking proactively - Alert when Blocked > threshold for more than N seconds
- Keep statistics updated - Optimal execution plans reduce query duration
- Review problematic queries regularly - Use Query Store to identify regressions
Stored Procedures Used
| Procedure | Description |
|---|---|
[ProcessStatus].[spProcessStatus_Runnable_new] | Processes in Running state |
[ProcessStatus].[spProcessStatus_Suspended_new] | Processes in Suspended state |
[ProcessStatus].[spProcessStatus_Blocked_new] | Processes in Blocked state |
[ProcessStatus].[spProcessStatus_Blocking_new] | Processes that are blocking |
[ProcessStatus].spTempDBSize | TempDB size and usage |
[ProcessStatus].spLongQuerys | Long-running queries |
[ProcessStatus].[spDeadlock] | Deadlock information |
Views Used
| View | Description |
|---|---|
ProcessStatus.vwProcessStatus_new | Current process status |
Troubleshooting
Frequent Blocking
- Identify the blocking query
- Review if there are missing indexes
- Evaluate isolation level (consider RCSI)
- Optimize transactions to make them shorter
TempDB Full
- Identify queries with high Internal Objects
- Review temporary table usage
- Verify long transactions with Version Store
- Consider adding files to TempDB
Recurring Deadlocks
- Analyze the deadlock XML
- Identify conflicting resources
- Establish consistent order of table access
- Consider locking hints if necessary
Microsoft References
Blocking and Concurrency
- Transaction Locking and Row Versioning Guide - Complete guide on how SQL Server manages locking and row versioning
- Understand and Resolve Blocking Problems - Blocking problem diagnosis and resolution
- sys.dm_tran_locks - Dynamic management view to monitor active locks
Deadlocks
- Analyze and Prevent Deadlocks - Guide to understand, detect, and prevent deadlocks
- Detect and End Deadlocks - Procedures to resolve deadlocks
- Trace Flag 1222 - Enable detailed deadlock information in the error log
TempDB
- Optimize TempDB Performance - TempDB configuration and best practices
- Monitor TempDB Usage - How to monitor TempDB space and usage
- TempDB Contention - Diagnose and resolve TempDB contention issues
Isolation Levels
- Transaction Isolation Levels - Reference for different isolation levels
- Read Committed Snapshot Isolation - Implement RCSI to reduce read locks
- Snapshot Isolation - Use snapshot isolation for consistent transactions