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


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.

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.

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.

Figure 3 runnable

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

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.

Figure 5 suspended

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

Figure 6 sleeping

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

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.

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.

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

[2] Find Currently Running Query