MS SQL 2000 SP Query Plan

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.

-AdamAdam --

I honestly don't think that what you think is happening is actually
happening. I think what might be more realistic is that one of your
stored procedures has started scanning a table, or acquiring a
long-lived lock, causing the others to slow down. Or perhaps something
else is acquiring a lock, slowing up your procedures. 1:30 to
recompile a query plan is an absolutely enormous amount of time. Keep
in mind that the time that it takes to run your query may vary by the
inputs that are passed to it. Have you run SQL Server Profiler and run
a trace? Look for large amounts of reads and writes associated with
the long duration of your stored procedures.

-Dave|||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/defaul...b;EN-US;q308737.

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

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx