asked
Sp suddenly taking WAAAY too long
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.
Did the increase in time taken to run directly coincide with the date/time of modification? If so, then what was the modification?
Take a look at the estimated execution plan, try to find any types of scans. In Management studio you can use shortcut key CTRL + L You should also beware of this: - a procedure with 4000 lines of code is not very efficient. The query optimizer may have issues when the procedure is compliled. Try to break the procedure into smaller parts. - Cursors are not very efficient in SQL server. Try to use a SET based approach. - Statistics that is out of date may cause an incorrect execution plan - Indexes that are fragmented may cause even worse performance issues. - Indexes that are missing or incorrect causes scans (table or index scans) Incorrect indexes may be caused by: - Index not selective enough (more data added) - query doesn't include a filter on the first column in the index. (make sure you are using filter on columns that is specified in the index, from left to right. If you don't use a filter on the last column in an index may be okey, but not the first column) - The index used is not covering and SQL need to use a key lookup to get the rest of the data from clustered index.
***4000 lines & Cursors! That sounds like your problem.*** But to get you started: You say the sproc has recently been modified, this is the first place to start. What has changed? Have you changed table structure? Added lots of code to the sproc? Added lots more data to the underlying tables? Added any triggers to tables that are now slowing things down? The best way to go is to take the sproc apart, step by step and see what is going on (look at the execution plans for each part separately). If you can remove the cursors that will help - SQL Server was not designed for cursors and can do most things without them. What is your sproc doing in those 4000 lines, if you don't mind me asking?
My reaction is worse. I think I need a lie-down from just imagining this SP... I'll leave this with you guys, as you're already way ahead of me.
> involves an inner and outer cursor That says it all, pretty much. Ok, so it's started to take a lot longer - so that's going to be something related to your recent changes. However, it's pretty likely that you will be able to get that procedure running much much much quicker if it was written in a set based way. I accept that you're probably not going to re-write a 4000 line proc, so other things you can try: * Review the changes to make sure it wasn't something silly * Have a look at the estimated execution plan to check nothing shows up * Check that the statistics on the referenced tables are up to date * Check that the indexing on the referenced tables is appropriate
Was there additional logging added to the SPROC? Any additional I/O is exponentially bothersome within cursors. Has blocking increased? Are there multiple transactions in the SPROC or is it all wrapped up as one big one?
Based on what has been said it looks like you need to alter your SPROC to tackle, say, 1/10th the data at a time and run it more. What kind of inputs/outputs are used with the SPROC?
Consider using some diagnostic DMVs. Glenn Berry has [some here for SQL 2008][1], and I am sure there are more on his site for 2005. [1]:
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!6874.entry
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?
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.
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...
Pray for me. 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.
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?
