本文共 5585 字，大约阅读时间需要 18 分钟。
SQL Server 2008 R2有490种等待状态（SQL Server 2014 CTP1中甚至多达759种)，
但是常用的、有价值的其实不多。总的来说，等待类型分为3类:资源等待、队列等待和 外部等待。在日常使用中，通常会过滤掉系统相关的等待类型，因为这些对诊断性能瓶颈 没有多大用处，同时还过滤掉等待时间为0的类型，语句如下SELECT wait_type , signal_wait_time_ms , wait_time_msFROM sys.dm_os_wait_statsWHERE wait_time_ms > 0 AND wait_type NOT IN ( 'CLR_SEMAPHORE', 'CLR_AUTO_EVENT', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP' )ORDER BY signal_wait_time_ms DESC
在数据库配置方面，首先要考虑的是，如果是OLTP系统，理想情况下事务很短，这
时候就没有必要通过并行运行来提高运行速度了。所以对于这类系统，有一个极端方法(如 非必要不要用)，即把最大并行度（Max Degree of Parallelism）设为1，强制SQL Server不 去使用并行操作，从而减少不必要的资源等待。SELECT *FROM sys.configurationsWHERE name LIKE '%Max Degree of Parallelism%'GOEXEC sys.sp_configure N'show advanced options', N'1'GORECONFIGUREGOEXEC sys.sp_configure N'Max Degree of Parallelism', N'1'GORECONFIGUREGO
如果是OLAP系统，由于事务普遍较长，所以并行操作往往能提高速度和资源利用率。
这时候可以让SQL Server自己控制并行，也就是把最大并行度设为0(即不限制)。 下面的脚本用于查询在计划缓存中存在并行查询的语句。SELECT TOP 10 p.* , q.* , qs.* , cp.plan_handleFROM sys.dm_exec_cached_plans cp CROSS APPLY sys.Dm_exec_query_plan(cp.plan_handle) p CROSS APPLY sys.Dm_exec_sql_text(cp.plan_handle) AS q JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = cp.plan_handleWHERE cp.cacheobjtype = 'Compiled Plan' AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/SQL Server/2004/07/showplan";max(//p:RelOp/@Parallel)', 'float') > 0OPTION ( MAXDOP 1 ) 正如前面所述，这个等待通常与CPU压力有关，最简单的解决方案就是增加CPU或
者提升CPU性能。不过通常服务器也不是随便可以改的，所以不应该为了解决问题而解决 问题，要多考虑其产生的原因。当查询需要大面积扫描时，会消耗CPU资源。.下面的脚本 可以查看使用最多CPU的查询语句，之后就可以针对这些结果来进行优化了。除了对这里 找到的查询进行优化之外，还可以查找运行时间最长的脚本进行优化。SELECT SUBSTRING(qt.text, ( qs.statement_start_offset / 2 ) + 1, ( ( CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2 ) + 1) , qs.execution_count , qs.total_logical_reads , qs.last_logical_reads , qs.total_logical_writes , qs.last_logical_writes , qs.total_worker_time , qs.last_worker_time , qs.total_elapsed_time / 1000000 AS total_elapsed_time_in_S , qs.last_elapsed_time / 1000000 AS last_elapsed_time_in_S , qs.last_execution_time , qp.query_planFROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.sql_handle) qpORDER BY qs.total_worker_time DESC --CPU时间
常见降低 WRITELOG等待的手段是把日志文件和数据文件及其他文件如TEMPDB存
放到独立的磁盘中。另外就是避免类似游标等的低效操作，同时加快提交事务的频率，最 后检查I/O相关的计数器。 除此之外，删除没用的非聚集索引、减少日志开销、修改索引键或使用填充因子减少 页分裂(第6章介绍过)、修改程序架构、把负载分摊到多个服务器或者数据库中，这些手 段都能减少出现这类等待的情况。 3．扩充说明 不要见到这种等待就以为是I/O问题，也不要直接增加日志文件。上面已经说过， 增加日志文件解决不了这类问题。 应该进行如下更加深入的分析: 查看sys.dm_.io_virtual_file_stats的数据。 查看LOGBUFFER等待（下面介绍)，看是否存在对日志缓冲区(log buffer)的争抢。 查看日志文件所在磁盘的磁盘等待队列。 查看事务的平均大小。 查看是否有大量的页分裂，因为这样也会导致大量的日志。， 下面的脚本用于检查活动事务的日志情况。SELECT DTST.[session_id] , DES.[login_name] AS [Login Name] , DB_NAME(DTDT.database_id) AS [Database] , DTDT.[database_transaction_begin_time] AS [Begin Time] , -- DATEDIFF(ms,DTDT.[database_transaction_begin_time], GETDATE()) AS [Durationms], CASE DTAT.transaction_type WHEN 1 THEN 'Read/write' WHEN 2 THEN 'Read-only' WHEN 3 THEN 'System' WHEN 4 THEN 'Distributed' END AS [Transaction Type] , CASE DTAT.transaction_state WHEN 0 THEN 'Not fully initialized' WHEN 1 THEN 'Initialized, not started' WHEN 2 THEN 'Active' WHEN 3 THEN 'Ended' WHEN 4 THEN 'Commit initiated' WHEN 5 THEN 'Prepared, awaiting resolution' WHEN 6 THEN 'Committed' WHEN 7 THEN 'Rolling back' WHEN 8 THEN 'Rolled back' END AS [Transaction State] , DTDT.[database_transaction_log_record_count] AS [Log Records] , DTDT.[database_transaction_log_bytes_used] AS [Log Bytes Used] , DTDT.[database_transaction_log_bytes_reserved] AS [Log Bytes RSVPd] , DEST.[text] AS [Last Transaction Text] , DEQP.[query_plan] AS [Last Query Plan]FROM sys.dm_tran_database_transactions DTDT INNER JOIN sys.dm_tran_session_transactions DTST ON DTST.[transaction_id] = DTDT.[transaction_id] INNER JOIN sys.[dm_tran_active_transactions] DTAT ON DTST.[transaction_id] = DTAT.[transaction_id] INNER JOIN sys.[dm_exec_sessions] DES ON DES.[session_id] = DTST.[session_id] INNER JOIN sys.dm_exec_connections DEC ON DEC.[session_id] = DTST.[session_id] LEFT JOIN sys.dm_exec_requests DER ON DER.[session_id] = DTST.[session_id] CROSS APPLY sys.dm_exec_sql_text(DEC.[most_recent_sql_handle]) AS DEST OUTER APPLY sys.dm_exec_query_plan(DER.[plan_handle]) AS DEQPORDER BY DTDT.[database_transaction_log_bytes_used] DESC; -- ORDER BY [Duration ms] DESC;
转载地址：http://lsesn.baihongyu.com/