Новое в SQL Server 2022: tempdb Contention Enhancements with Page Latch Concurrency

https://www.mssqltips.com/sqlservertip/8025/sql-server-2022-tempdb-contention-enhancements/

Автор Simon Liew

Tempdb используется в качестве промежуточного хранилища и репозитория метаданных, и все эти объекты в SQL Server могут быть источником высокой нагрузки, а также приводить к конфликтам и конкуренции в критически-важных для работы сервера местах. Проявление этих негативных факторов возможно при использовании временных таблиц, триггеров, промежуточной материализации данных для спулов, курсоров, сортировок, хэш-соединений и больших объектов (LOB), и это лишь наиболее очевидные источники проблем. Ещё одной распространённой проблемой является конкуренция за страницы системных объектов Global Allocation Map (GAM) и Shared Global Allocation Map (SGAM), это особенно актуально для некоторых видов нагрузки с высоким уровнем параллелизма.

SQL Server 2022 привнёс множество впечатляющих новых функций, некоторые из которых более широко известны, чем другие. Одно из менее известных новшеств — улучшение параллелизма блокировки системных страниц.

В SQL Server 2022 страницы GAM практически лишены кратких блокировок (latch-free), и это меняет правила игры для тех типов рабочей нагрузки, где это порождало проблемы и с которыми было нелегко справиться. Однако эта функция недоступна в более ранних версиях SQL Server, поэтому для получения выгоды от этого улучшения необходимо выполнить обновление версии.

Демонстрация

Давайте посмотрим на результаты стресс-тестов на SQL Server 2019, рабочую нагрузку для которых мы получали с помощью «ostress.exe», и потом тот же тест был повторен на SQL Server 2022. Для обоих версий собрались метрики, связанные с краткими блокировками.

Конфигурация файла tempdb

Ниже на Рисунке 1 показаны свойства файлов базы данных tempdb и её журнала транзакций, они были одинаковыми как для SQL Server 2019, так и для SQL Server 2022. Там есть только один файл данных, что не соответствует лучшим практикам Microsoft.

Рисунок 1. Файлы tempdb

Настройка стресс-теста рабочей нагрузки

Приведенный ниже сценарий создаёт базу данных и хранимую процедуру, которая выполняется с высоким параллелизмом и воспроизводит конфликт tempdb на странице GAM. В данном случае нас не беспокоит возможная конкуренция на страницах SGAM, поскольку, начиная с SQL Server 2016 распределение страниц GAM включено по умолчанию.

CREATE DATABASE TestGamContention

GO

USE TestGamContention

GO

CREATE OR ALTER PROCEDURE dbo.GenerateGamContention

AS

BEGIN

    SET NOCOUNT ON;

 

    exec sp_executesql N'exec sp_executesql N'

    CREATE TABLE #TestGamContention

    (TransactionTimestamp DATETIME2, Quantity tinyint);

 

    INSERT INTO #TestGamContention (TransactionTimestamp, Quantity)

    SELECT GETDATE() AS TransactionTimestamp, 1 AS Quantity'

END

GO

Стресс-тест

Команда ниже запускается из командной строки и создает 100 потоков, повторяя рабочую нагрузку 300 раз.

Ostress.exe -Slocalhost -E -Q"exec dbo.GenerateGamContention" -n100 -r300 -dTestGamContention

Сценарий мониторинга

Скрипт ниже показывает ожидания, типы которых связанны с краткими блокировками страниц. В столбце ResourceType показан тип страницы — PFS, GAM или SGAM.

; WITH waits AS (

        SELECT 

            os.session_id,

            os.wait_type,

            os.wait_duration_ms,

            os.blocking_session_id,

            os.resource_description,

            CONVERT(INT, REPLACE(PARSENAME(REPLACE(os.resource_description, ':', '.'), 1), '.', ':')) PageId,

            ExecutingSQL =

            SUBSTRING(t.text,(r.statement_start_offset/2)+1,

                CASE

                    WHEN r.statement_end_offset=-1 OR r.statement_end_offset=0 THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1

                    ELSE (r.statement_end_offset-r.statement_start_offset)/2+1

                    END)

        FROM sys.dm_os_waiting_tasks os

        JOIN sys.dm_exec_requests r ON os.session_id = r.session_id

        CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t

        WHERE os.wait_type LIKE 'PAGE%LATCH_%'

        AND os.resource_description LIKE '2:%'

            )

SELECT

   w.session_id,

   w.wait_type,

   w.wait_duration_ms,

   w.blocking_session_id,

   w.resource_description,

   w.PageId,

   ResourceType =

   CASE

       WHEN w.PageID = 1 OR w.PageID % 8088 = 0 THEN 'PFS Page'

       WHEN w.PageID = 2 OR w.PageID % 511232 = 0 THEN 'GAM Page'

       WHEN w.PageID = 3 OR (w.PageID - 1) % 511232 = 0 THEN 'SGAM Page'

       ELSE 'Not PFS, GAM, SGAM page'

   END,

   w.PageId,

   ResourceObject = object_name(pg.object_id),

   w.ExecutingSQL

FROM waits w

OUTER APPLY sys.dm_db_page_info (2, 1, w.PageId, DEFAULT) pg;

Стресс-тест на SQL Server 2019

Если стресс-тест выполняется без включенной функции оптимизации tempdb в памяти (in-memory tempdb optimization), первым узким местом может стать конфликт на метаданных в tempdb. На моем ноутбуке с процессором i9-10885H с частотой 2,40 ГГц, 8 ядрами, 16 логическими процессорами, дисками NVMe и памятью 64 ГБ для полного завершения итераций этой рабочей нагрузки потребовалось ~13 секунд.

Конфликт происходит на sysallocunits.

Мы не будем останавливаться на конфликте метаданных, поскольку основное внимание тут уделяется конфликту GAM. Давайте для этого оптимизируем работу с tempdb, включив функцию in-memory tempdb optimization. Для этого выполним приведенную ниже команду, а затем перезапустим службу SQL Server, чтобы изменения вступили в силу.

USE master

GO

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

GO

Перед повторным запуском рабочей нагрузки очистим всю статистику ожиданий на сервере:

DBCC SQLPERF('sys.dm_os_wait_stats',CLEAR);

Конфликт кратких блокировок на странице GAM tempdb

Если теперь запустить эту рабочую нагрузку ещё раз, мы увидим конфликты на странице GAM. Важно отметить, что в SQL Server 2019 включение memory-optimize для tempdb не решает проблему конкуренции за GAM.

Следуя рекомендациям Microsoft, у вас может возникнуть соблазн увеличить число файлов данных у tempdb того же размера, но это помогает только до того момента, пока не будет устранена основная причина конкуренции.

Команда Ostress.exe выполняется три раза.

Ниже, на снимке экрана perfmon показаны интересующие нас метрики, и видно, что рабочая нагрузка запускалась три раза. Каждая запускаемая последовательно итерация рабочей нагрузки завершается примерно за 10 секунд. Самое большое из продемонстрированных значений пропускной способности Batch Requests/sec составляет ~ 3100 запросов в секунду.

При мониторинге в режиме реального времени у конфликта GAM тип ожидания будет PAGELATCH_UP. Но согласно sys.dm_os_wait_stats, наиболее распространенным типом ожидания оказывается PAGELATCH_EX. Эти два типа ожидания связаны следующим образом:

• PAGELATCH_UP: несколько потоков пытаются изменить одну и ту же битовую маску.
• PAGELATCH_EX: у потоков, которые пытаются сделать вставку на эту же страницу, и в то же время PAGELATCH_SH у потоков, пытающихся прочитать страницу.

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

SELECT *, (wait_time_ms + signal_wait_time_ms) / 1000 total_wait_time_sec

FROM sys.dm_os_wait_stats

WHERE wait_type LIKE '%LATCH%'

ORDER BY waiting_tasks_count DESC

GO

Стресс-тест на SQL Server 2022

На том же оборудовании и с той же конфигурацией в SQL Server 2022 мы должны сначала включить для метаданных функцию memory-optimized tempdb, иначе конфликт метаданных станет первым узким местом, с которым мы столкнемся.

Одна и та же рабочая нагрузка повторяется три раза, и каждая итерация завершается примерно за 7 секунд.

Вы можете подумать, что это не такое уж впечатляющее улучшение производительности сравнивая продолжительности выполнения с тем, что было проделано выше, но многие значения метрик производительности стали лучше. Batch Requests/sec может достигать 4800 запросов в секунду, что примерно на 55% лучше, чем было у SQL Server 2019 (максимум было в 3100 запросов в секунду).

Если посмотреть на типы ожиданий, которые в это время фиксировал perfmon, то они связаны с некоторым ожиданием на структурах в памяти и при изменении страниц на диске.

Устранение конкуренции за краткие блокировки в tempdb

Если присмотреться к хранимой процедуре, которая генерирует конфликт GAM в tempdb, следует отметить использование в коде sp_executesql.

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

Для SQL Server 2019 лучше переписать хранимую процедуру, удалив динамический запрос, что позволит снизить время выполнения нашей нагрузки до 3-х секунд.

USE TestGamContention

GO

CREATE OR ALTER PROCEDURE dbo.AlleviateGamContention

AS

BEGIN

    SET NOCOUNT ON;

 

    CREATE TABLE #AlleviateGamContention

    (TransactionTimestamp DATETIME2, Quantity tinyint);

 

    INSERT INTO #AlleviateGamContention (TransactionTimestamp, Quantity)

    SELECT GETDATE() AS TransactionTimestamp, 1 AS Quantity

END

GO

Конфликты в GAM по-прежнему будут, но они намного лучше по сравнению с теми, что получались при использовании исходной версии кода процедуры.

Ещё меньше продолжительность обслуживания рабочей нагрузки будет в SQL Server 2022, там каждая итерация завершается примерно за 1 секунду.

 Заключение

Включение Memory-optimized для метаданных в tempdb помогает устранить только конфликты метаданных. Конкуренция за такие системные страницы, как GAM и SGAM практически исчезает только в SQL Server 2022.

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