Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-25096

"Buffer cache hit ratio" issue on MSSQL by Zabbix Agent 2 Template

XMLWordPrintable

    • Prev.Sprint, S24-W36/37

      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%'

       

            abakaldin Alexander Bakaldin
            bkruszewski Bartosz Kruszewski
            Team INT
            Votes:
            2 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated: