Sql Server 查询系统资源的使用情况 (恢复模式, 事务日志的大小等)

• CPU以及资源等待所占的百分比

 -- Clear Wait Stats，清理等待的统计。

 --如果你的SQL Server运行很长时间，并且进行了重大改变，例如添加了一个新索引，那么你应该考虑清理旧的统计信息，否则旧的累计统计数据会影响结果。
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) ;
     

        -- Total waits are wait_time_ms (high signal waits indicates CPU pressure)
SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits] ,

CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [%resource waits]

FROM sys.dm_os_wait_stats ;

此查询可用于帮助确认 CPU 压力。因为信号等待时间等待 CPU 服务线程，如果记录总信号等待它大致大比 10%到 15%，则表明这是一种很好的 CPU 压力指标

• 确定占用时间的最多资源

-- Isolate top waits for server instance since last restart
-- or statistics clear
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 ( 'CLR_SEMAPHORE', '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', 'CLR_AUTO_EVENT',
'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN' ) )

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 ; -- percentage threshold

此脚本将会帮助您找到实例级最大的瓶颈。这可以帮助您您优化的努力集中在一个特定类型的问题。例如，如果累积顶部等待类型是我/O 有关，磁盘，然后会要调查这一问题进一步使用磁盘相关 DMV 查询和性能监视器计数器。

• 查询数据库恢复模式、 日志重用等待描述、 事务日志的大小、 使用的日志空间、 日志使用百分比、 兼容级别和页面验证

-- Recovery model, log reuse wait description, log file size,
-- log usage size and compatibility level for all databases on instance

SELECT db.[name] AS [Database Name] , db.recovery_model_desc AS [Recovery Model] 
, db.log_reuse_wait_desc AS [Log Reuse Wait Description] , ls.cntr_value AS [Log Size (KB)] 
,lu.cntr_value AS [Log Used (KB)] 
, CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Log Used %] 
, db.[compatibility_level] AS [DB Compatibility Level] , db.page_verify_option_desc AS [Page Verify Option]
FROM sys.databases AS db 
INNER JOIN sys.dm_os_performance_counters AS lu ON db.name = lu.instance_name 
INNER JOIN sys.dm_os_performance_counters AS ls ON db.name = ls.instance_name
WHERE lu.counter_name LIKE 'Log File(s) Used Size (KB)%' AND 
ls.counter_name LIKE 'Log File(s) Size (KB)%' ;