How to precompile stored procedures in SQL server?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.