[MSSQL] 7. 전체 인덱스 사용 여부 조회

sys.indexes DMV를 이용하여 각 DB별로 접속하여 조회하는 쿼리입니다.

아래 쿼리로 대략적으로 인덱스가 사용되고 있는지를 확인해볼 수 있습니다. 

 

만약 인덱스가 불필요로 나와 삭제하려는 경우 세션 모니터링 등을 통해 삭제하려는 인덱스가 정말로 미사용 중인지 다시 한번 체크해야합니다.

그 이유는 sys.dm_db_index_usage_stats DMV가 메모리 상에만 존재하기 때문에(저장이 안되기 때문에)

DB가 재기동된지 얼마 지나지 않았거나, 서버 메모리가 부족해 해당 DMV의 성능 정보가 Cached-Out되었으면 조회가 불가능하기 때문입니다. 

 

IF OBJECT_ID(N'tempdb..#DB') IS NOT NULL
BEGIN
    DROP TABLE #DB
END
 
CREATE TABLE #DB (name sysname, dbid smallint)
 
INSERT INTO #DB (name, dbid)
SELECT name, dbid FROM master.dbo.sysdatabases
WHERE name not in ('master', 'msdb', 'model')
GO
 
 
DECLARE @dbname sysname
DECLARE @min_dbid smallint
DECLARE @SQL varchar(8000)
 
IF OBJECT_ID(N'tempdb..#TABLEINDEX') IS NOT NULL
BEGIN
     DROP TABLE #TABLEINDEX
END
 
CREATE TABLE #TABLEINDEX (
        database_id INT
    ,   dbname sysname
    ,   SchemaName sysname
    ,   tablename sysname
    ,   indexid int
    ,   indexname varchar(500)
    ,   type_desc varchar(500)
    ,   is_primary_key INT
    ,   is_unique INT
    ,   is_unique_constraint INT
    ,   KeyColumns varchar(8000)
    ,   IncludedColumns varchar(8000)
    ,   user_seeks  BIGINT
    ,   user_scans  BIGINT
    ,   user_lookups BIGINT
    ,   user_updates BIGINT
);
 
SET @min_dbid = 4
 
WHILE (@min_dbid IS NOT NULL)
BEGIN
  SELECT @min_dbid = MIN(dbid) FROM #DB WHERE dbid > @min_dbid
  IF (@min_dbid IS NULL)  BREAK
 
  SELECT @dbname = name FROM #DB WHERE dbid = @min_dbid
 
  SET @SQL = 'USE [' + @dbname + ']; '
  SET @SQL = @SQL + '
    SELECT
        DB_ID() as database_id,
        DB_NAME() as dbName,
        (SCHEMA_NAME(t.schema_id)) AS SchemaName,
        (t.name) AS TableName,
        i.index_id,
        (i.name) AS IndexName,
        i.type_desc,
        i.is_primary_key,
        i.is_unique,
        i.is_unique_constraint AS UniqCon,
        STUFF(REPLACE(REPLACE((
            SELECT QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN '' DESC'' ELSE '''' END AS [data()]
            FROM sys.index_columns AS ic
            INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
            WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
            ORDER BY ic.key_ordinal
            FOR XML PATH
        ), ''<row>'', '', ''), ''</row>'', ''''), 1, 2, '''') AS KeyColumns,
        STUFF(REPLACE(REPLACE((
            SELECT QUOTENAME(c.name) AS [data()]
            FROM sys.index_columns AS ic
            INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
            WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
            ORDER BY ic.index_column_id
            FOR XML PATH
        ), ''<row>'', '', ''), ''</row>'', ''''), 1, 2, '''') AS KeyColumns,
        u.user_seeks,
        u.user_scans,
        u.user_lookups,
        u.user_updates
    FROM sys.tables AS t
    INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
    LEFT JOIN sys.dm_db_index_usage_stats AS u ON i.object_id = u.object_id AND i.index_id = u.index_id
    WHERE t.is_ms_shipped = 0
    AND i.type <> 0
    AND u.user_seeks IS NULL
   '
  INSERT INTO #TABLEINDEX
  EXEC (@SQL)
END
 

SELECT
    database_id AS DBID
,   dbName
,   SchemaName AS schName
,   tablename AS tabName
,   indexid AS idxID
,   IndexName AS idxName
,   type_desc AS type
,   IIF(is_primary_key = 1, 'PK', IIF(is_unique = 1, 'UK', '')) as KeyConst
,   KeyColumns AS KeyCols
,   IncludedColumns AS IncludeCols
,   user_seeks 
,   user_scans 
,   user_lookups
,   user_updates
FROM #TABLEINDEX
ORDER BY database_id, tablename, indexid
GO

 

주요 지표 내용 : 링크

user_seeks	사용자 쿼리별 검색(Seek) 수입니다.
user_scans	'seek' 조건자를 사용하지 않은 사용자 쿼리별 스캔 수입니다.
user_lookups	해당 인덱스를 통해 RID LookUP이 발생한 횟수입니다.
user_updates	사용자 쿼리별 업데이트 수입니다. 영향을 받는 실제 행이 아닌 작업 수를 나타내는 삽입, 삭제 및 업데이트 포함됩니다. 예를 들어 한 문에서 1000개의 행을 삭제하는 경우 이 개수는 1씩 증가합니다.

user_seeks이 찍혀있다면, 해당 인덱스는 현재 사용 중이라는 의미입니다. 

이와 반대로 user_seeks 값이 0이거나 NULL이면 해당 인덱스는 사용되지 않고 있을 가능성이 높습니다.

 

 

6. 전체 인덱스 용량 조회 쿼리에서 만든 #temp2 테이블을 이용하면 아래 내용도 조회가 가능합니다.

SELECT A.database_id, A.dbname, A.SchemaName, A.tablename, A.indexid, A.indexname, A.type_desc, A.KeyColumns, 
     B.[Indexsize(KB)], user_seeks,user_scans,user_lookups,user_updates
    FROM #TABLEINDEX A
        INNER JOIN #temp2 B
        ON A.database_id = B.DBID
        AND A.dbname = B.DBName
        AND A.SchemaName = B.SchemaName
        AND A.tablename = B.TableName
GO