Включаем умный параллелелизм вставки с помощью OPTIMIZE_FOR_SEQUENTIAL_KEY

Автор: Chandan Shukla, Unlocking High-Concurrency Inserts in SQL Server with OPTIMIZE_FOR_SEQUENTIAL_KEY

Системы с высокой параллельностью на бумаге всегда выглядят впечатляюще. Вы добавляете десятки процессорных ядер, увеличиваете объём памяти, проектируете схему с «лёгкими» вставками и с удовлетворением думаете: «Всё полетит». И, по правде говоря, при небольшой нагрузке так и выходит. Одна сессия, вставляющая строки в простую таблицу, даже не заставляет SQL Server напрячься.

Но картина меняется, как только вы начинаете нагружать систему сотнями параллельных вставок. Внезапно вся вычислительная мощь перестаёт иметь значение, потому что каждый поток бьётся за одно крошечное место в памяти: последнюю страницу кластерного индекса. Это классическая проблема «last-page insert contention problem». Она возникает всякий раз, когда ключ кластерного индекса последовательный — типичные IDENTITY, DATETIME или NEWSEQUENTIALID(). Каждая новая строка естественным образом «тянется» в конец B-дерева. Это звучит упорядоченно и эффективно, но при конкуренции — это ловушка. Вместо распределения вставок по нескольким страницам все они наваливаются на одну «горячую» страницу.

В такой ситуации у SQL Server не остаётся выбора, кроме как сериализовать доступ с помощью кратких блокировок (latch). Вы увидите рост ожиданий PAGELATCH_EX, «spinning» рабочих потоков и падение пропускной способности. То, что в теории выглядело как «minimal locking», превращается в пробку, где каждая вставка ждёт своей очереди, чтобы захватить latch в памяти. Почти несправедливо: вас блокируют не логические блокировки, а куда более низкоуровневый механизм — latch, защищающий структуру страницы. Итог прост: ваш масштабируемый проект "разваливается" из-за единственной горячей точки в B-дереве.

Для наглядности представьте простейшую таблицу заказов:

INSERT INTO SalesOrders(CustomerID, Amount) VALUES (123, 99.99);

Запускайте это в нескольких параллельных сессиях — и всё в порядке. Масштабируйте до десятков или сотен потоков — и вместо красивых графиков производительности вы начинаете смотреть на диаграммы состязаний за latch.

Microsoft понимала, что это реальная проблема, поэтому в SQL Server 2019 появилась настройка OPTIMIZE_FOR_SEQUENTIAL_KEY. Лучшее в ней то, что не требуется менять схему, переписывать приложение или прибегать к хитростям. Одним-единственным ALTER INDEX вы даёте сигнал SQL Server включить внутреннюю стратегию «рассинхронизации» — своего рода разумного регулировщика, который «разводит» потоки, не позволяя им всем одновременно влетать в один и тот же узкий перекрёсток.

Эта статья — о понимании проблемы, демонстрации её под нагрузкой и о том, как OPTIMIZE_FOR_SEQUENTIAL_KEY незаметно сглаживает острые углы.

Проблема: конкуренция за последнюю страницу при вставках

Допустим, у вас есть простая таблица продаж:

CREATE TABLE dbo.SalesOrders
(
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    OrderDate DATETIME DEFAULT GETDATE(),
    CustomerName VARCHAR(100),
    Amount DECIMAL(10,2)
);

При однопоточном потоке вставок проблем нет. Но когда много сессий вставляют строки, каждая новая запись попадает на последнюю страницу кластерного индекса. Множество сессий хотят получить эксклюзивную защёлку на этой странице → SQL Server их сериализует → ожидания PAGELATCH_EX доминируют. Парадокс в том, что вы боретесь не за логические блокировки — вы упираетесь в latch, защищающий физическую структуру страницы. Традиционные обходные пути — переупорядочивание ключей, смена схемы, шардинг — редко применимы в промышленной эксплуатации.

INSERT INTO dbo.SalesOrders (CustomerName, Amount)
VALUES ('TestUser', 99.99);

Поскольку OrderID возрастает, все строки собираются на последней странице кластерного индекса. Результат?

• Несколько сессий пытаются получить эксклюзивный latch на последней странице.
• SQL Server сериализует эти запросы → ожидание PAGELATCH_EX.
• Пропускная способность падает, CPU простаивает, вставки ползут.

Что происходит внутри при OPTIMIZE_FOR_SEQUENTIAL_KEY

Когда несколько сессий пытаются вставить данные на одну и ту же последнюю страницу, все разом стремятся захватить эксклюзивный latchу (PAGELATCH_EX). Без OFSK (Optimize for Sequential Key) SQL Server действует по сути так: «Все в очередь, первый получил latch, остальные ждут».

И вот 50 сессий одновременно упираются в latch. Невезучие тратят CPU на «spinning» или просто ждут увеличивая время ожидания. Поэтому вы видите накапливающиеся signal wait во внешней статистике ожиданий (sys.dm_os_wait_stats).

С OPTIMIZE_FOR_SEQUENTIAL_KEY = ON SQL Server умнее. Вместо того чтобы позволить всем потокам конкурировать, он вводит алгоритм рассинхронизации. Внутренне он отслеживает картину конкуренции и «говорит» входящим сессиям:

«Погодите, слишком много поваров на кухне. Ты — сейчас, ты — подожди чуть-чуть, ты — подожди ещё чуть больше».

Это не случайность. SQL Server разводит попытки захвата защёлки микросекундными задержками. И вместо того, чтобы 50 сессий сталкивались и блокировались, они сериализуются более упорядоченно. Итог:

• Меньше «spinning» (потоки не запрашивают latch в плотном цикле)
• Ниже накладные расходы на CPU
• Более предсказуемая пропускная способность при конкуренции
• Общее время ближе к «идеально параллельному»

Да, отдельные вставки могут получить микро-задержку, но весь рабочий пакет завершается быстрее, потому что удаётся избежать хаоса на «горячей» странице.

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

ALTER INDEX [PK_SalesOrders] ON dbo.SalesOrders  
SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);

Что меняется. Без этой опции каждый поток вставки идёт напролом к последней странице, все пытаются взять эксклюзивную защёлку одновременно. Один её получает, остальные либо агрессивно крутятся, либо выстраиваются, накапливая ожидания PAGELATCH_EX. ЦП тратится впустую, затраченное время растёт.

С включённым OPTIMIZE_FOR_SEQUENTIAL_KEY SQL Server незримо становится регулировщиком. Замечая конкуренцию, он начинает разводить запросы на latch. Внутренне он применяет разумный «бэк-офф»: «Ты — сейчас, ты — подожди микросекунду, ты — немного дольше». Вместо 50 сессий, одновременно таранящих одну дверь, они выстраиваются аккуратнее.

И волшебство в том, что никаких изменений схемы, переписываний приложений или хитрых трюков не нужно. Это работает на уровне индекса (не всего сервера), так что вы можете включать опцию только там, где действительно есть проблема. Можно даже проверить, включена ли она:

SELECT name, optimize_for_sequential_key  
FROM sys.indexes  
WHERE object_id = OBJECT_ID('dbo.SalesOrders');

Под капотом вы не «убираете» конкуренцию — вы учите SQL Server управлять конкуренцией немного разумней. Результат — меньше впустую потраченного CPU, более плавная пропускная способность, меньше «штормов» latch.

Моделирование: доказываем узкое место

Построим тестовый стенд. Сначала создадим таблицу с кластерным индексом по последовательному ключу:

DROP TABLE IF EXISTS dbo.TestInserts;
GO
CREATE TABLE dbo.TestInserts (
ID INT IDENTITY(1,1) PRIMARY KEY,
Data CHAR(100)
);

Затем вставим миллион строк, чтобы «набить» структуру B-дерева:

INSERT INTO dbo.TestInserts (Data)
SELECT TOP 1000000 REPLICATE('A', 100)
FROM sys.all_objects a, sys.all_objects b;

Вместо десятков окон SSMS я воспользовался PowerShell, чтобы запустить 50 параллельных сессий. Каждая сессия выполняет цикл из 20 000 вставок и фиксирует своё время. Я запускал скрипт по нескольку раз с OPTIMIZE_FOR_SEQUENTIAL_KEY в режиме ON и OFF — и каждый раз статистика ожиданий хорошо коррелировала с настройкой.

$connectionString = "Server=localhost;Database=simple_talk;Integrated Security=True;"
$query = @"
SET NOCOUNT ON;
DECLARE @i INT = 0;
WHILE @i < 20000
BEGIN
    INSERT INTO dbo.TestInserts (Data) VALUES (REPLICATE('B',100));
    SET @i += 1;
END
"@
$totalSw = [System.Diagnostics.Stopwatch]::StartNew()
$jobs = @()
for ($i=1; $i -le 200; $i++) {
    $jobs += Start-Job -ScriptBlock {
        param($conn,$q,$n)
        $sw = [System.Diagnostics.Stopwatch]::StartNew()
        $sqlConn = New-Object System.Data.SqlClient.SqlConnection $conn
        $sqlConn.Open()
        $cmd = $sqlConn.CreateCommand()
        $cmd.CommandTimeout = 0
        $cmd.CommandText = $q
        $cmd.ExecuteNonQuery() | Out-Null
        $sqlConn.Close()
        $sw.Stop()
        [PSCustomObject]@{
            Session   = $n
            ElapsedMs = $sw.ElapsedMilliseconds
        }
    } -ArgumentList $connectionString,$query,$i
}
$results = $jobs | Receive-Job -Wait -AutoRemoveJob
$totalSw.Stop()
$results | Sort-Object Session | Format-Table -AutoSize
Write-Host "Total runtime: $($totalSw.Elapsed.ToString())"

Так вы получаете как времена выполнения по сессиям, так и суммарные.

В наших испытаниях итоговое время оказалось очень близким независимо от того, был ли OPTIMIZE_FOR_SEQUENTIAL_KEY включён или выключен. Разница составила порядка 10 секунд на весь прогон — не драматично. Но статистика ожиданий показала иное: при выключенной опции ожидания на latch росли почти линейно со временем — «сырой» конфликт. При включённой — мы увидели примерно ~10 секунд дополнительного суммарного ожидания, но это не «медленнее» — это намеренные микро-задержки, которыми SQL Server разводил вставки. Выгода в том, что конкуренция сглаживается, потоки не зацикливаются, CPU менее нагружен, даже если временное окно всех вставок почти не меняется.

Когда OFSK был OFF, общее время для всех 200 сессий: 00:02:17.4372205
Когда OFSK был ON, общее время для всех 200 сессий: 00:02:05.9792700

Мы можем отслеживать типы ожиданий PAGELATCH, запуская запрос во время обоих прогонов — когда OFSK выключен и включён:

SELECT *
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGELATCH_%';

Когда OFSK был OFF —>

Теперь включим оптимизацию, активировав OPTIMIZE_FOR_SEQUENTIAL_KEY (OFSK) для индекса:

ALTER INDEX [PK_TestInserts] ON dbo.TestInserts
SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);

Запустите тест снова — тот же сценарий, те же данные, то же железо — но уже с «throttling».

Когда OPTIMIZE_FOR_SEQUENTIAL_KEY был OFF, мы наблюдали около 904 тыс. ожиданий PAGELATCH_EX. Суммарное время ожидания накопилось примерно до 354 тыс. мс, а самая длинная одиночная задержка достигала 66 мс. Это классическая картина конкуренции за latch: море сессий бьют в одну последнюю страницу, выстраиваются друг за другом, а некоторым «не везёт» ждать дольше остальных. Задача завершается, но неровно — отдельные сессии залипают сильнее, чем должны.

Когда мы включили опцию, число ожиданий немного снизилось — до ~877 тыс. Гораздо важнее, что суммарное время ожиданий тоже уменьшилось — до ~206 тыс. мс, а максимальная одиночная задержка сократилась до 33 мс. Что происходит? SQL Server перестаёт позволять потокам драться одновременно. Он вводит микро-паузы, которые распределяют вставки равномернее. Поэтому вы видите не просто меньше ожиданий, но и более гладкую пропускную способность: сессии больше не скапливаются так густо у одной «горячей» страницы.

Сравнение очевидно: OFF = больше ожиданий, длиннее залипания, неровность. ON = меньше ожиданий, короче залипания, ровнее поток. Реальное суммарное время всей партии может выглядеть похоже, но «под капотом» режим ON обеспечивает более спокойную и справедливую работу для каждой сессии.

Практические сценарии

Эта возможность предназначена для:

• OLTP-систем с интенсивными вставками;
• таблиц с кластерным индексом по IDENTITY;
• систем с высокой конкуренцией;
• конвейеров журналирования, аудита, телеметрии;
• витрин данных с шаблонами вставок в таблицы фактов.

Чего это не исправляет

Настройка не поможет, если:

• у вас нет конкурентных вставок;
• ключи уже случайны (GUID, хеш);
• состязание происходит на не листовых уровнях;
• узкое место — диск (I/O), а не latch.

Кроме того, это не «ускоритель» во всех случаях — помогает именно тогда, когда корень проблемы — latch из‑за последовательных вставок.

Может ли навредить?

Хотя документация и испытания выглядят обнадёживающе, это не «серебряная пуля». В некоторых случаях средства профилирования нагрузки могут показать слегка возросшую задержку отдельной вставки — потому что SQL Server добавляет преднамеренные микрозадержки для сглаживания конкуренции. Но в целом система становится стабильнее и лучше масштабируется. См. соответствующий материал в блоге Microsoft TechCommunity: Behind the Scenes on OPTIMIZE_FOR_SEQUENTIAL_KEY

Итоги

Лучшие возможности производительности — те, о которых не нужно думать постоянно. OPTIMIZE_FOR_SEQUENTIAL_KEY — из их числа: вы включаете опцию, и SQL Server становится разумнее в вопросах конкуренции.

Для таблиц, страдающих от проблемы вставок в последнюю страницу, эта простая опция индекса способна сберечь вам часы боли, переписываний кода и «разговоров о масштабировании». Это точечное, адресное и неинвазивное средство — каким и должна быть умеренная доработка.

Если вы работаете на SQL Server 2019 или новее и ещё не проверили «горячие» OLTP-таблицы… возможно, уже можно смело использовать этот переключатель.