DMV를 통해 인덱스 힌트를 사용한 SP 찾기

쿼리에 인덱스 힌트를 사용하게 되면, 힌트에 사용되는 인덱스에 대해 이름 변경 또는 삭제와 같은 작업이 발생하게 되면
쿼리가 실행하지 못하고 에러가 발생되어 서비스에 문제를 줄 수 있습니다.
이러한 점으로 인덱스 힌트를 사용하는 환경이라면 인덱스 작업시 많은 주의가 필요합니다.

SQL Server 2000 버젼의 경우라면 이러한 인덱스 힌트를 사용한 SP를 확인하기 위해서는 syscomments 시스템테이블에서 확인 할 수 밖에 없었습니다.
이방법도 문자열을 비교하는 형태로 찾을 수 밖에 없기에 꽤나 시간 걸리는 비효율적인 작업이었습니다

SQL Server 2005부터 추가된 DMV에 있는 XML 플랜 정보를 바탕으로 인덱스 힌트를 사용한 SP 이름을 쉽게 확인 할 수 있습니다. 

아래 XML 플랜을 보시면 ForcedIndex항목으로 인덱스 힌트 사용 유무를 판단할 수 있습니다.

<IndexScan Ordered="0" ForcedIndex="1" ForceSeek="0" NoExpandHint="0">                     <DefinedValues>                       <DefinedValue>                         <ColumnReference Database="[TEST]" Schema="[dbo]" Table="[t1]" Column="c1" />

아래의 DMV 쿼리로 확인 할 수 있습니다.

SELECT

    db_name(st.dbid)

           ,object_name(st.objectid,st.dbid)

   ,st.text

   ,qp.query_plan

FROM sys.dm_exec_cached_plans cp

    cross apply sys.dm_exec_query_plan(cp.plan_handle) qp

    cross apply sys.dm_exec_sql_text(cp.plan_handle) as st

WHERE cp.cacheobjtype = 'Compiled Plan'

AND qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";max(//p:IndexScan/@ForcedIndex)', 'float') =1

AND st.dbid IS NOT NULL

송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com