#title DB의 모든 SP 재컴파일하기

인덱스를 추가하거나 인덱싱된 열의 데이터를 변경하는 등의 동작을 수행하면 데이터베이스가 변경되기 때문에 데이터베이스 테이블에 액세스할 때 사용한 원래 쿼리 계획을 다시 컴파일하여 최적화해야 합니다. 이 최적화는 Microsoft SQL Server를 다시 시작한 후 저장 프로시저를 처음 실행하면 자동으로 수행됩니다. 저장 프로시저에서 사용하는 기본 테이블이 변경될 때도 최적화가 자동으로 수행됩니다. __그러나 저장 프로시저의 성능을 높이기 위해 새 인덱스를 추가했을 경우에는 Microsoft SQL Server를 다시 시작하고 저장 프로시저를 실행하기 전까지는 최적화가 수행되지 않습니다.__ 이 경우 다음에 저장 프로시저를 실행할 때 강제로 다시 컴파일되도록 하면 유용합니다. 

저장 프로시저를 강제로 다시 컴파일하는 또 다른 이유는 필요한 경우 저장 프로시저 컴파일의 "매개 변수 스니핑" 동작을 막기 위한 것입니다. SQL Server에서 저장 프로시저를 실행하면 저장 프로시저 컴파일에 사용되는 모든 매개 변수 값이 쿼리 계획 생성 시 포함됩니다. 이러한 매개 변수 값이 나중에 저장 프로시저를 호출할 때 사용되는 일반적인 값을 나타낼 경우 저장 프로시저가 컴파일되고 실행될 때마다 이 쿼리 계획을 이용할 수 있습니다. 그러나 그렇지 않은 경우에는 성능이 저하될 수 있습니다.

'''참고:'''
SQL Server 2008에서는 저장 프로시저를 문 수준으로 다시 컴파일할 수 있습니다. SQL Server 2008에서는 저장 프로시저를 다시 컴파일할 때 전체 프로시저가 아닌 다시 컴파일이 필요한 문만 컴파일됩니다. 따라서 SQL Server에서는 쿼리 계획을 재생성할 때 다시 컴파일된 문에 존재하는 이러한 매개 변수 값을 사용할 수 있습니다. 이러한 매개 변수 값은 저장 프로시저에 처음 전달된 값과 다를 수 있습니다.

{{{
set statistics io off

declare @sql nvarchar(4000);
declare cur cursor for
	select 
		'exec sp_recompile ''' + schema_name(schema_id) + '.' + name + '''' exe
	from sys.all_objects
	where type = 'P'
	and object_id > 0;

open cur;
fetch next from cur into @sql;
while @@fetch_status not in (-1, -2)
begin
	--exec(@sql);
	print @sql;
	fetch next from cur into @sql;
end
close cur;
deallocate cur;
}}}
----
제가 찾던 바로 그 기능이네요..
SP_DEPENDS 를 통해 의존도의 신뢰성을 믿을 수 있는지 판별하고 싶은데, 간혹 SP가 생성된 후 해당 SP가 참조하는 특정 테이블이 DROP 된 후 다시 CREATE 됐을 때는 OBJECT_ID가 달라져 SP_DEPENDS에서 잡지 못하는 경우가 발생하더군요.. 이를 방지하기 위해 SP_DEPENDS 실행 전 전체 SP나 VIEW 를 재컴파일 할 필요가 있을 때 유용하겠네요. 감사합니다 ^^ -- 민경태 2012-07-09 18:16:21
----
허허.. 이걸 무슨 '기능'씩이나요..
귀차니즘의 산물입니다. -- 이재학 2012-07-09 18:25:07