sys.dm_db_index_operational_stats(Transact-SQL)

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

데이터베이스에 있는 테이블 또는 인덱스의 각 파티션에 대한 현재 하위 수준 I/O, 잠금, 래치 및 액세스 메서드 작업을 반환합니다.

메모리 최적화 인덱스는 이 DMV에 표시되지 않습니다.

참고 항목

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

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_number NULL도 지정해야 합니다.

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로 설정되었습니다.

반환된 테이블

열 이름	데이터 형식	설명
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)