Skip to content

Counter instance with special character in name

Hi,
the 'SQLServer:Plan Cache' counter has an instance named 'Temporary Tables & Table Variables'.
It seems that the ampersand sign causes some trouble with the mspdh provider.
Sonar returns only zeros for all instances from the 'SQLServer:Plan Cache'.
If I change the filter to e.g. '_Total' it works. But unfortunately only for '_Total'.

<add name="sonar_sqlserver_plan_cache" namespace="SQLServer:Plan Cache" filter="*" type="raw" resource=".">
  <Tags>
    <add name="name" value="name" />
  </Tags>
  <Values>
    <add name="hit_ratio" value="Cache Hit Ratio" />
  </Values>
</add>

By the way the sql provider works fine.

<add name="sonar_sqlserver_plan_cache" type="sql"
   filter="SELECT
      RTRIM(hits.instance_name) AS name, 
      CAST(hits.cntr_value * 1.0 / base.cntr_value * 100.0 AS DECIMAL(5,2)) AS hit_ratio
    FROM sys.dm_os_performance_counters hits
       JOIN sys.dm_os_performance_counters base 
          ON base.instance_name = hits.instance_name 
          AND base.counter_name = 'Cache Hit Ratio Base'
   WHERE hits.object_name = 'SQLServer:Plan Cache'
     AND hits.counter_name = 'Cache Hit Ratio'
     AND hits.instance_name NOT IN ('_Total')">
   <Tags>
      <add name="name" value="name"/>
   </Tags>
   <Values>
   </Values>
</add>
Edited by Frank Brendel