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, 2010 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, 2010 at 07:45 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

11 answers: sort voted first

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

answered Aug 10, 2010 at 07:08 AM

Håkan Winther gravatar image

Håkan Winther
15.7k 35 37 48

+1 because you added all the bits I was typing :)
Aug 10, 2010 at 07:19 AM Matt Whitfield ♦♦
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
Aug 10, 2010 at 07:24 AM WilliamD
I agree with you williamD (but I have seen worse) :)
Aug 10, 2010 at 10:25 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

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

answered Aug 10, 2010 at 07:18 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

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

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

answered Aug 10, 2010 at 07:10 AM

WilliamD gravatar image

25.9k 17 19 41

+1 because you had exactly the same reaction as me!
Aug 10, 2010 at 07:19 AM Matt Whitfield ♦♦
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.
Aug 10, 2010 at 07:45 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

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

answered Aug 10, 2010 at 07:26 AM

Blackhawk-17 gravatar image

11.9k 28 31 37

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?
Aug 10, 2010 at 01:56 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

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

answered Aug 10, 2010 at 08:35 AM

goyo gravatar image

40 1 1 2

(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, 2010 at 07:01 AM

Seen: 2253 times

Last Updated: Aug 10, 2010 at 07:16 AM