How to precompile stored procedures on a SQL server?

Question

Good morning!

Is there a way to precompile stored procedures in SQL Server? My requirement is fulfilled as follows. I have several stored procedures that take longer to compile than to execute. Therefore, I want to precompile all stored procedures. It would be nice to precompile them when starting and starting the db server.

Any ideas on this will be very helpful!

Thank you and respect, Pavan.

Answer 1

you can force recompile, but this will not happen until the next start.

EXEC SP_RECOMPILE YourProcedureName

more details here ...

, :
CREATE PROCEDURE YourProcedureNameWITH RECOMPILE.....

:
EXEC YourProcedureName WITH RECOMPILE

SQL Server,

...

EDIT OP:

( ) ( , , , sniffing google: sql server) , , sql, , . , .

Answer 2

script, , "SET FMTONLY ON".

, . , (#table).

Microsoft Visual Studio, , .

Answer 3

SQL Server 2008, , , .

. , , , " OBJECT"

, , , , .

Answer 4

SP , - ? , , , SP .

Answer 5

(, , ), .

WITH RECOMPILE , . , ( , ), SP , , "SP, , . .

, , , . , , .

Answer 6

These days, compilation times are rare. Paste this code at the top of your program and see for yourself how long it takes to compile.

SET STATISTICS TIME ON
GO

You will find that compilation time is usually indicated in 0ms, which means too little to worry, while execution time is tens, hundreds or even thousands of milliseconds.