辨識具有最大時間差的執行緒

我的 SQL Server 數據庫中有許多並行執行的查詢。我需要找出最長執行緒和最短執行緒的時間差異很大的查詢（不包括協調執行緒）。事實上，我需要找到差異最大的前 10 個此類查詢（來自在 1 小時內執行的查詢）。如何在 SQL Server 中跟踪此資訊？

注意：我使用的是 SQL Server 2012。

參考

1. SQL Server 2012 的並行執行緒使用資訊 - Joe Sack
2. 並行執行計劃 - 分支和執行緒 - Paul White

需要注意的一點是，查詢執行並非對所有運算符並行進行。它取決於每個操作員的成本以及您對“並行成本門檻值”和“最大並行度”的設置。

從理論上講，您正在查看執行緒之間執行時差異最大的操作（執行計劃中的操作員）。

根據您收集的數量，我建議您創建一些索引以加快速度。如果您在執行我的程式碼時遇到任何問題，請告訴我。

/*============================================================================
PerThreadcollection.sql
Written by Taiob M Ali
SqlWorldWide.com

This script will show where queries (certain tasks within execution plan) are running 
in parallel and difference in time for the longest thread and the shortest thread is high (excluding co-ordinator thread).  

Instruction to run this script
--------------------------------------------------------------------------

--You will have to adjust @howManyTimes Variable values based on your requirements
--You will have to adjust @WaitSec Variable values based on your requirements

============================================================================*/

USE [DbaDB]
GO
--Creating table to hold raw data
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [DbaDB].[dbo].[PerThreadcollection](
   [timestamp] [datetime] NOT NULL,
   [session_id] [smallint] NOT NULL,
   [status] [nvarchar](30) NOT NULL,
   [command] [nvarchar](32) NOT NULL,
   [blocking_session_id] [smallint] NULL,
   [wait_type] [nvarchar](60) NULL,
   [exec_context_id] [int] NULL,
   [task_state] [nvarchar](60) NULL,
   [text] [nvarchar](max) NULL
) ON [PRIMARY]

GO

--Collecting raw data
DECLARE @howManyTimes int=0
DECLARE @WaitSec char(2) = '30'
DECLARE @Delay char(8)
SET @Delay = '00:' + '00:' +@WaitSec

WHILE(@howManyTimes<101)
BEGIN
INSERT INTO [DbaDB].[dbo].[PerThreadcollection]

SELECT 
       GETDATE(),
      er.session_id, 
      er.status, 
      er.command, 
      er.blocking_session_id, 
      er.wait_type, 
      ot.exec_context_id, 
      ot.task_state, 
      st.text 
FROM   sys.dm_exec_requests er 
      JOIN sys.dm_os_tasks ot 
        ON ( er.session_id = ot.session_id ) 
      CROSS apply sys.Dm_exec_sql_text(er.sql_handle) st 
WHERE  er.session_id IN (SELECT session_id 
                        FROM   sys.dm_os_tasks 
                        GROUP  BY session_id 
                        HAVING Count(exec_context_id) > 1) 
WAITFOR DELAY @Delay
SET @howManyTimes = @howManyTimes + 1
END
GO

;WITH MinExecution (sessionID, execContextId, queryText, mintime) 
AS
(
 Select session_id, exec_context_id, [text], MIN(timestamp) AS [MinTime]
 FROM [DbaDB].[dbo].[PerThreadcollection]
 WHERE exec_context_id <>0
 GROUP BY session_id, exec_context_id, [text]
),

MaxExecution (sessionID, execContextId, queryText, maxtime) 
AS
(
 SELECT session_id, exec_context_id, [text], MAX(timestamp) AS [MaxTime]
 FROM [DbaDB].[dbo].[PerThreadcollection]
 WHERE exec_context_id <>0
 GROUP BY session_id, exec_context_id, [text]
)

SELECT sessionId, queryText, (MAX(duration)-MIN(duration)) AS [deltaInSeconds] from 
(
SELECT 
 mi.sessionID, mi.execContextId,mi.queryText ,DATEDIFF(ss, minTime, maxTime) AS [duration]
FROM MinExecution AS mi
JOIN MaxExecution AS mx
ON mi.sessionID=mx.sessionID
 AND mi.execContextId=mx.execContextId
 AND mi.queryText=mx.queryText) as t
GROUP BY sessionId, queryText 
ORDER BY [deltaInSeconds] DESC

GO

您想要採用的方法（即最短和最長執行緒之間的最大差異）與根據開始時間查找最長執行查詢的方法之間的（實際）區別是什麼？唯一的區別似乎是執行緒 1 啟動所需的時間。因此，假設就執行緒 1 啟動所需的時間而言，查詢之間相當一致（即差異最小），那麼該時間量將不再是確定最長執行查詢的相關因素。

另外，最短和最長執行緒之間的“時間差異”到底是什麼意思？您是指從開始時間到結束時間（即使執行緒“暫停”或“睡眠”也會增加）還是僅活動的處理時間（考慮到在非執行狀態下花費的時間）？實際上，這種區別可能並不重要，因為似乎不可能通過查詢獲得任何一個。只有少數 DMV 甚至具有“context_id”（或類似）欄位，並且沒有一個報告start_timeor elapsed_time：

SELECT OBJECT_NAME(sac.[object_id]), sac.[name]
FROM   sys.all_columns sac
WHERE  sac.[name] LIKE N'%exec%context%'
AND    OBJECT_NAME(sac.[object_id]) NOT LIKE N'dm[_]pdw[_]%'
ORDER BY OBJECT_NAME(sac.[object_id]), sac.[name];

回報：

dm_db_task_space_usage   exec_context_id
dm_os_tasks              exec_context_id
dm_os_waiting_tasks      blocking_exec_context_id
dm_os_waiting_tasks      exec_context_id
dm_tran_locks            request_exec_context_id

此外，在問題中發布的兩個參考連結中的任何一個中都沒有跡象表明每個執行緒的時間被跟踪，或者至少被報告。但同樣，即使有，我也看不出這會給你任何真正有意義的資訊來做出決定。

似乎您最好的選擇是從以下內容開始查找在單個查詢/批處理基礎上花費時間最長的查詢（好吧，查詢批處理）：

SELECT qstat.*
FROM   sys.dm_exec_query_stats qstat
ORDER BY qstat.total_worker_time DESC;

排序依據total_worker_time而不是total_elapsed_time應該考慮花費等待的時間（即不主動執行）。

引用自：https://dba.stackexchange.com/questions/150460