SQL 如何找出 spid 的状态被暂停的原因？spid 在等待什么资源？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22038695/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find out why the status of a spid is suspended? What resources the spid is waiting for?
提问by Marcello Miorelli
I run EXEC sp_who2 78and I get the following results:
我运行EXEC sp_who2 78并得到以下结果：
How can I find why its status is suspended?
我如何找到其状态被暂停的原因？
This process is a heavy INSERTbased on an expensive query. A big SELECTthat gets data from several tables and write some 3-4 millions rows to a different table.
这个过程是一个繁重的INSERT基于昂贵的查询。一个大SELECT从几个表中获取数据，并写一些3-4百万行不同的表。
There are no locks/ blocks.
没有锁/块。
The waittypeit is linked to is CXPACKET. which I can understand because there are 9 78s as you can see on the picture below.
将waittype它与IS CXPACKET。我可以理解，因为有 9 个 78，如下图所示。
What concerns me and what I really would like to know is why the number 1 of the SPID78 is suspended.
令我担忧且我真正想知道的是，为什么SPID78号的 1 号被停赛。
I understand that when the status of a SPIDis suspended it means the process is waiting on a resource and it will resume when it gets its resource.
我知道当 a 的状态SPID被挂起时，这意味着进程正在等待资源，并且在获得资源时将恢复。
How can I find more details about this? what resource? why is it not available?
我如何才能找到有关此的更多详细信息？什么资源？为什么不可用？
I use a lot the code below, and variations therefrom, but is there anything else I can do to find out why the SPIDis suspended?
我经常使用下面的代码及其变体，但是我还能做些什么来找出SPID暂停的原因吗？
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
I already used sp_whoisactive. The result I get for this particular spid78 is as follow: (broken into 3 pics to fit screen)
我已经使用过sp_whoisactive。我为这个特定的 spid78 得到的结果如下：（分成 3 张图片以适应屏幕）
回答by Marcello Miorelli
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.
SUSPENDED：表示请求当前处于非活动状态，因为它正在等待资源。资源可以是用于读取页面的 I/O，A WAIT 可以是网络上的通信，或者它正在等待锁定或闩锁。一旦它等待的任务完成，它就会被激活。例如，如果查询已经发布了一个 I/O 请求来读取一个完整表 tblStudents 的数据，那么这个任务将被挂起，直到 I/O 完成。一旦 I/O 完成（表 tblStudents 的数据在内存中可用），查询将移动到 RUNNABLE 队列中。
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 列以了解它在等待什么并根据wait_time 进行故障排除。
I have developed the following procedure that helps me with this, it includes the WAIT_TYPE.
我开发了以下程序来帮助我解决这个问题，它包括 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
This query below also can show basic information to assist when the spid is suspended, by showing which resource the spid is waiting for.
下面的查询还可以通过显示 spid 正在等待的资源来显示基本信息，以在 spid 挂起时提供帮助。
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
Please see the picture below as an example:
请看下面的图片作为例子：
回答by steoleary
I use sp_whoIsActive to look at this kind of information as it is a ready made free tool that gives you good information for troubleshooting slow queries:
我使用 sp_whoIsActive 查看此类信息，因为它是一个现成的免费工具，可为您提供用于解决慢查询问题的良好信息：
How to Use sp_WhoIsActive to Find Slow SQL Server Queries
如何使用 sp_WhoIsActive 查找慢速 SQL Server 查询
With this, you can get the query text, the plan it is using, the resource the query is waiting on, what is blocking it, what locks it is taking out and a whole lot more.
有了这个，您可以获得查询文本、它正在使用的计划、查询正在等待的资源、阻塞它的东西、它取出的锁等等。
Much easier than trying to roll your own.
比尝试自己动手要容易得多。
回答by Moises Conejo
You can solve it with to ways:
您可以通过以下方式解决它：
- Fix the cluster index.
- Use temporal tables to get a part of the all table and work with it.
- 修复簇索引。
- 使用时态表获取所有表的一部分并使用它。
I have the same problem with a table with a 400,000,000 rows, and use a temporal tables to get a part of it and then i use my filters and inners because change the index was not a option.
我对一个有 400,000,000 行的表有同样的问题，并使用一个临时表来获取它的一部分，然后我使用我的过滤器和内部，因为更改索引不是一个选项。
Some example:
一些例子：
--
--this is need be cause DECLARE @TEMPORAL are not well for a lot of data.
CREATE TABLE #TEMPORAL
(
ID BIGINT,
ID2 BIGINT,
DATA1 DECIMAL,
DATA2 DECIMAL
);
WITH TABLE1 AS
(
SELECT
L.ID,
L.ID2,
L.DATA
FROM LARGEDATA L
WHERE L.ID = 1
), WITH TABLE2 AS
(
SELECT
L.ID,
L.ID2,
L.DATA
FROM LARGEDATA L
WHERE L.ID = 2
) INSERT INTO #TEMPORAL SELECT
T1.ID,
T2.ID,
T1.DATA,
T2.DATA
FROM TABLE1 T1
INNER JOIN TABLE2 T2
ON T2.ID2 = T2.ID2;
--
--this take a lot of resources proces and time and be come a status suspend, this why i need a temporal table.
SELECT
*
FROM #TEMPORAL T
WHERE T.DATA1 < T.DATA2
--
--IMPORTANT DROP THE TABLE.
DROP TABLE #TEMPORAL
