sys.dm_db_index_physical_stats(Transact-SQL)

지정한 테이블 또는 뷰의 데이터 및 인덱스에 대한 크기 및 조각화 정보를 반환합니다. 인덱스의 경우 각 파티션에 있는 B-트리의 각 수준에 대해 행이 반환됩니다. 힙의 경우 각 파티션의 IN_ROW_DATA 할당 단위에 대해 행이 반환됩니다. LOB(Large Object) 데이터의 경우 각 파티션의 LOB_DATA 할당 단위에 대해 행이 반환됩니다. 테이블에 행-오버플로 데이터가 있는 경우 각 파티션의 ROW_OVERFLOW_DATA 할당 단위에 대해 행이 반환됩니다. xVelocity 메모리 액세스에 최적화된 columnstore 인덱스에 대한 정보를 반환하지 않습니다.

중요
AlwaysOn 읽을 수 있는 보조 복제본을 호스트하는 서버 인스턴스에서 sys.dm_db_index_physical_stats를 쿼리하면 REDO 차단 문제가 발생할 수 있습니다. 이 동적 관리 뷰는 지정된 사용자 테이블 또는 뷰에서 X 잠금에 대한 REDO 스레드의 요청을 차단할 수 있는 해당 사용자 테이블 또는 뷰에 대한 IS 잠금을 획득하기 때문입니다.

Transact-SQL 구문 표기 규칙

구문

sys.dm_db_index_physical_stats ( 
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | NULL | 0 | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
  , { mode | NULL | DEFAULT }
)

인수

• database_id | NULL | 0 | DEFAULT
  데이터베이스 ID입니다. database_id는 smallint입니다. 올바른 입력은 데이터베이스의 ID 번호, NULL, 0 또는 DEFAULT입니다. 기본값은 0입니다. 이 컨텍스트에서 NULL, 0 및 DEFAULT는 동등한 값입니다.

  SQL Server 인스턴스의 모든 데이터베이스에 대한 정보를 반환하려면 NULL을 지정합니다. database_id에 NULL을 지정하는 경우 object_id, index_id 및 partition_number에도 NULL을 지정해야 합니다.

  DB_ID 기본 제공 함수를 지정할 수 있습니다. 데이터베이스 이름을 지정하지 않고 DB_ID를 사용하는 경우 현재 데이터베이스의 호환성 수준은 90 이상이어야 합니다.

• object_id | NULL | 0 | DEFAULT
  인덱스가 있는 테이블 또는 뷰의 개체 ID입니다. object_id는 int입니다.

  올바른 입력은 테이블 및 뷰의 ID 번호, NULL, 0 또는 DEFAULT입니다. 기본값은 0입니다. 이 컨텍스트에서 NULL, 0 및 DEFAULT는 동등한 값입니다.

  지정된 데이터베이스에 있는 모든 테이블 및 뷰에 대한 정보를 반환하려면 NULL을 지정합니다. object_id에 NULL을 지정하는 경우 index_id 및 partition_number에도 NULL을 지정해야 합니다.

• index_id | 0 | NULL | -1 | DEFAULT
  인덱스 ID입니다. index_id는 int입니다. 올바른 입력은 인덱스의 ID 번호로, object_id가 힙인 경우 0, NULL, -1 또는 DEFAULT입니다. 기본값은 -1입니다. 이 컨텍스트에서 NULL, -1 및 DEFAULT는 동등한 값입니다.

  기본 테이블 또는 뷰에 대한 모든 인덱스 정보를 반환하려면 NULL을 지정합니다. index_id에 NULL을 지정하는 경우 partition_number에도 NULL을 지정해야 합니다.

• partition_number | NULL | 0 | DEFAULT
  개체의 파티션 번호입니다. partition_number은 int입니다. 올바른 입력은 인덱스 또는 힙의 partion_number, NULL, 0 또는 DEFAULT입니다. 기본값은 0입니다. 이 컨텍스트에서 NULL, 0 및 DEFAULT는 동등한 값입니다.

  소유하는 개체의 모든 파티션에 대한 정보를 반환하려면 NULL을 지정합니다.

  partition_number는 1부터 시작합니다. 분할되지 않은 인덱스 또는 힙의 partition_number는 1로 설정되어 있습니다.

• mode | NULL | DEFAULT
  모드 이름입니다. mode는 통계를 얻는 데 사용되는 검색 수준을 지정합니다. mode는 sysname입니다. 유효한 입력은 DEFAULT, NULL, LIMITED, SAMPLED 또는 DETAILED입니다. 기본값(NULL)은 LIMITED입니다.

반환된 테이블

주의

sys.dm_db_index_physical_stats 동적 관리 함수는 DBCC SHOWCONTIG 문을 대체합니다. 이 동적 관리 함수는 CROSS APPLY 및 OUTER APPLY에서 상호 관련된 매개 변수를 허용하지 않습니다.

검색 모드

함수가 실행되는 모드에 따라 함수에 사용되는 통계 데이터를 가져오기 위해 수행하는 검색 수준이 결정됩니다. mode는 LIMITED, SAMPLED 또는 DETAILED로 지정됩니다. 함수는 테이블이나 인덱스의 지정한 파티션을 구성하는 할당 단위에 대해 페이지 체인을 트래버스합니다. sys.dm_db_index_physical_stats에는 실행되는 모드에 관계없이 IS(내재된 공유) 테이블 잠금만 필요합니다.

LIMITED 모드는 가장 빠른 모드이며 가장 적은 수의 페이지를 검색합니다. 인덱스의 경우 B-트리의 부모 수준 페이지만(즉, 리프 수준 이상의 페이지) 검색합니다. 힙의 경우 연결된 PFS 및 IAM 페이지가 검사되고 힙의 데이터 페이지는 LIMITED 모드로 검색됩니다.

LIMITED 모드를 사용하면 데이터베이스 엔진이 B-트리의 비-리프 페이지와 힙의 IAM 및 PFS 페이지만 검색하므로 compressed_page_count가 NULL입니다. compressed_page_count의 예상 값을 가져오려면 SAMPLED 모드를 사용하고 compressed_page_count의 실제 값을 가져오려면 DETAILED 모드를 사용합니다. SAMPLED 모드는 인덱스 또는 힙에서 모든 페이지의 1퍼센트 샘플을 기반으로 통계를 반환합니다. SAMPLED 모드의 결과는 대략적인 값으로 간주되어야 합니다. 인덱스나 힙의 페이지 수가 10,000개 미만이면 SAMPLED 대신 DETAILED 모드가 사용됩니다.

DETAILED 모드는 모든 페이지를 검색하여 전체 통계를 반환합니다.

LIMITED에서 DETAILED 모드의 순서로 각 모드에서 수행되는 작업이 더 많아지기 때문에 속도는 점점 더 느려집니다. 테이블 또는 인덱스의 크기나 조각화 수준을 빠르게 측정하려면 LIMITED 모드를 사용합니다. 이 모드가 가장 빠르게 실행되며 인덱스의 IN_ROW_DATA 할당 단위에서 리프가 아닌 각 수준에 대해서는 행을 반환하지 않습니다.

시스템 함수를 사용하여 매개 변수 값 지정

Transact-SQL 함수 DB_ID 및 OBJECT_ID를 사용하여 database_id 및 object_id 매개 변수 값을 지정할 수 있습니다. 그러나 이러한 함수에 유효하지 않은 값을 전달하면 의도하지 않은 결과가 발생할 수 있습니다. 존재하지 않거나 철자가 틀린 경우와 같이 데이터베이스 또는 개체 이름을 찾을 수 없는 경우에는 두 함수 모두 NULL을 반환합니다. sys.dm_db_index_physical_stats 함수는 NULL을 모든 데이터베이스나 모든 개체를 지정하는 와일드카드 값으로 해석합니다.

또한 OBJECT_ID 함수는 sys.dm_db_index_physical_stats 함수가 호출되기 전에 처리되므로 database_id에 지정된 데이터베이스가 아니라 현재 데이터베이스의 컨텍스트에서 계산됩니다. 이 동작으로 인해 OBJECT_ID 함수에서 NULL 값이 반환될 수 있습니다. 또는 개체 이름이 현재 데이터베이스 컨텍스트와 지정된 데이터베이스 둘 다에 있는 경우에는 오류 메시지가 반환될 수도 있습니다. 다음 예에서는 의도되지 않은 이러한 결과를 보여 줍니다.

USE master;
GO
-- In this example, OBJECT_ID is evaluated in the context of the master database. 
-- Because Person.Address does not exist in master, the function returns NULL.
-- When NULL is specified as an object_id, all objects in the database are returned.
-- The same results are returned when an object that is not valid is specified.
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'AdventureWorks'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- This example demonstrates the results of specifying a valid object name
-- that exists in both the current database context and
-- in the database specified in the database_id parameter of the 
-- sys.dm_db_index_physical_stats function.
-- An error is returned because the ID value returned by OBJECT_ID does not
-- match the ID value of the object in the specified database.
CREATE DATABASE Test;
GO
USE Test;
GO
CREATE SCHEMA Person;
GO
CREATE Table Person.Address(c1 int);
GO
USE AdventureWorks2012;
GO
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- Clean up temporary database.
DROP DATABASE Test;
GO

최선의 구현 방법

DB_ID 또는 OBJECT_ID를 사용할 때는 항상 유효한 ID가 반환되는지 확인합니다. 예를 들어 OBJECT_ID를 사용하는 경우 OBJECT_ID(N'AdventureWorks2012.Person.Address')와 같이 세 부분으로 구성된 이름을 지정하거나 이 함수에서 반환된 값을 sys.dm_db_index_physical_stats 함수에 사용하기 전에 테스트합니다. 뒤에 나오는 예 1과 2에서는 데이터베이스와 개체 ID를 안전하게 지정하는 방법을 보여 줍니다.

조각화 검색

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

인덱스 또는 힙의 조각화 수준은 avg_fragmentation_in_percent 열에 표시됩니다. 힙의 경우 이 값은 힙의 익스텐트 조각화를 나타냅니다. 인덱스의 경우 이 값은 인덱스의 논리적 조각화를 나타냅니다. DBCC SHOWCONTIG와는 달리 두 경우 모두 조각화 계산 알고리즘에 따라 여러 파일에 걸쳐 있는 저장소가 고려되므로 결과가 정확합니다.

논리적 조각화

인덱스의 리프 페이지에서 순서가 잘못된 페이지의 비율입니다. 순서가 잘못된 페이지란 인덱스에 할당된 다음 물리적 페이지가 현재 리프 페이지의 다음 페이지포인터가 가리키는 페이지와 다른 경우를 나타냅니다.

익스텐트 조각화

힙의 리프 페이지에서 순서가 잘못된 익스텐트의 비율입니다. 순서가 잘못된 익스텐트란 힙의 현재 페이지가 들어 있는 익스텐트가 실제로 이전 페이지가 들어 있는 익스텐트의 다음 익스텐트가 아닌 경우입니다.

최상의 성능을 얻기 위해서는 avg_fragmentation_in_percent 값이 0에 가까워야 하지만 대개 0~10%의 값이면 적당합니다. 다시 작성, 다시 구성 또는 다시 만들기 등 조각화를 줄이기 위한 모든 방법을 통해 이 값을 줄일 수 있습니다. 인덱스의 조각화 수준을 분석하는 방법은 인덱스 다시 구성 및 다시 작성을 참조하십시오.

인덱스의 조각화 줄이기

인덱스의 조각화가 쿼리 성능에 영향을 미치는 경우 다음 세 방법 중 하나를 사용하여 조각화를 줄일 수 있습니다.

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

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

• DBCC INDEXDEFRAG 대신 ALTER INDEX REORGANIZE를 사용하여 인덱스의 리프 수준 페이지를 논리적 순서로 다시 정렬합니다. 이 작업은 온라인 작업이므로 문이 실행 중일 때 인덱스를 사용할 수 있습니다. 또한 작업이 중단되더라도 이미 완료된 작업은 손실되지 않습니다. 이 방법의 단점은 데이터를 다시 구성하는 작업이 인덱스를 다시 작성하는 작업만큼 효과적이지 않다는 것과 통계가 업데이트되지 않는다는 것입니다.

• DBCC DBREINDEX 대신 ALTER INDEX REBUILD를 사용하여 인덱스를 온라인이나 오프라인 상태로 다시 작성합니다. 자세한 내용은 ALTER INDEX(Transact-SQL)를 참조하세요.

인덱스를 다시 구성하거나 작성하는 이유가 전적으로 조각화 때문만은 아닙니다. 조각화는 주로 인덱스 검색 중 페이지 미리 읽기 성능을 저하시킵니다. 이로 인해 응답 시간이 느려집니다. 조각화된 테이블이나 인덱스에 대한 쿼리 작업은 기본적으로 단일 조회이기 때문에 검색과 관련이 없는 경우에는 조각화를 제거하더라도 효과를 기대하기 어려울 수 있습니다. 자세한 내용은 Microsoft 웹 사이트를 참조하십시오.

[!참고]

축소 작업 중에 인덱스 일부나 전부가 이동한 경우 DBCC SHRINKFILE 또는 DBCC SHRINKDATABASE를 실행하면 조각화가 발생할 수 있습니다. 따라서 조각화를 제거하기 전에 축소 작업을 수행하십시오.

힙의 조각화 줄이기

힙의 익스텐트 조각화를 줄이려면 테이블에 클러스터형 인덱스를 만든 다음 해당 인덱스를 삭제합니다. 이렇게 하면 클러스터형 인덱스를 만드는 동안 데이터가 다시 구성되고 데이터베이스에서 사용할 수 있는 공간의 분포를 고려하여 최적화됩니다. 그런 다음 클러스터형 인덱스를 삭제하여 힙을 다시 만들면 데이터가 이동하지 않고 최적의 분포를 유지합니다. 이러한 작업을 수행하는 방법은 CREATE INDEX 및 DROP INDEX를 참조하십시오.

주의
테이블에서 클러스터형 인덱스를 만들고 삭제하면 해당 테이블에서 모든 비클러스터형 인덱스가 두 번 다시 작성됩니다.

LOB 데이터 압축

기본적으로 ALTER INDEX REORGANIZE 문은 LOB(Large Object) 데이터가 들어 있는 페이지를 압축합니다. LOB 페이지는 비어 있는 경우에도 할당이 취소되지 않으므로 대량의 LOB 데이터를 삭제했거나 LOB 열을 제거한 경우 이 데이터를 압축하면 사용할 수 있는 디스크 공간이 늘어납니다.

지정한 클러스터형 인덱스를 다시 구성하면 클러스터형 인덱스에 포함된 모든 LOB 열이 압축됩니다. 비클러스터형 인덱스를 다시 구성하면 인덱스에서 키가 아닌(포괄) 열인 LOB 열이 모두 압축됩니다. 문에 ALL을 지정하면 지정한 테이블이나 뷰에 연결된 모든 인덱스가 다시 구성됩니다. 또한 클러스터형 인덱스, 기본 테이블 또는 포괄 열이 있는 비클러스터형 인덱스에 연결된 모든 LOB 열이 압축됩니다.

디스크 공간 사용률 평가

avg_page_space_used_in_percent 열은 페이지 사용률을 나타냅니다. 디스크 공간 사용을 최적화하려면 이 값이 임의로 삽입된 항목이 많지 않은 인덱스에 대해 100%에 가까워야 합니다. 그러나 임의 삽입이 많고 페이지 사용률이 매우 높은 인덱스에서는 페이지 분할의 횟수가 증가하므로 더 많은 조각이 생깁니다. 따라서 페이지 분할을 줄이려면 값이 100%보다 작아야 합니다. FILLFACTOR 옵션을 지정하여 인덱스를 다시 작성하면 인덱스에 대한 쿼리 패턴에 맞게 페이지 사용률을 변경할 수 있습니다. 채우기 비율에 대한 자세한 내용은 인덱스의 채우기 비율 지정을 참조하십시오. 또한 ALTER INDEX REORGANIZE는 마지막으로 지정한 FILLFACTOR에 따라 페이지 채우기를 시도하여 인덱스를 압축하므로 avg_space_used_in_percent 값이 증가합니다. ALTER INDEX REORGANIZE는 페이지 사용률을 줄이지 않습니다. 페이지 사용률을 줄이려면 인덱스를 다시 작성해야 합니다.

인덱스 조각 평가

조각은 할당 단위에 대해 동일한 파일에서 물리적으로 연속되는 리프 페이지로 구성됩니다. 인덱스에는 적어도 하나의 조각이 있습니다. 인덱스에 포함할 수 있는 최대 조각 수는 인덱스의 리프 수준에 있는 페이지 수와 동일합니다. 동일한 수의 페이지의 경우 조각이 클수록 읽는 데 필요한 디스크 I/O는 줄어듭니다. 따라서 avg_fragment_size_in_pages 값이 클수록 범위 검색 성능이 좋아집니다. avg_fragment_size_in_pages 및 avg_fragmentation_in_percent 값은 서로 반비례합니다. 따라서 인덱스를 다시 작성하거나 다시 구성하면 조각화의 양은 줄어들고 조각 크기는 커집니다.

사용 권한

다음 사용 권한이 필요합니다.

• 데이터베이스 내의 지정된 개체에 대한 CONTROL 권한

• 개체 와일드카드 @object\_id=NULL을 사용하여 지정된 데이터베이스 내의 모든 개체에 대한 정보를 반환하는 VIEW DATABASE STATE 권한

• 데이터베이스 와일드카드 @database\_id= NULL을 사용하여 모든 데이터베이스에 대한 정보를 반환하는 VIEW SERVER STATE 권한

VIEW DATABASE STATE를 허용하면 특정 개체에 대해 거부된 CONTROL 권한에 관계없이 데이터베이스의 모든 개체가 반환됩니다.

VIEW DATABASE STATE를 거부하면 특정 개체에 대해 허용된 CONTROL 권한에 관계없이 데이터베이스의 모든 개체가 반환되지 않습니다. 또한 데이터베이스 와일드카드 @database\_id=NULL을 지정하면 해당 데이터베이스가 생략됩니다.

자세한 내용은 동적 관리 뷰 및 함수(Transact-SQL)를 참조하세요.

예

1. 지정한 테이블에 대한 정보 반환

다음 예에서는 AdventureWorks2012 데이터베이스에 있는 Person.Address 테이블의 모든 인덱스와 파티션에 대한 크기 및 조각화 통계를 반환합니다. 최대한의 성능을 발휘하고 반환되는 통계를 제한하기 위해 검색 모드를 'LIMITED'로 설정합니다. 이 쿼리를 실행하려면 최소한 Person.Address 테이블에 대한 CONTROL 권한이 필요합니다.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.Person.Address');

IF @db_id IS NULL
BEGIN;
    PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO

2. 힙에 대한 정보 반환

다음 예에서는 AdventureWorks2012 데이터베이스의 dbo.DatabaseLog 힙에 대한 모든 통계를 반환합니다. 테이블에 LOB 데이터가 들어 있으므로 LOB_DATA 할당 단위에 대한 행이 반환되고 힙의 데이터 페이지를 저장하는 IN_ROW_ALLOCATION_UNIT에 대한 행도 반환됩니다. 이 쿼리를 실행하려면 최소한 dbo.DatabaseLog 테이블에 대한 CONTROL 권한이 필요합니다.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.dbo.DatabaseLog');
IF @object_id IS NULL 
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED');
END;
GO

3. 모든 데이터베이스에 대한 정보 반환

다음 예에서는 모든 매개 변수에 와일드카드인 NULL을 지정하여 SQL Server 인스턴스 내의 모든 테이블과 인덱스에 대한 모든 통계를 반환합니다. 이 쿼리를 실행하려면 VIEW SERVER STATE 권한이 필요합니다.

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
GO

4. 스크립트에 sys.dm_db_index_physical_stats를 사용하여 인덱스를 다시 작성하거나 다시 구성

다음 예에서는 데이터베이스에서 평균 조각화가 10%를 넘는 모든 파티션을 자동으로 다시 구성하거나 다시 작성합니다. 이 쿼리를 실행하려면 VIEW DATABASE STATE 권한이 필요합니다. 이 예에서는 데이터베이스 이름을 지정하지 않고 DB_ID를 첫 번째 매개 변수로 지정합니다. 현재 데이터베이스의 호환성 수준이 80 이하이면 오류가 발생합니다. 오류를 해결하려면 DB_ID()를 올바른 데이터베이스 이름으로 대체합니다. 데이터베이스 호환성 수준에 대한 자세한 내용은 ALTER DATABASE 호환성 수준(Transact-SQL)을 참조하십시오.

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000); 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

5. sys.dm_db_index_physical_stats를 사용하여 페이지 대 압축된 페이지 수 표시

다음 예에서는 행 및 페이지가 압축된 페이지에 대한 총 페이지 수를 표시하고 비교하는 방법을 보여 줍니다. 이 정보는 인덱스 또는 테이블 압축의 효과를 확인할 때 사용할 수 있습니다.

SELECT o.name,
    ips.partition_number,
    ips.index_type_desc,
    ips.record_count, ips.avg_record_size_in_bytes,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.page_count, ips.compressed_page_count
FROM sys.dm_db_index_physical_stats ( DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
JOIN sys.objects o on o.object_id = ips.object_id
ORDER BY record_count DESC;

F. SAMPLED 모드에서 sys.dm_db_index_physical_stats 사용

다음 예에서는 SAMPLED 모드가 DETAILED 모드 결과와 다른 대략적인 값을 어떻게 반환하는지를 보여 줍니다.

CREATE TABLE t3 (col1 int PRIMARY KEY, col2 varchar(500)) WITH(DATA_COMPRESSION = PAGE);
GO
BEGIN TRAN
DECLARE @idx int = 0;
WHILE @idx < 1000000
BEGIN
    INSERT INTO t3 (col1, col2) 
    VALUES (@idx, 
    REPLICATE ('a', 100) + CAST (@idx as varchar(10)) + REPLICATE ('a', 380))
    SET @idx = @idx + 1
END
COMMIT;
GO
SELECT page_count, compressed_page_count, forwarded_record_count, * 
FROM sys.dm_db_index_physical_stats (db_id(), 
    object_id ('t3'), null, null, 'SAMPLED');
SELECT page_count, compressed_page_count, forwarded_record_count, * 
FROM sys.dm_db_index_physical_stats (db_id(), 
    object_id ('t3'), null, null, 'DETAILED');

참고 항목

참조

동적 관리 뷰 및 함수(Transact-SQL)

인덱스 관련 동적 관리 뷰 및 함수(Transact-SQL)

sys.dm_db_index_operational_stats(Transact-SQL)

sys.dm_db_index_usage_stats(Transact-SQL)

sys.dm_db_partition_stats(Transact-SQL)

sys.allocation_units(Transact-SQL)

시스템 뷰(Transact-SQL)