sys.dm_db_index_physical_stats(Transact-SQL)

아티클
10/04/2024

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

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

참고 항목

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

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

가용성 그룹 읽기 가능한 보조 복제본을 호스팅하는 서버 인스턴스에서 쿼리 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 | 기본값

데이터베이스 ID입니다. database_id 작습니다. 유효한 입력은 데이터베이스의 ID 또는 NULL0DEFAULT. 기본값은 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입니다. 유효한 입력은 테이블 및 뷰NULL0의 ID, 또는 DEFAULT. 기본값은 0입니다. NULL, 0이 DEFAULT 컨텍스트에서 동일한 값입니다.

SQL Server 2016(13.x) 이상 버전에서 유효한 입력에는 서비스 브로커 큐 이름 또는 큐 내부 테이블 이름도 포함됩니다. 기본 매개 변수(즉, 모든 개체, 모든 인덱스 등)가 적용되면 모든 큐에 대한 조각화 정보가 결과 집합에 포함됩니다.

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

index_id | 0 | NULL | -1 | 기본값

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

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

partition_number | NULL | 0 | 기본값

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

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

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

mode | NULL | 기본값

모드의 이름입니다. 모드 는 통계를 가져오는 데 사용되는 검사 수준을 지정합니다. 모드는 sysname입니다. 유효한 입력은 DEFAULT, NULL, LIMITEDSAMPLED또는 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` - `CLUSTERED INDEX` - `NONCLUSTERED INDEX` - `PRIMARY XML INDEX` - `EXTENDED INDEX` - `XML INDEX` - `COLUMNSTORE MAPPING INDEX` (내부) - `COLUMNSTORE DELETEBUFFER INDEX` (내부) - `COLUMNSTORE DELETEBITMAP INDEX` (내부)
`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 형식의 열에 저장된 데이터가 포함됩니다. 자세한 내용은 데이터 형식을 참조하십시오. 할당 단위에는 `ROW_OVERFLOW_DATA` varchar(n), nvarchar(n), varbinary(n) 및 행에서 푸시된 sql_variant 열에 저장된 데이터가 포함됩니다.
`index_depth`	tinyint	인덱스 수준의 수입니다. `1` = 힙 또는 `LOB_DATA` `ROW_OVERFLOW_DATA` 할당 단위입니다.
`index_level`	tinyint	인덱스의 현재 수준입니다. `0` 인덱스 리프 수준, 힙 및 `LOB_DATA` `ROW_OVERFLOW_DATA` 할당 단위의 경우 비리프 인덱스 수준보다 `0` 큽다. index_level 인덱스의 루트 수준에서 가장 높습니다. 비리프 수준의 인덱스는 모드가 될 때만 처리됩니다`DETAILED`.
`avg_fragmentation_in_percent`	float	인덱스에 대한 논리적 조각화 또는 할당 단위의 힙에 `IN_ROW_DATA` 대한 익스텐트 조각화입니다. 값은 백분율로 측정되며 여러 파일을 고려합니다. 논리 및 익스텐트 조각화에 대한 정의는 비고를 참조 하세요. `0ROW_OVERFLOW_DATA` 및 `LOB_DATA` 할당 단위입니다. `NULL`모드인 경우 힙의 경우 .`SAMPLED`
`fragment_count`	bigint	할당 단위의 리프 수준에 있는 조각 수입니다 `IN_ROW_DATA` . 조각에 대한 자세한 내용은 비고를 참조 하세요. `NULL` 인덱 `LOB_DATA` `ROW_OVERFLOW_DATA` 스 또는 할당 단위의 비리프 수준에 대한 것입니다. `NULL`모드인 경우 힙의 경우 .`SAMPLED`
`avg_fragment_size_in_pages`	float	할당 단위의 리프 수준에서 한 조각의 평균 페이지 수입니다 `IN_ROW_DATA` . `NULL` 인덱 `LOB_DATA` `ROW_OVERFLOW_DATA` 스 또는 할당 단위의 비리프 수준에 대한 것입니다. `NULL`모드인 경우 힙의 경우 .`SAMPLED`
`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` 할당 단위에 있는 모든 페이지의 평균입니다. `NULL`모드가 .인 경우 `LIMITED`
`record_count`	bigint	총 레코드 수입니다. 인덱스의 경우 총 레코드 수는 할당 단위에서 B-트리 `IN_ROW_DATA` 의 현재 수준에 적용됩니다. 힙의 경우 할당 단위의 총 레코드 `IN_ROW_DATA` 수입니다. 참고: 힙의 경우 이 함수에서 반환된 레코드 수가 힙에 대해 실행 `SELECT COUNT()` 하여 반환되는 행 수와 일치하지 않을 수 있습니다. 행에 여러 레코드가 포함될 수 있기 때문입니다. 예를 들어 일부 업데이트 상황에서는 업데이트 작업의 결과로 단일 힙 행에 전달 레코드와 전달된 레코드가 있을 수 있습니다. 또한 대부분의 큰 LOB 행은 스토리지의 `LOB_DATA` 여러 레코드로 분할됩니다. 할당 단위의 `ROW_OVERFLOW_DATA` 경우 `LOB_DATA` 전체 할당 단위의 총 레코드 수입니다. `NULL`모드가 .인 경우* `LIMITED`
`ghost_record_count`	bigint	할당 단위에서 삭제할 레코드 정리 태스크에 의해 제거될 삭제할 레코드 수입니다. `0` 할당 단위에 있는 인덱스의 비리프 수준에 해당합니다 `IN_ROW_DATA` . `NULL`모드가 .인 경우 `LIMITED`
`version_ghost_record_count`	bigint	할당 단위에서 미해결 스냅샷 격리 트랜잭션에 의해 보존된 고스트 레코드의 수입니다. `0` 할당 단위에 있는 인덱스의 비리프 수준에 해당합니다 `IN_ROW_DATA` . `NULL`모드가 .인 경우 `LIMITED`
`min_record_size_in_bytes`	int	최소 레코드 크기(바이트)입니다. 인덱스의 경우 최소 레코드 크기는 할당 단위에서 B-트리 `IN_ROW_DATA` 의 현재 수준에 적용됩니다. 힙의 경우 할당 단위의 최소 레코드 크기입니다 `IN_ROW_DATA` . 할당 단위의 `ROW_OVERFLOW_DATA` 경우 `LOB_DATA` 전체 할당 단위의 최소 레코드 크기입니다. `NULL`모드가 .인 경우 `LIMITED`
`max_record_size_in_bytes`	int	최대 레코드 크기(바이트)입니다. 인덱스의 경우 최대 레코드 크기는 할당 단위에서 B-트리 `IN_ROW_DATA` 의 현재 수준에 적용됩니다. 힙의 경우 할당 단위의 최대 레코드 크기입니다 `IN_ROW_DATA` . 할당 단위의 `ROW_OVERFLOW_DATA` 경우 `LOB_DATA` 전체 할당 단위의 최대 레코드 크기입니다. `NULL`모드가 .인 경우 `LIMITED`
`avg_record_size_in_bytes`	float	평균 레코드 크기(바이트)입니다. 인덱스의 경우 평균 레코드 크기는 할당 단위에서 B-트리 `IN_ROW_DATA` 의 현재 수준에 적용됩니다. 힙의 경우 할당 단위의 평균 레코드 크기입니다 `IN_ROW_DATA` . 할당 단위의 `ROW_OVERFLOW_DATA` 경우 `LOB_DATA` 전체 할당 단위의 평균 레코드 크기입니다. `NULL`모드가 .인 경우 `LIMITED`
`forwarded_record_count`	bigint	다른 데이터 위치로의 전달 포인터가 있는 힙의 레코드 수입니다. (이 상태는 업데이트 중에 새 행을 원래 위치에 저장할 공간이 부족할 때 발생합니다.) `NULL` 힙의 할당 단위가 `IN_ROW_DATA` 아닌 할당 단위의 경우 `NULL`모드인 경우 힙의 경우 .`LIMITED`
`compressed_page_count`	bigint	압축된 페이지 수입니다. 힙의 경우 새로 할당된 페이지는 압축되지 않습니다 `PAGE` . 힙은 `PAGE` 데이터를 대량으로 가져오는 경우 또는 힙을 다시 작성할 때의 두 가지 특별한 조건에서 압축됩니다. 페이지 할당을 유발하는 일반적인 DML 작업은 압축되지 않습니다 `PAGE` . 값이 원하는 임계값보다 커지면 힙 `compressed_page_count` 을 다시 빌드합니다. 클러스터형 인덱스가 있는 테이블의 경우 값은 `compressed_page_count` 압축의 `PAGE` 효과를 나타냅니다.
`hobt_id`	bigint	인덱스 또는 파티션의 힙 또는 B-트리 ID입니다. columnstore 인덱스의 경우 파티션에 대한 내부 columnstore 데이터를 추적하는 행 집합의 ID입니다. 행 집합은 데이터 힙 또는 B-트리로 저장됩니다. 부모 columnstore 인덱스와 동일한 인덱스 ID를 갖습니다. 자세한 내용은 sys.internal_partitions 참조하세요.
`columnstore_delete_buffer_state`	tinyint	`0` = `NOT_APPLICABLE` `1` = `OPEN` `2` = `DRAINING` `3` = `FLUSHING` `4` = `RETIRING` `5` = `READY` 적용 대상: SQL Server 2016(13.x) 이상 버전, Azure SQL Database 및 Azure SQL Managed Instance
`columnstore_delete_buffer_state_desc`	nvarchar(60)	`NOT VALID` - 부모 인덱스가 columnstore 인덱스가 아닙니다. `OPEN` - 삭제자와 스캐너는 이를 사용합니다. `DRAINING` - 삭제자는 드레이닝되지만 스캐너는 여전히 사용합니다. `FLUSHING` - 버퍼가 닫히고 버퍼의 행이 삭제 비트맵에 기록됩니다. `RETIRING` - 닫힌 삭제 버퍼의 행이 삭제 비트맵에 기록되었지만 스캐너가 여전히 사용 중이므로 버퍼가 잘리지 않았습니다. 열려 있는 버퍼로 충분하기 때문에 새 스캐너는 사용 중지 버퍼를 사용할 필요가 없습니다. `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문을 대체합니다.

검사 모드

함수가 실행되는 모드는 함수에서 사용되는 통계 데이터를 얻기 위해 수행되는 검사 수준을 결정합니다. 모드는 , SAMPLED또는 DETAILED.로 LIMITED지정됩니다. 함수는 테이블이나 인덱스의 지정한 파티션을 구성하는 할당 단위에 대해 페이지 체인을 탐색합니다. 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페이지 미만이 DETAILED 면 모드가 대신 SAMPLED사용됩니다.

모드는 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및 UPDATEDELETE 문) 프로세스와 테이블에 정의된 인덱스를 통해 발생합니다. 이러한 수정 내용은 일반적으로 테이블 및 인덱스의 행 간에 동일하게 분산되지 않으므로 각 페이지의 전체성은 시간에 따라 달라질 수 있습니다. 테이블의 인덱스 일부 또는 전체를 검색하는 쿼리의 경우 이러한 종류의 조각화로 인해 페이지 읽기가 늘어나 데이터 병렬 검색을 방해할 수 있습니다.

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

논리적 조각화

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

익스텐트 조각화

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

최대 성능을 위해 avg_fragmentation_in_percent 값은 가능한 한 0에 가까워야 합니다. 그러나 0%에서 10%의 값은 허용될 수 있습니다. 다시 빌드, 재구성 또는 다시 만들기와 같은 조각화를 줄이는 모든 메서드를 사용하여 이러한 값을 줄일 수 있습니다. 인덱스의 조각화 정도를 분석하는 방법에 대한 자세한 내용은 인덱스 유지 관리 최적화를 참조 하여 쿼리 성능을 개선하고 리소스 사용을 줄입니다.

인덱스 조각화 줄이기

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

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

클러스터형 인덱스를 다시 만들면 데이터가 재배포되고 전체 데이터 페이지가 생성됩니다. 사용률 수준은 CREATE INDEX의 FILLFACTOR 옵션으로 구성할 수 있습니다. 이 메서드의 단점은 삭제 및 다시 만들기 주기 중에 인덱스가 오프라인 상태이고 작업이 원자성이라는 것입니다. 인덱스 만들기가 중단되면 인덱스가 다시 만들어지지 않습니다. 자세한 내용은 CREATE INDEX를 참조하세요.
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(큰 개체) 데이터를 포함하는 페이지를 압축합니다. 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 데이터베이스 내의 지정된 개체에 대한 사용 권한입니다.
VIEW DATABASE STATE 또는 VIEW DATABASE PERFORMANCE STATE (SQL Server 2022) 개체 와일드카드 @object_id 사용하여 지정된 데이터베이스 내의 모든 개체에 대한 정보를 반환할 수 = NULL 있는 권한입니다.
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 코드 샘플은 AdventureWorks2022 또는 AdventureWorksDW2022 샘플 데이터베이스를 사용하며, 이는 Microsoft SQL Server 예시 및 커뮤니티 프로젝트(Microsoft SQL Server Samples and Community Projects) 홈 페이지에서 다운로드할 수 있습니다.

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

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

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. 힙에 대한 정보 반환

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

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 하지 않고 첫 번째 매개 변수로 지정하는 예제입니다.

-- 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 사용

다음 예제에서는 모드가 모드 결과와 다른 근사값을 DETAILED 반환하는 방법을 SAMPLED 보여줍니다.

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. 인덱스 조각화에 대한 서비스 브로커 큐 쿼리

관련 콘텐츠

피드백

추가 리소스