視圖dm_os_performance_counters的cntr_type含義

dmosperformance_counters說明

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

大概有以下不同類型：

select object_name,counter_name,instance_name,cntr_value,
case cntr_type 
    when 65792 then 'Absolute Meaning' 
    when 65536 then 'Absolute Meaning' 
    when 272696576 then 'Per Second counter and is Cumulative in Nature'
    when 1073874176 then 'Bulk Counter. To get correct value, this value needs to be divided by Base Counter value'
    when 537003264 then 'Bulk Counter. To get correct value, this value needs to be divided by Base Counter value' 
end as counter_comments
from sys.dm_os_performance_counters
where cntr_type not in (1073939712);           

參考連接配接：https://techcommunity.microsoft.com/t5/sql-server-support-blog/interpreting-the-counter-values-from-sys-dm-os-performance/ba-p/317824

1、PERFCOUNTERLARGE_RAWCOUNT：65792：

PERFCOUNTERLARGERAWCOUNT 計數器類型的 cntrtypes 列值為 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、PERFLARGERAW_BASE：1073939712

PERFLARGERAWBASE 計數器類型的 cntrtypes 列值為 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、PERFAVERAGEBULK ：1073874176

PERFAVERAGEBULK 計數器類型的 cntrtypes 列值為 1073874176。 cntrvalue 列值是累積的。要計算計數器的目前值，必須監視 PERFAVERAGEBULK 及其對應的 PERFLARGERAW_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、PERFLARGERAW_FRACTION ：537003264

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

如：檢視緩存命中率

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、PERFCOUNTERBULK_COUNT：272696576

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

但是，了解采樣周期有多長很重要。否則，将無法計算每秒的值。通常使用 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';           

該類型的計數器類型有：Page lookups/sec, Free list stalls/sec, Lazy writes/sec, Page reads/sec, Page writes/sec, Logins/sec等等。