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