Explain the status values of SQL Server: runnable, running, suspended, sleeping

Time:2020-10-25

During the management, monitoring and performance tuning of SQL server, we may execute the following SQL instructions to observe the status of SQL Server:

SELECT * FROM sys.sysprocesses;

EXEC sp_who2;
SELECT sqltext.TEXT, req.session_id, req.status, req.command, req.cpu_time, req.blocking_session_id, req.total_elapsed_time
FROM sys.dm_exec_requests req (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

As shown in Figure 1, there is a status field in each process in operation.
The values of status field include: pending, runnable, running, suspended, sleeping, dormant, background, spinlock

This paper is a reference SQLAuthority.com This article [1], together with my own practical experience, explains what these values in the status field mean.

status
Figure 1 value of status field

“Pending”, representing the process, has neither thread nor CPU available, and is waiting for both system resources at the same time.

Pending
Figure 2 pending

“Runnable” stands for the process. Thread is available, but no CPU is available, so it is waiting for the CPU system resource.

Runnable
Figure 3 runnable

“Running” stands for the process, with thread available and CPU available.

Running
Figure 4 running

“Suspended” means that this process is “waiting” for other processes to run. The waiting system resources may be disk I / O or database lock.

Editor’s note: if the select executed by this process does not have the nolock keyword, and other processes are in the process of “trading” or writing (lock will be added), the selected process will be in the “suspended” state.

Suspended
Figure 5 suspended

“Sleeping”, which means the process, is not doing anything at present and is waiting for further instructions.

Sleeping
Figure 6 sleeping

“Dormant” means that SQL server is resetting the process.

Dormant
Figure 7 dormant

“Background” means that the process is executing in the background of SQL server. Even if you see a lot of “background” processes running, don’t worry.

Background
Figure 8 background

spin lock essentially means that query is in kind of running mode where it is busy waiting in cpu for its own turn.

Spinlock
Figure 9 spinlock

The following figure 10 shows the actual cases encountered by the printer. After reporting, it was found that the system was stuck and could not move. The compiler went to the SQL server to check and found that there were a large number of insert instructions in the “suspended” state, which represented that these processes were “waiting” for other processes to run. The waiting system resources might be disk I / O or database lock.

Later, it was found that all the “suspended” processes that were stuck were sessions_ The “runnable” process with ID 70.

實際案例
Figure 10 actual case

 

—————————————————————————————————————————————-
Reference articles:

[1] Sleeping vs Suspended Process
https://blog.sqlauthority.com/2020/09/10/sleeping-vs-suspended-process-sql-in-sixty-seconds-122/?fbclid=IwAR1hbs8IWBER9orsnA72lvKqD0S5mPg6ifcfUh5L4I0Ah_4WiglNswmGRKw

[2] Find Currently Running Query
https://blog.sqlauthority.com/2009/01/07/sql-server-find-currently-running-query-t-sql/
—————————————————————————————————————————————-