Monthly Trending with SQL Monitor

SQL Server performance monitoring and alerting

Moderators: Colin Millerchip, priyasinha, Adam, chriskelly

Monthly Trending with SQL Monitor

Postby buttonbj » Mon Jul 21, 2014 9:01 pm

Hi everyone,

To start off, I have purging setup on SQL Monitor to happen once every month. I am looking at creating a trending report for SQL Server and I was wondering if SQL Monitor already had something in place for this. I am basically looking for all of the data found in the Analysis page, but instead of having the day to day data in it, I would like to see the average for each month on a line graph. As an example, for one SQL Server/Instance I would have one plot point for each month in the year that would display the Average User Connections for the month. So over the course of the year you would have 12 plot points on a line graph that showed you the average User Connections each month for 12 months. I have looked around some and I have done some Googling, but I don't see where SQL Monitor can do this for me right out of the box. I have read that people have written their own SQL queries to pull the data out of the database to create their own trending reports, but I am looking to save some time as I would have to figure out the layout of the database. Does this make sense?

Thank you,

-Brandon J. Button
buttonbj
 
Posts: 8
Joined: Fri Oct 12, 2012 9:02 pm

Postby Evan Moss » Tue Jul 22, 2014 10:57 pm

Hi Brandon,

Thanks for your post.

Unfortunately if you have your purge settings set for 1 month, that means there will never be more than a month of data in your repository, so you won't have access to any old data. An added obstacle is that the SQL Monitor data repository is undocumented and pretty tough to work with to be honest.

In theory, you could keep the purge settings really high (and leave a year's worth of data), then go to the analysis page and select a 28 day time period and compare to previous months.

One other thing you could do would be to use the export to csv function on the analysis page and save it off every month, but you'd have to use a different tool to display the analysis you're looking for.

Sorry about the inconvenience.

Thanks,
Evan
Evan Moss
Product Support
(866) 627-8107
Evan Moss
 
Posts: 99
Joined: Wed Jun 12, 2013 9:56 pm

Postby Colin Millerchip » Wed Jul 23, 2014 9:16 am

Just to add to this:

There are some blog posts to introduce people to the schema. Whilst these are a bit old (early 2013), most of the changes since they were written have been additive to the schema, rather than changing the schema. They might help as a starting point.
https://www.simple-talk.com/blogs/author/198217-chris-lambrou/

Also, for situations where you want a very long duration time series, but don't want to keep all the core monitoring data, some customers are using Custom Metrics. This has a couple of benefits of this approach:
- You can configure separate purge settings for the Custom Metrics data;
- You can configure the collection frequency from once per minute to once per day; so again this can limit the volume of data, and can be especially useful for less volatile, slowly changing data.

Hope this helps,


Colin.
Colin Millerchip
 
Posts: 66
Joined: Wed Oct 31, 2007 5:31 pm
Location: Cambridge, UK

Postby buttonbj » Wed Jul 23, 2014 1:11 pm

Thank you Evan and Colin for your suggestions! I will check out that simple-talk article and mess around with custom metrics more. Thank you again!

-Brandon
buttonbj
 
Posts: 8
Joined: Fri Oct 12, 2012 9:02 pm


Return to SQL Monitor 4

Who is online

Users browsing this forum: No registered users and 1 guest