???????? ????????? recompilation ?????? ??????

???? ?????? ???? ??????
???? ID: 243586 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

??????

?? ???? Microsoft SQL Server ?? ??? ??????????? ?? ????? ?? ???? ?? ?? ??????????? ?????? ?? ?? ??????? ?????? ?? ??? deals: ???????? ???????????? ?? ??-???? recompilation. ??? ???? ???????? ?????? ?????? ???, ????? ?? ?? ??? ????????? ???? ?? ???? ?????? ?? precise ????? ??, ????? ???? ?? Microsoft ???????? ??? ??? ???? ?? ???? ?? ??? ??????:

224587TO HOW: ???????????? SQL ????? ?? ??? ????????? ????????
?? ???? ????? ?? ?? ?? ???? ?????? ?? ????? ??? ????? ???? ?? ??? ?? ???? ????? ???? ???? ??, ?? ?? ?? ?? ????? ??????? ?? ???? ??????? ??? ?? ??????? ?? ??? ???? SQL Server Profiler ????? ?????? ???? ??? ???

???? ???????

?? ??? ?????????? ??? ???????? ????????? ????????? ???? ?? ??? ?? ?????? ???? ?? ???? ?? ?? ??? ???, SQL ????? ????????? ?? ??? ???? ?? ?? ??? ?????? ????? compiles ??? ?????? ????? ??? ??? ????? ??? ??, ?? ???????? ????????? ?? ?????? ??? ???? ????? ??? ?? reused ?? ?? ?? ?? ????? ?????? ?? ?? recompilation ?? ????? ???? ?? ??? ??? ?????? ???? ??? ????? ???????? ?? ??? ???????? ????????? ????? recompilation ?? ???? ?? ???? ??:
  • ????????? ?? ????? ?? EXECUTE ??? ??? ?? ??? RECOMPILE ??? ?? ????? ?????
  • ?????? ?????? ?? ??????, ???????, ?? ???? ?? ?????? ???? ???????? ???????? ??? ?? ???? ?? ????????? ???? ???
  • ?? ???sp_recompile???? ?????? ????????? ?? ?????? ???????? ?? ?? ????
  • ?????? ????????? ?? ???????? ?? ??? ?? ???? ??????? ?? ???????????? ????????? ??? (??? ?? ?????-??????? ???????? ?? ??? ???)?
  • ???????? ????? ??????? ?? ??? aged ???? ?? ??? ????? ???? ?? ??? ???
??? ???????? ????????? recompiling ?? ??? ?? ??? ?????? ?? ?????? ????????? ??? ????? ??, ?? ?? ???????? ???? ?? ????????? ???? ???? ?? ???? recompile ???? ?? ??? ????? ?? ???? ??? ??? SQL Server 7.0, ?? ??? ??????? ???????? ?? ?? ???????? ?? ????? recompile ???? ?? ??? ??? ???????? ????????? ?? ???? ?? ???? ??? ?? ?? ??????? ????????? ???? ?? ?? ??????????? ????? ??? ???? ????? ???? ????? ?? ????????? ??? ??????? ??? ???????? ?? ??? ??? ???? ???????? ????????? ?? ?? ??-???? recompilation ????? ????? ?? ???? ?? ???? ??:
  • ???????? ????????? ?????? ???????? ?? ?? ???? ?????? ??? ???? ???????? ???? ?? ???????? ????????
  • ????????? interleaves ???? ???????? ???? (DDL) ?? ???? Manipulation ???? (DML) ???????? ???
  • ????????? ??????? ???? ?? ??? ???????? ?? ????????? ???? ???
?? ?????? ?? ?? ???? ??? ??? ????? ??? discussed ???

??? ?????? ??? ???????? ????????? recompiling ?? ???? ?? ???? ?? ???, ??????? ??? ???????????? ?? ??? ?? ?????????? ???? ?? ??? recompilation ?????? ??, ?? ??? ???? ?? ??? ???? ?????????? ???????????? ?? ????????? recompiled ??? ???? ???? ?? ?? ?? ????? ?????? ??????? ????????? ?? ??? ?? ???? ?? ??? ???? ???????? ??? ?? ???? ?? ???? ??? ???? ??????? ?? ??? SQL Server ???????? ?????? ??? "Transact SQL ?????????" ???? ?? ??????


?? ???? ??? ????? ??????? ???????? ????????? ??-???? recompilations ?? ???? ????? ?? focuses ?? ?????? ????? ?? ??? ????? ?? ???? ??? ??????? ?? ???? ??? ????? ???? ???

??????? ??????

?????? ?? ??? ??????? ?? ?? ?? ?? ????????? ?? ????????? ???????? ????????? ??? ?????? ?? ????? ???????? ?? ?? ??????? ?????????? ?????? ?????? ???????? ????? ?? ???? reuse ?? ??? ?????? ???? ??? For example, if a user who is not the database owner (dbo) executes a dbo-owned stored procedure (calledmyProcin this example) in thepubsdatabase, use the following statement:
exec dbo.myProc
				
Instead of this:
exec myProc
				
This technique eliminates confusion about other possible versions of the procedure by different owners from a coding and maintenance standpoint, and also allows SQL Server to access the execution plan for the specific procedure more directly.

By not qualifying the owner name, SQL Server enters the compile code and acquires a COMPILE lock on the procedure. However, eventually it determines that a new plan is not required (assuming no other reasons apply), so it does NOT recompile the plan at this point due to the lack of qualification. However, the extra step of getting a COMPILE lock on the procedure can cause blocking contention in severe situations. Refer to Q263889 INF: SQL Blocking Due to [[COMPILE]] Locks for more details on this situation.

If you owner qualify the procedure call with owner.procedure, you do not need to acquire the compile lock, so the contention is reduced.

Identifying and Resolving Issues

If you have not already done so, refer to the following article in the Microsoft Knowledge Base for details on capturing Profiler data to help analyze the performance of your system:
224587TO HOW: ???????????? SQL ????? ?? ??? ????????? ????????

View the Profiler Data

SQL Server Profiler includes anSP:Recompileevent that you can use to monitor the number of recompiles occurring. TheSP:Recompileevent occurs whenever a stored procedure recompiles during execution.
  • Group the Profiler trace by Event Class

    1. ????? ???????????? ??,???.
    2. ????? ????Data Columnstab, use the UP button to move theEvent Class, ??????? ???????????heading, withEvent Classfirst. Use the DOWN button to remove all other columns under the????heading.
    3. ????? ????,OK.
    Check the number of theSP:Recompileevents.

    You can expand the SP:Recompile group to see the details of the individual occurrences. The???column of the event indicates the name of the stored procedure that recompiled. If multiple procedures are causing recompiles, they are sorted by the number of occurrences. If you have a large number ofSP:Recompileevents and you are experiencing high CPU usage, focus on resolving the procedures that have the highest number of recompiles. Note the system process ID (SPID) and Start Time of theSP:Recompileevent for one instance of the particular stored procedure(s) and follow the steps below.

    If you do not see anySP:Recompileevents, but you are still experiencing a performance problem, see the following article in the Microsoft Knowledge Base:
    224587TO HOW: ???????????? SQL ????? ?? ??? ????????? ????????
  • Determine the Statement That Triggered the Recompile Event

    1. ????? ???????????? ??,???.
    2. ????? ????Data Columnstab, use the DOWN button to remove all other columns under the????heading.
    3. ????? ?????????tab, remove all events exceptSP: ???????,SP:StmtStarting,SP:Recompile, ??SP: ?????. If you did not capture theSP:StmtStartingevent, you can substituteSP:StmtCompleted, but do not include both because doing so doubles the amount of information you need to look through.
    4. If you have identified a particular instance of a stored procedure recompilation to examine, you can limit the data you view to the specific SPID and time frame of the occurrence by using the????????? ?? ????? ????..
    5. ????? ????,OK.

    TheSP:Recompileevent will be raised directly following theSP:StmtStartedevent of the stored procedure statement that caused the recompilation. After the recompile event is finished, you will see a repeat of theSP:StmtStartedevent, indicating that the statement is executing with the newly generated plan.

    ????? ?????? ?? ????? ????:
    use pubs
    go
    drop procedure RecompProc 
    go
    create procedure RecompProc as
    create table #t (a int)
    select * from #t
    go
    exec RecompProc
    						
    If you execute this code in Query Analyzer and view the above events in a Profiler trace, you will see the following sequence:

    ?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
    Event Class???
    SP: ???????RecompProc
    SP:StmtStartingcreate table #t (a int)
    SP:StmtStartingselect * from #t
    SP:RecompileRecompProc
    SP:StmtStartingselect * from #t
    SP: ?????RecompProc


    You can tell immediately that the statement that caused the recompilation was:
    select * from #t
    						
    Because it appears both before and after theSP:Recompile??????

    ??? ?? ???? ?????? ???? ??? ??SP:StmtCompleted?????, ????? ????SP:StmtStarting????? ?? ???,SP:Recompile????? ?? ????? ?? ??? ??? ???? ??? ???? ??????:

    ?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
    ????? ???????
    SP: ???????RecompProc
    SP:RecompileRecompProc
    SP:StmtCompleted?? ??? ???? * ?? # t
    SP: ?????RecompProc


    ?? ?? ??? ???? ???SP:Recompile????? ???? ?? ???? ????? ??SP:StmtCompleted?? ??? ????? "?? ??? ???? * # t ??" ???, ?? recompilation ?? ????? ?? ??? ???? ????? ???? ?? ?? ?? ?? recompile ?? ??? ?? ?????? ????? ????? ?? ??, ??? ??? sense, ???? ??? ?? ???? ????? ??? ???????? ?? ??? ???SP:StmtStarting?????? ??? ?? ???? ?????? ???? ??? ??SP:StmtCompleted????? ?? ???, ??? ?? ??? ??? ?? ????? ?? ??? ??? ????SP:Recompile, ??? explained ?? ??? ????

    ???? ?? ??? ?? ????????? ?? ????? ???????? ????????? ?????? ???, SQL Server ????? ????? ?????? ????? ?? ?? ????????? ?? ??? ??? ????? ?? ???? ??????? recompile ????? ?? ????????? ?? ???? ????????, ?? ??? ?? ???? ?? ????????? ?? ?? ??? re-create ????????? ?? ????????? ????? ?? ???? ?? "Recompilations ???? ?? ????: ????? ???? ???????? ???? (DDL) ?? ???? Manipulation ???? (DML) ??????" ??? ??? ?? ??????? ?????? ??? recompilation ?? ???? discussed ??; ?? ?? ?? ?? ?????? ?? ????? ?? ??? ?? ???????? ??? recompilation ???? ?? ??? ?? ?? ???????? ???? illustrate ???? ?? ??? ???

?????? ?? ?????? ?? ???? recompilations

??? ???? ?? ??????? ???????? ??? ??? ?????? ????? ????? ?? ?? ?? ??????? ??? ???????? ????????? ?? ?????? ???????? ?? ?? ?????? ??? ????????? ?? ??? ??, SQL ????? ?? ????? ?? ???? ?????? statistical ???? ?? ?????? ?? ?? ?? ????????? ???? ?? ??? ???????? ????????? recompile ?????? ?? ?????? ?? ??? ??? ????? ???????? ????????? ?? ????? ????:
drop procedure RowModifications 
go
create procedure RowModifications as
-- assume SomeTable exists with the same definition as #t, 
-- and has over 1000 rows
create table #t (a int, b char(10))
select * from #t
insert #t select * from SomeTable
select count(*) from #t  where a = 37
go
exec RowModifications
exec RowModifications
				
?? ????? ???????? ?? ???RowModifications????????? ?? ???, ?? ??????? Profiler ??? ????? ?????:

?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
????? ???????
SP: ???????RowModifications
SP:StmtStarting?????? # t (??? int, b char(10)) ?????
SP:StmtStarting?? ??? ???? * ?? # t
SP:StmtStarting# t ?? ??? ???? ???????? ???? * SomeTable ??
SP:StmtStarting# t ?? count(*) ?? ??? ???? ???? ??? = 37
SP:RecompileRowModifications
????:-UpdateStats???
SP:StmtStarting# t ?? count(*) ?? ??? ???? ???? ??? = 37
SP: ?????RowModifications

???::???? ???????? ???????? ?? ??SP:Recompile?? ??? ????? "?? ??? ???? * # t ??" ???? ?? ???? ?? "Recompilations ???? ?? ????: ????? ???? ???????? ???? (DDL) ?? ???? Manipulation ???? (DML) ??????" ??? ??? ?? ??????? ?????? ??? recompilation ?? ???? discussed ?? ??? ?? ?????? ?? ??? ????? ??SP:Recompile??? ????? ?? ??????? ?? ?? ??? ????????? ????????? ???? ???

