對SQLSERVER進行性能監控

在上一篇文章《SQLSERVER性能監控級別步驟》里說到性能監控的步驟中有一步涉及到建立性能基線，但是沒有說到有哪些計數器

可以用來進行監控的，這篇文章結合《企業級平臺管理實踐》的書本說一下監控SQLSERVER有哪些計數器可以用到的

3、建立性能基線
 
 當確定了性能監控中所涉及的資源、負載和目標后，開始進行監控，并建立性能基線與當前服務器性能進行比較。
 
性能基線是一個保證系統正常操作性能范圍值，達到或超過這個范圍，系統性能可能會顯著下降。
 
應該對接近或超過性能基線的數字做進一步調查找出原因監控的周期是一段時間，而不是一兩天。
 
其中應該包括數據庫活動的峰值時間和非峰值時間，數據查詢和批處理命令的響應時間、數據庫備份和還原所需時間
 
建立服務器性能基線后，將基線統計與當前服務器性能進行比較。對高于或遠低于基線的數字需要做進一步調查。
 
他們可能表明有需要調整或重新配置的區域。例如，執行一組查詢的時間增加，檢查這些查詢以確定能否重新編寫他們，
 
或者是否添加統計信息或索引

介紹：

性能監視器 Performance Monitor

性能監視器是Windows的一個工具，在系統管理工具組里。默認里面就有很多Windows層面的性能計數器，可以監視系統的運行。

直接運行"perfmon"，也可以打開他。這里以 WindowsXP/2003/2008的性能監視器為例。

Windows2008R2和Windows7的性能監視器界面有了比較大的變化，功能也有擴展，更加好用。同時也完全向前兼容。

后面談到的功能都有包括

SQLSERVER自己開發了一些擴展的性能計數器。在安裝SQLSERVER的時候，會注冊到Windows里。

這樣， Windows的性能監視器就能看到一些以“SQL”打頭的計數器了。SQLSERVER在運行時，會統計這些計數器的值。

在性能監視器里能夠看到：

默認性能監視器是用來實時檢測系統的，在窗口里，用不同顏色的線條表示不同的計數器值。

當窗口畫滿以后，會從頭覆蓋前面的內容。所以默認只能看到最近一小段時間的值。

但是在現實的問題分析中，實時監測還是比較少的。更常見的場景是需要在問題發生之前，就要開啟性能計數器的收集，

收集一段時間之后，或者問題重現之后，再離線地分析問題的現象和原因。

那么日志怎樣收集呢？

通常可以使用下面這些步驟：

（1）在性能監視器左邊的窗口，展開性能 日志和警告子樹，點擊“計數器日志” 在右邊的窗口里，右鍵點擊，

選擇“新 日志設置”，他會彈出一個對話框，讓你為新的日志記錄配置命名。這里我們取名為Test，日志默認保存路徑是

%systemdrive%\PerfLogs\Admin\Test

（2）在接著彈出的對話框里，就可以配置DBA要搜集的信息要求了。首先要選擇搜集哪些計數器，以及他們的取樣時間間隔sample data every，

默認是15秒取一次，這個間隔能夠滿足大部分需求。

有說法講在搜集和磁盤相關的性能日志時，間隔要設置短一點，最好是3到5秒。如果設置30秒以上，可能信息就不完整了。

所以15秒是大部分情況下比較好的選擇

（3）選擇添加對象，就可以選擇要收集的性能監視器對象。對于非在線分析，問題可能還不清楚，很難確定哪些性能計數器有用，哪些沒有用。

所以在這里，一定要多選一些。一般的SQL問題，可以選擇下面這些對象

在memory,process,physicaldisk,processor,system對象下的所有計數器，以及他們的所有instance

所有以SQLSERVER：開頭的性能監視對象

如果要監視CPU類問題，最好還包含thread下面的所有計數器，以及他所有的instance

有些DBA會擔心，抓這麼多計數器會不會影響性能。

應該說根據經驗，性能監視器對系統整體性能的影響幾乎感覺不到。所以可以比較放心大膽地多收一些計數器。

基本工作原理是在.NET編譯出的IL代碼里放入鉤子用來記錄時間，然后通過直觀的界面顯示出哪部分代碼耗能最大。

只是間隔可能還是選15秒比較安全

（4）設置文件的位置和最大大小 ，另一個重要配置，是日志文件存放在哪里，保存格式，以及最大大小。

日志文件的后綴是blg的二進制文件，需要使用性能監視器才能打開這個文件

如果性能日志文件大小超過1GB，可能有些機器打開會很慢。所以一定要注意其最大值可以設為200MB。

如果一個200MB的文件寫滿，性能監視器會自動創建一個新的。文件格式可以選二進制文件

日志搜集當然可以手動開始和終止。但是如果問題會發生在半夜，最好能讓系統自動開啟，自動關閉。性能監視器也可以幫DBA做到這一點

當得到一個性能日志后，可以在性能監視器里選擇 查看 日志 數據

在數據源里添加日志文件

然后點擊數據選項卡，就能看到在原來那臺服務器上收集的性能計數器了

這時候再點擊“源”選項卡，能看見性能日志文件所包含的那段時間。拉動滾動條，可以把時間段縮短到DBA最關心的那段時間

對收集到的日志，DBA可以進行分析

---------------------------------------華麗的分割線----------------------------------------------------------------------

一些性能監視器計數器
相關計數器

性能對象                                                 計數器
SQLSERVER:BUFFER MANAGER:    buffer cache hit ratio,lazy writes/sec ,procedure cache pages,total pages
SQLSERVER：Cache Manager:    cache hit ratio,cache object counts,cache pages ,cache use counts/sec
SQLSERVER:MEMORY MANAGER:    sql cache memory(kb)
SQLSERVER:SQL STATISTICS:    auto-param attmpts/sec,batch request/sec,failed auto-params/sec,safe autoparam/sec, sql compilations/sec,

