How much historical data do you need to keep?

A bit like Dr Who, SQL Response 2 will allow you to “rewind time” back to a specific point – for example 2 minutes ago, last Wednesday, six weeks, or even six months earlier – to see overview data for a server or SQL Server instance exactly as it was at that point in time.
Dali clock

This will allow you to compare what’s going on at certain times of the day or days of the week, or inspect the status of the system when a specific issue occurred in the past. It will also enable you to look at trends for a whole range of metrics over different timescales.

However, we’d like to know how far back, realistically, you need to go to look at this kind of data. Do you consider a month to be sufficient? Two months? A year? Longer?

And what kind of data do you or would you particularly look for when travelling back in time in this way? Would you expect an alert from six months ago to still be able to display all metric data from the period when it was raised (such as CPU% or scans/sec) as part of its diagnostic data? What about the case of overviews – ie not tied to a specific alert – would you want to see all metric data and info about databases, jobs,indexes etc from six months ago?

Any policy for storing historical data is very much dependent on how much disk space you have available. How much disk space do you feel is realistic or viable for the SQL Response database to require? Is disk space an issue for you at all?

We’d love to hear your views, so we can figure out how best to handle historical data, based on realistic use cases. Tell us what you do currently, and what you’d like to do, in an ideal world. Thanks for reading!

This entry was posted in Uncategorized. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

3 Comments

  1. Mike Correa
    Posted November 9, 2009 at 2:27 pm | Permalink

    The same key indicators for the performance of my servers ie uptime, # errors, etc. are used as performance indicators for my position as a DBA. I need to use server historical data going as far back as my last performance report, one year. Disk space is not an issue.

    Thank you, SR2 Team!

  2. Jonathan Allen
    Posted November 9, 2009 at 4:20 pm | Permalink

    Taking the view that to go through the data to compare now with “then” will take time/effort on what is probably a business server I guess it would be good to have configurable windows of past and current data. If this is possible then I could compare 1 month of data that was 12 months ago to the most recent month – eg today being 09-nov-09. I compare now to 12 months ago as historic data, 09-Oct-08 to 09-Nov-08 compared to 09-Oct-09 to 09-Nov-09. Alternately I could choose to compare this week with this week from last year or indeed any given period from archive to current. At least that way we are only reviewing/comparing small chunks of archives.

    This doesnt solve the storage issue unless we have some funky compression/backup solution. I could hopefully cope with a performance archive of up to 2GB (across whole enterprise, so for me 5 servers) without too much heartache but it would need some external archive feature (ie to DVD) so that I dont add this to my overnight tape backups.

    Regarding what to archive, can we simply use something like a filter on the current view to describe the alerts that should be stored for posterity?

    I dont see any reason to hold more than 12months archive as our applications are reviewed/upgraded frequently and making comparisons across versions is frought with problems. I only need compare now with previous, going beyond that is not a balanced comparison.

    Seeing trends on CPU, disk usage per sec, transactions per sec, user connections as so forth will assist a lot in abig way when reviewing need/specification of new hardware. (RAM usage is irrelevant, SQL just takes it all anyway :D )

    cheers

    Jonathan

  3. Brian.Harris
    Posted November 12, 2009 at 4:33 pm | Permalink

    Thanks Mike, and Jonathan. Your comments confirm what we suspected, that DBAs do want to keep historical data for at least a year, to allow for reporting and trending information; to determine any hardware requirements and also to provide information for management. Also, when you guys say “a year” you probably mean more like 13 or even 15 months, to allow for holidays etc?

    Our concern is that storing all the data we collect for a year could require terabytes of storage. We haven’t yet tested enough to provide ballpark figures of disk space needed to store monitoring data per server per day, but we suspect it may be quite large. Maintaining this sort of data for 12 months at its original level of granularity may make heavy demands on disk space. We’ve been discussing the possibility of data thinning (reducing the number of stored sample points to smooth out the data) but this could get complicated. Another approach is to allow you to automatically purge some data that you don’t need to keep – but again, this raises questions of whether you should be able to purge a specific channel of collected data, or everything on a particular server, or a mixture of both.

    Another possibility might be to allow you to capture snapshots for comparison purposes, for example a page of static data that does not need to access the database to generate its metrics, but stores them as a file somewhere. If you took a snapshot on Nov 12th 09 for example, you could then use it compare with Nov 12th 2010 next year. However, you would need to manually perform these captures – rewinding time may not take you back a full year if you have purged data, so this would be an alternative to that mechanism. Obviously, the problem with this method is that you’d need to know a year in advance what data you were going to want – would this be a problem for you?

    Regarding archiving of alerts – that’s an interesting question, and one we’re also considering. Identifying alerts to keep (equivalent to flagging an email for follow-up in some ways, I imagine) is a great suggestion, Jonathan. How about some general rule that could apply to a particular server – to delete alerts older than x days? The flagged alerts could be handled as exceptions to these rules?

    Great food for thought, as always guys. We’re always very grateful for your opinions.

Post a Comment

Required fields are marked *

Add an Image

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>