Workload Group Stats counters and SQL Server Edition

Problem Statement

During one of our performance evaluation, we wanted to assess the resource utilization for one of servers using the Perfmon Counters.

If you are not familiar with perfmon counters, please have a look at the below link for the improtance and significance of perfmon counter in performance evaluation.

https://technet.microsoft.com/en-us/magazine/2008.08.pulse.aspx

In our assessment, in addition to the usual counters, we wanted to collect Workload information from SQL Server.Hence, we enabled the below counters:


SQLServer:Workload Group Stats/ CPU Usage %
SQLServer:Resource Pool Stats/ CPU Usage %

After configuring the above counters in the perfmon, it is observed that the above counters are not collecting the information from the server. However, when we configured it locally, it was collecting the counter values.

These counter values can also be queried in SSMS using the below queries:


Select * From [master].[sys].[dm_os_performance_counters]
Where 
(object_name ='SQLServer:Workload Group Stats' and counter_name ='CPU Usage %') OR
(object_name ='SQLServer:Resource Pool Stat' and counter_name ='CPU Usage %')

Cause/Reason:

On further analysis, we identified that SQLServer:Workload Group Stats object contains performance counters that report information about Resource Governor workload group statistics.

As Resource Governor can only be supported for Datacenter, Enterprise, Developer and Evaluation editions, those counters can not be collected information on any other editions, eventually we have Standard edition in our environment.

Lesson Learned:

When you configure perfmon counters, always check the associated features and its supporting version.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s