sys.dm_db_index_physical_stats (Transact-SQL)

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

Важно!

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

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

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

Для указания значений параметров database_id и object_id можно использовать функции языка Transact-SQLDB_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 обратно пропорциональны друг другу. То есть перестройка или реорганизация индекса уменьшают степень фрагментации и увеличивают размер фрагментов.

Разрешения

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

• разрешение 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).

Примеры

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

В следующем примере возвращаются размер и статистика фрагментации для всех индексов и секций таблицы Person.Address в базе данных AdventureWorks2012. Для повышения производительности и ограничения возвращаемой статистики используется режим просмотра '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, кроме строки, возвращаемой для единицы распределения IN_ROW_ALLOCATION_UNIT, хранящей страницы данных кучи, возвращается строка для единицы распределения LOB_DATA. Для выполнения этого запроса необходимо по крайней мере разрешение 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

E. Использование функции 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;

F. Использование 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');

См. также

Справочник

Динамические административные представления и функции (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)