Monthly Metric Report
-
My EVP has decided that he needs Monthly Metrics for all of our systems. Providing various business metrics is not the issue. He is also asking me to provide monthly metrics on the health of our servers.
Daily I run reports to check various aspects of our servers: disk space, job histories, backup histories, fragmentation and the like. However, I am drawing a blank trying to come up with a meaning metric on a monthly basis that is suitable for an Executive Level report. So, I thought I would turn to you all for suggestions.
Thanks in advance,
Gordon
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
-
It seems to me that the Executives would like to see availability, backup history, job history, and maybe some performance stats. If I were an executive, I'd at least want to know availability, backup history, and job history.
-
Deadlocks are one of the most important but most people seem to have no clue about it and it doesn't turn on automatically...
Lookup "deadlocks, troubleshooting" for more on deadlocks and the all important Trace Flag 1204.
Zero deadlocks per day should be your target. When I first started with my current company, we had an average of 640 deadlocks per day with spikes at 4000!!!! After some pretty tough investigation and instituting a code review process, we're down to an average of 4-5 per week. It also helped overall performance... many 8 hour jobs (frequently failed) now run in 10 minutes or less.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
p.s. Deadlocks are so important, that the DBA(s) should monitor them daily and report on them weekly. They should also assist in trying to figure out where the heck they come from. If you have more than a very small handfull of deadlocks per week (4-5), you have some pretty nasty problems and your company needs to dedicate some serious resources to fixing them as well as identifying a plan to keep them from happening again in the future. That usually means peer reviews, code reviews, and DBA reviews. You will be amazed at the performance gain your procs and views realize once the correct process is in place.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Deadlocks and such things are important at operating level, but not at executive level.
What execs want to see is how business is going on, do they need to plan an investment, is there any danger and so on.
So as a monthly mertics I would put some data/graphs about some performance, avalability, SLA figures such as AVG response time, disk space grow, available disk space, AVG disk que length, processor, memory usage, outages, planned and not planned maintenances, critical errors etc.
I would not put there and operational data, like fragmentation, deadlocks, backups etc. because those are the all day tasks of a DBA. There job is to eliminate such issues and maintan the database.
Bye
Gabor -
Deadlocks ARE "critical errors"!!!
Deadlocks are lost data (an excessive waits) and are a key measure to the health of an SQL based system. Perhaps most companies have executives that are simply oblivious to the effect of deadlocks on their business, but that does not lessen the significance of producing a deadlock report. It is a key measure of the relative health of the code (and the data) and someone with the ability to assign resources to a problem needs to immediately assign those resources if deadlocks occur. That may be at the executive level or not but the report must exist and someone must do something about it.
A side benefit will be that once someone starts paying serious attention to deadlocks (and actually doing something about it), ESPECIALLY at the executive level, the other metrics of performance, availablity, average response time, processor, memory usage, and critical errors will improve significantly.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff - you mention producing a deadlock report. Excuse my ignorance, but how would one go about producing such a report?
Thnx,
BP
-
My DBA is the one who set it up so I'm not sure... but I'll find out...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hello,
I found this post very interesting as I'm in the process of creating a report myself on the health of our SQL Servers. I wanted to check to see if there was any follow up information on how one would go about producing a deadlocking report? I think that would be a great addition to my report as we have been running into a few of those lately.
Thanks in advance for any advice on how to go about doing this.
Barbara
-
I to an coming up with a monthly measure of for our production servers. We are planning on measuring availability. To do this we plan to use my monitoring processes (see http://www.databasejournal.com/features/mssql/article.php/3622746 for information on this monitoring process). Basically we are going to have a network attached machine that checks every ten minutes to see if our production machines are responding. If they are available we increment a counter in a table in a database. So at the end of the month we can determine the value of the counter, and divide it by the total number possible, to give us an idea of how available our server was during the month. Our goal is to shut for 99.9 % availability.
Gregory A. Larsen, MVP
-
Sorry... dropped the ball on this one... I'll try to remember this one when I get to work.
Just for fun, lemme share what I recently heard in the hallway from an IT manager...
"Our goal is to have 100% up time except when the sever is down."
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
"Our goal is to have 100% up time except when the sever is down."
I wish my management had that goal.
Gregory A. Larsen, MVP
-
"I wish my management had that goal."
Ditto
No rush Jeff. Whenever you get the time that would be great.
-BP
-
Ok... here's what our DBA's do (I don't like it and can think of at least one way to automate)... they do a "WinGrep" on the SQL Server log looking for the header of a deadlock. They manually count those and enter it into a spreadsheet. How stupid is that. It works but it's stupid in the light of automation. Even a simple bulk insert would be better. I'm truly disappointed in how they do this. Not sure I have the time but I've gotta make a better way to do this important task.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff- Thank you for providing the information, and for the chuckle:
"Our goal is to have 100% up time except when the sever is down." I wish my management had that goal.
I appreciate both
Barbara
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply