question

OTPS avatar image
OTPS 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.
sql-server-2005performance-tuningcursor
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Did the increase in time taken to run directly coincide with the date/time of modification? If so, then what was the modification?
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
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.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
My only concern is the execution plan for a 4000 line sproc is going to be unreadable. Break it down and analyse each part of the sproc is going to be easier and more sensible IMO
1 Like 1 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
+1 because you added all the bits I was typing :)
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
I agree with you williamD (but I have seen worse) :)
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
***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?
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
+1 because you had exactly the same reaction as me!
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
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.
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered
> 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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered
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?
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image Blackhawk-17 commented ·
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?
0 Likes 0 ·
goyo avatar image
goyo answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mark avatar image
Mark answered
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?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

OTPS avatar image
OTPS answered
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!"
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Massive deletes / inserts / updates? Are these within the SP? If so, it might be worth "chunking" the data...
2 Likes 2 ·
goyo avatar image
goyo answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
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?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.