DBCC SHOWCONTIG (Transact-SQL)

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

Отображает сведения о фрагментации данных и индексов указанной таблицы или представления.

Внимание

Эта функция будет удалена в будущей версии SQL Server. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется. Вместо этого используйте sys.dm_db_index_physical_stats .

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

Соглашения о синтаксисе Transact-SQL

Синтаксис

DBCC SHOWCONTIG
[ (
    { table_name | table_id | view_name | view_id }
    [ , index_name | index_id ]
) ]
    [ WITH
        {
         [ , [ ALL_INDEXES ] ]
         [ , [ TABLERESULTS ] ]
         [ , [ FAST ] ]
         [ , [ ALL_LEVELS ] ]
         [ NO_INFOMSGS ]
         }
    ]

Аргументы

table_name | table_id | view_name | view_id

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

index_name | index_id

Индекс для проверки сведений о фрагментации. Если этот аргумент не задан, инструкция обрабатывает базовый индекс указанной таблицы или представления. Для получения идентификатора индекса используется представление каталога sys.indexes.

ВМЕСТЕ С

Задает параметры типа возвращаемых инструкцией DBCC сведений.

БЫСТРО

Определяет, выполнять ли быстрый просмотр индекса с выводом минимального количества сведений. Быстрая проверка не считывает конечные страницы или страницы уровня данных индекса.

ALL_INDEXES

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

TABLERESULTS

Отображает результаты в виде набора строк с дополнительными сведениями.

ALL_LEVELS

Поддерживается только для обратной совместимости. Даже если ALL_LEVELS задано, обрабатывается только конечный уровень индекса или уровень данных таблицы.

NO_INFOMSGS

Подавляет все информационные сообщения со степенями серьезности от 0 до 10.

Результирующие наборы

В следующей таблице описаны сведения в результирующем наборе.

Статистический показатель	Description
Pages Scanned	Количество страниц в таблице или индексе.
Extents Scanned	Количество экстентов в таблице или индексе.
Extent Switches	Количество раз, когда инструкция DBCC в процессе обхода страниц таблицы или индекса переходила с одного экстента на другой.
Среднее значение. Страницы на экстент	Количество страниц на экстент в цепочке страниц.
Scan Density [Best Count: Actual Count]	Процент. Это отношение Best Count к Actual Count. Значение 100 указывает на отсутствие фрагментации; значение меньше 100 указывает на наличие фрагментации. Best Count — это идеальное количество изменений экстентов в случае полной непрерывности. Actual Count — это реальное количество изменений экстентов.
Logical Scan Fragmentation	Процент неупорядоченных страниц, определенный с помощью просмотра конечных страниц индекса. Это число не относится к кучам. Страница вне порядка — это страница, для которой следующая физическая страница, выделенная индексу, не указывает указатель на следующую страницуна текущую конечную страницу.
Extent Scan Fragmentation	Процент неупорядоченных экстентов при просмотре конечных страниц индекса. Это число не относится к кучам. Экстент вне порядка — это экстент, для которого экстент, содержащий текущую страницу индекса, физически не является следующим после экстента, содержащего предыдущую страницу для индекса. Примечание. Это число бессмысленно, если индекс охватывает несколько файлов.
Среднее значение. Байт бесплатно на страницу	Среднее количество свободных байтов на просмотренных страницах. Чем больше это число, тем меньше заполнены страницы. Более низкие числа лучше, если индекс не будет иметь большого количества случайных вставок. На этот показатель влияет также размер строк: большой размер строки может привести к его увеличению.
Среднее значение. Плотность страниц (полная)	Средняя плотность страницы в процентах. Этот показатель учитывает размер строк. Поэтому он более точно отражает плотность заполнения страниц. Чем больше процентное отношение, тем лучше.

При указании DBCC SHOWCONTIG table_id и FAST возвращает результирующий набор только со следующими столбцами:

• Pages Scanned
• Extent Switches
• Scan Density [Best Count:Actual Count]
• Extent Scan Fragmentation
• Logical Scan Fragmentation

При TABLERESULTS указании DBCC SHOWCONTIG возвращает следующие столбцы, а также девять столбцов, описанных в предыдущей таблице.

