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

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

?? ??? ??????

??????

????? ??? ??????? ?????? ?? ??? ???? ?? ????? ?????? ???? ?? ????? ????????? ?? Microsoft SQL Server: recompilation ??? ??????? ?? ????????? ???????. ??? ??? ???? ???????? ????? ????? ???? ???? ?? ?? ????? ?? ??? ?? ???? ??????? ???? ?????? ??? ??????? ??????? ?? ????? ?????Microsoft ??? ????????:

224587HOW: ???? ??????? ?? SQL Server ???????
????? ??? ??????? ??????? ??? ??????? ?????? ????? ???? ??????? ? ?????? ???? ???? ????? ??????? ???? SQL ?? ????? ????? ?????? ???????? ????????? ????.

??????? ????

??? ????? ??? ?????????? ????? ???? ??? ?? ??? ?????? ?? ????? ??????? ??????, ?????? ??????? SQL Server ? ??????? ??? ???????. ??? ??? ?? ????? ??????? ?????? ??? ???????? ???? ????????? ?????? ???????? ??????? ?? ??????? ?????? ??? ???? ??? ????????? ??? ????? ????? ??? recompilation. ?? ???? ????????? ??????? recompilation ???? ??????? ??????:
  • ??????? ???? WITH ????? ????? ?? ????? CREATE PROCEDURE ?? ???????.
  • ????? ?????? ??? ?? ?? ???????? ?????? ???? ??? ?? ??? ????? ?? ????? ???? ?? ????? ?????????? ?? ???????.
  • ????? sp_recompile ?????? ?????? ???? ?????? ???????.
  • ??????? ????? ???????? ???? ????? ??? ??????? ?? ?? ???? ????? ??????? (??? ??? ???? ?????? ????? ???????? ????????).
  • ???? ????? aged ?? ????? ??????? ?????? ???? ?????? ?????.
???? ??? ??????? ?????? ????? ????? ???? ?????? ?? ????????? ??????? ?? ???? ????? ????? ????? ??? ????? ?? ????? ???????. ?? SQL Server 7.0 ??????? ???? ???? ??? ?? ???? ????? ???? ????? ????? ????? ???????. ???? ??? ?????? ?????? ????? ?????? ????? ???? ??? ??????? ??? ?????? ????? ???? ?????. ?? ???? ??????? ??????? recompilation ??? ??????? ?? ??????? ??????:
  • ?????? ????? ????? ??????? ???????? ?? ???? ?????? ???? ?? ??? ??????? ??????.
  • ??????? interleaves ?????? ??? ????? ???????? (DDL) ? ??? ?????? ???????? (DML).
  • ????? ??????? ?????? ????? ??? ??????? ???????.
?? ?? ??? ??????? ??? ??????? ?? ???? ?? ???????? ?? ??? ???????.

?? ??? ???????? ????? ????? ??????? ?????? ???? ?? ??????? ????? ?? ?????? ????, ????? ?? ??? ??????? ?????. ?? ????? ???? ???? ??? ??? ????? recompilation ?????? ?????? ?? ????? ?? recompiled. ??? ???? ?? ????? ?????? ?????? ?????? ?? ??? ?????? ?? ???????. ?????? ??? ???? ?? ????????? ??? ??? ???????? ???? ??????? "??????? ??? Transact-SQL" ?? "??? SQL Server ???????".


????????? ??????? ?? ??? ??????? ???? ??? ????? ????? recompilations ??????? ?????? ??? ??????? ??????? ???????? ???? ????? ????????? ???? ?????.

????? ????

?? ?????? ???? ????? ????? ??????? ?????? ????? ???? ?????? ?????. ???? ??? ?????? ??? ???? ?????? ?????? ???? ?? ??? ??????? ???????? ?????? ???????? ??????. ??? ???? ??????? ??? ??? ?????? ??? ???? ????? ???????? (dbo) ????? ???? dbo ????? ???? (???? myProc ?? ??? ??????) ?? ????? ?????? pubs (????????) ? ?????? ??????? ???????:
exec dbo.myProc
				
????? ?? ???:
exec myProc
				
??? ??????? ???? ??? ??????? ??? ??????? ???? ????? ?? ??????? ?????? ????? ?????? ?? standpoint ????? ???????? ??? ???? ????? SQL Server ?????? ??? ??? ????? ??????? ????? ???? ??????.

?????? ??? ??????? ??? ?????? ? ?????? ??? ??????? SQL Server ? ????? ????? ????? ??? ???????. ??? ???? ?????? ?????? ???? ???? ??? ?????? (?? ????? ????? ????) ? ???? NOT ????? ????? ??? ??? ??? ?????? ??? ???? ??? ?????. ??? ????? ?? ???? ????? ?????? ???????? ?? ?????? ??? ????? ????? ??? ??????? ??? ??????? ?? ????? ?????. ???? ??? Q263889 INF: ????????? ????? SQL ??? ????? [[???????]] ????? ?? ???????? ??? ??? ??????.

??? ??? ???? ????? ??????? ??????? ?? owner.procedure ? ?? ????? ??? ?????? ??? ????? ??????? ??? ??? ????? ?? ???????.

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

??? ?? ??? ?? ??? ?????? ?????? ??? ? ???? ??????? ??????? ?? ????? ?????Microsoft ?????? ??? ?????? ??? ?????? ???????? ???? ????? ??????? ???????? ?? ????? ???? ?????? ????? ??:
224587HOW: ???? ??????? ?? SQL Server ???????

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

????? SQL Server ???? ????? ??????? ??? SP:Recompile ????? ????????? ??????? ??? recompiles ????. ??? ????? ????? SP:Recompile ???? recompiles ????? ???? ????? ???????.
  • ?????? ?????? ???? ????? ??????? ?????? "??? ?????"

    1. ?? ??????? ???? ???? ??? ?????.
    2. ?? ????? ??????? "????? ??????" ? ?????? ???? ?????? ???? ??? ????? "?" ???? ??? ????? ????????? ?? ??? ????? ?????. ?????? ???? ????? ??? ????? ???? ????? ???? ??? ????? ?????????.
    3. ???? ??? ?????.
    ???? ?? ??? ??????? SP:Recompile.

    ????? ????? ???????? SP:Recompile ??????? ?????? ????????? ????????. ???? ?????? ?? ????? ??? ??? ??????? ?????? recompiled. ??? ???? recompiles ???? ?? ??????? ??????, ??? ??? ??? ??? ?????????. ??? ??? ???? ??? ???? ?? ??????? SP:Recompile ?????? ??????? CPU ???? ??????? ??? ?? ????????? ???? ??? ???? ??? recompiles. ???? ????? ?????? ??????? (SPID) ? "???? ?????" ????? SP:Recompile ???? ???? ?? ????? (???????) ??????? ????? ?? ???? ??????? ???????.

    ??? ?? ??? ??? ??? ????? SP:Recompile ???? ?? ???? ??????? ?????? ????? ???? ? ???? ??????? ??????? ?? "????? ??????? ?? Microsoft:
    224587HOW: ???? ??????? ?? SQL Server ???????
  • ????? ???? ???? ?? ????? ????? ?????

    1. ?? ??????? ???? ???? ??? ?????.
    2. ?? ????? ??????? "????? ??????" ? ?????? ???? ????? ??? ????? ???? ????? ???? ??? ????? ?????????.
    3. ??? ??????? ???????? ?? ?????? ???? ??????? ???????? SP: ??? ? SP:StmtStarting ? SP:Recompile ? SP: ?????. ??? ?? ?????? ??????? SP:StmtStarting ? ???? ??????? SP:StmtCompleted ???? ?? ??? ?????? ?????? ??? ?????? ???? ?????? ????? ????????? ???? ????? ??? ?????.
    4. ??? ??? ?????? ??? ???? ??? ?? recompilation ??????? ?????? ???? ????? ????? ???????? ??? SPID ?? ?????? ?????? ?????? ????? ???????? ????? ??????? ?????.
    5. ???? ??? ?????.

    ??? ??? ??? ????? SP:Recompile ?????? ??? ????? SP:StmtStarted ????? ??????? ?????? ???? ???? ?? recompilation. ??? ?????? ????? ????? ??? ??? ????? ????? SP:StmtStarted ???? ??? ????? ??????? ?? ???? ?? ?????? ??????.

    ?? ???? ???????? ?????? ??????:
    use pubs
    go
    drop procedure RecompProc 
    go
    create procedure RecompProc as
    create table #t (a int)
    select * from #t
    go
    exec RecompProc
    						
    ??? ????? ??? ????????? ???????? ?? "???? ???????" ??? ??????? ????? ?? ???? ???? ????? ??????? ???? ??????? ??????:

    ?? ??? ??????????? ??? ??????
    ??? ?????????
    SP: ???????RecompProc
    SP:StmtStarting????? ???? t # (Int)
    SP:StmtStarting??? * ?? # t
    SP:RecompileRecompProc
    SP:StmtStarting??? * ?? # t
    SP: ?????RecompProc


    ????? ????? ?????? ?? ??? ???? ???? ?? recompilation:
    select * from #t
    						
    ??? ???? ??? ???? ????? SP:Recompile.

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

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


    ???? ????? ????? SP:Recompile ??? ????? SP:StmtCompleted ?????? "??? * ?? # t" ??????? ??????? ?? recompilation. ???? ??? ???????, ??? ????? ????? ??????? ??? ??? ?? ??? ????? ???? ??????? ???? ?????. ??????? ???? ???? ??????? ?? ??? ?????? ????? SP:StmtStarting. ??? ?? ?????? ??????? SP:StmtCompleted ? ??? ???? ??? ?????? ??? SP:Recompile ??? ?? ???? ?????.

    ???? ??? ??? ??? ????? ????? ??? ??????? ?????? ???? ??? ???? SQL Server ???? ????? ??? ???????? ???? ???????. ???? ??? ????? ????? ??? ????? ??????? ????? ?? ?? ???? ????? ????? ????? ??????? ?? ??? ???? ???? ????? ???????? ?????. ??? recompilation ?? ??? ?????? ????? ??? ??????? ?? ????? "Recompilations ??? ????????? ??????? ????? ????? (DDL) ? ???????? ?????? ????? (DML) ?????? ??????" ?? ??? ???????; ??? ?????? ???? ?????? ??? ?????? ??????? ???? ???? ?? recompilation ??????.

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

??? ?? ????? ?????? ????? ????? ?? ???????? ?? ???? ?????? ???? ?? ??? ????? ???? ??? ????? ?? ????? ??? ????????? ??????, ????? SQL Server ????? ????? ??????? ?????? ?????? ?? ???? ????? ??? ??? ???????? ??? ???????? ????????? ????. ????? ? ?? ???? ???????? ????? ???? ???????:
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 ??? ???? ??????? ??????? ?? ???? ????? ???????:

?? ??? ??????????? ??? ??????
??? ?????????
SP: ???????RowModifications
SP:StmtStarting????? ???? t # (Int ? b char(10))
SP:StmtStarting??? * ?? # t
SP:StmtStarting????? ??? t # * ?? SomeTable
SP:StmtStarting??? count(*) ?? # t ??? = 37
SP:RecompileRowModifications
UpdateStats ??????a
SP:StmtStarting??? count(*) ?? # t ??? = 37
SP: ?????RowModifications

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

?? ??? ??????? "??? count(*) ?? # t ??? = 37" ??????? recompilation ????? ????? ?? ??? ?????? ??? ????? ??????. ???? ???? ????? UpdateStats ?????? ?? recompilation ????? ????????? ????. ???? ?????? ???? ??? ???? ?? ????? ??????????.

