Kevin Holman's System Center Blog

Posts in this blog are provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of UseAre you interested in having a dedicated engineer that will be your Mic

How to collect performance data for SQL databases (multi-instance objects)

How to collect performance data for SQL databases (multi-instance objects)

  • Comments 8
  • Likes

I have had several blog posts in the past discussing how to write rules and monitors against multi-instance objects.  Special care must always be taken when writing workflows against classes where an agent can host more than one instance of the same class type.  Examples would be Logical Disk, SQL DB Engine, SQL Database, etc.

Some of the previous articles:

How do I collect data from a multi-instance object – like a SQL DB instance-

Writing monitors to target Logical or Physical Disks

and most recently:

Collecting SQL Database size as a performance counter

On the last article – I discussed how to collect database size – for all databases, and targeting the SQL (200x) DB Engine as the target class for the collection.  This probably wasn’t the best idea.  This is because, for a database specific counter, we probably want to collect that performance data at the database class level – not the instance.  The reason for this, is to facilitate SQL performance views when scoping to the database objects, and for reporting down the road, when we add specific databases to a report.

 

So – the rest of this post will be an example on how to collect the database size.

 

I want to replicate the way the SQL MP’s work – so I will actually create two rules – one to collect for SQL 2005 database objects, and one to collect for SQL 2008 database objects.  The reason I am doing this – is because if I targeted generically “SQL Database” – the next version of SQL would be included in this parent class, but might use a different object/counter down the road.  So I will stick to known versions and perf counters.  So I will create my rule targeting “SQL Server 2005 DB ” and “SQL 2008 DB”

 

The first step in creating a Management Pack – is to open your existing custom SQL workflow MP into the Authoring Console, or create a new empty Management Pack.

I will create a new empty MP and give it the ID of “Microsoft.SQLServer.2008.Monitoring.Addendum” and Display Name of “SQL Server 2008 (Monitoring) Addendum”.  Once you save it – go to File – Management Pack Properties.  We need to version our MP (increment by 1) since we will be changing it, or assign a new version number.

We also need to add a reference here if we don’t already have it – to ensure this MP has a reference for the SQL 2008 Discovery MP.  This will allow us to choose SQL Classes later on when targeting our collection rules.  Click the references tab, and add the SQL 2008 discovery MP if it isn't already present:

image

 

 

Then go to Health Model, Rules, New, Collection, Performance Based, Windows Performance Collection.

image

 

The first step is give my rule an ID.  This will be the ID of the management pack, plus some additional text.  It defaults to “NewElement” and we need to change that:

I will call mine “Microsoft.SQLServer.2008.Monitoring.Addendum.CollectSQL2008DBSize

Give the rule a display name that is in accordance with your custom rule naming standard

Under “Target” – Browse all classes and find the “Microsoft.SQLServer.2008.Database” class. 

Under Category – change to Performance Collection.

When completed – here is how mine appears:

image

 

Click Next.

On this screen – we have the option to type in the performance counter, object, and instance we want to collect.

Great care should be taken here.  This is because the SQL DB Engine is a multi-instance object, and each instance appears differently in Perfmon.  If we don’t choose the correct object here – then we wont collect the data from all of our instances.  Let me explain.

In a “default instance” of SQL – the perf counter looks like this:

image

In a Named instance – it appears like the following:

image

If we typed in “SQLServer:Databases” we would only collect from the default instances of SQL in the environment.  If we typed in “MSSQL$I01:Databases” we would only collect the data from identically named instances in the environment.  However – we want to collect this from ALL instances.  In that case – we need to use a VARIABLE in the performance counter object – since the actual object names vary in Perfmon.  We can cheat by looking at a some other perf collection rules in the SQL MP and see how they handled this…. or we can look in discovered inventory and see if there is a good class property of our chosen class to handle this.

It just so happens that the SQL DB Engine class – has a property called “Performance Counter Object Name” that was created specifically for this purpose!  If you look at this value in discovered inventory, you can see these correspond perfectly with what we need:

image

Sweet!  And if you spot check a few Perf Collection rules in the SQL MP using our same target class, you’d find they also use this.

