Red Gate forums :: View topic - Monthly Trending with SQL Monitor
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Monitor 4
SQL Monitor 4 forum

Monthly Trending with SQL Monitor

Search in SQL Monitor 4 forum
Post new topic   Reply to topic
Jump to:  
Author Message
buttonbj



Joined: 12 Oct 2012
Posts: 8

PostPosted: Mon Jul 21, 2014 9:01 pm    Post subject: Monthly Trending with SQL Monitor Reply with quote

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
Back to top
View user's profile Send private message
Evan Moss



Joined: 12 Jun 2013
Posts: 99

PostPosted: Tue Jul 22, 2014 10:57 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Colin Millerchip



Joined: 31 Oct 2007
Posts: 66
Location: Cambridge, UK

PostPosted: Wed Jul 23, 2014 9:16 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message Send e-mail
buttonbj



Joined: 12 Oct 2012
Posts: 8

PostPosted: Wed Jul 23, 2014 1:11 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group