Диагностика конкуренции за tempdb

Автор: Paul Randal, The Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention

Одна из самых распространённых проблем производительности, существующих в экземплярах SQL Server по всему миру, известна как конкуренция за tempdb. Что это означает? Конкуренция за tempdb относится к узкому месту для потоков, пытающихся получить доступ к страницам распределения, находящимся в памяти; это не связано с вводом-выводом.

Рассмотрим сценарий сотен параллельных запросов, которые создают, используют, а затем удаляют небольшие временные таблицы (которые по своей природе всегда хранятся в tempdb). При каждом создании временной таблицы должна быть выделена страница данных, а также страница метаданных распределения для отслеживания страниц данных, выделенных таблице. Это требует внесения пометки на странице распределения (называемой страницей PFS) о том, что эти две страницы были выделены в базе данных. При удалении временной таблицы эти страницы освобождаются, и их снова необходимо пометить как таковые на этой странице PFS. Только один поток за раз может изменять страницу распределения, что делает её «горячей точкой» и замедляет общую рабочую нагрузку.

В SQL Server моя команда разработчиков в Microsoft реализовала небольшой кэш временных таблиц, чтобы попытаться уменьшить эту точку конкуренции, но это лишь небольшой кэш, поэтому очень часто эта конкуренция остаётся проблемой даже сегодня.

Что действительно интересно, так это то, что многие люди не осознают наличия этой проблемы, даже опытные администраторы баз данных. Очень легко определить, есть ли у вас такая проблема, используя DMV sys.dm_os_waiting_tasks. Если вы выполните запрос, который я привёл ниже, вы получите представление о том, где ожидают различные потоки на вашем сервере, как ранее обсуждала Эрин (Erin).

SELECT
    [owt].[session_id],
    [owt].[exec_context_id],
    [owt].[wait_duration_ms],
    [owt].[wait_type],
    [owt].[blocking_session_id],
    [owt].[resource_description],
    CASE [owt].[wait_type]
        WHEN N'CXPACKET' THEN
            RIGHT ([owt].[resource_description],
            CHARINDEX (N'=', REVERSE ([owt].[resource_description])) - 1)
        ELSE NULL
    END AS [Node ID],
    [es].[program_name],
    [est].text,
    [er].[database_id],
    [eqp].[query_plan],
    [er].[cpu_time]
FROM sys.dm_os_waiting_tasks [owt]
INNER JOIN sys.dm_exec_sessions [es] ON
    [owt].[session_id] = [es].[session_id]
INNER JOIN sys.dm_exec_requests [er] ON
    [es].[session_id] = [er].[session_id]
OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]
OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]
WHERE
    [es].[is_user_process] = 1
ORDER BY
    [owt].[session_id],
    [owt].[exec_context_id];
GO

Обратите внимание, что строка [est].text не имеет разделителей — это нарушает работу плагина.

Если вы видите много строк вывода, где wait_type равно PAGELATCH_UP или PAGELATCH_EX, а resource_description равно 2:1:1, то это страница PFS (база данных ID 2 — tempdb, файл ID 1, страница ID 1), а если вы видите 2:1:3, то это другая страница распределения, называемая SGAM.

Есть три вещи, которые вы можете сделать, чтобы уменьшить этот вид конкуренции и увеличить пропускную способность общей рабочей нагрузки:

1. Прекратить использовать временные таблицы
2. Включить трассировочный флаг 1118 как стартовый флаг в старых версиях, где он ещё актуален.
3. Создать несколько файлов данных tempdb

Хорошо, пункт #1 гораздо легче сказать, чем сделать, но он действительно решает эту проблему :-) Серьёзно, вы можете обнаружить, что временные таблицы являются шаблоном проектирования в вашей среде, потому что когда-то они ускорили запрос, и затем все начали их использовать, независимо от того, действительно ли они нужны для повышения производительности и пропускной способности. Это уже совсем другая тема и выходит за рамки этой статьи.

Пункт #2 предотвращает конкуренцию за страницы SGAM, незначительно изменяя используемый алгоритм распределения. Включение этого флага не имеет недостатков, и я даже говорю, что все экземпляры SQL Server в мире должны иметь этот трассировочный флаг включённым по умолчанию (и я говорил то же самое, когда руководил командой разработчиков, отвечающей за код распределения в механизме хранения SQL Server).

Пункт #3 поможет устранить конкуренцию за страницы PFS, распределяя нагрузку распределения по нескольким файлам, тем самым уменьшая конкуренцию за отдельные страницы PFS для каждого файла. Но сколько файлов данных следует создать?

Лучшее руководство, которое я видел, принадлежит моему хорошему другу Бобу Уорду (Bob Ward), который является ведущим инженером эскалации в службе поддержки продуктов Microsoft SQL. Определите количество логических ядер процессора (например, два процессора по 4 физических ядра каждый, с включённой гиперпоточностью = 2 (процессора) × 4 (ядра) × 2 (гиперпоточность) = 16 логических ядер). Затем, если у вас менее 8 логических ядер, создайте столько же файлов данных, сколько логических ядер. Если у вас более 8 логических ядер, создайте 8 файлов данных, а затем добавляйте ещё по 4, если вы всё ещё видите конкуренцию за PFS. Убедитесь, что все файлы данных tempdb имеют одинаковый размер. (Этот совет теперь является официальным руководством Microsoft в статье базы знаний 2154845.)

Это был лишь обзор проблемы, но, как я уже сказал, она очень распространена. Вы можете получить больше информации в статье: Заблуждения вокруг флага трассировки TF1118