How to precompile stored procedures in SQL server?

Question

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!

Answer 1

Write a script that executes each stored procedure using "SET FMTONLY ON".

The procedure will be compiled, but no permanent changes will be made to the DB during execution. You can't use this with procedures that use temporary tables (#table syntax).

That's how Microsoft does it in Visual Studio to determine what the output of your stored procedure should be.

Answer 2

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.