Create custom charts in DPA

11/21/2018 9:04 PM

5/10/2019 5:07 PM

This article provides instructions for creating custom charts (specific performance counters) in DPA. 

From time to time, users want to gather details for specific performance counters. DPA allows for this degree of customization. All it takes is a quick edit of some XML files and a little custom T-SQL code.

Database Performance Analyzer

The following steps are for adding objects to your DPA collection in the Page Life Expectancy:
 

1. Create a metrics directory in {install directory}\Ignite PI\iwc\tomcat\ignite_config\idc.

2. Create two files, and name them as: 

• {install directory}\Ignite PI\iwc\tomcat\ignite_config\idc\metrics\custom-metrics-queries.xml
• {install directory}\Ignite PI\iwc\tomcat\ignite_config\idc\metrics\custom-metrics-properties_SQLServer.xml

​3. In the custom-metrics-properties_SQLServer.xml file, add the following code, and save the file. 

<metricsPropertiesList>
<metricsProperties name="Page Life Expectancy">
  <displayName>Page Life Expectancy</displayName>
    <description>Number of seconds a page stays in buffer pool.
</description>
  <category>Memory</category>
  <units>seconds</units>
  <heartbeat>60</heartbeat>
  <minY></minY>
  <maxY></maxY>
  <thresholds>
     <warning>
        <minValue>300</minValue>
        <maxValue>600</maxValue>
     </warning>
     <critical>
        <minValue></minValue>
        <maxValue>300</maxValue>
     </critical>
  </thresholds>
</metricsProperties>
</metricsPropertiesList>

4. Open the custom-metrics-queries.xml file, and enter the following code. This code will run the T-SQL to get the desired performance counter Page Life Expectancy:

<metricsQueryList>
<metricsQuery className="com.confio.idc.database.metrics.domain.queries.
SingleValueMetricsQuery">
  <sql>
     <![CDATA[
        select cntr_value
        from sys.dm_os_performance_counters
        where counter_name = 'Page life expectancy'
        and object_name like '%Buffer Manager%'
     ]]>
  </sql>
<frequency>60</frequency>
  <queryTimeout>10</queryTimeout>
  <name>Page Life Expectancy</name>
<supportedDatabaseList>
  <supportedDatabase>
     <databaseType>SQL Server</databaseType>
     <minimumVersion>9.0.0</minimumVersion>
   </supportedDatabase>
</supportedDatabaseList>
</metricsQuery>
</metricsQueryList>

Additional Information:
 

The sys.dm_os_performance_counters DMV contains a number of objects found in System Monitor.

If you what you need is NOT in the DMV, for example, Avg. Disk sec/Read, you can use some WMI objects using sp_OA.

 

The following example should help customize DPA collections, especially if the counter is already available in the sys.dm_os_performance_counters DMV.

<metricsQuery className="com.confio.idc.database.metrics.domain.queries.
DeltaCalculationMetricsQuery">
  <sql>
     <![CDATA[
        DECLARE @WmiServiceLocator INT ,
                @WmiService INT ,
                @CounterObject INT ,
                @DiskSecPerRead BIGINT ,
                @DiskSecPerRead_Base BIGINT ,
                @DiskSecPerWrite BIGINT ,
                @DiskSecPerWrite_Base BIGINT

        EXEC sp_OACreate 'WbemScripting.SWbemLocator',
@WmiServiceLocator OUTPUT, 5

        EXEC sp_OAMethod @WmiServiceLocator, 'ConnectServer',
@WmiService OUTPUT, '.', 'root\cimv2'

        IF ISNULL(@WmiService, -1) <= 0
           BEGIN
              EXEC sp_OADestroy @WmiServiceLocator
                 RAISERROR('Could not access WMI service.
, 16, 1)
              RETURN
           END

        EXEC sp_OAMethod @WmiService, 'Get', @CounterObject OUTPUT,
'Win32_PerfRawData_PerfDisk_PhysicalDisk="_Total"'

        EXEC sp_OAGetProperty @CounterObject, 'AvgDiskSecPerRead',
@DiskSecPerRead OUTPUT
        EXEC sp_OAGetProperty @CounterObject, 'AvgDiskSecPerRead_Base',
@DiskSecPerRead_Base OUTPUT
        EXEC sp_OAGetProperty @CounterObject, 'AvgDiskSecPerWrite',
@DiskSecPerWrite OUTPUT
        EXEC sp_OAGetProperty @CounterObject, 'AvgDiskSecPerWrite_Base',
@DiskSecPerWrite_Base OUTPUT

        EXEC sp_OADestroy @CounterObject
        EXEC sp_OADestroy @WmiService
        EXEC sp_OADestroy @WmiServiceLocator  

        IF @DiskSecPerRead IS NULL OR @DiskSecPerWrite IS NULL
           BEGIN
              RAISERROR('Could not access WMI counter:
Win32_PerfRawData_PerfDisk_PhysicalDisk.Name="_Total".
, 16, 1)
              RETURN
           END

        SELECT @DiskSecPerRead, @DiskSecPerRead_Base, @DiskSecPerWrite,
@DiskSecPerWrite_Base
        ]]>
    </sql>
        <frequency>60</frequency>
        <querytimeout>30</querytimeout>
        <metricslist>
            <metric name="DiskSecPerRead">
                <expression>args[0].divide(ONE_HUNDRED,
BigDecimal.ROUND_HALF_DOWN)
.divide(TEN, BigDecimal.ROUND_HALF_DOWN).divide(args[1],
BigDecimal.ROUND_HALF_DOWN)
</expression>
            </metric>
            <metric name="DiskSecPerWrite">
                <expression>args[2].divide(ONE_HUNDRED,
BigDecimal.ROUND_HALF_DOWN)
.divide(TEN, BigDecimal.ROUND_HALF_DOWN).divide(args[3],
BigDecimal.ROUND_HALF_DOWN)
</expression>
            </metric>
        </metricslist>
        <supporteddatabaselist>
          <supporteddatabase>
            <databasetype>SQL Server</databasetype>
            <minimumversion>8.0.0</minimumversion>
          </supporteddatabase>
        </supporteddatabaselist>
</metricsquery>

If you need to get additional counters not in that DMV then you need to resort to other methods to pull the data from the OS. 

You can use WMICodeCreator tool available for free from Microsoft  (© 2018 Microsoft, available at http://www.microsoft.com obtained on November 21, 2018)


Disclaimer: Please note, any content posted here is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment.  You elect to use third party content at your own risk, and you will be solely responsible for the incorporation of the same, if any.