Requirement for
Recompilation :
When
we create and execute a procedure it will compiled and create
execution plan for the present state of the database . If we do
changes into database structure, we should recompiling a procedure
updates and optimizes the procedure’s query plan for those changes.
This can improve the procedure’s processing performance.
Another
reason to stress a procedure to recompile is to retro-act the
"parameter sniffing". When SQL Server executes procedures,
any parameter values that are used by the procedure when it compiles
are connected as part of generating the query plan.
SQL
Server also do automatic recompiling of procedure whenever Server is
restarted.
3
ways to recompile manually :
1.)
Directly writing in procedure.
2.)
During execution of procedure.
3.)
Ad-hoc manner from query prompt.
Directly
writing in procedure.
Create
PROCEDURE
[dbo].[upTitle_Select]
@TitleID
int
= NULL,
@Title
varchar(5)
= NULL
WITH
RECOMPILE
AS
BEGIN
SET
NOCOUNT
ON;
DECLARE
@ErrorCode int;
SELECT
@ErrorCode =
@@ERROR;
.....
...
..
End
During
execution of procedure
exec
upTitle_Select
230,
'just Test'
WITH
RECOMPILE
Ad-hoc
manner for query prompt.
sp_recompile
'upTitle_Select'
Comments
Post a Comment
Plz dont forget to like Facebook Page..
https://www.facebook.com/pages/Sql-DBAcoin/523110684456757