??? ????? ?????? t # ? ??? ?????? ?????. ????? ??????? "??? * ?? # t" ????? ?? ??? ?????? ???? ???????? ??? ??? ??????? "????? ??? ???? (*)". ??? ???? ??? ????? "????? count(*)" ??? ????? ?????? ??????? 1,000 ?? ?????? t #. ???? ????? ???? ????? ?? ???????? ?????? recompiles ??????? ?????? ?? ?? ??? ?????? ??? ?????? ????? ???????. ???? ??? recompilation ??? ?? ????? ??????? ?????? ??? ??????? ?????? 1000 ????? ??? ??? ???? ??? ???? ???? recompilation.

?? ???????? ?????? SQL Server ?????? ?? ??? ??? ??? recompiled ??? ???????? ??? ??????? ???????? ????? ?????? ??? ?? ???? ?? ??????? ??????? ?? "????? ??????? ?? Microsoft:
195565INF: ??? SQL Server 7.0 ? SQL Server 2000 Autostats ?????
?? ?????? ?????? ??????? ?????? ????? ??? ???? ?? recompilation ?? ?? ???? ???? ????? ??????? ??? ??????. ??? ???? ??? ??? ???? ????? ???? ???? ???? ???? ?????? ??????? ?????? ???? recompilations ?????? ?? ????? ?????? ????.

????? ??????? ?????? ??? counteract recompilations ????? ????????? ????:
  • ????? ??????? ???????? sp_executesql.
    ??? ?? ??????? ??????. ?? ??? ????? ?????? ??????? ???????? ??????? sp_executesql ??????? ???? ?? ??? ??????? ??????. ????? ??? ????? ??????? ? ??? SQL Server ????? ??? ??????? ???? ????? ?? ????? ??????? ?????? ??????? ?? ????? ????? ????? ?? ??? ???????. ?? ???? ????????? ??? ??????? ?????? ????????? ????? ?? ??? ?? ??????? ?? ???? recompiled.

    ????? ????? EXECUTE ??? ???????; ??? ???? ??? ??? ????????. ???????? ?? ??????? ??????? ??? ???? ??? ???????? sp_executesql ???? ?? ???? ?? parameterization ?????????.

    ???? ????? ??????? RowModifications ????? ????? ???????? sp_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
    						

    ??????? ?????? ?? ??????? RowModifications2 ???? ??????? ??????? ?? ???? ????? ???????:

    ?? ??? ??????????? ??? ??????
    ??? ?????????
    SP: ???????RowModifications2
    SP:StmtStarting????? ???? t # (Int ? b char(10))
    SP:StmtStarting??? * ?? # t
    SP:StmtStarting????? ??? t # * ?? SomeTable
    SP:StmtStartingsp_executesql exec N'select count(*) ?? # t ??? = @ ' ? Int N'@a ' ? @ = 37
    SP: ???????
    SP:StmtStarting??? count(*) ?? # t ??? = @
    UpdateStats ??????a
    SP:StmtStarting??? count(*) ?? # t ??? = @
    SP: ?????
    SP: ?????RowModifications2


    ???? ?? ???? ??? ????? SP:Recompile ??????? RowModifications2. ???? ????? SP: ??? ??? SP: ????? ????? sp_executesql ??????? ???? ? ??? UpdateStats ?????? ????? . ??? ???? ????? ??? ??? ???????? ???? ???? ??????? ?????? ??????? RowModifications2 ?? ????? ??? recompiled ?? ??? ??????.

    ?????? ??? ???? ?? ????????? ??? ??????? ??????? sp_executesql ????? ???? "sp_executesql (T-SQL)" ? "??????? sp_executesql" ???????? ?? "??? SQL Server ???????".
  • ?????? sub-procedures ????? ???????? ???? ????? ?? recompilations.
    ?? ??? ??????? ??? ????? ??????? recompilation ???? ????? ????? ?? ????? ??????? ?????? ????????? ?????, ???? ??? ??? ????? sub-procedure ????.
  • ?????? ?????? "???????? ????????.
    ????? ??????? ??????? ????? ???? ???? ????? recompilations ???? ?? ??? ???????? ???? ????? ????? ???? ?? ???????? recompilation ?????????. ????? ??????? "??????? ????????" ?????? ????? ?? ???? ???? ??? ???????? ?????????. ????? ?? ?????????? ???? ??? "???? Recompilation ?? ???? ??????? ???????? ???? ????" ?? ??? ???????.
??????: ????? ????? RowModifications ???? ????? ?????? ?? ??????? recompiled ????? ????????? ????. ?????? ?????? ??????? ??????? ??????? ???????? ??? ??????:

  • ??? ????? ?? ?? ?????? ?????? ???? ???? ? ????? ??? ?????? ????????? ??????? ???? ???? ??? ????? ????? ?????. ??? ?? ????? ???? ????? ?? ???????? ?? ???? ????? ??? ?? ?? ????? ??? ????????? ? ???? recompiled ??????? ??????. ????? ?????????? ???????? ??? ??????? ??????? ????? ??????? ?????? recompilation ?????? ?? "???? Recompilation ?????? ??????? ???????? ???? ????" ??? ?? ??? ???????.
  • ???? executions ????? ????????? ????? ????? ??? recompilation ??? ??? ????? ?? t # ?????? ??????. ????? ??? recompilation ??? ???????? ?? "Recompilations ??? ????????? ??????? ????? ????? (DDL) ? ???????? ?????? ????? (DML) ?????? ??????" ??? ?? ??? ???????.
  • ?? ??????? ????? "????? count(*) ?? # t" ?? ??? ?????? ????? ?? ???? "??? * ?? # t" ????. ????? recompilations ?????? SQL Server ?? ??? ????? "??? ???????" (??? ??? * ?? ????) ????? ????????? ????.

recompilations ???????? ??? ??????? ???? ????? ???????? (DDL) ? ???????? ????? (DML) ?????? ????????

??? ??? ??? ????? ?????? 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
				
??? ????? ??? ????????? ???????? ?? "???? ???????" ??? ??????? ????? ?? ???? ???? ????? ??????? ???? ??????? ??????:

?? ??? ??????????? ??? ??????
??? ?????????
SP: ????????????
SP:StmtStarting????? ???? t1 (Int)
SP:StmtStarting??? * ?? t1
SP:Recompile?????
SP:StmtStarting??? * ?? t1
SP:StmtStarting????? ???? idx_t1 ??? t1(a)
SP:StmtStarting??? * ?? t1
SP:Recompile?????
SP:StmtStarting??? * ?? t1
SP:StmtStarting????? ???? t2 (Int)
SP:StmtStarting??? * ?? t2
SP:Recompile?????
SP:StmtStarting??? * ?? t2
SP: ??????????


?? ??? ??????? ??? ????? ???? recompiled ???? ???? ????? ???????. ???? ????? ???? ?? ???? ???????? ????? ????? ???? ???????? ??? ???? ??????? ???????:
  1. ????? ??????? ??????? ?????? ?? ??????? t1 ??????? ? t2 ??? ??????. ????? ???? ?? ??? ????? ?? ??? ?????????? ?????? ??? ??? ???????. ??? ?? ???? ????? ???? ?? ??? ?????.
  2. ??? ??? ????? ??????? ????? ??????, ?????? ?????? ????? t1 ??????. ?????? ??????? ?? ????? ?? t1 ??????--??? ?? ???? ?? ???. ????? ??? recompiled ??????? ??? ??? ?????? ?????? ??? ????? SELECT. ??? ????? ??? ??? ?????? ?? t1 ???????? ??? ????? ?? t1 ??? ????? ????. ???? ?? ??? ????? ?? ??? ??? ?? t2 ??? t2 ?? ??? ??? ????? ???.
  3. ?????? ??????? ????? ???? ??? t1. ??????? ???? ??? ????? ????? ??? ??? t1, ????? ???? ?? ??? ?? ????? ?????. ??? ???? ???? ?? ????? ?????? t1 ??? ?? ?? ????? ??? ??? ??????? ??? ?? ???? recompiled ??? ???? ?????? ???? ???? ??? ?? t1. ?? ??? t2 ?? ??? ??? ?????, ???? ????? ?? ??? ??? ?? t.
  4. ??? ???? ??? ????? t2 ?????? ???? ??????? ??? ?? t2. ???? ???? ?? ??? ??????, ??? ??????? recompiled ??? ??????.
