一. 工具

Microsoft SQL Server 2008R2 提供了一些工具來監控數據庫。方法之一是動態管理視圖。動態管理視圖 (DMV)返回的服務器狀態信息可用於監控服務器實例的運行狀況、診斷問題和優化性能。

常規服務器動態管理對象包括：

dm_db_*：數據庫和數據庫對象

dm_exec_*：執行用戶代碼和關聯的連接

dm_os_*：內存、鎖定和時間安排

dm_tran_*：事務和隔離

dm_io_*：網絡和磁盤的輸入/輸出

二. Metrics

用戶控製台所看到的metrics

磁盤空間

命令： 總空間 = 數據空間 + 日誌空間 + 其它文件空間（temp）

解釋： 頁麵顯示了磁盤空間的使用量單位是GB

IOPS

命令： iopsflt.exe（自主開發）

解釋： IOPS (Input/Output Operations Per Second)，即每秒進行讀寫（I/O）操作的次數

CPU使用率

命令：利用進程pid獲得此進程的cpu使用率，再用獲得的cpu使用率比上用戶申請的得到一個百分比

解釋： IOPS (Input/Output Operations Per Second)，即每秒進行讀寫（I/O）操作的次數

連接數

命令
select count(*) from sys.dm_exec_sessions
where is_user_process=1

and login_name not in (‘***’)

and login_name not like ‘***’
and status not in (‘Preconnect’, ‘Dormant‘）

解釋
sys.dm_exec_sessions      是服務器範圍的視圖，顯示了有關所有活動用戶連接和內部任務的信息。

login_name 當前執行的會話所使用的 SQL Server 登錄名。

is_user_process   用於判斷是否是係統連接。

status    會話的狀態。 可能的值：

Running - 當前正在運行一個或多個請求

Sleeping - 當前沒有運行任何請求

Dormant – 會話因連接池而被重置，並且現在處於登錄前狀態。

Preconnect - 會話在資源調控器分類器中。

分類資源調控器支持對傳入會話的分類。分類基於函數中包含的一組用戶編寫的條件。

函數邏輯的結果使資源調控器可以將會話歸入現有工作負荷組類。

會話傳入鏈接——分類（分類器函數）——路由到工作負荷組——工作負荷組使用相關聯的資源池——資源池提供同時限製應用程序資源

網絡流量

命令
SELECT round(SUM(net_packet_size * 1.0 * num_reads / 1024), 0) AS read_kb, round(SUM(net_packet_size * 1.0 * num_writes / 1024), 0) AS write_kb
FROM sys.dm_exec_connections
WHERE session_id IN (

SELECT session_id

FROM sys.dm_exec_sessions

WHERE is_user_process = 1

)

理解
dm_exec_connections     返回與 SQL Server 實例建立的連接有關的信息以及每個連接的詳細信息。

net_packet_size  用於信息和數據的網絡包的大小。可為 Null 值。

num_reads   此連接中已發生的讀包次數。可為 Null 值。

num_writes  此連接中已發生的寫數據包次數。可為 Null 值。

session_id    標識與此連接關聯的會話。可為 Null 值。

is_user_process   用於判斷是否是係統連接。

TPS

命令
SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE ‘MSSQL$MS3001:database%%’
AND instance_name = ‘_Total’
AND counter_name LIKE ‘Transactions/sec%%’

理解
Database 對象提供了計數器，來監視大容量複製操作、備份和還原吞吐量以及事務日誌活動。

_Total     有的計數器的instance_name中有值為_Total的實例，它是對該計數器所有實例的聚合值(SUM)。

Transactions/sec       每秒為數據庫啟動的事務數

QPS

命令
SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name = ‘MSSQL$MS3001:SQL Statistics’
AND counter_name = ‘Batch Requests/sec’

理解
SQL Statistics      對象提供計數器來監視編譯和發送到 SQL Server 實例的請求類型。通過監視查詢編譯和重新編譯的次數以及 SQL Server 實例收到的批數，可了解 SQL Server 處理用戶查詢的速度，以及查詢 優化器處理查詢的效率。

Batch Requests/sec  每秒收到的 Transact-SQL 命令批數。

 

緩存命中率

命令
SELECT a.cntr_value * 1.0 / b.cntr_value * 100.0 AS BufferCacheHitRatio
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value, OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name = ‘Buffer cache hit ratio base’
AND OBJECT_NAME = ‘MSSQL$MS3001:Buffer Manager’
) b ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = ‘Buffer cache hit ratio’
AND a.OBJECT_NAME = ‘MSSQL$MS3001:Buffer Manager’

理解
sys.dm_os_performance_counters       為服務器維護的每個性能計數器返回一行。

counter_name    計數器的名稱

OBJECT_NAME          計數器的所屬類別

Buffer Manager 對象提供了計數器，用於監視 SQL Server 如何使用：內存存儲數據頁、內部數據結構和過程緩存； 計數器監視 SQL Server 讀取和寫入數據庫頁時的物理 I/O。

Buffer Manager中的Buffer cache hit ratio base和Buffer cache hit ratio

Buffer cache hit ratio 官方解釋在緩衝區高速緩存中找到而不需要從磁盤中讀取的頁的百分比。

平均每秒全表掃描數

命令
SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name = ‘MSSQL$MS3001:Access Methods’
AND counter_name = ‘Full Scans/sec’

理解
Access Methods       對象提供用於監視如何訪問數據庫中的邏輯數據的計數器。

Full Scans/sec    每秒不受限製的完全掃描數。這些掃描可以是基表掃描，也可以是全文索引掃描。

每秒SQL編譯

命令
SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name = ‘MSSQL$MS3001:SQL Statistics’
AND counter_name = ‘SQL Compilations/sec’

理解
每秒的 SQL 編譯數。 表示編譯代碼路徑被進入的次數。 包括 SQL Server 中語句級重新編譯導致的編譯。 當 SQL Server 用戶活動穩定後，該值將達到穩定狀態。

每秒檢查點寫入Page數

命令
SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name = ‘MSSQL$MS3001:Buffer Manager’
AND counter_name = ‘Checkpoint pages/sec’

理解

由要求刷新所有髒頁的檢查點或其他操作每秒刷新到磁盤的頁數。

每秒登陸次數

命令
SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name = ‘MSSQL$MS3001:General Statistics’
AND counter_name = ‘Logins/sec’

理解
General Statistics 對象提供計數器，用於監視服務器範圍內的常規活動

Logins/sec   每秒啟動的登錄數。 這不包括已入池連接。

每秒鎖超時

命令
SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name = ‘MSSQL$MS3001:Locks’
AND counter_name = ‘Lock Timeouts/sec’
AND instance_name = ‘_Total’

理解
Locks 對象提供了有關各種資源類型的 SQL Server 鎖的信息

Lock Timeouts/sec    每秒超時的鎖請求數，包括對 NOWAIT 鎖的請求。

每秒死鎖

命令
SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name = ‘MSSQL$MS3001:Locks’
AND counter_name = ‘Number of Deadlocks/sec’
AND instance_name = ‘_Total’

理解
Number of Deadlocks/sec     每秒導致死鎖的鎖請求數。

每秒鎖等待

命令
SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name = ‘MSSQL$MS3001:Locks’
AND counter_name = ‘Lock Waits/sec’
AND instance_name = ‘_Total’

解釋
Lock Waits/sec   每秒要求調用者等待的鎖請求數。