sys.dm_db_index_physical_stats (Transact-SQL)

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

Важно!

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

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

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

Синтаксис

sys.dm_db_index_physical_stats ( 
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | NULL | 0 | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
  , { mode | NULL | DEFAULT }
)

Аргументы

• database_id | NULL | 0 | 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.

Возвращаемая таблица

Замечания

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

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

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

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

В режиме LIMITED счетчик compressed_page_count имеет значение NULL, поскольку компонент Компонент Database Engine просматривает только неконечные страницы сбалансированного дерева, а также 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).

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

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

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

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

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

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

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

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

Оценка фрагментов индекса

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

Ограничения

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

Разрешения

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

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

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

Примеры

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

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

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.Person.Address');

IF @db_id IS NULL
BEGIN;
    PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO

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

В следующем примере возвращается вся статистика для кучи dbo.DatabaseLog в базе данных AdventureWorks2012. Так как таблица содержит данные типа LOB, кроме строки, возвращаемой для единицы распределения 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

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

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

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

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

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

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

См. также

Справочник

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