SQL Server效能計數器架構

標籤：des   style   blog   http   ar   io   color   使用   sp   

SQL Server效能計數器架構

1. WINDOWS效能計數器介紹

作業系統可以使用效能監控器圖形工具來測量系統、應用程式、服務、驅動程式的效能資訊。通過效能計數器可以查看SQL Server對象、效能計數器以及其他對象的行為，這些對象包括處理器、記憶體、緩衝、線程和進程。每個對象都有一個相關的計數器集合，用於測量裝置的使用方式、隊列長度、延遲情況。

為了方便批量管理，可以通過命令語句查看和添加計數器。PerfMon.exe中可以查看效能物件、效能計數器和對象執行個體，可通過添加計數器來查看相關描述資訊。TypePerf.exe可以把Windows作業系統的效能計數器資料輸出到命令視窗或寫入到支援該功能的記錄檔格式中。

1. SQL Server計數器收集、統計架構圖

可以看出

1. 生產伺服器通過ODBC訪問監控監控伺服器的SQLPerfData，將資料寫入該資料庫。
2. 通過作業計算將寫入SQLPerfData的資料寫入到SQLPerfDataStat資料庫。
   1. SQL Server計數器部署方案

我們通過ODBC直接存取到監控伺服器的資料庫，每15秒鐘將資料轉送到監控伺服器；SQL Server資料庫需要監控的效能包括：CPU、IO、記憶體、網路、SQLServer阻塞、並發、緩衝等；我們選擇了64個計數器，每個伺服器的計數器執行個體個數不同，經過篩選最終每個伺服器達到百個以上計數器執行個體；計數器的詳細屬性和用處請查看計數器屬性詳解。如此多的計數器，我們採用指令碼批量部署，具體方式可以查看文檔《SQL Server效能計數器部署_HuangCH_20141202》

1. SQL Server計數器收集匯總檢查方案

如所示，計數器架構分別儲存三個資料庫SQLPerfData、SQLPerfDataStat、SQLPerfDataStat_Histroy；這三個計數器的功能具體如下：

1. SQLPerfData接收計數器資訊

DisplayToID：計數器自動產生，記錄計數器啟動資訊

CounterDetails：計數器自動產生，記錄計數器的基本資料

CounterData：記錄計數器傳入的資料

?

?

1. SQLPerfDataStat統計計數器資訊

基本資料模組：

CounterDetails_Collect：用於同步 CounterDetails表資料，用於輪訓統計

CounterDetails_Collect_bak：無用計數器備份

CounterDetails_Dts：統計傳輸控製表，以伺服器為單位，記錄伺服器統計傳輸時間。

CounterDetails_Report：同步CounterDetails_Collect表資訊，通過最佳化，用於報表顯示作用。

?

資料模組：

CounterData_FifteenSeconds：15秒資料收集，用於同步CounterData表資料。

CounterData_OneMinute：一分鐘資料收集，15秒資料均值計算而成；適合查看當天資料。保留7天資料。

CounterData_FiveMinute：五分鐘資料收集，一分鐘資料均值計算而成；適合查看4天內資料。保留7天資料。

CounterData_HalfHour：30分鐘資料收集，一分鐘資料均值計算而成；適合查看一周內資料。保留7天資料。

CounterData_OneHour：一小時資料收集，一分鐘資料均值計算而成；適合查看一個月內資料。保留永久

CounterData_SixHour：六小時資料收集，一分鐘資料均值計算而成；適合查看一年內資料。保留永久

?

基準模組：

CBaseCounterData_OneMinute：同比基準，可以計算未來一周的資料走勢。保留7天資料。

RBaseCounterData_OneMinute：環比基準，可以計算未來一天的資料走勢。保留7天資料。

?

手動維護模組：

CounterTypeDetails：計數器類型和描述資訊記錄表。

MonitorContorl：手動維護，計數器警示監控控製表。

3、SQLPerfDataStat_Histroy歸檔計數器資訊

CounterData_OneMinute_bak：一分鐘資料備份，保留永久。

CounterData_FiveMinute_bak：五分鐘資料備份，保留永久。

CounterData_HalfHour_bak：30分鐘資料備份，保留永久。

基準模組：

CBaseCounterData_OneMinute_bak：同比基準備份，保留7天。

RBaseCounterData_OneMinute_bak：環比基準備份，保留7天。

1. SQL Server基準演算法

SQL Server計數器演算法採用同比和環比兩種方式。

同比：可以計算未來一周的基準資料，取近八周同一天同一時刻的均值。同比可以消除由於業務變動帶來的波動，具有警示對比價值。紅線同比資料，表示曆史上該時段存在異常升高。

環比：可以計算未來一天的基準資料，取近兩周每天同一時刻的均值。環比資料可以說明資料近期走勢。具有最佳化對比價值。從上同比圖可以看出117-27-139-238伺服器cpu在曆史上存在異常升高，紅線環比比資料，卻與藍線同步，說明此異常在近期已經正常。

?

1. SQL Server效能警示監控

效能目前監控cpu、磁碟空間，使用者並發三者的計數器數值情況，如示，具體請查看文檔：

監控對象	計數器名稱	監控頻率	對比資料
CPU	% Processor Time	1分鐘	同比
Disk	% Free Space；Free Megabytes	5分鐘	閥值
User Connections	User Connections	5分鐘	環比

?

1. 計數器屬性詳解

常規計數器

收集作業系統伺服器的伺服器效能資訊，包括Processor、磁碟、網路、記憶體

1. Processor 處理器
   1. % Processor Time指處理器用來執行非閑置線程時間的百分比。通過此計數器可以確定伺服器當前CPU使用百分比。
   2. % Privileged Time是在特權模式下進程線程執行代碼所花時間的百分比。當調用 Windows 系統服務時，此服務經常在特權模式運行，以便擷取對系統專有資料的訪問。
2. PhysicalDisk物理磁碟（某個RAID）
   1. Avg. Disk Queue Length等待請求的隊列長度，若此值過大，說明磁碟存在瓶頸，自然磁碟延遲的值也會過大。
   2. Avg. Disk sec/Read；Avg. Disk sec/Transfer；Avg. Disk sec/Write這三個計數器表示磁碟每次讀寫需要多少時間，表示磁碟的延遲情況，以秒為單位，若延遲超過25mm表示磁碟效能較差。
3. LogicalDisk邏輯磁碟（某個卷）
   1. % Free Space某個磁碟的空閑百分比；Free Megabytes 磁碟剩餘大小MB
   2. Disk Read Bytes/sec；Disk Transfers/sec；Disk Write Bytes/sec這三個計數器表示每秒磁碟的讀寫情況，表示磁碟的IO效能（iops）
4. Memory 記憶體
   1. Available Mbytes可以記憶體大小MB，此記憶體能立刻分配給一個進程或系統使用。它等於分配給待機(緩衝的)、空閑和零分頁列表記憶體的總和。
   2. Cache Bytes 系統使用的記憶體
   3. Commit Limit可以申請的記憶體總大小（虛擬記憶體和實體記憶體）；Committed Bytes已經使用的記憶體大小（虛擬記憶體和實體記憶體）；均是以Byte（位元組）為單位。
   4. Free System Page Table Entries指系統當前未使用的頁表項數目。表示記憶體是否空閑。
   5. Page Faults/sec 每秒出錯頁面的數量，也就是未在記憶體中命中的頁面數。因此伴隨著Pages/sec計數器是由於出錯頁面從磁碟讀取資料頁的速度。此值越大說明記憶體可能存在一定瓶頸，或者存在大量資料存取。
5. Network Interface 網路介面
   1. Current Bandwidth指以位/每秒估計的網路介面的當前頻寬。對於那些頻寬不變的或無法估計出準確資料的介面，這個值為名義頻寬。
   2. Bytes Received/sec；Bytes Sent/sec；Bytes Total/sec每秒種發送和接收的位元組數，表示當前網路的使用方式。
   3. Output Queue Length輸出資料包隊列，此值大於2說明傳輸存在延遲。
   4. Packets Outbound Discarded 被選為丟棄的資料包數目，丟棄的原因可能是釋放緩衝空間。Packets Outbound Errors 由於錯誤不能傳輸的資料包數目
   5. Packets Received Discarded 被選為丟棄的資料包數目，丟棄的原因可能是釋放緩衝空間。Packets Received Errors 由於錯誤不能傳輸的資料包數目

