SQL Server 查詢請求

當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   db_name(r.database_id) as db_name
        ,s.group_id
        ,r.session_id
        ,r.blocking_session_id as blocking
        ,s.login_name
        ,r.wait_type as current_wait_type
        ,r.wait_resource
        ,r.last_wait_type
        ,r.wait_time/1000 as wait_s
        ,r.status as request_status
        ,r.command
        ,r.cpu_time/1000 as cpu_time_s
        ,r.reads
        ,r.writes
        ,r.logical_reads
        ,r.total_elapsed_time/1000 as total_elapsed_s
        ,r.start_time
        ,s.status as session_status
        ,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
                ,s.program_name
                ,s.host_name
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s 
    on r.session_id=s.session_id
outer APPLY sys.dm_exec_sql_text(r.sql_handle) as st
where ((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<>@@spid
order by r.logical_reads desc

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；

有一些wait type是僅供內部使用的(Internal use only)，通常是系統請求，可以把這些wait type 過濾掉：

select   db_name(r.database_id) as db_name
        ,s.group_id
        ,r.session_id
        ,r.blocking_session_id as blocking
        ,s.login_name
        ,r.wait_type as current_wait_type
        ,r.wait_resource
        ,r.last_wait_type
        ,r.wait_time/1000 as wait_s
        ,r.status as request_status
        ,r.command
        ,r.cpu_time/1000 as cpu_time_s
        ,r.reads
        ,r.writes
        ,r.logical_reads
        ,r.total_elapsed_time/1000 as total_elapsed_s
        ,r.start_time
        ,s.status as session_status
        ,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
                ,s.program_name
                ,s.host_name
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s 
    on r.session_id=s.session_id
outer APPLY sys.dm_exec_sql_text(r.sql_handle) as st
where r.session_id>50
    and r.session_id<>@@spid
    and r.wait_type not in (
        N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
        N'CHKPT', N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC', N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
        N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT',
        N'XE_LIVE_TARGET_TVF',N'PWAIT_DIRECTLOGCONSUMER_GETNEXT'
    )
order by r.session_id asc

View Code

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_address
FROM sys.dm_os_tasks
ORDER BY session_id, request_id;

或利用 Task Hierarchy來查詢

select 
    tp.session_id, 
    tp.task_state as ParentTaskState,
    tc.task_state as ChildTaskState
from sys.dm_os_tasks tp
inner 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_id
from sys.dm_os_waiting_tasks wt

DMV： sys.dm_os_wait_stats 查看系統中wait type的統計數據：

有一些wait type是僅供內部使用的(Internal use only)，在查看wait的統計數據時，可以忽略掉：

SELECT TOP(20) wait_type
             , wait_time = CAST(wait_time_ms / 1000. AS DECIMAL(18,4))
             , wait_resource = CAST((wait_time_ms - signal_wait_time_ms) / 1000. AS DECIMAL(18,4))
             , wait_signal = CAST(signal_wait_time_ms / 1000. AS DECIMAL(18,4))
             , wait_time_percent = CAST(100. * wait_time_ms / NULLIF(SUM(wait_time_ms) OVER (), 0) AS DECIMAL(18,2))
             , waiting_tasks_count
             , max_wait_time = CAST(max_wait_time_ms / 1000. AS DECIMAL(18,4))
             , avg_wait = CAST(wait_time_ms / 1000. / waiting_tasks_count AS DECIMAL(18,4))
             , avg_wait_resource = CAST((wait_time_ms - signal_wait_time_ms) / 1000. / waiting_tasks_count AS DECIMAL(18,4))
             , avg_wait_signal = CAST(signal_wait_time_ms / 1000. / waiting_tasks_count AS DECIMAL(18,4))
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0
    AND wait_time_ms > 0
    AND wait_type NOT IN (
        N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
        N'CHKPT', N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC', N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
        N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT',
        N'XE_LIVE_TARGET_TVF',N'PWAIT_DIRECTLOGCONSUMER_GETNEXT'
    )
ORDER BY wait_time_ms DESC

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

dbcc inputbuffer(spid)

六，休眠會話（Sleeping Session）

休眠的會話（Sleeping Session）表示當前的會話處于休眠狀態，該會話沒有運行任何Request。如果一個Session沒有運行任何Request，那么該Session為什么不結束，而要保持休眠狀態？

休眠會話雖然沒有運行任何Request，但是，它和SQL Server的連接并沒有斷開，出現這種情況的可能原因主要有兩個：

Session中存在沒有提交的事務；
Session的中所有事務都已經提交，僅僅是沒有運行任何Request。

1，查看休眠會話開啟的事務

SELECT db_name(dt.database_id) as database_name,
    dt.transaction_id,
    st.session_id,
    dt.database_transaction_begin_time,
    CASE dt.database_transaction_type
        WHEN 1 THEN 'Read/write transaction'
        WHEN 2 THEN 'Read-only transaction'
        WHEN 3 THEN 'System transaction'
    END database_transaction_type,
    CASE dt.database_transaction_state
        WHEN 1 THEN 'The transaction has not been initialized.'
        WHEN 3 THEN 'The transaction has been initialized but has not generated any log recorst.'
        WHEN 4 THEN 'The transaction has generated log recorst.'
        WHEN 5 THEN 'The transaction has been prepared.'
        WHEN 10 THEN 'The transaction has been committed.'
        WHEN 11 THEN 'The transaction has been rolled back.'
        WHEN 12 THEN 'The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted'
    END database_transaction_state,
    dt.database_transaction_log_record_count,
    dt.database_transaction_log_bytes_used,
    dt.database_transaction_log_bytes_reserved 
FROM   sys.dm_tran_database_transactions dt
INNER JOIN sys.dm_tran_session_transactions st
     ON st.transaction_id = dt.transaction_id
inner join sys.dm_exec_sessions s 
    on st.session_id=s.session_id
where s.status='sleeping'

View Code

2，查看休眠會話最后執行的TSQL語句

使用DBCC InputBuffer查看休眠會話最后執行的TSQL語句

dbcc inputbuffer(sleeping_session_id)

3，休眠會話可能產生阻塞

雖然休眠會話占用的資源特別少，但是，如果休眠會話開啟的事務不能及時關閉，在某些特定情況下，不僅會阻止事務日志的截斷（backup log 能夠截斷Transaction log，減少日志文件的增長，避免硬盤空間耗盡），甚至會阻塞其他查詢。因此，在產品環境中，應當避免出現休眠會話。在開發程序時保證：打開一個連接，執行完相應的查詢語句之后，及時提交事務，關閉連接。

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

SELECT r.[statement_start_offset],   
  r.[statement_end_offset],  
  CASE   
     WHEN r.[statement_start_offset] > 0 THEN  
        --The start of the active command is not at the beginning of the full command text 
        CASE r.[statement_end_offset]  
           WHEN -1 THEN  
              --The end of the full command is also the end of the active statement 
              SUBSTRING(st.TEXT, (r.[statement_start_offset]/2) + 1, 2147483647) 
           ELSE   
              --The end of the active statement is not at the end of the full command 
              SUBSTRING(st.TEXT, (r.[statement_start_offset]/2) + 1, (r.[statement_end_offset] - r.[statement_start_offset])/2)   
        END  
     ELSE  
        --1st part of full command is running 
        CASE r.[statement_end_offset]  
           WHEN -1 THEN  
              --The end of the full command is also the end of the active statement 
              RTRIM(LTRIM(st.[text]))  
           ELSE  
              --The end of the active statement is not at the end of the full command 
              LEFT(st.TEXT, (r.[statement_end_offset]/2) +1)  
        END  
     END AS [executing statement],  
  st.[text] AS [full statement code]  
FROM sys.[dm_exec_requests] r 
CROSS APPLY sys.[dm_exec_sql_text](r.[sql_handle]) st  
WHERE r.session_id > 50  
ORDER BY r.[session_id]