저장 프로시저 재컴파일 문제 해결

기술 자료 번역 기술 자료 번역
기술 자료: 243586 - 이 문서가 적용되는 제품 보기.
이 문서는 이전에 다음 ID로 출판되었음: KR243586
모두 확대 | 모두 축소

이 페이지에서

요약

이 문서에서는 응용 프로그램에서 Microsoft SQL Server를 사용할 때 발생할 수 있는 성능 문제 중 한 가지 유형인 저장 프로시저의 런타임 시 재컴파일되는 문제에 대해 설명합니다. 성능 문제를 해결하려고 하지만 문제의 정확한 원인에 대해 확실히 모르는 경우 작업을 진행하기 전에 Microsoft 기술 자료의 다음 문서를 참조하십시오.

224587 HOWTO: SQL Server에서 응용 프로그램 성능 문제 해결
이 문서에서는 문제의 범위를 좁히기 위해 사용자가 위의 문서를 사용했고 SQL Server 프로필러에서 해당 문서에 자세히 설명된 특정 이벤트 및 데이터 열에 대해 추적했다고 가정합니다.

추가 정보

사용자가 저장 프로시저를 실행할 때 이 프로시저가 아직 캐시에서 사용할 수 있는 상태가 아니면 SQL Server는 프로시저를 로드하고 쿼리 계획을 컴파일합니다. 컴파일된 계획은 어떤 작업이 발생하여 계획을 무효로 만들고 재컴파일할 때까지 캐시에 저장되며 저장 프로시저의 다음 호출자에 의해 다시 사용됩니다. 다음 작업으로 저장 프로시저 계획이 재컴파일될 수 있습니다.
  • CREATE PROCEDURE 또는 EXECUTE 문에서 WITH RECOMPILE 절을 사용할 경우
  • 제약 조건, 기본값 또는 규칙의 추가 또는 제거를 포함하여 참조된 개체의 스키마가 변경될 경우
  • 프로시저가 참조한 테이블에 대해 sp_recompile을 실행할 경우
  • 프로시저나 프로시저가 참조하는 개체를 포함하는 데이터베이스가 복원될 경우(데이터베이스 간 작업을 수행할 경우)
  • 서버 작업이 많아서 오래된 계획이 캐시에서 제거될 경우
저장 프로시저를 재컴파일해야 하는 이러한 모든 경우는 이전 버전에서도 발생했으며 프로시저를 실행하기 전에 계획이 재컴파일되도록 했습니다. SQL Server 7.0에서는 저장 프로시저를 실행하는 동안에도 재컴파일할 수 있습니다. 이러한 새로운 동작은 최적화 프로그램이 항상 프로시저에 있는 각각의 특정 문에 대해 가능한 한 가장 좋은 계획을 세울 수 있도록 보장합니다. 다음 이벤트가 발생하면 저장 프로시저가 런타임에 재컴파일됩니다.
  • 저장 프로시저가 참조하는 테이블에서 충분한 비율의 데이터가 변경될 경우
  • 프로시저가 DDL(데이터 정의 언어)과 DML(데이터 조작 언어) 작업을 인터리브할 경우
  • 프로시저가 임시 테이블에서 특정 작업을 수행할 경우
이 문서에서는 이러한 각 원인을 자세히 설명합니다.

프로시저가 특히 클 경우 저장 프로시저의 재컴파일 비용이 재컴파일하여 얻는 이익보다 더 클 수 있습니다. 재컴파일이 발생할 때 전체 일괄 처리 또는 프로시저가 재컴파일된다는 점에 유의하십시오. 이는 성능 저하가 프로시저 또는 일괄 처리의 크기에 직접적으로 비례한다는 것을 의미합니다. 이 항목에 대한 자세한 내용은 SQL Server 온라인 설명서의 "Transact-SQL Tips" 항목을 참조하십시오.


이 문서의 다음과 같은 정보는 런타임에 저장 프로시저가 재컴파일되는 원인을 확인하는데 중점을 두고 있으며 재컴파일을 막을 수 있는 방법을 설명합니다.

최상의 방법

프로시저를 실행할 때는 저장 프로시저 이름을 소유자로 한정하는 것이 좋습니다. 이렇게 하면 현재 사용자가 기존 실행 계획을 구분하고 다시 사용하기가 쉬워집니다. 예를 들어, 데이터베이스 소유자(dbo)가 아닌 사용자가 pubs 데이터베이스에서 dbo가 소유한 저장 프로시저(이 예제에서는 myProc)를 실행하는 경우 아래 첫 번째 문 대신 두 번째 문을 사용합니다.
exec dbo.myProc
				
소유자로 한정된 저장 프로시저:
exec myProc
				
이 기술로 코딩 및 유지 관리 관점에서 다른 소유자가 다른 버전의 프로시저와 혼동하는 것을 막을 수 있으며 SQL Server에서 특정 프로시저에 대한 실행 계획에 보다 직접적으로 액세스할 수 있습니다.

소유자 이름을 한정하지 않기 때문에 SQL Server는 컴파일 코드를 입력하고 프로시저에서 COMPILE 잠금을 인식합니다. 그러나 SQL Server는 다른 이유가 적용되지 않았다는 가정 하에 새로운 계획이 필요없다고 결정하고 자격이 부족하기 때문에 이 지점에서 계획을 재컴파일하지 않습니다. 그러나 프로시저에 COMPILE 잠금을 얻는 추가적인 단계는 심각한 상황에서 블로킹 경합을 발생시킬 수 있습니다. 이 경우에 대한 자세한 내용은 Microsoft 기술 자료 문서 263889 "INF: [[COMPILE]] 잠금으로 인한 SQL 블로킹"을 참조하십시오.

database.owner.procedure와 같이 프로시저 호출을 소유자로 한정하면 컴파일 잠금을 인식할 필요가 없기 때문에 경합이 줄어듭니다.

문제 확인 및 해결

Microsoft 기술 자료의 다음 문서에는 시스템 성능을 분석하는 데 유용한 프로필러 데이터를 캡처하는 방법이 설명되어 있습니다. 아직 이 부분을 참조하지 않았으면 참조하십시오.
224587 HOWTO: SQL Server에서 응용 프로그램 성능 문제 해결

프로필러 데이터 보기

SQL Server 프로필러에는 재컴파일 발생 횟수를 모니터링하는 데 사용할 수 있는 SP:Recompile 이벤트가 포함되어 있습니다. SP:Recompile 이벤트는 실행 중에 저장 프로시저가 재컴파일될 때마다 발생합니다.
  • 이벤트 클래스별로 프로필러 추적 그룹화

    1. 파일(File) 메뉴에서 속성(Properties)을 누릅니다.
    2. 데이터 열(Data Columns) 탭에서 위로(UP) 단추를 사용하여 그룹(Groups) 머리글 아래에 있는 EventClassText를 이동하되 EventClass를 먼저 이동합니다. 아래로(DOWN) 단추를 사용하여 그룹(Groups) 머리글 아래에 있는 다른 모든 열을 제거합니다.
    3. 확인(OK)을 누릅니다.
    SP:Recompile 이벤트의 개수를 확인합니다.

    SP:Recompile 그룹을 확장하여 개별 항목의 세부 내용을 볼 수 있습니다. 이벤트의 Text 열은 재컴파일한 저장 프로시저의 이름을 나타냅니다. 여러 프로시저에서 재컴파일을 발생시키는 경우 발생 횟수에 따라 프로시저가 정렬됩니다. SP:Recompile 이벤트가 많아 CPU 사용이 많아지는 경우 재컴파일 횟수가 가장 많은 프로시저를 해결하는 데 주의하십시오. 특정 저장 프로시저 중 하나의 인스턴스에 대한 SP:Recompile 이벤트의 SPID(시스템 프로세스 ID) 및 시작 시간을 참고하고 다음 단계를 따릅니다.

    SP:Recompile 이벤트가 나타나지 않지만 여전히 성능에 문제가 있다면 Microsoft 기술 자료의 다음 문서를 참조하십시오.
    224587 HOWTO: SQL Server에서 응용 프로그램 성능 문제 해결
  • 재컴파일 이벤트를 트리거한 문 확인

    1. 파일(File) 메뉴에서 속성(Properties)을 누릅니다.
    2. 데이터 열(Data Columns) 탭에서 아래로(DOWN) 단추를 사용하여 그룹(Groups) 머리글 아래에 있는 다른 모든 열을 제거합니다.
    3. 이벤트(Events) 탭에서 SP:Starting, SP:StmtStarting, SP:RecompileSP:Completed를 제외한 모든 이벤트를 제거합니다. SP:StmtStarting 이벤트를 캡처하지 않은 경우 SP:StmtCompleted 이벤트를 대신 사용할 수 있지만 두 개 모두를 포함하지는 마십시오. 둘 모두 포함하면 확인해야 할 정보의 양이 두 배가 됩니다.
    4. 검사할 저장 프로시저 재컴파일의 특정 인스턴스를 확인했으면 필터(Filters) 탭을 사용하여 항목의 특정 SPID 및 시간 프레임으로 데이터를 제한할 수 있습니다.
    5. 확인(OK)을 누릅니다.

    재컴파일을 발생시킨 저장 프로시저 문의 SP:StmtStarted 이벤트가 발생한 직후에 SP:Recompile 이벤트가 발생합니다. 재컴파일 이벤트가 완료된 다음에는 새로 만들어진 계획으로 문이 실행됨을 나타내는 SP:StmtStarted 이벤트가 반복됩니다.

    다음 예제를 참고하십시오.
    use pubs
    go
    drop procedure RecompProc 
    go
    create procedure RecompProc as
    create table #t (a int)
    select * from #t
    go
    exec RecompProc
    						
    이 코드를 쿼리 분석기에서 실행하고 프로필러 추적에서 위의 이벤트를 확인하면 다음 시퀀스를 볼 수 있습니다.

    표 축소표 확대
    EventClassText
    SP:StartingRecompProc
    SP:StmtStartingcreate table #t (a int)
    SP:StmtStartingselect * from #t
    SP:RecompileRecompProc
    SP:StmtStartingselect * from #t
    SP:CompletedRecompProc


    재컴파일이 발생한 문이 다음과 같다는 것을 즉시 알 수 있습니다.
    select * from #t
    						
    위의 문이 SP:Recompile 이벤트의 앞뒤에 모두 나타났기 때문입니다.

    SP:StmtCompleted 이벤트만 캡처하고 SP:StmtStarting 이벤트는 캡처하지 않은 경우 다음과 같이 재컴파일을 발생시킨 문 바로 앞에 SP:Recompile 이벤트가 나타납니다.

    표 축소표 확대
    EventClassText
    SP:StartingRecompProc
    SP:RecompileRecompProc
    SP:StmtCompletedselect * from #t
    SP:CompletedRecompProc


    "select * from #t" 문의 SP:StmtCompleted 이벤트 앞에서 SP:Recompile 이벤트가 발생한 것을 볼 수 있습니다. 재컴파일은 이로 인해 발생한 것입니다. 재컴파일을 위한 새로운 쿼리 계획이 만들어지기 전까지는 문을 완료할 수 없으므로 이 경우는 타당하다고 할 수 있습니다. 이 문서의 나머지 모든 예제에서는 SP:StmtStarting 이벤트를 사용합니다. SP:StmtCompleted 이벤트만 캡처한 경우 위에서 설명한 것처럼 SP:Recompile 다음에 문이 와야 합니다.

    이 특정 저장 프로시저를 여러 번 실행할 경우 SQL Server에서는 이 프로시저에 대한 기존 계획을 다시 사용합니다. 프로시저를 처음 실행할 때만 재컴파일 이벤트가 나타납니다. 프로시저를 삭제하고 다시 만든 경우에는 스크립트를 실행할 때마다 재컴파일 이벤트가 나타납니다. 특정한 경우에 재컴파일을 해야 하는 이유는 이 문서의 "DDL(데이터 정의 언어)과 DML(데이터 조작 언어) 작업의 인터리브로 인한 재컴파일" 절에서 설명합니다. 이 예제에서는 재컴파일을 발생시키는 문을 쉽게 확인할 수 있는 방법을 보여 줍니다.

