DBCC SHOWCONTIG(Transact-SQL)

적용 대상: SQL Server Azure SQL Managed Instance

지정한 테이블이나 뷰의 데이터와 인덱스에 대한 조각화 정보를 표시합니다.

적용 대상: SQL Server 2008(10.0.x) 이상 버전

Transact-SQL 구문 표기 규칙

Syntax

DBCC SHOWCONTIG
[ (
    { table_name | table_id | view_name | view_id }
    [ , index_name | index_id ]
) ]
    [ WITH
        {
         [ , [ ALL_INDEXES ] ]
         [ , [ TABLERESULTS ] ]
         [ , [ FAST ] ]
         [ , [ ALL_LEVELS ] ]
         [ NO_INFOMSGS ]
         }
    ]

인수

table_name | table_id | view_name | view_id

조각화 정보를 검사할 테이블이나 뷰입니다. 이 인수를 지정하지 않으면 현재 데이터베이스의 모든 테이블과 인덱싱된 뷰를 검사합니다. 테이블이나 뷰 ID를 구하려면 OBJECT_ID 함수를 사용합니다.

index_name | index_id

조각화 정보를 검사할 인덱스입니다. 이 인수를 지정하지 않으면 지정한 테이블이나 뷰의 기본 인덱스를 처리합니다. 인덱스 ID를 가져오려면 sys.indexes 카탈로그 뷰를 사용합니다.

WITH

DBCC 문에서 반환되는 정보 유형에 대한 옵션을 지정합니다.

FAST

인덱스의 고속 검색과 최소 정보 출력 수행 여부를 지정합니다. 고속 검사는 인덱스의 리프 또는 데이터 수준 페이지를 읽지 않습니다.

ALL_INDEXES

특정 인덱스가 지정된 경우라도 지정한 테이블과 뷰의 모든 인덱스에 대한 결과를 표시합니다.

TABLERESULTS

결과를 추가 정보와 함께 행 집합으로 표시합니다.

ALL_LEVELS

이전 버전과의 호환성을 위해서만 유지됩니다. ALL_LEVELS가 지정되더라도 인덱스 리프 수준 또는 테이블 데이터 수준만 처리됩니다.

NO_INFOMSGS

심각도가 0에서 10 사이인 모든 정보 메시지를 표시하지 않습니다.

결과 집합

다음 표에서는 결과 집합에 표시되는 정보를 설명합니다.

table_id와 FAST를 지정하면 DBCC SHOWCONTIG는 다음 열만 가진 결과 집합을 반환합니다.

• 검색한 페이지
• 익스텐트 스위치
• 검색 밀도[최적:실제]
• 익스텐트 검색 조각화 상태
• 논리 검색 조각화 상태

TABLERESULTS를 지정하면 DBCC SHOWCONTIG는 다음 열을 반환하고 이전 테이블에 설명된 9개의 열도 반환합니다.

WITH TABLERESULTS와 FAST를 지정할 때의 결과 집합은 다음 열이 null 값을 가진다는 점을 제외하면 WITH TABLERESULTS를 지정할 때와 동일합니다.

설명

DBCC SHOWCONTIG 문은 index_id를 지정했을 때 지정된 인덱스의 리프 수준에서 페이지 체인을 트래버스합니다. table_id만 지정하거나 index_id가 0인 경우에는 지정한 테이블의 데이터 페이지를 검색합니다. 이 작업에는 내재된 공유(IS) 테이블 잠금만 필요합니다. 이런 방법으로 배타적(X) 테이블 잠금이 필요한 경우를 제외하고 모든 업데이트와 삽입을 수행할 수 있으므로 실행 속도 및 동시성의 유지 그리고 반환되는 통계 데이터 수 사이에서 절충점을 찾을 수 있습니다. 그러나 조각화를 평가하는 데만 이 명령을 사용하는 경우에는 최상의 성능을 얻으려면 WITH FAST 옵션을 사용하는 것이 좋습니다. 고속 검사는 인덱스의 리프 또는 데이터 수준 페이지를 읽지 않습니다. WITH FAST 옵션은 힙에 적용되지 않습니다.

제한

DBCC SHOWCONTIG는 ntext, text 및 image 데이터 형식의 데이터를 표시하지 않습니다. 이것은 텍스트와 이미지 데이터를 저장하는 텍스트 인덱스가 더 이상 존재하지 않기 때문입니다.

