DB 개발을 하다보면 수많은 프로시져가 생성되기 마련인데 그중 상당수는 그 특정 용도로 사용 후 더이상 사용하지 않지만 삭제되지 않고 남아있는 프로시져들 일 것이다. 특히 잘 관리되지 않는 사이트라면 이런 프로시져들이 더 많을텐데 딱히 문서화가 되어있거나 하지 않은 경우 골라서 삭제하기가 힘든게 사실이다.
따라서 SQL 2005의 DMV와 시스템 뷰를 바탕으로 사용하지 않는 프로시져를 찾아보려고 한다.
일단 생성된 프로시져는 SQL Server 시작 후 최초 실행될 때 실행계획이 캐쉬에 저장된 후 재사용된다.
따라서 캐쉬에 실행계획이 없는 프로시져는 SQL Server가 (재)시작 후 한번도 실행되지 않았다고 가정할 수 있다.
위의 가정을 바탕으로 실행계획이 생성되지 않은 프로시져들을 찾는 쿼리는 아래와 같다.
select * from sys.objects as o left outer join
(
select qp.objectid from sys.dm_exec_cached_plans as cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) as qp
where qp.dbid = 6 --dbid의 경우 해당 DB의 dbid는 sys.databases 에서 확인할 수 있다.
) as ep on o.object_id = ep.objectid
where ep.objectid IS NULL and o.type = 'P'
and o.object_id IN ()
-- object_id에 범위를 준 이유는 전체 프로시져가 아닌 특정 대상 프로시져 중 사용유무를 파악하기 위함이다
order by o.name asc
