sys.dm_db_index_physical_stats (Transact-SQL)

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

Примечание.

В документации термин B-tree обычно используется в ссылке на индексы. В индексах rowstore ядро СУБД реализует дерево B+. Это не относится к индексам columnstore или индексам в таблицах, оптимизированных для памяти. Дополнительные сведения см. в руководстве по архитектуре и проектированию индексов SQL Sql Server и Azure.

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

При запросе sys.dm_db_index_physical_stats на экземпляр сервера, на котором размещена доступная для чтения вторичная реплика группы доступности, может возникнуть проблема с блокировкой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 | ПО УМОЛЧАНИЮ

Идентификатор базы данных. database_id имеет небольшой размер. Допустимые входные данные — это идентификатор базы данных, NULL0или 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 | ПО УМОЛЧАНИЮ

Идентификатор объекта таблицы или представления индекса включен. object_id имеет значение int. Допустимые входные данные — это идентификатор таблицы и представления, NULL0или DEFAULT. Значение по умолчанию — 0. NULL, 0и DEFAULT эквивалентны значениям в этом контексте.

В SQL Server 2016 (13.x) и более поздних версиях допустимые входные данные также включают имя очереди посредника служб или имя внутренней таблицы очереди. Если применяются параметры по умолчанию (то есть все объекты, все индексы и т. д.), сведения о фрагментации для всех очередей включаются в результирующий набор.

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

index_id | 0 | NULL | -1 | ПО УМОЛЧАНИЮ

Идентификатор индекса. index_id имеет значение int. Допустимые входные данные — это идентификатор индекса, 0 если object_id является кучей, NULL-1или DEFAULT. Значение по умолчанию — -1. NULL, -1и DEFAULT эквивалентны значениям в этом контексте.

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

partition_number | NULL | 0 | ПО УМОЛЧАНИЮ

Номер секции в объекте. partition_number является int. Допустимые входные данные — это partion_number индекса или кучи, NULL0или DEFAULT. Значение по умолчанию — 0. NULL, 0и DEFAULT эквивалентны значениям в этом контексте.

Укажите NULL , чтобы возвращать сведения для всех секций объекта- владельцев.

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

режим | NULL | ПО УМОЛЧАНИЮ

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

Таблица возвращенной информации

Имя столбца	Тип данных	Description
database_id	smallint	Идентификатор базы данных таблицы или представления. В База данных SQL Azure значения уникальны в одной базе данных или эластичном пуле, но не в логическом сервере.
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 MAPPING INDEX (внутренний) - COLUMNSTORE DELETEBUFFER INDEX (внутренний) - COLUMNSTORE DELETEBITMAP INDEX (внутренний)
alloc_unit_type_desc	nvarchar(60)	Описание типа единицы распределения: - IN_ROW_DATA - LOB_DATA - ROW_OVERFLOW_DATA Единица LOB_DATA выделения содержит данные, хранящиеся в столбцах текста типа, ntext, image, varchar(max),nvarchar(max), varbinary(max)и xml. Дополнительные сведения см. в разделе Типы данных. Единица 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 является самым высоким на корневом уровне индекса. Небезопасные уровни индексов обрабатываются только в режимеDETAILED.
avg_fragmentation_in_percent	float	Логическая фрагментация для индексов или фрагментации экстентов для куч в единице IN_ROW_DATA выделения. Значение измеряется в процентах и учитывает несколько файлов. Определения фрагментации логических и экстентов см. в разделе "Примечания". 0 для LOB_DATA единиц выделения и ROW_OVERFLOW_DATA распределения. NULLдля кучи при использовании SAMPLEDрежима .
fragment_count	bigint	Количество фрагментов на конечном IN_ROW_DATA уровне единицы выделения. Дополнительные сведения об фрагментах см. в разделе "Примечания". NULL для небезопасных уровней индекса и LOB_DATA ROW_OVERFLOW_DATA единиц распределения. NULLдля кучи при использовании SAMPLEDрежима .
avg_fragment_size_in_pages	float	Среднее количество страниц в одном фрагменте на конечном IN_ROW_DATA уровне единицы выделения. NULL для небезопасных уровней индекса и LOB_DATA ROW_OVERFLOW_DATA единиц распределения. NULLдля кучи при использовании SAMPLEDрежима .
page_count	bigint	Общее количество страниц индекса или данных. Для индекса общее количество страниц индекса в текущем уровне дерева B в единице IN_ROW_DATA выделения. Для кучи общее количество страниц данных в единице IN_ROW_DATA выделения. ROW_OVERFLOW_DATA Для LOB_DATA единиц распределения общее количество страниц в единице выделения.
avg_page_space_used_in_percent	float	Средний процент доступного места для хранения данных, используемого всеми страницами. Для индекса среднее значение применяется к текущему уровню дерева B в единице IN_ROW_DATA выделения. Для кучи среднее значение всех страниц данных в единице IN_ROW_DATA выделения. Для LOB_DATA единиц ROW_OVERFLOW_DATA распределения среднее значение всех страниц в единице выделения. NULLПри использовании LIMITEDрежима .
record_count	bigint	Общее количество записей. Для индекса общее количество записей применяется к текущему уровню дерева B в единице IN_ROW_DATA выделения. Для кучи общее количество записей в единице IN_ROW_DATA выделения. Примечание. Для кучы количество записей, возвращаемых из этой функции, может не соответствовать количеству строк, возвращаемых при выполнении SELECT COUNT(*) кучи. Это связано с тем, что строка может содержать несколько записей. Например, в некоторых ситуациях обновления одна строка кучи может иметь запись пересылки и переадресованную запись в результате операции обновления. Кроме того, большинство больших бизнес-строк разделены на несколько записей в LOB_DATA хранилище. Для LOB_DATA единиц ROW_OVERFLOW_DATA распределения общее количество записей в полной единице выделения. NULLПри использовании LIMITEDрежима .
ghost_record_count	bigint	Количество фантомных записей в единице распределения, готовых к удалению задачей очистки фантомных записей. 0 для небезопасных уровней индекса в единице IN_ROW_DATA выделения. NULLПри использовании LIMITEDрежима .
version_ghost_record_count	bigint	Количество фантомных записей, сохраняемых в единице распределения необработанной транзакцией изоляции моментального снимка. 0 для небезопасных уровней индекса в единице IN_ROW_DATA выделения. NULLПри использовании LIMITEDрежима .
min_record_size_in_bytes	int	Минимальный размер записи в байтах. Для индекса минимальный размер записи применяется к текущему уровню дерева B в единице IN_ROW_DATA выделения. Для кучи минимальный размер записи в единице IN_ROW_DATA выделения. Для LOB_DATA единиц ROW_OVERFLOW_DATA распределения минимальный размер записи в полном блоке выделения. NULLПри использовании LIMITEDрежима .
max_record_size_in_bytes	int	Максимальный размер записи в байтах. Для индекса максимальный размер записи применяется к текущему уровню дерева B в единице IN_ROW_DATA выделения. Для кучи максимальный размер записи в единице IN_ROW_DATA выделения. Для LOB_DATA единиц ROW_OVERFLOW_DATA распределения максимальный размер записи в полном блоке выделения. NULLПри использовании LIMITEDрежима .
avg_record_size_in_bytes	float	Средний размер записи в байтах. Для индекса средний размер записи применяется к текущему уровню дерева B в единице IN_ROW_DATA выделения. Для кучи средний размер записи в единице IN_ROW_DATA выделения. Для LOB_DATA единиц ROW_OVERFLOW_DATA распределения средний размер записи в полной единице выделения. NULLПри использовании LIMITEDрежима .
forwarded_record_count	bigint	Количество записей в куче, содержащих указатели на данные в других местах. (Это состояние происходит во время обновления, когда недостаточно места для хранения новой строки в исходном расположении.) NULL для любой единицы выделения, отличной IN_ROW_DATA от единиц распределения для кучи. NULLдля кучи при использовании LIMITEDрежима .
compressed_page_count	bigint	Количество сжатых страниц. Для кучи только что выделенные страницы не PAGE сжимаются. Куча сжимается PAGE в двух особых условиях: при массовом импорте данных или при перестроении кучы. Типичные операции DML, которые вызывают выделение страниц, не PAGE сжимаются. Перестройте кучу, когда compressed_page_count значение увеличивается больше порогового значения. Для таблиц с кластеризованным индексом compressed_page_count значение указывает на эффективность PAGE сжатия.
hobt_id	bigint	Кучи или идентификатор дерева B индекса или секции. Для индексов columnstore это идентификатор набора строк, отслеживающего внутренние данные columnstore для секции. Наборы строк хранятся в виде кучи данных или B-деревьев. Они имеют тот же идентификатор индекса, что и родительский индекс columnstore. Дополнительные сведения см. в sys.internal_partitions.
columnstore_delete_buffer_state	tinyint	0 = NOT_APPLICABLE 1 = OPEN 2 = DRAINING 3 = FLUSHING 4 = RETIRING 5 = READY Область применения: SQL Server 2016 (13.x) и более поздних версий, База данных SQL Azure и Управляемый экземпляр SQL Azure
columnstore_delete_buffer_state_desc	nvarchar(60)	NOT VALID — родительский индекс не является индексом columnstore. OPEN — средства удаления и сканеры используют это. DRAINING — удаленные средства вытекают, но сканеры по-прежнему используют его. FLUSHING — буфер закрыт, а строки в буфере записываются в растровое изображение удаления. RETIRING — строки в закрытом буфере удаления были записаны на растровое изображение удаления, но буфер не был усечен, так как сканеры по-прежнему используют его. Новые сканеры не должны использовать буфер выхода на пенсию, так как открытый буфер достаточно. READY — Этот буфер удаления готов к использованию. Область применения: SQL Server 2016 (13.x) и более поздних версий, База данных 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
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

Примечание.

В документации термин B-tree обычно используется в ссылке на индексы. В индексах rowstore ядро СУБД реализует дерево B+. Это не относится к индексам columnstore или индексам в таблицах, оптимизированных для памяти. Дополнительные сведения см. в руководстве по архитектуре и проектированию индексов SQL Sql Server и Azure.

Замечания

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

Режимы сканирования

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

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

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

Режим 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'AdventureWorks2022'), 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 AdventureWorks2022;
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'AdventureWorks2022.Person.Address'), или проверьте значение, возвращаемое функциями, прежде чем использовать их в sys.dm_db_index_physical_stats функции. Примеры A и B, которые следуют, демонстрируют безопасный способ указания идентификаторов базы данных и объектов.

Обнаружение фрагментации

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

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

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

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

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

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

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

Уменьшение фрагментации в индексе

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

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

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

• Используйте 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, являющиеся неключевыми столбцами, включенными в индекс. При ALL указании в инструкции все индексы, связанные с указанной таблицей или представлением, реорганизоваться. Кроме того, все бизнес-столбцы, связанные с кластеризованным индексом, базовой таблицей или некластеризованным индексом с включенными столбцами, сжимаются.

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

Столбец 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 разрешение на указанный объект в базе данных.

• VIEW DATABASE STATE или VIEW DATABASE PERFORMANCE STATE разрешение (SQL Server 2022) на возврат сведений обо всех объектах в указанной базе данных с помощью подстановочного знака объекта @object_id = NULL.

• VIEW SERVER STATEили VIEW SERVER PERFORMANCE STATE разрешение (SQL Server 2022) на возврат сведений обо всех базах данных с помощью подстановочного знака базы данных @database_id = NULL.

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

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

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

Примеры

Примеры кода Transact-SQL в этой статье используют AdventureWorks2022 базу данных или AdventureWorksDW2022 пример базы данных, которую можно скачать с домашней страницы примеров и проектов сообщества Microsoft SQL Server.

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

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

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.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

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

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

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.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

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

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

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

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

В следующем примере автоматически реорганизуются или перестраиваются все секции в базе данных со средней степенью фрагментации более 10 процентов. Для выполнения этого запроса требуется VIEW DATABASE STATE разрешение. В данном примере в качестве первого параметра указывается DB_ID без определения имени базы данных.

-- 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
    INNER 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
INNER 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 TRANSACTION

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');

G. Запрос очередей посредника служб для фрагментации индекса

Область применения: SQL Server 2016 (13.x) и более поздних версий

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

--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)
• 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 (ядро СУБД)