tags:

views:

1113

answers:

3

Hi,

Good morning !

Is there any way to pre compile stored procedures in SQL Server? My requirement goes like this.. I have some stored procedures, which take more time in compiling than executing. So I want to precompile all the stored procedures. It would be nice to precompile them when the db server is started and running.

Any ideas on this would be greatly helpful!

Thanks & Regards, Pavan.

+1  A: 

you can force a recompile, but it will not happen until the next time it is run

EXEC SP_RECOMPILE YourProcedureName

more info here...

force it to recompile each time it is run:
CREATE PROCEDURE YourProcedureNameWITH RECOMPILE .....

force it to recompile this time:
EXEC YourProcedureName WITH RECOMPILE

here is a good article on Optimizing SQL Server Stored Procedures to Avoid Recompiles

and another...

EDIT based on OP's comments:

why don't you manually run it (outside the application) with bogus data (not so bogus that the execution plan is bad though, google: sql server parameter spoofing and sniffing) the first time, that can force a compile, then run some sql to delete out what was inserted. when the users run it for the first time, it will have already run and have been compiled.

KM
I tried using sp_Recompile option. But when the sp is executed for first time, it takes a lot of time, and later on it is quick enough. How can i improve it for first time use?
Pavan
+1  A: 

If you are using SQL Server 2008 then you may be able to use a Plan Guide in order to enforce the re-use of an existing, pre compiled, Execution Plan.

See Understanding Plan Guides, for more details and in particular read "OBJECT Plan Guides"

I suspect however that the source of your issue is the process logic being implemented within your stored procedure and would suggest this as your first point of review for performance tuning.

John Sansom
Hi, I am using Sql Server 2005, and in the stored procedure contains a plain insert statement. Index views exists on the table. So preparing execution plan is consuming time. Any idea how we can prepare all this ahead and keep sp ready for execution?
Pavan
Hi, in order to help with my understanding of your environment, Why are you using indexed views rather than adding indexes directly to the underlying table? How many indexed views are you using? How many indexes are there on the table?
John Sansom
This realy sounds like the problem. Indexed Views are often misunderstood as just another index. But they realy create a phyiscal representation of the view. If you insert into a table that is part of an indexed view you are asking for trouble.If this is part of a regular import routine, then before importing disable the index. After import enable again.
Malcolm Frexner
A: 

A stored procedure should only compile (and for that matter only create a query plan) when it is created and first executed.

If you are using WITH RECOMPILE a lot, you should stop. If you're doing that to force recalculation of query plans because different parameters work more efficiently with different query plans (and if that matters, performance-wise) then you need to consider creating different SPs for the different query plans, perhaps with a "parent" SP to decide which to call. But it's not a pain-free exercise.

If your tables are really in the sub-million row category then I'd look most carefully at indexing and keeping statistics up to date, recompiling periodically at quiet times to keep the query plans efficient. Once you're into tens or hundreds of millions of rows then there may be a case for going through the pain of duplication.

Mike Woodhouse