Submit a ticketCall us

Welcome to the NEW Success Center. Search all resources (documentation, videos, training, knowledge base articles) or browse resources by product. If you are unable to find what you are looking for, please contact us at customersuccess@solarwinds.com

 

 

 

 

Home > Success Center > Database Performance Analyzer (DPA) > Use XML and TSQL to create custom charts that cannot be created in the Web Interface

Use XML and TSQL to create custom charts that cannot be created in the Web Interface

Created by Interspire Import, last modified by MindTouch on Jun 23, 2016

Views: 627 Votes: 0 Revisions: 6

Creating a Microsoft SQL Server Page Life Expectancy metric example

If you can write XML and T-SQL, you can gather details for specific performance counters that aren't supported by the DPA Custom Metric dialog. Here is an example scenario adding the "Page Life Expectancy" Microsoft SQL Server object into your DPA collection.

  1. Create the folder {install directory}\Ignite PI\iwc\tomcat\ignite_config\idc\metrics
  2. In the metrics folder, create the file custom-metrics-properties_SQLServer.xml
    <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>
    
  3. In the metrics folder, create the file custom-metrics-queries.xml
    <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 many, but not all, objects found in System Monitor.

So, if you need something not in the DMV (say Avg. Disk sec/Read), one option is to resort to some WMI objects using sp_OA procedures as follows:

<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. 
See: http://support.confio.com/kb/1588', 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". 
See: http://support.confio.com/kb/1588', 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>

The example above should help get anyone started customizing DPA collections, especially if the counter is already available in the sys.dm_os_performance_counters DMV. 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. I like to use the WMI Code Creator tool available for free from Microsoft. With that tool, for example, you can locate the code with just a few clicks.

Last modified
19:19, 22 Jun 2016

Tags

Classifications

Public