Поделиться через


sys.dm_db_index_operational_stats (Transact-SQL)

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

Возвращает текущие операции ввода-вывода нижнего уровня, блокировку, блокировку, блокировку и действие метода доступа для каждой секции таблицы или индекса в базе данных.

Индексы с оптимизацией для памяти в данном 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 | ПО УМОЛЧАНИЮ

Идентификатор базы данных. database_id имеет небольшой размер. Допустимыми входными значениями являются идентификатор базы данных, NULL, 0 или DEFAULT. По умолчанию установлено значение 0. В данном контексте значения NULL, 0 и DEFAULT эквивалентны.

Укажите ЗНАЧЕНИЕ NULL для возврата сведений для всех баз данных в экземпляре SQL Server. Если для database_id задано значение NULL, необходимо также указать значение NULL для object_id, index_id и partition_number.

Можно указать встроенную функцию DB_ID .

object_id | NULL | 0 | ПО УМОЛЧАНИЮ

Идентификатор объекта таблицы или представления индекса включен. object_id имеет значение int.

Допустимыми входными значениями являются идентификатор таблицы, NULL, 0 или DEFAULT. По умолчанию установлено значение 0. В данном контексте значения NULL, 0 и DEFAULT эквивалентны.

Укажите значение NULL, чтобы вернуть кэшированные данные для всех таблиц и представлений в указанной базе данных. Если для object_id задано значение NULL, необходимо также указать ЗНАЧЕНИЕ NULL для index_id и partition_number.

index_id | 0 | NULL | -1 | ПО УМОЛЧАНИЮ

Идентификатор индекса. index_id имеет значение int. Допустимые входные данные — это идентификатор индекса, 0, если object_id куча, NULL, -1 или DEFAULT. Значение по умолчанию равно -1. Значения NULL, -1 и DEFAULT в данном контексте эквивалентны.

Укажите значение NULL, чтобы вернуть кэшированные данные для всех индексов базовой таблицы или представления. При указании NULL для index_id необходимо также указать ЗНАЧЕНИЕ NULL для partition_number.

partition_number | NULL | 0 | ПО УМОЛЧАНИЮ

Номер секции в объекте. partition_number является int. Допустимые входные данные — это partion_number индекса или кучи, NULL, 0 или DEFAULT. По умолчанию установлено значение 0. В данном контексте значения NULL, 0 и DEFAULT эквивалентны.

Укажите NULL, чтобы возвратить кэшированные данные для всех секций индекса или кучи.

partition_number основан на 1. Непартиментный индекс или куча имеет значение 1 partition_number.

Возвращаемая таблица

Имя столбца Тип данных Description
database_id smallint Идентификатор базы данных.

В База данных SQL Azure значения уникальны в одной базе данных или эластичном пуле, но не в логическом сервере.
object_id int Идентификатор таблицы или представления.
index_id int Идентификатор индекса или кучи.

0 = куча;
partition_number int Номер секции внутри индекса или кучи (нумерация начинается с 1).
hobt_id bigint Область применения: SQL Server 2016 (13.x) и более поздних версий База данных SQL Azure.

Идентификатор кучи данных или набора строк B-дерева, который отслеживает внутренние данные для индекса columnstore.

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 Совокупное количество слияний страниц на конечном уровне. Всегда 0 для индекса columnstore.
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), извлеченных из единицы распределения LOB_DATA. Эти страницы содержат данные, хранящиеся в столбцах текста типа, 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 Совокупное количество раз, когда ядро СУБД ждали на странице ввода-вывода.
page_io_latch_wait_in_ms bigint Совокупное количество миллисекундах ядро СУБД ожидалось на блоке ввода-вывода страницы.
tree_page_latch_wait_count bigint Подмножество page_latch_wait_count , включающее только страницы дерева B верхнего уровня. Всегда 0 для кучи или индекса columnstore.
tree_page_latch_wait_in_ms bigint Подмножество page_latch_wait_in_ms , которое включает только страницы дерева B верхнего уровня. Всегда 0 для кучи или индекса columnstore.
tree_page_io_latch_wait_count bigint Подмножество page_io_latch_wait_count, включающее только страницы дерева B верхнего уровня. Всегда 0 для кучи или индекса columnstore.
tree_page_io_latch_wait_in_ms bigint Подмножество page_io_latch_wait_in_ms , включающее только страницы дерева B верхнего уровня. Всегда 0 для кучи или индекса columnstore.
page_compression_attempt_count bigint Количество страниц, которые были оценены как пригодные для сжатия на уровне страницы для конкретных секций таблицы, индекса или индексированного представления. Включает несжатые страницы, поскольку это не привело бы к значительной экономии. Всегда 0 для индекса columnstore.
page_compression_success_count bigint Количество страниц данных, которые были сжаты с помощью сжатия PAGE для конкретной секции таблицы, индекса или индексированного представления. Всегда 0 для индекса columnstore.

Примечание.

В документации термин B-tree обычно используется в ссылке на индексы. В индексах rowstore ядро СУБД реализует дерево B+. Это не относится к индексам columnstore или индексам в таблицах, оптимизированных для памяти. Дополнительные сведения см. в руководстве по архитектуре и проектированию индексов SQL Sql Server и Azure.

Замечания

Этот динамический объект управления не принимает коррелированные параметры из CROSS APPLY и OUTER APPLY.

Вы можете использовать sys.dm_db_index_operational_stats для отслеживания времени, когда пользователи должны ждать чтения или записи в таблицу, индекс или секцию, а также определить таблицы или индексы, которые сталкиваются со значительными действиями ввода-вывода или горячими точками.

Используйте следующие столбцы для идентификации областей состязаний.

Чтобы проанализировать общий шаблон доступа к таблице или секции индекса, используйте следующие столбцы:

  • 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

    Эти столбцы показывают наличие состязания блокировок в индексе или куче и значимость состязания.

Анализ статистики физического ввода-вывода в индексе или секции кучи

  • page_io_latch_wait_count и page_io_latch_wait_in_ms

    Эти столбцы указывают, были ли произведены физические операции ввода-вывода, чтобы занести страницы индекса или кучи в память, и сколько операций ввода-вывода было произведено.

Примечания по столбцам

Значения в lob_orphan_create_count и lob_orphan_insert_count всегда должны быть равными.

Значение столбцов lob_fetch_in_pages и lob_fetch_in_bytes может быть больше нуля для некластеризованных индексов, содержащих один или несколько столбцов бизнес-объектов, как включенные столбцы. Дополнительные сведения см. в статье 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).

Значения для каждого столбца устанавливаются в нуль всякий раз, когда метаданные для кучи или индекса заносятся в кэш метаданных, и статистические данные накапливаются, пока объект кэша не удаляется из кэша метаданных. Таким образом, активная куча или индекс, скорее всего, всегда будет иметь свои метаданные в кэше, а совокупные счетчики могут отражать действия, так как экземпляр SQL Server был последним запуском. Метаданные для менее активной кучи или индекса будут перемещаться в кэш и из него по мере их использования. В результате метаданные могут иметь или не иметь действительных значений. Удаление индекса приведет к удалению соответствующих статистических данных из памяти, и они больше не будут передаваться функцией. При других DDL-операциях с индексом может произойти обнуление статистических данных.

Использование системных функций для указания значений параметров

Функции Transact-SQL можно использовать DB_ID и OBJECT_ID , чтобы указать значение для параметров database_id и object_id . Однако передавая значения, которые не допустимы для этих функций, можно получить неожиданные результаты. Всегда следует проверять, что функции DB_ID и OBJECT_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).

Примеры

А. Возвращение данных для указанной таблицы

В следующем примере возвращаются сведения обо всех индексах и секциях Person.Address таблицы в базе данных AdventureWorks2022. Выполнение этого запроса требует как минимум разрешения CONTROL на таблицу Person.Address.

Внимание

При использовании функций Transact-SQL DB_ID и OBJECT_ID для возврата значения параметра всегда убедитесь, что возвращается допустимый идентификатор. Если имя базы данных или объекта не может быть найдено, например если база данных или объект не существуют или неправильно записаны, то обе функции возвратят значение NULL. Функция sys.dm_db_index_operational_stats интерпретирует значение NULL как значение шаблона, соответствующее всем базам данных или все объектам. Так как эта операция может быть непреднамеренной, примеры в этом разделе демонстрируют безопасный способ определения идентификаторов базы данных и объекта.

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)