10.監視SQL Server效能

標籤：資訊   包括   分布式應用   一點   視圖   搜集   圖形化介面   操作   ota   

資料庫管理員的主要責任之一是持續監視SQL Server效能。之所以要進行監視，原因 有多種，包括效能、儲存狀態、安全性和標準符合程度等。雖然很多此類監視可以自動完
成，但在大多數情況下，資料庫管理員必須以系統的方法說明監視的結果並對其採取行動。
監視工作需要持續進行，它可能變得非常複雜。知道監視什麼、什麼時候監視以及什麼是
可接受的和不可接受的行為，這些都可能成為一項全職性工作的內容。使事情變得更困難
的是，每個SQL Server安裝都是不同的，所以很難給出一個關於可接受和不可接受效能的 指標的通用性建議。
本章介紹了各種用來監視SQL Server的工具，並就如何使用這些工具標識潛在安全問 題和可最佳化部分提供指導。監視SQL Server是一個充滿挑戰的過程。SQL Server與每個操 作系統子系統都有大量互動。一些應用程式非常依賴RA M ,而另一些則是CPU密集或磁
盤密集型。SQL Server可能同時符合這三種情況。SQL Server也可能是網路密集型的，尤 其是分布式應用程式、複製或資料庫鏡像這些部分。許多資料庫管理員覺得整個監視和優
化的過程是神秘而模糊不清的。不過，它並沒有那麼神秘。很好地理解工具並熟悉要監視
的不同對象，可以使監視任務變得不那麼令人生畏。
許多書都討論監視的主題，還有一些網站專門討論它。本書不會介紹有關監視SQL
Server的一切內容，但是會解釋-些基本知識，這是最好的起始點。
1 0 .1 效能監控
SQL Server 2008監視本質上可以分為5 個基本地區：
? 系統資源
? SQL Server 自身
? 資料庫
? 資料庫應用程式
? 網路
在開始探討效能監控的具體方面之前，瞭解監視方法是很重要的。為了監視而監視是沒
有意義的。監測硬體和SQL Server實現方案是為了預測和預防效能問題。為此，必須有某種 計劃，即一種可使您投入適當的時間量和資源量來維護和改善SQL Server的效能的策略。

10.1.1 效能監控策略
監視和最佳化SQL Server的策略是相當簡單的，由以下幾步組成： (1) 建立一個效能基準一 如果資料庫伺服器沒有一個基準，那麼在改動伺服器平台
時，很難確信更改會達到期望的效能改進。基準包含之前提到過的所有系統(系統資源、SQL
Server、資料庫、資料庫應用程式和網路)的度量。具體的計數器和度量將在本章稍後論述。 當評估基準時，可以確定保證即時最佳化的地區。如果做了更改，則必須建立一個新的基準。
(2) 完成定期效能審核一 在建立基準之後，執行定期效能審核確保在基準建立之後
效能沒有降低。這一步通常會由被動的審核補充或取代，執行被動審核的目的是為了回應
對伺服器效能低下的抱怨。我傾向於主動安排這些審核，但有時還是會因為發生不可預料
的效能問題而進行被動審核。
(3) 做出改動並評估它們的影響一 在執行審核之後，可能會發現需要修改的地區。
在做出這些改動時，需要相當小心。一般來說，不應該一次進行多個改動，而是應該先執
行一兩個改動，然後評估提示您變更的度量。這樣更加容易找到哪些更改對於效能的
影響最大。第 11章將更詳細地介紹在效能審核標識出問題地區時，可以執行的用於最佳化
SQL Server的具體更改。
(4) 重設基準—— 完成所有修改之後，可以建立另一個基準來度量未來效能趨勢。
Mad Clicker
我曾經與一位被眾人親切地稱為“Mad Clicker(瘋狂敲擊者)”的同事共事.當伺服器 房間裡出現問題時，他總是會投入其中並開始不停敲擊鍵盤，對配置設定做徹底的更改以
期能夠解決問題。他通常都會成功，但以後想要重複他的操作幾乎是不可能的，因為甚至
他自己都不清楚自己改動的每一個內容。因此，不要成為一位“Mad Clicker”。每完成一 項改動之後，要度量並歸檔結果。這樣重複操作就很簡單，而且如果這個改動導致了效能
降低而不是提升，還可以輕鬆地復原。
1 0 .1 .2 建立一個效能基準
在建立基準時，監視典型活動是很重要的。在每月一次的匯入中監視效能可能會帶來
一些有趣的資料，但是它對評估和提髙整體的系統效能沒什麼協助。建立基準的方式有多
種。大多數資料庫管理員對於搜集和比較效能資料有自己的偏好。他們還有自己喜歡的計
數器和系統檢視表，認為這些計數器和系統檢視表可以讓他們更好地瞭解資料庫的效能。其實
SQL Server效能監控和最佳化與其說是一種科學，不如說是一種藝術。 關於搜集哪些“系統監視器”計數器和監視哪些SQL Server所特定的活動，我曾經看 到過很多種建議。所有建議都是各不相同的。有些資料庫管理員建議監視所有內容，而另
一些則建議有選擇地監視一小部分進程。我支援後者，原因有兩點。第一個原因是“資訊太
多”這樣的情況肯定會出現。如果收集了所有的效能資料，那麼很可能會導致“只見樹木，
不見森林”，因為有太多的資料需要詳細審査。第二個(可能也是更重要的)原因是效能因素。
搜集效能資訊不是沒有代價的。搜集得越多，效能損耗就越大。這就出現了一個有趣
的悖論。要想充分地監視效能，必須把效能降級操作引入到資料庫中。這所導致的窘境就