행 수정으로 인한 재컴파일

원본 쿼리 계획이 만들어진 이후 저장 프로시저가 참조한 테이블에서 많은 비율의 데이터가 변경되었으면 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 프로시저를 두 번째 실행할 경우 프로필러에서 다음과 같은 이벤트를 볼 수 있습니다.

표 축소표 확대
EventClassText
SP:StartingRowModifications
SP:StmtStartingcreate table #t (a int, b char(10))
SP:StmtStartingselect * from #t
SP:StmtStartinginsert #t select * from SomeTable
SP:StmtStartingselect count(*) from #t where a = 37
SP:RecompileRowModifications
Auto-UpdateStatsa
SP:StmtStartingselect count(*) from #t where a = 37
SP:CompletedRowModifications

참고: 첫 번째 실행에서도 "select * from #t" 문에 대해 SP:Recompile 이벤트가 표시됩니다. 이 특정한 경우에 재컴파일해야 하는 이유는 이 문서의 "DDL(데이터 정의 언어)과 DML(데이터 조작 언어) 작업의 인터리브로 인한 재컴파일" 절에서 설명합니다. 이 예제에서는 프로시저를 실행할 때마다 위에 표시된 SP:Recompile 이벤트가 발생하므로 이 이벤트에 주의하십시오.

이 예제에서 테이블이 만들어진 후에 행 수가 변경되었으므로 "select count(*) from #t where a = 37"은 프로시저를 재컴파일합니다. Auto-UpdateStats 이벤트가 있기 때문에 행 수정으로 인한 재컴파일이 발생했음을 확인할 수 있습니다. Text 열은 통계가 수정된 열을 나타냅니다.

#t 테이블을 만들 때 행 수는 0입니다. "select count (*)" 쿼리에 대한 계획뿐만 아니라 원본 "select * from #t"에 대한 계획도 해당 행 계산과 함께 개발되었습니다. 그러나 "select count(*)"를 실행하기 전에 1,000개의 새로운 행이 #t 테이블에 추가됩니다. 충분한 양의 데이터가 변경되었기 때문에 최적화 프로그램은 이 문에 가장 효과적인 계획을 선택할 수 있도록 이 프로시저를 재컴파일합니다. 1,000개의 행을 추가하는 것은 재컴파일을 정당화할 만큼 충분히 중요한 것이므로 저장 프로시저를 실행할 때마다 이러한 재컴파일이 발생합니다.

SQL Server가 재컴파일할 계획을 선택하는 데 사용하는 알고리즘은 통계 자동 업데이트에 사용되는 알고리즘과 같습니다. 통계 자동 업데이트에 사용되는 알고리즘은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
195565 INF: SQL Server 7.0 Autostats 작업 방법
위 예제에서는 저장 프로시저가 너무 작기 때문에 재컴파일이 성능에 크게 영향을 미치지는 않습니다. 그러나 여러 개의 재컴파일이 일어나는 유사한 작업을 수행하는 대규모 저장 프로시저가 있는 경우 성능이 저하되는 것을 알 수 있습니다.

