Software application running slow - is it SQL??
-
HI. I run performance monitors daily on my sql server so in general I know such things as cpu usage, buffer cache hit ratio, avg. disk time, etc.. but when a programmer comes to me and says their application is running slow or a batch job is timing out.. what can i do at that time to see if sql is the cause or not? I don't run profiler on a regular basis so I would not have caught the transactions as they were coming across. Is it enough from the performance monitors that I should be able to determine if sql is the culprit or not?
Thanks so much !
Juanita
-
First, you can start the trace right away.
Second (and this is what I normally do): I open Enterprise Manager Current Activity Window , ask the app developer the user name of the connecting application user and the client machine name and then I am trying to find a line in Processes tab of Current Activity Window that corresponds the developer's process. Make sure the process is there at all! Means, they were able to connect and it is not a connection issue. After that I look at the last fields that show Blocked By and Blocking to see if there is a locking there. I check WAIT field to see if the process is waiting and all other fields showing how much resorces are allocated.
Regards,Yelena Varsha
-
HI.
Thank you so much !! I will give that a try !
Juanita
-
Here's my 'short list' for tuning:
Round I
DBCC UPDATEUSAGE
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round II
DBCC DBREINDEX
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round III
Profiler
Query Hints
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Thanks !!
Juanita
-
Rudy,
do we have to update the procedure cache after Update Statistics by DBCC FREEPROCCACHE? I tend to do that because I think if we updated statistics then everything has to be recompiled. Is sp_recompile a replacement for that or it does part of the job? I always had this question. Anyway, in both cases the first execution after both will be slow because the compliation will take place.
Regards,Yelena Varsha
-
Yelena,
sp_recompile is not a replacement, it and sp_refreshview simply marks the object and any SP that references that object will have it's plan recreated on the next execution in essence replacing that plan in procedure cache. DBCC FREEPROCCACHE might put a total damper on your server for a bit because it will empty out everything, possibly even cached plans that do not need updating.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply