Когда имеет значение фрагментация индекса?

Некоторое время назад мы писали о том, что фрагментация индексов в 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, лежащая в основе схема остается интересной для специалистов по базам данных.