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
единицы выделения в каждой секции.
Примечание.
В документации по SQL Server термин "сбалансированное дерево" обычно используется в отношении индексов. В индексах rowstore SQL Server реализует B+-дерево. Это не относится к индексам columnstore или хранилищам данных в памяти. Дополнительные сведения см. в руководстве по архитектуре и проектированию индексов SQL Sql Server и Azure.
sys.dm_db_index_physical_stats
не возвращает сведения об индексах, оптимизированных для памяти. Сведения об использовании индексов, оптимизированных для памяти, см. в sys.dm_db_xtp_index_stats (Transact-SQL).
При запросе 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 имеет небольшой размер. Допустимые входные данные — это идентификатор базы данных, 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) допустимые входные данные также включают имя очереди посредника служб или внутреннее имя таблицы очереди. Если применяются параметры по умолчанию (то есть все объекты, все индексы и т. д.), сведения о фрагментации для всех очередей включаются в результирующий набор.
Укажите значение NULL, чтобы вернуть данные для всех таблиц и представлений в указанной базе данных. Если для object_id задано значение NULL, необходимо также указать ЗНАЧЕНИЕ 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 индекса или кучи, NULL, 0 или DEFAULT. По умолчанию установлено значение 0. ЗНАЧЕНИЯ NULL, 0 и DEFAULT эквивалентны в этом контексте.
Чтобы получить сведения обо всех секциях объекта, укажите значение NULL.
partition_number основан на 1. Непартиментный индекс или куча имеет значение 1 partition_number.
режим | NULL | ПО УМОЛЧАНИЮ
Имя режима. режим указывает уровень сканирования, используемый для получения статистики. mode — sysname. Допустимыми входными данными являются значения DEFAULT, NULL, LIMITED, SAMPLED и 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 — КЛАСТЕРИЗОВАННЫЙ ИНДЕКС — НЕКЛАСТЕРИЗОВАННЫЙ ИНДЕКС — ПЕРВИЧНЫЙ XML-ИНДЕКС - РАСШИРЕННЫЙ ИНДЕКС — XML-ИНДЕКС — ИНДЕКС СОПОСТАВЛЕНИЯ COLUMNSTORE (внутренний) — COLUMNSTORE DELETEBUFFER INDEX (internal) — COLUMNSTORE DELETEBITMAP INDEX (internal) |
hobt_id | bigint | Кучи или идентификатор дерева B индекса или секции. Для индексов columnstore это идентификатор набора строк, отслеживающего внутренние данные columnstore для секции. Наборы строк хранятся в виде кучи данных или B-деревьев. Они имеют тот же идентификатор индекса, что и родительский индекс columnstore. Дополнительные сведения см. в разделе sys.internal_partitions (Transact-SQL). |
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. Дополнительные сведения см. в разделе Типы данных (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 является самым высоким на корневом уровне индекса. Небезопасные уровни индексов обрабатываются только в режиме = 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 | Количество сжатых страниц. Для кучи только что выделенные страницы не сжимаются. Куча — это СТРАНИЦА, сжимаемая при наступлении двух особых условий: при массовом импорте данных или при перестройке кучи. Типичные операции DML, которые приводят к выделению страниц, не сжимаются. Перестройте кучу, когда compressed_page_count значение увеличивается больше порогового значения.Для таблиц с кластеризованным индексом compressed_page_count значение указывает на эффективность сжатия PAGE. |
columnstore_delete_buffer_state | tinyint | 0 = NOT_APPLICABLE 1 = OPEN; 2 = ОЧИСТКА 3 = ОЧИСТКА 4 = УДАЛЕНИЕ 5 = READY Область применения: SQL Server 2016 (13.x) и более поздних версий, База данных SQL Azure и Управляемый экземпляр SQL Azure |
columnstore_delete_buffer_state_desc | nvarchar(60) | НЕДОПУСТИМО. Родительский индекс не является индексом columnstore. OPEN — средства удаления и сканеры используют это. ОЧИСТКА — удаленные удаляются, но сканеры по-прежнему используют его. FLUSHING — буфер закрывается, а строки в буфере записываются в растровое изображение удаления. DELETEING — строки в закрытом буфере удаления были записаны на растровое изображение удаления, но буфер не был усечен, так как сканеры по-прежнему используют его. Новые сканеры не должны использовать буфер выхода на пенсию, так как открытый буфер достаточно. 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 |
Примечание.
В документации по SQL Server термин "сбалансированное дерево" обычно используется в отношении индексов. В индексах rowstore SQL Server реализует 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 страниц, вместо режима 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'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, 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, являющиеся неключевыми столбцами, включенными в индекс. При использовании в инструкции аргумента 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).
Примеры
А. Возврат сведений об указанной таблице
В следующем примере возвращаются размер и статистика фрагментации для всех индексов и секций таблицы 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
без определения имени базы данных. Ошибка возникает, если текущая база данных имеет уровень совместимости 80 или ниже. Чтобы исправить эту ошибку, замените вызов функции DB_ID()
действительным именем базы данных. Дополнительные сведения о уровнях совместимости базы данных см. в разделе ALTER DATABASE Compatibility Level (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
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)
- Индексы, связанные с динамическими административными представлениями и функциями (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)