Статья
09/28/2010

DBCC INDEXDEFRAG (Transact-SQL)

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

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

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

Значок ссылки на раздел Синтаксические обозначения в Transact-SQL

Синтаксис

DBCC INDEXDEFRAG
(
    { database_name | database_id | 0 } 
        , { table_name | table_id | view_name | view_id } 
    [ , { index_name | index_id } [ , { partition_number | 0 } ] ]
)
    [ WITH NO_INFOMSGS ]

Аргументы

database_name| database_id | 0
База данных, содержащая индекс для дефрагментации. Если этот аргумент равен 0, используется текущая база данных. Имена баз данных должны соответствовать правилам идентификаторов.
table_name | table_id | view_name | view_id
Таблица или представление, содержащие индекс для дефрагментации. Имена таблиц и представлений должны соответствовать требованиям, предъявляемым к идентификаторам.
index_name | index_id
Имя или идентификатор индекса, подлежащего дефрагментации. Если этот аргумент не указан, дефрагментируются все индексы заданной таблицы или представления. Имена индексов должны соответствовать требованиям, предъявляемым к идентификаторам.
partition_number | 0
Номер секции индекса, которую следует дефрагментировать. Если этот аргумент не указан или равен 0, дефрагментируются все секции заданного индекса.
WITH NO_INFOMSGS
Подавляет все информационные сообщения с уровнями серьезности от 0 до 10.

Замечания

Инструкция DBCC INDEXDEFRAG дефрагментирует конечный уровень индекса, приводя физический порядок страниц в соответствие логическому порядку конечных узлов слева направо, что повышает эффективность сканирования индекса.

Примечание
При выполнении инструкции DBCC INDEXDEFRAG дефрагментация индекса осуществляется последовательно. Это означает, что операции над одним индексом выполняются в одном потоке без параллелизма. Операции над несколькими индексами, относящиеся к одной инструкции DBCC INDEXDEFRAG, выполняются над одним индексом за раз.

При выполнении инструкции DBCC INDEXDEFRAG дефрагментация индекса осуществляется последовательно. Это означает, что операции над одним индексом выполняются в одном потоке без параллелизма. Операции над несколькими индексами, относящиеся к одной инструкции DBCC INDEXDEFRAG, выполняются над одним индексом за раз.

Кроме того, инструкция DBCC INDEXDEFRAG сжимает страницы индекса с учетом коэффициента заполнения, указанного при создании индекса. Любые пустые страницы при этом удаляются. Дополнительные сведения см. в разделе Коэффициент заполнения.

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

Инструкция DBCC INDEXDEFRAG сообщает процент выполнения дефрагментации каждые пять минут. Дефрагментацию можно остановить в любой момент, при этом вся выполненная работа сохраняется.

В отличие от инструкции DBCC DBREINDEX и операций создания индексов вообще, инструкция DBCC INDEXDEFRAG выполняется в оперативном режиме. Она не удерживает блокировки длительное время. Таким образом, она не блокирует выполнение запросов или обновлений. Так как время дефрагментации зависит от степени фрагментации, сравнительно нефрагментированный индекс иногда можно дефрагментировать быстрее, чем создать новый индекс. На дефрагментацию сильно фрагментированного индекса может уйти гораздо больше времени, чем на его создание заново.

Процесс дефрагментации всегда полностью регистрируется в журнале независимо от модели восстановления баз данных. Дополнительные сведения см. в разделе ALTER DATABASE (Transact-SQL). Дефрагментация сильно фрагментированного индекса может привести к записи большего объема данных, чем создание индекса с полной регистрацией в журнале. Однако дефрагментация выполняется как ряд кратких транзакций, поэтому она не требует большого журнала, если часто создаются резервные копии журнала или если применяется простая (SIMPLE) модель восстановления.

Ограничения

Инструкция DBCC INDEXDEFRAG перемещает конечные страницы индекса в произвольном порядке. Таким образом, если содержимое индекса чередуется на диске с содержимым других индексов, выполнение инструкции DBCC INDEXDEFRAG для этого индекса не приведет к расположению всех конечных страниц индекса в последовательном порядке. Чтобы улучшить кластеризацию страниц, создайте индекс заново.

Инструкция DBCC INDEXDEFRAG не может быть использована для дефрагментации следующих индексов:

Отключенный индекс.
Индекс с отключенной блокировкой страниц.
Пространственный индекс.

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

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

Если в инструкции DBCC INDEXDEFRAG указан индекс (но не указан аргумент WITH NO_INFOMSGS), она возвращает следующий результирующий набор (значения могут быть иными):

Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
359           346         8

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Разрешения

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

Примеры

А. Использование инструкции DBCC INDEXDEFRAG для дефрагментации индекса

Следующий код дефрагментирует все секции индекса PK_Product_ProductID таблицы Production.Product в базе данных AdventureWorks.

DBCC INDEXDEFRAG (AdventureWorks, "Production.Product", PK_Product_ProductID)
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