?? ?????? ???, "# t ?? count(*) ?? ??? ???? ???? ??? = 37" ???? ??? recompilation ????????? ??? ????????? ?? ?????? ????????? ???? ?? ??? ?????? ???? ?? ??? ?? ????????????:-UpdateStatsevent confirms that the recompilation was due to row modifications. The???column indicates the column for which the statistics were modified.

When the #t table was created, the number of rows is zero. The plan for the original "select * from #t" is developed with that row count, as well as the plan for the "select count (*)" query. However, before the "select count(*)" is executed, 1,000 new rows are inserted into the #t table. Because a sufficient amount of data has been changed, the optimizer recompiles the procedure to ensure that it chooses the most efficient plan for the statement. This recompilation will occur on every execution of the stored procedure because the insertion of 1,000 rows will always be viewed as significant enough to warrant recompilation.

The algorithm SQL Server uses to determine whether a plan should be recompiled is the same algorithm used for auto-update statistics as described in the following article in the Microsoft Knowledge Base:
195565INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work
In the example above, the stored procedure is small enough that the recompilation would not have a noticeable effect on performance. However, if you have a large stored procedure that performs similar activities resulting in multiple recompilations, you may notice a performance degradation.

The following methods exist to counteract recompilations due to row modifications:
  • Execute the statement usingsp_executesql.
    This is the preferred method. Statements executed using thesp_executesqlstored procedure are not compiled as part of the stored procedure plan. Therefore, when executing the statement, SQL Server will be free to either use an existing plan in cache for the statement or create a new one at run time. In either case, the plan for the calling stored procedure is unaffected and does not have to be recompiled.

    The EXECUTE statement will have the same effect; however, it is not recommended. Using the EXECUTE statement is not as efficient as usingsp_executesqlbecause it does not allow for parameterization of the query.

    TheRowModificationsprocedure given above can be written to usesp_executesql???????????:

    drop procedure RowModifications2 
    go
    create procedure RowModifications2 as
    set nocount on
    -- assume SomeTable exists with the same definition as #t, 
    -- and has over 1000 rows
    create table #t (a int, b char(10))
    select * from #t
    insert #t select * from SomeTable
    exec sp_executesql N'select count(*) from #t where a = @a', 
                       N'@a int', @a =  37
    go
    exec RowModifications2
    exec RowModifications2
    						

    For the second execution of theRowModifications2procedure, you will see the following events in Profiler:

    ?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
    Event Class???
    SP: ???????RowModifications2
    SP:StmtStartingcreate table #t (a int, b char(10))
    SP:StmtStartingselect * from #t
    SP:StmtStartinginsert #t select * from SomeTable
    SP:StmtStartingexec sp_executesql N'select count(*) from #t where a = @a', N'@a int', @a = 37
    SP: ???????
    SP:StmtStartingselect count(*) from #t where a = @a
    Auto-UpdateStats???
    SP:StmtStartingselect count(*) from #t where a = @a
    SP: ?????
    SP: ?????RowModifications2


    Notice that there are noSP:Recompileevents for theRowModifications2procedure. There are completeSP: ??????????? ?? ???SP: ?????events for thesp_executesqlcall context, and anAuto-UpdateStatsevent for column???. However, because this call is out of the context of the stored procedure, theRowModifications2procedure does not need to be recompiled in this case.

    For more information on using thesp_executesqlstored procedure, see the "sp_executesql (T-SQL)" and "Using sp_executesql" topics in SQL Server Books Online.
  • Use sub-procedures to execute the statements that are causing the recompilations.
    In this case, the statement may still cause a recompilation, but instead of recompiling the large calling stored procedure, it will only recompile the small sub-procedure.
  • Use the KEEP PLAN option.
    Temporary tables have special rules regarding recompilations which, in some cases, can be more stringent than the default recompilation algorithm. You can use the KEEP PLAN option to relax the temporary table threshold back to the default algorithm. For more information, see the "Avoiding Recompilation by Using the KEEP PLAN Option" section of this article.
