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

Collecting SQL Database size as a performance counter

Collecting SQL Database size as a performance counter

  • Comments 33
  • Likes

There were some changes made in the most recent SQL MP I wrote about here:

SQL MP version 6.1.314.36 released–adds support for SQL 2008R2 and many other changes

 

Some of the noisy rules and monitors were disabled by default – and some workflows were deprecated, and replaced with new workflows.  Also – the SQL database class property discovery was changed – and now Config Churn is no longer an issue, at ALL, from the SQL MP.

This is great news!

However, one of the common things collected by this MP is no longer collected out of the box – Database Size.

This is normal.  A Management pack will (and should) focus on health.  It is not meant to be a collection of every performance collection for every possible counter that every customer might ever want.  It is totally expected that you as a customer, would examine what we collect out of the box – and disable anything you don’t care about.  It is also expected you would write additional rules and monitors to add monitoring for any Microsoft application, if you have additional items you want to see monitored or reported on.

Database Size is an example of that.

In the previous MP – DB size was captured in two places:

1.  The Database class property.  Previous versions of this MP had 4 properties of the database class, which would cause config churn.  These were Database Size (MB) (String), Database Size (MB) Numeric), Log Size (MB) (String), and Log Size (MB) (Numeric).  These are now deprecated – the discovery will hard code these values to “0” (zero) for all databases.  If you see that – this is NORMAL and by design.  We should not ever design a property of a class that will change often, as this one did.  Lesson learned.  :-)

image

 

2.  A performance collection rule.  This previous perf collection rule used a shared datasource (script) which was performing the free space monitoring and collection calulations.  This whole datasource was deprecated and replaced with a new model, and therefore this collection rule was deprecated as well.

image

 

Not to worry.  Database size is a simple performance object in perfmon, and very simple to collect.  Let’s create one if you think this is something you’d like to report on (largest DB’s report, etc)

 

The first step in creating a rule like this is to identify the data source.  In this case – in perfmon – there is a Performance counter for:

SQLServer:Databases\Data File(s) Size (KB)\*(all instances)

image

 

Next – lets identify a proper target.  Two obvious choices here would be the SQL Instance object, and the Database Object.  Since this is a simple performance collection rule, where I want to collect and insert ALL instances of the counter, I will choose the SQL instance as my target for this collection rule.  I want to replicate the way the SQL MP’s work – so I will actually create two rules – one to collect for SQL 2005, and one to collect for SQL 2008.  The reason I am doing this – is because if I targeted generically “SQL DB Engine” – 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 2008 DB Engine” and “SQL Server 2005 DB Engine”

I prefer to create my rules using the authoring console – so I will be performing this activity there.  You can do the same thing in the UI, using the wizards there as well.  First thing I will open the management pack that contains my custom SQL rules for SQL 2005. 

Go to File – Management Pack Properties.  We need to version our MP (increment by 1) since we will be changing it. 

We also need to add a reference here is we don’t already have it – to ensure this MP has a reference for the SQL 2005 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 2005 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.2005.Monitoring.Addendum.CollectSQL2005DBSize

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.2005.DBEngine. 

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 – simply choose the correct one:

 

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)

For the instance – check the box to include all instances.

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).

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.

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 2005 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

 

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 – can repeat this process for the SQL 2008 Database Engines, to ensure you are collecting DB size for SQL 2008 hosted databases as well.

Comments
  • Have you got our office bugged Kevin?

    We spent a good hour or so going over this today in the office as one of the DBA's was not impressed when they went to check the size of a DB today only to find out we had upgraded the SQL MP.

    I promised him faithfully that I would look into this weekend and add a rule to collect the perf counter ready for Monday.

    Thank you ever so much for making my weekend free again!!!

  • Is it also possible to change the data that is returned from KB to MB? Or should this be done by a script instead?

  • @Vincent -

    It is SUPER easy to change that to Megabytes... or whatever you want - using the ScaleBy function - which I document here:

    blogs.technet.com/.../how-to-monitor-a-process-on-a-multi-cpu-agent-using-scaleby.aspx

    Simply add the function like:  <ScaleBy>1000</ScaleBy> and this should divide the number by one thousand and provide megabytes from a counter that provides bytes.

  • Thank you Kevin!  I found this very helpful!

  • Hi Kevin,

    I´ve realized this successfull, and I have now the File Size of the SQL Database. Thanks...great

    Now i´d like to have how much is used from the file, and how big is my database really?

    And there is my Problem, I have no idea how to realize this.

    I cannot create a sql Query for this for reporting, I cannot bring it in SCOM like in the old MP.

    I hope you can help me?

    Thank you for helpand your great blog

    Best regards, Markus

  • Hi! Sorry for late join - just found this blog recently :) Kevin - do you really mean that DB size is useful? Could you please explain me how DBAs uses it? In fact SQL MP right now care about space monitoring for their databases. I'm asking because I rather involved in SQL MP development process.

    Thank you!

  • DB size is VERY important to DBA's.  Many were miffed when we removed this from the SQL MP as they depended on this counter for reporting.  The reason so - most customers leverage autogrow, and they need to track the size of the databases for trending and capacity planning.  Many care very little about free space monitoring, which is the focus of our MP, when autogrow is set, other than the free space on disk or free space as a factor of the max autogrow limit.  Total DB size is very important, and allows customers to report on the largest DB's, and trend them over time to make storage decisions.

  • Thanks, this works like a champ for SCOM 2012 as well.

  • hallo kevin how can i get thes progerm

  • Kevin, how can we get report with values listed in pdf or excel

  • @kelly -

    See:  blogs.technet.com/.../how-to-collect-performance-data-for-sql-databases-multi-instance-objects.aspx

    Write the rules to collect this according to the OTHER article - targeting database, then simply write a report using a generic performance report and schedule it using PDF or excel.

  • Thanks for the quick response. Do you suggest generic performance report based on rule?  It didn't work for me. i will try again.

  • Kevin,

    In the generic Reports library performance and performance detail both were giving chart.

  • @Kelly - yes, however, there is also a table in the perf detail report.  

    You asked to export this to PDF or Excel.  ANY report can be exported to PDF or Excel.  What exactly are you looking for?

  • Please ignore, if it a duplicate post. I am looking for this type of report. It works fine for sql 2000 databases. With new MP,  how to get this type of report for sql 2005/2008 and sql 2012?

    blogs.technet.com/.../inventory-and-configuration-reports.aspx

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