DBCC INDEXDEFRAG(Transact-SQL)

적용 대상: SQL Server Azure SQL Managed Instance

지정된 테이블 또는 뷰의 인덱스를 조각 모음합니다.

Important

SQL Server의 이후 버전에서는 이 기능이 제거됩니다. 새 개발 작업에서는 이 기능을 사용하지 않도록 하고, 현재 이 기능을 사용하는 애플리케이션은 수정하세요. 대신 ALTER INDEX를 사용합니다.

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

Transact-SQL 구문 표기 규칙

구문

DBCC INDEXDEFRAG
(
    { database_name | database_id | 0 }
    , { table_name | table_id | view_name | view_id }
    [ , { index_name | index_id } [ , { partition_number | 0 } ] ]
)
    [ WITH NO_INFOMSGS ]

인수

database_name | database_id | 0

조각 모음할 인덱스가 들어 있는 데이터베이스입니다. 0을 지정하면 현재 데이터베이스가 사용됩니다. 데이터베이스 이름은 식별자에 대한 규칙을 준수해야 합니다.

table_name | table_id | view_name | view_id

조각 모음할 인덱스가 들어 있는 테이블 또는 뷰입니다. 테이블 및 뷰 이름은 식별자 규칙을 따라야 합니다.

index_name | index_id

조각 모음할 인덱스의 이름 또는 ID입니다. 이 인수를 지정하지 않으면 지정한 테이블이나 뷰의 모든 인덱스를 조각 모음합니다. 인덱스 이름은 식별자에 대한 규칙을 따라야 합니다.

partition_number | 0

조각 모음할 인덱스의 파티션 번호입니다. 지정하지 않거나 0을 지정하면 지정한 인덱스의 모든 파티션을 조각 모음합니다.

WITH NO_INFOMSGS

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

설명

DBCC INDEXDEFRAG는 인덱스 검색 성능을 향상시키기 위해 페이지의 물리적 순서가 왼쪽에서 오른쪽으로 리프 노드의 논리적 순서와 일치하도록 인덱스 리프 수준의 조각을 모읍니다.

참고

DBCC INDEXDEFRAG가 실행되면 인덱스 조각 모음이 직렬로 수행됩니다. 이는 하나의 인덱스에 대한 작업이 하나의 스레드를 통해 수행됨을 의미합니다. 병렬 처리는 수행되지 않습니다. 또한 동일한 DBCC INDEXDEFRAG 문에서 여러 개의 인덱스에 대해 작업을 실행하면 한 번에 한 인덱스씩 차례로 수행됩니다.

DBCC INDEXDEFRAG는 또한 인덱스가 만들어졌을 때 지정된 채우기 비율을 고려하여 인덱스의 페이지를 압축합니다. 압축으로 인해 생성된 빈 페이지는 제거됩니다. 자세한 내용은 인덱스의 채우기 비율 지정을 참조하세요.

인덱스가 하나 이상의 파일에 걸쳐있을 경우 DBCC INDEXDEFRAG는 한 번에 한 파일씩 조각을 모으며 파일 간에 페이지를 마이그레이션할 수 없습니다.

DBCC INDEXDEFRAG는 5분마다 예상 완료율을 보고합니다. DBCC INDEXDEFRAG는 진행 도중 언제든지 종료할 수 있으며 완료된 작업은 모두 그대로 보존됩니다.

DBCC DBREINDEX나 일반적인 인덱스 작성 작업과 달리 DBCC INDEXDEFRAG는 온라인 작업이므로 장시간 잠금 상태를 유지하지 않습니다. 그러므로 DBCC INDEXDEFRAG는 쿼리 실행이나 업데이트를 차단하지 않습니다. 조각 모음에 걸리는 시간은 조각화 정도와 관련이 있으므로 상대적으로 덜 조각난 인덱스는 새 인덱스를 만드는 것보다 빠르게 조각 모음을 실행할 수 있습니다. 심하게 조각난 인덱스는 새로 만드는 것보다 조각 모음을 실행하는 데 시간이 훨씬 많이 걸릴 수도 있습니다.

조각 모음은 데이터베이스 복구 모델 설정에 관계없이 항상 전체 로깅됩니다. 자세한 내용은 ALTER DATABASE(Transact-SQL)를 참조하세요. 심하게 조각난 인덱스를 조각 모음할 경우 인덱스 작성을 전체 로깅하는 것보다 더 큰 로그가 생성될 수 있습니다. 그러나 조각 모음은 일련의 짧은 트랜잭션으로 수행되므로 로그 백업을 자주 하거나 복구 모델 설정이 SIMPLE인 경우에는 큰 로그가 필요하지 않습니다.

제한

DBCC INDEXDEFRAG는 인덱스 리프 페이지를 섞어 놓습니다. 따라서 인덱스가 디스크의 다른 인덱스와 인터리브된 경우 해당 인덱스에 대해 DBCC INDEXDEFRAG를 실행해도 인덱스의 모든 리프 페이지가 연결되지는 않습니다. 페이지의 클러스터링을 향상시키려면 인덱스를 다시 작성하십시오.

DBCC INDEXDEFRAG는 다음 인덱스를 조각 모음하는 데 사용할 수 없습니다.

• 비활성화된 인덱스입니다.
• 페이지 잠금이 OFF로 설정된 인덱스입니다.
• 공간 인덱스입니다.

DBCC INDEXDEFRAG는 시스템 테이블에서 사용할 수 없습니다.

결과 집합

DBCC INDEXDEFRAG는 인덱스가 문에 지정되고 WITH NO_INFOMSGS를 지정하지 않은 경우 다음과 같은 결과 집합을 반환합니다. 값은 다를 수 있습니다.

Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
359           346         8
  
(1 row(s) affected)
  
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

사용 권한

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

예

A. DBCC INDEXDEFRAG를 사용하여 인덱스 조각 모음 수행

다음 예제에서는 PK_Product_ProductID 데이터베이스의 Production.Product 테이블에서 AdventureWorks2022 인덱스의 모든 파티션을 조각 모음합니다.

DBCC INDEXDEFRAG (AdventureWorks2022, 'Production.Product', PK_Product_ProductID);
GO

B. 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

추가 정보

• DBCC(Transact-SQL)
• sys.dm_db_index_physical_stats(Transact-SQL)
• CREATE TABLE(Transact-SQL)
• ALTER TABLE(Transact-SQL)
• ALTER INDEX(Transact-SQL)