Некоторое время назад мы писали о том, что фрагментация индексов в SQL Server не имеет значения. Этот пост вызвал много горячих дискуссий и помог людям понять, что причиной их проблем может быть вовсе не фрагментация. Однако иногда бывают такие схемы доступа к данным, при которых фрагментация действительно играет важную роль. 

Постоянная фрагментация

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

 Как выглядит эта модель? Давайте посмотрим!

CREATE TABLE process_list

    (
      transaction_id INT IDENTITY(1, 1)
                     	PRIMARY KEY ,
      status_flag BIT ,
      last_update DATETIME2 ,
      transaction_type INT ,
      transaction_desc VARCHAR(25)
    );
GO

CREATE INDEX IX_giant_index_process_list
ON dbo.process_list (transaction_id, status_flag);
GO

TRUNCATE TABLE process_list ;
GO

/*
   Random string generation code taken from:
   http://social.technet.microsoft.com/wiki/contents/articles/21196.t-sql-random-string.aspx
*/

/* insert another 1000 rows */
DECLARE @string_max_length TINYINT = 25;

INSERT  INTO process_list (status_flag, last_update, transaction_type, transaction_desc)
        SELECT  0 ,
                GETUTCDATE() ,
                v.number % 4 + 1 ,
                x.txt
        FROM    master.dbo.spt_values v
                JOIN ( SELECT TOP 1
                                LEFT(REPLACE(CAST (NEWID() AS NVARCHAR(MAX)),
                                         	'-', ''),
                                 	ABS(CHECKSUM(NEWID()))
                                 	% @string_max_length + 1) AS txt
                   	FROM 	SYS.OBJECTS A
                                CROSS JOIN SYS.OBJECTS B
                 	) AS x ON 1 = 1
        WHERE   v.type = 'P'
                AND v.number < 1001;

/* 
   Look at table/index size.
   The table gains 9 pages.
   The index only gains 1 page from its previous state
*/
SELECT  OBJECT_SCHEMA_NAME(o.object_id) AS schema_name,
        o.name,
        i.name,
        ddps.reserved_page_count AS reserved_pages
FROM    sys.objects AS o
        JOIN sys.dm_db_partition_stats AS ddps ON ddps.object_id = o.object_id
        JOIN sys.indexes AS i ON i.object_id = ddps.object_id AND i.index_id = ddps.index_id
WHERE   o.name = 'process_list' ;

-- Change the status of 70%ish of transaction to TRUE
UPDATE process_list
SET 	status_flag = 1
WHERE   transaction_id % 10 < 7 ;

/* Get rid of processed rows */
DELETE FROM process_list
WHERE status_flag = 1 ;

/* 
   Look at table/index size.
   No page size changes...
*/
SELECT  OBJECT_SCHEMA_NAME(o.object_id) AS schema_name,
        o.name,
        i.name,
        ddps.reserved_page_count AS reserved_pages
FROM    sys.objects AS o
        JOIN sys.dm_db_partition_stats AS ddps ON ddps.object_id = o.object_id
        JOIN sys.indexes AS i ON i.object_id = ddps.object_id AND i.index_id = ddps.index_id
WHERE   o.name = 'process_list' ;
GO 10

/* 
   And now we rebuild the table
   Before the rebuild:
 	index - 25 pages
 	table - 57 pages

   If you just run ALTER TABLE process_list REBUILDema:
 	index - 25 pages
 	table - 25 pages
   After the rebuild with the index:
 	index -  7 pages
 	table - 25 pages
*/
ALTER INDEX ALL ON dbo.process_list REBUILD ;

SELECT  OBJECT_SCHEMA_NAME(o.object_id) AS schema_name,
        o.name,
        i.name,
        ddps.reserved_page_count AS reserved_pages
FROM    sys.objects AS o
        JOIN sys.dm_db_partition_stats AS ddps ON ddps.object_id = o.object_id
        JOIN sys.indexes AS i ON i.object_id = ddps.object_id AND i.index_id = ddps.index_id
WHERE   o.name = 'process_list' ;

GO

Единственный способ избавиться от этой фрагментации — перестроить как некластеризованный индекс, так и кластерный индекс. 

В этом примере дефрагментация уменьшает размер индекса более чем в 3 раза (с 25 страниц до 7 страниц), а таблица занимает чуть больше половины места. 

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

 Без дефрагментации эти базы данных продолжат увеличиваться в размере и приведут к обычным проблемам, связанным с фрагментацией, которые мы знаем. Что не вызывает постоянной фрагментации На данный момент вы можете сказать: «Ага! Вы доказали, что я должен всегда дефрагментировать свои индексы! Постоянно! ОМГ ВТФ У!!1!1!!!111!!!!»

 Вы ошибаетесь. 

Для некоторых рабочих нагрузок можно по-прежнему избежать фрагментации индексов — если вы добавляете данные в конец таблицы и удаляете данные из начала таблицы, вам не нужно будет выполнять дефрагментацию индексов. 

В данном случае очистку записей-призраков следует обрабатывать для удаления строк.

 Для большинства рабочих нагрузок ваша лучшая стратегия все еще заключается в анализе статистики ожидания SQL Server и понимании основной проблемы, прежде чем начать регулярно дефрагментировать индексы. 

Благодарности 

Идея этого поста взята из статьи Хеманта К Читале "Индекс растет больше, чем таблица". 

Хотя SQL Server обрабатывает эту ситуацию иначе, чем Oracle, лежащая в основе схема остается интересной для специалистов по базам данных.