Options
Best Means to see older performance data
OrientalHero
Posts: 8 Bronze 1
Our Business Unit has requested performance information from our last peak period which was Black Friday/Weekend in Nov last year. We don't have it in SQL Monitor because the purge is set to 3 months (on the whole).
So we have just passed another peak performance period.
How do I "persist" this performance data for the next time the business unit requests it? It might be 3 weeks ago, might be 1 year depending on the timing as we have several Peak performance periods in any year.
So far I know I can
1) Increase the data purge limits to 1year or even do not purge at all.
2) Export the analysis charts to CSV (to be consumed via Excel and some simple charting)
3) Generate some screenshots of existing Analysis sets
Unfortunately each has some cons.
1) We're quite busy - 25 servers generate some 500GB of RedGateMonitor database for default 3 month settings-
What effect on the database response when it has 1 year, this being 2TB in size?
2) Ideally this export to CSV will be an automated process. You can save the URL once your relevant metrics are chosen/displayed, but exporting to CSV means you have a weak manual human driven process failure point.
3) Similar to the above and weaker, in that scenarios requiring troubleshooting are atypical, so one fixed picture is harder to compare to the next situation.
Oh and is the last "hail mary" option viable where you make database backups, restore elsewhere, and point another installation of SQL monitor at this to get the "comparative" baseline for a period beyond your data purge limit?
It would be nice to get some input on how other people and RedGate see the long term usage of SQL Monitor to get comparisons.
My company's business has several peak performance periods in any year, but I would imagine most industries only have 1 or 2, so would need to consider the longer term comparisons.
Thanks for your attention and hope that some ppl get something out of the various options above (like saving URLs for a particular set of metrics).
So we have just passed another peak performance period.
How do I "persist" this performance data for the next time the business unit requests it? It might be 3 weeks ago, might be 1 year depending on the timing as we have several Peak performance periods in any year.
So far I know I can
1) Increase the data purge limits to 1year or even do not purge at all.
2) Export the analysis charts to CSV (to be consumed via Excel and some simple charting)
3) Generate some screenshots of existing Analysis sets
Unfortunately each has some cons.
1) We're quite busy - 25 servers generate some 500GB of RedGateMonitor database for default 3 month settings-
What effect on the database response when it has 1 year, this being 2TB in size?
2) Ideally this export to CSV will be an automated process. You can save the URL once your relevant metrics are chosen/displayed, but exporting to CSV means you have a weak manual human driven process failure point.
3) Similar to the above and weaker, in that scenarios requiring troubleshooting are atypical, so one fixed picture is harder to compare to the next situation.
Oh and is the last "hail mary" option viable where you make database backups, restore elsewhere, and point another installation of SQL monitor at this to get the "comparative" baseline for a period beyond your data purge limit?
It would be nice to get some input on how other people and RedGate see the long term usage of SQL Monitor to get comparisons.
My company's business has several peak performance periods in any year, but I would imagine most industries only have 1 or 2, so would need to consider the longer term comparisons.
Thanks for your attention and hope that some ppl get something out of the various options above (like saving URLs for a particular set of metrics).
Comments
SQL Monitor is aimed more towards diagnosing current issues and trends in the performance of your SQL Server estate than comparing historical contexts as you describe. It's more about making the day to day easier for the DBA - just see the product page and the Simple Talk articles on it.
Having said that there is the capability to do this and the options you describe are the options I would list. Ideally if you need to have data from 6 months or a year back then you would just set the purge settings to keep all of the data. The team have been streamlining SQL Monitor to use less resource, including database size so it may not take up as much as you think in the end (though it would need the data to purge the existing larger amounts of data and run a shrink to see a difference I suppose).
The last "Hail Mary" option you mention is feasible as well. In this instance you install SQL Monitor version equal to the version when the backup was taken (just to avoid any issues in upgrading the schema), stop the services and restore the backup to the database then restart the services and at this point set the data purge setting to 'Do not purge'. You may also want to suspend monitoring on all the servers to avoid gathering double data.
I suppose, in theory, if you were only interested in comparing the peak periods you could start a new data repository and set it to not purge and then have it monitor during the peak period and then stop the SQL Monitor services so no further data was captured between times and just do this for each peak period.
I hope that helps!
Kind regards,
Alex
Have you visited our Help Center?