-
Problem report
-
Resolution: Unresolved
-
Trivial
-
None
-
None
-
Prev.Sprint, S24-W36/37
-
0.7
On most hosts we’re getting the error message
Preprocessing failed for: [{"object_name":"SQLServer:Buffer Manager","cntr_value":21119,"counter_name":"Background writer p...
1. Failed: cannot extract value from json by path "$[?(@.counter_name=='BufferCacheHitRatio')].cntr_value.first()": no data matches the specified path
The probably cause is due to the executed query. The object name for the named instances, such as TSTSRV, has the following structure:
MSSQL$TSTSRV:Buffer Manager
However, the object name for the default instances looks like this:
SQLServer:Buffer Manager
In your query, you explicitly search for ‘MSSQL$’ followed by the service name and then ‘:Buffer Manager’. With the following, slightly more open query, it should now also work for the others:
SELECT a.object_name, 'BufferCacheHitRatio' AS counter_name, '' AS instance_name, cast(a.cntr_value * ISNULL((100.0 / NULLIF(b.cntr_value,0)),0) AS dec(3, 0)) AS cntr_value FROM sys.dm_os_performance_counters a JOIN ( SELECT cntr_value, OBJECT_NAME FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio base' AND OBJECT_NAME LIKE '%:Buffer Manager%' ) b ON a.OBJECT_NAME = b.OBJECT_NAME WHERE a.counter_name = 'Buffer cache hit ratio' AND a.OBJECT_NAME LIKE '%:Buffer Manager%'
