sys.dm_db_index_operational_stats (Transact-SQL)
Возвращает текущую активность ввода-вывода низкого уровня, блокировки, кратковременной блокировки и метода доступа для каждой секции таблицы или индекса в базе данных.
.gif "Значок ссылки на раздел")
Синтаксис
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.
.gif "Важное примечание") Важно! |
|---|
При использовании 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
См. также