또한 DBCC SHOWCONTIG는 몇몇 새로운 기능을 지원하지 않습니다. 다음은 그 예입니다.

• 지정된 테이블이나 인덱스가 분할되는 경우 DBCC SHOWCONTIG는 지정된 테이블이나 인덱스의 첫 번째 파티션만 표시합니다.
• DBCC SHOWCONTIG는 행 오버플로 스토리지 정보와 nvarchar(max), varchar(max), varbinary(max), xml 등의 새로운 행 외부 데이터 형식을 표시하지 않습니다.
• 공간 인덱스는 DBCC SHOWCONTIG에서 지원되지 않습니다.

모든 새로운 기능은 sys.dm_db_index_physical_stats(Transact-SQL) 동적 관리 뷰에서 완전히 지원됩니다.

테이블 조각화

DBCC SHOWCONTIG는 테이블의 조각화 여부를 확인합니다. 테이블 조각화는 테이블에서 INSERT, UPDATE, DELETE 문 등의 데이터 수정 문을 처리할 때 발생합니다. 이러한 수정 내용은 테이블의 행에서 균등하게 분산되지 않으므로 각 페이지의 사용률 수준이 시간에 따라 달라지게 됩니다. 테이블의 일부 또는 전부를 검색하는 쿼리의 경우 이런 테이블 조각화로 인해 읽어야 하는 페이지의 수가 늘어날 수 있으며 이는 데이터의 병렬 검색에 방해가 됩니다.

인덱스가 심하게 조각화된 경우에는 다음의 방법으로 조각화를 줄일 수 있습니다.

• 클러스터형 인덱스를 삭제한 다음 다시 만듭니다.

  클러스터형 인덱스를 다시 만들면 데이터가 재구성되어 완전한 데이터 페이지가 만들어집니다. 사용률 수준은 CREATE INDEX의 FILLFACTOR 옵션으로 구성할 수 있습니다. 이 방법의 단점은 삭제하거나 다시 만드는 동안 인덱스가 오프라인 상태라는 것과 작업의 원자성에 있습니다. 인덱스 생성이 중단되면 그 인덱스는 다시 생성되지 않습니다.

• 인덱스의 리프 수준 페이지를 논리적인 순서로 다시 정렬합니다.

  ALTER INDEX...REORGANIZE를 사용하여 인덱스의 리프 수준 페이지를 논리적인 순서로 다시 정렬합니다. 이 작업은 온라인 작업이므로 문이 실행 중일 때 인덱스를 사용할 수 있습니다. 또한 이 작업은 완료된 작업의 손실 없이 중단할 수 있습니다. 이 방법의 단점은 클러스터형 인덱스를 삭제하거나 다시 만드는 방법만큼은 데이터를 잘 구성하지 못한다는 점입니다.

• 인덱스를 다시 작성합니다.

  ALTER INDEX를 REBUILD와 함께 사용하여 인덱스를 다시 빌드합니다. 자세한 내용은 ALTER INDEX(Transact-SQL)를 참조하세요.

결과 집합의 Avg. Bytes free per page 및 Avg. Page density (full) 통계는 인덱스 페이지의 사용률을 나타냅니다. 임의 삽입이 많지 않은 인덱스의 경우 페이지당 사용 가능한 평균 바이트 수 값은 낮아야 하고 평균 페이지 밀도(전체) 값은 높아야 합니다. 인덱스를 삭제하고 FILLFACTOR 옵션을 지정하여 다시 만들면 통계 결과를 개선할 수 있습니다. 또한 FILLFACTOR를 고려하여 ALTER INDEX에 REORGANIZE를 사용하면 인덱스가 압축되어 통계 결과가 개선됩니다.

인덱스의 조각화 수준은 다음 방법으로 확인할 수 있습니다.

• 익스텐트 스위치와 검색한 익스텐트의 값을 비교합니다.

  익스텐트 스위치의 값은 검색한 익스텐트의 값에 가능한 근접해야 합니다. 이 비율은 검색 밀도 값으로 계산됩니다. 이 값은 높을수록 좋으며 인덱스 조각화를 줄여 개선할 수 있습니다.

  참고

  인덱스가 여러 파일에 걸쳐 있으면 이 방법을 사용할 수 없습니다.

