了解視圖dm_os_performance_counters的cntr_type含義

dm_os_performance_counters說明

該視圖用于檢視資料庫的性能名額，但是不同的名額類型(cntr_type)計算方法有所不同。

大概有以下不同類型：

select object_name,counter_name,instance_name,cntr_value,
case cntr_type 
  when 65792 then '所見即所得，無需計算' 
  when 65536 then '所見即所得，無需計算' 
  when 272696576 then '累計值'
  when 1073874176 then '需要除以同名 Base對應的值'
  when 537003264 then '需要除以同名 Base對應的值' 
end as counter_comments
from sys.dm_os_performance_counters
where cntr_type not in (1073939712);      

1、PERF_COUNTER_LARGE_RAWCOUNT：65792：

PERF_COUNTER_LARGE_RAWCOUNT 計數器類型的 cntr_types 列值為 65792。這些計數器顯示最後觀察到的值，而不是平均值。它通常用于監視對象計數 這意味着，如果計數器類型為 65792，那麼在查詢視圖時在 counter_value 列中得到的值就是該計數器的目前值，不需要額外計算。也就是"所見即所得"。

如：檢視Page life expectancy

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page Life expectancy'
      AND object_name LIKE '%buffer manager%';      

該類型的計數器類型有：*General Statistics User connections, Buffer Manager Page life expectancy and Database pages, Databases – Data and Log file size (KB), Log file used size (KB), Percent Log used, Memory Manager – Free Memory (KB),*等等。

檢視SQL:

select distinct object_name,counter_name from sys.dm_os_performance_counters where cntr_type=65792      

2、PERF_LARGE_RAW_BASE：1073939712

PERF_LARGE_RAW_BASE 計數器類型的 cntr_types 列值為 1073939712。這些計數器收集最後觀察到的值，該計數器值用作進一步計算的​

​分母​

​。此類型的計數器僅用于計算通過視圖可用的其他計數器，屬于此計數器類型的所有計數器的名稱中都包含單詞 base，是以清楚地表明這不是提供有用資訊的計數器，它隻是進一步計算的基值。

通常用于計算命中率相關資訊，如：

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'      

Buffer Cache Hit Ratio % = 100 * Buffer Cache Hit Ratio /Buffer Cache Hit Ratio Base

= 100 * 2,135 / 3,573

= 59.75%

該類型的計數器類型有：Buffer Cache Hit Ratio Base, Log Cache Hit Ratio Base, Average Latch Wait Time Base, Cache Hit Ratio Base, CPU usage % base, and more

檢視SQL:

select distinct object_name,counter_name from sys.dm_os_performance_counters where cntr_type=1073939712      

3、PERF_AVERAGE_BULK ：1073874176

PERF_AVERAGE_BULK 計數器類型的 cntr_types 列值為 1073874176。 cntr_value 列值是累積的。要計算計數器的目前值，必須監視 PERF_AVERAGE_BULK 及其對應的 PERF_LARGE_RAW_BASE 計數器(1073939712)，同時取兩個樣本，并使用這些值進行計算。

如：計算平均等待時間

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%Average Wait Time%'
      AND instance_name = 'database'      

時間點 T1：

時間點T2:

即：Average Wait Time (ms) = (53736 ms -52939 ms)/(23-18) = 797 ms / 5 = 159.4 ms

該類型的計數器類型有：*Average Wait Time (ms), Average Latch Wait Time (ms), Update conflict ratio, Avg. Length of Batched Writes, Avg. Time to Write Batch (ms), Avg. Time Between Batches (ms)*等等。

4、PERF_LARGE_RAW_FRACTION ：537003264

PERF_LARGE_RAW_FRACTION 計數器類型的 cntr_types 列值為 537003264。這些計數器用來計算命中率，即兩個值之間的分數 - PERF_LARGE_RAW_FRACTION 計數器及其對應的 PERF_LARGE_RAW_BASE 計數器值的比值。

如：檢視緩存命中率

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name like 'Buffer cache hit ratio%'      

Buffer Cache Hit Ratio % = 100 * Buffer Cache Hit Ratio /Buffer Cache Hit Ratio Base = 100* 564/564 = 100.

該類型的計數器類型有：Buffer Cache Hit Ratio, Log Cache Hit Ratio, Worktables From Cache Ratio, Cache Hit Ratio, CPU usage %, and Rem Req Cache Hit Ratio等。

5、PERF_COUNTER_BULK_COUNT：272696576

PERF_COUNTER_BULK_COUNT 計數器類型的 cntr_types 列值為 272696576。這些計數器顯示的值是累積的，它是自上次 SQL Server 執行個體重新啟動以來的累積值，是以需要對其實際值進行采樣，與 PERF_AVERAGE_BULK 計數器類型相同。

但是，了解采樣周期有多長很重要。否則，将無法計算每秒的值。通常使用 5 分鐘的時間段，要計算每秒速率，請計算兩個樣本值之間的差異，然後将其除以樣本之間的秒數。

如：計算Page lookups/秒

DECLARE @PageLookups1 BIGINT;
 
SELECT @PageLookups1 = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page lookups/sec';
 
WAITFOR DELAY '00:00:10';
 
SELECT (cntr_value - @PageLookups1) / 10 AS 'Page lookups/sec'
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page lookups/sec';