深入SQL Server資料庫速度提升問題（二）

　　捕獲等待類資料

　　SQL伺服器已經提供了等待類的觀點，但是遺憾的是，這些觀點大多數情況下有些模糊因此難以發揮作用。從SQL Server 7.0和2000開始，資料庫管理員可以使用企業管理器來檢視等待類。問題是所有企業管理器提供的都是等待類的名稱和程式等待的時長。當SQL Server Management Studio被引入SQL Server 2005的時間，主動查詢和對話的觀點都得到了儲存。此外，資料庫管理員會獲得一個等待類和等待時間。

　　目前，觀察SQL伺服器中統計的最佳方法是使用動態管理檢視(DMVs)。如果你仍在執行SQL Server 2000或更舊的版本，那就無法使用這一功能。與檢視統計關係最密切的動態管理檢視是sys.dm_exec_requests，sys.dm_exec_query_stats和sys.dm_os_wait_stats。

　　l sys.dm_exec_requests： 該動態管理檢視提供了與 給定SQL伺服器上執行的每個請求相關的資訊。當看到等待狀態的時候，你所關心的只是其中的某些部分：尤其是sql_handle, wait_type, wait_time, last_wait_type 和 wait_resource。

　　l sys.dm_exec_query_stats： 該檢視返回了用於緩衝查詢的效能統計資訊。通過使用sys.dm_exec_requests中的sql_handle細則來加入檢視，就可以獲取等待發生頻率的圖片。記住，該檢視不會給出詳細資訊。

　　l sys.dm_os_wait_stats： 該檢視提供所有SQL伺服器上等待狀態的圖片。它提供所有不同等待狀態的列表和狀態任務的詳細資訊，包括狀態中等待任務的數量和所有等待時間以及平均等待時間。

　　情境一：識別問題查詢

　　資料庫管理員遭遇的最惱人的問題是“問題查詢”(圖一)。通常這樣的查詢是程式設計師定義的執行緩慢的查詢。資料庫管理員通常會聽說這類查詢“在開發的時候能正常執行”或“已經正常執行一段時間”。有時，反覆的效能投訴會使資料庫管理員尋找這一問題產生的原因以提高其效能。

　　

　　圖一

　　在這兩種情況中，傳統的研究方法涉及到開放若干工具，如SQL Server Profile和Windows Performance Monitor，再嘗試捕獲實時問題。具體來說，大多數資料庫管理員都會看到具有較高等待時長，大量讀取/寫入的查詢以及那些被頻繁重複執行的查詢。

　　不過在這兩種情況中，基本的資料可能會有誤導作用。例如，頻繁重複執行但是執行得很快的查詢一般不會形成阻礙。如果基本查詢能高效快速地執行，一般應該不存在什麼問題。但是如果查詢的等待類持續總是相同，如ASYNC_IO_COMPLETION，那就可能是有問題了。

　　情境二：解決鎖定問題

　　SQL伺服器鎖定通常是一個令人費解的問題。然而，使用等待類分析，要找到所需的鎖定以及鎖定的方式就容易得多。

　　大多數SQL伺服器都會經歷瞬間鎖定和阻塞狀況。只有當這些鎖定導致長時間阻塞時才出現問題。列出鎖定種類的等待類，如LCK_M_SCH_M，能準確識別程式等待的東西(圖二)。圖二示例中，一個等待鎖定的程式需要修改表或檢視的框架，因此該程式需要等待前面的插入，更新或刪除資料的程式才能完成。

　　另一個潛在的問題是單獨阻塞程式的自然擴充套件：阻塞鏈。一旦一個程式處於等待資源並阻斷另一個程式的狀態，另一個程式就很有可能會結束等待。解決的方案是找到阻塞鏈的源頭。一旦等待類的源頭找到並解決，其他程式就會不再受影響。

　　

　　圖二

　　情境三：找到硬體瓶頸

　　識別硬體資源的瓶頸可能是最複雜的情況。雖然會有一些指示瓶頸的症狀，但是卻似乎沒有等待類以外的其他方法可用來確定硬體問題。

　　在本例中，關鍵是要找到與磁碟子系統(PAGELATCHIO_*)，CPU(CXPACKET)或通用儲存系統(RESOURCE_*)相關的等待類。當等待超過幾秒後，這些等待類就會指示硬體故障。

　　例如，我們假設由一個經常執行20分鐘左右的查詢，且該查詢使用三個表格來確定第四個表格的更新。程式設計師已經提供反饋顯示該查詢已經隨機啟動超過四小時;查詢執行的快慢速度之間沒有可辨認模式。一個資料庫管理員能夠確定這樣的查詢一般會產生怎樣的等待類，以及每個等待類的持續時間。如果等待類與硬體瓶頸相關，就該看看系統中那些在類似等待類中等待時長超出預期的其他查詢。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-600462/，如需轉載，請註明出處，否則將追究法律責任。