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 = NULLVIEW 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)