[MSSQL] Table의 Index 별 사용 횟수 확인

 

MSSQL Server가 재시작된 이후의 Index별 사용 횟수를 조회함.

VIEW SERVER STATE 권한이 있는 sa 와 같은 계정으로 실행해야 정상 실행됨.

 

-- Table의 Index 별 사용 횟수 확인.
-- USER_SEEK : 정상적으로 INDEX만 사용하여 검색됨.
-- USER_SCANS : INDEX 내에서 SCAN됨.
-- USER_LOOKUPS : INDEX 내에서 조회됨.
-- RANGE_SCAN_COUNT : INDEX에서 범위로 찾은 후 데이터에서 다시 조회됨.
--                    INDEX KEY, PK 외 다른 조건이 붙어 있는 경우.

DECLARE @V_WORK_DATE NVARCHAR(8) = CONVERT(NVARCHAR,GETDATE(),112)
    SELECT LEFT(@V_WORK_DATE,4) + '-' + SUBSTRING(@V_WORK_DATE,5,2) + '-' + RIGHT(@V_WORK_DATE,2) AS WORK_DATE
         , OBJECT_NAME(IUS.OBJECT_ID) AS TABLE_NAME
         , I.NAME AS INDEX_NAME
         , IUS.INDEX_ID
         , IZ.INDEX_SIZE
         , IUS.USER_SEEKS
         , IUS.USER_SCANS
         , IUS.USER_LOOKUPS
         , IOS.RANGE_SCAN_COUNT
      FROM SYS.DM_DB_INDEX_USAGE_STATS AS IUS
           INNER JOIN SYS.INDEXES AS I
        ON I.OBJECT_ID = IUS.OBJECT_ID
       AND I.INDEX_ID  = IUS.INDEX_ID
           INNER JOIN SYS.DM_DB_INDEX_OPERATIONAL_STATS(DB_ID(),null,NULL,NULL) AS IOS
        ON IOS.OBJECT_ID = IUS.OBJECT_ID
       AND IOS.INDEX_ID = IUS.INDEX_ID
           INNER JOIN
           (
            SELECT I.OBJECT_ID
                 , I.INDEX_ID AS INDEX_ID
                 , SUM(AU.USED_PAGES) * 8 / 1024 AS 'INDEX_SIZE'
              FROM SYS.INDEXES I
                   INNER JOIN SYS.PARTITIONS AS P
                ON P.OBJECT_ID = I.OBJECT_ID
               AND P.INDEX_ID   = I.INDEX_ID
                   INNER JOIN SYS.ALLOCATION_UNITS AS AU
                ON AU.CONTAINER_ID = P.PARTITION_ID
             WHERE 1=1--I.object_id = OBJECT_ID(@V_TABLE_NAME)
          GROUP BY I.OBJECT_ID
                 , I.INDEX_ID
           ) AS IZ
        ON IZ.OBJECT_ID = I.OBJECT_ID
       AND IZ.INDEX_ID  = I.INDEX_ID
     WHERE 1=1 --IUS.OBJECT_ID = OBJECT_ID(@V_TABLE_NAME)
       AND IUS.DATABASE_ID = DB_ID()
    ORDER BY 1,2,4

 

자세한 사항은 msdn 참고

https://learn.microsoft.com/ko-kr/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql?view=sql-server-ver16

sys.dm_db_index_usage_stats(Transact-SQL) - SQL Server