행 수정으로 인한 재컴파일을 막으려면 다음 방법을 사용하십시오.
  • sp_executesql을 사용하여 문을 실행합니다.
    이 방법이 주로 사용되는 방법입니다. sp_executesql 저장 프로시저를 사용하여 실행된 문은 저장 프로시저 계획의 일부로 컴파일되지 않습니다. 따라서 해당 문을 실행하면 SQL Server는 캐시에 있는 기존 계획을 사용하거나 런타임에 새로운 계획을 만들 수 있습니다. 어떤 경우든지 저장 프로시저를 호출하기 위한 계획은 변하지 않으며 재컴파일할 필요가 없습니다.

    EXECUTE 문은 같은 효과가 있지만 이 문은 권장하지 않습니다. EXECUTE 문을 사용하면 쿼리를 매개 변수화할 수 없기 때문에 sp_executesql을 사용하는 것만큼 효과적이지 않습니다.

    위의 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 프로시저를 두 번째 실행할 경우 프로필러에서 다음과 같은 이벤트를 볼 수 있습니다.

    표 축소표 확대
    EventClassText
    SP:StartingRowModifications2
    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:Starting
    SP:StmtStartingselect count(*) from #t where a = @a
    Auto-UpdateStatsa
    SP:StmtStartingselect count(*) from #t where a = @a
    SP:Completed
    SP:CompletedRowModifications2


    RowModifications2 프로시저에 대한 SP:Recompile 이벤트는 없습니다. sp_executesql 호출 컨텍스트에 대해 완전한 SP:StartingSP:Completed 이벤트와 a 열에 대한 Auto-UpdateStats 이벤트가 있습니다. 하지만 이 호출은 저장 프로시저의 컨텍스트 외부에 있으므로 이 경우에는 RowModifications2 프로시저를 재컴파일할 필요가 없습니다.

    sp_executesql 저장 프로시저 사용에 대한 자세한 내용은 SQL Server 7.0 온라인 설명서에 있는 "sp_executesql (T-SQL)" 및 "Using sp_executesql" 항목을 참조하십시오.
  • 하위 프로시저를 사용하여 재컴파일을 발생시키는 문을 실행합니다.
    이 경우 문은 여전히 재컴파일을 발생시킬 수 있지만 대규모 호출 저장 프로시저를 재컴파일하지 않고 작은 하위 프로시저만 재컴파일합니다.
  • KEEP PLAN 옵션을 사용합니다.
    임시 테이블은 경우에 따라 기본 재컴파일 알고리즘보다 더 엄격하게 적용될 수 있는 특별한 재컴파일 규칙을 갖고 있습니다. 임시 테이블 임계값을 기본 알고리즘으로 다시 완화하기 위해 KEEP PLAN 옵션을 사용할 수 있습니다. 자세한 내용은 이 문서의 "KEEP PLAN 옵션을 사용하여 재컴파일 방지" 절을 참조하십시오.
참고:RowModifications 프로시저는 행 수정으로 인해 재컴파일되는 프로시저의 아주 간단한 예제입니다. 이 예제와 관련된 다음의 주의 사항을 검토하십시오.

  • 이 예제에서는 임시 테이블을 사용하지만 이 상황은 영구 테이블을 참조하는 저장 프로시저에도 적용됩니다. 쿼리 계획을 만든 후에 참조된 테이블에 있는 데이터를 충분히 대체한 경우 저장 프로시저가 재컴파일됩니다. 임시 테이블이 재컴파일 목적으로 고려되는 방법의 차이는 이 문서의 "KEEP PLAN 옵션을 사용하여 재컴파일 방지" 절에서 설명합니다.
  • 위의 두 프로시저를 처음 실행하면 임시 테이블 #t의 첫 번째 select에서 재컴파일이 발생합니다. 이러한 재컴파일이 발생하는 이유는 이 문서의 "DDL(데이터 저장 언어)과 DML(데이터 조작 언어) 작업의 인터리브로 인한 재컴파일" 절에서 설명합니다.
  • 이 예제에서는 단순 "select * from #t" 문이 아닌 "select count(*) from #t" 문이 사용됩니다. 지나친 재컴파일을 방지하기 위해 행 수정으로 인한 "사소한 계획"(예: select * from table)의 재컴파일은 고려되지 않습니다.

DDL(데이터 정의 언어)과 DML(데이터 조작 언어) 작업의 인터리브로 인한 재컴파일

프로시저 또는 일괄 처리에서 DDL 작업이 수행되면 프로시저 또는 일괄 처리는 DDL에 포함된 테이블에 영향을 주는 다음 DML 작업이 처음 수행될 때 재컴파일됩니다.

다음 저장 프로시저 예제를 참고하십시오.
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
				
이 코드를 쿼리 분석기에서 실행하고 프로필러 추적에서 위의 이벤트를 확인하면 다음 시퀀스를 볼 수 있습니다.

표 축소표 확대
EventClassText
SP:StartingInterleave
SP:StmtStartingcreate table t1 (a int)
SP:StmtStartingselect * from t1
SP:RecompileInterleave
SP:StmtStartingselect * from t1
SP:StmtStartingcreate index idx_t1 on t1(a)
SP:StmtStartingselect * from t1
SP:RecompileInterleave
SP:StmtStartingselect * from t1
SP:StmtStartingcreate table t2 (a int)
SP:StmtStartingselect * from t2
SP:RecompileInterleave
SP:StmtStartingselect * from t2
SP:CompletedInterleave


이 경우에는 실행하는 동안 저장 프로시저가 세 번 재컴파일됩니다. 이 경우가 발생하는 이유를 이해하려면 최적화 프로그램이 이 저장 프로시저에 대한 계획을 세우는 방식을 참고하십시오.
  1. 프로시저를 처음 컴파일하는 동안에는 테이블 t1과 t2가 없습니다. 따라서 이러한 테이블을 참조하는 쿼리에 대한 계획을 만들 수 없습니다. 이러한 계획은 실행 시 만들어져야 합니다.
  2. 프로시저가 처음 실행될 때 첫 번째 단계는 테이블 t1을 만드는 것입니다. 다음 단계는 해당되는 계획이 없는 테이블 t1에서 선택하는 것입니다. 이 지점에서 SELECT 문에 대한 계획을 세우기 위해 프로시저를 재컴파일합니다. t1에서 현재 선택뿐 아니라 인덱스 작성 후 t1에서의 선택에 대해서도 계획이 만들어집니다. t2가 아직 없기 때문에 t2에서의 선택에 대한 계획은 만들 수 없습니다.
  3. 다음 단계는 t1에 인덱스를 만드는 것입니다. 그런 다음 t1에서 또 다른 선택을 수행하면 t1은 첫 번째 재컴파일에서 만들어진 계획을 갖게 됩니다. 그러나 계획을 작성한 이후에 t1의 스키마가 변경되었기 때문에 t1에서의 선택에 대한 새로운 계획을 만들려면 프로시저를 다시 재컴파일해야 합니다. t2는 아직 없기 때문에 t에서의 선택에 대한 계획은 만들 수 없습니다.
  4. 그런 다음 테이블 t2가 만들어지고 t2에서의 선택이 실행됩니다. 문에 대한 계획은 없기 때문에 프로시저는 마지막에 재컴파일됩니다.
이러한 재컴파일은 저장 프로시저를 실행할 때마다 발생합니다. 재컴파일을 줄이려면 다음과 같이 우선 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 프로시저를 처음 실행하면 프로필러에 다음과 같은 이벤트가 나타납니다.

표 축소표 확대
EventClassText
SP:StartingNoInterleave
SP:StmtStartingcreate table t1 (a int)
SP:StmtStartingcreate index idx_t1 on t1(a)
SP:StmtStartingcreate table t2 (a int)
SP:StmtStartingselect * from t1
SP:RecompileNoInterleave
SP:StmtStartingselect * from t1
SP:StmtStartingselect * from t1
SP:StmtStartingselect * from t2
SP:CompletedNoInterleave


이 경우 모든 DDL 문은 앞부분에서 모두 완료됩니다. 최적화 프로그램은 다음과 같이 이 프로시저를 컴파일합니다.
  1. 프로시저를 처음 컴파일하는 동안에는 테이블 t1과 t2가 없습니다. 따라서 이러한 테이블을 참조하는 쿼리에 대한 계획을 만들 수 없습니다. 이러한 계획은 실행 시 만들어져야 합니다.
  2. 첫 번째 단계에서 프로시저는 테이블 t1과 t2를 만들고 t1에 인덱스를 만드는 DDL 작업을 수행합니다.
  3. 다음 단계에서는 t1에서의 첫 번째 선택을 실행합니다. 이 SELECT 문에 사용할 수 있는 계획은 없기 때문에 프로시저가 재컴파일됩니다. 모든 개체가 있기 때문에 이번에는 프로시저의 모든 SELECT 문에 대한 계획이 만들어집니다.
  4. 나머지 프로시저는 이미 만들어진 계획을 사용하여 실행됩니다. 참조된 개체가 변경되지 않았기 때문에 프로시저를 더 이상 재컴파일할 필요가 없습니다.
