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


sys.dm_db_index_operational_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 | DEFAULT
    Идентификатор базы данных. Аргумент database_id имеет тип smallint. Допустимыми входными значениями являются идентификатор базы данных, NULL, 0 или DEFAULT. Значение по умолчанию — 0. Значения NULL, 0 и DEFAULT в данном контексте эквивалентны.

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

    Может быть указана встроенная функция DB_ID. При использовании функции DB_ID без указания имени базы данных уровень совместимости текущей базы данных должен быть равен 90.

  • object_id | NULL | 0 | DEFAULT
    Идентификатор объекта таблицы или представления, которое содержит индекс. Аргумент object_id имеет тип int.

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

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

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

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

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

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

    Аргумент partition_number отсчитывается от 1. Значением аргумента partition_number для несекционированного индекса или кучи является 1.

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

Имя столбца

Тип данных

Описание

database_id

smallint

Идентификатор базы данных.

object_id

int

Идентификатор таблицы или представления.

index_id

int

Идентификатор индекса или кучи.

0 = куча

partition_number

int

Номер секции внутри индекса или кучи (нумерация начинается с 1).

leaf_insert_count

bigint

Совокупное количество вставок конечного уровня.

leaf_delete_count

bigint

Совокупное количество удалений конечного уровня.

leaf_update_count

bigint

Совокупное количество обновлений конечного уровня.

leaf_ghost_count

bigint

Совокупное количество строк конечного уровня, которые помечены как удаленные, но еще не удалены. Эти строки будут удалены потоком очистки в установленные интервалы времени. Это значение не включает строки, которые сохранены, из-за необработанной транзакции изоляции моментальных снимков. Дополнительные сведения о транзакции изоляции моментальных снимков см. в разделе Уровни изоляции, основанные на управлении версиями строк, в компоненте Database Engine.

nonleaf_insert_count

bigint

Совокупное количество вставок выше конечного уровня.

0 = куча

nonleaf_delete_count

bigint

Совокупное количество удалений выше конечного уровня.

0 = куча

nonleaf_update_count

bigint

Совокупное количество обновлений выше конечного уровня.

0 = куча

leaf_allocation_count

bigint

Совокупное количество размещений страниц конечного уровня в индексе или куче.

Для индекса размещение страницы соответствует разбиению страницы.

nonleaf_allocation_count

bigint

Совокупное количество размещений страниц, вызванных разбиениями страниц выше конечного уровня.

0 = куча

leaf_page_merge_count

bigint

Совокупное количество слияний страниц на конечном уровне.

nonleaf_page_merge_count

bigint

Совокупное количество слияний страниц выше конечного уровня.

0 = куча

range_scan_count

bigint

Совокупное количество просмотров диапазонов и таблиц, запущенных на индексе или куче.

singleton_lookup_count

bigint

Совокупное количество получений одиночных строк из индекса или кучи.

forwarded_fetch_count

bigint

Число строк, выбранных через перенаправляющую запись.

0 = индексы.

lob_fetch_in_pages

bigint

Совокупное количество страниц больших объектов (LOB), извлеченных из единицы распределения LOB_DATA. Эти страницы содержат данные, которые хранятся в столбцах типа text, 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, которые были принудительно отправлены вне строки. Дополнительные сведения см. в разделе Превышающие размер страницы данные строки, превышающие 8 КБ. Дополнительные сведения о единицах распределения см. в разделе Организация таблиц и индексов.

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

Совокупное количество раз, когда компонент Database Engine ожидал блокировку строки.

row_lock_wait_in_ms

bigint

Общее время в миллисекундах, которое компонент Database Engine ожидал блокировку строки.

page_lock_count

bigint

Совокупное количество запрошенных блокировок страниц.

page_lock_wait_count

bigint

Совокупное количество раз, которое компонент Database Engine ожидал блокировку страницы.

page_lock_wait_in_ms

bigint

Общее время в миллисекундах, которое компонент Database Engine ожидал блокировку страницы.

index_lock_promotion_attempt_count

bigint

Совокупное количество раз, которое компонент Database Engine пытался укрупнять блокировки.

index_lock_promotion_count

bigint

Совокупное количество раз, которое компонент Database Engine укрупнял блокировки.

page_latch_wait_count

bigint

Совокупное количество раз, когда компонент Database Engine ожидал из-за конфликтов кратковременной блокировки.

page_latch_wait_in_ms

bigint

Совокупное количество миллисекунд, которое компонент Database Engine ожидал из-за конфликтов кратковременной блокировки.

page_io_latch_wait_count

bigint

Совокупное количество раз, когда компонент Database Engine ожидал кратковременную блокировку страницы ввода-вывода.

page_io_latch_wait_in_ms

bigint

Совокупное количество миллисекунд, которое компонент Database Engine ожидал кратковременную блокировку страницы ввода-вывода.

tree_page_latch_wait_count

bigint

Подмножество множества page_latch_wait_count, которое включает только страницы сбалансированного дерева верхнего уровня. Для кучи всегда равно 0.

tree_page_latch_wait_in_ms

bigint

Подмножество множества page_latch_wait_in_ms, которое включает только страницы сбалансированного дерева верхнего уровня. Для кучи всегда равно 0.

tree_page_io_latch_wait_count

bigint

Подмножество множества page_io_latch_wait_count, которое включает только страницы сбалансированного дерева верхнего уровня. Для кучи всегда равно 0.

tree_page_io_latch_wait_in_ms

bigint

Подмножество множества page_io_latch_wait_in_ms, которое включает только страницы сбалансированного дерева верхнего уровня. Для кучи всегда равно 0.

page_compression_attempt_count

bigint

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

page_compression_success_count

bigint

Количество страниц данных, которые были сжаты с помощью сжатия PAGE для конкретной секции таблицы, индекса или индексированного представления.

Замечания

Этот объект DMO не принимает коррелированные параметры из 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

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

  • 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 может быть больше нуля для некластеризованных индексов, содержащих один или более столбцов больших объектов в качестве включенных. Дополнительные сведения см. в разделе Индекс с включенными столбцами. Точно так же значение в столбцах row_overflow_fetch_in_pages и row_overflow_fetch_in_bytes может быть больше 0 для некластеризованных индексов, если индекс содержит столбцы, которые могут быть принудительно отправлены вне строки. Дополнительные сведения см. в разделе Превышающие размер страницы данные строки, превышающие 8 КБ.

Сброс счетчиков в кэше метаданных

Данные, возвращенные sys.dm_db_index_operational_stats, существуют, пока объект кэша метаданных, представляющий кучу или индекс, является доступным. Эти данные не являются постоянными и не согласованы на уровне транзакций. Это означает, что эти счетчики не позволяют определить факт использования индекса или время, когда индекс применялся последний раз. Дополнительные сведения см. в разделе sys.dm_db_index_usage_stats (Transact-SQL).

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

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

Для указания значений параметров database_id и object_id можно использовать функции языка Transact-SQLDB_ID и OBJECT_ID. Однако передавая значения, которые не допустимы для этих функций, можно получить неожиданные результаты. Всегда следует проверять, что функции DB_ID и OBJECT_ID возвращают допустимый идентификатор. Дополнительные сведения см. в подразделе «Примечания» раздела sys.dm_db_index_physical_stats (Transact-SQL).

Разрешения

Требуются следующие разрешения.

  • Разрешение CONTROL на указанный объект в базе данных.

  • Разрешение VIEW DATABASE STATE для получения сведений обо всех объектах в пределах указанной базы данных с помощью использования шаблона базы данных @object\_id = NULL.

  • Разрешение VIEW SERVER STATE для получения сведений обо всех базах данных с использованием символа-шаблона @database\_id = NULL.

Предоставление разрешения VIEW DATABASE STATE позволяет всем объектам в базе данных быть возвращаемыми, независимо от любых разрешений CONTROL, запрещенных на определенные объекты.

Запрет разрешения VIEW DATABASE STATE запрещает всем объектам в базе данных быть возвращаемыми, независимо от любых разрешений CONTROL, предоставленных на определенные объекты. Кроме того, если для базы данных указан шаблон @database\_id=NULL, эта база данных пропускается.

Дополнительные сведения см. в разделе Динамические административные представления и функции (Transact-SQL).

Примеры

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

В следующем примере возвращаются сведения по всем индексам и секциям таблицы Person.Address в базе данных AdventureWorks. Выполнение этого запроса требует как минимум разрешения 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'AdventureWorks');
SET @object_id = OBJECT_ID(N'AdventureWorks.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

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

В следующем примере возвращаются сведения по всем таблицам и индексам в экземпляре SQL Server. Выполнение этого запроса требует разрешения VIEW SERVER STATE.

SELECT * FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL);
GO