Database monitoring tool(s) needed!
-
Today, on our main sql2k production box the CPU utilization jumped up to about 80% - solid. SqlServer.exe accounted for about 90% of the usage. I checked for blocking in the "usual suspect" databases but didn't find any. I looked at EM, Process ID, but couldn't really gain much info from the "CPU" column.
What product(s) can you recommend to give me a better idea where the trouble is? Can it be pinpointed to a particular database, a particular user, an application?
TIA,
Bill
P.S. We had all the users log out of the front-end system and the CPU utilization dropped way down to almost 0, including sql. But that doesn't make me feel any better when they log back on!
-
Have a look through this Powerpoint presentation from a meeting we had last night.
http://www.sqlserver.org.au/resources/ViewResource.aspx?resourceId=33
--------------------
Colt 45 - the original point and click interface
-
Well you could look at your indexing, I've just done an indexing check on one of my prod databases and adding a dozen indexes dropped the cpu by over 50%.
One of the tools I used was sql monitor from http://www.teratrax.com .. you can get a free demo ( fully functional ) it will list all your sql by i/o ( and by cpu ) - check your i/o high sql vs indexes, add indexes , watch cpu drop.
It's a neat tool ( teratrax ) but not quite functional enough for my needs. - but it is cheap I'd highly recommend it for viewing sql by cpu and i/o
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
TIA,
Think of introducing MS MOM (on a dedicated server)
or
Try SPOTLIGHT on SQLserver from Quest.
GKramer
The Netherlands
-
I use a tool from Idera called SqlCheck.
It's free, and gives more information than the tetrax offering above which costs $149
http://www.idera.com/Products/?s=sc
-
I've used SQLCheck and for the price, it's great. I found it to be a [bit] doggy as far as data refreshes but that could have been as a result of my workstation typically having about 30 open apps at the same time
On the problem at hand, run SQL Profiler for about 60 seconds. Get T-SQL only but grab [everything] happening. I agree with Colin on the indexing suggestion - almost every time I checked on a similar situation on my server, it was lacking an index (the structure of this particular db was very dynamic with tables coming and going constantly). Believe it or not, I actually wrote a server-side trace stored proc to identify the tables requiring indexes and targeting the columns needing an index as part of the alert.
Yeah, I spoil the programmers...
-
A couple of us use DBArtisan from Embarcadero. The built-in performance analyst monitor works real well.
-
Profiler
duration over 1500ms should find slow running procs or querys.-- George.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply