---
id: cc8eca16-89b2-4d52-9dd5-3917fd11c926
---

# High number of processes blocked in SQLServer
---

This incident type involves a high number of processes being blocked in SQLServer. It may cause delays or errors in database operations and can impact the performance of the system.

### Parameters
```shell
# Environment Variables

export SQLSERVER="PLACEHOLDER"

export SERVER_NAME="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export TABLE_NAME="PLACEHOLDER"

export PATH_TO_ERROR_LOG_FILE="PLACEHOLDER"



```

## Debug

### Next Step
```shell
powershell

# 1. Check the CPU usage of the SQLServer process.

Get-Process -Name ${SQLSERVER} | Select-Object -Property CPU
```

### 2. Check the memory usage of the SQLServer process.
```shell
Get-Process -Name ${SQLSERVER} | Select-Object -Property WorkingSet
```

### 3. Check the blocked processes in SQLServer.
```shell
Invoke-Sqlcmd -ServerInstance ${SERVER_NAME} -Database ${DATABASE_NAME} -Query "SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id <> 0"
```

### 4. Check the top queries that are causing the blocking.
```shell
Invoke-Sqlcmd -ServerInstance ${SERVER_NAME} -Database ${DATABASE_NAME} -Query "SELECT TOP 10 * FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE execution_count > 0 AND last_execution_time > DATEADD(minute, -5, GETDATE()) ORDER BY total_worker_time DESC"
```

### 5. Check the current state of the SQLServer.
```shell
Get-Service -Name ${SQLSERVER}
```

### 6. Check the SQLServer error logs for any relevant messages.
```shell
Get-Content "${PATH_TO_ERROR_LOG_FILE}" | Select-String -Pattern "blocked", "error", "warning"
```

## Repair

### Define input parameters
```shell
DB_NAME=${DATABASE_NAME}

TABLE_NAME=${TABLE_NAME}
```

### Check for inefficient queries
```shell
echo "Checking for inefficient queries in ${DB_NAME}..."

inefficient_queries=$(sqlcmd -S ${SERVER_NAME} -U ${USERNAME} -P ${PASSWORD} -d ${DB_NAME} -Q "SELECT query FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE text LIKE '%${TABLE_NAME}%' ORDER BY total_worker_time DESC")

if [[ -z "${inefficient_queries}" ]]; then

    echo "No inefficient queries found."

else

    echo "Inefficient queries found:"

    echo "${inefficient_queries}"

fi
```

### Kill any long-running or blocking processes manually or automatically using a script.
```shell

# Define variables
 USERNAME="PLACEHOLDER"
 PASSWORD="PLACEHOLDER"
 SERVERINSTANCE="PLACEHOLDER"


# Define the query to find long-running or blocking processes

$Query = "SELECT session_id, status, blocking_session_id, wait_type, wait_time, wait_resource, command, last_wait_type, cpu_time, total_elapsed_time FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND (status = 'running' OR blocking_session_id <> 0)"



# Get the list of processes to kill

$Processes = Invoke-Sqlcmd -Query $Query -ServerInstance ${SERVERINSTANCE} -Database ${DATABASE}



# Loop through the processes and kill them

ForEach ($Process in $Processes) {

    Write-Host "Killing process ID $($Process.session_id)..."

    Invoke-Sqlcmd -Query "KILL $($Process.session_id)" -ServerInstance ${SERVERINSTANCE} -Database ${DATABASE}

}



# Print a message to indicate completion

Write-Host "All long-running or blocking processes have been killed."


```

### Check the database for inefficiencies in queries or indexes that could potentially cause blocking. Optimize the queries or add indexes to improve performance.
```shell


# Define variables
 USERNAME="PLACEHOLDER"
 PASSWORD="PLACEHOLDER"
 SERVERINSTANCE="PLACEHOLDER"
 COLUMN_NAME="PLACEHOLDER"
 INDEX_NAME="PLACEHOLDER"



# Check for inefficiencies in queries or indexes

sqlcmd -S $SERVERINSTANCE -d ${DATABASE_NAME} -U $USERNAME -P $PASSWORD -Q "SET NOCOUNT ON; SELECT TOP 10 * FROM sys.dm_exec_requests"

sqlcmd -S $SERVERINSTANCE -d ${DATABASE_NAME} -U $USERNAME -P $PASSWORD -Q "SET NOCOUNT ON; SELECT TOP 10 * FROM sys.dm_exec_sessions"

sqlcmd -S $SERVERINSTANCE -d ${DATABASE_NAME} -U $USERNAME -P $PASSWORD -Q "SET NOCOUNT ON; SELECT TOP 10 * FROM sys.dm_exec_query_stats"



# Optimize queries or add indexes

sqlcmd -S $SERVERINSTANCE -d $database_name -U $USERNAME -P $databasePASSWORD_password -Q "SET NOCOUNT ON; EXEC sp_helpindex '${TABLE_NAME}'"

sqlcmd -S $SERVERINSTANCE -d $database_name -U $USERNAME -P $PASSWORD -Q "SET NOCOUNT ON; CREATE INDEX ${INDEX_NAME} ON ${TABLE_NAME} (${COLUMN_NAME})"



# Exit script

exit 0


```


This incident type involves a high number of processes being blocked in SQLServer. It may cause delays or errors in database operations and can impact the performance of the system.


In software engineering, a large number of suspended threads in Tomcat is an incident type that occurs when a high number of threads are created but are blocked from executing. This can happen due to a variety of reasons, such as slow database queries, network latency, or resource contention. When this occurs, the system may become unresponsive or slow, causing a degradation in performance or even a complete system failure. It is important to investigate and resolve this issue as quickly as possible to ensure the system can continue to function properly.


Tomcat Large Number of Suspended Threads Incident.

This incident type refers to a situation where the Tomcat server's JDBC connection pool usage surpasses its configured limits. This can lead to a shortage of available connections for incoming requests, resulting in slow response times or even server crashes. It is important to monitor and manage the JDBC connection pool usage to prevent such incidents from occurring.


Tomcat High JDBC Connection Pool Usage Incident.

A SQL Server deadlock incident occurs when two or more processes are blocked and waiting on each other to release a resource. This results in a situation where none of the processes can continue executing, causing a system slowdown or complete system failure. The incident requires immediate attention from a software engineer to identify and resolve the issue.


SQL Server Deadlock Incident

This incident type refers to a scenario where there is a high rate of statement timeouts in a Postgresql database instance. This can lead to degraded performance and potentially impact the availability of the database. It is important to quickly identify and address the underlying cause of the timeouts to ensure the stability of the system.


Postgresql high rate statement timeout incident.

The Low Postgresql commit rate incident is triggered when the number of transactions processed by Postgresql drops below a certain threshold, indicating a potential issue with the database's performance. This could be caused by a variety of factors, such as high load or inadequate resources. The incident requires investigation and resolution to ensure the database is functioning properly.


Low Postgresql commit rate incident

```shell
# Environment Variables

export SQLSERVER="PLACEHOLDER"

export SERVER_NAME="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export TABLE_NAME="PLACEHOLDER"

export PATH_TO_ERROR_LOG_FILE="PLACEHOLDER"



```


### Next Step

```shell
powershell

# 1. Check the CPU usage of the SQLServer process.

Get-Process -Name ${SQLSERVER} | Select-Object -Property CPU
```

### 2. Check the memory usage of the SQLServer process.

```shell
Get-Process -Name ${SQLSERVER} | Select-Object -Property WorkingSet
```

### 3. Check the blocked processes in SQLServer.

```shell
Invoke-Sqlcmd -ServerInstance ${SERVER_NAME} -Database ${DATABASE_NAME} -Query "SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id <> 0"
```

### 4. Check the top queries that are causing the blocking.

```shell
Invoke-Sqlcmd -ServerInstance ${SERVER_NAME} -Database ${DATABASE_NAME} -Query "SELECT TOP 10 * FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE execution_count > 0 AND last_execution_time > DATEADD(minute, -5, GETDATE()) ORDER BY total_worker_time DESC"
```

### 5. Check the current state of the SQLServer.

```shell
Get-Service -Name ${SQLSERVER}
```

### 6. Check the SQLServer error logs for any relevant messages.

```shell
Get-Content "${PATH_TO_ERROR_LOG_FILE}" | Select-String -Pattern "blocked", "error", "warning"
```


### Define input parameters

```shell
DB_NAME=${DATABASE_NAME}

TABLE_NAME=${TABLE_NAME}
```

### Check for inefficient queries

```shell
echo "Checking for inefficient queries in ${DB_NAME}..."

inefficient_queries=$(sqlcmd -S ${SERVER_NAME} -U ${USERNAME} -P ${PASSWORD} -d ${DB_NAME} -Q "SELECT query FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE text LIKE '%${TABLE_NAME}%' ORDER BY total_worker_time DESC")

if [[ -z "${inefficient_queries}" ]]; then

    echo "No inefficient queries found."

else

    echo "Inefficient queries found:"

    echo "${inefficient_queries}"

fi
```

### Kill any long-running or blocking processes manually or automatically using a script.

```shell

# Define variables
 USERNAME="PLACEHOLDER"
 PASSWORD="PLACEHOLDER"
 SERVERINSTANCE="PLACEHOLDER"


# Define the query to find long-running or blocking processes

$Query = "SELECT session_id, status, blocking_session_id, wait_type, wait_time, wait_resource, command, last_wait_type, cpu_time, total_elapsed_time FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND (status = 'running' OR blocking_session_id <> 0)"



# Get the list of processes to kill

$Processes = Invoke-Sqlcmd -Query $Query -ServerInstance ${SERVERINSTANCE} -Database ${DATABASE}



# Loop through the processes and kill them

ForEach ($Process in $Processes) {

    Write-Host "Killing process ID $($Process.session_id)..."

    Invoke-Sqlcmd -Query "KILL $($Process.session_id)" -ServerInstance ${SERVERINSTANCE} -Database ${DATABASE}

}



# Print a message to indicate completion

Write-Host "All long-running or blocking processes have been killed."


```

### Check the database for inefficiencies in queries or indexes that could potentially cause blocking. Optimize the queries or add indexes to improve performance.

```shell


# Define variables
 USERNAME="PLACEHOLDER"
 PASSWORD="PLACEHOLDER"
 SERVERINSTANCE="PLACEHOLDER"
 COLUMN_NAME="PLACEHOLDER"
 INDEX_NAME="PLACEHOLDER"



# Check for inefficiencies in queries or indexes

sqlcmd -S $SERVERINSTANCE -d ${DATABASE_NAME} -U $USERNAME -P $PASSWORD -Q "SET NOCOUNT ON; SELECT TOP 10 * FROM sys.dm_exec_requests"

sqlcmd -S $SERVERINSTANCE -d ${DATABASE_NAME} -U $USERNAME -P $PASSWORD -Q "SET NOCOUNT ON; SELECT TOP 10 * FROM sys.dm_exec_sessions"

sqlcmd -S $SERVERINSTANCE -d ${DATABASE_NAME} -U $USERNAME -P $PASSWORD -Q "SET NOCOUNT ON; SELECT TOP 10 * FROM sys.dm_exec_query_stats"



# Optimize queries or add indexes

sqlcmd -S $SERVERINSTANCE -d $database_name -U $USERNAME -P $databasePASSWORD_password -Q "SET NOCOUNT ON; EXEC sp_helpindex '${TABLE_NAME}'"

sqlcmd -S $SERVERINSTANCE -d $database_name -U $USERNAME -P $PASSWORD -Q "SET NOCOUNT ON; CREATE INDEX ${INDEX_NAME} ON ${TABLE_NAME} (${COLUMN_NAME})"



# Exit script

exit 0


```


High number of processes blocked in SQLServer

Overview

Parameters

Debug

Next Step

2. Check the memory usage of the SQLServer process.

3. Check the blocked processes in SQLServer.

4. Check the top queries that are causing the blocking.

5. Check the current state of the SQLServer.

6. Check the SQLServer error logs for any relevant messages.

Repair

Define input parameters

Check for inefficient queries

Kill any long-running or blocking processes manually or automatically using a script.

Check the database for inefficiencies in queries or indexes that could potentially cause blocking. Optimize the queries or add indexes to improve performance.

Learn more

Related Runbooks

Tomcat Large Number of Suspended Threads Incident.

Tomcat High JDBC Connection Pool Usage Incident.

SQL Server Deadlock Incident

Postgresql high rate statement timeout incident.

Support