|
We have a procedure (SQL 2005) that has recently been modified and it suddenly went from an hour of processing to 15 hours of processing. I'm not sure where to look to narrow down where the extra hit is coming from. I have looked a Profiler, but I didn't see anything obvious. (Translate as maybe doesn't know how to best use Profiler?) My next step will be to look at the execution plan but how do I get the plan without running this proc for another 15 hours? Any suggestions? P.S. The proc is about 4000 lines long and involves an inner and outer cursor. I didn't want to subject anyone to that.
(comments are locked)
|
|
Yes, you can query the DMV's directly for the execution plans that are still in cache. That's the key though. The DMV's will only show the plans that are in the cache. With a 4000 line query, you're going to have to try to figure out multiple plans and which ones are applicable to your query. The DMV's will work with 2005 and up. Still, sounds like the cursors are the biggest issue.
(comments are locked)
|
|
Good answers here as usual. But I only want to add one more question: are the cursors and temp tables being dropped when they are no longer needed?
(comments are locked)
|
|
Welcome to my pain. I did not write the proc and have begged them to let me re-write it in set-based format with no luck. These were not SQL people who wrote it. I'm going to answer these in the order received: Matt - changes were specific and necessary, statistics are fresh and indexing was re-tuned. I already took this proc from 2 hours down to 45 minutes with minor changes and indexing. Hakan - Other than reading through the code and trying to cover all the WHERE clauses with indexes, I can't see where the problem is piling up. William - Now you know how I feel. I'm trying to break up the proc, but I have to be REAL careful because of the @!#% cursors. Thomas - My pharmacy is already throwing a party on the money I'm spending on anti-anxiety drugs. BlackHawk - Good call. I/O is killing me already. Did I mention non-SQL people being involved? There is not any traffic besides me. The massive Deletes and then Updates and Inserts are enough! Goyo - Thanks for the tip. I'll look. Mark - Yes, I made sure all cursors were deallocated and all temp tables dropped. Grant - You're right. Cursors like these were designed by sadists. Good answers by everyone. I'm trying to break up the proc now. Pray for me. OTPS - "Only The Paranoid Survive!" Massive deletes / inserts / updates? Are these within the SP? If so, it might be worth "chunking" the data...
Aug 10 '10 at 10:22 AM
ThomasRushton ♦
(comments are locked)
|
As my bother used to chant, making a sign of the cross with hand extended, "I betcha my father can beat your father at dominos." Good luck.
(comments are locked)
|
|
There are lots of good suggestions, but in addition to those, have you looked at the server as a whole? Have any new demands been placed on it? Is there a spike on the number of users? On a related note, could there be a locking/blocking issue causing problems?
(comments are locked)
|
Did the increase in time taken to run directly coincide with the date/time of modification? If so, then what was the modification?