Monitoring SQL Server Systems
-
I am curious, how many DBA out there actually run performance monitor against their SQL Server systems on a daily basis, and if you do, do you actually sit there for hours watching it.
-
I'm not sure what they did, but our DBA's setup a system of alerts that sends them email on their "crack berry" if a CPU spikes for more than a minute or certain thresholds are met on the I/O systems or if more than 90% of memory is used for more than a minute. THEN they might turn on performance monitor and watch it for a minute or two... chances are, they don't bother then... they're too busy looking at the SPID(s) that caused the problem and what the query was for each...
--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) -
Idera's Diagnostic Manager is excellent for this - keeps track of all sorts of good information, and, YES, you can sit and watch it for hours!!!
-
But would you watch it for hours every day?
-
Run Performance Logs. Then analyze them once in a while
Regards,Yelena Varsha
-
Hi Lynn,
We just recently bought Idera's SQL Diagnostic Monitor. It is a fantastic tool. I do not watch all day but glance at it periodically. I, like one of the other replies, have alerts and emails mailed to me if anything is not on the up and up. Before this, I did run performance monitor on all the critical counters and would glance at the chart periodically during the day.
Juanita Drobish
-
A little honesty, I'm not the one the one at our shop doing this. One of my coworkers claims that it takes 4 to 6 hours daily to monitor one of the SQL Server systems. My boss and I wonder if this is normal and we are just abnormal as we would only look at the system if there was a report of a problem or if we had alerts setup to notify us when a problem occurred.
-
Pet peeve... maybe a rant... (please forgive the forthcoming soapbox)... consider this carefully... no matter which "performance monitoring" tool you use, what good does it do you if you find a performance problem when the only people you have to fix the performance problem are the people who wrote the original problem into the code in the first place?
Although I absolutely agree that some basic performance monitoring of the system and the database is essential, I'm sure that most of you would agree that finding a problem with code that way is a bit like closing the gate after the horse got out. Finding out that a production batch job really takes 8 to 12 hours to run instead of the 10 minutes that everyone thought it would is a real wakeup call. Finding out that a new production batch job locks up the server for 10 minutes 4 times a day usually causes a head or two to roll. Finding out that you have a 10 second web latency when only 20 users login, usually causes many people much pain into the wee hours of the morning.
I think that companies spend way too much money on slick performance monitoring "toys" and not enough on good solid Developers and Analysts (GUI, Backend, or combined) that know how to design/write/unit-test performance enabled, highly scalable code nor do they give them enough time to just "do it right". Even if you have magic performance monitoring tools that say things like "the following code caused the problem because it contains a cursor/triangular-join/unnecessary-correlated-subquery/whatever in it", ya gotta have someone that can fix it... the catch 22 usually is, if you did have someone to fix it, it wouldn't have been written to cause a problem in the first place.
I've also found that simple things, like reviews of the code by Peers and DBAs, some good QA testing, and a little performance/scalability testing, go a very long way to keep your performance monitors from paging you at both 2 in the morning and 2 in the afternoon.
Bottom line... if you can get Management to buy into performance monitoring tools, you should also get them to buy into problem prevention... which costs more? ... Doing it right the first time, or monitoring it and reworking it every time it becomes a problem?
Don't kill me folks... I just had to get that off my chest
--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) -
Lynn,
Sorry, got a bit distracted... I think your co-worker is spending way too much time monitoring performance. HOWEVER, I've not had to walk in his/her shoes... it may be that he/she really means that that much time is spent trying to figure out why the system is running so poorly (if it is running poorly). I think your boss should ask that person what a typical day consists of... maybe in the form of performance/blocking/deadlock/IO graphs... your boss will either be greatly enlightened by a dedicated individual or truly discouraged by a consumate slacker. With that much time reportedly being spent on performance monitoring on a single system, there is likely no in-between...
--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,
I can see your point but I prefer to monitor the instances using one of the graphical third-party tools because they really do make it easy to determine where problems may exist in the system.
You are indeed correct about how a business should hire competent programmers to do the job right in the first place but there are a lot of programmers who aren't and the tools give the confirmation to back me up when the games of office politics start over who is at fault when the system is moving like molasses on a winters day. I realise this flies in the face of good customer service but sometimes the blame has just got to be put where it belongs.
I've been using Quest Spotlight and while I think it's great and that it's much easier to read where problems may exist over that of the display of Idera's Diagnostic Manager, DM does have some functionality that is much better in some ways.
A lack of planning on your part does not constitute an emergency on mine.
-
Spot-on on all points, Matt. And, thanks for the reminder... I forgot that such tools just short office politics right straight to ground.
--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,.
I will have to go with the later statement regarding the co-worker. The application for which the SQL Server is dedicated to is a 3rd party app. Yes, at times the system experiences deadlocks or run-a-way queries due to ad hoc queries by some users, but even with the "constant" monitoring, these problems aren't identified unless someone complains about the system being down or extremely slow.
My boss has asked what a typical day in the life of is, and we have discussed it several times and find it hard to believe. I help support PeopleSoft from just the SQL side, and the only time I have fired up perfmon or SQL's DTA on a trace was when a problem was reported and we were trying to determine why there was a problem.
-
Lynn,
Too bad about that person. We've still got one like that, too. Dunno why they keep him around.
Say, that 3rd party app wouldn't happen to be a product called "Cost Guard", would it? If it is, I have a fix for the deadlock issue...
--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) -
Thanks, Jeff
If there is anything that needs to be stamped on FAST - it is office politics and the clowns who will neglect their duties so that they can score points over someone else. Unfortunately for my career in the past, these people always thought that game-playing in the office had to be by their rules - when they came across someone who didn't see the sport in it and offered to throw their useless carcass down the fire escape, they got a bit scared and really started making trouble for me
At least I'm out of there now....
A lack of planning on your part does not constitute an emergency on mine.
-
Jeff,
Nope, it isn't. It happens to be our Student Information System (I work for a K-12 School District).
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply