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 desc1，在故障排除时，可以过滤掉一些无用的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 asc2，查看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 wtDMV： 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 (), ) 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 >
AND wait_time_ms >
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)相关文章