Sql Server 查詢系統(tǒng)資源的使用情況

如果你的SQL Server運行很長時間，并且進(jìn)行了重大改變，例如添加了一個新索引，那么你應(yīng)該考慮清理舊的統(tǒng)計信息，否則舊的累計統(tǒng)計數(shù)據(jù)會影響結(jié)果。

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 ;
此查詢可用于幫助確認(rèn) CPU 壓力。因為信號等待時間等待 CPU 服務(wù)線程，如果記錄總信號等待它大致大比 10%到 15%，則表明這是一種很好的 CPU 壓力指標(biāo)

確定占用時間的最多資源
-- 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

此腳本將會幫助您找到實例級最大的瓶頸。這可以幫助您您優(yōu)化的努力集中在一個特定類型的問題。例如，如果累積頂部等待類型是我/O 有關(guān)，磁盤，然后會要調(diào)查這一問題進(jìn)一步使用磁盤相關(guān) DMV 查詢和性能監(jiān)視器計數(shù)器。

查詢數(shù)據(jù)庫恢復(fù)模式、 日志重用等待描述、 事務(wù)日志的大小、 使用的日志空間、 日志使用百分比、 兼容級別和頁面驗證
-- 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)%' ;