통계를 이용한 인덱스 추천

텔레비전에 내가나왔으면 정말 좋겠네~ 정말 좋겠네

보통 메이저 데이터베이스들은 통계 기능을 지원한다.

 

새로운 프로그램만 개발하는 개발팀의 경우 필요성을 못느껴서 잘 모를수가 있는데

 

유지보수팀에서 일하다보면 표준에 맞지않는 테이블도 많고

(개발 당시 필요에 의해 만들었으나 문서나 인수인계가 안된 경우)

 

시스템 전체코드를 분석할 시간적 여유도 없는 경우가 많다.

 

그럴경우 데이터베이스에 기록된 통계 정보를 활용하여 많이 조회되는 테이블 및 컬럼을 찾아서 인덱스를 추천해 주는 쿼리를 만들어 봤다.

 

물론 이걸 걸어서 더 느려지는 경우....도 있는데(기존 인덱스를 타는게 빠른데 새로 만든 인덱스를 탄다거나)

그 경우는 뭐.. 인덱스 강제지정을 해주는 수 밖에 없다.

 

주의 사항.

내가 알기론 MS-SQL은 SQL-Server 서비스 재시작 시 초기화되는걸로 알고있다.

그러니까 서버 재부팅한지 1~2시간 밖에 안됐는데 이 쿼리로 확인하면 다 안쓰는 인덱스라고 나온다.

 

--사용률이 낮은 인덱스 찾기
SELECT 'DROP INDEX [' + B.NAME + '] ON [' + OBJECT_NAME(A.OBJECT_ID) + ']' AS [QUERY]
     , OBJECT_NAME(A.OBJECT_ID) AS [테이블 이름]
     , B.NAME AS [인덱스 이름], B.INDEX_ID AS [인덱스 ID]
     , A.USER_UPDATES AS [쓰기 횟수]
     , A.USER_SEEKS + A.USER_SCANS + A.USER_LOOKUPS AS [읽기 횟수]
     , A.USER_UPDATES - (A.USER_SEEKS + A.USER_SCANS + A.USER_LOOKUPS) AS [쓰기 - 읽기]
  FROM SYS.DM_DB_INDEX_USAGE_STATS AS A (NOLOCK)
 INNER JOIN SYS.indexes AS B (NOLOCK) ON A.OBJECT_ID = B.OBJECT_ID AND A.INDEX_ID = B.INDEX_ID
 WHERE OBJECTPROPERTY(A.OBJECT_ID, 'IsUserTable') = 1	--유저 생성 테이블만
   AND A.DATABASE_ID = DB_ID()	--현재 DB만
   AND A.USER_UPDATES > (A.USER_SEEKS + A.USER_SCANS + A.USER_LOOKUPS)
   AND B.INDEX_ID > 1
   AND (A.USER_SEEKS + A.USER_SCANS + A.USER_LOOKUPS) = 0	--0인 경우는 한번도 사용되지 않았을 경우.
 ORDER BY [쓰기 - 읽기] DESC, [쓰기 횟수] DESC, [읽기 횟수] ASC

--인덱스 생성 추천
--인덱스 생성에는 시간이 많이 소요될 수 있음. DB 사용률이 낮을때 할것(점심시간, 새벽 등)
SELECT USER_SEEKS * AVG_TOTAL_USER_COST * (AVG_USER_IMPACT * 0.01) AS INDEX_ADVANTAGE
     , MIGS.LAST_USER_SEEK AS [마지막 사용]
     , MID.STATEMENT AS [테이블]
     , ROW_NUMBER() OVER (PARTITION BY MID.STATEMENT ORDER BY (USER_SEEKS * AVG_TOTAL_USER_COST * (AVG_USER_IMPACT * 0.01)) DESC) ADX
     , MID.EQUALITY_COLUMNS AS [= 연산 컬럼], MID.INEQUALITY_COLUMNS AS [= 외 연산 컬럼], MID.INCLUDED_COLUMNS AS [INCLUDED 컬럼]
     , MIGS.UNIQUE_COMPILES, MIGS.USER_SEEKS, MIGS.AVG_TOTAL_USER_COST, MIGS.AVG_USER_IMPACT
  INTO #TEMP_ADV
  FROM SYS.DM_DB_MISSING_INDEX_GROUP_STATS AS MIGS (NOLOCK)
 INNER JOIN SYS.DM_DB_MISSING_INDEX_GROUPS AS MIG (NOLOCK) ON MIGS.GROUP_HANDLE = MIG.INDEX_GROUP_HANDLE
 INNER JOIN SYS.DM_DB_MISSING_INDEX_DETAILS AS MID (NOLOCK) ON MIG.INDEX_HANDLE = MID.INDEX_HANDLE
 WHERE MID.STATEMENT LIKE '%' + DB_NAME() + '%'  --조회할 DB
   AND USER_SEEKS * AVG_TOTAL_USER_COST * (AVG_USER_IMPACT * 0.01) > 100
 ORDER BY INDEX_ADVANTAGE DESC; 

SELECT 'CREATE INDEX IDX_'
   + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(A.테이블, DB_NAME(), ''), 'dbo', ''), '[', ''), ']', ''), '.', '')
   + '_' + CONVERT(NVARCHAR(8), GETDATE(), 112) + '_' + CAST(A.ADX AS NVARCHAR)
   + ' ON ' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(A.테이블, DB_NAME(), ''), 'dbo', ''), '[', ''), ']', ''), '.', '')
   + '(' + ISNULL(A.[= 연산 컬럼], '')
   + CASE WHEN A.[= 연산 컬럼] IS NULL OR A.[= 외 연산 컬럼] IS NULL THEN '' ELSE ',' END
   + ISNULL(A.[= 외 연산 컬럼], '') + ')'
   + CASE WHEN A.[INCLUDED 컬럼] IS NULL THEN ''
      ELSE ' INCLUDE (' + A.[INCLUDED 컬럼] + ')' END AS [QUERY]
   , A.*
  FROM #TEMP_ADV AS A

 DROP TABLE #TEMP_ADV

 

우리회사는 프로시저를 많이 사용하니까 프로시저 확인용 쿼리도

--프로시저 사용률 확인하기
SELECT SP.NAME AS [프로시저 이름]
     , SPST.EXECUTION_COUNT AS [프로시저 실행 횟수]
     , SPST.TOTAL_WORKER_TIME AS [총 CPU시간]
     , SPST.TOTAL_WORKER_TIME / SPST.EXECUTION_COUNT AS [1회당 총 CPU시간]
     , SPST.TOTAL_ELAPSED_TIME AS [총 경과시간]
     , SPST.TOTAL_ELAPSED_TIME / SPST.EXECUTION_COUNT AS [1회당 총 경과시간]
     , SPST.CACHED_TIME
  FROM SYS.PROCEDURES AS SP
 INNER JOIN SYS.DM_EXEC_PROCEDURE_STATS AS SPST ON SP.OBJECT_ID = SPST.OBJECT_ID
 ORDER BY SPST.EXECUTION_COUNT DESC