• 논리 검색 조각화 상태와 익스텐트 검색 조각화 상태 값을 파악하는 방법입니다.

  논리 검색 조각화 상태와 그 정도는 덜하지만 익스텐트 검색 조각화 상태 값은 테이블의 조각화를 가장 잘 나타내는 지표입니다. 이 두 값은 모두 0%부터 10%까지의 값이 될 수 있지만 가능한 0에 가까워야 합니다.

  참고

  인덱스가 여러 파일에 걸쳐 있으면 익스텐트 검색 조각화 상태 값이 높아집니다. 이 값을 줄이려면 인덱스 조각화를 줄여야 합니다.

사용 권한

사용자는 테이블을 소유하거나 sysadmin 고정 서버 역할, db_owner 고정 데이터베이스 역할 또는 db_ddladmin 고정 데이터베이스 역할의 멤버여야 합니다.

예

A. 테이블의 조각화 정보 표시

다음 예에서는 Employee 테이블의 조각화 정보를 표시합니다.

USE AdventureWorks2022;
GO
DBCC SHOWCONTIG ('HumanResources.Employee');
GO

B. OBJECT_ID를 사용하여 테이블 ID 가져오기 및 sys.indexes를 사용하여 인덱스 ID 가져오기

다음 예에서는 OBJECT_ID 및 sys.indexes 카탈로그 뷰를 사용하여 AdventureWorks2022데이터베이스의 Production.Product테이블에서 AK_Product_Name 인덱스의 테이블 ID 및 인덱스 ID를 가져옵니다.

USE AdventureWorks2022;
GO
DECLARE @id INT, @indid INT
SET @id = OBJECT_ID('Production.Product');

SELECT @indid = index_id
FROM sys.indexes
WHERE object_id = @id
   AND name = 'AK_Product_Name';

DBCC SHOWCONTIG (@id, @indid);
GO

C. 테이블에 대한 생략된 결과 집합 표시

다음 예에서는 AdventureWorks2022데이터베이스의 Product테이블에 대해 생략된 결과 집합을 반환합니다.

USE AdventureWorks2022;
GO
DBCC SHOWCONTIG ('Production.Product', 1) WITH FAST;
GO

D. 데이터베이스의 모든 테이블의 모든 인덱스에 대한 전체 결과 집합 표시

다음 예에서는 AdventureWorks2022 데이터베이스에 있는 모든 테이블의 모든 인덱스에 대한 전체 테이블 결과 집합을 반환합니다.

USE AdventureWorks2022;
GO
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;
GO

E. DBCC SHOWCONTIG 및 DBCC INDEXDEFRAG를 사용하여 데이터베이스에서 인덱스 조각 모음 수행

다음 예에서는 데이터베이스에서 선언된 임계값 이상으로 조각화된 모든 인덱스에 조각 모음을 수행하는 간단한 방법을 보여 줍니다.

/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename VARCHAR(255);
DECLARE @execstr   VARCHAR(400);
DECLARE @objectid  INT;
DECLARE @indexid   INT;
DECLARE @frag      DECIMAL;
DECLARE @maxfrag   DECIMAL;
  
-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;
  
-- Declare a cursor.
DECLARE tables CURSOR FOR
   SELECT TABLE_SCHEMA + '.' + TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE';
  
-- Create the table.
CREATE TABLE #fraglist (
   ObjectName CHAR(255),
   ObjectId INT,
   IndexName CHAR(255),
   IndexId INT,
   Lvl INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity DECIMAL,
   BestCount INT,
   ActualCount INT,
   LogicalFrag DECIMAL,
   ExtentFrag DECIMAL);
  
-- Open the cursor.
OPEN tables;
  
-- Loop through all the tables in the database.
FETCH NEXT
   FROM tables
   INTO @tablename;
  
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
   FETCH NEXT
      FROM tables
      INTO @tablename;
END;
  
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
  
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
  
-- Open the cursor.
OPEN indexes;
  
-- Loop through the indexes.
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag;
  
WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%';
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')';
   EXEC (@execstr);
  
   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag;
END;
  
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
  
-- Delete the temporary table.
DROP TABLE #fraglist;
GO

추가 정보

• ALTER INDEX(Transact-SQL)
• CREATE INDEX(Transact-SQL)
• DBCC(Transact-SQL)
• DROP INDEX(Transact-SQL)
• sys.dm_db_index_physical_stats(Transact-SQL)
• OBJECT_ID(Transact-SQL)
• sys.indexes(Transact-SQL)