sql re-compilations/sec,unsafe auto-params/sec

------------------------------------華麗的分割線--------------------------------------------------------

與內存有關的計數器

Windows與SQLSERVER系統使用內存情況和合理配置SQLSERVER內存 

性能監視器  perfmon --添加-》可用計數器-》Memory-》添加available MBytes和pages/sec

數據收集器集-》用戶定義-》新建-》數據收集器集-》名稱：SQLSERVER內存使用-》手動創建-》性能計數器-》 添加下面的性能計數器-》

時間間隔15秒-》保存路徑：C:\Users\Administrator\Desktop\SQLSERVER內存使用-》 保存并關閉-》選中剛才創建的數據收集器-》啟動-》變成

datacollector01   -》在用戶定義下面 SQLSERVER內存使用 右鍵-》停止或者在空白的地方-》右鍵-》停止

可以右鍵-》在用戶定義下面 SQLSERVER內存使用-》屬性-》更改數據收集器保存路徑

 計數器

committed bytes:整個Windows系統，包括Windows自身以及所有用戶進程使用的內存總數

commit limit:整個Windows系統能夠申請的最大內存數，其值等于物理內存加上文件緩存大小

available MBytes（重要）:現在系統空閑的物理內存數。這個指標能夠直接反映出Windows層面上有沒有內存壓力跑在Windows2000上會把空閑內存用完知道剩下4MB~10MB。跑在Windows2003或以上就會留給Windows多一點的物理內存

page file :%usage  page file:% peak usage ：反應緩存文件使用量的多少，使用越多緩存，性能越差

pages /sec:每秒鐘需要從磁盤上讀取或寫入的頁面數目

soft page fault一般不會帶來性能影響，因此一般不太關心

一個良好的系統，他要處理的數據應該比較長期地保存在物理內存里。如果頻繁換頁/換入換出勢必影響性能，pages/sec不能長時間保持在一個比較高的值

對于一臺SQL服務器，如果available MBytes長期小于10MB，說明物理內存不太夠pages/sec 物理內存不足也會做成頻繁換頁/換入換出 pages/sec不能長時間保持在一個比較高的值

Windows系統自身內存使用情況

一個32位Windows系統，正常內存使用大概幾百MB --64位Windows系統大概1GB~2GB

--如果發生內存泄漏（一般由硬件驅動造成），Windows會用到幾個GB甚至十幾GB，反過來擠壓應用的內存

memory :cache bytes --系統的working set，也就是系統使用的物理內存數目，包括高速緩存，頁交換區，可調頁的ntoskrnl.exe 和驅動程序代碼，

以及系統映射視圖

cache bytes計數器是下面幾個計數器的和：

system cache resident bytes,system driver resident bytes ,system code resident bytes ,pool paged resident bytes

system cache resident bytes:系統高速緩存消耗的物理內存。高速緩存的主要功能是提高文件讀寫的速度

pool paged resident bytes：頁交互區消耗的物理內存

system driver resident bytes：可調頁的設備驅動程序代碼消耗的物理內存

system code resident bytes：ntoskrnl.exe中可調頁代碼消耗的內存

system pool 內存池  如果兩個重要的內存池內存出現泄漏，或者空間用盡，Windows會出現奇怪不正常的行為， 進而影響SQL穩定運行。

所以需要檢查這兩個內存池

pool nonpaged bytes 非換頁內存池

pool paged resident bytes 換頁內存池

單個process使用情況

常見場景：available MBytes看出服務器的內存基本用盡，但是從cache bytes看Windows自己沒有使用多少。

現在要開始分析應用程序的內存使用了

在選擇對象的實例里面要每個進程都要添加進計數器里面，不要選擇_Total SQL的進程是sqlservr

%processor time:是目標進程消耗的CPU資源數，包括用戶態和核心態的時間

page faults/sec:是目標進程上發生的page faults的數目

handle count：目標進程handle(指向object指針)數目句柄數。如果進程內部有對象老是創建，不及時回收，就會發生handle leak

thread count:目標進程的線程數目。如果進程老是創建新線程，不釋放老線程，就會發生thread leak

pool paged bytes:是目標進程所使用的paged pool大小

pool nonpaged bytes:是目標進程所使用的non-paged pool大小

working set:某個進程的地址空間，存放在物理內存的那一部分

virtual bytes:某個進程所申請的虛擬地址空間大小，包括reserved memory 和committed memory

private bytes:某個進程提交了的地址空間commited memory中，非共享部分

假設有processA 和processB,他們的虛擬地址空間都分成兩部分，核心態和用戶態 --核心態是由Windows控制，所有進程共享。

processA --committed memory :1,2,3,4,7 --reserved memory:8 --shared memory:通過特殊API申請的內存，processA和processB都能夠訪問

物理內存physical memory：1，3，4，d，7，9，b，c 緩存文件page file：2，y

系統核心態內存 system working set=x

檢查計數器主要找到以下：

使用內存最多的進程

內存使用量在不斷增長的進程

出現問題的那個時間段，內存使用數量發生過突變（增或降）的進程

這些可以通過working set  private bytes得到初步答案

 -------------------------------------------------華麗的分割線-------------------------------------------------------

上面這些都是《SQLSERVER企業級平臺管理實踐》讀書筆記整理出來的一些常用SQLSERVER性能計數器，大家做性能基線的時候

都可以用來做參考

如有不對的地方，歡迎大家拍磚o(∩_∩)o