Resolving no data error for MSSQL performance metrics

Resolving no data error for MSSQL performance metrics

Problem:

If the metrics in the MS SQL monitor's 'Overview' page isn't getting populated, it might be due to any of the following reasons:

 

- MSSQL user does not have permission to access the performance counters.

- Data collected from SQL server is not being inserted into Applications Manager.

- Performance counters have not been enabled.

 

Solution:
Applications Manager uses the below query to collect data of critical performance counters of your SQL server.
Open SQL Server Management Studio and run the query based on the SQL server version that you are using.


For SQL Server 2008 (Or) above:

SELECT perf.object_name,perf.counter_name,perf.instance_name,perf.cntr_type,'value' = CASE perf.cntr_type WHEN 537003008 THEN CASE perfbase.cntr_value WHEN 0 THEN 0 ELSE CONVERT(FLOAT,perf.cntr_value) / perfbase.cntr_value END WHEN 537003264 THEN CASE perfbase.cntr_value WHEN 0 THEN 0 ELSE CONVERT(FLOAT,perf.cntr_value) / perfbase.cntr_value END ELSE perf.cntr_value END FROM sys.dm_os_performance_counters perf left outer join sys.dm_os_performance_counters perfbase on perf.object_name = perfbase.object_name AND perf.instance_name = perfbase.instance_name AND ((RTRIM(perf.counter_name) + N' Base' = perfbase.counter_name) OR (RTRIM(perf.counter_name) + N' base' = perfbase.counter_name) OR (perf.counter_name = N'Worktables From Cache Ratio' AND perfbase.counter_name = N'Worktables From Cache Base')) where perf.cntr_type not in (1073939459,1073939712) and RTRIM(perf.counter_name)!='Device Throughput Bytes/sec' and ((perf.object_name like '%Buffer Manager%' and RTRIM(perf.counter_name) in ('Total pages','Free pages','Page life expectancy','Database pages')) or (perf.instance_name='_Total' and RTRIM(perf.counter_name) in ('Lock Requests/sec','Lock Waits/sec','Lock Timeouts/sec','Number of Deadlocks/sec','Average Wait Time (ms)','Cache Object Counts','Cache Pages','Log Cache Hit Ratio','Cache Use Counts/sec','Cache Objects in use')) or (perf.object_name like '%Plan Cache%' and perf.instance_name='_Total' and RTRIM(perf.counter_name) in ('Cache Hit Ratio')) or (RTRIM(perf.counter_name) in ('Buffer cache hit ratio','Page lookups/sec','Page Requests/sec','Page reads/sec','Page writes/sec','User Connections','Logins/sec','Logouts/sec','Latch Waits/sec','Average Latch Wait Time','Average Latch Wait Time (ms)','Total Server Memory (KB)','SQL Cache Memory (KB)','Optimizer Memory (KB)','Memory Grants Pending','Memory Grants Outstanding','Lock Memory (KB)','Connection Memory (KB)','Granted Workspace Memory (KB)','Batch Requests/sec','SQL Compilations/sec','SQL Re-Compilations/sec','Auto-Param Attempts/sec','Failed Auto-Params/sec','Index Searches/sec','Safe Auto-Params/sec','Scan Point Revalidations/sec','Table Lock Escalations/sec','Unsafe Auto-Params/sec','Workfiles Created/sec','Worktables Created/sec','FreeSpace Scans/sec','Lazy writes/sec','Full Scans/sec','Range Scans/sec','Probe Scans/sec')))

For versions below SQL Server 2008:

