如何找出为什么spid的状态被暂停? spid正在等待什么资源? [英] How to find out why the status of a spid is suspended? What resources the spid is waiting for?

问题描述

我运行EXEC sp_who2 78，并得到以下结果:

如何查找其状态为何被暂停?

How can I find why its status is suspended?

基于昂贵的查询，此过程很繁琐INSERT.一个很大的SELECT，它从多个表中获取数据并将大约3-4百万行写入另一个表中.

This process is a heavy INSERT based on an expensive query. A big SELECT that gets data from several tables and write some 3-4 millions rows to a different table.

没有锁/锁.

链接到的waittype是CXPACKET.我能理解，因为您可以在下面的图片中看到9个78.

The waittype it is linked to is CXPACKET. which I can understand because there are 9 78s as you can see on the picture below.

与我有关，我真正想知道的是为什么SPID 78的数字1被暂停.

What concerns me and what I really would like to know is why the number 1 of the SPID 78 is suspended.

我了解到，当SPID的状态被挂起时，这意味着该进程正在等待资源，并且它将在获取其资源后恢复运行.

I understand that when the status of a SPID is suspended it means the process is waiting on a resource and it will resume when it gets its resource.

如何找到有关此的更多详细信息?什么资源?为什么不可用?

How can I find more details about this? what resource? why is it not available?

我在下面使用了大量代码及其变体，但是我还能做些什么来找出为什么SPID被挂起的原因吗?

I use a lot the code below, and variations therefrom, but is there anything else I can do to find out why the SPID is suspended?

select * 
from sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
where r.session_id = 78

我已经使用了 sp_whoisactive .对于这个特殊的spid78，我得到的结果如下:(分为3张图片以适合屏幕)

I already used sp_whoisactive. The result I get for this particular spid78 is as follow: (broken into 3 pics to fit screen)

推荐答案

SUSPENDED: 这意味着该请求当前处于非活动状态，因为它正在等待资源.该资源可以是用于读取页面的I/O，WAITit可以是网络上的通信，或者它正在等待锁或闩锁.等待任务完成后，它将变为活动状态.例如，如果查询已发布I/O请求以读取完整表tblStudents的数据，则该任务将被挂起，直到I/O完成. I/O完成后(内存中有表tblStudents的数据可用)，查询将移至RUNNABLE队列.

SUSPENDED: It means that the request currently is not active because it is waiting on a resource. The resource can be an I/O for reading a page, A WAITit can be communication on the network, or it is waiting for lock or a latch. It will become active once the task it is waiting for is completed. For example, if the query the has posted a I/O request to read data of a complete table tblStudents then this task will be suspended till the I/O is complete. Once I/O is completed (Data for table tblStudents is available in the memory), query will move into RUNNABLE queue.

因此，如果正在等待，请检查wait_type列以了解其正在等待什么，并根据wait_time进行故障排除.

So if it is waiting, check the wait_type column to understand what it is waiting for and troubleshoot based on the wait_time.

我已经开发出以下过程来帮助我解决这个问题，其中包括WAIT_TYPE.

I have developed the following procedure that helps me with this, it includes the WAIT_TYPE.

use master
go

CREATE PROCEDURE [dbo].[sp_radhe] 

AS
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT es.session_id AS session_id
,COALESCE(es.original_login_name, '') AS login_name
,COALESCE(es.host_name,'') AS hostname
,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch
,es.status
,COALESCE(er.blocking_session_id,0) AS blocked_by
,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype
,COALESCE(er.wait_time,0) AS waittime
,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype
,COALESCE(er.wait_resource,'') AS waitresource
,coalesce(db_name(er.database_id),'No Info') as dbid
,COALESCE(er.command,'AWAITING COMMAND') AS cmd
,sql_text=st.text
,transaction_isolation =
    CASE es.transaction_isolation_level
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'Read Uncommitted'
    WHEN 2 THEN 'Read Committed'
    WHEN 3 THEN 'Repeatable'
    WHEN 4 THEN 'Serializable'
    WHEN 5 THEN 'Snapshot'
END
,COALESCE(es.cpu_time,0) 
    + COALESCE(er.cpu_time,0) AS cpu
,COALESCE(es.reads,0) 
    + COALESCE(es.writes,0) 
    + COALESCE(er.reads,0) 
    + COALESCE(er.writes,0) AS physical_io
,COALESCE(er.open_transaction_count,-1) AS open_tran
,COALESCE(es.program_name,'') AS program_name
,es.login_time
FROM sys.dm_exec_sessions es
    LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
    LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
    LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid
    LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id
    LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
where es.is_user_process = 1 
  and es.session_id <> @@spid
ORDER BY es.session_id

end 

下面的查询还可以通过显示spid正在等待的资源来显示基本信息，以在spid暂停时提供帮助.

This query below also can show basic information to assist when the spid is suspended, by showing which resource the spid is waiting for.

SELECT  wt.session_id, 
    ot.task_state, 
    wt.wait_type, 
    wt.wait_duration_ms, 
    wt.blocking_session_id, 
    wt.resource_description, 
    es.[host_name], 
    es.[program_name] 
FROM  sys.dm_os_waiting_tasks  wt  
INNER  JOIN sys.dm_os_tasks ot ON ot.task_address = wt.waiting_task_address 
INNER JOIN sys.dm_exec_sessions es ON es.session_id = wt.session_id 
WHERE es.is_user_process =  1 

请以下面的图片为例:

这篇关于如何找出为什么spid的状态被暂停? spid正在等待什么资源?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！