with SQL Server

오늘 소개드릴 DMV를 이용해서 누락된 인덱스 찾기!! 입니다.

SELECT  user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )

AS [index_advantage] ,

        migs.last_user_seek ,

        mid.[statement] AS [Database.Schema.Table] ,

        mid.equality_columns ,

        mid.inequality_columns ,

        mid.included_columns ,

        migs.unique_compiles ,

        migs.user_seeks ,

        migs.avg_total_user_cost ,

        migs.avg_user_impact

FROM    sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )

        INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK )

           ON migs.group_handle = mig.index_group_handle

        INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK )

           ON mig.index_handle = mid.index_handle

WHERE   mid.database_id = DB_ID()

ORDER BY index_advantage DESC

위 결과에서 보시면 인덱스 누락으로 인한 사용 비용이 제일 높은 1, 2위가

TConsign(위탁판매) 테이블의 seller_id와 item_kind 컬럼입니다.

아! 아래 3위 아래는 시간을 보시면 아시겠지만 정기점검 중 ad-hoc으로 일괄 처리된 사항이니까

실 서비스와는 상관없는 내용이니. Pass하죠~

결과가 확인 됐으니 이제 분석을 하고 튜닝을 해볼까요?

첫번째 seller_id 컬럼부터 확인해 보겠습니다.

DMV에 등록된걸 봐서는 분명 프로시저 어디선가 필터링 조건일 겁니다.

그래서 어디어디서 쓰나 찾아보니 아래 프로시저에서 실제 필터링 조건으로 사용 중입니다.

-       ConsignBuyItem --Seller_id 필터링조건

-       ConsignGetItemList --Seller_id 필터링조건

-       ConsignUnregistItem --Seller_id 필터링조건

-       GetConsignItemList --Seller_id Join 조건절

그러므로 Seller_id 컬럼은 다음주 점검 때 인덱스 생성 예정입니다.

재밌는건 두번째 item_kind 컬럼입니다.

Item_kind 컬럼은 이미 인덱스가 생성되어 있고 사용하는 프로시저는

TConSign_Get_List --item_kind 필터링 조건절 입니다.

그런데 어쩔 땐 인덱스를 안탑니다.. 왜 그럴까요? 뭐 옵티마이저가 타지 말라고 했겠죠..

그럼 왜 인덱스가 있는데 사용을 안 했을까요..

인덱스를 타지 않을 때와 인덱스를 강제로 태웠을 때 실행계획을 보시죠~

1.     인덱스 타지 않고 풀 스캔

2.     강제로 인덱스 사용

비용은 안 나왔지만 강제로 인덱스 사용을 했을 때가 Key Lookup이 발생해서

풀 스캔 때보다 증가합니다. 역시 똑똑한 옵티마이저네요..ㅎㅎ

Key Lookup은 오늘 주제와 관계 없으므로 다음에 기회가 되면 알아보도록 하죠

결론을 말씀 드리면 옵티마이저는 인덱스가 존재하는 컬럼을 필터링 할때에도

필터링할 값이 선택도가 높으냐 낮으냐를 미리 계산한 뒤 인덱스를 태울지 안 태울지 결정합니다.

위 실행계획 처럼 선택도가 낮은 필터링 조건은 그냥 한번에 풀스캔에서 찾으면 되지

인덱스를 태워서 하나하나 루프조인으로 풀리는게 더 비용이 많이 소모되기 때문입니다.

Reference : SQL Server DMV StarterPack