Статистический показатель	Description
Имени объекта	Имя обработанной таблицы или представления.
ObjectId	Идентификатор объекта.
IndexName	Имя обработанного индекса. NULL для кучи.
IndexId	Идентификатор индекса. 0 для кучы.
Уровень	Уровень индекса. Уровень 0 представляет собой конечный уровень или уровень данных индекса. Для кучи уровень равен 0.
Страницы	Количество страниц, образующих данный уровень индекса или всю кучу.
Строки	Количество записей данных или индексных записей на этом уровне индекса. Для кучи это число записей данных во всей куче. Для кучы количество записей, возвращаемых из этой функции, может не соответствовать количеству строк, возвращаемых при выполнении SELECT COUNT(*) кучи. Это происходит потому, что строка может содержать несколько записей. Например, при обновлении одна строка кучи может иметь указывающую запись и перенаправленную запись как результат операции обновления. Также большинство больших LOB-строк разбиты на различные записи в хранилище LOB_DATA.
MinimumRecordSize	Минимальный размер записи на данном уровне индекса или во всей куче.
MaximumRecordSize	Максимальный размер записи на данном уровне индекса или во всей куче.
AverageRecordSize	Средний размер записи на данном уровне индекса или во всей куче.
ForwardedRecords	Количество перенаправленных записей на данном уровне индекса или во всей куче.
Extents	Количество экстентов на данном уровне индекса или во всей куче.
ExtentSwitches	Количество раз, когда инструкция DBCC в процессе обхода страниц таблицы или индекса переходила с одного экстента на другой.
AverageFreeBytes	Среднее количество свободных байтов на просмотренных страницах. Чем больше это число, тем меньше заполнены страницы. Более низкие числа лучше, если индекс не будет иметь большого количества случайных вставок. На этот показатель влияет также размер строк: большой размер строки может привести к его увеличению.
AveragePageDensity	Средняя плотность страницы в процентах. Этот показатель учитывает размер строк. Поэтому он более точно отражает плотность заполнения страниц. Чем больше процентное отношение, тем лучше.
ScanDensity	Процент. Это отношение значения BestCount к значению ActualCount. Значение 100 указывает на отсутствие фрагментации; значение меньше 100 указывает на наличие фрагментации.
BestCount	Идеальное число изменений экстентов, если все тесно связано.
ActualCount	Фактическое количество изменений экстентов.
LogicalFragmentation	Процент неупорядоченных страниц, определенный с помощью просмотра конечных страниц индекса. Это число не относится к кучам. Страница вне порядка — это страница, для которой следующая физическая страница, выделенная индексу, не указана указателем следующей страницы на текущую конечную страницу.
ExtentFragmentation	Процент неупорядоченных экстентов при просмотре конечных страниц индекса. Это число не относится к кучам. Экстент вне порядка — это экстент, для которого экстент, содержащий текущую страницу индекса, физически не является следующим после экстента, содержащего предыдущую страницу для индекса. Примечание. Это число бессмысленно, если индекс охватывает несколько файлов.

При WITH TABLERESULTS указании результирующего FAST набора совпадает WITH TABLERESULTS с указанным значением, за исключением следующих столбцов:

Строки	Экстенты
MinimumRecordSize	AverageFreeBytes
MaximumRecordSize	AveragePageDensity
AverageRecordSize	ExtentFragmentation
ForwardedRecords

Замечания

Оператор DBCC SHOWCONTIG проходит по цепочке страниц на конечном уровне указанного индекса при указании index_id . Если задан только аргумент table_id или же аргумент index_id равен 0, просматриваются страницы данных указанной таблицы. Для этой операции достаточно блокировки намерения (IS) таблицы. Таким способом можно выполнять все операции обновления и вставки, кроме операций, требующих монопольной (X) блокировки таблицы. Это позволяет достичь компромисса между скоростью выполнения без снижения параллелизма и числом возвращаемых статистических показателей. Однако если команда используется только для измерения фрагментации, рекомендуется использовать WITH FAST этот параметр для оптимальной производительности. Быстрая проверка не считывает конечные страницы или страницы уровня данных индекса. Параметр WITH FAST не применяется к куче.

Ограничения

DBCC SHOWCONTIG не отображает данные с типами данных ntext, text и image . Это связано с тем, что текстовые индексы, в которых хранятся текстовые и графические данные, больше не существуют.

Кроме того, DBCC SHOWCONTIG не поддерживает некоторые новые функции. Например:

• Если указанная таблица или индекс секционирована, DBCC SHOWCONTIG отображается только первая секция указанной таблицы или индекса.
• DBCC SHOWCONTIGне отображает сведения о хранилище строк и другие новые типы данных вне строки, такие как nvarchar(max), varchar(max), varbinary(max)и xml.
• Пространственные индексы не поддерживаются DBCC SHOWCONTIG.

Полностью поддерживает все новые возможности динамического административного представления sys.dm_db_index_physical_stats (Transact-SQL).

Фрагментация таблицы

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

Если индекс сильно фрагментирован, для снижения фрагментации можно применить следующее.

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

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

• Повторно упорядочить страницы индекса конечного уровня в логическом порядке.

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

• Перестроить индекс.

  Используется ALTER INDEX для REBUILD перестроения индекса. Дополнительные сведения см. в разделе ALTER INDEX (Transact-SQL).