참고: 두 번째 이후에 실행할 때는 기존 쿼리 계획과 캐시가 사용되므로 재컴파일이 발생하지 않습니다. 모든 DDL 문이 프로시저의 시작 부분에 있도록 하기 위해 테이블을 만들거나 변경하거나 삭제하는 프로시저를 수정해야 합니다.

임시 테이블 작업으로 인한 재컴파일

저장 프로시저에서 임시 테이블을 사용하면 프로시저가 실행될 때마다 저장 프로시저가 재컴파일될 수 있습니다.

다음 요구 사항을 충족하도록 저장 프로시저를 변경하여 이러한 문제가 발생하지 않도록 합니다.
  • 임시 테이블의 이름을 포함하는 모든 문은 같은 저장 프로시저에서 만들어진 임시 테이블을 참조합니다. 호출하거나 호출되는 저장 프로시저에서 만들어진 임시 테이블 또는 EXECUTE 문이나 sp_executesql 저장 프로시저를 사용하여 실행되는 문자열에서 만들어진 임시 테이블은 참조하지 않습니다.
  • 임시 테이블의 이름을 포함하는 모든 문은 구문적으로 저장 프로시저 또는 트리거에 있는 임시 테이블 다음에 나타납니다.
  • SELECT 문이 임시 테이블을 참조하는 DECLARE CURSOR 문은 없습니다.
  • 임시 테이블의 이름을 포함하는 모든 문은 임시 테이블을 참조하는 모든 DROP TABLE 문보다 앞에 옵니다.

    DROP TABLE 문은 저장 프로시저에서 만들어진 임시 테이블에는 필요하지 않습니다. 프로시저가 완료되면 테이블은 자동으로 삭제됩니다.
  • 임시 테이블을 만드는 문(CREATE TABLE 또는 SELECT... INTO)은 IF... ELSE 또는 WHILE과 같은 흐름 제어 문에는 나타나지 않습니다.

KEEP PLAN 옵션을 사용하여 재컴파일 방지

저장 프로시저 내에서 임시 테이블을 사용하면 쿼리 최적화 프로그램의 작업이 복잡해집니다. 테이블의 행 계산 및 통계 정보는 저장 프로시저가 실행되는 기간 동안 상당히 변할 수 있습니다. 최적화 프로그램이 임시 테이블과 관련된 모든 경우에 최상의 계획을 사용할 수 있도록 하려면 재컴파일을 보다 적극적으로 실행하는 특별한 알고리즘을 개발해야 합니다. 이 알고리즘은 저장 프로시저로 만든 임시 테이블이 7번 이상 변경된 경우 다음 문이 이 임시 테이블을 참조하면 프로시저가 재컴파일되도록 합니다.

다음 예제를 참고하십시오.
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
				
이 경우 프로필러에는 두 번째 실행에 대한 다음 이벤트가 나타납니다.

표 축소표 확대
EventClassText
SP:StartinguseKeepPlan
SP:StmtStartingcreate table #t (a int)
SP:StmtStarting - The seven insert statements -
SP:StmtStartingselect count(*) from #t1
SP:RecompileuseKeepPlan
SP:StmtStartingselect count(*) from #t1
SP:CompleteduseKeepPlan

이 프로시저는 임시 테이블 #t가 7번 변경된 후 select 문이 실행될 때 재컴파일됩니다.

이러한 적극적 재컴파일은 임시 테이블의 데이터 분포가 변경되어 이를 참조하는 문에 대한 최적 쿼리 계획이 상당한 영향을 받을 수 있는 경우에 유용합니다. 그러나 임시 테이블을 크게는 아니라도 자주 수정하는 큰 프로시저의 경우 재컴파일로 인해 성능이 전반적으로 느려질 수 있습니다. 이러한 경우를 위해 SELECT 문의 KEEP PLAN 옵션을 도입하였습니다.

