DBCC SHOWCONTIG (Transact-SQL)

Статья
12/15/2008

Изменения: 15 сентября 2007 г.

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

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

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

Значок ссылки на раздел Соглашения о синтаксическом обозначении в 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.

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

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

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

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

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

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

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

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

Статистика

Описание

Pages Scanned

Количество страниц в таблице или индексе.

Extents Scanned

Количество экстентов в таблице или индексе.

Extent Switches

Количество раз, когда инструкция DBCC в процессе обхода страниц таблицы или индекса переходила с одного экстента на другой.

Avg. Pages per Extent

Количество страниц на экстент в цепочке страниц.

Scan Density [Best Count: Actual Count]

Процентное отношение. Это отношение Best Count к Actual Count. Значение 100 указывает на отсутствие фрагментации; значение меньше 100 указывает на наличие фрагментации.

Best Count — это идеальное количество изменений экстентов в случае полной непрерывности. Actual Count — это реальное количество изменений экстентов.

Logical Scan Fragmentation

Процент неупорядоченных страниц, определенный с помощью просмотра конечных страниц индекса. Это значение несущественно для куч. Неупорядоченной называется страница, для которой следующая физическая страница, выделенная для индекса, не является страницей, на которую ссылается указатель следующей страницы** в текущей конечной странице.

Extent Scan Fragmentation

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

ms175008.note(ru-ru,SQL.90).gif Примечание.

Это значение не имеет смысла, если индекс охватывает несколько файлов.

Avg. Bytes Free per Page

Среднее количество свободных байтов на просмотренных страницах. Чем больше это число, тем меньше заполнены страницы. Предпочтительны более низкие показатели, если в индексе не будет большого количества случайных вставок. На этот показатель влияет также размер строк: большой размер строки может привести к его увеличению.

Avg. Page density (full)

Средняя плотность страницы в процентах. Этот показатель учитывает размер строк. Поэтому он более точно отражает плотность заполнения страниц. Чем больше процентное отношение, тем лучше.

Если указаны параметры table_id и FAST, инструкция DBCC SHOWCONTIG возвращает в результирующем наборе только следующие столбцы.

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

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

Статистика

Описание

Object Name

Имя обработанной таблицы или представления.

ObjectId

Идентификатор объекта.

IndexName

Имя обработанного индекса. Для кучи это значение NULL.

IndexId

Идентификатор индекса. Для кучи равен 0.

Level

Уровень индекса. Уровень 0 представляет собой конечный уровень или уровень данных индекса.

Для кучи уровень равен 0.

Pages

Количество страниц, образующих данный уровень индекса или всю кучу.

Rows

Количество записей данных или индексных записей на этом уровне индекса. Для кучи это число записей данных во всей куче.

Число записей, возвращаемых этой функцией для кучи, может не соответствовать числу строк, возвращаемых из кучи запросом SELECT COUNT(*). Это происходит потому, что строка может содержать несколько записей. Например, при обновлении одна строка кучи может иметь указывающую запись и перенаправляющую запись как результат операции обновления. Также большинство больших LOB-строк разбиты на различные записи в хранилище LOB_DATA.

MinimumRecordSize

Минимальный размер записи на данном уровне индекса или во всей куче.

MaximumRecordSize

Максимальный размер записи на данном уровне индекса или во всей куче.

AverageRecordSize

Средний размер записи на данном уровне индекса или во всей куче.

ForwardedRecords

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

Extents

Количество экстентов на данном уровне индекса или во всей куче.

ExtentSwitches

AverageFreeBytes

AveragePageDensity

ScanDensity

Процентное отношение. Это отношение значения BestCount к значению ActualCount. Значение 100 указывает на отсутствие фрагментации; значение меньше 100 указывает на наличие фрагментации.

BestCount

Идеальное количество изменений экстентов в случае полной непрерывности.

ActualCount

Реальное число изменений экстентов.

LogicalFragmentation

ExtentFragmentation

ms175008.note(ru-ru,SQL.90).gif Примечание.

Это значение не имеет смысла, если индекс охватывает несколько файлов.

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

