Query comes to a crawl until executed using the WITH RECOMPILE option

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Query comes to a crawl until executed using...

Query comes to a crawl until executed using the WITH RECOMPILE option

Rate Topic

Display Mode

Topic Options

Author

Message

dbx

Posted Tuesday, March 05, 2013 8:37 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 12:52 PM
Points: 3, Visits: 12

we have a stored procedure that will run just fine for a couple of hours but then somewhere during that time its execution plan seems to get confused and executing it then comes to crawl

when executing the stored procedure using the WITH RECOMPILE option we see that query begins executing at what we consider a normal amount of time again and will usually for a while, but eventually it slows down to a crawl again

currently, a simple fix for us is to leave the WITH RECOMPILE option in the stored procedure

however, we'd like to get a better understanding and come to an actual resolution if possible rather than this "workaround"

any insight is greatly appreciated, thanks in advance!

Post #1426868

SQLRNNR

Posted Tuesday, March 05, 2013 8:47 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 18,858, Visits: 12,443

Is the stored procedure that has many different parameters that could be passed in?

Do you see a lot of changes against the tables the procedure runs against?

What you are describing sounds like it could be that the statistics for the tables related to that query might need updated. It could also be that there is a parameter issue.

The recompile of that one procedure is a good first step in troubleshooting. Understanding the data and how the proc is used can help us to provide some better answers.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1426872

dbx

Posted Wednesday, March 06, 2013 7:56 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 12:52 PM
Points: 3, Visits: 12

thank you for the quick response

the stored procedure only takes 4 parameters, so i wouldn't say that's too many

the tables the procedure runs against have a lot of data that is added on a daily basis, the structure of the tables themselves however does not change

please let me know what other information would be helpful, i'll try and work up a basic example of what this procedure is doing when i have a chance

also, i've updated my settings so i get constant updates on when this thread is replied to so hopefully i'll be more prompt to replies

thanks again!

Post #1427426

Chris Harshman

Posted Wednesday, March 06, 2013 10:12 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:13 AM
Points: 1,573, Visits: 1,732

dbx (3/6/2013)

...the stored procedure only takes 4 parameters, so i wouldn't say that's too many...

The number of parameters isn't as much an issue as if any of the parameters are ever passed in the value NULL. Is the stored proc called the same way each time? I could see a situation where param_1 is passed in a value durring execution1 which matches an index on the table, but then later execution25 passes in NULL for param_1 and a value for param_2 which doesn't match an index or matches a different index, but the old execution plan is still cached so it's tryng to use an index which won't help it resolve the query. To get more specific we'd probably have to see the query of the stored proc.

Post #1427515

Brian.cs

Posted Wednesday, March 06, 2013 11:27 AM

Valued Member

Group: General Forum Members
Last Login: Monday, June 03, 2013 9:59 AM
Points: 59, Visits: 199

You may want to consider dummy variables to prevent parameter sniffing, that could explain what you're seeing. Here's one article about parameter sniffing:
http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx

Post #1427545

Van Heghe Eddy

Posted Sunday, March 10, 2013 8:19 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 6:29 AM
Points: 590, Visits: 733

dbx (3/6/2013)

the tables the procedure runs against have a lot of data that is added on a daily basis,
...

As the OP marked, with a whole lot of changes happening in the data on a daily basis (continious maybe),
It could well be that after some amount of time the statistics do not longer represent the actual data and therefore the cached execution plan is no longer the best possible.
I would recommend executing the SP manual when things are slowing down and check the execution plan to estimated row count and actual row counts.

If these are off then update the stats, if they are not it could well be a case of parameter problem.
Wkr,
Eddy

Post #1428983

Steven Willis

Posted Sunday, March 10, 2013 7:05 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:51 AM
Points: 298, Visits: 1,323

Try:


SELECT 
    is_auto_update_stats_on 
   ,is_auto_update_stats_async_on
FROM sys.databases

If auto update stats is on and the statistics get out of date the query optimizer will always wait for the statistics to be auto-updated and then do a recompile before execution of the query. If this is a large table that has had a lot of changes that can take a long time.

If you change the auto-statistics mode to asynchronous (the default is synchronous), the query will use the existing query plan (even a sub-optimal one) without recompilation even if the statistics are out of date. Of course then you must schedule a manual statistics update periodically. But that update can be scheduled to run before the scheduled execution of your offending procedure.

To turn on the auto updates:


ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS ON 
ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC ON

Post #1429050

« Prev Topic | Next Topic »

Permissions