SELECT perf.object_name,perf.counter_name,perf.instance_name,perf.cntr_type,'value' = CASE perf.cntr_type WHEN 537003008 THEN CASE perfbase.cntr_value WHEN 0 THEN 0 ELSE CONVERT(FLOAT,perf.cntr_value) / perfbase.cntr_value END WHEN 537003264 THEN CASE perfbase.cntr_value WHEN 0 THEN 0 ELSE CONVERT(FLOAT,perf.cntr_value) / perfbase.cntr_value END ELSE perf.cntr_value END FROM master..sysperfinfo perf left outer join master..sysperfinfo perfbase on perf.object_name = perfbase.object_name AND perf.instance_name = perfbase.instance_name AND ((RTRIM(perf.counter_name) + N' Base' = perfbase.counter_name) OR (RTRIM(perf.counter_name) + N' base' = perfbase.counter_name) OR (perf.counter_name = N'Worktables From Cache Ratio' AND perfbase.counter_name = N'Worktables From Cache Base')) where perf.cntr_type not in (1073939459,1073939712) and RTRIM(perf.counter_name)!='Device Throughput Bytes/sec' and ((perf.object_name like '%Buffer Manager%' and RTRIM(perf.counter_name) in ('Total pages','Free pages','Page life expectancy','Database pages')) or (perf.instance_name='_Total' and RTRIM(perf.counter_name) in ('Lock Requests/sec','Lock Waits/sec','Lock Timeouts/sec','Number of Deadlocks/sec','Average Wait Time (ms)','Cache Object Counts','Cache Pages','Log Cache Hit Ratio','Cache Use Counts/sec','Cache Objects in use')) or (perf.object_name like '%Plan Cache%' and perf.instance_name='_Total' and RTRIM(perf.counter_name) in ('Cache Hit Ratio')) or (RTRIM(perf.counter_name) in ('Buffer cache hit ratio','Page lookups/sec','Page Requests/sec','Page reads/sec','Page writes/sec','User Connections','Logins/sec','Logouts/sec','Latch Waits/sec','Average Latch Wait Time','Average Latch Wait Time (ms)','Total Server Memory (KB)','SQL Cache Memory (KB)','Optimizer Memory (KB)','Memory Grants Pending','Memory Grants Outstanding','Lock Memory (KB)','Connection Memory (KB)','Granted Workspace Memory (KB)','Batch Requests/sec','SQL Compilations/sec','SQL Re-Compilations/sec','Auto-Param Attempts/sec','Failed Auto-Params/sec','Index Searches/sec','Safe Auto-Params/sec','Scan Point Revalidations/sec','Table Lock Escalations/sec','Unsafe Auto-Params/sec','Workfiles Created/sec','Worktables Created/sec','FreeSpace Scans/sec','Lazy writes/sec','Full Scans/sec','Range Scans/sec','Probe Scans/sec')))

If you encounter 'The user does not have permission to perform this action' message while running the query, then update the permission for the specified SQL user and execute the query again.

Scenario I: If the query does not return any result, you'll need to check if you have enabled performance counters for your MSSQL server.
There are two ways to check if performance counters have been enabled for your MSSQL server.

Method 1:
Start > Run > Perfmon.exe > MonitoringTools > Performance Monitor > Click “Add Counters…+” -> check if "<SQLSERVER/MSSQL$InstanceName>:x" counters are available, where x stands for the following counters: AccessMethods, Availability Replica, Buffer Manager, Database Replica, Databases, General Statistics, Latches, Locks, Memory Manager, Plan Cache, SQL Statistics.



If not, performance counters might not be enabled for your MSSQL server.

Method 2:
Execute the below query from the MSSQL SQL Server Management Studio and verify if all performance counters get listed in the query result.

select * from sys.dm_os_performance_counters


 

If the above query does not return a list of all performance counters, then it might not be enabled for your MSSQL server. 

Follow the steps below to enable performance counters for your MSSQL server:

  1. Open cmd prompt in administrator mode.

  2. Navigate to MSSQL Installation bin directory (Default Directory: C:\Program Files\Microsoft SQL Server\MSSQL14.SQLXE\MSSQL\Binn).

  3. Run the below commands:

    For SQL servers Using Default Instance:
    unlodctr MSSQLSERVER
    lodctr perf-MSSQLSERVERsqlctr.ini
    For SQL servers Using Named Instance:
    unlodctr MSSQL$InstanceName
    lodctr perf-MSSQL$InstanceNamesqlctr.ini

  
  1. Restart the services "Remote Registry" and "Performance Logs & Alerts" by  executing the following commands:

    net stop "Remote Registry"  &&  net start "Remote Registry"  
    net stop "Performance Logs & Alerts" && net start "Performance Logs & Alerts"



  2. After performing all the above steps, check if Performance counters have been enabled using Method 1 or 2 mentioned above.

    If performance counters have not been enabled yet, check the registry hives
    and keys (it’s not recommended to do any changes at the registry level as doing so could lead to instability on the operating system. If you choose to do these changes, you should have a backup of the Operating System and Registry, to recover them if necessary). The following registry keys should exist and must have a valid configuration:

    1. Library

    2. Open

    3. Collect

    4. Close

    5. PerfIniFile



Note: The Registry Path is as follows:
[HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\<<service name>>\Performance]

You should also check if there’s a key named "Disable Performance Counters" inside HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib directory.



If it is configured with the value 1, you must configure it with value 0. This should allow you to rebuild the registry counters and help files.

Refer here for more detailed information.

After following the above steps, check the query result and 'Overview' page of the reported MS SQL monitor after polling. If the problem persists, check Scenario II as well.


Scenario II: If the query returns the desired result, it indicates that data collection has happened. Now, poll your monitor in Applications Manager and verify if data is populated in the 'Overview Tab'.

If data is not populated, share the below information with us for further analysis:

  1. Output for the below queries by using the same SQL user used in Applications Manager for monitoring:

select * from sys.dm_os_performance_counters

select * from sysperfinfo 

      2.  Version of the SQL Server that you are checking

      3.  Support information file, by following the steps in this link.