sys.dm_db_index_physical_stats (Transact-SQL)

Применимо к: даSQL Server (все поддерживаемые версии) ДаБаза данных SQL Azure

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

Важно!

При запросе sys.dm_db_index_physical_stats на экземпляре сервера, на котором размещена Always On вторичная реплика для чтения, может возникнуть ошибка блокировки повтора. Это связано с тем, что данное динамическое административное представление получает блокировку (IS) в указанной пользовательской таблице либо в представлении, которые могут блокировать запросы посредством потока REDO для монопольной блокировки (X) этой пользовательской таблицы или представления.

sys.dm_db_index_physical_stats не возвращает сведения о индексах, оптимизированных для памяти. сведения об использовании индексов, оптимизированных для памяти, см. в разделе sys.dm_db_xtp_index_stats (Transact-SQL).

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

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

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

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

Допустимыми входными значениями являются идентификатор таблицы, NULL, 0 или DEFAULT. Значение по умолчанию равно 0. В данном контексте значения NULL, 0 и DEFAULT эквивалентны. В SQL Server 2016 (13.x); допустимые входные данные также включают имя очереди компонента Service Broker или имя внутренней таблицы очереди. Когда применяются параметры по умолчанию (т. е. все объекты, все индексы и т. д.), сведения о фрагментации для всех очередей включаются в результирующий набор.

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

index_id | 0 | null | -1 | по умолчанию
Идентификатор индекса. index_id имеет тип int. Допустимые входные данные — это ИДЕНТИФИКАЦИОНный номер индекса, 0, если object_id является КУЧЕЙ, null,-1 или ЗНАЧЕНИЕМ по умолчанию. Значение по умолчанию — -1. Значения NULL,-1 и DEFAULT являются эквивалентными значениями в этом контексте.

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

partition_number | NULL | 0 | по умолчанию
Номер секции в объекте. partition_number имеет тип int. Допустимыми входными значениями являются partion_number индекса, КУЧИ, null, 0 или Default. Значение по умолчанию равно 0. В данном контексте значения NULL, 0 и DEFAULT эквивалентны.

Чтобы получить сведения обо всех секциях объекта, укажите значение NULL.

partition_number , основанный на 1. Несекционированный индекс или куча имеет partition_number значение 1.

режим | NULL | по умолчанию
Имя режима. режим задает уровень сканирования, используемый для получения статистики. режим имеет тип 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

EXTENDED INDEX

XML INDEX

Индекс СОПОСТАВЛЕНИЯ COLUMNSTORE (внутренний)

Индекс COLUMNSTORE ДЕЛЕТЕБУФФЕР (внутренний)

Индекс COLUMNSTORE ДЕЛЕТЕБИТМАП (внутренний)
hobt_id bigint Идентификатор кучи или сбалансированного дерева индекса или секции.

Помимо возврата hobt_id определяемых пользователем индексов, это также возвращает hobt_id внутренних индексах columnstore.
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. Дополнительные сведения см. в разделе Типы данных (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.

Значения больше 0 соответствуют неконечным уровням индекса. 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.

Примечание. Для кучи число записей, возвращаемых этой функцией, может не совпадать с количеством строк, возвращаемых при выполнении 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. Куча — это СТРАНИЦА, сжимаемая при наступлении двух особых условий: при массовом импорте данных или при перестройке кучи. Типичные операции DML, которые вызывают выделение страниц, не связаны со сжатием PAGE. Перестройте кучу, если значение compressed_page_count увеличивается сверх желательного порога.

Для таблиц с кластеризованным индексом значение compressed_page_count указывает эффективность сжатия страниц.
hobt_id BIGINT Только для индексов columnstore это идентификатор набора строк, который отслеживает внутренние данные columnstore для секции. Наборы строк хранятся в виде куч данных или двоичных деревьев. Они имеют тот же идентификатор индекса, что и родительский индекс columnstore. дополнительные сведения см. в разделе sys.internal_partitions (Transact-SQL).

NULL, если

применимо к: SQL Server 2016 и более поздних версий, База данных SQL Azure SQL Azure Управляемый экземпляр
column_store_delete_buffer_state tinyint 0 = NOT_APPLICABLE

1 = OPEN;

2 = СТОК

3 = ОЧИСТКА

4 = СНЯТИЕ С УЧЕТА

5 = ГОТОВО

применимо к: SQL Server 2016 и более поздних версий, База данных SQL Azure SQL Azure Управляемый экземпляр
column_store_delete_buff_state_desc Недопустимый — родительский индекс не является индексом columnstore.

Это используется с помощью открытых и удаляемых сканеров.

Сток — удаляются, но сканеры все еще используют его.

Очистка буфера закрывается, а строки в буфере записываются в точечный рисунок удаления.

Снятие с учета-строки в закрытом буфере удаления записываются в битовую карту удаления, но буфер не был усечен, так как сканеры все еще используют его. Новым сканерам не нужно использовать буфер снятия с учета, поскольку достаточно свободного места в открытом буфере.

Готово — этот буфер удаления готов к использованию.

применимо к: SQL Server 2016 и более поздних версий, База данных SQL Azure SQL Azure Управляемый экземпляр
version_record_count bigint Это число записей версий строк, сохраняемых в этом индексе. Эти версии строк поддерживаются функцией ускоренного восстановления базы данных .

Применимо к: даSQL Server 2019 (15.x), База данных SQL Azure
inrow_version_record_count bigint Число записей версий ADR, сохраненных в строке данных для быстрого извлечения.

Применимо к: даSQL Server 2019 (15.x), База данных SQL Azure e
inrow_diff_version_record_count bigint Число записей версий ADR, сохраненных в виде отличий от базовой версии.

Применимо к: даSQL Server 2019 (15.x), База данных SQL Azure
total_inrow_version_payload_size_in_bytes bigint Общий размер в байтах записей версии экземпляров управлении для этого индекса.

Применимо к: даSQL Server 2019 (15.x), База данных SQL Azure
offrow_regular_version_record_count bigint Число записей версий, которые хранятся за пределами исходной строки данных.

Применимо к: даSQL Server 2019 (15.x), База данных SQL Azure
offrow_long_term_version_record_count bigint Число записей версий, которые считаются долгосрочными.

Применимо к: даSQL Server 2019 (15.x), База данных SQL Azure

Remarks

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

Режимы просмотра

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

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

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

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

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

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

Можно использовать Transact-SQL функции DB_ID и object_id , чтобы указать значения для параметров database_id и object_id . Однако передавая значения, которые не допустимы для этих функций, можно получить неожиданные результаты. Например, если имя базы данных или объекта не могут быть найдены из-за того, что объект или база данных не существуют или соответствующее имя указано неверно, обе функции возвращают NULL. Функция sys.dm_db_index_physical_stats интерпретирует NULL как значение шаблона, задающее все базы данных или все объекты.

Кроме того, функция OBJECT_ID обрабатывается до вызова функции sys.dm_db_index_physical_stats и, следовательно, вычисляется в контексте текущей базы данных, а не в базе данных, указанной в database_id. Это поведение может привести к тому, что функция OBJECT_ID возвратит значение NULL или, если имя объекта существует в контексте как текущей, так и указанной базы данных, возвратит сообщение об ошибке. В следующих примерах демонстрируются эти неожиданные результаты.

USE master;  
GO  
-- In this example, OBJECT_ID is evaluated in the context of the master database.   
-- Because Person.Address does not exist in master, the function returns NULL.  
-- When NULL is specified as an object_id, all objects in the database are returned.  
-- The same results are returned when an object that is not valid is specified.  
SELECT * FROM sys.dm_db_index_physical_stats  
    (DB_ID(N'AdventureWorks'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');  
GO  
-- This example demonstrates the results of specifying a valid object name  
-- that exists in both the current database context and  
-- in the database specified in the database_id parameter of the   
-- sys.dm_db_index_physical_stats function.  
-- An error is returned because the ID value returned by OBJECT_ID does not  
-- match the ID value of the object in the specified database.  
CREATE DATABASE Test;  
GO  
USE Test;  
GO  
CREATE SCHEMA Person;  
GO  
CREATE Table Person.Address(c1 int);  
GO  
USE AdventureWorks2012;  
GO  
SELECT * FROM sys.dm_db_index_physical_stats  
    (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');  
GO  
-- Clean up temporary database.  
DROP DATABASE Test;  
GO  

Рекомендации

Всегда следует проверять, что функции DB_ID и OBJECT_ID возвращают допустимый идентификатор. Например, при использовании OBJECT_ID укажите имя из трех частей OBJECT_ID(N'AdventureWorks2012.Person.Address') , например, или проверьте значение, возвращаемое функциями, прежде чем использовать их в функции sys.dm_db_index_physical_stats. Примеры А и Б демонстрируют безопасный способ указания базы данных и идентификаторов объекта.

Выявление фрагментации

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

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

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

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

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

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

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

Снижение фрагментации в индексе

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

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

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

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

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

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

Примечание

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

Снижение фрагментации в куче

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

Внимание!

Создавая и удаляя кластеризованный индекс для таблицы, перестраивает все некластеризованные индексы в этой таблице дважды.

Сжатие данных больших объектов

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

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

Оценка использования места на диске

Столбец avg_page_space_used_in_percent указывает заполненность страниц. Для достижения оптимального использования места на диске это значение должно быть близким к 100 процентам для индексов, где операции случайных вставок выполняются нечасто. Однако в индексе с множеством случайных вставок, имеющем очень заполненные страницы, будет расти число разбиений страниц. Это приводит к увеличению фрагментации. Поэтому для снижения числа разбиений страниц это значение должно быть меньше 100 процентов. Перестроение индекса с параметром FILLFACTOR позволяет изменять степень заполнения страницы для обеспечения соответствия индекса шаблону запроса. Дополнительные сведения о коэффициенте заполнения см. в разделе Указание коэффициента заполнения для индекса. Кроме того, инструкция 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 обратно пропорциональны друг другу. То есть перестройка или реорганизация индекса уменьшают степень фрагментации и увеличивают размер фрагментов.

Ограничения

Не возвращает данные для кластеризованных индексов columnstore.

Разрешения

Необходимы следующие разрешения:

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

  • Разрешение Просмотр состояния базы данных для получения сведений обо всех объектах в указанной базе данных с помощью шаблона объекта @object_id= null.

  • Разрешение Просмотр состояния сервера для возврата сведений обо всех базах данных с помощью подстановочного знака @database_id = null.

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

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

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

Примеры

A. Возврат сведений об указанной таблице

В следующем примере возвращаются размер и статистика фрагментации для всех индексов и секций таблицы Person.Address. Для повышения производительности и ограничения возвращаемой статистики используется режим просмотра 'LIMITED'. Для выполнения этого запроса необходимо по крайней мере разрешение CONTROL на таблицу Person.Address.

DECLARE @db_id SMALLINT;  
DECLARE @object_id INT;  
  
SET @db_id = DB_ID(N'AdventureWorks2012');  
SET @object_id = OBJECT_ID(N'AdventureWorks2012.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_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');  
END;  
GO  
  

Б. Возврат сведений о куче

В следующем примере возвращается вся статистика для кучи dbo.DatabaseLog в базе данных AdventureWorks2012. Так как таблица содержит данные типа LOB, кроме строки, возвращаемой для единицы распределения LOB_DATA, хранящей страницы данных кучи, возвращается строка для единицы распределения IN_ROW_ALLOCATION_UNIT. Для выполнения этого запроса необходимо по крайней мере разрешение CONTROL на таблицу dbo.DatabaseLog.

DECLARE @db_id SMALLINT;  
DECLARE @object_id INT;  
SET @db_id = DB_ID(N'AdventureWorks2012');  
SET @object_id = OBJECT_ID(N'AdventureWorks2012.dbo.DatabaseLog');  
IF @object_id IS NULL   
BEGIN;  
    PRINT N'Invalid object';  
END;  
ELSE  
BEGIN;  
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED');  
END;  
GO  
  

В. Возврат сведений обо всех базах данных

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

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

Г. Использование представления sys.dm_db_index_physical_stats в скрипте для перестроения или реорганизации индексов

В следующем примере автоматически реорганизуются или перестраиваются все секции в базе данных со средней степенью фрагментации более 10 процентов. Для выполнения этого запроса необходимо разрешение VIEW DATABASE STATE. В данном примере в качестве первого параметра указывается DB_ID без определения имени базы данных. Если уровень совместимости текущей базы данных составляет 80 или ниже, будет сформирована ошибка. Чтобы исправить эту ошибку, замените вызов функции DB_ID() действительным именем базы данных. дополнительные сведения об уровнях совместимости баз данных см. в разделе уровень совместимости ALTER database ()Transact-SQL .

-- Ensure a USE <databasename> statement has been executed first.  
SET NOCOUNT ON;  
DECLARE @objectid int;  
DECLARE @indexid int;  
DECLARE @partitioncount bigint;  
DECLARE @schemaname nvarchar(130);   
DECLARE @objectname nvarchar(130);   
DECLARE @indexname nvarchar(130);   
DECLARE @partitionnum bigint;  
DECLARE @partitions bigint;  
DECLARE @frag float;  
DECLARE @command nvarchar(4000);   
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function   
-- and convert object and index IDs to names.  
SELECT  
    object_id AS objectid,  
    index_id AS indexid,  
    partition_number AS partitionnum,  
    avg_fragmentation_in_percent AS frag  
INTO #work_to_do  
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')  
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;  
  
-- Declare the cursor for the list of partitions to be processed.  
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;  
  
-- Open the cursor.  
OPEN partitions;  
  
-- Loop through the partitions.  
WHILE (1=1)  
    BEGIN;  
        FETCH NEXT  
           FROM partitions  
           INTO @objectid, @indexid, @partitionnum, @frag;  
        IF @@FETCH_STATUS < 0 BREAK;  
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)  
        FROM sys.objects AS o  
        JOIN sys.schemas as s ON s.schema_id = o.schema_id  
        WHERE o.object_id = @objectid;  
        SELECT @indexname = QUOTENAME(name)  
        FROM sys.indexes  
        WHERE  object_id = @objectid AND index_id = @indexid;  
        SELECT @partitioncount = count (*)  
        FROM sys.partitions  
        WHERE object_id = @objectid AND index_id = @indexid;  
  
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.  
        IF @frag < 30.0  
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';  
        IF @frag >= 30.0  
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';  
        IF @partitioncount > 1  
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));  
        EXEC (@command);  
        PRINT N'Executed: ' + @command;  
    END;  
  
-- Close and deallocate the cursor.  
CLOSE partitions;  
DEALLOCATE partitions;  
  
-- Drop the temporary table.  
DROP TABLE #work_to_do;  
GO  
  

Д. Использование представления sys.dm_db_index_physical_stats для отображения числа страниц, подвергнутых сжатию на уровне страниц

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

SELECT o.name,  
    ips.partition_number,  
    ips.index_type_desc,  
    ips.record_count, ips.avg_record_size_in_bytes,  
    ips.min_record_size_in_bytes,  
    ips.max_record_size_in_bytes,  
    ips.page_count, ips.compressed_page_count  
FROM sys.dm_db_index_physical_stats ( DB_ID(), NULL, NULL, NULL, 'DETAILED') ips  
JOIN sys.objects o on o.object_id = ips.object_id  
ORDER BY record_count DESC;  

Е. Использование sys.dm_db_index_physical_stats в режиме SAMPLED

В следующем примере показано, как в режиме SAMPLED возвращается примерное значение, отличающееся от результатов в режиме DETAILED.

CREATE TABLE t3 (col1 int PRIMARY KEY, col2 varchar(500)) WITH(DATA_COMPRESSION = PAGE);  
GO  
BEGIN TRAN  
DECLARE @idx int = 0;  
WHILE @idx < 1000000  
BEGIN  
    INSERT INTO t3 (col1, col2)   
    VALUES (@idx,   
    REPLICATE ('a', 100) + CAST (@idx as varchar(10)) + REPLICATE ('a', 380))  
    SET @idx = @idx + 1  
END  
COMMIT;  
GO  
SELECT page_count, compressed_page_count, forwarded_record_count, *   
FROM sys.dm_db_index_physical_stats (db_id(),   
    object_id ('t3'), null, null, 'SAMPLED');  
SELECT page_count, compressed_page_count, forwarded_record_count, *   
FROM sys.dm_db_index_physical_stats (db_id(),   
    object_id ('t3'), null, null, 'DETAILED');  

Ж. Запрос очередей компонента Service Broker для фрагментации индекса

Применимо к: с SQL Server 2016 (13.x); до SQL Server.

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

--Using queue internal table name   
select * from sys.dm_db_index_physical_stats (db_id(), object_id ('sys.queue_messages_549576996'), default, default, default)   
  
--Using queue name directly  
select * from sys.dm_db_index_physical_stats (db_id(), object_id ('ExpenseQueue'), default, default, default)  
  

См. также

Динамические административные представления и функции (Transact-SQL)
Динамические административные представления и функции, связанные с индексами (Transact-SQL)
sys.dm_db_index_operational_stats (Transact-SQL)
sys.dm_db_index_usage_stats (Transact-SQL)
sys.dm_db_partition_stats (Transact-SQL)
sys.allocation_units (Transact-SQL)
системные представления (Transact-SQL)