???::TheRowModificationsprocedure is a very simplified example of a procedure that is recompiled due to row modifications. Please review the following caveats concerning this example:

  • ?????? ?? ??????? ?????? ?? ????? ???? ??, ?????? ?? ?????? ?? ?????? ???????? ?? ?????? ???????? ???????????? ???? ?? ??? ???? ???? ??? ??? ???????? ?????? ??? ???????? ???? ?????? ??? ???? ?? ????????? ????, ??????? ?????? ????? ????? ?? ?? ??, ???????? ????????? recompiled ?? ?????? ??????? ???? ???????? ??? ???? ???? ???? ??? recompilation ???????? "Avoiding Recompilation ?????? ????? ???? ?? ????? ????? ??????" ??? ????? ???? ??? ?? ?? ??? ?? ???? ?? ????
  • ??????? ?? ???????????? ?? ???? executions ?? ???? ??? recompilation ???? ??? ?? ??????? ?????? # t ??? ?? recompilation ?? ?????? ??? "Recompilations ???? ?? ????: ????? ???? ???????? ???? (DDL) ?? ???? Manipulation ???? (DML) ??????" discussed ??? ?? ???? ?? ????
  • ?? ??????, ???? ???? simple ??? "# t ?? count(*) ?? ??? ????" ??? ?? ????? ???? ??? ?? "?? ??? ???? * # t ??" ???? ??????? recompilations ?? ????, ?? ??? SQL ????? ???? ????? "trivial ???????" recompiling (???? ?? ?? ?? ??? ???? * ???? ?????? ??) ?????? ?? ?????? ?? ?????

????: ????? ???? ???????? ???? (DDL) ?? ???? Manipulation ???? (DML) ?????? ?? ???? recompilations

??? DDL ???????? ?? ???? ????????? ?? ??? ??? ?? ?? ???, ?? ????????? ?? ??? ?? recompiled ?? ?????? DML ???? ??? ????? ?? ???????? ?????? DDL ??? ????? ?? ?? ???????? ????

????? ?????? ???????? ????????? ?? ????? ????:
drop procedure Interleave 
go
create procedure Interleave as
-- DDL
create table t1 (a int)
-- DML
select * from t1
-- DDL
create index idx_t1 on t1(a)
-- DML
select * from t1
-- DDL
create table t2 (a int)
-- DML
select * from t2
go
exec Interleave
				
??? ?? ?????? ???????? ??? ?? ??? ?? ???????? ?? ?? Profiler ????? ??? ??? ?? ?????? ?? ?????, ?? ????? ???? ???????:

?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
????? ???????
SP: ???????Interleave
SP:StmtStarting?????? t1 ????? (??? int)
SP:StmtStarting?? ??? ???? * t1 ??
SP:RecompileInterleave
SP:StmtStarting?? ??? ???? * t1 ??
SP:StmtStarting??????????? idx_t1 ?? t1(a) ?? ?????
SP:StmtStarting?? ??? ???? * t1 ??
SP:RecompileInterleave
SP:StmtStarting?? ??? ???? * t1 ??
SP:StmtStarting?????? t2 ????? (??? int)
SP:StmtStarting?? ??? ???? * t2 ??
SP:RecompileInterleave
SP:StmtStarting?? ??? ???? * t2 ??
SP: ?????Interleave


?? ?????? ???, ???????? ????????? ???????? ?? ????? ??? ??? recompiled ??? ??? ??? ????? ???? ??, ?? ??? ???? ??????????? ?? ???????? ????????? ?? ??? ?? ????? develops ?? ????? ????:
  1. ????????? ???? ?? ?????? compilation ?? ????? ???????? t1 ?? t2 ????? ???? ???? ?????, ?? ???????? ?? ???????? ?????? ?? ??? ??? ????? ????? ?? ????? ?? ???????? ??? ????? ???? ???? ??????
  2. ???? ??? ?? ??? ????????? ????????? ???? ??, ?? ??? ??? ???? ??? ?? ?????? t1 ????? ?? ???? ???? ??? ?? ?? ?????? t1--?? ??? ??? ????? ?? ?? ???? ??? ??? ?????, ????????? ?? recompiled ?? ????? ?? ??? ?? ??? ???? ?? ??? ?? ????? ?? ?????? ???? ?? ???? ??????????? ????? ?? ??? ?? ????? ????? t1 ?? ??????? ??? ????, ??? ?? t1 ?? ?? ??? ???? ?? ??? ??? ??? ????? ?? t2 ?? ??? ???? ?? ??? ?? ???? ??? ????? ???? ?? ???? ??????? t2 ??? ?? ????? ???? ?? ????
  3. ???? ??? ?? t1 ??????????? ?? ????? ?? ??? ??? ????? ??, t1 ?? ???? ???? ?? ??? ???? ??, ?? ?? ??? ?? ????? ???? recompile ?? ??? ???????, t1 ?? ?????? ????????? ???? ??? ??????? ?? ????? ????? ?? ?? ??, ??????? ????????? ?????? ?? recompiled ???: t1 ?? ??? ???? ?? ??? ?? ?? ????? ?? ??????? ???? ?? ???? ?? t2 ??? ?? ????? ???? ??, ??????? ??? ????? t ?? ?? ??? ???? ?? ??? ????? ?? ???? ????
  4. ????, ?????? t2 ????? ?? ?? ???????? ?? ??? ???? t2 ?? ??? ???????? ??? ?? ??? ??? ????? ????? ??, ??????? ????????? ?? ?? ????? ??? recompiled ???
