July 21, 2017 at 3:47 pm
Hi all,
I've been in the SQL space for a number of years now and recently been asked to act as a service assurance manager for our department's Database as a service.
I have been told to provide a high-level 'senior' management report detailing each months database performance. However, as all know performance is a huge subject and senior management are likely to question, for example, 'whats a wait type' etc... I also need to keep it to one high-level graph/pie chart etc..
I guess the question is....what metric/s would be suitable to demonstrate overall performance for that month, what is the best way to capture it and how to present it in an understandable format?
Any ideas welcome 🙂
Thanks, Phil
I should probably add some context here;
The infrastructure is hosted on physical hosts within our provider's data centre. These hosts are monitored by SCOM, however, we do not have access to these dashboards.
July 22, 2017 at 10:47 am
lassell - Friday, July 21, 2017 3:47 PMHi all,
I've been in the SQL space for a number of years now and recently been asked to act as a service assurance manager for our department's Database as a service.
I have been told to provide a high-level 'senior' management report detailing each months database performance. However, as all know performance is a huge subject and senior management are likely to question, for example, 'whats a wait type' etc... I also need to keep it to one high-level graph/pie chart etc..
I guess the question is....what metric/s would be suitable to demonstrate overall performance for that month, what is the best way to capture it and how to present it in an understandable format?Any ideas welcome 🙂
Thanks, PhilI should probably add some context here;
The infrastructure is hosted on physical hosts within our provider's data centre. These hosts are monitored by SCOM, however, we do not have access to these dashboards.
Personally I think this goes way beyond a free forum request. But perhaps you could read Redgate's free book on Performance Tuning with DMVs to get an idea of what metrics are valuable and how to store/report on them.
Paul Randall has a site that stores wait type details you could grep through.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 22, 2017 at 10:13 pm
Apologies if you feel this goes way beyond a free forum request but you have under miss-understood my request.
I am not after someone to providing me with a free answer, I have a full understanding of the different metrics I can collate for performance tuning, reporting etc I wanted to know if anyone had any ideas about how these metrics can be presented within one graph that a non-technical person would understand or relate to.
July 23, 2017 at 6:00 pm
lassell - Saturday, July 22, 2017 10:13 PMApologies if you feel this goes way beyond a free forum request but you have under miss-understood my request.I am not after someone to providing me with a free answer, I have a full understanding of the different metrics I can collate for performance tuning, reporting etc I wanted to know if anyone had any ideas about how these metrics can be presented within one graph that a non-technical person would understand or relate to.
In your case (high-level senior management) I don't think the graph type or visual is nearly as important as providing a basic, clearly (and shortly) worded statements of what is being observed - as well as at least what order of magnitude numbers represent "suboptimal or worse". Even a descending line where "smaller is better" could represent a HORRID situation, whereas an increasing chart could be awesome because it is two orders of magnitude away from a pain-point threshold.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 23, 2017 at 9:41 pm
lassell - Saturday, July 22, 2017 10:13 PMIn your case (high-level senior management) I don't think the graph type or visual is nearly as important as providing a basic, clearly (and shortly) worded statements of what is being observed - as well as at least what order of magnitude numbers represent "suboptimal or worse". Even a descending line where "smaller is better" could represent a HORRID situation, whereas an increasing chart could be awesome because it is two orders of magnitude away from a pain-point threshold.
Totaly agree. I need to keep this as simple and understandable as possible.
Thanks for allowing me to pick your brain.
July 25, 2017 at 5:00 am
Hi Lassel
Have you had a look at the reporting module within Redgate's SQL Monitor?
There is a quick overview video here https://youtu.be/mkpxRIJUnlY
Mark
July 25, 2017 at 9:42 am
Hello Phil,
I have to ask what value does this give to senior management? Does it give value to management to know wait times(which by the way are easily skewed by simple blocking such as a nightly index job blocking a simple query) are increasing or decreasing?
I would take a step back and ask what performance is meaningful to management? Application load times? Report run times? I would work towards tracking those and finding what is considered good and measuring perhaps your average load time and how often you meet your goal. So for example your average application screen load time is 2.3 seconds, but you meet your goal of 3 seconds 95% of the time. Those might be interesting to see and track over time. It's much more meaningful to know that customers have a good experience with your system then knowing your Buffer Page Life Expectancy is great or CPU usage is below 50%. Those kinds of metrics I would say are more tools to address performance issues as you have them and could if anything confuse management.
Also I would think up-time is pretty standard. If users can't access your system, they can't work. And tell those provider's to send you the SCOM data and/or reports! Your paying them after all!
Just my two cents.
Regards,
Stephan
SQL SSIS Reporting Database Engineer
July 31, 2017 at 4:17 pm
lassell - Friday, July 21, 2017 3:47 PMI have been told to provide a high-level 'senior' management report detailing each months database performance.
I think your first stop is to ask the person that told you this what they have in mind for this report. Reporting is all about the audience. Some 'senior' management wants a dashboard with traffic light indicators, others (accountants, specifically) want to see numbers.
If your requirements are truly wide open, I'd start simple and quick and be prepared to give them more detail if they ask for it.
If you are secure in your position, try making an intentionally horrible report to drive home the need for more detailed requirements. This could either be a 'data dump' of the raw data onto a screen or, my preference, a single green thumbs up image to indicate everything is good ( Dilbert ). I've found that helpful a couple times when I couldn't get a manager to give me any requirements beyond wanting 'a status report'. It can help to restart the conversation about what they really need to see and what they want to see.
Good luck.
Wes
(A solid design is always preferable to a creative workaround)
August 1, 2017 at 1:56 pm
lassell - Friday, July 21, 2017 3:47 PMHi all,
I've been in the SQL space for a number of years now and recently been asked to act as a service assurance manager for our department's Database as a service.
I have been told to provide a high-level 'senior' management report detailing each months database performance. However, as all know performance is a huge subject and senior management are likely to question, for example, 'whats a wait type' etc... I also need to keep it to one high-level graph/pie chart etc..
I guess the question is....what metric/s would be suitable to demonstrate overall performance for that month, what is the best way to capture it and how to present it in an understandable format?Any ideas welcome 🙂
Thanks, PhilI should probably add some context here;
The infrastructure is hosted on physical hosts within our provider's data centre. These hosts are monitored by SCOM, however, we do not have access to these dashboards.
If I were tasked with such a thing, you can certainly use something like RedGate's SQL Monitor but I'm also one that get's a certain amount of joy in building my own tools. I'd set up PerfMon to measure ...
Total CPU%
File Reads
File Writes
Cache Hit Ratio
Loc Blocks (a type of memory usage but not to be confused with how much memory is being used)
Processes blocked
User Connections
... and have it sample once per minute. That's only a little over 43 thousand rows of data per month. Sounds like a lot but once people see what you can do, they'll ask for more and you'll be able to provide more and nearly any desired resolution with little work at all.
Import that data into a table, aggregate it by hour (or whatever) and you'll be able to make a single line chart that shows the averages by hour of the day for the whole month. The imagination can take over from there.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply