sys.dm_db_index_operational_stats(Transact-SQL)
적용 대상:
SQL ServerAzure SQL 데이터베이스Azure SQL Managed Instance
데이터베이스에 있는 테이블 또는 인덱스의 각 파티션에 대한 현재 하위 수준 I/O, 잠금, 래치 및 액세스 메서드 작업을 반환합니다.
메모리 최적화 인덱스는 이 DMV에 표시되지 않습니다.
참고 항목
sys.dm_db_index_operational_stats 메모리 최적화 인덱스에 대한 정보를 반환하지 않습니다. 메모리 최적화 인덱스 사용에 대한 자세한 내용은 sys.dm_db_xtp_index_stats(Transact-SQL)를 참조하세요.
구문
sys.dm_db_index_operational_stats (
{ database_id | NULL | 0 | DEFAULT }
, { object_id | NULL | 0 | DEFAULT }
, { index_id | 0 | NULL | -1 | DEFAULT }
, { partition_number | NULL | 0 | 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를 지정할 수 있습니다.
object_id | NULL | 0 | 기본값
인덱스가 있는 테이블 또는 뷰의 개체 ID입니다. object_id int입니다.
올바른 입력은 테이블 및 뷰의 ID 번호, NULL, 0 또는 DEFAULT입니다. 기본값은 0입니다. 이 컨텍스트에서 NULL, 0 및 DEFAULT는 동등한 값입니다.
지정된 데이터베이스의 모든 테이블 및 뷰에 대해 캐시된 정보를 반환하려면 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 | 기본값
개체의 파티션 번호입니다.
인덱스 또는 힙의 모든 파티션에 대해 캐시된 정보를 반환하려면 NULL을 지정합니다.
partition_number 1부터 시작하는 경우 분할되지 않은 인덱스 또는 힙은 partition_number 1로 설정되었습니다.
반환된 테이블
| 열 이름 | 데이터 형식 | 설명 |
|---|---|---|
| database_id | smallint | 데이터베이스 ID입니다. Azure SQL Database에서 값은 단일 데이터베이스 또는 탄력적 풀 내에서 고유하지만 논리 서버 내에는 고유하지 않습니다. |
| object_id | int | 테이블 또는 뷰의 ID입니다. |
| index_id | int | 인덱스 또는 힙의 ID입니다. 0 = 힙 |
| partition_number | int | 인덱스 또는 힙 내의 1부터 시작하는 파티션 번호입니다. |
| hobt_id | bigint |
적용 대상: SQL Server 2016(13.x) 이상 버전, Azure SQL Database. columnstore 인덱스의 내부 데이터를 추적하는 데이터 힙 또는 B-트리 행 집합의 ID입니다. NULL - 내부 columnstore 행 집합이 아닙니다. 자세한 내용은 sys.internal_partitions(Transact-SQL)를 참조하세요 . |
| leaf_insert_count | bigint | 리프 수준 삽입의 누적 개수입니다. |
| leaf_delete_count | bigint | 리프 수준 삭제의 누적 수입니다. leaf_delete_count 먼저 고스트로 표시되지 않은 삭제된 레코드에 대해서만 증가합니다. 먼저 삭제된 레코드의 경우 leaf_ghost_count 대신 증가합니다. |
| leaf_update_count | bigint | 리프 수준 업데이트의 누적 수입니다. |
| leaf_ghost_count | bigint | 삭제하도록 표시되어 있지만 아직 제거되지 않은 리프 수준 행의 누적 개수입니다. 이 개수에는 고스트로 표시되지 않고 즉시 삭제되는 레코드가 포함되지 않습니다. 이러한 행은 설정된 간격으로 정리 스레드에 의해 제거됩니다. 이 값은 미해결 스냅샷 격리 트랜잭션으로 인해 유지되는 행을 포함하지 않습니다. |
| nonleaf_insert_count | bigint | 리프 수준 위에서 발생한 삽입의 누적 횟수입니다. 0 = 힙 또는 columnstore |
| nonleaf_delete_count | bigint | 리프 수준 위에서 발생한 삭제의 누적 횟수입니다. 0 = 힙 또는 columnstore |
| nonleaf_update_count | bigint | 리프 수준 이상의 누적 업데이트 수입니다. 0 = 힙 또는 columnstore |
| leaf_allocation_count | bigint | 인덱스 또는 힙의 리프 수준 페이지 할당 누적 수입니다. 인덱스의 경우 페이지 할당은 페이지 분할에 해당합니다. |
| nonleaf_allocation_count | bigint | 리프 수준 이상의 페이지 분할로 인한 페이지 할당의 누적 수입니다. 0 = 힙 또는 columnstore |
| leaf_page_merge_count | bigint | 리프 수준에서 발생한 페이지 병합의 누적 횟수입니다. columnstore 인덱스의 경우 항상 0입니다. |
| nonleaf_page_merge_count | bigint | 리프 수준 이상의 페이지 병합 누적 수입니다. 0 = 힙 또는 columnstore |
| range_scan_count | bigint | 인덱스 또는 힙에서 시작된 범위 및 테이블 검색의 누적 횟수입니다. |
| singleton_lookup_count | bigint | 인덱스 또는 힙에서 단일 행 검색의 누적 수입니다. |
| forwarded_fetch_count | bigint | 전달 레코드를 통해 가져온 행의 수입니다. 0 = 인덱스 |
| lob_fetch_in_pages | bigint | LOB_DATA 할당 단위에서 검색된 LOB(큰 개체) 페이지의 누적 수입니다. 이러한 페이지에는 텍스트, ntext, image, varchar(max), nvarchar(max), varbinary(max) 및 xml 형식의 열에 저장된 데이터가 포함됩니다. 자세한 내용은 데이터 형식(Transact-SQL)을 참조하세요. |
| lob_fetch_in_bytes | bigint | 검색된 LOB 데이터 바이트의 누적 수입니다. |
| lob_orphan_create_count | bigint | 대량 작업을 위해 만든 분리된 LOB 값의 누적 수입니다. 0 = 비클러스터형 인덱스 |
| lob_orphan_insert_count | bigint | 대량 작업 중에 삽입된 고아 LOB의 누적값입니다. 0 = 비클러스터형 인덱스 |
| row_overflow_fetch_in_pages | bigint | ROW_OVERFLOW_DATA 할당 단위에서 검색된 행 오버플로 데이터 페이지의 누적 개수입니다. 이러한 페이지에는 행에서 벗어난 varchar(n), nvarchar(n), varbinary(n) 및 sql_variant 형식의 열에 저장된 데이터가 포함됩니다. |
| row_overflow_fetch_in_bytes | bigint | 검색된 행 오버플로 데이터 바이트의 누적값입니다. |
| column_value_push_off_row_count | bigint | 삽입되거나 업데이트된 행이 페이지 내에 맞도록 행에서 푸시되는 LOB 데이터 및 행 오버플로 데이터에 대한 열 값의 누적 수입니다. |
| column_value_pull_in_row_count | bigint | 행에서 끌어온 LOB 데이터 및 행 오버플로 데이터에 대한 열 값의 누적 개수입니다. 이 문제는 업데이트 작업에서 레코드의 공간을 확보하고 LOB_DATA 또는 ROW_OVERFLOW_DATA 할당 단위에서 IN_ROW_DATA 할당 단위로 하나 이상의 오프 행 값을 끌어올 수 있는 기회를 제공하는 경우에 발생합니다. |
| row_lock_count | bigint | 요청된 행 잠금의 누적 수입니다. |
| row_lock_wait_count | bigint | 데이터베이스 엔진 행 잠금에서 대기한 누적 횟수입니다. |
| row_lock_wait_in_ms | bigint | 데이터베이스 엔진 행 잠금에서 대기한 총 시간(밀리초)입니다. |
| page_lock_count | bigint | 요청된 페이지 잠금의 누적 개수입니다. |
| page_lock_wait_count | bigint | 데이터베이스 엔진 페이지 잠금에서 대기한 누적 횟수입니다. |
| page_lock_wait_in_ms | bigint | 데이터베이스 엔진 페이지 잠금에서 대기한 총 시간(밀리초)입니다. |
| index_lock_promotion_attempt_count | bigint | 데이터베이스 엔진 잠금을 에스컬레이션하려고 시도한 누적 횟수입니다. |
| index_lock_promotion_count | bigint | 데이터베이스 엔진 에스컬레이션된 잠금의 누적 횟수입니다. |
| page_latch_wait_count | bigint | 래치 경합으로 인해 데이터베이스 엔진 대기한 누적 횟수입니다. |
| page_latch_wait_in_ms | bigint | 래치 경합으로 인해 데이터베이스 엔진 대기한 누적 시간(밀리초)입니다. |
| page_io_latch_wait_count | bigint | I/O 페이지 래치에서 데이터베이스 엔진 대기한 누적 횟수입니다. |
| page_io_latch_wait_in_ms | bigint | 페이지 I/O 래치에서 대기한 데이터베이스 엔진 누적 시간(밀리초)입니다. |
| tree_page_latch_wait_count | bigint | 상위 수준 B-트리 페이지만 포함하는 page_latch_wait_count 하위 집합입니다. 힙 또는 columnstore 인덱스인 경우 항상 0입니다. |
| tree_page_latch_wait_in_ms | bigint | 상위 수준 B-트리 페이지만 포함하는 page_latch_wait_in_ms 하위 집합입니다. 힙 또는 columnstore 인덱스인 경우 항상 0입니다. |
| tree_page_io_latch_wait_count | bigint | 상위 수준 B-트리 페이지만 포함하는 page_io_latch_wait_count 하위 집합입니다. 힙 또는 columnstore 인덱스인 경우 항상 0입니다. |
| tree_page_io_latch_wait_in_ms | bigint | 상위 수준 B-트리 페이지만 포함하는 page_io_latch_wait_in_ms 하위 집합입니다. 힙 또는 columnstore 인덱스인 경우 항상 0입니다. |
| page_compression_attempt_count | bigint | 테이블, 인덱스 또는 인덱싱된 뷰의 특정 파티션에 대한 PAGE 수준 압축에 대해 평가된 페이지 수입니다. 상당한 절감액을 달성할 수 없어 압축되지 않은 페이지가 포함됩니다. columnstore 인덱스의 경우 항상 0입니다. |
| page_compression_success_count | bigint | 테이블, 인덱스 또는 인덱싱된 뷰의 특정 파티션의 PAGE 압축을 사용하여 압축된 데이터 페이지 수입니다. columnstore 인덱스의 경우 항상 0입니다. |
참고 항목
설명서는 인덱스를 지칭할 때 B-트리라는 용어를 사용합니다. rowstore 인덱스에서 데이터베이스 엔진은 B+ 트리를 구현합니다. 이는 columnstore 인덱스나 메모리 최적화 테이블 인덱스에는 적용되지 않습니다. 자세한 내용은 SQL Server 및 Azure SQL 인덱스 아키텍처 및 디자인 가이드를 참조하세요.
설명
이 동적 관리 개체는 상호 관련된 매개 변수 CROSS APPLY 를 허용하지 않습니다.OUTER APPLY
sys.dm_db_index_operational_stats 사용하여 사용자가 테이블, 인덱스 또는 파티션을 읽거나 쓸 때까지 기다려야 하는 시간을 추적하고 중요한 I/O 활동 또는 핫 스폿이 발생하는 테이블 또는 인덱스를 식별할 수 있습니다.
다음 열을 사용하여 경합 영역을 식별할 수 있습니다.
테이블 또는 인덱스 파티션에 대한 일반적인 액세스 패턴을 분석하려면 다음 열을 사용합니다.
leaf_insert_count
leaf_delete_count
leaf_update_count
leaf_ghost_count
range_scan_count
singleton_lookup_count
래치 및 잠금 경합을 식별하려면 다음 열을 사용합니다.
page_latch_wait_count 및 page_latch_wait_in_ms
이러한 열은 인덱스 또는 힙에 래치 경합이 있는지 여부와 경합의 중요성을 나타냅니다.
row_lock_count 및 page_lock_count
이러한 열은 데이터베이스 엔진 행 및 페이지 잠금을 획득하려고 시도한 횟수를 나타냅니다.
row_lock_wait_in_ms 및 page_lock_wait_in_ms
이 두 열은 인덱스 또는 힙에 잠금 경합이 있는지 여부와 해당 경합의 의미를 나타냅니다.
인덱스 또는 힙 파티션에서 물리적 I/O의 통계를 분석하려면
page_io_latch_wait_count 및 page_io_latch_wait_in_ms
이 두 열은 인덱스 또는 힙 페이지를 메모리로 가져가기 위한 물리적 I/O가 발생했는지 여부와 I/O 발생 횟수를 나타냅니다.
열 설명
lob_orphan_create_count 및 lob_orphan_insert_count 값은 항상 같아야 합니다.
포함된 열로 하나 이상의 LOB 열을 포함하는 비클러스터형 인덱스의 경우 열 lob_fetch_in_pages 및 lob_fetch_in_bytes 값이 0보다 클 수 있습니다. 자세한 내용은 Create Indexes with Included Columns을 참조하세요. 마찬가지로 인덱스에 행에서 푸시할 수 있는 열이 포함된 경우 비클러스터형 인덱스의 경우 row_overflow_fetch_in_pages 및 row_overflow_fetch_in_bytes 열의 값이 0보다 클 수 있습니다.
메타데이터 캐시의 카운터를 다시 설정하는 방법
sys.dm_db_index_operational_stats 반환된 데이터는 힙 또는 인덱스를 나타내는 메타데이터 캐시 개체를 사용할 수 있는 한 존재합니다. 이 데이터는 영구적이거나 트랜잭션적으로 일관되지 않습니다. 즉, 이러한 카운터로는 인덱스가 사용되었는지 여부나 인덱스가 마지막으로 사용된 시기를 확인할 수 없습니다. 이에 대한 자세한 내용은 sys.dm_db_index_usage_stats(Transact-SQL)를 참조 하세요.
각 열의 값은 힙 또는 인덱스에 대한 메타데이터를 메타데이터 캐시로 가져오고 메타데이터 캐시에서 캐시 개체가 제거될 때까지 통계가 누적될 때마다 0으로 설정됩니다. 따라서 활성 힙 또는 인덱스에는 항상 해당 메타데이터가 캐시에 있을 수 있으며, 누적 개수는 SQL Server 인스턴스가 마지막으로 시작된 이후의 활동을 반영할 수 있습니다. 덜 활성 힙 또는 인덱스의 메타데이터는 사용되는 캐시 내부 및 외부로 이동합니다. 결과적으로 사용할 수 있는 값이 있을 수도 있고 없을 수도 있습니다. 인덱스를 삭제하면 해당 통계가 메모리에서 제거되고 함수에서 더 이상 보고되지 않습니다. 인덱스에 대한 다른 DDL 작업으로 인해 통계 값이 0으로 다시 설정될 수 있습니다.
시스템 함수를 사용하여 매개 변수 값 지정
Transact-SQL 함수 DB_ID 및 OBJECT_ID 사용하여 database_id 및 object_id 매개 변수의 값을 지정할 수 있습니다. 그러나 이러한 함수에 유효하지 않은 값을 전달하면 의도하지 않은 결과가 발생할 수 있습니다. DB_ID 또는 OBJECT_ID 사용할 때 항상 유효한 ID가 반환되는지 확인합니다. 자세한 내용은 sys.dm_db_index_physical_stats 설명 섹션 (Transact-SQL)을 참조하세요.
사용 권한
다음 권한이 필요합니다.
CONTROL데이터베이스 내의 지정된 개체에 대한 사용 권한VIEW DATABASE STATE또는VIEW DATABASE PERFORMANCE STATE(SQL Server 2022) 개체 와일드카드 @object_id = NULL을 사용하여 지정된 데이터베이스 내의 모든 개체에 대한 정보를 반환할 수 있는 권한VIEW SERVER STATEVIEW SERVER PERFORMANCE STATE(SQL Server 2022) 데이터베이스 와일드카드 @database_id = NULL을 사용하여 모든 데이터베이스에 대한 정보를 반환할 수 있는 권한
부여를 VIEW DATABASE STATE 사용하면 특정 개체에 대해 거부된 CONTROL 권한에 관계없이 데이터베이스의 모든 개체를 반환할 수 있습니다.
거부하면 VIEW DATABASE STATE 특정 개체에 대해 부여된 CONTROL 권한에 관계없이 데이터베이스의 모든 개체가 반환될 수 없습니다. 또한 데이터베이스 와일드카드 @database_id=NULL 를 지정하면 데이터베이스가 생략됩니다.
자세한 내용은 동적 관리 뷰 및 함수(Transact-SQL)를 참조하세요.
예제
A. 지정된 테이블에 대한 정보 반환
다음 예제에서는 AdventureWorks2022 데이터베이스에 Person.Address 있는 테이블의 모든 인덱스 및 파티션에 대한 정보를 반환합니다. 이 쿼리를 실행하려면 최소한 테이블에 대한 Person.Address CONTROL 권한이 필요합니다.
Important
Transact-SQL 함수 DB_ID 사용하고 매개 변수 값을 반환하는 OBJECT_ID 경우 항상 유효한 ID가 반환되는지 확인합니다. 존재하지 않는 이름을 입력하거나 철자를 잘못 입력하는 등의 이유로 데이터베이스 또는 개체 이름을 찾을 수 없으면 두 함수 모두 NULL을 반환합니다. sys.dm_db_index_operational_stats 함수는 NULL을 모든 데이터베이스나 모든 개체를 지정하는 와일드카드 값으로 해석합니다. 이는 의도하지 않은 결과일 수 있으므로 이 섹션의 예에서는 안전하게 데이터베이스 및 개체 ID를 확인하는 방법을 보여 줍니다.
DECLARE @db_id int;
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_operational_stats(@db_id, @object_id, NULL, NULL);
END;
GO
B. 모든 테이블 및 인덱스에 대한 정보 반환
다음 예제에서는 SQL Server 인스턴스 내의 모든 테이블 및 인덱스에 대한 정보를 반환합니다. 이 쿼리를 실행하려면 VIEW SERVER STATE 권한이 필요합니다.
SELECT * FROM sys.dm_db_index_operational_stats( NULL, NULL, NULL, NULL);
GO
참고 항목
동적 관리 뷰 및 함수(Transact-SQL)
인덱스 관련 동적 관리 뷰 및 함수(Transact-SQL)
성능 모니터링 및 튜닝
sys.dm_db_index_physical_stats(Transact-SQL)
sys.dm_db_index_usage_stats(Transact-SQL)
sys.dm_os_latch_stats(Transact-SQL)
sys.dm_db_partition_stats(Transact-SQL)
sys.allocation_units(Transact-SQL)
sys.indexes(Transact-SQL)