sys.dm_os_performance_counters showing a discrepancy in results on multiple instances

Question

I've seen posts from people saying they see no results returned from sys.dm_os_performance_counters. For me it's a bit different. See below.

Instance 1:

+-------------------------------------+-----------------------+---------------+------------+
|             object_name             |     counter_name      | instance_name | cntr_value |
+-------------------------------------+-----------------------+---------------+------------+
| MSSQL$TRAINING:Workload Group Stats | CPU usage %           | internal      |          0 |
| MSSQL$TRAINING:Workload Group Stats | CPU usage % base      | internal      |          0 |
| MSSQL$TRAINING:Resource Pool Stats  | CPU usage %           | internal      |          0 |
| MSSQL$TRAINING:Resource Pool Stats  | CPU usage % base      | internal      |          0 |
| MSSQL$TRAINING:Resource Pool Stats  | CPU usage target %    | internal      |          0 |
| MSSQL$TRAINING:Query Store          | Query Store CPU usage | _Total        |          0 |
+-------------------------------------+-----------------------+---------------+------------+

@@Version: Microsoft SQL Server 2019 (RTM-CU20) (KB5024276) - 15.0.4312.2 (X64) Apr 1 2023 12:10:46 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

Instance 2:

+--------------------------------+-----------------------+---------------+------------+
|          object_name           |     counter_name      | instance_name | cntr_value |
+--------------------------------+-----------------------+---------------+------------+
| SQLServer:Workload Group Stats | CPU usage %           | default       |       1177 |
| SQLServer:Workload Group Stats | CPU usage % base      | default       |       4044 |
| SQLServer:Workload Group Stats | CPU usage %           | internal      |         50 |
| SQLServer:Workload Group Stats | CPU usage % base      | internal      |       4044 |
| SQLServer:Resource Pool Stats  | CPU usage %           | default       |       1177 |
| SQLServer:Resource Pool Stats  | CPU usage % base      | default       |       4044 |
| SQLServer:Resource Pool Stats  | CPU usage target %    | default       |          0 |
| SQLServer:Resource Pool Stats  | CPU usage %           | internal      |         50 |
| SQLServer:Resource Pool Stats  | CPU usage % base      | internal      |       4044 |
| SQLServer:Resource Pool Stats  | CPU usage target %    | internal      |          0 |
| SQLServer:Query Store          | Query Store CPU usage | _Total        |          0 |
+--------------------------------+-----------------------+---------------+------------+

@@Version: Microsoft SQL Server 2016 (SP3-GDR) (KB5021129) - 13.0.6430.49 (X64) Jan 22 2023 17:38:22 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

How can I change it so instance 1 returns populated values like instance 2?

Query:

SELECT RTRIM(LTRIM(object_name)) AS [object_name], RTRIM(LTRIM(counter_name)) AS counter_name, RTRIM(LTRIM(instance_name)) AS instance_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name like '%CPU usage%'

Answer 1

After some testing I believe it's indeed because of the Resource Governor (@TiborKaraszi). So basically this DMV depends on the SQL Server edition you are using. Some counters will permanently remain zero if you are not running Enterprise Edition. Whether or not you can get it working 100% on the other editions, I don't know.

Using SELECT * FROM sys.dm_resource_governor_workload_groups I can see a difference in results with Express Edition compared to Developer Edition. In Express some counters remain zero forever, like total_cpu_usage_ms, while in Developer Edition this columns is populated with values that change.

Anyway, I will be using the PowerShell cmdlet Get-Counter as an alternative to log some counters to a table to build a history.