0

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%'
5
  • Do you have the View Server State permission on instance 1? Jun 13, 2023 at 10:01
  • @AlwaysLearning the user is a syadmin, so it should have that permission
    – tutu
    Jun 13, 2023 at 10:06
  • From the docs "look at the setup log and search for error 3409, Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions. This denotes that performance counters were not enabled." Jun 13, 2023 at 10:13
  • @Charlieface that's only when the counters are disabled, which can be tested when this query is run and zero rows are returned: SELECT COUNT(*) FROM sys.dm_os_performance_counters;. In my case, rows are returned.
    – tutu
    Jun 13, 2023 at 10:19
  • My guess is that it is because your 2016 instance is Standard Edition, which "doesn't have" resource governor. It is there, but but usable for you, which is why I believe that you don't see the default instances/groups/pools. Perhaps this can be verified by others here, running your query on Standard Edition (I only have Developer to test on, which is functionally the same as Enterprise). Jun 13, 2023 at 16:32

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.