Продажи: 1-800-867-1389

sys.dm_db_index_physical_stats

Обновлено: Апрель 2011 г.

Данный раздел УСТАРЕЛ. Последняя версия доступна в справочнике по Transact-SQL (SQL 14).

ImportantВажно!
Этот раздел не поддерживается. См. текущую версию в разделе sys.dm_db_index_physical_stats.

Возвращает сведения о размере и фрагментации данных и индексов указанной таблицы или представления. Для индекса возвращается одна строка для каждого уровня сбалансированного дерева в каждой секции. Для кучи возвращается одна строка для единицы распределения IN_ROW_DATA каждой секции. Для данных больших объектов (LOB) возвращается одна строка для единицы распределения LOB_DATA каждой секции. Если в таблице существуют превышающие размер страницы данные строки, то возвращается одна строка для единицы распределения ROW_OVERFLOW_DATA каждой секции. Не возвращает информацию об индексах columnstore с оптимизированной памятью xVelocity.

Синтаксические обозначения в Transact-SQL

sys.dm_db_index_physical_stats( 
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | NULL | 0 | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
  , { mode | NULL | 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.

mode | NULL | DEFAULT
Имя режима. Параметр mode задает уровень просмотра, используемый для получения статистики. Параметр mode имеет тип sysname. Допустимыми входными данными являются значения DEFAULT, NULL, LIMITED, SAMPLED и DETAILED. Значение по умолчанию (NULL) соответствует значению LIMITED.

 

Имя столбца Тип данных Описание

database_id

smallint

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

object_id

int

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

index_id

int

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

0 = куча.

partition_number

int

Номер секции объекта, значения начинаются с 1; для таблицы, представления или индекса.

1 = несекционированный индекс или куча.

index_type_desc

nvarchar(60)

Описание типа индекса:

HEAP

CLUSTERED INDEX

NONCLUSTERED INDEX

PRIMARY XML INDEX

SPATIAL INDEX

XML INDEX

alloc_unit_type_desc

nvarchar(60)

Описание типа единицы распределения:

IN_ROW_DATA

LOB_DATA

ROW_OVERFLOW_DATA

Единица распределения LOB_DATA содержит данные, которые хранятся в столбцах типа text, ntext, image, varchar(max), nvarchar(max), varbinary(max) и xml. Дополнительные сведения см. в разделе Data Types (Transact-SQL).

Единица распределения ROW_OVERFLOW_DATA содержит данные, которые выведены за пределы строки и хранятся в столбцах типа varchar(n), nvarchar(n), varbinary(n) и sql_variant.

index_depth

tinyint

Количество уровней индекса.

1 = куча или единица распределения LOB_DATA или ROW_OVERFLOW_DATA.

index_level

tinyint

Текущий уровень индекса.

0 для конечного уровня индекса, для кучи и единиц распределения LOB_DATA или ROW_OVERFLOW_DATA.

Значения больше нуля соответствуют неконечным уровням индекса. Наибольшее значение аргумент index_level имеет на корневом уровне индекса.

Неконечные уровни индекса обрабатываются только в том случае, если задан аргумент mode = DETAILED.

avg_fragmentation_in_percent

float

Логическая фрагментация для индексов или фрагментация экстентов для куч в единице распределения IN_ROW_DATA.

Значение измеряется в процентах и учитывает несколько файлов. Определения логической фрагментации и фрагментации экстентов см. в разделе «Примечания».

0 для единиц распределения LOB_DATA и ROW_OVERFLOW_DATA.

NULL для куч, если указан аргумент mode = SAMPLED.

fragment_count

bigint

Количество фрагментов на конечном уровне единицы распределения IN_ROW_DATA. Дополнительные сведения о фрагментах см. в разделе «Примечания».

NULL для неконечных уровней индекса и единиц распределения LOB_DATA или ROW_OVERFLOW_DATA.

NULL для куч, если указан аргумент mode = SAMPLED.

avg_fragment_size_in_pages

float

Среднее количество страниц в одном фрагменте на конечном уровне единицы распределения IN_ROW_DATA.

NULL для неконечных уровней индекса и единиц распределения LOB_DATA или ROW_OVERFLOW_DATA.

NULL для куч, если указан аргумент mode = SAMPLED.

page_count

bigint

Общее количество страниц индекса или данных.

Для индекса — общее количество страниц индекса на текущем уровне сбалансированного дерева в единице распределения IN_ROW_DATA.

Для кучи — общее количество страниц данных в единице распределения IN_ROW_DATA.

Для единиц распределения LOB_DATA или ROW_OVERFLOW_DATA — общее количество страниц в единице распределения.

avg_page_space_used_in_percent

float

Средний процент доступного места для хранения данных, используемого всеми страницами.

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

Для кучи — среднее значение для всех страниц данных в единице распределения IN_ROW_DATA.

Для единиц распределения LOB_DATA или ROW_OVERFLOW_DATA — среднее значение для всех страниц в единице распределения.

NULL, если задан аргумент mode = LIMITED.

record_count

bigint

Общее количество записей.

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

Для кучи — общее количество записей в единице распределения IN_ROW_DATA.

noteПримечание
Для кучи число записей, возвращаемых этой функцией, может не соответствовать числу строк, возвращаемых запросом SELECT COUNT(*) из кучи. Это происходит потому, что строка может содержать несколько записей. Например, при обновлении одна строка кучи может иметь указывающую запись и перенаправленную запись как результат операции обновления. Также большинство больших LOB-строк разбиты на различные записи в хранилище LOB_DATA.

Для единиц распределения LOB_DATA или ROW_OVERFLOW_DATA — общее количество записей во всей единице распределения.

NULL, если задан аргумент mode = LIMITED.

ghost_record_count

bigint

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

0 для неконечных уровней индекса в единице распределения IN_ROW_DATA.

NULL, если задан аргумент mode = LIMITED.

version_ghost_record_count

bigint

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

0 для неконечных уровней индекса в единице распределения IN_ROW_DATA.

NULL, если задан аргумент mode = LIMITED.

min_record_size_in_bytes

int

Минимальный размер записи в байтах.

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

Для кучи — минимальный размер записи в единице распределения IN_ROW_DATA.

Для единиц распределения LOB_DATA или ROW_OVERFLOW_DATA — минимальный размер записи во всей единице распределения.

NULL, если задан аргумент mode = LIMITED.

max_record_size_in_bytes

int

Максимальный размер записи в байтах.

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

Для кучи — максимальный размер записи в единице распределения IN_ROW_DATA.

Для единиц распределения LOB_DATA или ROW_OVERFLOW_DATA — максимальный размер записи во всей единице распределения.

NULL, если задан аргумент mode = LIMITED.

avg_record_size_in_bytes

float

Средний размер записи в байтах.

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

Для кучи — средний размер записи в единице распределения IN_ROW_DATA.

Для единиц распределения LOB_DATA или ROW_OVERFLOW_DATA — средний размер записи во всей единице распределения.

NULL, если задан аргумент mode = LIMITED.

forwarded_record_count

bigint

Количество записей в куче, содержащих указатели на данные в других местах. (Такое состояние возникает во время обновления, когда не хватает места для сохранения новой строки в исходном расположении.)

NULL для любой единицы распределения, отличающейся от единиц распределения IN_ROW_DATA для кучи.

NULL для куч, если указан аргумент mode = LIMITED.

compressed_page_count

bigint

Количество сжатых страниц.

  • Вновь выделенные для куч страницы не сжаты с использованием сжатия PAGE. Куча сжимается с использованием метода PAGE в двух особых ситуациях: при массовом импорте данных или перестройке кучи. Типичные операции DML, которые вызывают выделение страниц, не связаны со сжатием PAGE. Перестройте кучу, если значение compressed_page_count увеличивается сверх порогового.

  • Для таблиц с кластеризованным индексом значение compressed_page_count указывает эффективность сжатия PAGE.

Функция динамического управления sys.dm_db_index_physical_stats заменяет инструкцию DBCC SHOWCONTIG. Данная функция динамического управления не принимает связанные параметры операторов CROSS APPLY и OUTER APPLY.

Режим, в котором выполняется функция, определяет уровень просмотра для получения статистических данных, используемых функцией. mode указывается как LIMITED, SAMPLED или DETAILED. Эта функция проходит цепочки страниц в поисках единиц распределения, составляющих заданные секции таблицы или индекса. Функция sys.dm_db_index_physical_stats требует только блокировки таблицы типа Intent-Shared (IS) независимо от режима, в котором она выполняется.

Режим LIMITED является самым быстрым, в нем производится наименьшее число просмотров страниц. Для индекса просматриваются только страницы родительского уровня в сбалансированном дереве (то есть страницы, расположенные выше конечного уровня). Для кучи просматриваются только связанные PFS- и IAM-страницы. Страницы данных в куче просматриваются в ограниченном режиме.

В режиме LIMITED счетчик compressed_page_count имеет значение NULL, поскольку компонент просматривает только неконечные страницы сбалансированного дерева, а также IAM- и PFS-страницы кучи. Используйте режим SAMPLED , чтобы получить оценку значения для compressed_page_count, и режим DETAILED, чтобы получить фактическое значение для compressed_page_count. В режиме SAMPLED возвращается статистика на основе 1-процентной выборки всех страниц в индексе или куче. Результаты в режиме SAMPLED следует рассматривать как приблизительные. Если в индексе или куче менее 10 000 страниц, вместо режима SAMPLED используется режим DETAILED.

В режиме DETAILED проводится просмотр всех страниц и возвращается вся статистика.

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

Фрагментация возникает в процессе изменений данных (инструкциями INSERT, UPDATE и DELETE), выполняемых на таблице и, следовательно, в индексах, определенных для таблицы. Так как эти изменения обычно не распределяются равномерно по строкам таблицы и индекса, заполненность каждой страницы со временем может меняться. Для запросов, выполняющих просмотр части или всех индексов таблицы, этот вид фрагментации может приводить к чтению дополнительных страниц. Это затрудняет параллельный просмотр данных.

Уровень фрагментации индекса или кучи показан в столбце avg_fragmentation_in_percent. Для куч это значение соответствует фрагментации экстентов. Для индексов это значение соответствует логической фрагментации. В отличие от инструкции DBCC SHOWCONTIG, алгоритмы вычисления фрагментации в обоих случаях учитывают место для хранения нескольких файлов и поэтому являются точными.

Логическая фрагментация

Это процент неупорядоченных страниц конечного уровня индекса. Неупорядоченной называется страница, для которой следующая физическая страница, выделенная для индекса, не является страницей, на которую ссылается указатель следующей страницы в текущей конечной странице.

Фрагментация экстентов

Это процент неупорядоченных экстентов на конечном уровне кучи. Неупорядоченным называется такой экстент, для которого экстент, содержащий текущую страницу кучи, не расположен физически непосредственно за кластером, содержащим предыдущую страницу.

Для наибольшей производительности значение аргумента avg_fragmentation_in_percent должно быть как можно ближе к нулю. Но могут быть приемлемыми значения от 0 до 10 процентов. Для снижения этих значений могут использоваться любые методы снижения фрагментации, такие как перестройка, реорганизация или повторное создание. Дополнительные сведения об анализе степени фрагментации индекса см. в разделе Reorganizing and Rebuilding Indexes.

Если индекс становится фрагментирован настолько, что это влияет на производительность запросов, для снижения фрагментации есть три возможности.

  • Удаление и повторное создание кластеризованного индекса.

    Повторное создание кластеризованного индекса перераспределяет данные и приводит к полному заполнению страниц данных. Уровень заполнения можно настроить с помощью параметра FILLFACTOR инструкции CREATE INDEX. Недостатком этого метода является то, что в цикле удаления и повторного создания индекс находится в автономном режиме, а также то, что эта операция является атомарной. Если создание индекса прервано, он не создается заново. Дополнительные сведения см. в разделе CREATE INDEX (Transact-SQL).

  • Использование инструкции ALTER INDEX REORGANIZE, заменившей DBCC INDEXDEFRAG, для переупорядочения страниц индекса конечного уровня в логическом порядке. Так как эта операция выполняется в режиме «в сети», во время выполнения инструкции индекс доступен. Кроме того, операция может быть прервана без потери уже выполненной работы. Недостатком этого метода является то, что он не так хорошо выполняет реорганизацию данных, как операция перестроения индекса, и не обновляет статистику.

  • Использование инструкции ALTER INDEX REBUILD, заменившей DBCC DBREINDEX, для перестроения индекса, как «в сети», так и в режиме «вне сети». Дополнительные сведения см. в разделе ALTER INDEX (Transact-SQL).

Фрагментация сама по себе не является достаточной причиной реорганизации или перестроения индекса. Основной эффект фрагментации заключается в том, что она замедляет упреждающее чтение во время просмотра индекса. В результате этого увеличивается время ответа. Если запрос к фрагментированным таблице или индексу не предусматривает просмотра, потому что в основном выполняются единичные уточняющие запросы, устранение фрагментации может не привести к нужным результатам. Дополнительные сведения см. на веб-сайте корпорации Майкрософт.

noteПримечание
Выполнение инструкции DBCC SHRINKFILE или DBCC SHRINKDATABASE может вызвать фрагментацию, если индекс частично или полностью перемещается во время операции сжатия. Поэтому, если необходимо выполнить операцию сжатия, нужно выполнить ее до устранения фрагментации.

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

CautionВнимание!
При создании и удалении кластеризованного индекса таблицы дважды перестраиваются все ее некластеризованные индексы.

По умолчанию инструкция ALTER INDEX REORGANIZE делает более компактными страницы, содержащие данные типа больших объектов (LOB). Так как страницы LOB не освобождаются, когда становятся пустыми, сжатие этих данных может оптимизировать использование места на диске, если удаляются в больших объемах данные типа LOB или же столбцы типа LOB.

Изменение указанного кластеризованного индекса сжимает все столбцы LOB, которые содержатся в кластеризованном индексе. Изменение некластеризованного индекса сжимает все столбцы LOB, являющиеся неключевыми столбцами, включенными в индекс. При использовании в инструкции аргумента ALL реорганизуются все индексы, связанные с указанной таблицей или представлением. Кроме того, сжимаются все столбцы типа LOB, связанные с кластеризованным индексом, базовой таблицей или некластеризованным индексом с включенными столбцами.

Столбец avg_page_space_used_in_percent показывает заполненность страниц. Для достижения оптимального использования места на диске это значение должно быть близким к 100 процентам для индексов, где операции случайных вставок выполняются нечасто. Однако в индексе с множеством случайных вставок, имеющем очень заполненные страницы, будет расти число разбиений страниц. Это приводит к увеличению фрагментации. Поэтому для снижения числа разбиений страниц это значение должно быть меньше 100 процентов. Перестроение индекса с параметром FILLFACTOR позволяет изменять степень заполнения страницы для обеспечения соответствия индекса шаблону запроса. Дополнительные сведения о коэффициенте заполнения см. в разделе Fill Factor. Кроме того, инструкция ALTER INDEX REORGANIZE сжимает индекс, пытаясь заполнять страницы до последнего заданного значения аргумента FILLFACTOR. Это увеличивает значение в avg_space_used_in_percent. Обратите внимание, что инструкция ALTER INDEX REORGANIZE не может снизить степень заполнения страницы. Для этого необходимо выполнить перестроение индекса.

Фрагмент состоит из физически последовательных конечных страниц в одном файле единицы распределения. Индекс состоит, по крайней мере, из одного фрагмента. Максимальное число фрагментов, которое может иметь индекс, равно числу страниц на конечном уровне индекса. Увеличение размера фрагментов означает, что для считывания того же количества страниц понадобится меньшее количество обращений к диску. Следовательно, чем больше значение avg_fragment_size_in_pages, тем выше производительность при просмотре диапазона. Значения avg_fragment_size_in_pages и avg_fragmentation_in_percent обратно пропорциональны друг другу. То есть перестройка или реорганизация индекса уменьшают степень фрагментации и увеличивают размер фрагментов.

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

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

  • Разрешение VIEW DATABASE STATE для возврата сведений обо всех объектах в указанной базе данных при помощи шаблона объекта @object_id=NULL;

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

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

Дополнительные сведения см. в разделе Dynamic Management Objects.

Была ли вам полезна эта информация?
(1500 символов осталось)
Спасибо за ваш отзыв
Показ:
© 2014 Microsoft