So – back to the authoring console – we need to use this object, as a variable, for our Perfmon Object.  Here is how:  There is a fly-out on the right – this will show all the class properties based on our target.  In this specific case – our class target is “database”.  The Database is hosted by a SQL DB Engine – so in the flyout – select (Host=SQL DB Engine), and this will expose class properties from the host class.  From here we can choose the “Performance Counter Object Name”

image

 

That will drop the entire variable into the object.  We only need to add the actual perfmon object at the end (:Databases)

image

 

For the counter – that’s simple – just type in the counter exactly as it is by name in perfmon:  Data File(s) Size (KB)

image

Now – for the instance – this is another tricky part.  We don’t want to collect “all instances” when targeting all databases – that could potentially collect a TON of duplicate data depending on the datasource configuration.  It is best to use a variable again here – to match the database perfmon instance to the database name.  This will allow each instance of the rule – targeting each database, to collect performance data only about itself.  Here is an example:

Under “Instance” – again using the flyout on the right – choose a property of the targeted class which matches up in Perfmon.  In this class “Database Name” is perfect!

image

 

For the Interval – we don’t expect this to change often, so once an hour is fine.  (You could even do once or twice a day, but then our hourly reports would not be populated).

Here is our final configuration:

image

 

Click Next.  For the Optimization tab – this counter might be a good candidate for optimization – not to even collect the data unless there is significant change, but since I chose once per hour – I will not use optimization and get an actual perf record per hour, for each database.  If you wanted to collect this counter more frequently – you might consider optimization.

Done!  Now import this into your management group. 

To test if our new rule is working – go to My Workspace, create a new performance view, and scope it to “Collected by specific rules”.  Choose your rule from the list….

image

 

Once your SQL 2008 Servers have downloaded your new MP, applied the new config, and sent up their first performance data sample (takes up to the frequency of the collection rule), you will see this view populated:

image

 

You can also right click any database object in a state view – and choose “Open > Performance View” and see all the counters available for a given database.

 

image

 

 

Now – you can also run a “Performance Top Objects” report – and create a new one for “My Largest Databases”  (required daily aggregation – so wait 24 hours for data to show up)

Now – you can repeat this process for the SQL Server 2005 DB objects, to ensure you are collecting DB size for SQL 2005 hosted databases as well.

 

I am attaching my sample MP below:

Attachment: Microsoft.SQLServer.2008.Monitoring.Addendum.zip
Comments
  • Hi Kevin,

    Thanks for the great post. Is there a way to combine the "Data File(s) Size (KB)" and the "Log File(s) Size (KB)" counters into one rule to get a more accurate data point for a databases size?

  • @cMoLe -

    Not in the performance collection rule itself natively.  You have two options:

    1.  Collect both - then architect a custom report which collects both.

    2.  Use a script to collect both values from WMI, do the math, then insert the information as a propertybag to a collection rule.

    Option #1 might be challenging to get the right data all the time, and Option #2 will be resource intensive due to the number of DB's and scripts, and you have to be very careful with that route - not running it too frequently and learning how to make your custom script datasources support cookdown.

  • Kevin,

    Any word about collecting DMV data?

  • Like what?  Give me an example?

  • Kevin,

    Something like this:

    Find Most Expensive Queries Using DMV

    blog.sqlauthority.com/.../sql-server-find-most-expensive-queries-using-dmv

    Generate alert is total_logical_reads exceed certain threshold.

    It’s doable using SQL jobs easily.

    Interesting to bring data into SCOM and correlate with something else to avoid false positives.

  • Mhm this didn't work for me. Is there something like an error log? I see no counters in my performance view. I have an English SCOM Server and a German SQL Server 2005, is this a Problem? I also tried it with with the German Objectname ("Datenbanken instead of Databases") and Countername("Größe der Datendatei(en) (KB)" instead of "Data File(s) Size (KB)"), but it wont work. It would be helpful if there is something like an error log,... eventually somebody here got an idea.

  • I am using SCOM 2012  facing problem in performance counter monitoring, Problem is when performance counter is deleted SCOM console still shows healthy sate for that object.

  • I am using System.Performance.ThresholdMonitorType to monitor the performance counter.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
Search Blogs