SQL Server計數器

SQL Server中的Access Methods對象提供監視訪問資料庫中的邏輯資料的計數器；Buffer Manager計數器監視對磁碟上的資料庫也的物理訪問。

1. Access Methods 存取方式

Access Methods監視用於訪問資料庫中儲存的資料的方法有助於確定是否可以通過添加或修改索引、添加或移動分區、添加檔案或檔案組、對索引進行磁碟重組或者重寫查詢來提高查詢效能。?Access Methods?計數器也可用於監視資料庫中資料、索引和可用空間的數量，從而指示每個伺服器執行個體的資料卷和片段。索引的片段過多會降低效能。

1. Page Splits/sec每秒由於索引頁溢出而發生的頁面分割數。此資料越大表示某表的修改頻率較高，且要確認該索引是否適合建立該欄位上。
2. Workfiles Created/sec每秒建立的工作檔案數
3. Worktables Created/sec每秒建立的工作表數

1. Buffer Manager 緩衝管理

   監視用於儲存資料頁的記憶體；讀取和寫入資料庫頁時的物理io等，通過此對象可以判斷是否存在實體記憶體瓶頸可以瞭解伺服器的查詢效能等等。

   1. Buffer cache hit ratio命中高速緩衝區頁的百分比。若此值過低說明可能記憶體存在一定瓶頸
   2. Database pages緩衝池中資料庫內容的頁數
   3. Page life expectancy頁指示頁面在沒有引用的情況下，在此節點的緩衝池中停留的時間（以秒計）。此值若降低說明，伺服器頻繁查詢資料庫中資料。
   4. Checkpoint pages/sec Checkpoint的執行頻率，若執行的頻率過高。則說明伺服器過於頻繁的修改資料。
   5. Lazy writes/sec 若懶寫入過於頻繁，說明沒有閒置緩衝區供使用，記憶體可能存在一定瓶頸。"惰性編寫器"是一個系統進程，用於成批重新整理髒的到期的緩衝區（包含更改的緩衝區，必須將這些更改寫回磁碟，才能將緩衝區重用於其他頁），並使它們可用於使用者進程。
2. Memory Manager 記憶體管理?

   監視總體的伺服器記憶體使用量情況，以估計使用者活動和資源使用，有助於查明效能瓶頸。

   1. Memory Grants Pending指定等待工作空間記憶體授權的進程總數。指等待分配記憶體的進程數量，若此值過高或記憶體或磁碟存在一定瓶頸。
   2. Target Server Memory (KB)；Total Server Memory (KB)理想上能使用記憶體量與記憶體管理器提交的記憶體量。若此兩值存在差異則說明記憶體異常。
3. General Statistics 常規統計
   1. User Connections；Logins/sec；Logouts/sec 使用者並發情況
   2. Processes blocked當前阻塞數量
   3. Temp Tables For Destruction等待被清除系統線程破壞的暫存資料表/表變數數。無用的暫存資料表和表變數。
4. SQL Statistics; Wait Statistics 請求與等待統計
   1. Batch Requests/sec；SQL Compilations/sec；SQL Re-Compilations/sec每秒的請求、編譯和重編譯的次數，這些對於最佳化供參考。
   2. Lock waits等待鎖的進程數。
   3. Log write waits等待寫入日誌緩衝的進程數
   4. Page IO latch waits與頁 I/O 閂鎖相關的統計資訊。用於判斷記憶體或磁碟的瓶頸情況
   5. Page latch waits與頁閂鎖（不包括 I/O 閂鎖）相關的統計資訊。用於判斷記憶體或磁碟的瓶頸情況
5. Databases 針對資料庫層級
   1. Log Flush Wait Time；Log Flushes/sec日誌重新整理等待和頻率，若資料異常則可能存在磁碟瓶頸
   2. Transactions/sec 事務數
6. Locks 鎖
   1. Lock Requests/sec；Lock Timeouts/sec鎖請求頻率；鎖逾時頻率
   2. Number of Deadlocks/sec 死結情況。

SQL Server效能計數器架構