Могут ли ключи кластерного индекса с типом GUID вызывать фрагментацию некластерных индексов?

Автор: Paul Randal, Can GUID cluster keys cause non-clustered index fragmentation?

На встрече пользовательской группы я потратил некоторое время на объяснение того, как GUID могут вызывать фрагментацию как в кластерных, так и в некластерных индексах, даже если GUID специально не включён в ключ некластерного индекса. GUID — это, по сути, случайные значения (псевдослучайные в диапазонах, если генерируются с помощью NEWSEQUENTIALID), которые также уникальны. Их уникальность делает их привлекательными для многих разработчиков в качестве значения ключа, не понимая при этом того хаоса, который они могут вызвать в производственной среде с точки зрения фрагментации и низкой производительности запросов.

Ключ GUID вызывает фрагментацию из-за своей случайности. Точка вставки новой записи в индекс определяется значением ключа индекса, поэтому если значение ключа случайно, то случайна и точка вставки. Это означает, что если страница индекса заполнена, случайная вставка, которая должна попасть именно на эту страницу, приведёт к разбиению страницы (page split) для освобождения места для новой записи. Разбиение страницы — это когда выделяется новая страница и (насколько это возможно) половина строк из разделяемой страницы перемещается на новую страницу. Новая строка затем вставляется в одну из двух страниц в зависимости от значения ключа. Обычно вновь выделенная страница не является физически смежной с разделяемой страницей, и поэтому возникает фрагментация. В этом случае возникают два вида фрагментации: логическая (внешняя) фрагментация (когда следующая логическая страница, определяемая порядком индекса, не является следующей физической страницей в файле данных) и физическая (внутренняя) фрагментация (когда место на страницах индекса тратится впустую). Обе могут влиять на производительность запросов, а также на стоимость самого разбиения страницы.

Довольно хорошо известно, что GUID могут вызывать фрагментацию в индексе, где GUID является ключом (например, кластерный индекс), но не о побочных эффектах в некластерных индексах. Вот пример — я создам два кластерных индекса с ключами GUID (один, сгенерированный из NEWID, и один из NEWSEQUENTIALID), а также некластерный индекс для каждого. Посмотрим, что произойдёт при вставке 100000 строк:

-- Создание таблицы с ключом GUID
CREATE TABLE BadKeyTable (
   c1 UNIQUEIDENTIFIER DEFAULT NEWID() ROWGUIDCOL,
   c2 DATETIME DEFAULT GETDATE(),
   c3 CHAR(400) DEFAULT 'a');
CREATE CLUSTERED INDEX BadKeyTable_CL ON BadKeyTable (c1);
CREATE NONCLUSTERED INDEX BadKeyTable_NCL ON BadKeyTable (c2);
GO

-- Создание другой таблицы, но с NEWSEQUENTIALID
CREATE TABLE BadKeyTable2 (
   c1 UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
   c2 DATETIME DEFAULT GETDATE(),
   c3 CHAR(400) DEFAULT 'a');
CREATE CLUSTERED INDEX BadKeyTable2_CL ON BadKeyTable2 (c1);
CREATE NONCLUSTERED INDEX BadKeyTable2_NCL ON BadKeyTable2 (c2);
GO

DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 10000)
BEGIN
   INSERT INTO BadKeyTable DEFAULT VALUES;
   INSERT INTO BadKeyTable2 DEFAULT VALUES;
   SELECT @a = @a + 1;
END;
GO

-- Проверка фрагментации
SELECT
   OBJECT_NAME(ips.[object_id]) AS 'Object Name',
   si.name AS 'Index Name',
   ROUND(ips.avg_fragmentation_in_percent, 2) AS 'Fragmentation',
   ips.page_count AS 'Pages',
   ROUND(ips.avg_page_space_used_in_percent, 2) AS 'Page Density'
FROM sys.dm_db_index_physical_stats (DB_ID('DBMaint2008'), NULL, NULL, NULL, 'DETAILED') ips
CROSS APPLY sys.indexes si
WHERE
   si.object_id = ips.object_id
   AND si.index_id = ips.index_id
   AND ips.index_level = 0;
GO

Результаты:

• BadKeyTable (NEWID): кластерный индекс — фрагментация 99.13%, страниц 8092, плотность 66.08%; некластерный индекс — фрагментация 30.97%, страниц 78, плотность 64.1%.
• BadKeyTable2 (NEWSEQUENTIALID): кластерный индекс — фрагментация 0.83%, страниц 5556, плотность 96.26%; некластерный индекс — фрагментация 1.88%, страниц 372, плотность 99.61%.

Кластерный индекс BadKeyTable_CL с ключом GUID, сгенерированным из NEWID, почти полностью фрагментирован, при этом 34% пространства на каждой странице тратится впустую. И наоборот, кластерный индекс BadKeyTable2_CL с ключом GUID, сгенерированным из NEWSEQUENTIALID, почти не фрагментирован, и тратится впустую только 4% свободного пространства. Эти числа полностью ожидаемы, учитывая природу ключей кластера.

Теперь посмотрите на некластерные индексы. BadKeyTable_NCL фрагментирован на 31% с 36% потраченного пространства на каждой странице! BadKeyTable2_NCL почти не фрагментирован с практически отсутствующим потраченным пространством. Так что же происходит? Ключ некластерного индекса в обоих случаях — это столбец типа datetime, который имеет минимальную гранулярность 3 миллисекунды. Код выше выполняется в тесном цикле, вставляя записи, и может вставлять более одной записи за 3-миллисекундный интервал — и я допускаю это, потому что не сделал некластерные индексы уникальными. Для всех записей, вставленных за один 3-миллисекундный интервал, должен существовать какой-то фактор, делающий ключ некластерного индекса внутренне уникальным (так как даже если индекс определён как неуникальный, Storage Engine требует, чтобы каждая запись действительно была уникальной в индексе, и добавит всё необходимое для этого).

В этом случае ключ кластерного индекса (который в любом случае должен присутствовать в некластерном индексе) используется для различения всех записей некластерного индекса с одинаковым значением datetime. Для BadKeyTable_NCL ключ кластера — это случайный GUID, поэтому точки вставки записей некластерного индекса ТАКЖЕ становятся случайными в каждом 3-миллисекундном интервале — что приводит к фрагментации, показанной выше. Некластерный индекс BadKeyTable2_NCL имеет ту же проблему с временным интервалом, но его ключ кластера является последовательным GUID, поэтому некластерный индекс не фрагментируется. Если бы ключ некластерного индекса был типом времени с большей минимальной гранулярностью (например, smalldatetime или новый date), фрагментация BadKeyTable_NCL была бы ещё хуже — попробуйте сами, и вы увидите.

Таким образом, ответ на вопрос в заголовке сообщения блога — действительно, зависит от обстоятельств! При определённых условиях ключ кластера GUID также может серьёзно фрагментировать некластерный индекс.