저장 프로시저 다시 컴파일
인덱스를 추가하거나 인덱싱된 열의 데이터를 변경하는 등의 동작을 수행하면 데이터베이스가 변경되기 때문에 데이터베이스 테이블에 액세스할 때 사용한 원래 쿼리 계획을 다시 컴파일하여 최적화해야 합니다. 이 최적화는 SQL Server를 다시 시작한 후 저장 프로시저를 처음 실행하면 자동으로 수행됩니다. 저장 프로시저에서 사용하는 기본 테이블이 변경될 때도 최적화가 자동으로 수행됩니다. 그러나 저장 프로시저의 성능을 높이기 위해 새 인덱스를 추가했을 경우에는 SQL Server를 다시 시작하고 저장 프로시저를 실행하기 전까지는 최적화가 수행되지 않습니다. 이 경우 다음에 저장 프로시저를 실행할 때 강제로 다시 컴파일되도록 하면 유용합니다.
저장 프로시저를 강제로 다시 컴파일하는 또 다른 이유는 필요한 경우 저장 프로시저 컴파일의 "매개 변수 스니핑" 동작을 막기 위한 것입니다. SQL Server에서 저장 프로시저를 실행하면 저장 프로시저 컴파일에 사용되는 모든 매개 변수 값이 쿼리 계획 생성 시 포함됩니다. 이러한 매개 변수 값이 나중에 저장 프로시저를 호출할 때 사용되는 일반적인 값을 나타낼 경우 저장 프로시저가 컴파일되고 실행될 때마다 이 쿼리 계획을 이용할 수 있습니다. 그러나 그렇지 않은 경우에는 성능이 저하될 수 있습니다.
SQL Server 2008 R2에서는 저장 프로시저를 문 수준으로 다시 컴파일할 수 있습니다. SQL Server 2008 R2에서는 저장 프로시저를 다시 컴파일할 때 전체 프로시저가 아닌 다시 컴파일이 필요한 문만 컴파일됩니다. 따라서 SQL Server에서는 쿼리 계획을 재생성할 때 다시 컴파일된 문에 존재하는 이러한 매개 변수 값을 사용할 수 있습니다. 이러한 매개 변수 값은 저장 프로시저에 처음 전달된 값과 다를 수 있습니다.
저장 프로시저의 다시 컴파일 강제 수행
SQL Server에는 저장 프로시저를 강제로 다시 컴파일하는 세 가지 방법이 있습니다.
sp_recompile 시스템 저장 프로시저는 저장 프로시저가 다음에 실행될 때 저장 프로시저를 다시 컴파일합니다. 프로시저가 다음에 실행될 때 기존 계획을 프로시저 캐시에서 삭제하고 새 계획이 생성되도록 하여 이 작업을 수행합니다.
저장 프로시저 정의에 WITH RECOMPILE 옵션을 지정하여 저장 프로시저를 만들면 SQL Server가 이 저장 프로시저의 계획을 캐시하지 않습니다. 이 저장 프로시저는 실행될 때마다 다시 컴파일됩니다. 저장 프로시저를 실행할 때마다 값이 많이 다른 매개 변수를 사용하기 때문에 다른 실행 계획이 만들어진다면 WITH RECOMPILE 옵션을 사용하십시오. 이 옵션은 일반적으로 사용되지 않으며, 저장 프로시저가 실행될 때마다 다시 컴파일되어야 하기 때문에 실행 속도가 느려집니다.
전체 저장 프로시저 대신 저장 프로시저 내의 개별 쿼리만 다시 컴파일하려면 다시 컴파일할 각 쿼리 내에 RECOMPILE 쿼리 힌트를 지정합니다. 이 동작은 이 섹션의 앞부분에 설명된 SQL Server의 문 수준 다시 컴파일 동작과 비슷하지만 문을 컴파일할 때 RECOMPILE 쿼리 힌트는 저장 프로시저의 현재 매개 변수 값을 사용하는 동시에 저장된 프로시저 내의 지역 변수 값도 사용합니다. 저장 프로시저에 속하는 쿼리의 하위 집합에서 불규칙하거나 임시적인 값을 사용할 때 이 옵션을 사용합니다. 자세한 내용은 쿼리 힌트(Transact-SQL)를 참조하십시오.
저장 프로시저를 실행할 때 WITH RECOMPILE 옵션을 지정하면 저장 프로시저를 다시 컴파일할 수 있습니다. 제공하는 매개 변수가 불규칙하거나 저장 프로시저를 만든 다음 데이터가 많이 변경되었을 때 이 옵션을 사용하십시오.
[!참고]
저장 프로시저가 참조하는 개체가 삭제되거나 이름이 바뀌면 저장 프로시저가 실행될 때 오류가 반환됩니다. 그러나 저장 프로시저에서 참조하는 개체를 같은 이름의 개체로 바꾼 경우에는 저장 프로시저를 다시 만들지 않고 실행할 수 있습니다.
저장 프로시저가 다음에 실행될 때 다시 컴파일하려면