Среднее значение байтов бесплатно на страницу и среднее значение. Статистика плотности страниц (полная) в результирующем наборе указывает на полноту страниц индекса. Среднее число байтов для каждого номера страницы должно быть низким, а среднее значение плотности страницы (полное) должно быть высоким для индекса, который не будет иметь большого количества случайных вставок. Удаление и повторное создание индекса с указанным параметром FILLFACTOR может улучшить статистику. Кроме того, с REORGANIZE сжатием индекса, ALTER INDEX учитывая егоFILLFACTOR, и улучшит статистику.

Примечание.

В индексе с множеством случайных вставок и очень заполненными страницами возрастет количество разделений страниц. Это приводит к увеличению фрагментации.

Уровень фрагментации индекса можно определить одним из следующих способов.

• Сравнением значений Extent Switches и Extents Scanned.

  Значение Extent Switches должно быть как можно ближе к значению Extents Scanned. Это отношение вычисляется как значение показателя Scan Density. Это значение должно быть как можно выше, и его можно улучшить, снизив уровень фрагментации индекса.

  Примечание.

  Данный метод не работает в том случае, если индекс охватывает несколько файлов.

• Путем анализа значений Logical Scan Fragmentation и Extent Scan Fragmentation.

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

  Примечание.

  Значение Extent Scan Fragmentation будет высоким, если индекс охватывает несколько файлов. Для снижения этих значений необходимо снизить уровень фрагментации индекса.

Разрешения

Пользователь должен быть либо владельцем таблицы, либо членом предопределенной роли сервера sysadmin, предопределенной роли базы данных db_owner или предопределенной роли базы данных db_ddladmin.

Примеры

А. Отображение сведений о фрагментации таблицы

В следующем примере отображаются сведения о фрагментации таблицы Employee.

USE AdventureWorks2022;
GO
DBCC SHOWCONTIG ('HumanResources.Employee');
GO

B. Используйте OBJECT_ID для получения идентификатора таблицы и sys.indexes для получения идентификатора индекса.

В следующем примере функция OBJECT_ID и представление каталога sys.indexes используются для получения идентификаторов таблицы и индекса для индекса AK_Product_Name таблицы Production.Product в базе данных AdventureWorks2022.

USE AdventureWorks2022;
GO
DECLARE @id INT, @indid INT
SET @id = OBJECT_ID('Production.Product');

SELECT @indid = index_id
FROM sys.indexes
WHERE object_id = @id
   AND name = 'AK_Product_Name';

DBCC SHOWCONTIG (@id, @indid);
GO

В. Отображение сокращенного результированного набора для таблицы

Следующий пример возвращает сокращенный результирующий набор для таблицы Product в базе данных AdventureWorks2022.

USE AdventureWorks2022;
GO
DBCC SHOWCONTIG ('Production.Product', 1) WITH FAST;
GO

D. Отображение полного результирующий набор для каждого индекса для каждой таблицы в базе данных

Следующий пример возвращает полный табличный результирующий набор по каждому индексу каждой таблицы в базе данных AdventureWorks2022.

USE AdventureWorks2022;
GO
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;
GO

Е. Использование DBCC SHOWCONTIG и DBCC INDEXDEFRAG для дефрагментации индексов в базе данных

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

/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename VARCHAR(255);
DECLARE @execstr   VARCHAR(400);
DECLARE @objectid  INT;
DECLARE @indexid   INT;
DECLARE @frag      DECIMAL;
DECLARE @maxfrag   DECIMAL;
  
-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;
  
-- Declare a cursor.
DECLARE tables CURSOR FOR
   SELECT TABLE_SCHEMA + '.' + TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE';
  
-- Create the table.
CREATE TABLE #fraglist (
   ObjectName CHAR(255),
   ObjectId INT,
   IndexName CHAR(255),
   IndexId INT,
   Lvl INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity DECIMAL,
   BestCount INT,
   ActualCount INT,
   LogicalFrag DECIMAL,
   ExtentFrag DECIMAL);
  
-- Open the cursor.
OPEN tables;
  
-- Loop through all the tables in the database.
FETCH NEXT
   FROM tables
   INTO @tablename;
  
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
   FETCH NEXT
      FROM tables
      INTO @tablename;
END;
  
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
  
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
  
-- Open the cursor.
OPEN indexes;
  
-- Loop through the indexes.
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag;
  
WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%';
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')';
   EXEC (@execstr);
  
   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag;
END;
  
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
  
-- Delete the temporary table.
DROP TABLE #fraglist;
GO

См. также

• ALTER INDEX (Transact-SQL)
• Инструкция CREATE INDEX (Transact-SQL)
• DBCC (Transact-SQL)
• DROP INDEX (Transact-SQL)
• sys.dm_db_index_physical_stats (Transact-SQL)
• OBJECT_ID (Transact-SQL)
• sys.indexes (Transact-SQL)