DBCC SHOWCONTIG (Transact-SQL)
Применимо к:
SQL Server Управляемый экземпляр SQL Azure
Отображает сведения о фрагментации данных и индексов указанной таблицы или представления.
Важно!
В будущей версии Microsoft 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 ]
}
]
Примечание
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Аргументы
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 в процессе обхода страниц таблицы или индекса переходила с одного экстента на другой. |
| Средн. 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 | Процент неупорядоченных экстентов при просмотре конечных страниц индекса. Это число не относится к кучам. Экстент не в порядке — это экстент, для которого экстент, содержащий текущую страницу индекса, физически не является следующим экстентом после экстента, содержащего предыдущую страницу индекса. Примечание: Это число не имеет смысла, если индекс охватывает несколько файлов. |
| Средн. Bytes Free per Page | Среднее количество свободных байтов на просмотренных страницах. Чем больше это число, тем меньше заполнены страницы. Более низкие числа лучше, если индекс не будет содержать много случайных вставок. На этот показатель влияет также размер строк: большой размер строки может привести к его увеличению. |
| Средн. 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 описанных в предыдущей таблице.
| Статистика | Описание |
|---|---|
| Имени объекта | Имя обработанной таблицы или представления. |
| ObjectId | Идентификатор объекта. |
| IndexName | Имя обработанного индекса. Значение NULL для кучи. |
| IndexId | Идентификатор индекса. 0 для кучи. |
| Level | Уровень индекса. Уровень 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 указании, за исключением того, что следующие столбцы будут иметь значения NULL:
| Строки | Экстенты |
|---|---|
| MinimumRecordSize | AverageFreeBytes |
| MaximumRecordSize | AveragePageDensity |
| AverageRecordSize | ExtentFragmentation |
| ForwardedRecords |
Remarks
Оператор 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).
Показатель Avg. Bytes free per page и Avg. Page density (full) в результирующем наборе указывает на заполнение страниц индекса. Среднее число свободных байтов на страницу должно быть низким, а среднее значение плотности страницы (полная) должно быть высоким для индекса, который не будет иметь много случайных вставок. Удаление и повторное создание индекса с указанным параметром FILLFACTOR может улучшить статистику. Кроме того, ALTER INDEX с REORGANIZE будет сжимать индекс с учетом его 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.
Примеры
A. Отображение сведений о фрагментации для таблицы
В следующем примере отображаются сведения о фрагментации таблицы Employee.
USE AdventureWorks2022;
GO
DBCC SHOWCONTIG ('HumanResources.Employee');
GO
Б. Используйте 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
Г. Отображение полного результирующих наборов для каждого индекса в каждой таблице базы данных
Следующий пример возвращает полный табличный результирующий набор по каждому индексу каждой таблицы в базе данных 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
См. также раздел
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по