?? recompilations ?? ???????? ????????? ?? ???????? ???????? ??? ??? DDL ???? ?? ??? ????????? ?? ??????? recompilations ?? ?? ???? ?? ??? ???????? ????, ??? DML ???????? ???, ????? ??? ?????? ??????:
drop procedure NoInterleave 
go
create procedure NoInterleave as
-- All DDL first
create table t1 (a int)
create index idx_t1 on t1(a)
create table t2 (a int)
-- Then DML 
select * from t1
select * from t1
select * from t2
go
exec NoInterleave 
exec NoInterleave
				
?? ???? ????????NoInterleave????????? Profiler ??? ????? ????? ?????? ????:

?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
????? ???????
SP: ???????NoInterleave
SP:StmtStarting?????? t1 ????? (??? int)
SP:StmtStarting??????????? idx_t1 ?? t1(a) ?? ?????
SP:StmtStarting?????? t2 ????? (??? int)
SP:StmtStarting?? ??? ???? * t1 ??
SP:RecompileNoInterleave
SP:StmtStarting?? ??? ???? * t1 ??
SP:StmtStarting?? ??? ???? * t1 ??
SP:StmtStarting?? ??? ???? * t2 ??
SP: ?????NoInterleave


?? ?????? ??? ??? DDL ????? ??? front ??? ??????????? compiles ?? ????????? ?? ??? ???:
  1. ????????? ???? ?? ?????? compilation ?? ????? ???????? t1 ?? t2 ????? ???? ???? ?????, ?? ???????? ?? ???????? ?????? ?? ??? ??? ????? ????? ?? ????? ?? ???????? ??? ????? ???? ???? ??????
  2. ???? ??? ????????? ?? ????? ???? ?? ??? DDL ???????? ?? t1 ???????? t1 ?? t2, ??? ?? ??????????? ??? ??? ???
  3. ???? ??? ??? t1 ?? ???? ????? ??? ?? ??? ????? ?? ??? ?? ??? ?? ??? ?????? ??, ??????? ????????? recompiled ??? ??? ???????? ????? ??, ??????? ??????? ??? ????? ??? ?? ??? ???? ????? ??? ????????? ?? ??? ?? ????
  4. ????????? ?? ??? ??? ????? ?? ??????? ?? ????? ???? ??? ????????? ???? ??? ???????? ???????? ?? ??? ??? ???????? ???? ???, ??????? ????????? ?? ??? recompile ???? ?? ???????? ???? ???
???::????? ?? ???? ??? executions ????? ?? ?????? ????? ?? ?????? ?? ??? ?? ????? ????, ?? ???? ???? ?????? ???? ?? recompilations ??? ??? ??????????? ?????, ???????? ?? ???????? ????? ?? ?? ??? DDL ????? ????????? ?? ?????? ??? ????? ??? ?? ????????? ???? ?? ??? ??????? ???? ????? ?? ?? ???? ???

??? ?????? ?????? ?????? ?? ???? recompilations

??? ???????? ????????? ??? ??????? ???????? ?? ????? ?? ??? ????????? ????????? ?? recompiled ???? ?? ??? ???????? ????????? ?? ???? ?? ???? ???

?? ?? ????, ?? ??? ?? ????? ?????????? ???? ???? ?? ???? ???????? ????????? ?? ?????:
  • ?? ??????? ?????? ?? ??? ??? ??? ????? ??? ?? ?? ???????? ?????????, ?? ?? ?????? ?? ??? ???????? ????????? ??? ???? ??, ?? ???? EXECUTE ?? ????? ???? ??? ????????? ???? ??? ?? ???????? ??? ??? ??????? ?????? ?? ??????? ?? ??? ??sp_executesql???????? ??????????
  • ?? ??????? ?????? ?? ??? ??? ??? ????? syntactically ??????? ?????? ?? ??? ??? ????? ???????? ????????? ?? ???????
  • ????? ????? ????? ?? ??? ???? ????? ????? ?????? ???? ??????? ?????? ???? ???
  • ???? ?? ??????? ?????? ?? ??? ??? ??? ????? ?? ???? ??????? ?????? ?? ?????? ??? ??? ??? ?????? ????? ??? ???

    ?????? ????? ????? ???? ???????? ????????? ??? ???? ?? ??????? ???????? ?? ??? ?????? ???? ???? ????????? ????? ???? ?? ???? ???????? ????? ???? ????? ???????? ??? ?? ?? ???
  • ??? ????? ??? ??? ??????? ?????? ??? ??? ?? (???? CREATE ?????? ?? ?? ??? ????... ) ??? ??? ???? ???? ???????? ?? ?????? ??? ??? ?????... ???? ?? ????

???? ????? ?????? ?? ????? ???? Recompilation avoiding ????

???????? ???????????? ??? ??????? ?????? ????? ?????? ??????????? ?? ??? ??? complexities introduces ??? ???-??? ?????? ?????? ?? ???????? ?? statistical ??????? ?? ???? ??? dramatically ???? ?????? ?? ???????? ????????? ?? ???????? ?? ?????? ????????? ???? ?? ??????????? ???? ??????? ????? ??????? ???????? ?? ????? ?? ??? ?????? ???, ???? ?? ??? ?? ????? ?????????? recompilations ?? ???? ???????? ?? ?????? ???? ??? ??? ?????????? ????? ?? ?? ??? ??? ???????? ????????? ?? ???? ?? ??????? ?????? ?? ??? ?? ???? ??? ??? ??, ????????? ????? ?? recompiled ?? ???? ??? ?????? ??????? ?????? ??? ??? ???

????? ?????? ?? ????? ????:
drop procedure useKeepPlan 
go
create procedure useKeepPlan as
create table #t (a int, b char(3))
select * from #t
-- Make greater than 6 changes to #t
insert #t values (1, 'abc')
insert #t values (2, 'abc')
insert #t values (3, 'abc')
insert #t values (4, 'abc')
insert #t values (5, 'abc')
insert #t values (6, 'abc')
insert #t values (7, 'abc')
-- Now reference #t
select count(*) from #t 
--option (KEEP PLAN)
go
exec useKeepPlan
exec useKeepPlan
				
?? ?????? ???, ???? ????? ???????? ?? ??? Profiler ??? ????? ????? ????? ????:

?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
????? ???????
SP: ???????useKeepPlan
SP:StmtStarting?????? # t (??? int) ?? ?????
SP:StmtStarting-??????? - ??? ?? ???????? ????
SP:StmtStarting# t1 ?? count(*) ?? ??? ????
SP:RecompileuseKeepPlan
SP:StmtStarting# t1 ?? count(*) ?? ??? ????
SP: ?????useKeepPlan

?? ????????? ?? ??? ???? ?? ??????? ?????? # t ??? ???????? ?? ??? ???? ?? ?? recompiled ?? ???

?? ???????? recompilation ???? ???????? ??????? ?????? ?? ???? ?? ????? ?? ??? ?? ???? ??? dramatically ???????? ????????? ?????? ????? ?? ???????? ?? ??? ?? ?????? ??? ?????? ??? ???????, ??????? ???????? ?? ????? ??????? ??? ???????????? ?? ????? ???, ????? ???? ??? ?????????? ??? ??, recompilations ?????? ????? ???? ???????????? ????? ???? ?????? ?? ??? ???? ??? ?? ?? ?????? ?? ??? ???????? ???? ??? ???