KEEP PLAN은 프로시저 내에서 임시 테이블이 7번 이상 변경되어 발생하는 저장 프로시저 재컴파일을 방지하고 위에서 이 문서 윗부분의 "행 수정으로 인한 재컴파일" 절에서 설명한 행 수정으로 인한 재컴파일의 표준 알고리즘으로 되돌립니다. KEEP PLAN은 모든 재컴파일을 금지하는 것이 아니라 프로시저에서 참조되는 임시 테이블이 7번 이상 변경되어 발생하는 재컴파일만 방지합니다. 위의 예제에서 저장 프로시저의 "option (KEEP PLAN)" 줄에서 주석을 제거하면 SP:Recompile 이벤트가 생성되지 않습니다.

위의 코드에 있는 "option (KEEP PlAN)" 줄에서 주석을 제거한 후 이 코드를 실행하면 프로필러에 다음과 같은 이벤트가 나타납니다.

표 축소표 확대
EventClassText
SP:StartinguseKeepPlan
SP:StmtStartingcreate table #t (a int)
SP:StmtStarting - The seven insert statements -
SP:StmtStartingselect count(*) from #t1 option (KEEP PLAN)
SP:CompleteduseKeepPlan


SP:Recompile 이벤트는 없습니다.

저장 프로시저에서 실행된 특정 SET 문으로 인한 재컴파일

다음 여섯 개의 SET 옵션은 기본적으로 ON으로 설정되어 있습니다.
  • ANSI_DEFAULTS
  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • CONCAT_NULL_YIELDS_NULL
SET 문을 실행하여 이러한 옵션을 OFF로 설정하면 저장 프로시저가 실행될 때마다 재컴파일됩니다. 이러한 옵션을 변경하면 재컴파일을 트리거한 쿼리 결과에 영향을 줄 수 있기 때문입니다.

다음 예제 코드를 참고하십시오.
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 프로필러에 다음 이벤트가 나타납니다.
표 축소표 확대
EventClassText
SP:Startingtest_recompile
SP:StmtStartingSet ANSI_DEFAULTS OFF
SP:StmtStartingselect au_lname, au_fname, au_id
SP:Recompiletest_recompile
SP:StmtStartingselect au_lname, au_fname, au_id
SP:Completedtest_recompile
SET 옵션을 위에 나열된 다섯 개의 옵션 중 하나로 바꿀 경우에도 동일한 결과가 나타납니다. 또한 SET 문 때문에 재컴파일이 발생하므로 여기서는 KEEP PLAN 옵션을 사용해도 재컴파일을 방지할 수 없습니다.

재컴파일을 방지하기 위해 권장되는 방법은 저장 프로시저에서 이러한 다섯 개의 SET 문을 사용하지 않는 것입니다. 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
294942 PRB: SET CONCAT_NULL_YIELDS_NULL로 인해 저장 프로시저가 재컴파일될 수 있다
SET 문을 실행하여 저장 프로시저와 동일한 값으로 연결 옵션을 다시 설정하면 재컴파일을 방지할 수도 있지만 이러한 방법은 권장되지 않습니다.
Set ANSI_DEFAULTS OFF

exec test_recompile
				
SQL 프로필러 추적에서 SP:Recompile 이벤트가 더 이상 표시되지 않습니다.

다음 표에서는 몇 가지 일반적인 SET 문과 저장 프로시저에서 SET 문의 변경할 경우 재컴파일이 발생하는지 여부를 보여 줍니다.
표 축소표 확대
Set 문재컴파일
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아니요

참조

308737 SP:Recompile 이벤트에서 재컴파일 원인을 확인하는 방법

SQL Server 프로필러 사용에 대한 자세한 내용은 SQL Server 온라인 설명서를 참조하십시오.



?Microsoft 제품 관련 기술 전문가들과 온라인으로 정보를 교환하시려면 Microsoft 뉴스 그룹에 참여하시기 바랍니다.

속성

기술 자료: 243586 - 마지막 검토: 2005년 10월 19일 수요일 - 수정: 2.2
본 문서의 정보는 다음의 제품에 적용됩니다.
  • 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
키워드:?
kbinfo KB243586

피드백 보내기

 

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