인덱스 조각화(단편화) 정보를 조회하는 뷰 2

앞서 dbo.idxinfo_v 라는 이름으로 인덱스 정보를 조회하는 뷰를 소개한 바 있다.
본 포스트에서는 dbo.idxinfo_v2 라는 이름의 인덱스 조각화 정보도 함께 조회해 주는 뷰를 소개하겠다.

dbo.idxinfo_v2 는 dbo.idxinfo_v 에 sys.dm_db_index_physical_stats 동적 관리 함수에서 리턴된 일부 정보를 포함한 것이라고 생각하면 되겠다. 그리고 인덱스 조각화에 대한 추가적인 정보가 필요하면 이 뷰를 수정하여 사용하기 바란다.

 

Index 조각화정보를 조회하는 뷰를 작성하는 스크립트

DROP VIEW [dbo].[idxinfo_v2] GO CREATE VIEW [dbo].[idxinfo_v2] AS SELECT        CAST(SERVERPROPERTY('MachineName') AS VARCHAR(20)) AS [Hostname]      , DB_NAME(DB_ID()) AS [DBName]      , [u].[name]       AS [Owner]      , [o].[name]       AS [TableName]      , [i].[indid]      AS [IndexID]      , CASE [i].[name]  WHEN [o].[name] THEN '** NONE **'                         ELSE [i].[name] END AS [IndexName]      , CASE [i].[indid] WHEN 1 THEN 'CLUSTERED'                         WHEN 0 THEN 'HEAP'                         ELSE 'NONCLUSTERED' END AS [ClusterType]      , CASE WHEN ([i].[status] & 2048) > 0 THEN 'PRIMARY KEY'             WHEN ([i].[status] & (2|4096)) > 0 THEN 'UNIQUE'             ELSE '' END AS [UniqueType]      , CASE WHEN ([i].[status] & (2048)) > 0 OR (([i].[status] & (4096)) > 0 ) THEN 'CONSTRAINT'             WHEN [i].[indid] = 0 THEN ' '             ELSE 'INDEX' END AS [IndexType]      , CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 1) IS NULL THEN ''             ELSE INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid], 1) END +        CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 2) IS NULL THEN ''             ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],2) END +        CASE WHEN INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid], 3) IS NULL THEN ''             ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],3) END +        CASE WHEN INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid], 4) IS NULL THEN ''             ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],4) END +        CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 5) IS NULL THEN ''             ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],5) END +        CASE WHEN INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid], 6) IS NULL THEN ''             ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],6) END +        CASE WHEN INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid], 7) IS NULL THEN ''             ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid], 7) END +        CASE WHEN INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid], 8) IS NULL THEN ''             ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],8) END +        CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 9) IS NULL THEN ''             ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],9) END +        CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 10) IS NULL THEN ''             ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],10) END +        CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 11) IS NULL THEN ''             ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],11) END +        CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 12) IS NULL THEN ''             ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],12) END +        CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 13) IS NULL THEN ''             ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],13) END +        CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 14) IS NULL THEN ''             ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],14) END +        CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 15) IS NULL THEN ''             ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],15) END +        CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 16) IS NULL THEN ''             ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],16) END AS [AllColName]      , [i].[OrigFillFactor]AS [FillFactor]      , [st].[index_depth]  AS [IndexDepth]      , [st].[index_level]  AS [IndexLevel]      , [st].[page_count]   AS [TotalPageCount]      , ROUND([st].[avg_fragmentation_in_percent], 2) AS [PctFrag]  FROM [dbo].[sysobjects] o (NOLOCK)  LEFT OUTER JOIN [dbo].[sysindexes] i (NOLOCK)    ON [o].[id] = [i].[id]  LEFT OUTER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , 'LIMITED') st    ON [i].[id] = [st].[object_id]   AND [i].[indid] = [st].[index_id]      JOIN [dbo].[sysusers] u (NOLOCK)    ON [o].[uid] = [u].[uid]  WHERE [o].[type] = 'U'  AND [i].[indid] < 255  AND [o].[name] NOT IN ('dtproperties')  AND [i].[name] NOT LIKE '_WA_Sys_%' GO   GRANT SELECT ON [dbo].[idxinfo_v2] TO public GO

 

다수의 데이터베이스 서버와 데이터베이스를 관리하는 경우 호스트명과 데이터베이스 명을 함께 표시할 수 있도록 하였으며, dbo.idxinfo_v에서 추가적으로 조회할 수 있는 인덱스 정보는 다음과 같다.

 

결과 집합

컬럼 명	설명
FillAactor	해당 인덱스의 FillFactor(채우기 비율)
Index_depth	인덱스 수준의 수, 즉 인덱스의 깊이
Page_count	전체 인덱스 혹은 데이터 페이지 수
PctFrag	단편화 정도(%)

 

작성된 뷰의 사용방법은 일반적인 뷰를 조회하는 것과 같으며, 이하  sys.dm_db_index_physical_stats  에 대한  더 자세한 내용은

링크된 (BOL)을 참조하기 바란다.