目錄
确定思路
wait event的基本troubleshooting
虛拟檔案資訊（virtual file Statistics）
性能名額
執行計劃緩沖的使用
總結
性能調優很難有一個固定的理論。調優本來就是處理一些特殊的性能問題。
通常一旦拿到一個伺服器那麼就先做一下性能檢查。檢視整個資料庫是運作在什麼樣的狀況下的。
分析收集的資料想像這種情況是否合理。
确定思路
一個資料庫操作的時間都是執行時間+等待時間，在無法估計執行時間的時候看要看看等待時間。
那麼等待時間分為鎖等待時間和資源等待時間。
那麼就先用 sys.dm_os_wait_stats動态性能視圖，檢視主要的狀況。如果pageiolatch_sh等待很大，那麼就說明，session在等待buffer pool的頁。當一個session要select一些資料，但是剛剛好，這些資料并沒有在buffer pool 中，那麼sql server 就會配置設定一些緩存這些緩存是屬于buffer pool 的，用來存放從磁盤讀取出來的資料，在讀取的時候都會給這些緩存上latch（可以看成是鎖）。當存在io瓶頸的時候，那麼磁盤上的資料不能立即讀到buffer pool 中就會出現等待latch的情況。這個可能是io過慢，也有可能是在做一些多餘的io造成的。
那麼接下來檢視sys.dm_io_virtual_file_stats 性能視圖來确定哪個資料庫造成了怎麼大的延遲。并且通過physical disk \avg.disk reads/sec和physical disk\avg.disk writes/sec來确定到底資料庫有多少io負載。
接下來通過 sys.dm_exec_query_stats 檢視執行計劃，通過檢視高實體讀的sql和執行計劃看看有沒有優化的空間。如添加索引，修改sql，優化引擎通路資料的方法。
有可能，sql 語句已經不能再優化，但是性能還是不行，往往這種sql是報表查詢類的sql，會從磁盤中讀取大量資料，很多資料往往在buffer pool 找不到那麼就會發生大量的pageiolatch_sh等待。這時，我們就要看看是否是記憶體不足照成的，用perfmon 檢視 page life expectancy(頁壽命長度)，free list stalls/sec(等待空頁的次數)和Lazy writes/sec。 page life expectancy 波動很厲害，free liststalls/sec 一直大于0，Lazy writes/sec 的量也很大，那麼就說明buffer pool 不夠大。但是也有可能是sql 寫的不嚴謹，select了很多沒必要的資料。
在上面的troubleshooting 過程中，很容易走入一個誤區，sys.dm_io_virtual_file_stats和一些性能名額，就會很容易斷定說io有問題，需要額外的預算來擴充io的性能，但是擴充io是比較貴的。io性能不理想很有可能miss index或者buffer pool的壓力造成的。如果單純的添加實體裝置，但是沒有找到根本原因，當資料量增長後，依然會出現相同的問題。
waitevent的基本troubleshooting
wait statistics 是SQLOS跟蹤得到的
SQLOS 是一個僞作業系統，是SQL Server 的一部分，有排程線程，記憶體管理等其他操作。
SQLOS比windows排程器更好的排程sql server 線程。SQLOS的排程器間的互動，會比強占式的系統排程又更好的并發性
當sql server 等待一個sql 執行的時候，等待的時間會被sqlos捕獲，這些時間都會存放在 sys.dm_os_wait_stats性能視圖中。各種等待時間的長度，并且和其他的性能視圖，性能計數器結合，可以很明顯的看出性能問題。
對于未知的性能問題sys.dm_os_wait_stats 用來判斷性能問題是很好用的，但是在伺服器重新開機或者dbcc 指令清空 sys.dm_os_wait_stats後會很好分析，時間一長就很難分析，因為等待時間是累計的，搞不清楚哪個是你剛剛執行出來的時間。當然可以考慮先捕獲一份，當sql 執行完後，再捕獲一份，進行比較。
檢視wait event，得到的資訊隻是實際性能問題的其中一個症狀，為了更利用wait event 資訊，你需要了解資源等待和非資源等待的差別，還有需要了解其他troubleshooting資訊。
在sql server中有一部分的sql是沒問題的，可以使用一下sql 語句檢視說有的 session的wait event
SELECT DISTINCT
wt.wait_type
FROM sys.dm_os_waiting_tasks AS wt
JOIN sys.dm_exec_sessions AS s ON wt.session_id = s.session_id
WHERE s.is_user_process = 0
因為很大一部分是正常的，是以提供了一個sql 來過濾正常查詢操作
SELECT TOP 10
wait_type ,
max_wait_time_mswait_time_ms ,
signal_wait_time_ms ,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )
AS percent_total_waits ,
100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )
AS percent_total_signal_waits ,
100.0 * ( wait_time_ms - signal_wait_time_ms )
/ SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0 -- remove zerowait_time
AND wait_type NOT IN -- filter outadditional irrelevant waits
( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
'RESOURCE_QUEUE' )
ORDER BY wait_time_ms DESC
檢查wait event一般隻關注前幾個等待資訊，檢視高等待時間的等待類型。
CXPACKET：
表明并發查詢的等待時間，通常不會立刻産生問題，也可能是因為别的性能問題，導緻CXPACKET等待過高。
SOS_SCHEDULER_YIELD
任務在執行的時候被排程器中斷，被放入可執行隊列等待被運作。這個時間過長可能是cpu壓力造成的。
THREADPOOL
一個任務必須綁定到一個工作任務才能執行，threadpool 就是task等待被綁定的時間。出現threadpool過高可能是，cpu不夠用，也可能是大量的并發查詢。
LCK_*
這中等待類型過高，說明可能session發生堵塞，可以看sys.dm_db_index_operational_stats 獲得更深入的内容
PAGEIOLATCH_*,IO_COMPLETION,WRITELOG
這些往往和磁盤的io瓶頸關聯，根本原因往往都是效率極差的查詢操作消費了過多的記憶體。PAGEIOLATCH_*和資料庫檔案的讀寫延遲相關。writelog和事務日 志檔案的讀寫相關。這些等待最好和sys.dm_io_virtual_file_stats 關聯确定問題是發生在資料庫，資料檔案，磁盤還是整個執行個體。
PAGELATCH_*
在buffer pool 中非io等待latch。PAGELATCH_* 大量的等待通常是配置設定沖突。當tempdb中大量的對象要被删除或者建立，那麼系統就會對SGAM，GAM和PFS的配置設定發生沖突。
LATCH_*
LATCH_*和内部cache的保護，這種等待過高會發生大量的問題。可以通過 sys.dm_os_latch_stats 檢視詳細内容。
ASYNC_NETWORK_IO
這個等待不完全表明網絡的瓶頸。事實上多數情況下是用戶端程式一行一行的處理sql server 的結果集導緻。發生這種問題那麼就修改用戶端代碼。
簡單的解釋了主要的等待，減少在分析wait event 的時候走的彎路。
為了确定是否已經排除問題可以用DBCC SQLPERF('sys.dm_os_wait_stats', clear)清除wait event。也可以用2個wait event 資訊相減。
虛拟檔案資訊（virtual file Statistics）
通常，當使用wait event 分析問題的時候，都為認為很想io的性能問題。但是wait event 并不能說明io是怎麼發生的，是以很有可能會誤判
這就是為什麼要使用sys.dm_os_latch_stats 檢視的原因，可以檢視累計的io統計資訊，每個檔案的讀寫資訊，日志檔案的讀寫，可以計算讀寫的比例，io等待的次數，等待的時間。
SELECT DB_NAME(vfs.database_id) AS database_name ,
vfs.database_id ,
vfs.FILE_ID ,
io_stall_read_ms / NULLIF(num_of_reads, 0) AS avg_read_latency ,
io_stall_write_ms / NULLIF(num_of_writes, 0)
AS avg_write_latency ,
io_stall / NULLIF(num_of_reads + num_of_writes, 0)
AS avg_total_latency ,
num_of_bytes_read / NULLIF(num_of_reads, 0)
AS avg_bytes_per_read ,
num_of_bytes_written / NULLIF(num_of_writes, 0)
AS avg_bytes_per_write ,
vfs.io_stall ,
vfs.num_of_reads ,
vfs.num_of_bytes_read ,
vfs.io_stall_read_ms ,
vfs.num_of_writes ,
vfs.num_of_bytes_written ,
vfs.io_stall_write_ms ,
size_on_disk_bytes / 1024 / 1024. AS size_on_disk_mbytes ,
physical_name
FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS vfs
JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id
AND vfs.FILE_ID = mf.FILE_ID
ORDER BY avg_total_latency DESC
檢視是否讀寫過大，平均延時是否過高。通過這個可以知道是否是io的問題。
如果資料檔案和日志檔案是共享磁盤隊列的，avg_total_latency 比預期的要高，那麼就有可能是io的問題了
如果目前的資料庫是用來歸檔資料到比較慢的存儲中，可能會有很高的PAGEIOLATCH_*和io_stall那麼我們就需要确定怎麼高的等待是否屬于歸檔的線程，是以在troubleshooting的時候要注意你的伺服器的類型。
如果你的磁盤讀寫比例是1:10，而且又很高的 avg_total_latency 那麼就考慮把磁盤隊列換成 raid5，為io讀提供更多的主軸。
性能名額
在最開始的troubleshooting，性能名額是非常有用的。也可以用來驗證自己的判斷是否正确。
PLA 是一個很好的性能日志分析工具http://pal.codeplex.com. 可惜沒有中文版，當然可以去codeplex 下載下傳源代碼自己修改。這個工具内嵌了性能收集集合，也就是通常要收集的一些性能名額。也内嵌了閥值模闆，可以在性能名額收集完之後做分析。
sql server 對自己的性能名額有對應的性能視圖 sys.dm_os_performance_counters。對于性能名額有些是累計值，是以需要做2個快照，相減計算結果。
DECLARE @CounterPrefix NVARCHAR(30)
SET @CounterPrefix = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
THEN 'SQLServer:'
ELSE 'MSSQL$' + @@SERVICENAME + ':'
END ;
-- Capture thefirst counter set
SELECT CAST(1 AS INT) AS collection_instance ,
[OBJECT_NAME] ,
counter_name ,
instance_name ,
cntr_value ,
cntr_type ,
CURRENT_TIMESTAMP AS collection_time
INTO #perf_counters_init
FROM sys.dm_os_performance_counters
WHERE ( OBJECT_NAME = @CounterPrefix + 'Access Methods'
AND counter_name = 'Full Scans/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Access Methods'
AND counter_name = 'Index Searches/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Buffer Manager'
AND counter_name = 'Lazy Writes/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Buffer Manager'
AND counter_name = 'Page lifeexpectancy'
)
OR ( OBJECT_NAME = @CounterPrefix + 'General Statistics'
AND counter_name = 'Processes Blocked'
)
OR ( OBJECT_NAME = @CounterPrefix + 'General Statistics'
AND counter_name = 'User Connections'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Locks'
AND counter_name = 'Lock Waits/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Locks'
AND counter_name = 'Lock Wait Time(ms)'
)OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'SQLRe-Compilations/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Memory Manager'
AND counter_name = 'Memory GrantsPending'
)
OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'Batch Requests/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'SQLCompilations/sec'
)
-- Wait on Secondbetween data collection
WAITFOR DELAY '00:00:01'
-- Capture thesecond counter set
SELECT CAST(2 AS INT) AS collection_instance ,
OBJECT_NAME ,
counter_name ,
instance_name ,
cntr_value ,
cntr_type ,
CURRENT_TIMESTAMP AS collection_time
INTO #perf_counters_second
FROM sys.dm_os_performance_counters
WHERE ( OBJECT_NAME = @CounterPrefix + 'Access Methods'
AND counter_name = 'Full Scans/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Access Methods'
AND counter_name = 'Index Searches/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Buffer Manager'
AND counter_name = 'Lazy Writes/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Buffer Manager'
AND counter_name = 'Page lifeexpectancy'
)
OR ( OBJECT_NAME = @CounterPrefix + 'General Statistics'
AND counter_name = 'Processes Blocked'
)
OR ( OBJECT_NAME = @CounterPrefix + 'General Statistics'
AND counter_name = 'User Connections'
)OR ( OBJECT_NAME = @CounterPrefix + 'Locks'
AND counter_name = 'Lock Waits/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Locks'
AND counter_name = 'Lock Wait Time(ms)'
)
OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'SQLRe-Compilations/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Memory Manager'
AND counter_name = 'Memory GrantsPending'
)
OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'Batch Requests/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'SQLCompilations/sec'
)
-- Calculate thecumulative counter values
SELECT i.OBJECT_NAME ,
i.counter_name ,
i.instance_name ,
CASE WHEN i.cntr_type = 272696576
THEN s.cntr_value - i.cntr_value
WHEN i.cntr_type = 65792 THEN s.cntr_value
END AS cntr_value
FROM #perf_counters_init AS i
JOIN #perf_counters_second AS s
ON i.collection_instance + 1 = s.collection_instance
AND i.OBJECT_NAME = s.OBJECT_NAME
AND i.counter_name = s.counter_name
AND i.instance_name = s.instance_name
ORDER BY OBJECT_NAME
-- Cleanup tables
DROP TABLE #perf_counters_init
DROP TABLE #perf_counters_second
主要收集一下性能名額：
• SQLServer:AccessMethods\Full Scans/sec
• SQLServer:AccessMethods\Index Searches/sec
• SQLServer:BufferManager\Lazy Writes/sec
• SQLServer:BufferManager\Page life expectancy
• SQLServer:BufferManager\Free list stalls/sec
• SQLServer:GeneralStatistics\Processes Blocked
• SQLServer:GeneralStatistics\User Connections
• SQLServer:Locks\LockWaits/sec
• SQLServer:Locks\LockWait Time (ms)
• SQLServer:MemoryManager\Memory Grants Pending
• SQLServer:SQLStatistics\Batch Requests/sec
• SQLServer:SQLStatistics\SQL Compilations/sec
• SQLServer:SQLStatistics\SQL Re-Compilations/sec
這裡又2個 Access Methods 性能名額，說明了通路資料庫不同的方式，full scans/sec 表示了發生在資料庫中索引和表掃描的次數。
如果io出現瓶頸，并且伴随着大量的掃描出現，那麼很有可能就是miss index 或者sql 代碼不理想照成的。那麼多少次數到多少時可以認為有問題呢？在通常狀況下 index searches/sec 比 full scans/sec 高800-1000，如果 full sacans/sec過高，那麼很有可能是miss index 和多餘的io操作引起的。
Buffer Manager 和 memory manager 通常用來檢測是否存在記憶體壓力，lazy writes/sec，page life expectancy ,free list stalls/sec 用來佐證是否處于記憶體壓力。
很多網上的文章和論壇都說，如果Page Life expectancy 低于300秒的時候，存在記憶體壓力。但是這隻是對于以前隻有4g記憶體的伺服器的，現在的伺服器一般都是32g以上記憶體5分鐘的閥值已經不能在說明問題了。300秒雖然已經不再适用，但是我們可以用300來作為基值來計算目前的PLE的閥值(32/4)*300 = 2400那麼如果是32g的伺服器設定為2400可能會比較合适。
如果PEL一直低于閥值，并且 lazy writes/sec一直很高，那麼有可能是buffer pool壓力造成的。如果這個時候full scans/sec值也很高，那麼請先檢查是不是miss index 或者讀取了多餘的資料。
generalstatistics\processes blocked,locks\lock waits/sec和locks\lock wait time(ms)如果這3個值都是非0那麼資料庫會發生堵塞。
SQL Statistics 計數器說明了sql 的編譯或者重編譯的速度，sql compilations/sec和 batch requests/sec 成正比，那麼很有可能大量sql 通路都是 ad hoc方式無法通過執行計劃緩沖優化它們，如果 SQLRe-compilations/sec 和 batch requests/sec 成正比，那麼應用程式中可能又強制重新編譯的選項。
memorymanager\momory grants pending 表示等待授權記憶體的等待，如果這個值很高那麼增加記憶體可能會有效果。但是也有可能是大的排序，hash操作也可能造成，可以使用調整索引或者查詢來減小這種狀況。
執行計劃緩沖的使用
執行計劃緩沖是sql server 的内部元件，可以使用 sys.dm_exec_query_stats 查詢，下面有個sql查詢實體讀前十的計劃
SELECT TOP 10
execution_count ,
statement_start_offset AS stmt_start_offset ,
sql_handle ,
plan_handle ,
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 ,
t.text
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
ORDER BY avg_physical_reads DESC
在執行計劃裡面的這些值可以看出哪些查詢實體io操作很頻繁，也可以和wait event 和虛拟檔案結合分析有問題的io操作。
我們也可以使用sys.dm_exec_query_plan()檢視存在記憶體裡面的執行計劃。
這裡又2本書深入的講述了查詢執行計劃：《SQL Server 2008 Query performance tuningdistilled》，《Inside Microsoft SQL Server 2008:T-SQLQuerying》。
sys.dm_exec_query_stats還用來查詢 cpu時間，最長執行時間，或者最頻繁的sql
在sql server 2008中加入了2個額外的列，query_hash,query_plan_hash用來聚合相似的sql的。對于ad hoc 過大的伺服器可以用來分析相似的sql，不同的編譯的總數。
總結
上面各個部分都講了一個思維，一個思路。要想性能調優調的好，那麼就先系統體系結構，你要清楚如前面說的miss index 一旦發生，那麼不知會影響io，還會影響記憶體和cpu。接下來要會分析，從一開始的簡單的性能統計資訊，往下分析，用其他統計資訊排除問題，得到性能問題的真正原因。
文章來源于：Troubleshooting SQL Server: A Guide for theAccidental DBA 如果看不懂的或者想更深入了解的，可以看原文。
轉載于:https://www.cnblogs.com/davidhou/p/5408234.html
![ACS基本配置-權限等級管理[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)