Rows	Extents
MinimumRecordSize	AverageFreeBytes
MaximumRecordSize	AveragePageDensity
AverageRecordSize	ExtentFragmentation
ForwardedRecords

Замечания

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

Изменения в SQL Server 2005

Алгоритм для расчета фрагментации в SQL Server 2005 более точный, чем в SQL Server 2000. В результате этого показатели фрагментации повысились. Например, в SQL Server 2000 таблица не считается фрагментированной, если страницы 11 и 13 расположены в одном экстенте, а страница 12 — в другом. Однако, чтобы получить доступ к этим двум страницам, потребуется две физические операции ввода-вывода, поэтому в SQL Server 2005 это считается фрагментацией.

Инструкция DBCC SHOWCONTIG не отображает данные типов ntext, text и image. Это связано с тем, что в SQL Server 2005 больше нет текстовых индексов (идентификатор индекса 255 в SQL Server 2000), хранящих текстовые и графические данные. Дополнительные сведения об идентификаторе индекса 255 см. в разделе sys.sysindexes (Transact-SQL).

Кроме того, инструкция DBCC SHOWCONTIG не поддерживает некоторые новые возможности SQL Server 2005. Например:

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

Все новые возможности SQL Server 2005 полностью поддерживаются динамическим административным представлением sys.dm_db_index_physical_stats.

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

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

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

Удалить и повторно создать кластеризованный индекс.
При повторном создании кластеризованного индекса данные реорганизуются, и страницы данных заполняются полностью. Уровень заполнения можно настроить с помощью параметра FILLFACTOR инструкции CREATE INDEX. К недостаткам этого метода относятся атомарный характер операции и переход индекса в автономный режим во время цикла удаления или повторного создания. Если создание индекса прервано, он не создается заново.
Повторно упорядочить страницы индекса конечного уровня в логическом порядке.
Повторно упорядочить страницы индекса конечного уровня в логическом порядке можно с помощью инструкции ALTER INDEX…REORGANIZE. Так как эта операция производится в оперативном режиме, в процессе выполнения инструкции индекс остается доступным. Кроме того, операция может быть прервана без потери выполненной работы. Недостаток этого метода — эффективность реорганизации данных с его помощью ниже, чем при удалении и повторном создании кластеризованного индекса.
Позволяет перестроить индекс.
Перестроить индекс можно с помощью инструкции ALTER INDEX с параметром REBUILD. Дополнительные сведения см. в разделе ALTER INDEX (Transact-SQL).

Статистические показатели Avg. Bytes free per page и Avg. Page density (full) в результирующем наборе указывают на заполнение страниц индекса. Статистические показатели Avg. Bytes free per page должен быть низким, а показатель Avg. Page density (full) — высоким для индекса с небольшим количеством случайных вставок. При удалении и повторном создании индекса с параметром FILLFACTOR показатели могут улучшиться. Кроме того, инструкция ALTER INDEX с параметром REORGANIZE позволяет сжать индекс, принимая во внимание его фактор заполнения FILLFACTOR, что также улучшит показатели.

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

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

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

Примечание.

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

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

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

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

Разрешения

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

Примеры

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

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

USE AdventureWorks;
GO
DBCC SHOWCONTIG ("HumanResources.Employee");
GO

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

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

USE AdventureWorks;
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 базы данных AdventureWorks .

USE AdventureWorks;
GO
DBCC SHOWCONTIG ("Production.Product", 1) WITH FAST;
GO

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

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

USE AdventureWorks;
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

См. также

Журнал изменений

Версия	Журнал
15 сентября 2007 г.	Изменения Исправлены сведения о блокировании в разделе «Примечания». В SQL Server 2005 эта команда использует для таблицы только коллективную блокировку намерения (IS), а не S-блокировку, как было заявлено ранее. Включены дополнительные пояснения в определение столбца Rows для куч.
17 июля 2006 г.	Добавления В раздел «Изменения в SQL Server 2005» добавлены сведения об алгоритме для расчета фрагментации.
5 декабря 2005 г.	Изменения Исправлено описание LogicalFragmentation.

Поделиться через