sys.dm_db_index_physical_stats(Transact-SQL)

아티클
08/21/2023

적용 대상:SQL Server Azure SQL DatabaseAzure SQL Managed Instance

SQL Server에서 지정된 테이블 또는 뷰의 데이터 및 인덱스에 대한 크기 및 조각화 정보를 반환합니다. 인덱스의 경우 각 파티션에 있는 B-트리의 각 수준에 대해 행이 반환됩니다. 힙의 경우 각 파티션의 할당 단위에 IN_ROW_DATA 대해 하나의 행이 반환됩니다. LOB(큰 개체) 데이터의 경우 각 파티션의 할당 단위에 LOB_DATA 대해 하나의 행이 반환됩니다. 테이블에 행 오버플로 데이터가 있는 경우 각 파티션의 ROW_OVERFLOW_DATA 할당 단위에 대해 하나의 행이 반환됩니다.

참고 항목

SQL Server 설명서는 인덱스를 지칭할 때 B-트리라는 용어를 사용합니다. rowstore 인덱스에서 SQL Server는 B+ 트리를 구현합니다. 이는 columnstore 인덱스나 메모리 내 데이터 저장소에는 적용되지 않습니다. 자세한 내용은 SQL Server 및 Azure SQL 인덱스 아키텍처 및 디자인 가이드를 참조 하세요.

sys.dm_db_index_physical_stats 는 메모리 최적화 columnstore 인덱스에 대한 정보를 반환하지 않습니다. 메모리 최적화 인덱스 사용에 대한 자세한 내용은 sys.dm_db_xtp_index_stats(Transact-SQL)를 참조하세요.

가용성 그룹 읽기 가능한 보조 복제본을 호스팅하는 서버 인스턴스에서 쿼리 sys.dm_db_index_physical_stats 하는 경우 차단 문제가 발생할 REDO 수 있습니다. 이 동적 관리 뷰는 해당 사용자 테이블 또는 뷰에 대한 잠금에 대한 스레드의 요청을 REDO 차단할 수 있는 지정된 사용자 테이블 또는 뷰에 대한 X 잠금을 획득 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 | 기본

데이터베이스의 ID입니다. database_id 작습니다. 유효한 입력은 데이터베이스, NULL, 0 또는 DEFAULT의 ID입니다. 기본값은 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 | 기본

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

유효한 입력은 테이블 및 뷰, NULL, 0 또는 DEFAULT의 ID입니다. 기본값은 0입니다. NULL, 0 및 DEFAULT는 이 컨텍스트에서 동일한 값입니다. SQL Server 2016(13.x)을 기준으로 유효한 입력에는 서비스 브로커 큐 이름 또는 큐 내부 테이블 이름도 포함됩니다. 기본 매개 변수(즉, 모든 개체, 모든 인덱스 등)가 적용되면 모든 큐에 대한 조각화 정보가 결과 집합에 포함됩니다.

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

index_id | 0 | NULL | -1 | 기본

인덱스의 ID입니다. index_id int입니다. 유효한 입력은 인덱스의 ID이며, object_id 힙, NULL, -1 또는 DEFAULT인 경우 0입니다. 기본값은 -1입니다. NULL, -1 및 DEFAULT는 이 컨텍스트에서 동일한 값입니다.

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

partition_number | NULL | 0 | 기본

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

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

partition_number 1부터 시작하는 경우 분할되지 않은 인덱스 또는 힙은 partition_number 1로 설정되었습니다.

mode | NULL | 기본

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

반환된 테이블

열 이름	데이터 형식	설명
database_id	smallint	테이블 또는 뷰의 데이터베이스 ID입니다. Azure SQL Database에서 값은 단일 데이터베이스 또는 탄력적 풀 내에서 고유하지만 논리 서버 내에는 없습니다.
object_id	int	인덱스가 있는 테이블 또는 뷰의 개체 ID입니다.
index_id	int	인덱스의 인덱스 ID입니다. 0 = 힙입니다.
partition_number	int	소유 개체 내의 1부터 시작하는 파티션 번호입니다. 테이블, 뷰 또는 인덱스 1 = 분할되지 않은 인덱스 또는 힙입니다.
index_type_desc	nvarchar(60)	인덱스 형식에 대한 설명: - HEAP - 클러스터형 인덱스 - 비클러스터형 인덱스 - 기본 XML 인덱스 - 확장 인덱스 - XML INDEX - COLUMNSTORE 매핑 인덱스(내부) - COLUMNSTORE DELETEBUFFER INDEX(내부) - COLUMNSTORE DELETEBITMAP INDEX(내부)
hobt_id	bigint	인덱스 또는 파티션의 힙 또는 B-트리 ID입니다. columnstore 인덱스의 경우 파티션에 대한 내부 columnstore 데이터를 추적하는 행 집합의 ID입니다. 행 집합은 데이터 힙 또는 B-트리로 저장됩니다. 부모 columnstore 인덱스와 동일한 인덱스 ID를 갖습니다. 자세한 내용은 sys.internal_partitions(Transact-SQL)를 참조 하세요.
alloc_unit_type_desc	nvarchar(60)	할당 단위 유형에 대한 설명: -In_row_data - LOB_DATA - ROW_OVERFLOW_DATA 할당 단위에는 `LOB_DATA` 텍스트, ntext, image, varchar(max), nvarchar(max), varbinary(max) 및 xml 형식의 열에 저장된 데이터가 포함됩니다. 자세한 내용은 데이터 형식(Transact-SQL)을 참조하세요. 할당 단위에는 `ROW_OVERFLOW_DATA` varchar(n), nvarchar(n), varbinary(n) 및 행에서 푸시된 sql_variant 열에 저장된 데이터가 포함됩니다.
index_depth	tinyint	인덱스 수준의 수입니다. 1 = 힙 또는 `LOB_DATAROW_OVERFLOW_DATA` 할당 단위입니다.
index_level	tinyint	인덱스의 현재 수준입니다. 인덱스 리프 수준, 힙 및 `LOB_DATAROW_OVERFLOW_DATA` 할당 단위의 경우 0입니다. 비리프 인덱스 수준의 경우 0보다 큽다. index_level 인덱스의 루트 수준에서 가장 높습니다. 비리프 수준의 인덱스는 모드 = DETAILED일 때만 처리됩니다.
avg_fragmentation_in_percent	float	인덱스에 대한 논리적 조각화 또는 할당 단위의 힙에 `IN_ROW_DATA` 대한 익스텐트 조각화입니다. 값은 백분율로 측정되며 여러 파일을 고려합니다. 논리 및 익스텐트 조각화에 대한 정의는 비고를 참조하세요. 0 및 할당 단위입니다 `LOB_DATAROW_OVERFLOW_DATA` . 모드 = SAMPLED인 경우 힙의 경우 NULL입니다.
fragment_count	bigint	할당 단위의 리프 수준에 있는 조각 수입니다 `IN_ROW_DATA` . 조각에 대한 자세한 내용은 비고를 참조하세요. 인덱 `LOB_DATAROW_OVERFLOW_DATA` 스 및 할당 단위의 비리프 수준에 대한 NULL입니다. 모드 = SAMPLED인 경우 힙의 경우 NULL입니다.
avg_fragment_size_in_pages	float	할당 단위의 리프 수준에서 한 조각의 평균 페이지 수입니다 `IN_ROW_DATA` . 인덱 `LOB_DATAROW_OVERFLOW_DATA` 스 및 할당 단위의 비리프 수준에 대한 NULL입니다. 모드 = SAMPLED인 경우 힙의 경우 NULL입니다.
page_count	bigint	총 인덱스 또는 데이터 페이지 수입니다. 인덱스의 경우 할당 단위에서 B-트리의 현재 수준에 있는 `IN_ROW_DATA` 총 인덱스 페이지 수입니다. 힙의 경우 할당 단위의 총 데이터 페이지 `IN_ROW_DATA` 수입니다. 할당 단위의 `ROW_OVERFLOW_DATA` 경우 `LOB_DATA` 할당 단위의 총 페이지 수입니다.
avg_page_space_used_in_percent	float	모든 페이지에서 사용되는 사용 가능한 데이터 스토리지 공간의 평균 백분율입니다. 인덱스의 경우 평균은 할당 단위에서 B-트리 `IN_ROW_DATA` 의 현재 수준에 적용됩니다. 힙의 경우 할당 단위에 있는 `IN_ROW_DATA` 모든 데이터 페이지의 평균입니다. 할당 단위 또는 `ROW_OVERFLOW_DATA` 할당 단위의 경우 `LOB_DATA` 할당 단위에 있는 모든 페이지의 평균입니다. 모드 = LIMITED인 경우 NULL입니다.
record_count	bigint	총 레코드 수입니다. 인덱스의 경우 총 레코드 수는 할당 단위에서 B-트리 `IN_ROW_DATA` 의 현재 수준에 적용됩니다. 힙의 경우 할당 단위의 총 레코드 `IN_ROW_DATA` 수입니다. 참고: 힙의 경우 이 함수에서 반환된 레코드 수가 힙에 대해 실행 `SELECT COUNT()` 하여 반환되는 행 수와 일치하지 않을 수 있습니다. 이것은 한 행에 여러 레코드가 있기 때문입니다. 예를 들어 특정 업데이트 상황에서 업데이트 작업으로 인해 단일 힙 행에 한 개의 전달되고 있는 레코드와 한 개의 전달된 레코드가 있을 수 있습니다. 또한 대부분의 큰 LOB 행은 스토리지의 `LOB_DATA` 여러 레코드로 분할됩니다. 할당 단위의 `ROW_OVERFLOW_DATA` 경우 `LOB_DATA` 전체 할당 단위의 총 레코드 수입니다. 모드 = LIMITED인 경우* NULL입니다.
ghost_record_count	bigint	할당 단위에서 삭제할 레코드 정리 태스크에 의해 제거될 삭제할 레코드 수입니다. 할당 단위의 인덱 `IN_ROW_DATA` 스가 아닌 수준의 경우 0입니다. 모드 = LIMITED인 경우 NULL입니다.
version_ghost_record_count	bigint	할당 단위에서 미해결 스냅샷 격리 트랜잭션에 의해 보존된 고스트 레코드의 수입니다. 할당 단위의 인덱 `IN_ROW_DATA` 스가 아닌 수준의 경우 0입니다. 모드 = LIMITED인 경우 NULL입니다.
min_record_size_in_bytes	int	최소 레코드 크기(바이트)입니다. 인덱스의 경우 최소 레코드 크기는 할당 단위에서 B-트리 `IN_ROW_DATA` 의 현재 수준에 적용됩니다. 힙의 경우 할당 단위의 최소 레코드 크기입니다 `IN_ROW_DATA` . 할당 단위의 `ROW_OVERFLOW_DATA` 경우 `LOB_DATA` 전체 할당 단위의 최소 레코드 크기입니다. 모드 = LIMITED인 경우 NULL입니다.
max_record_size_in_bytes	int	최대 레코드 크기(바이트)입니다. 인덱스의 경우 최대 레코드 크기는 할당 단위에서 B-트리 `IN_ROW_DATA` 의 현재 수준에 적용됩니다. 힙의 경우 할당 단위의 최대 레코드 크기입니다 `IN_ROW_DATA` . 할당 단위의 `ROW_OVERFLOW_DATA` 경우 `LOB_DATA` 전체 할당 단위의 최대 레코드 크기입니다. 모드 = LIMITED인 경우 NULL입니다.
avg_record_size_in_bytes	float	평균 레코드 크기(바이트)입니다. 인덱스의 경우 평균 레코드 크기는 할당 단위에서 B-트리 `IN_ROW_DATA` 의 현재 수준에 적용됩니다. 힙의 경우 할당 단위의 평균 레코드 크기입니다 `IN_ROW_DATA` . 할당 단위의 `ROW_OVERFLOW_DATA` 경우 `LOB_DATA` 전체 할당 단위의 평균 레코드 크기입니다. 모드 = LIMITED인 경우 NULL입니다.
forwarded_record_count	bigint	다른 데이터 위치로의 전달 포인터가 있는 힙의 레코드 수입니다. (이 상태는 업데이트 중에 새 행을 원래 위치에 저장할 공간이 부족할 때 발생합니다.) 힙의 할당 단위 이외의 할당 단위에 `IN_ROW_DATA` 대한 NULL입니다. 모드 = LIMITED인 경우 힙의 경우 NULL입니다.
compressed_page_count	bigint	압축된 페이지 수입니다. 힙의 경우 새로 할당된 페이지는 PAGE 압축되지 않습니다. 힙은 데이터를 대량으로 가져오는 경우 또는 힙을 다시 빌드할 때의 두 가지 특수 조건에서 PAGE 압축됩니다. 페이지 할당을 유발하는 일반적인 DML 작업은 PAGE 압축되지 않습니다. 값이 원하는 임계값보다 커지면 힙 `compressed_page_count` 을 다시 빌드합니다. 클러스터형 인덱스가 있는 테이블의 경우 값은 `compressed_page_count` PAGE 압축의 효과를 나타냅니다.
columnstore_delete_buffer_state	tinyint	0 = NOT_APPLICABLE 1 = OPEN 2 = 드레이닝 3 = 플러시 4 = 사용 중지 5 = READY 적용 대상: SQL Server 2016(13.x) 이상 버전, Azure SQL Database 및 Azure SQL Managed Instance
columnstore_delete_buffer_state_desc	nvarchar(60)	유효하지 않음 - 부모 인덱스가 columnstore 인덱스가 아닙니다. OPEN - 삭제자와 스캐너가 이를 사용합니다. 드레이닝 - 삭제자는 배수하지만 스캐너는 여전히 사용합니다. FLUSHING - 버퍼가 닫히고 버퍼의 행이 삭제 비트맵에 기록됩니다. 사용 중지 - 닫힌 삭제 버퍼의 행이 삭제 비트맵에 기록되었지만 스캐너가 여전히 사용 중이므로 버퍼가 잘리지 않았습니다. 열려 있는 버퍼로 충분하기 때문에 새 스캐너는 사용 중지 버퍼를 사용할 필요가 없습니다. READY - 이 삭제 버퍼를 사용할 준비가 된 것입니다. 적용 대상: SQL Server 2016(13.x) 이상 버전, Azure SQL Database 및 Azure SQL Managed Instance
version_record_count	bigint	이 인덱스로 유지 관리되는 행 버전 레코드의 수입니다. 이러한 행 버전은 가속 데이터베이스 복구 기능에 의해 유지 관리됩니다. 적용 대상: SQL Server 2019(15.x) 이상 버전 및 Azure SQL Database
inrow_version_record_count	bigint	빠른 검색을 위해 데이터 행에 보관된 ADR 버전 레코드의 수입니다. 적용 대상: SQL Server 2019(15.x) 이상 버전 및 Azure SQL Database
inrow_diff_version_record_count	bigint	기본 버전과 다른 형태로 유지되는 ADR 버전 레코드의 수입니다. 적용 대상: SQL Server 2019(15.x) 이상 버전 및 Azure SQL Database
total_inrow_version_payload_size_in_bytes	bigint	이 인덱스의 행 내 버전 레코드의 총 크기(바이트)입니다. 적용 대상: SQL Server 2019(15.x) 이상 버전 및 Azure SQL Database
offrow_regular_version_record_count	bigint	원래 데이터 행 외부에 유지되는 버전 레코드의 수입니다. 적용 대상: SQL Server 2019(15.x) 이상 버전 및 Azure SQL Database
offrow_long_term_version_record_count	bigint	장기로 간주되는 버전 레코드의 수입니다. 적용 대상: SQL Server 2019(15.x) 이상 버전 및 Azure SQL Database

참고 항목

설명

동적 관리 함수는 sys.dm_db_index_physical_stats 문을 대체합니다 DBCC SHOWCONTIG .

검사 모드

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

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

LIMITED 모드에서는 compressed_page_count 데이터베이스 엔진이 B-트리의 비리프 페이지와 힙의 IAM 및 PFS 페이지만 검사하기 때문에 NULL입니다. SAMPLED 모드를 사용하여 예상 값을 compressed_page_count가져와서 DETAILED 모드를 사용하여 실제 값을 compressed_page_count가져옵니다. 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'AdventureWorks2022'), 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 AdventureWorks2022;
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'AdventureWorks2022.Person.Address')3부)을 지정하거나 함수에서 sys.dm_db_index_physical_stats 반환된 값을 테스트합니다. 다음 예제 A와 B는 데이터베이스 및 개체 ID를 지정하는 안전한 방법을 보여 줍니다.

조각화 검색

조각화는 테이블에 대해 수행되는 데이터 수정(INSERT, UPDATE 및 DELETE 문) 프로세스와 테이블에 정의된 인덱스를 통해 발생합니다. 이러한 수정 내용은 일반적으로 테이블 및 인덱스의 행 간에 동일하게 분산되지 않으므로 각 페이지의 전체성은 시간에 따라 달라질 수 있습니다. 테이블의 인덱스 일부 또는 전체를 검사하는 쿼리의 경우 이러한 종류의 조각화로 인해 추가 페이지 읽기가 발생할 수 있습니다. 이는 데이터의 병렬 검색에 방해가 됩니다.

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

논리적 조각화

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

익스텐트 조각화

이는 힙의 리프 페이지에서 순서가 잘못된 익스텐트 비율입니다. 순서가 잘못된 익스텐트입니다. 힙에 대한 현재 페이지가 포함된 익스텐트를 이전 페이지가 포함된 익스텐트 이후의 다음 익스텐트를 실제로 포함하지 않는 범위입니다.

최대 성능을 위해 avg_fragmentation_in_percent 값은 가능한 한 0에 가까워야 합니다. 그러나 0%에서 10%의 값은 허용될 수 있습니다. 다시 작성, 다시 구성 또는 다시 만들기 등 조각화를 줄이기 위한 모든 방법을 통해 이 값을 줄일 수 있습니다. 인덱스의 조각화 수준을 분석하는 방법에 대한 자세한 내용은 인덱스 다시 구성 및 다시 작성을 참조 하세요.

인덱스 조각화 줄이기

조각화가 쿼리 성능에 영향을 주는 방식으로 인덱스가 조각화되는 경우 조각화를 줄이기 위한 세 가지 옵션이 있습니다.

클러스터형 인덱스 삭제 및 다시 만들기

클러스터형 인덱스를 다시 만들면 데이터가 재배포되고 전체 데이터 페이지가 생성됩니다. CREATE INDEX의 FILLFACTOR 옵션을 사용하여 충만 수준을 구성할 수 있습니다. 이 메서드의 단점은 드롭 및 다시 만들기 주기 중에 인덱스가 오프라인 상태이고 작업이 원자성이라는 것입니다. 인덱스 생성이 중단되면 그 인덱스는 다시 생성되지 않습니다. 자세한 내용은 CREATE INDEX(Transact-SQL)를 참조하세요.
대신 ALTER INDEX REORGANIZE DBCC INDEXDEFRAG를 사용하여 인덱스의 리프 수준 페이지를 논리적 순서로 다시 정렬합니다. 온라인 작업이므로 문이 실행되는 동안 인덱스도 사용할 수 있습니다. 작업이 이미 완료된 작업 손실 없이 중단될 수도 있습니다. 이 메서드의 단점은 데이터를 인덱스 다시 작성 작업으로 다시 구성하는 것만큼 좋지 않으며 통계를 업데이트하지 않는다는 것입니다.
대체인 ALTER INDEX REBUILD를 DBCC DBREINDEX사용하여 온라인 또는 오프라인에서 인덱스 다시 작성 자세한 내용은 ALTER INDEX(Transact-SQL)를 참조하세요.

조각화만으로는 인덱스 재구성 또는 다시 작성을 위한 충분한 이유가 아닙니다. 조각화는 주로 인덱스 검색 중 페이지 미리 읽기 성능을 저하시킵니다. 이로 인해 응답 시간이 느려집니다. 조각화된 테이블 또는 인덱스의 쿼리 워크로드에 검사가 포함되지 않는 경우 워크로드는 주로 단일 조회이므로 조각화를 제거해도 아무런 영향을 주지 않을 수 있습니다.

참고 항목

축소 작업 중에 인덱스가 부분적으로 또는 완전히 이동된 경우 실행 DBCC SHRINKFILE 중이거나 DBCC SHRINKDATABASE 조각화를 도입할 수 있습니다. 따라서 축소 작업을 수행해야 하는 경우 조각화가 제거되기 전에 수행해야 합니다.

힙에서 조각화 줄이기

힙의 익스텐트 조각화를 줄이려면 테이블에 클러스터형 인덱스가 만들어지고 인덱스가 삭제됩니다. 이렇게 하면 클러스터형 인덱스를 만드는 동안 데이터가 다시 구성되고 또한 데이터베이스에서 사용 가능한 여유 공간의 분포를 고려하여 가능한 한 최적이 됩니다. 클러스터형 인덱스가 삭제되어 힙을 다시 만들면 데이터가 이동되지 않고 최적으로 유지됩니다. 이러한 작업을 수행하는 방법에 대한 자세한 내용은 CREATE INDEX 및 DROP INDEX 를 참조하세요.

주의

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

큰 개체 데이터 압축

기본적으로 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_fragmentation_in_percent 값은 avg_fragment_size_in_pages 서로 반비례합니다. 따라서 인덱스를 다시 작성하거나 다시 구성하면 조각화의 양은 줄어들고 조각 크기는 커집니다.

제한 사항

클러스터형 columnstore 인덱스에 대한 데이터를 반환하지 않습니다.

사용 권한

다음 권한이 필요합니다.

데이터베이스 내의 지정된 개체에 대한 CONTROL 권한입니다.
개체 와일드카드 @object_id=NULL을 사용하여 지정된 데이터베이스 내의 모든 개체에 대한 정보를 반환할 수 있는 VIEW DATABASE STATE 또는 VIEW DATABASE PERFORMANCE STATE(SQL Server 2022) 권한입니다.
VIEW SERVER STATE 또는 VIEW SERVER PERFORMANCE STATE(SQL Server 2022) 권한은 데이터베이스 와일드카드 @database_id = NULL을 사용하여 모든 데이터베이스에 대한 정보를 반환합니다.

VIEW DATABASE STATE를 부여하면 특정 개체에 대해 거부된 CONTROL 권한에 관계없이 데이터베이스의 모든 개체를 반환할 수 있습니다.

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

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

예

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

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

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
  
SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.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

B. 힙에 대한 정보 반환

다음 예제에서는 AdventureWorks2022 데이터베이스의 힙 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'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.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

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

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

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

D. 스크립트에서 인 `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
    INNER 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

E. 페이지 압축 페이지 수를 표시하는 데 사용 `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
INNER 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 TRANSACTION

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');

G. 인덱스 조각화에 대한 서비스 브로커 큐 쿼리

적용 대상: SQL Server 2016(13.x) 이상 버전

다음 예제에서는 서버 브로커 큐에서 조각화를 쿼리하는 방법을 보여 줍니다.

--Using queue internal table name
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('sys.queue_messages_549576996'), DEFAULT, DEFAULT, DEFAULT);

--Using queue name directly
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('ExpenseQueue'), DEFAULT, DEFAULT, DEFAULT);

sys.dm_db_index_physical_stats(Transact-SQL)

구문

인수

database_id | NULL | 0 | 기본

object_id | NULL | 0 | 기본

index_id | 0 | NULL | -1 | 기본

partition_number | NULL | 0 | 기본

mode | NULL | 기본

반환된 테이블

설명

검사 모드

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

모범 사례

조각화 검색

논리적 조각화

익스텐트 조각화

인덱스 조각화 줄이기

힙에서 조각화 줄이기

큰 개체 데이터 압축

디스크 공간 사용 평가

인덱스 조각 평가

제한 사항

사용 권한

예

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

B. 힙에 대한 정보 반환

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

D. 스크립트에서 인 `sys.dm_db_index_physical_stats` 덱스 다시 작성 또는 다시 구성

E. 페이지 압축 페이지 수를 표시하는 데 사용 `sys.dm_db_index_physical_stats`

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

G. 인덱스 조각화에 대한 서비스 브로커 큐 쿼리

참고 항목

피드백

피드백

추가 리소스

sys.dm_db_index_physical_stats(Transact-SQL)

구문

인수

database_id | NULL | 0 | 기본

object_id | NULL | 0 | 기본

index_id | 0 | NULL | -1 | 기본

partition_number | NULL | 0 | 기본

mode | NULL | 기본

반환된 테이블

설명

검사 모드

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

모범 사례

조각화 검색

논리적 조각화

익스텐트 조각화

인덱스 조각화 줄이기

힙에서 조각화 줄이기

큰 개체 데이터 압축

디스크 공간 사용 평가

인덱스 조각 평가

제한 사항

사용 권한

예

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

B. 힙에 대한 정보 반환

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

D. 스크립트에서 인 sys.dm_db_index_physical_stats 덱스 다시 작성 또는 다시 구성

E. 페이지 압축 페이지 수를 표시하는 데 사용 sys.dm_db_index_physical_stats

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

G. 인덱스 조각화에 대한 서비스 브로커 큐 쿼리

참고 항목

피드백

피드백

추가 리소스

D. 스크립트에서 인 `sys.dm_db_index_physical_stats` 덱스 다시 작성 또는 다시 구성

E. 페이지 압축 페이지 수를 표시하는 데 사용 `sys.dm_db_index_physical_stats`

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