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.
more ▼

asked Aug 10 '10 at 07:01 AM in Default

OTPS gravatar image

11 1 1 1

Did the increase in time taken to run directly coincide with the date/time of modification? If so, then what was the modification?
Aug 10 '10 at 07:45 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

11 answers: sort voted first

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.
more ▼

answered Aug 10 '10 at 08:44 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.7k 19 21 74

(comments are locked)
10|1200 characters needed characters left

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?

more ▼

answered Aug 10 '10 at 08:43 AM

Mark gravatar image

2.6k 21 25 27

(comments are locked)
10|1200 characters needed characters left

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!"

more ▼

answered Aug 10 '10 at 09:29 AM

OTPS gravatar image

11 1 1 1

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)
10|1200 characters needed characters left
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.
more ▼

answered Aug 10 '10 at 12:53 PM

goyo gravatar image

41 1 1 2

(comments are locked)
10|1200 characters needed characters left

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?
more ▼

answered Aug 10 '10 at 01:15 PM

TimothyAWiseman gravatar image

15.5k 19 22 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 10 '10 at 07:01 AM

Seen: 1859 times

Last Updated: Aug 10 '10 at 07:16 AM