Monitoring Page Read/Write in SQL Instances

If you are familiar with the Microsoft SQL Management pack for SCOM 2012 (can be found here), you will know that it brings a lot of performance counters out of the box. However, two counters, very often used by SQL administrators to check on the performance of each individual instance is not present by default.

Adding a performance counter collection rule is not a big deal in terms of SCOM authoring. It can even be done in the SCOM Operations Console. In this case, however, you can’t simply target the computer object and determine the counter in a static. Or, you can, but you will need one rule per instance. Here’s why: when SQL has multiple instances running in the same box, a new performance counter object is created for each instance. For example, in a single (non named) instance of SQL, you see a service called MSSQLSERVER on your  SQL Computer. That will generate a target with that name. When you have multiple instances, different services will be created and will have different names, like:

image

Interestingly enough, the service name is not exactly the same name as the performance counter. It works for multiple instances:

image

But when you have a single instance, the counter is named only SQLServer (not MSSQLServer).

image

Luckily, then SCOM discovers the SQL DB instance, it creates a property called Performance Counter Object Name:

image

That really solves the problem. Without that, I would need to manually refer the object name when creating a performance rule. Now, I can simply create a generic rule, targeting all SQL DB Engines and using a dynamic consistent object name. For that, you will need SCOM’s current management pack authoring tool: Visual Studio, with the SCOM Authoring extensions installed (or you’re preferred xml editor!). Here’s my Management pack fragments:

Library Reference:

<Reference Alias=”MSL”>
  <ID>Microsoft.SQLServer.Library</ID>
  <Version>6.5.1.0</Version>
  <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
</Reference>

 

Now, the rule itself (for Page Reads/Sec):

<Rule ID=”FEHSE.SQLServer.Rule.Performance.PageReadsPerSec” Enabled=”false” Target=”MSL!Microsoft.SQLServer.DBEngine” ConfirmDelivery=”false” Remotable=”true” Priority=”Normal” DiscardLevel=”100″>
        <Category>PerformanceCollection</Category>
        <DataSources>
          <DataSource ID=”DS” TypeID=”Performance!System.Performance.OptimizedDataProvider”>
            <ComputerName>$Target/Host/Property[Type=”Windows!Microsoft.Windows.Computer”]/NetworkName$</ComputerName>
            <CounterName>Page reads/sec</CounterName>
            <ObjectName>$Target/Property[Type=”MSL!Microsoft.SQLServer.DBEngine”]/PerformanceCounterObject$:Buffer Manager</ObjectName>
            <InstanceName />
            <AllInstances>false</AllInstances>
            <Frequency>300</Frequency>
            <Tolerance>0</Tolerance>
            <ToleranceType>Absolute</ToleranceType>
            <MaximumSampleSeparation>1</MaximumSampleSeparation>
          </DataSource>
        </DataSources>
        <WriteActions>
          <WriteAction ID=”WriteToDB” TypeID=”SC!Microsoft.SystemCenter.CollectPerformanceData” />
          <WriteAction ID=”WriteToDW” TypeID=”SCDW!Microsoft.SystemCenter.DataWarehouse.PublishPerformanceData” />
        </WriteActions>
      </Rule>

And the Page Writes/Sec rule:

<Rule ID=”KGC.SQLServer.Rule.Performance.PageWritesPerSec” Enabled=”false” Target=”MSL!Microsoft.SQLServer.DBEngine” ConfirmDelivery=”false” Remotable=”true” Priority=”Normal” DiscardLevel=”100″>
        <Category>PerformanceCollection</Category>
        <DataSources>
          <DataSource ID=”DS” TypeID=”Performance!System.Performance.OptimizedDataProvider”>
            <ComputerName>$Target/Host/Property[Type=”Windows!Microsoft.Windows.Computer”]/NetworkName$</ComputerName>
            <CounterName>Page writes/sec</CounterName>
            <ObjectName>$Target/Property[Type=”MSL!Microsoft.SQLServer.DBEngine”]/PerformanceCounterObject$:Buffer Manager</ObjectName>
            <InstanceName />
            <AllInstances>false</AllInstances>
            <Frequency>300</Frequency>
            <Tolerance>0</Tolerance>
            <ToleranceType>Absolute</ToleranceType>
            <MaximumSampleSeparation>1</MaximumSampleSeparation>
          </DataSource>
        </DataSources>
        <WriteActions>
          <WriteAction ID=”WriteToDB” TypeID=”SC!Microsoft.SystemCenter.CollectPerformanceData” />
          <WriteAction ID=”WriteToDW” TypeID=”SCDW!Microsoft.SystemCenter.DataWarehouse.PublishPerformanceData” />
        </WriteActions>
      </Rule>

The trick is here:

<ObjectName>$Target/Property[Type=”MSL!Microsoft.SQLServer.DBEngine”]/PerformanceCounterObject$:Buffer Manager</ObjectName>

Note that I’m using the PerformanceCounterObject as part of the name of the object, which makes the rule completely generic, no matter which instance it runs against.

 

Make sure to add display Strings:

<DisplayString ElementID=”FEHSE.SQLServer.Rule.Performance.PageReadsPerSec”>
  <Name>FEHSE SQL Server Rule Performance Page Reads Per Sec</Name>
  <Description />
</DisplayString>
<DisplayString ElementID=”FEHSE.SQLServer.Rule.Performance.PageWritesPerSec”>
  <Name>FEHSE SQL Server Rule Performance Page Writes Per Sec</Name>
  <Description />
</DisplayString>

And there you have it. You can download the MP here.

 

Hope this helps!