Concepts：Request 和 Task

當SQL Server 引擎接收到用戶發出的查詢請求時，SQL Server執行優化器將查詢請求（Request）和Task綁定，并為Task分配一個Workder，SQL Server申請操作系統的進程（Thread）來執行Worker。如果以并行的方式執行Request，SQL Server根據Max DOP（Maximum Degree Of Parallelism） 配置選項創建新的Child Tasks，SQL Server將Request和多個Task綁定；例如，如果Max DOP=8，那么將會存在 1個Master Task和 8 個Child Tasks。每個Task綁定到一個Worker中，SQL Server引擎將分配相應數量的Worker來執行Tasks。

一，查看正在執行的Request

使用 sys.dm_exec_requests 返回正在執行的查詢請求（Request）關聯的查詢腳本，阻塞和資源消耗。

1，查看SQL Server正在執行的查詢語句

• sql_handle，statement_start_offset，statement_end_offset ，能夠用于查看正在執行的查詢語句；

• 字段plan_handle，用于查看查詢語句的執行計劃；

• 字段 command 用于表示正在被處理的Command的當前的類型：SELECT，INSERT，UPDATE，DELETE，BACKUP LOG ，BACKUP DATABASE，DBCC，FOR；

2，查看阻塞（Block）的語句

• 字段 wait_type：如果Request正在被阻塞，字段wait_type 返回當前的Wait Type

• 字段 last_wait_type：上一次阻塞的Wait Type

• 字段 wait_resource：當前阻塞的Request正在等待的資源

• 字段 blocking_session_id ：將當前Request阻塞的Session

3，內存，IO，CPU消耗統計

• 字段 granted_query_memory: 授予內存的大小，Number of pages allocated to the execution of a query on the request

• 字段 cpu_time，total_elapsed_time ：消耗的CPU時間和總的消耗時間

• 字段 reads，writes，logical_reads：物理Read，邏輯Write 和邏輯Read的次數

二，查看SQL Server 當前正在執行的SQL查詢語句

在進行故障排除時，使用DMV：sys.dm_exec_requests 查看SQL Server當前正在執行的查詢語句：

select  r.session_id,
        r.blocking_session_id as blocking,
        r.wait_type as current_wait_type,
        r.wait_resource, 
        r.last_wait_type,
        r.wait_time/1000 as wait_s,
        r.status,
        r.command,
        r.cpu_time,r.reads,r.writes,r.logical_reads,
        r.total_elapsed_time,r.start_time,r.database_id,        substring(  st.text, 
                    r.statement_start_offset/2+1,
                    ( case when r.statement_end_offset = -1 
                                then len(convert(nvarchar(max), st.text))                           else (r.statement_end_offset - r.statement_start_offset)/2
                      end 
                    )
                ) as individual_query        --,db_name(r.database_id) as dbname,r.percent_complete,r.estimated_completion_time,r.granted_query_memoryfrom sys.dm_exec_requests router APPLY sys.dm_exec_sql_text(r.sql_handle) as stwhere ((r.wait_type<>'MISCELLANEOUS' and r.wait_type <> 'DISPATCHER_QUEUE_SEMAPHORE' ) or r.wait_type is null)    and r.session_id>50
    and r.session_id<>@@spidorder by r.session_id asc

1，在故障排除時，可以過濾掉一些無用的wait type 和當前Session：

• @@SPID 表示當前的spid，一般來說，SPID<=50是system session，SPID>50的是User Session；

• WaitType 為'MISCELLANEOUS' 時，不用于標識任何有效的Wait，僅僅作為默認的Wait；

• WaitType 為‘DISPATCHER_QUEUE_SEMAPHORE’時，表示當前的Thread在等待處理更多的Work，如果Wait Time增加，說明Thread調度器（Dispatcher）非常空閑；

• 關于WaitType ，請查看 The SQL Server Wait Type Repository；

2，查看request執行的SQL查詢語句

sql_handle 字段表示當前查詢語句的句柄（handle），將該字段傳遞給sys.dm_exec_sql_text函數，將獲取Request執行的SQL語句，SQL Server對某些包含常量的查詢語句自動參數化（“Auto-parameterized”），獲取的SQL 查詢語句格式如下，SQL Server在查詢語句的開頭增加參數聲明：

(@P1 int,@P2 int,@P3 datetime2(7),@P4 datetime2(7))WITH CategoryIDs      AS (SELECT B.CategoryID,
  .....

兩個字段：stmt_start和stmt_end，用于標識參數聲明的開始和結尾的位置，使用這兩個字段，將參數聲明剝離，返回SQL Server執行的查詢語句。

3，阻塞

字段 blocking_session_id ：阻塞當前Request的Session，但排除0，-2，-3，-4 這四種ID值：

• If this column is 0, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified).

• -2 = The blocking resource is owned by an orphaned distributed transaction.                    

• -3 = The blocking resource is owned by a deferred recovery transaction.                    

• -4 = Session ID of the blocking latch owner could not be determined at this time because of internal latch state transitions.

三，查看SQL Server實例中活動的Task

使用DMV：sys.dm_os_tasks 查看當前實例中活動的Task

1，字段 task_state，標識Task的狀態

• PENDING: Waiting for a worker thread.

• RUNNABLE: Runnable, but waiting to receive a quantum.

• RUNNING: Currently running on the scheduler.

• SUSPENDED: Has a worker, but is waiting for an event.

• DONE: Completed.

• SPINLOOP: Stuck in a spinlock.

2，掛起的IO（Pending）

• pending_io_count

• pending_io_byte_count

• pending_io_byte_average

3，關聯的Request和Worker（associated）

• request_id ： ID of the request of the task.

• worker_address ：Memory address of the worker that is running the task. NULL = Task is either waiting for a worker to be able to run, or the task has just finished running.

4, Task Hierarchy

• task_address: Memory address of the object.

• parent_task_address: Memory address of the task that is the parent of the object.

5，監控并發Request（Monitoring parallel requests）

For requests that are executed in parallel, you will see multiple rows for the same combination of (<session_id>, <request_id>).

SELECT
    session_id,
    request_id,
    task_state,
    pending_io_count,
    pending_io_byte_count,
    pending_io_byte_average,
    scheduler_id,
    context_switches_count,
    task_address,
    worker_address,
    parent_task_addressFROM sys.dm_os_tasksORDER BY session_id, request_id;

或利用 Task Hierarchy來查詢

select 
    tp.session_id, 
    tp.task_state as ParentTaskState,
    tc.task_state as ChildTaskStatefrom sys.dm_os_tasks tpinner join sys.dm_os_tasks tc    on tp.task_address=tc.parent_task_address

四，等待資源的Task（waiting）

使用DMV：sys.dm_os_waiting_tasks 查看系統中正在等待資源的Task

• waiting_task_address: Task that is waiting for this resouce.

• blocking_task_address: Task that is currently holding this resource

• resource_description: Description of the resource that is being consumed.  參考sys.dm_os_waiting_tasks (Transact-SQL)

在對阻塞進行故障排除時，查看Block 和 爭用的資源：

select wt.waiting_task_address,
    wt.session_id,    --Wait and Resource    wt.wait_duration_ms,
    wt.wait_type,
    wt.resource_address,
    wt.resource_description,
    wt.blocking_task_address,
    wt.blocking_session_idfrom sys.dm_os_waiting_tasks wt

五，使用dbcc inputbuffer(spid)獲取spid最后一次執行的SQL語句

dbcc inputbuffer(spid)

Appendix:

引用《How to isolate the current running commands in SQL Server》，該文章描述了如何分離Request執行的查詢語句：

 View Code