????? ???? ?? ????????? ??? ??????? ???????? ??? ?? ?? ???? ???????? ?? ???? ???????? ????????? recompilations eliminates ?? recompilation ?? ?????? ?? ??? discussed ?? ???? ?? "Recompilations ???? ?? ??? ?????? ??????" ??? ??? ?????? ???? ?? ??? ???? ?????????? ?? reverts. ????? ???? ???? ??? recompilations altogether, ?? ????? ????? ?? ?? ?? ???? ???????? ?? ????????? ??? ???????? ??????? ???????? ?? ???? ??? ?????? ???, ??? ?? ??????? ?? ??????? "?????? (???? ?????)" ?????? ??? ???????? ?????????SP:Recompile????? ?? ????? ???? ?????

??? ?? ??????? ?? ??? ??? ??? "?????? (???? ?????)" ?????? ?? ??????? ?? ??? ????????? ??, ?? ?? Profiler ??? ????? ????? ???????:

?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
????? ???????
SP: ???????useKeepPlan
SP:StmtStarting?????? # t (??? int) ?? ?????
SP:StmtStarting-??????? - ??? ?? ???????? ????
SP:StmtStartingcount(*) ?? # t1 ?????? ?? ??? ???? (???? ?? ????? ?????)
SP: ?????useKeepPlan


?? ??? ????SP:Recompile??????

????? ?? ???? recompilations SET ??? ????????? ??? ???????? ?????????

????? ???? SET ?????? ???????? ??? ?? ???? ???? ?? ??? ??? ???:
  • ANSI_DEFAULTS
  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • CONCAT_NULL_YIELDS_NULL
??? ?? ?? ???????? ??? ?? ??? ?? OFF ???? ?? ??? ??? ???? ?? ??? SET ??? ?????????, ???????? ????????? ????? ?? recompiled ?? ??? ?? ????? ??? The reason for this is that changing these options may affect the query result that triggered the recompilation.

Consider the following sample code:
Use pubs
drop procedure test_recompile
go

create procedure test_recompile as
Set ANSI_DEFAULTS OFF
Select au_lname, au_fname, au_id from authors
where au_lname like 'L%'
--Option (Keep Plan)
Go
				
In this case, you will see the following events in SQL Profiler for every execution of the stored procedure:
+---------------------------------------------------+
| Event Class     | Text                            | 
+---------------------------------------------------+
| SP:Starting     | test_recompile                  | 
+---------------------------------------------------+
| SP:StmtStarting | Set ANSI_DEFAULTS OFF           | 
+---------------------------------------------------+
| SP:StmtStarting | select au_lname, au_fname, au_id| 
+---------------------------------------------------+
| SP:Recompile    | test_recompile                  | 
+---------------------------------------------------+
| SP:StmtStarting | select au_lname, au_fname, au_id| 
+---------------------------------------------------+
| SP:Completed    | test_recompile                  | 
+---------------------------------------------------+
				
Replacing the SET option with any one of the five options listed above will show the same results. Also, using the option of keep plan here won't help to avoid the recompilation because the cause of the recompilation is from the SET statement.

The recommended way to avoid the recompilation is not to use any of these five SET statement in a stored procedure. For additional information, see the following article in the Microsoft Knowledge Base:
294942PRB: SET CONCAT_NULL_YIELDS_NULL May Cause Stored Procedures to Recompile
However, as not recommended, running the SET statement to reset the connection option to the same value as the stored procedure, it can also avoid the recompile, doing it as:
Set ANSI_DEFAULTS OFF

exec test_recompile
				
The SQL Profiler trace will show no more SP:Recompile events.

The following table lists some common SET statements and whether or not changing the SET statement in a stored procedure causes a recompile:
?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
Set StatementRecompile
Set quoted_identifier????
Set arithabort???
Set ansi_null_dflt_on???
Set ansi_defaults???
Set ansi_warnings???
Set ansi_padding???
Set concat_null_yields_null???
Set numeric_roundabort????
Set nocount????
Set rowcount????
Set xact_abort????
Set implicit_transactions????
Set arithignore????
Set lock_timeout????
Set fmtonly????

??????

308737INF: How to Identify the Cause of Recompilation in a SP:Recompile Event

For information about using SQL Server Profiler, see SQL Server Books Online.

???

???? ID: 243586 - ????? ???????: 02 ?????? 2010 - ??????: 2.0
???? ???? ???? ??:
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Standard Edition
??????: 
kbinfo kbmt KB243586 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:243586

??????????? ???

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com