Strange low performance during store procs execution
-
I have a big problem:
recently I have found out that some procedures that run fast one time ago (2-3 weeks ago, +- 3-4 secs) now they take more than 20 secs. The database is the same, I have tried to check deadlocks, rebuild index, updated database statistics, shrinked db, restarted the server but the time took to perform these procedures is the same.
I installed an istance of SQL server 2000 on a PC (I mean with very poor hardware) and store procs take +- 3-4 sec.
Anyone knows how to help me to find the problem/s that got down sql server performance.
What is the best strategy to investigate performance problem.
The server is production one with 1 xeon , raid 5.
thank.....
-
Sounds like the execution plan may have changed, do you have a set of input params that you know where being processed in the past in a reasonable time. If so it might be of benifit to recompile the sp and then pass the input params that you knew worked in n seconds and see if you can get sql to then use the correct query plan. Could be that the plan changed on a an unusual set of params that were passed in. If this turns out to be the case I would search this board for paramater sniffing and see ken Englands solution.
-
Solved with applying SP4
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply