MSSQL2005性能调优--DBA需掌握系统字典sql_mssql中command下的vdi_client

select spid ,a.status , hostname ,cpu ,physical_io,blocked ,plan_handle, qt.text ,[Query] = SUBSTRING (qt.text,
b.statement_start_offset/2,
(CASE WHEN b.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE b.statement_end_offset
END - b.statement_start_offset)/2) ,cmd , b.wait_type, object_name(qt.objectid,qt.dbid) ObjectName
,a.dbid,a.request_id
,convert(sysname, rtrim(loginame)) as loginname ,spid as 'spid_sort' ,program_name
, b.start_time
from master.dbo.sysprocesses a, sys.dm_exec_requests b CROSS APPLY sys.dm_exec_sql_text(b.sql_handle)as qt
where b.session_id = a.spid and a.status not in ('sleeping','background') order by physical_io desc
--检测SQL查询的效率语法【对IO和Time对SQL执行进行统计】：
--显示执行计划(CTRL+L)，可查看查询语句使用了哪些索引
SET SHOWPLAN_TEXT ON
GO
--显示表扫描情况
SET STATISTICS IO ON
go
SET STATISTICS TIME ON
go
--注意：在检测之前要清理缓存，因为当我们执行SQL语句的时候查出的数据会在数据库中进行缓存，重新查询会返回缓存中的信息。
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
--sql 2005性能调优
--SQL Server在运行一段时间，随着数据的积累，SQL运行效率会逐步降低，为了使用业务系统正常动作，经常IT部门需要花高价请SQL调优专家来解决。其实调优也不复杂，主要是找到影响效率的SQL，然后对症下药，这里给出几个技巧，相信对大家非常实用。
--1、检查SQL阻塞原因
select blocking_session_id, wait_duration_ms, session_id
from sys.dm_os_waiting_tasks
where blocking_session_id is not null
--显示累计最常重新编译的25个运行计划
select top 25 plan_generation_num, SUBSTRING(qt.text,qs.statement_start_offset/2+1, (case when qs.statement_end_offset = -1 then DATALENGTH(qt.text) else qs.statement_end_offset end -qs.statement_start_offset)/2 + 1) as stmt_executing, qt.text, execution_count, sql_handle, dbid, db_name(dbid) DBName, objectid, object_name(objectid,dbid) ObjectName from sys.dm_exec_query_stats as qs Cross apply sys.dm_exec_sql_text(sql_handle) qt where plan_generation_num >1 order by plan_generation_num
--显示累计最消耗 CPU 时间的前50个运行计划
SELECT TOP 50 total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000. as [平均消耗CPU 时间(ms)],
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(case when qs.statement_end_offset = -1 then DATALENGTH(qt.text)
else qs.statement_end_offset end -qs.statement_start_offset)/2 + 1) as [使用CPU的语法], qt.text [完整语法],
qt.dbid, dbname=db_name(qt.dbid), qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt ORDER BY total_worker_time DESC
--显示锁定与被锁定之间的链状关系
select t1.resource_type as [信息锁定类型] ,db_name(resource_database_id) as [数据库名]
,t1.resource_associated_entity_id as [锁定的对象] ,t1.request_mode as [等待者需求的锁定类型]
,t1.request_session_id as [等待者sid] ,t2.wait_duration_ms as [等待时间]
,(select text from sys.dm_exec_requests as r cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.session_id = t1.request_session_id) as [等待者要运行的批处理] ,(select substring(qt.text,r.statement_start_offset/2+1,
(case when r.statement_end_offset = -1 then datalength(qt.text)
else r.statement_end_offset end - r.statement_start_offset)/2+1)
from sys.dm_exec_requests as r cross apply sys.dm_exec_sql_text(r.sql_handle) as qt where r.session_id = t1.request_session_id) as [等待者正要运行的语法] ,t2.blocking_session_id as [锁定者sid] ,(select text from sys.sysprocesses as p cross apply sys.dm_exec_sql_text(p.sql_handle) where p.spid = t2.blocking_session_id) as [锁定者的语法] from sys.dm_tran_locks as t1, sys.dm_os_waiting_tasks as t2 where t1.lock_owner_address = t2.resource_address
--以sys.dm_exec_query_stats 动态管理查看查询最耗 IO 资源的 SQL 语法
select top 5 object_name(qt.objectid,qt.dbid) ObjectName, (total_logical_reads/execution_count) as [平均逻辑读取次数], (total_logical_writes/execution_count) as [平均逻辑写入次数], (total_physical_reads/execution_count) as [平均对象读取次数], Execution_count 运行次数, substring(qt.text,r.statement_start_offset/2+1, (case when r.statement_end_offset = -1 then datalength(qt.text) else r.statement_end_offset end - r.statement_start_offset)/2+1) [运行语法] from sys.dm_exec_query_stats as r cross apply sys.dm_exec_sql_text(r.sql_handle) as qt order by (total_logical_reads + total_logical_writes) Desc
--通过sys.dm_io_pending_io_requests系统查看和sys.dm_io_virtual_file_stats 动态管理函数监控是否有 IO 延迟的状况
select db_name(i.database_id) db, name, physical_name, io_stall /1000 [用户等待文件完成I/O 的总时间(以秒为单位)]
, io_type [I/O 要求的类型], io_pending_ms_ticks [个别IO 在队列(Pending queue)等待的总时间]
from sys.dm_io_virtual_file_stats(NULL, NULL) i join sys.dm_io_pending_io_requests as p on i.file_handle = p.io_handle join sys.master_files m On m.database_id=i.database_id and m.file_id=i.file_id
2,-- 查看阻塞和锁
SELECT
t1.request_session_id AS spid,
t1.resource_type AS type,
t1.resource_database_id AS dbid,
(case resource_type
WHEN 'OBJECT' THEN object_name(t1.resource_associated_entity_id)
WHEN 'DATABASE' THEN ' '
ELSE (SELECT object_name(object_id)
FROM sys.partitions
WHERE hobt_id=resource_associated_entity_id)
END) AS objname,
t1.resource_description AS description,
t1.request_mode AS mode,
t1.request_status AS status,
t2.blocking_session_id
FROM sys.dm_tran_locks AS t1 LEFT OUTER JOIN sys.dm_os_waiting_tasks AS t2
ON t1.lock_owner_address = t2.resource_address
--2、检查前10个等待资源的SQL语句
select top 20 * from sys.dm_os_wait_stats order by wait_time_ms desc
--CXPACKET 并发线程
--并发线程可以使用的CPU数量
sp_configure 'max degree of parallelism', 4
GO
--时间为XX秒以上才并发
sp_configure 'cost threshold for parallelism', '25'
GO
RECONFIGURE WITH OVERRIDE
GO
--在系统访问量比较大的时候，看一下suspend的进程等的什么资源
select b.* from sys.dm_os_waiting_tasks a, sys.dm_exec_requests b
where a.session_id=b.session_id and b.status='suspended '
order by wait_duration_ms desc
--执行下面的脚本看一下数据等待的原因
SELECT TOP 10
[Wait type] = wait_type,
[Wait time (s)] = wait_time_ms / 1000,
[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0
/ SUM(wait_time_ms) OVER()),
waiting_tasks_count ,
max_wait_time_ms ,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
ORDER BY wait_time_ms DESC;
--第二确定那些语句编译是什么原因造成的，查看如下语句：
select usecounts, cacheobjtype, objtype, bucketid, text
from sys.dm_exec_cached_plans cp cross apply
sys.dm_exec_sql_text(cp.plan_handle)
where cacheobjtype = 'Compiled Plan'
order by objtype
select text, query_plan, requested_memory_kb, granted_memory_kb, used_memory_kb from sys.dm_exec_query_memory_grants MG
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
CROSS APPLY sys.dm_exec_query_plan(MG.plan_handle)
--第四关于并行查询等待，查看相关并行等待的SQL，如果有必要对OLTP来讲，限制并行查询：
--dbcc inputbuffer(sid) 找出相关语句，使用maxdop提示限制并行查询。
select * from sys.dm_os_waiting_tasks where wait_type='CXPACKET'
dbcc inputbuffer(697)
--过程使用的内存查询
select text, query_plan, requested_memory_kb, granted_memory_kb, used_memory_kb from sys.dm_exec_query_memory_grants MG
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
CROSS APPLY sys.dm_exec_query_plan(MG.plan_handle)
--查询正在等待的类型有哪些
select * from sys.dm_os_waiting_tasks
select * from sys.dm_os_wait_stats order by wait_time_ms desc
select top 20 * from sys.dm_os_latch_stats order by wait_time_ms desc
--3、查询显示 CPU 平均占用率最高的前50个SQL 语句
SELECT TOP 50 total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, *
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESC
--4、CPU 瓶颈通常由以下原因引起：查询计划并非最优、配置不当、设计因素不良或硬件资源不足。下面的常用查询可帮助您确定导致CPU瓶颈的原因。下面的查询使您能够深入了解当前缓存的哪些批处理或过程占用了大部分CPU资源。
SELECT
total_cpu_time,
total_execution_count,
number_of_statements,
s2.text
--(SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS query_text
FROM
(SELECT TOP 50
SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
COUNT(*) AS number_of_statements,
qs.sql_handle --,
--MIN(statement_start_offset) AS statement_start_offset,
--MAX(statement_end_offset) AS statement_end_offset
FROM
sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC) AS stats
CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2
select text from sys.dm_exec_sql_text(0x03000500E26FC43AFA59EF007A9F00000100000000000000)
--5、下面的查询显示缓存计划所占用的CPU总使用率（带 SQL 文本）。
SELECT
total_cpu_time,
total_execution_count,
number_of_statements,
s2.text
FROM
(SELECT TOP 50
SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
COUNT(*) AS number_of_statements,
qs.sql_handle
FROM
sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC) AS stats
CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2
--下面的查询显示哪个查询占用了最多的 CPU 累计使用率。
SELECT highest_cpu_queries.plan_handle, highest_cpu_queries.total_worker_time, q.dbid, q.objectid, q.number, q.encrypted, q.[text]
--into #tb
from (select top 50 qs.plan_handle, qs.total_worker_time
from sys.dm_exec_query_stats qs order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q order by highest_cpu_queries.total_worker_time desc
--6、下面的示例查询显示已重新编译的前 25 个存储过程。plan_generation_num 指示该查询已重新编译的次数。
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num > 1
order by plan_generation_num desc
--7、效率较低的查询计划可能增大 CPU 占用率。下面的查询显示哪个查询占用了最多的 CPU 累计使用率。
SELECT
highest_cpu_queries.plan_handle, highest_cpu_queries.total_worker_time, q.dbid, q.objectid, q.number, q.encrypted, q.[text]
from
(select top 50 qs.plan_handle, qs.total_worker_time from sys.dm_exec_query_stats qs order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc
--8、下面的查询显示一些可能占用大量 CPU 使用率的运算符（例如 ‘%Hash Match%’、‘%Sort%’）以找出可疑对象。
select *
from
sys.dm_exec_cached_plans
cross apply sys.dm_exec_query_plan(plan_handle)
where
cast(query_plan as nvarchar(max)) like '%Sort%'
or cast(query_plan as nvarchar(max)) like '%Hash Match%'
--9、如果已检测到效率低下并导致 CPU 占用率较高的查询计划，请对该查询中涉及的表运行 UPDATE STATISTICS 以查看该问题是否仍然存在。然后，收集相关数据并将此问题报告给 PerformancePoint Planning 支持人员。如果您的系统存在过多的编译和重新编译，可能会导致系统出现与 CPU 相关的性能问题。您可以运行下面的 DMV 查询来找出过多的编译/重新编译。
select * from sys.dm_exec_query_optimizer_info
where counter = 'optimizations' or counter = 'elapsed time'
SELECT TOP 5 creation_time, last_execution_time, total_clr_time,
total_clr_time/execution_count AS [Avg CLR Time], last_clr_time,
execution_count,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) as statement_text
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY total_clr_time/execution_count DESC;
GO
SELECT TOP 100 execution_count / datediff(second, creation_time, last_execution_time) AS [Execute Times Per Second], execution_count,
total_logical_reads /execution_count AS [Avg Logical Reads],
total_elapsed_time /execution_count AS [Avg Elapsed Time],
SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1, ((CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE execution_count > 100
ORDER BY 1 DESC;
SELECT TOP 100 execution_count / datediff(second, creation_time, last_execution_time) AS [Execute Count Per Second], execution_count,
total_logical_reads /execution_count AS [Avg Logical Reads],
total_elapsed_time /execution_count AS [Avg Elapsed Time],
db_name(st.dbid) as [database name],
object_name(st.objectid, st.dbid) as [object name],
SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1, ((CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE execution_count > 100
ORDER BY 1 DESC;
select top 100
transaction_id,
transaction_sequence_num,
elapsed_time_seconds
from sys.dm_tran_active_snapshot_database_transactions
order by elapsed_time_seconds DESC
--非 0 的值预示有任务需要等待时间切片来运行；这个数值高表明一个 CPU 瓶颈的征兆
select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from sys.dm_os_schedulers
where scheduler_id < 255
--下列查询给你一个查看当前缓存中最耗费 CPU 的批或过程高级别视图
select top 50
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc
--以利用如下语句通过sql_handle和plan_handle来得到SQL语句和查询计划：
select text from sys.dm_exec_sql_text(@sql_handle)
select * from sys.dm_exec_query_plan(@plan_handle)
select * from sys.dm_exec_query_plan(0x05000500E3B5594340E349C4010000000000000000000000)
--下面是示例给你展示了被重编译次数最多的 25 个存储过程。
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text where plan_generation_num >1
order by plan_generation_num desc
--
--低效率的查询计划通常可以被检测出来。低效率的查询计划可以导致增加 CPU 的消耗。
--
--查询 sys.dm_exec_query_stats 是确定哪个查询累计使用 CPU 时间最多的有效方法。
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from (select top 50 qs.plan_handle, qs.total_worker_time from sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc
--动态管理查看查询最耗 IO 资源的 SQL 语法
/**SQL05**/
select top 40
(total_logical_reads/execution_count) as [平均逻辑读取次数],
(total_logical_writes/execution_count) as [平均逻辑写入次数],
(total_physical_reads/execution_count) as [平均对象读取次数],
Execution_count 运行次数,
substring(qt.text,r.statement_start_offset/2+1,
(case when r.statement_end_offset = -1
then datalength(qt.text)
else r.statement_end_offset end - r.statement_start_offset)/2+1) [运行语法],getdate() [查询时间], q.text,plan_handle--,p.query_plan
from sys.dm_exec_query_stats as r
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
cross apply sys.dm_exec_query_plan(plan_handle) p
cross apply sys.dm_exec_sql_text(plan_handle) as q
--where plan_handle = 0x050005003AEA4921402337D9010000000000000000000000
order by
(total_logical_reads + (total_logical_writes) +(total_physical_reads))Desc
---------------------------------
select top 5 (total_logical_reads/execution_count) as avg_logical_reads,
(total_logical_writes/execution_count) as avg_logical_writes,
(total_physical_reads/execution_count) as avg_physical_reads,
Execution_count, statement_start_offset, p.query_plan, q.text
from sys.dm_exec_query_stats
cross apply sys.dm_exec_query_plan(plan_handle) p
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by (total_logical_reads + total_logical_writes)/execution_count Desc
-----------------------------------------------------------
等待类型
说明
ABR
标识为仅供参考。不提供支持。不保证以后的兼容性。
ASSEMBLY_LOAD
在以独占的方式访问程序集加载时出现。
ASYNC_DISKPOOL_LOCK
当尝试同步并行的线程（执行创建或初始化文件等任务）时出现。
ASYNC_IO_COMPLETION
当某任务正在等待 I/O 完成时出现。
ASYNC_NETWORK_IO
当任务被阻止在网络之后时出现在网络写入中。验证客户端是否正在处理来自服务器的数据。
AUDIT_GROUPCACHE_LOCK
当等待控制对某个特殊缓存的访问的锁时出现。该缓存包含正在使用哪些审核来审核每个审核操作组的相关信息。
AUDIT_LOGINCACHE_LOCK
当等待控制对某个特殊缓存的访问的锁时出现。该缓存包含正在使用哪些审核来审核登录审核操作组的相关信息。
AUDIT_ON_DEMAND_TARGET_LOCK
当等待用于确保扩展事件目标相关审核的单一初始化的锁时出现。
AUDIT_XE_SESSION_MGR
当等待用于同步扩展事件会话相关审核的启动和停止的锁时出现。
BACKUP
当任务作为备份处理的一部分被阻止时出现。
BACKUP_OPERATOR
当任务正在等待磁带装入时出现。若要查看磁带状态，请查询 sys.dm_io_backup_tapes。如果装入操作没有挂起，则该等待类型可能指示磁带机发生硬件问题。
BACKUPBUFFER
在备份任务等待数据或等待用来存储数据的缓冲区时发生。此类型不常见，只有当任务等待装入磁带时才会出现。
BACKUPIO
在备份任务等待数据或等待用来存储数据的缓冲区时发生。此类型不常见，只有当任务等待装入磁带时才会出现。
BACKUPTHREAD
当某任务正在等待备份任务完成时出现。等待时间可能较长，从几分钟到几个小时。如果被等待的任务正处于 I/O 进程中，则该类型不指示发生问题。
BAD_PAGE_PROCESS
当后台可疑页记录器正在尝试避免每隔五秒以上的时间运行时出现。过多的可疑页会导致记录器频繁运行。
BROKER_CONNECTION_RECEIVE_TASK
在等待访问以便在连接端点上接收消息时出现。已序列化对端点的接收访问。
BROKER_ENDPOINT_STATE_MUTEX
当存在访问 Service Broker 连接端点状态的争用时出现。已序列化对更改状态的访问。
BROKER_EVENTHANDLER
当某任务正在 Service Broker 的主事件处理程序中等待时出现。出现时间应该非常短暂。
BROKER_INIT
当初始化每个活动数据库中的 Service Broker 时出现。该状态应当频繁出现。
BROKER_MASTERSTART
当某任务正在等待 Service Broker 的主事件处理程序启动时出现。出现时间应该非常短暂。
BROKER_RECEIVE_WAITFOR
当 RECEIVE WAITFOR 正在等待时出现。如果没有准备接收的消息，则通常出现该状态。
BROKER_REGISTERALLENDPOINTS
在初始化 Service Broker 连接端点的过程中出现。出现时间应该非常短暂。
BROKER_SERVICE
当与目标服务关联的 Service Broker 目标列表更新或重新设定优先顺序时出现。
BROKER_SHUTDOWN
当按计划关闭 Service Broker 时出现。该状态出现的时间应当尽量短暂。
BROKER_TASK_STOP
当 Service Broker 队列任务处理程序尝试关闭任务时出现。已序列化状态检查，并且必须预先处于运行状态。
BROKER_TO_FLUSH
当 Service Broker 惰性刷新器将内存中传输对象刷新到工作表时出现。
BROKER_TRANSMITTER
当 Service Broker 发送器正在等待工作时出现。
BUILTIN_HASHKEY_MUTEX
可能在实例启动之后而在初始化内部数据结构时出现。数据结构初始化之后将不会再次出现。
CHECK_PRINT_RECORD
标识为仅供参考。不提供支持。不保证以后的兼容性。
CHECKPOINT_QUEUE
当检查点任务正在等待下一个检查点请求时出现。
CHKPT
在服务器启动时出现以通知检查点线程可以启动。
CLEAR_DB
在执行会更改数据库状态的操作过程中发生，例如打开或关闭数据库。
CLR_AUTO_EVENT
当某任务当前正在执行公共语言运行时 (CLR) 执行并且正在等待特殊的自动事件启动时出现。通常会出现长时间等待，这并不意味着出现问题。
CLR_CRST
当某任务当前正在执行 CLR 执行并且正在等待输入当前由另一项任务正在使用的任务的关键部分时出现。
CLR_JOIN
当某任务当前正在执行 CLR 执行并且正在等待另一项任务结束时出现。当两任务之间具有联接时出现该等待状态。
CLR_MANUAL_EVENT
当某任务当前正在执行 CLR 执行并且正在等待特定手动事件启动时出现。
CLR_MEMORY_SPY
当为用于记录来自 CLR 的所有虚拟内存分配的数据结构等待获取锁时出现。如果存在并行访问，该数据结构将被锁定以维护其完整性。
CLR_MONITOR
当某任务当前正在执行 CLR 执行并且正在等待获取用于监视器的锁时出现。
CLR_RWLOCK_READER
当某任务当前正在执行 CLR 执行并且正在等待读取器锁时出现。
CLR_RWLOCK_WRITER
当某任务当前正在执行 CLR 执行并且正在等待编写器锁时出现。
CLR_SEMAPHORE
当某任务当前正在执行 CLR 执行并且正在等待信号量时出现。
CLR_TASK_START
在等待 CLR 任务完成启动时出现。
CLRHOST_STATE_ACCESS
当等待获取对 CLR 宿主数据结构的独占访问时出现。当设置或关闭 CLR 运行时时出现此等待类型。
CMEMTHREAD
当某任务正在等待线程安全内存对象时出现。当多项任务尝试分配来自同一个内存对象的内存而导致出现争用时，便可能延长等待时间。
CXPACKET
当尝试同步查询处理器交换迭代器时出现。如果针对该等待类型的争用成为问题时，可以考虑降低并行度。
CXROWSET_SYNC
在并行范围扫描期间出现。
DAC_INIT
当正在初始化专用管理员连接时出现。
DBMIRROR_DBM_EVENT
标识为仅供参考。不提供支持。不保证以后的兼容性。
DBMIRROR_DBM_MUTEX
标识为仅供参考。不提供支持。不保证以后的兼容性。
DBMIRROR_EVENTS_QUEUE
在数据库镜像等待处理事件时出现。
DBMIRROR_SEND
当某任务正在等待清除网络层的通信积压以便能够发送消息时出现。指示通信层正在开始重载并影响数据库镜像数据吞吐量。
DBMIRROR_WORKER_QUEUE
指示数据库镜像工作线程任务正在等待更多的工作。
DBMIRRORING_CMD
当某任务正在等待日志记录刷新到磁盘时出现。该等待状态应当保留较长的时间。
DEADLOCK_ENUM_MUTEX
在死锁监视器和 sys.dm_os_waiting_tasks 尝试确保 SQL Server 不同时运行多个死锁搜索时出现。
DEADLOCK_TASK_SEARCH
长时间等待此资源指示服务器正在 sys.dm_os_waiting_tasks 之上执行查询，并且这些查询正在阻止死锁监视器运行死锁搜索。该等待类型仅供死锁监视器使用。sys.dm_os_waiting_tasks 之上的查询使用 DEADLOCK_ENUM_MUTEX。
DEBUG
在 Transact-SQL 和 CLR 调试内部同步期间出现。
DISABLE_VERSIONING
当 SQL Server 轮询版本事务管理器，以查看最早的活动事务的时间戳是否晚于状态开始更改时的时间戳时出现。如果是，则所有在 ALTER DATABASE 语句运行之前启动的快照事务都已完成。当 SQL Server 通过 ALTER DATABASE 语句禁用版本控制时使用该等待状态。
DISKIO_SUSPEND
当某任务正在等待访问文件（外部备份处于活动状态）时出现。针对每个正在等待的用户进程报告该状态。每个用户进程大于五的计数可能指示外部备份需要太长时间才能完成。
DISPATCHER_QUEUE_SEMAPHORE
当调度程序池中的线程正在等待更多要处理的工作时出现。当调度程序处于空闲状态时，此等待类型的等待时间预计要增加。
DLL_LOADING_MUTEX
在等待 XML 分析器 DLL 加载时出现。
DROPTEMP
在上次尝试删除临时对象失败后再进行下次尝试之前出现。对于每一次失败的删除尝试，等待持续时间都以指数形式增长。
DTC
当某任务正在等待用于管理状态转换的事件时出现。该状态控制当 SQL Server 接收到 Microsoft 分布式事务处理协调器 (MS DTC) 服务不可用的通知之后执行 MS DTC 事务恢复的时间。
该状态还说明在 SQL Server 启动了 MS DTC 事务提交并且 SQL Server 正在等待 MS DTC 提交完成时进行等待的任务。
DTC_ABORT_REQUEST
当 MS DTC 工作线程会话正在等待获得 MS DTC 事务的所有权时，在该会话中出现。当 MS DTC 拥有了事务后，该会话可以回滚事务。通常，该会话将等待另一个正在使用事务的会话。
DTC_RESOLVE
当恢复任务正在等待跨数据库事务中的 master 数据库以查询该事务的结果时出现。
DTC_STATE
当某任务正在等待对内部 MS DTC 全局状态对象的更改进行保护的事件时出现。该状态应当保持非常短的时间。
DTC_TMDOWN_REQUEST
当 SQL Server 接收到 MS DTC 服务不可用的通知时，在 MS DTC 工作线程会话中出现。首先，工作线程将等待 MS DTC 恢复进程启动。然后，工作线程等待获取其正在处理的分布式事务的结果。此过程可能一直执行，直到重新建立与 MS DTC 服务的连接。
DTC_WAITFOR_OUTCOME
当恢复任务等待 MS DTC 处于活动状态以启用准备好的事务的解决方法时出现。
DUMP_LOG_COORDINATOR
当主任务正在等待子任务生成数据时出现。该状态通常不会出现。长时间的等待指示出现意外的阻塞。应当对子任务进行调查。
DUMPTRIGGER
标识为仅供参考。不提供支持。不保证以后的兼容性。
EC
标识为仅供参考。不提供支持。不保证以后的兼容性。
EE_PMOLOCK
在语句执行过程中特定的内存分配类型同步期间出现。
EE_SPECPROC_MAP_INIT
在对内部过程哈希表创建进行同步期间发生。此等待只能发生在 SQL Server 实例启动之后对哈希表的初始访问期间。
ENABLE_VERSIONING
当 SQL Server 在声明数据库可以转换到快照隔离允许的状态之前，等待该数据库中的所有更新事务完成时出现。当 SQL Server 通过 ALTER DATABASE 语句启用快照隔离时使用该状态。
ERROR_REPORTING_MANAGER
在对多个并发错误日志初始化进行同步期间发生。
EXCHANGE
在并行查询过程中查询处理器交换迭代器同步期间出现。
EXECSYNC
在并行查询过程中同步与交换迭代器无关的区域内的查询处理器期间出现。例如，此类区域包括位图、二进制大型对象 (LOB) 以及假脱机迭代器等。LOB 可能会经常使用该等待状态。
EXECUTION_PIPE_EVENT_INTERNAL
当同步通过连接上下文提交的批处理执行的创建器和使用者部件期间出现。
FAILPOINT
标识为仅供参考。不提供支持。不保证以后的兼容性。
FCB_REPLICA_READ
当同步快照（或 DBCC 创建的临时快照）稀疏文件的读取时出现。
FCB_REPLICA_WRITE
当同步快照（或 DBCC 创建的临时快照）稀疏文件的页推送或页请求时出现。
FS_FC_RWLOCK
当 FILESTREAM 垃圾收集器等待执行下列操作之一时出现：
禁用垃圾收集（由备份和还原使用）。
执行 FILESTREAM 垃圾收集器的一个周期。
FS_GARBAGE_COLLECTOR_SHUTDOWN
当 FILESTREAM 垃圾收集器等待清除任务完成时出现。
FS_HEADER_RWLOCK
当等待获取对 FILESTREAM 数据容器的 FILESTREAM 标头的访问，以便读取或更新 FILESTREAM 标头文件 (Filestream.hdr) 中的内容时出现。
FS_LOGTRUNC_RWLOCK
当等待获取对 FILESTREAM 日志截断的访问以执行下列操作之一时出现：
临时禁用 FILESTREAM 日志 (FSLOG) 截断（由备份和还原使用）。
执行 FSLOG 截断的一个周期。
FSA_FORCE_OWN_XACT
当 FILESTREAM 文件 I/O 操作需要绑定到关联的事务，但该事务当前由另一个会话拥有时出现。
FSAGENT
当 FILESTREAM 文件 I/O 操作等待的 FILESTREAM 代理资源正由另一个文件 I/O 操作使用时出现。
FSTR_CONFIG_MUTEX
当等待另一个 FILESTREAM 功能重新配置完成时出现。
FSTR_CONFIG_RWLOCK
当等待序列化对 FILESTREAM 配置参数的访问时出现。
FT_METADATA_MUTEX
标识为仅供参考。不提供支持。不保证以后的兼容性。
FT_RESTART_CRAWL
在全文爬网需要从上一个已知可用点重新启动以便从暂时故障中恢复时出现。等待使当前正在此总体中工作的工作线程任务完成或退出当前步骤。
FULLTEXT GATHERER
在同步全文操作期间发生。
GUARDIAN
标识为仅供参考。不提供支持。不保证以后的兼容性。
HTTP_ENUMERATION
在启动时出现，以枚举 HTTP 端点以启动 HTTP。
HTTP_START
当连接正在等待 HTTP 完成初始化时出现。
IMPPROV_IOWAIT
当 SQL Server 等待 Bulkload I/O 完成时出现。
INTERNAL_TESTING
标识为仅供参考。不提供支持。不保证以后的兼容性。
IO_AUDIT_MUTEX
在跟踪事件缓冲区同步期间出现。
IO_COMPLETION
在等待 I/O 操作完成时出现。通常，该等待类型表示非数据页 I/O。数据页 I/O 完成等待显示为 PAGEIOLATCH_* waits。
IO_RETRY
当 I/O 操作（例如读取磁盘或写入磁盘）由于资源不足而失败，然后重试时出现。
IOAFF_RANGE_QUEUE
标识为仅供参考。不提供支持。不保证以后的兼容性。
KSOURCE_WAKEUP
在等待来自服务控制管理器的请求期间由服务控制任务使用。可能会出现长时间等待，这并不指示出现问题。
KTM_ENLISTMENT
标识为仅供参考。不提供支持。不保证以后的兼容性。
KTM_RECOVERY_MANAGER
标识为仅供参考。不提供支持。不保证以后的兼容性。
KTM_RECOVERY_RESOLUTION
标识为仅供参考。不提供支持。不保证以后的兼容性。
LATCH_DT
等待 DT（破坏）闩锁时出现。它不包括缓冲区闩锁或事务标记闩锁。sys.dm_os_latch_stats 中提供了 LATCH_* waits 的列表。请注意，sys.dm_os_latch_stats 将 LATCH_NL、LATCH_SH、LATCH_UP、LATCH_EX 以及 LATCH_DT 等待分到一组。
LATCH_EX
等待 EX（排他）闩锁时出现。它不包括缓冲区闩锁或事务标记闩锁。sys.dm_os_latch_stats 中提供了 LATCH_* waits 的列表。请注意，sys.dm_os_latch_stats 将 LATCH_NL、LATCH_SH、LATCH_UP、LATCH_EX 以及 LATCH_DT 等待分到一组。
LATCH_KP
等待 KP（保持）闩锁时出现。它不包括缓冲区闩锁或事务标记闩锁。sys.dm_os_latch_stats 中提供了 LATCH_* waits 的列表。请注意，sys.dm_os_latch_stats 将 LATCH_NL、LATCH_SH、LATCH_UP、LATCH_EX 以及 LATCH_DT 等待分到一组。
LATCH_NL
标识为仅供参考。不提供支持。不保证以后的兼容性。
LATCH_SH
等待 SH（共享）闩锁时出现。它不包括缓冲区闩锁或事务标记闩锁。sys.dm_os_latch_stats 中提供了 LATCH_* waits 的列表。请注意，sys.dm_os_latch_stats 将 LATCH_NL、LATCH_SH、LATCH_UP、LATCH_EX 以及 LATCH_DT 等待分到一组。
LATCH_UP
等待 UP（更新）闩锁时出现。它不包括缓冲区闩锁或事务标记闩锁。sys.dm_os_latch_stats 中提供了 LATCH_* waits 的列表。请注意，sys.dm_os_latch_stats 将 LATCH_NL、LATCH_SH、LATCH_UP、LATCH_EX 以及 LATCH_DT 等待分到一组。
LAZYWRITER_SLEEP
当惰性编写器被挂起时出现。正在等待的后台任务所用时间的度量值。在查找用户阻隔点所时不要考虑该状态。
LCK_M_BU
当某任务正在等待获取大容量更新 (BU) 锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL)。
LCK_M_IS
当某任务正在等待获取意向共享 (IS) 锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL)。
LCK_M_IU
当某任务正在等待获取意向更新 (IU) 锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL)。
LCK_M_IX
当某任务正在等待获取意向排他 (IX) 锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL)。
LCK_M_RIn_NL
当某任务正在等待获取当前键值上的 NULL 锁以及当前键和上一个键之间的插入范围锁时出现。键上的 NULL 锁是指立即释放的锁。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL)。
LCK_M_RIn_S
当某任务正在等待获取当前键值上的共享锁以及当前键和上一个键之间的插入范围锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL)。
LCK_M_RIn_U
任务正在等待获取当前键值上的更新锁以及当前键和上一个键之间的插入范围锁。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL)。
LCK_M_RIn_X
当某任务正在等待获取当前键值上的排他锁以及当前键和上一个键之间的插入范围锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL)。
LCK_M_RS_S
当某任务正在等待获取当前键值上的共享锁以及当前键和上一个键之间的共享范围锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL)。
LCK_M_RS_U
当某任务正在等待获取当前键值上的更新锁以及当前键和上一个键之间的更新范围锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL)。
LCK_M_RX_S
当某任务正在等待获取当前键值上的共享锁以及当前键和上一个键之间的排他范围锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL)。
LCK_M_RX_U
当某任务正在等待获取当前键值上的更新锁以及当前键和上一个键之间的排他范围锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL)。
LCK_M_RX_X
当某任务正在等待获取当前键值上的排他锁以及当前键和上一个键之间的排他范围锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL)。
LCK_M_S
当某任务正在等待获取共享锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL)。
LCK_M_SCH_M
当某任务正在等待获取架构修改锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL)。
LCK_M_SCH_S
当某任务正在等待获取架构共享锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL)。
LCK_M_SIU
当某任务正在等待获取共享意向更新锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL)。
LCK_M_SIX
当某任务正在等待获取共享意向排他锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL)。
LCK_M_U
当某任务正在等待获取更新锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL)。
LCK_M_UIX
当某任务正在等待获取更新意向排他锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL)。
LCK_M_X
当某任务正在等待获取排他锁时出现。有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL)。
LOGBUFFER
当某任务正在等待日志缓冲区的空间以存储日志记录时出现。连续的高值可能指示日志设备无法跟上服务器生成的日志量。
LOGGENERATION
标识为仅供参考。不提供支持。不保证以后的兼容性。
LOGMGR
在数据库关闭过程中，当某任务正在等待任何未完成的日志 I/O 在关闭日志之前完成时出现。
LOGMGR_FLUSH
标识为仅供参考。不提供支持。不保证以后的兼容性。
LOGMGR_QUEUE
在日志编写器任务等待工作请求时出现。
LOGMGR_RESERVE_APPEND
当某任务正在等待查看日志截断是否能释放日志空间以使该任务能写入新的日志记录时出现。请考虑为受影响的数据库增加日志文件的大小以减少该等待时间。
LOWFAIL_MEMMGR_QUEUE
在等待可用内存期间出现。
MISCELLANEOUS
标识为仅供参考。不提供支持。不保证以后的兼容性。
MSQL_DQ
当某任务正在等待分布式查询操作完成时出现。它用于检测潜在的多个活动的结果集 (MARS) 应用程序死锁。该等待将在分布式查询调用完成时结束。
MSQL_XACT_MGR_MUTEX
当某任务正在等待获取会话事务管理器的所有权以执行会话级别事务操作时出现。
MSQL_XACT_MUTEX
在事务使用同步期间出现。请求必须先获取互斥体才可以使用事务。
MSQL_XP
当某任务正在等待扩展存储过程结束时出现。SQL Server 使用该等待状态检测潜在的 MARS 应用程序死锁。该等待将在扩展存储过程调用结束时停止。
MSSEARCH
在全文搜索调用期间出现。该等待在全文操作完成时结束。它不指示争用，而指示全文操作的持续时间。
NET_WAITFOR_PACKET
在网络读取过程中连接正在等待网络数据包时出现。
OLEDB
在 SQL Server 调用 SQL Server Native Client OLE DB 访问接口时出现。该等待类型不用于同步。而是用于指示调用 OLE DB 访问接口的持续时间。
ONDEMAND_TASK_QUEUE
在后台任务等待高优先级系统任务请求时出现。长时间的等待指示一直没有要处理的高优先级请求，不应引起关注。
PAGEIOLATCH_DT
在任务等待 I/O 请求中缓冲区的闩锁时发生。闩锁请求处于“破坏”模式。长时间的等待可能指示磁盘子系统出现问题。
PAGEIOLATCH_EX
在任务等待 I/O 请求中缓冲区的闩锁时发生。闩锁请求处于“独占”模式。长时间的等待可能指示磁盘子系统出现问题。
PAGEIOLATCH_KP
在任务等待 I/O 请求中缓冲区的闩锁时发生。闩锁请求处于“保持”模式。长时间的等待可能指示磁盘子系统出现问题。
PAGEIOLATCH_NL
标识为仅供参考。不提供支持。不保证以后的兼容性。
PAGEIOLATCH_SH
在任务等待 I/O 请求中缓冲区的闩锁时发生。闩锁请求处于“共享”模式。长时间的等待可能指示磁盘子系统出现问题。
PAGEIOLATCH_UP
在任务等待 I/O 请求中缓冲区的闩锁时发生。闩锁请求处于“更新”模式。长时间的等待可能指示磁盘子系统出现问题。
PAGELATCH_DT
在任务等待不处于 I/O 请求中的缓冲区闩锁时发生。闩锁请求处于“破坏”模式。
PAGELATCH_EX
在任务等待不处于 I/O 请求中的缓冲区闩锁时发生。闩锁请求处于“独占”模式。
PAGELATCH_KP
在任务等待不处于 I/O 请求中的缓冲区闩锁时发生。闩锁请求处于“保持”模式。
PAGELATCH_NL
标识为仅供参考。不提供支持。不保证以后的兼容性。
PAGELATCH_SH
在任务等待不处于 I/O 请求中的缓冲区闩锁时发生。闩锁请求处于“共享”模式。
PAGELATCH_UP
在任务等待不处于 I/O 请求中的缓冲区闩锁时发生。闩锁请求处于“更新”模式。
PARALLEL_BACKUP_QUEUE
在序列化由 RESTORE HEADERONLY、RESTORE FILELISTONLY 或 RESTORE LABELONLY 生成的输出时出现。
PREEMPTIVE_ABR
标识为仅供参考。不提供支持。不保证以后的兼容性。
PREEMPTIVE_AUDIT_ACCESS_EVENTLOG
在 SQL Server 操作系统 (SQLOS) 计划程序切换到抢先模式时发生，以便将审核事件写入 Windows 事件日志。
PREEMPTIVE_AUDIT_ACCESS_SECLOG
在 SQLOS 计划程序切换到抢先模式时发生，以便将审核事件写入 Windows 安全日志。
PREEMPTIVE_CLOSEBACKUPMEDIA
在 SQLOS 计划程序切换到抢先模式时发生，以便关闭备份介质。
PREEMPTIVE_CLOSEBACKUPTAPE
在 SQLOS 计划程序切换到抢先模式时发生，以便关闭磁带备份设备。
PREEMPTIVE_CLOSEBACKUPVDIDEVICE
在 SQLOS 计划程序切换到抢先模式时发生，以便关闭虚拟备份设备。
PREEMPTIVE_CLUSAPI_CLUSTERRESOURCECONTROL
在 SQLOS 计划程序切换到抢先模式时发生，以便执行故障转移群集操作。
PREEMPTIVE_COM_COCREATEINSTANCE
在 SQLOS 计划程序切换到抢先模式时发生，以便创建 COM 对象。
PREEMPTIVE_SOSTESTING
标识为仅供参考。不提供支持。不保证以后的兼容性。
PREEMPTIVE_STRESSDRIVER
标识为仅供参考。不提供支持。不保证以后的兼容性。
PREEMPTIVE_TESTING
标识为仅供参考。不提供支持。不保证以后的兼容性。
PREEMPTIVE_XETESTING
标识为仅供参考。不提供支持。不保证以后的兼容性。
PRINT_ROLLBACK_PROGRESS
用于等待用户进程在已通过 ALTER DATABASE 终止子句完成转换的数据库中结束。有关详细信息，请参阅 ALTER DATABASE (Transact-SQL)。
QPJOB_KILL
指示异步统计信息自动更新在开始运行时通过调用 KILL 命令而取消。终止线程处于挂起状态，等待它开始侦听 KILL 命令。正常情况下，该值不到一秒钟。
QPJOB_WAITFOR_ABORT
指示异步统计信息自动更新在运行时通过调用 KILL 命令而取消。目前更新已完成，但是在终止线程消息协调完成之前一直于挂起状态。这是一个普通而少见的状态，应当非常短暂。正常情况下，该值不到一秒钟。
QRY_MEM_GRANT_INFO_MUTEX
当查询执行内存管理尝试控制对静态授予信息列表的访问时出现。该状态列出当前已批准的内存请求以及正在等待的内存请求的有关信息。该状态是一个简单的访问控制状态。该状态始终不应当等待较长的时间。如果未释放互斥体，则所有占用内存的新查询都将停止响应。
QUERY_ERRHDL_SERVICE_DONE
标识为仅供参考。不提供支持。不保证以后的兼容性。
QUERY_EXECUTION_INDEX_SORT_EVENT_OPEN
当脱机创建索引生成以并行方式运行，并且正在排序的不同工作线程同步访问排序文件时出现。
QUERY_NOTIFICATION_MGR_MUTEX
在查询通知管理器中的垃圾收集队列同步期间出现。
QUERY_NOTIFICATION_SUBSCRIPTION_MUTEX
在查询通知中事务的状态同步期间出现。
QUERY_NOTIFICATION_TABLE_MGR_MUTEX
在查询通知管理器中的内部同步期间出现。
QUERY_NOTIFICATION_UNITTEST_MUTEX
标识为仅供参考。不提供支持。不保证以后的兼容性。
QUERY_OPTIMIZER_PRINT_MUTEX
在查询优化器诊断信息输出生成的同步期间出现。该等待类型仅在诊断设置已根据 Microsoft 产品支持的说明启用后出现。
QUERY_TRACEOUT
标识为仅供参考。不提供支持。不保证以后的兼容性。
QUERY_WAIT_ERRHDL_SERVICE
标识为仅供参考。不提供支持。不保证以后的兼容性。
RECOVER_CHANGEDB
在备用数据库中同步数据库状态期间出现。
REPL_CACHE_ACCESS
在同步复制项目缓存的期间出现。在这些等待期间，复制日志读取器将停止，已发布表中的数据定义语言 (DDL) 语句也将被阻止。
REPL_SCHEMA_ACCESS
在同步复制架构版本信息的期间出现。该状态在下列情况下存在：针对复制对象执行 DDL 语句时，以及日志读取器根据 DDL 出现次数生成或使用版本控制架构时。
REPLICA_WRITES
在任务等待将页写入数据库快照或 DBCC 副本的操作完成时出现。
REQUEST_DISPENSER_PAUSE
在任务等待所有未完成的 I/O 完成时出现，以便可以为快照备份冻结文件的 I/O。
REQUEST_FOR_DEADLOCK_SEARCH
在死锁监视器等待开始下一次死锁搜索时出现。在两次死锁检测之间可能出现该等待，长时间等待此资源并不指示出现问题。
RESMGR_THROTTLED
在有新请求传入并且基于 GROUP_MAX_REQUESTS 设置而中止时出现。
RESOURCE_QUEUE
在同步不同的内部资源队列期间出现。
RESOURCE_SEMAPHORE
当由于存在其他并发查询而无法立即批准查询内存请求时出现。等待时间较长或等待次数较多可能指示并发查询的数量过多或内存请求的数量过多。
RESOURCE_SEMAPHORE_MUTEX
在查询等待其保留线程的请求完成时出现。它也在同步查询编译和内存授予请求时出现。
RESOURCE_SEMAPHORE_QUERY_COMPILE
在并发查询编译的数量达到中止限制时出现。等待时间较长或等待次数较多可能指示编译、重新编辑或不可缓存的计划过多。
RESOURCE_SEMAPHORE_SMALL_QUERY
当由于存在其他并发查询而无法立即批准较小查询的内存请求时出现。等待时间不应超过几秒钟，因为如果服务器无法在几秒钟内给予请求的内存，则会将请求传输到主查询内存池中。等待时间较长可能指示当主内存池被等待的查询阻塞时并发小查询的数量过多。
SEC_DROP_TEMP_KEY
在尝试删除临时安全密钥失败之后并在重试之前出现。
SECURITY_MUTEX
当等待互斥体时出现，这些互斥体控制对可扩展的密钥管理 (EKM) 加密提供程序的全局列表以及 EKM 会话的会话作用域列表的访问。
SEQUENTIAL_GUID
当正在获取新的连续 GUID 时出现。
SERVER_IDLE_CHECK
当资源监视器正在尝试将 SQL Server 实例声明为空闲或正在尝试唤醒时，在 SQL Server 实例空闲状态的同步期间出现。
SHUTDOWN
在关闭语句等待活动连接退出时出现。
SLEEP_BPOOL_FLUSH
当检查点为了避免磁盘子系统泛滥而中止新 I/O 的发布时出现。
SLEEP_DBSTARTUP
在等待所有数据库恢复时数据库的启动期间出现。
SLEEP_DCOMSTARTUP
通常在等待 DCOM 初始化完成时 SQL Server 实例的启动期间出现。
SLEEP_MSDBSTARTUP
在 SQL 跟踪等待 msdb 数据库完成启动时出现。
SLEEP_SYSTEMTASK
在等待 tempdb 完成启动时后台任务的启动期间出现。
SLEEP_TASK
当任务在等待一般事件出现期间睡眠时出现。
SLEEP_TEMPDBSTARTUP
在任务等待 tempdb 完成启动时出现。
SNI_CRITICAL_SECTION
在 SQL Server 网络组件中进行内部同步期间出现。
SNI_HTTP_WAITFOR_0_DISCON
在等待未完成的 HTTP 连接退出的过程中 SQL Server 的关闭期间出现。
SNI_LISTENER_ACCESS
当等待非一致性内存访问 (NUMA) 节点更新状态更改时出现。已序列化对状态更改的访问。
SNI_TASK_COMPLETION
当在 NUMA 节点状态更改期间等待所有任务完成时出现。
SOAP_READ
在等待 HTTP 网络读取完成时出现。
SOAP_WRITE
在等待 HTTP 网络写入完成时出现。
SOS_CALLBACK_REMOVAL
在为了删除回调而对回调列表执行同步期间出现。服务器初始化完成之后，此计数器可能不会更改。
SOS_DISPATCHER_MUTEX
在调度程序池进行内部同步期间出现。包括调整该池时。
SOS_LOCALALLOCATORLIST
在 SQL Server 内存管理器中进行内部同步期间出现。
SOS_MEMORY_USAGE_ADJUSTMENT
在池之间调整内存使用情况时出现。
SOS_OBJECT_STORE_DESTROY_MUTEX
当破坏池中的对象时在内存池中进行内部同步期间出现。
SOS_PROCESS_AFFINITY_MUTEX
在同步访问进程关联设置期间出现。
SOS_RESERVEDMEMBLOCKLIST
在 SQL Server 内存管理器中进行内部同步期间出现。
SOS_SCHEDULER_YIELD
在任务自愿为要执行的其他任务生成计划程序时出现。在该等待期间任务正在等待其量程更新。
SOS_SMALL_PAGE_ALLOC
在分配和释放由某些内存对象管理的内存时出现。
SOS_STACKSTORE_INIT_MUTEX
在内部存储初始化同步期间出现。
SOS_SYNC_TASK_ENQUEUE_EVENT
在任务以同步方式启动时出现。SQL Server 中的大多数任务都以同步方式启动，在此方式中控制权在任务请求放置在工作队列之后立即返回到启动器。
SOS_VIRTUALMEMORY_LOW
在内存分配等待资源管理器释放虚拟内存时出现。
SOSHOST_EVENT
当宿主组件（如 CLR）在 SQL Server 事件同步对象中等待时出现。
SOSHOST_INTERNAL
在宿主组件（如 CLR）使用的内存管理器回调同步期间出现。
SOSHOST_MUTEX
当宿主组件（如 CLR）在 SQL Server 互斥体同步对象中等待时出现。
SOSHOST_RWLOCK
当宿主组件（如 CLR）在 SQL Server 读取器编写器同步对象中等待时出现。
SOSHOST_SEMAPHORE
当宿主组件（如 CLR）在 SQL Server 信号量同步对象中等待时出现。
SOSHOST_SLEEP
当宿主任务在等待一般事件出现期间睡眠时出现。宿主任务由宿主组件（如 CLR）使用。
SOSHOST_TRACELOCK
在同步访问跟踪流期间出现。
SOSHOST_WAITFORDONE
在宿主组件（如 CLR）等待任务完成时出现。
SQLCLR_APPDOMAIN
在 CLR 等待应用程序域完成启动时出现。
SQLCLR_ASSEMBLY
在等待访问 appdomain 中已加载的程序集列表时出现。
SQLCLR_DEADLOCK_DETECTION
在 CLR 等待死锁检测完成时出现。
SQLCLR_QUANTUM_PUNISHMENT
在 CLR 任务由于已经超过了其执行量程而中止时出现。此中止已完成，以便减小此大量消耗资源的任务对其他任务的影响。
SQLSORT_NORMMUTEX
在初始化内部排序结构时进行内部同步期间出现。
SQLSORT_SORTMUTEX
在初始化内部排序结构时进行内部同步期间出现。
SQLTRACE_BUFFER_FLUSH
当某任务正在等待后台任务将跟踪缓冲区每隔四秒刷新到磁盘时出现。
SQLTRACE_LOCK
在文件跟踪过程中同步跟踪缓冲区期间出现。
SQLTRACE_SHUTDOWN
在跟踪关闭等待未完成的跟踪事件完成时出现。
SQLTRACE_WAIT_ENTRIES
在 SQL 跟踪事件队列等待数据包到达队列时出现。
SRVPROC_SHUTDOWN
在关闭进程等待内部资源释放以完全关闭时出现。
TEMPOBJ
在临时对象删除同步时出现。该等待很少出现，仅在任务已请求 temp 表的独占访问删除时出现。
THREADPOOL
当某任务正在等待工作线程运行时出现。这可能指示最大工作线程数设置过低，或批处理执行时间过长，从而减少可满足其他批处理的工作线程数。
TIMEPRIV_TIMEPERIOD
在扩展事件计时器进行内部同步期间出现。
TRACEWRITE
当 SQL 跟踪行集跟踪提供程序等待可用缓冲区或可处理事件的缓冲区时出现。
TRAN_MARKLATCH_DT
在等待事务标记闩锁中的破坏模式闩锁时出现。事务标记闩锁用于同步提交与标记的事务。
TRAN_MARKLATCH_EX
在等待标记事务中的排他模式闩锁时出现。事务标记闩锁用于同步提交与标记的事务。
TRAN_MARKLATCH_KP
在等待标记事务中的保持模式闩锁时出现。事务标记闩锁用于同步提交与标记的事务。
TRAN_MARKLATCH_NL
标识为仅供参考。不提供支持。不保证以后的兼容性。
TRAN_MARKLATCH_SH
在等待标记事务中的共享模式闩锁时出现。事务标记闩锁用于同步提交与标记的事务。
TRAN_MARKLATCH_UP
在等待标记事务中的更新模式闩锁时出现。事务标记闩锁用于同步提交与标记的事务。
TRANSACTION_MUTEX
在同步多个批处理访问事务期间出现。
UTIL_PAGE_ALLOC
在内存不足期间事务日志扫描等待可用内存时出现。
VIA_ACCEPT
当在启动过程中完成虚拟接口适配器 (VIA) 提供程序连接时出现。
VIEW_DEFINITION_MUTEX
在同步访问已缓存的视图定义期间出现。
WAIT_FOR_RESULTS
在等待查询通知触发时出现。
WAITFOR
显示为 WAITFOR Transact-SQL 语句的结果。等待持续时间由此语句的参数确定。它是用户启动的等待。
WAITFOR_TASKSHUTDOWN
标识为仅供参考。不提供支持。不保证以后的兼容性。
WAITSTAT_MUTEX
在同步访问用于填充 sys.dm_os_wait_stats 的统计信息集期间出现。
WCC
标识为仅供参考。不提供支持。不保证以后的兼容性。
WORKTBL_DROP
在删除出现故障的工作表之后，重试之前的暂停期间出现。
WRITE_COMPLETION
当正在进行写操作时出现。
WRITELOG
等待日志刷新完成时出现。导致日志刷新的常见操作是检查点和事务提交。
XACT_OWN_TRANSACTION
在等待获取事务的所有权时出现。
XACT_RECLAIM_SESSION
在等待会话的当前所有者释放会话的所有权时出现。
XACTLOCKINFO
在同步访问事务锁列表期间出现。除事务本身之外，在页拆分过程中死锁检测和锁迁移等操作也可访问锁列表。
XACTWORKSPACE_MUTEX
在同步事务中的脱离以及事务登记成员之间的数据库锁数时出现。
XE_BUFFERMGR_ALLPROCESSED_EVENT
在扩展事件会话缓冲区刷新到目标时发生。此等待在后台线程上发生。
XE_BUFFERMGR_FREEBUF_EVENT
当下列任一条件成立时发生：
扩展事件会话配置为无事件损失，且会话中的所有缓冲区当前已满。这表明扩展事件会话缓冲区太小，或应对其进行分区。
审核遇到延迟。这表明写入审核的驱动器上存在磁盘瓶颈。
XE_DISPATCHER_CONFIG_SESSION_LIST
在使用异步目标的扩展事件会话启动或停止时发生。此等待表明发生了以下某一情况：
扩展事件会话正在向后台线程池注册。
后台线程池正在根据当前负荷计算需要的线程数量。
XE_DISPATCHER_JOIN
在用于扩展事件会话的后台线程终止时发生。
XE_DISPATCHER_WAIT
在用于扩展事件会话的后台线程等待事件缓冲区进行处理时发生。
XE_MODULEMGR_SYNC
标识为仅供参考。不提供支持。不保证以后的兼容性。
XE_OLS_LOCK
标识为仅供参考。不提供支持。不保证以后的兼容性。
XE_PACKAGE_LOCK_BACKOFF
标识为仅供参考。不提供支持。不保证以后的兼容性。
SELECT a3.name AS [Schema 名称],
a2.name AS [表名称],
a1.rows as 记录条数,
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS [保留空间(K)],
a1.data * 8 AS [数据使用空间(k)],
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data
THEN (a1.used + ISNULL(a4.used,0)) - a1.data
ELSE 0 END) * 8 AS [索引使用空间(k)],
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used
THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used
ELSE 0 END) * 8 AS [未用空间(k)],
a1.data * 8*1024/(CASE WHEN a1.Rows=0 THEN 1 ELSE a1.Rows END) 平均每条记录长度
FROM
(
SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN
(ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(
SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id
) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY [保留空间(K)] DESC
--内存查询
SELECT TOP (10) type, SUM(single_pages_kb) AS [SPA Mem, KB]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC
SELECT TOP (10) type, SUM(multi_pages_kb) AS Memory_allocated_KB
FROM sys.dm_os_memory_clerks
WHERE multi_pages_kb!=0
GROUP BY type
SELECT objtype AS 'Cached Object Type',
COUNT(*) AS 'Number of Plans',
SUM(CAST(size_in_bytes AS BIGINT))/1024/1024 AS 'Plan Cache Size (MB)',
AVG(usecounts) AS 'Avg Use Count'
FROM sys.dm_exec_cached_plans
GROUP BY objtype
--I/O
--各磁盘和数据库的吞吐量和等待时间
SELECT b.name,c.name,c.physical_name,
a.num_of_reads,a.num_of_writes,
a.num_of_bytes_read,a.num_of_bytes_written,
a.io_stall_read_ms,a.io_stall_write_ms,
a.io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) a,
sys.databases b,sys.master_files c
WHERE a.database_id=b.database_id
AND a.file_id=c.file_id
AND a.database_id=c.database_id
order by a.num_of_bytes_written +a.io_stall_read_ms
--挂起的 I/O 请求和对应的数据文件
SELECT
database_id,file_id,
io_stall,io_pending_ms_ticks,
scheduler_address
FROM sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
WHERE t1.file_handle = t2.io_handle
SELECT object_name,COUNT(DISTINCT counter_name)
FROM sys.dm_os_performance_counters
GROUP BY object_name
ORDER BY object_name
--所有等待事件，共计484个
SELECT * FROM sys.dm_os_wait_stats
--消耗时间占据95%的等待事件
WITH Waits AS
(
SELECT wait_type,wait_time_ms/1000. AS wait_time_s,
100.*wait_time_ms/SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(order by wait_time_ms DESC) as rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('SLEEP_TASK','BROKER_TASK_STOP',
'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT',
'CLR_MANUAL_EVENT','LAZYWRITER_SLEEP')
)
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12,2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12,2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12,2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn<=W1.rn
GROUP BY W1.rn,W1.wait_type,W1.wait_time_s,W1.pct
HAVING SUM(W2.pct)-W1.pct<95
SELECT SessionID = s.Session_id ,
resource_type ,
DatabaseName = DB_NAME(resource_database_id) ,
request_mode ,
request_type ,
login_time ,
host_name ,
program_name ,
client_interface_name ,
login_name ,
nt_domain ,
nt_user_name ,
s.status ,
last_request_start_time ,
last_request_end_time ,
s.logical_reads ,
s.reads ,
request_status ,
request_owner_type ,
objectid ,
dbid ,
a.number ,
a.encrypted ,
a.blocking_session_id ,
a.text
FROM sys.dm_tran_locks l
JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
LEFT JOIN ( SELECT *
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
) a ON s.session_id = a.session_id
WHERE s.session_id > 50
select blocking_session_id,wait_duration_ms,session_id from sys.dm_os_waiting_tasks where blocking_session_id is not null
sp_who active
with tb
as
(
select blocking_session_id,
session_id,db_name(database_id) as dbname,text from master.sys.dm_exec_requests a
CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)
),
tb1 as
(
select a.*,login_time,program_name,client_interface_name,login_name,cpu_time,memory_usage*8 as 'memory_usage(KB)',
total_scheduled_time,reads,writes,logical_reads
from tb a inner join master.sys.dm_exec_sessions b
on a.session_id=b.session_id
)
select a.*,connect_time,client_tcp_port,client_net_address from tb1 a inner join master.sys.dm_exec_connections b on a.session_id=b.session_id
with tb
as
(
select blocking_session_id,
session_id,db_name(database_id) as dbname,text from master.sys.dm_exec_requests a
CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)
),
tb1 as
(
select a.*,login_time,program_name,client_interface_name,login_name,cpu_time,memory_usage*8 as 'memory_usage(KB)',
total_scheduled_time,reads,writes,logical_reads
from tb a inner join master.sys.dm_exec_sessions b
on a.session_id=b.session_id
)
select a.*,connect_time,client_tcp_port,client_net_address from tb1 a inner join master.sys.dm_exec_connections b on a.session_id=b.session_id
with tb
as
(
select blocking_session_id,
session_id,db_name(database_id) as dbname,text from master.sys.dm_exec_requests a
CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)
),
tb1 as
(
select a.*,login_time,program_name,client_interface_name,login_name,cpu_time,memory_usage*8 as 'memory_usage(KB)',
total_scheduled_time,reads,writes,logical_reads
from tb a inner join master.sys.dm_exec_sessions b
on a.session_id=b.session_id
)
select a.*,connect_time,client_tcp_port,client_net_address from tb1 a inner join master.sys.dm_exec_connections b on a.session_id=b.session_id
with tb
as
(
select blocking_session_id,
session_id,db_name(database_id) as dbname,text from master.sys.dm_exec_requests a
CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)
),
tb1 as
(
select a.*,login_time,program_name,client_interface_name,login_name,cpu_time,memory_usage*8 as 'memory_usage(KB)',
total_scheduled_time,reads,writes,logical_reads
from tb a inner join master.sys.dm_exec_sessions b
on a.session_id=b.session_id
)
select a.*,connect_time,client_tcp_port,client_net_address from tb1 a inner join master.sys.dm_exec_connections b on a.session_id=b.session_id
SELECT
DB_NAME(t1.resource_database_id) AS [数据库名],
t1.resource_type AS [资源类型],
-- t1.request_type AS [请求类型],
t1.request_status AS [请求状态],
-- t1.resource_description AS [资源说明],
CASE t1.request_owner_type WHEN 'TRANSACTION' THEN
'事务所有'
WHEN 'CURSOR' THEN '游标所有'
WHEN 'SESSION' THEN '用户会话所有'
WHEN
'SHARED_TRANSACTION_WORKSPACE' THEN '事务工作区的共享所有'
WHEN
'EXCLUSIVE_TRANSACTION_WORKSPACE' THEN '事务工作区的独占所有'
ELSE ''
END AS [拥有请求的实体类型],
CASE WHEN T1.resource_type = 'OBJECT'
THEN OBJECT_NAME(T1.resource_ASsociated_entity_id)
ELSE
T1.resource_type+':'+ISNULL(LTRIM(T1.resource_ASsociated_entity_id),'')
END AS [锁定的对象],
t4.[name] AS [索引],
t1.request_mode AS [锁定类型],
t1.request_session_id AS [当前spid],
t2.blocking_session_id AS [锁定spid],
-- t3.snapshot_isolation_state AS [快照隔离状态],
t3.snapshot_isolation_state_desc AS [快照隔离状态描述],
t3.is_read_committed_snapshot_on AS [已提交读快照隔离]
FROM
sys.dm_tran_locks AS t1
left join
sys.dm_os_waiting_tasks AS t2
ON
t1.lock_owner_address = t2.resource_address
left join
sys.databases AS t3
ON t1.resource_database_id = t3.database_id
left join
(
SELECT rsc_text,rsc_indid,rsc_objid,b.[name]
FROM
sys.syslockinfo a
JOIN
sys.indexes b
ON a.rsc_indid = b.index_id and b.object_id =
a.rsc_objid) t4
ON t1.resource_description = t4.rsc_text
--1,--确定服务器活动(正在活动的进程)
SELECT es.session_id
,es.program_name
,es.login_name
,es.nt_user_name
,es.login_time
,es.host_name
,es.cpu_time
,es.total_scheduled_time
,es.total_elapsed_time
,es.memory_usage
,es.logical_reads
,es.reads
,es.writes
,st.text
FROM sys.dm_exec_sessions es
LEFT JOIN sys.dm_exec_connections ec
ON es.session_id = ec.session_id
LEFT JOIN sys.dm_exec_requests er
ON es.session_id = er.session_id
OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) st
WHERE es.session_id > 50 -- < 50 system sessions
ORDER BY es.cpu_time DESC
GO
--3,--查找有关被阻塞的请求的信息
SELECT session_id ,status ,blocking_session_id
,wait_type ,wait_time ,wait_resource
,transaction_id
FROM sys.dm_exec_requests
WHERE status = N'suspended';
GO
--查询语句执行次数及CPU开销
SELECT TOP 10
total_worker_time/execution_count AS avg_cpu_cost,
execution_count,
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC;
--计算可运行状态下的工作进程数量
SELECT COUNT(*) AS workers_waiting_for_cpu, t2.Scheduler_id
FROM sys.dm_os_workers AS t1, sys.dm_os_schedulers AS t2
WHERE t1.state = 'RUNNABLE' AND
t1.scheduler_address = t2.scheduler_address AND
t2.scheduler_id < 255
GROUP BY t2.scheduler_id;
--查询工作进程在可运行状态下花费的时间
SELECT SUM(signal_wait_time_ms)
FROM sys.dm_os_wait_stats;
--每次执行占CPU最多的查询
SELECT TOP 30
total_worker_time/execution_count AS avg_cpu_cost,
plan_handle, execution_count,
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,--p.query_plan,
q.text,plan_handle
from sys.dm_exec_query_stats as r
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
cross apply sys.dm_exec_query_plan(plan_handle) p
cross apply sys.dm_exec_sql_text(plan_handle) as q
ORDER BY [avg_cpu_cost] DESC;
--找出工作负荷中运行最频繁的查询
SELECT TOP 10 total_worker_time, plan_handle,execution_count,
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max),text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY execution_count DESC;
--SQL Server 在优化查询计划上花费的时间
SELECT *
FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'optimizations' OR counter = 'elapsed time';
--被编译最多的查询
SELECT TOP 10 plan_generation_num, execution_count,plan_handle,
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max),text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
WHERE plan_generation_num >1
ORDER BY plan_generation_num DESC;
--检查分配给优化查询计划的过程高速缓冲的内存
DBCC MEMORYSTATUS
--查询缓冲区消耗内存总量
SELECT SUM(multi_pages_kb + virtual_memory_committed_kb
+ shared_memory_committed_kb
+ awe_allocated_kb)/1024 AS [Used by BPool, Mb]
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLBUFFERPOOL';
--查询消耗缓冲区大部分页面的内部组件
SELECT TOP 10 type, SUM(single_pages_kb) AS stolen_mem_kb
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC;
--查询在缓冲区池外分配了内存的内部组件
SELECT type, SUM(multi_pages_kb) AS memory_allocated_KB
FROM sys.dm_os_memory_clerks
WHERE multi_pages_kb != 0
GROUP BY type;
--查询I/O等待锁的统计信息
SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGEIOLATCH%'
ORDER BY wait_type;
--最重要的等待锁是PAGEIOLATCH_SH和PAGEIOLATCH_EX。当一个任务在锁存器上等待一个IO请求中的缓冲区时，这两种等待锁就会启动。如果等待时间过长，说明磁盘子系统出现故障。
--wait_time_ms列包含一个工作进程在悬挂状态下花费的时间和在可运行状态下的花费时间。
--signal_wait_time_ms表示的是一个工作进程在可运行状态下花费的时间。
--wait_time_ms - signal_wait_time_ms标识等待IO花费的时间。
--返回的是SQL Server 启动后的等待
--查找每次执行引发IO最多的前10位查询
SELECT top(10)
(total_logical_reads/execution_count) AS avg_logical_reads,
(total_logical_writes/execution_count) AS avg_logical_writes,
(total_physical_reads/execution_count) AS avg_phys_reads,
execution_count,plan_handle,
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX),text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY (total_logical_reads + total_logical_writes) DESC;
--使用句柄获得查询计划
select * from sys.dm_exec_query_plan(0x050005003AEA4921402337D9010000000000000000000000)
--用于确认丢失的索引以及他们的有用性
SELECT t1.object_id, t2.user_seeks, t2.user_scans,
t1.equality_columns, t1.inequality_columns,*
FROM sys.dm_db_missing_index_details AS t1,
sys.dm_db_missing_index_group_stats AS t2,
sys.dm_db_missing_index_groups AS t3
WHERE database_id = DB_ID()
-- AND object_id = OBJECT_ID('box_m')
AND t1.index_handle = t3.index_handle
AND t2.group_handle = t3.index_group_handle order by t2.user_seeks desc;
--显示当前正在等待的工作进程
SELECT session_id, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGE%LATCH_%'
AND resource_description like '2:%';
--查询tempdb中当前引发最多分配和回收操作的查询
SELECT TOP 10 t1.session_id, t1.request_id, t1.task_alloc,
t1.task_dealloc, t2.plan_handle,
(SELECT SUBSTRING (text, t2.statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX),text)) * 2
ELSE statement_end_offset
END - t2.statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM (SELECT session_id, request_id,
SUM(internal_objects_alloc_page_count +
user_objects_alloc_page_count) AS task_alloc,
SUM(internal_objects_dealloc_page_count +
user_objects_dealloc_page_count) AS task_dealloc
FROM sys.dm_db_task_space_usage
GROUP BY session_id, request_id) AS t1,
sys.dm_exec_requests AS t2
WHERE t1.session_id = t2.session_id AND
(t1.request_id = t2.request_id) AND t1.session_id > 50
ORDER BY t1.task_alloc DESC;
--查询应用程序中遇到的前10位等待
SELECT TOP 20 wait_type, waiting_tasks_count AS tasks,
wait_time_ms, max_wait_time_ms AS max_wait,
signal_wait_time_ms AS signal
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;
--查询在任一给定时刻所有授权给当前执行事务或当前执行事务等待的锁
SELECT request_session_id AS spid, resource_type AS rt,
resource_database_id AS rdb,
(CASE resource_type
WHEN 'OBJECT'
THEN object_name(resource_associated_entity_id)
WHEN 'DATABASE'
THEN ' '
ELSE (SELECT object_name(object_id)
FROM sys.partitions
WHERE hobt_id=resource_associated_entity_id)
END) AS objname,
resource_description AS rd, request_mode AS rm, request_status AS rs
FROM sys.dm_tran_locks;
--查询阻塞的生存期和正被阻塞的事务执行的T-SQL语句
SELECT t1.resource_type,
'database' = DB_NAME(resource_database_id),
'blk object' = t1.resource_associated_entity_id,
t1.request_mode, t1.request_session_id,
t2.blocking_session_id,
t2.wait_duration_ms,
(SELECT SUBSTRING(text, t3.statement_start_offset/2 + 1,
(CASE WHEN t3.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max),text)) * 2
ELSE t3.statement_end_offset
END - t3.statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
t2.resource_description
FROM
sys.dm_tran_locks AS t1,
sys.dm_os_waiting_tasks AS t2,
sys.dm_exec_requests AS t3
WHERE
t1.lock_owner_address = t2.resource_address AND
t1.request_request_id = t3.request_id AND
t2.session_id = t3.session_id;
--查询显示表中所有索引的操作统计值
SELECT index_id, range_scan_count,
row_lock_count, page_lock_count
FROM sys.dm_db_index_operational_stats(DB_ID('<db-name>'),
OBJECT_ID('employee'), NULL, NULL);
--跟索引相关的视图
--返回数据库中表或索引的每个分区的当前低级 I/O、锁定、闩锁和访问方法活动。
sys.dm_db_index_operational_stats (
{ database_id | NULL }
, { object_id | NULL }
, { index_id | NULL | 0 }
, { partition_number | NULL }
)
--leaf_insert_count 叶级插入的累积计数。
--leaf_delete_count 叶级删除的累积计数。
--leaf_update_count 叶级更新的累积计数。
--leaf_ghost_count 标记为删除但尚未删除的叶级行的累积计数。清除线程会按设置的间隔删除这些行。此值不包括由于某个快照隔离事务未完成而保留的行。有关快照隔离事务的详细信息，请参阅数据库引擎中基于行版本控制的隔离级别。
--nonleaf_insert_count 叶级以上的插入累积计数。 0 = 堆
--nonleaf_delete_count 叶级以上的删除累积计数。 0 = 堆
--nonleaf_update_count 叶级以上的更新累积计数。 0 = 堆
--leaf_allocation_count 索引或堆中的叶级页分配的累积计数。对于索引，页分配与页拆分对应。
--nonleaf_allocation_count 叶级以上由页拆分引起的页分配的累积计数。 0 = 堆
--leaf_page_merge_count 叶级页合并的累积计数。
--nonleaf_page_merge_count 叶级以上页合并的累积计数。 0 = 堆
--range_scan_count 从索引或堆开始的范围和表扫描的累积计数。
--singleton_lookup_count 对索引或堆的单行检索的累积计数。
--forwarded_fetch_count 通过前推记录提取的行计数。 0 = 索引
--lob_fetch_in_pages 从 LOB_DATA 分配单元检索到的大型对象 (LOB) 页的累积计数。这些页包含存储在类型为 text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max) 和 xml 的列中的数据。有关详细信息，请参阅数据类型 (Transact-SQL)。有关分配单元的详细信息，请参阅表组织和索引组织。
--lob_fetch_in_bytes 检索到的 LOB 数据字节数的累积计数。
--lob_orphan_create_count 为大容量操作创建的孤立 LOB 值的累积计数。0 = 非聚集索引
--lob_orphan_insert_count 大容量操作期间插入的孤立 LOB 值的累积计数。0 = 非聚集索引
--row_overflow_fetch_in_pages 从 ROW_OVERFLOW_DATA 分配单元检索到的行溢出数据页数的累积计数。这些页包含已推送到行外的 varchar(n)、nvarchar(n)、varbinary(n) 和 sql_variant 类型的列中存储的数据。有关详细信息，请参阅行溢出数据超过 8 KB。有关分配单元的详细信息，请参阅表组织和索引组织。
--row_overflow_fetch_in_bytes 检索到的行溢出数据字节数的累积计数。
--column_value_push_off_row_count 已推出行外以使插入或更新的行可容纳在页中的 LOB 数据和行溢出数据的列值累积计数。
--column_value_pull_in_row_count 已请求到行内的 LOB 数据和行溢出数据的列值的累积计数。当更新操作释放记录中的空间，并提供将一个或多个行外值从 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元请求到 IN_ROW_DATA 分配单元中的机会时，就会出现此计数。有关分配单元的详细信息，请参阅表组织和索引组织。
--row_lock_count 请求的行锁的累积数量。
--row_lock_wait_count 数据库引擎等待行锁的累积次数。
--row_lock_wait_in_ms 数据库引擎等待行锁的总毫秒数。
--page_lock_count 请求的页锁的累积数量。
--page_lock_wait_count 数据库引擎等待页锁的累积次数。
--page_lock_wait_in_ms 数据库引擎等待页锁的总毫秒数。
--index_lock_promotion_attempt_count 数据库引擎尝试升级锁的累积次数。
--index_lock_promotion_count 数据库引擎升级锁的累积次数。
--page_latch_wait_count 数据库引擎由于闩锁争用而等待的累积次数。
--page_latch_wait_in_ms 数据库引擎由于闩锁争用而等待的累积毫秒数。
--page_io_latch_wait_count 数据库引擎等待 I/O 页闩锁的累积次数。
--page_io_latch_wait_in_ms 数据库引擎等待页 I/O 闩锁的累积毫秒数。
--返回指定表或视图的数据和索引的大小和碎片信息
sys.dm_db_index_physical_stats (
5 --表或视图的数据库 ID。
, 717166992 --索引所在的表或视图的对象 ID。
, NULL --索引的索引 ID。
, NULL --所属对象内从 1 开始的分区号；表、视图或索引。 1 = 未分区的索引或堆。
, 'DETAILED' --模式名称。mode 指定用于获取统计信息的扫描级别。mode 的数据类型为 sysname。有效输入为 DEFAULT、NULL、LIMITED、SAMPLED 或 DETAILED。默认值 (NULL) 为 LIMITED
)
--返回不同类型索引操作的计数以及上次执行每种操作的时间
sys.dm_db_index_usage_stats
--database_id 定义表或视图所在的数据库 ID。
--object_id 为其定义索引的表或视图的 ID。
--index_id 索引的 ID。
--user_seeks 通过用户查询执行的搜索次数。
--user_scans 通过用户查询执行的扫描次数。
--user_lookups 通过用户查询执行的查找次数。
--user_updates 通过用户查询执行的更新次数。
--last_user_seek 用户上次执行搜索的时间。
--last_user_scan 用户上次执行扫描的时间。
--last_user_lookup 用户上次执行查找的时间。
--last_user_update 用户上次执行更新的时间。
--system_seeks 通过系统查询执行的搜索次数。
--system_scans 通过系统查询执行的扫描次数。
--system_lookups 通过系统查询执行的查找次数。
--system_updates 通过系统查询执行的更新次数。
--last_system_seek 系统上次执行搜索的时间。
--last_system_scan 系统上次执行扫描的时间。
--last_system_lookup 系统上次执行查找的时间。
--last_system_update 系统上次执行更新的时间。