The SQL Advanced Metrics service monitor is unable to pull counters from WMI...
This can occur from time to time with any product that publishes performance counters to WMI. For SQL specifically, we will usually need to reload counters. Sometimes, the WMI service just loses the counters and a simple restart of WMI and the dependent services can fix it. Below is a script that will shut down the appropriate services, including SQL server, so BE WARNED this can be very BAD to run on a production SQL server during regular hours so maintenance should be scheduled. You MAY try running the rest of the script without the MSSQL server shutdown and this may fix the issue. The problem can stem from multiple sources (hard to track down) so this script is a "cure all" for the issue. The script contents is below for those wanting to execute the steps manually.
NOTE: the directory depends on your version of MSSQL server, the one shown is for a SQL 2014 instance.
Open an administrative command prompt and execute:
cd c:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn
"Performance Logs & Alerts"
"User Access Logging Service"
"Windows Management Instrumentation"
net stop MSSQLSERVER
net start MSSQLSERVER
After executing, simply test the SQL Advanced Metrics service monitor again. Metrics should now be restored.
Powered by IDERA