是，您無法肯定自己的監視行為與不可接受的效能之間一點關係都沒有。
限制檢索的資料可以減少這一不確定性，但是要記住，不應當孤立地看待任何一個特
定的計數器。例如，繁重的磁碟活動可能是由記憶體限制引起的，而 CPU效能低下可能是因
為編寫的查詢較差或索引丟失造成的。任何子系統都不是處於真空中的。
那麼，基準中應包含什麼呢？根據多年的經驗，我總結出了一個為基準和效能審核而
監視的對象和進程的列表。下面將介紹這些計數器。
建立效能基準的主要工具是效能監控器。不過，我們也使用動態管理檢視(Dynamic Management View, DMV)來給基準提供更多的上下文。在解釋完用於基準和效能審核的計 數器之後，本章會深入介紹SQL Server特有的工具，並探討如何識別不正常的進程。
1 .效能計數器
下面的一些計數器在進行效能審核時都很有用。這裡的討論並不包括所有的計數器，
所涉及到的都是我和一些同事所依賴的從宏觀上瞭解SQL Server效能的一些計數器。除此 以外，還有很多計數器可以用來診斷效能問題和深入研究SQL Server活動。但是這裡介紹 的計數器更有可能提供快速評估伺服器狀態所需的資訊。
處理器計數器
處理器計數器(Processor Counter)和其他計數器一起使用，用來監視和評估CPU效能並 辨別CPU瓶頸。
? Processor： % Processor Time-----Processor： % Processor Time 計數器顯示了處
理非閑置線程所用時間的總百分比。在一個多處理器的機器上，可以獨立監視每
一個單獨的處理器。如果定製了 CPU關聯設定，那麼您可能想要監視一個特定的
CPU。除了這種情況之外，我一般使用_total執行個體標識符來査看組合的處理器使用。 CPU活動是SQL Server CPU活動的一個很好的指標，也是一個識別潛在的CPU 瓶頸的關鍵方法。對於這種計數器應該是怎麼樣的，不同的人有不同的建議。一
般來說，如果總的％ Processor Time —直大於70%，那麼可能就出現了一個 CPU 瓶頸，此時應當考慮最佳化當前應用程式進程，或升級CPU ,或兩者都做。應當把
這個計數器和Processor Queue Length計數器一起使用來確定CPU瓶頸。

? Process處理： % Processor Time 處理器時間(sqlservr)----- 當設定為監視來自於 SQL Server 進程
的資訊時，Process： % Processor Time計數器可以用來確定總處理時間中有多少由
SQL Server 佔用。

? System: Processor Queue Length-----Processor Queue Length 計數器顯示等待由
CPU處理的線程的數量。如果平均隊列長度一直大於處理器數量的兩倍，那麼可
能出現一個CPU瓶頸，因為處理器來不及處理請求。
可以同時使用Processor Queue Length和％ Processor Time計數器來確定是否存在CPU
瓶頸。如果兩者都處於可接受的範圍之外，那麼一定存在CPU瓶頸。

如 果 Processor Queue Length不在可接受的範圍之內，但％ Processor Time在可接受範
圍之內，那麼可能沒有出現CPU瓶頸，而只是存在配置問題。需要確保對於系統來說，沒
有將最大背景工作執行緒數(max worker threads)伺服器設定的值設得過髙。最大背景工作執行緒數的默

認設定是0 ,這會使SQL Server自動將最大背景工作執行緒數設定為和表10-1中的值相一致。不 過，除了 0 之外，還可以配置128? 32 767之間的任意值。SQL Server聯機從書給出的可 接受範圍是32? 32 767,這是錯誤的。圖形化介面會接受0? 32 767之間的任意值，但 1?
127之間的任何值都會被設定為128。

 

10.監視SQL Server效能