???? ??? recompilations ??? ????? ?? ????? ????. ?????? recompilations ?? ????? ??????? ?????? DDL ???? ?????? ?????? ??????? ?????? 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 ????? ??????? ??????? ?? ???? ????? ???????:

?? ??? ??????????? ??? ??????
??? ?????????
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 ???????? ??? ????????. ??????? ???? ???????? ??? ??????? ??? ???:
  1. ????? ??????? ??????? ?????? ?? ??????? t1 ??????? ? t2 ??? ??????. ????? ???? ?? ??? ????? ?? ??? ?????????? ?????? ??? ??? ???????. ??? ?? ???? ????? ???? ?? ??? ?????.
  2. ????? ??????? ?? ??????? ?????? ?? DDL ?????? ????? t1 ??????? ? t2 ????? ?????? ??? t1.
  3. ?????? ??????? ?? ????? ????? ?? t1. ???? ?? ????? ?? ??? ??? SELECT ? ?? recompiled ???????. ???? ???? ???? ???????? ??? ??? ????? ???? ?????? SELECT ?? ??????? ?? ??? ?????.
  4. ????? ???? ??????? ???????? ????? ???? ?? ???????. ???? ???? ??? ??????? ??? ???????? ?????? ???? ??? ???? ???? ????? ????? ??????? ???? ????.
??????: ??? executions ?????? ? ??????? ??????? ?? ??? ????????? ? ????? ??????? ?????? ???????? ????? ?? ???? ?? recompilations ???????. ??? ?? ??? ????? ????????? ???? ?????? ?? ????? ?? ????? ??????? ?????? ?? ?? ???? ??? DDL ??? ?? ????? ???????.

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

?? ???? ??????? ??????? ??????? ?? ????? ???? ??????? ?????? recompiled ?? ??? ??? ???? ????? ???????.

????? ??? ????? ??????? ?????? ???? ?????? ?? ????????? ???????:
  • ???? ???? ???????? ???? ????? ??? ??? ???? ???? ??? ???? ???? ?? ?????? ?? ??????? ?????? ??? ? ???? ?? ????? ???? ??????? ?? ???? ?? ?? ????? ??????? ???????? ?? ??????? ????? ???? ????? ?? sp_executesql.
  • ???? ???? ???????? ???? ????? ??? ??? ???? ???? ?? ???? ???? ??? ?????? ?????? ?? ??????? ?????? ?? ??????.
  • ?? ???? ??? ?????? "????? ??????" ?????? SELECT ?????? ?? ???? ???? ????.
  • ???? ???? ???????? ???? ????? ??? ??? ?? ???? ???? ?? ???? DROP TABLE ????? ???? ????.

    ?? ???? ?????? ??? DROP TABLE ??????? ??????? ???? ?? ??????? ?? ????? ????. ??? ????? ??????? ???????? ??? ?????? ???????.
  • ?? ???? ?????? ????? ???? ???? (??? CREATE TABLE ?? SELECT... INTO) ???? ?? ???? ?????? ????????? ??? IF... ??? ?? ?????.

???? Recompilation ???????? ???? ??? ????????

??????? ?????? ?????? ??? ????????? ??????? ???? ????? complexities ?? ?????? ?????????. ???? ?? ????? ??? ?????? ?????????? ????????? ??????? ???? ???? ???? ??? ????? ??????? ??????. ?????? ?? ?? ?????? ?????? ??? ???? ?? ???? ??????? ????? ??????? ??????? ?? ????? ???????? ???? ???? ????? ???? ?? 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
				
?? ??? ??????? ??? ??? ??????? ??????? ?? ???? ????? ??????? ??????? ??????:

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

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

??? recompilation ??????? ???? ?? ??????? ??? ????????? ??? ????? ???????? ?? ???? ???? ???? ?? ???? ???? ???? ??? ??? ????????? ?????? ?????? ?????? ??? ???. ??? ???? ?? ???? ???? ????????? ????? ????? ??????? ??????? ???? ????? ???? ??? ?? ????? ???? recompilations ?? ???? ??? ?? ?????? ?????. ?? ????? ???? "???????? ????" ?? ????? SELECT ?????? ??? ??????.

???????? ???????? ??? ??????? ?????? recompilations ??????? ?? ??? ???? ?? ????????? ??? ??????? ??????? ??? ??????? ?? ???? ??? ???????? ???????? recompilation ????? ????????? ???? ?????? ????? ?? ?????? "??????? ?? ??? ????????? Recompilations" ?? ??? ???????. ???????? ???????? ?? ???? recompilations ?????? ? ???? ?????? ??? ??????? ?? ??? ???? ?? ????????? ??? ??????? ??????? ?????? ???? ?? ???????. ?? ?????? ?????? ??? ?? ????? ??????? ?? ??? "?????? (???????? ???)" ?? ????? ???? ????? SP:Recompile ?? ??? ?????.

??? ??? ?????? ??????? ?? ??? "?????? (???????? ???)" ?? ????????? ???????? ????? ????? ??? ??? ??? ??????? ??????? ?? ???? ????? ???????:

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


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

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

?? ????? ?????? SET ?????? ??????? ON ???? ???????:
  • ansi_defaults
  • ansi_nulls
  • ansi_padding
  • ansi_warnings
  • concat_null_yields_null
?? ???? ????? ????? SET ?????? ?? ?? ??? ???????? ??? ????? "? ???? recompiled ??????? ?????? ?? ??? ??? ???? ??????. ?????? ?? ??? ?? ?? ????? ??? ???????? ?? ???? ??? ????? ????????? ??????? ?? recompilation.

?? ???? ???????? ????? ????????? ???????? ??????:
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
				
?? ??? ??????? ??? ??? ??????? ??????? ?? ???? ????? ??????? SQL ?? ?? ????? ??????? ??????:
+---------------------------------------------------+
| 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                  | 
+---------------------------------------------------+
				
????????? ?????? SET ?? ?? ?? ?????? ??? ???????? ????? ????? ??? ???????. ?????? ?? ????? ??????? ?????? ???????? ??? ??? ????? ?? recompilation ??? ????? recompilation ?? ????? SET.

??????? ????? ?? recompilation ?????? ?????? ??? ??????? ?? ?? ??? ????? SET ??? ?? ????? ????. ?????? ??? ???? ?? ?????????? ???? ??????? ??????? ?? "????? ??????? ?? Microsoft:
294942PRB: ?? ????? CONCAT_NULL_YIELDS_NULL SET ????? ??????? ?????
??? ??? ?? ???? ? ????? SET ?? ???? ?????? ????? ???? ??????? ??? ??? ??????? ?????? ?????? ???? ????? ???? ????? ?????? ?? ???:
Set ANSI_DEFAULTS OFF

exec test_recompile
				
?????? "???? ????? ??????? SQL" ????? ??? ????? SP:Recompile ????.

???? ?????? ?????? ??? ???????? SET ??????? ? ?? ?? ???? ????? ????? SET ?? ????? ???? ??? ?????:
?? ??? ??????????? ??? ??????
????? ?????????
quoted_identifier ????????
arithabort ?????????
ansi_null_dflt_on ?????????
ansi_defaults ?????????
ansi_warnings ?????????
ansi_padding ?????????
concat_null_yields_null ?????????
numeric_roundabort ????????
nocount ????????
rowcount ????????
xact_abort ????????
implicit_transactions ????????
arithignore ????????
lock_timeout ????????
fmtonly ????????

?????

308737INF: ??? ?????? ??? Recompilation ?? ????? SP:Recompile

?????? ??? ??????? ??? ??????? SQL Server ???? ???? ????? ??????? ? "??? SQL Server ???????".

???????

???? ???????: 243586 - ????? ??? ??????: 21/????/1428 - ??????: 2.5
????? ???
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Desktop Engine (Windows)
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Standard Edition
????? ??????: 
kbmt kbinfo KB243586 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????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