Posted by Erland Sommarskog on 02/04/06 17:26

Adam Rogas (adam.rogas@gmail.com) writes:
> I have a couple of complex stored procedures that work well and quickly
> once they have compiled. The problem I am running into is that every
> once in a while they want to refresh thier execution plans, and when
> that happens it takes about 1 minute and 30 seconds for them to
> rebuild, well of course my application is set up to time out commands
> after 30 seconds so basicly the stored procedure never completes and
> hangs up all of my subsequent stored procdures.
>
> I have tried to use
>
> OPTION KEEP FIXEDPLAN
>
> on all of my select statments but I was wondering what else could be
> done to stop a stored procedure from it's need to rebuild.

As Dave says, 1½ minute for a recompilation is a very long time. There
is all reason to reinvestigate whether the diagnosis is correct. There
could be several other reasons for such stalls.

One way to test this is to run a copy of a procedure with a different
name from Query Analyzer, in this fashion:

CREATE PROCEDURE alternate_name AS ...
go
DECLARE @d datetime
SELECT @d = getdate()
EXEC alternate_name ...
PRINT 'First run took ' + ltrim(str(datediff(ms, @d, getdate())
go
DECLARE @d datetime
SELECT @d = getdate()
EXEC alternate_name ...
PRINT 'Second run took ' + ltrim(str(datediff(ms, @d, getdate())
go
EXEC sp_recompile alternate_name ...
go
DECLARE @d datetime
SELECT @d = getdate()
EXEC alternate_name ...
PRINT 'Third run took ' + ltrim(str(datediff(ms, @d, getdate())

In the first run, there is no plan in csche, so the procedure will
be compiled at least once. Data may or may not be in cache. In the
second run, plan and data is in cache. In the third run, data is still
in cache, but the procedure will be compiled again. Thus, you should
compare the second and third runs.

The biggest procedure in our system is 3000 lines of code. It takes
about 8 seconds to compile. I've seen that queries with very long
IN lists (SELECT ... FROM tbl WHERE col IN (...)) with over 15000
elements can take up to 15 seconds to compile. That is still a far
cry from 90 seconds.

If you indeed have recompilation problems, you need to analyse what the
causes are. The SP:Recompile event populates the EventSubClass column,
values are documented here:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q308737.

The most likely reason is changed statistics. This white paper may give
guidance in such case:
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]