sys.dm_db_index_operational_stats

Статья
12/15/2008

Изменения: 17 июля 2006 г.

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

Значок ссылки на раздел Синтаксические обозначения в 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	Совокупное количество строк конечного уровня, которые помечены как удаленные, но еще не удалены. Эти строки будут удалены потоком очистки в установленные интервалы времени. Это значение не включает строки, которые сохранены, из-за невыполненной транзакции изоляции моментальных снимков. Дополнительные сведения о транзакции изоляции моментальных снимков см. в разделе Уровни изоляции, основанные на управлении версиями строк, в ядре СУБД.
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 ожидал кратковременную блокировку страницы ввода-вывода.

Замечания

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

Сброс счетчиков

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

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

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

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

Разрешения

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

Разрешение CONTROL на указанный объект в базе данных.
Разрешение VIEW DATABASE STATE для возврата сведений обо всех объектах в пределах указанной базы данных с помощью использования шаблона базы данных @object_id = NULL.
Разрешение VIEW SERVER STATE для получения сведений обо всех базах данных с использованием символа-шаблона @database_id = NULL.

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

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

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

Примеры

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

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

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

При использовании 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

См. также

Журнал изменений

Версия	Журнал
17 июля 2006 г.	Новое содержимое Разъяснены требования уровня совместимости база данных для использования функции DB_ID() в первом параметре.
14 апреля 2006 г.	Измененное содержимое В синтаксис аргументов database_id, object_id, и partition_number добавлены входные значения 0 и DEFAULT. В синтаксис аргумента index_id добавлены входные значения -1 и DEFAULT.
5 декабря 2005 г.	Новое содержимое Добавлен раздел «Использование системных функций для указания значений параметров». Измененное содержимое Изменен раздел «Разрешения».

17 июля 2006 г.

Новое содержимое

Разъяснены требования уровня совместимости база данных для использования функции DB_ID() в первом параметре.

14 апреля 2006 г.

Измененное содержимое

В синтаксис аргументов database_id, object_id, и partition_number добавлены входные значения 0 и DEFAULT.
В синтаксис аргумента index_id добавлены входные значения -1 и DEFAULT.

5 декабря 2005 г.