Насколько дороги расщепление страниц с точки зрения журнала транзакций?

Автор: Paul Randal, How expensive are page splits in terms of transaction log?

Расщепление страниц (page splits) всегда считаются дорогими, но насколько же на самом деле? В этой статье я покажу пример, насколько больше становится журнал транзакций, когда страница в индексе вынужденно расщепляются. Я буду использовать DMV sys.dm_tran_database_transactions, чтобы это продемонстрировать. Список столбцов и краткие пояснения к каждому столбцу можно найти в Books Online — я вспомнил о её существовании благодаря кому‑то в Twitter (увы, не помню, кто это был, и не смог найти упоминание в поиске).

В примере я создам таблицу со строками, приблизительно 1000‑байт каждая:

CREATE DATABASE PageSplitTest;
GO
USE pagesplittest;
GO

CREATE TABLE BigRows (c1 INT, c2 CHAR (1000));
CREATE CLUSTERED INDEX BigRows_CL ON BigRows (c1);
GO

INSERT INTO BigRows VALUES (1, 'a');
INSERT INTO BigRows VALUES (2, 'a');
INSERT INTO BigRows VALUES (3, 'a');
INSERT INTO BigRows VALUES (4, 'a');
INSERT INTO BigRows VALUES (6, 'a');
INSERT INTO BigRows VALUES (7, 'a');
GO

Я специально подготовил ситуацию, когда на странице данных кластерного индекса остаётся место ровно для одной строки и оставлен «пропуск» на c1=5. Добавим её в рамках явной транзакции и посмотрим, сколько журнала будет сгенерировано:

BEGIN TRAN
INSERT INTO BigRows VALUES (8, 'a');
GO

SELECT [database_transaction_log_bytes_used]
FROM sys.dm_tran_database_transactions
WHERE [database_id] = DB_ID('PageSplitTest');
GO

database_transaction_log_bytes_used
-----------------------------------
1228

Это примерно то, чего я ожидал для такой строки. А что произойдёт, если я спровоцирую расщепление страницы, вставив «пропущенную» строку с c1=5 в заполненную страницу?

-- commit previous transaction
COMMIT TRAN
GO

BEGIN TRAN
INSERT INTO BigRows VALUES (5, 'a');
GO

SELECT [database_transaction_log_bytes_used]
FROM sys.dm_tran_database_transactions
WHERE [database_id] = DB_ID('PageSplitTest');
GO

database_transaction_log_bytes_used
-----------------------------------
6724

Вот это да. В 5,5 раза больше байтов записано в журнал в составе системной транзакции, выполняющей расщепление.

Отношение становится хуже по мере уменьшения размера строки. Для приблизительно 100‑байтовой строки (используйте тот же код, но смените на CHAR (100), вставьте 67 строк с «пробелом» где‑то посередине, затем вставьте 68‑ю, чтобы вызвать расщепление) вышли числа 328 и 5924 — расщепление вызвало генерацию журнала в 18 раз больше! Для приблизительно 10‑байтовой строки я получил 240 и 10436, потому что я создал «перекошенные» данные (около 256 строк со значением ключа 8), а затем вставил значение ключа 5, что вынудило (редкое) «не по середине» расщепление страницы. Это соотношение более чем 43 раза по объёму сгенерированного журнала! Можете попробовать сами: я изменил код, указав CHAR (10), вставил значения 1, 2, 3, 4, 6, 7, затем вставил 256 ключевых значений 8 и затем 2 — значения 5. В результате на странице осталось всего 6 строк — она раскололась после ключевого значения 5 — механизм хранения не всегда делает расщепление 50/50. И это даже без неприятных каскадных расщеплений страниц или таких случаев, когда для размещения новой (переменной длины) строки приходится делить страницу многократно.

Вывод: расщепления страниц вызывают не только дополнительные операции ввода‑вывода и фрагментацию индексов, но и генерируют куда больше объёма журнала транзакций. А весь этот журнал потенциально нужно резервировать, пересылать в лог‑шиппинг, зеркалировать….