Рекомендации по сокращению состязания выделения ресурсов в базе данных tempdb SQL Server.

Исходная версия продукта: SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, SQL Server 2022
Исходный номер базы знаний: 2154845

Сводка

В этой статье объясняется, как диагностировать и уменьшить количество конфликтов выделения в базе данных SQL Server tempdb. В ней рассматриваются PAGELATCH_UP ожидания на страницах "Свободное пространство страницы" (PFS), "Глобальная карта выделения" (GAM) и страницы "Общая глобальная карта выделения" (SGAM). В нем описывается использование нескольких файлов данных с равным размером tempdb и в более старых версиях флаги трассировки 1118 и 1117 для повышения производительности SQL Server при тяжелых параллельных рабочих нагрузках.

Симптомы

На сервере, на котором выполняется Microsoft SQL Server, вы заметите серьезную блокировку, когда сервер находится под тяжелой нагрузкой. Динамические административные представления sys.dm_exec_requests и sys.dm_os_waiting_tasks показывают, что запросы или задачи ожидают tempdb ресурсов. Тип ожидания — PAGELATCH_UP, а ресурс ожидания указывает на страницы в tempdb. Эти страницы могут находиться в формате 2:1:1, 2:1:3 и т. д. (страницы PFS и SGAM в tempdb).

Следующие операции широко используют tempdb:

• Повторяющиеся операции создания и удаления временных таблиц (локальные или глобальные).
• Табличные переменные, использующие tempdb для хранения.
• Рабочие таблицы, связанные с курсорами.
• Рабочие таблицы, связанные с выражением ORDER BY.
• Рабочие таблицы, связанные с условием GROUP BY.
• Рабочие файлы, связанные с хэш-планами.

Эти действия могут вызвать спор.

Причина

Если tempdb интенсивно используется, SQL Server может испытывать конкуренцию при выделении страниц. В зависимости от уровня конкуренции за ресурсы запросы и обращения, использующие tempdb, могут на короткое время перестать отвечать.

При создании объекта в ранних версиях SQL Server система выделяет две страницы из смешанного экстента и закрепляет их за новым объектом. Одна страница — это страница карты распределения индекса (IAM), а вторая — первая страница для объекта. SQL Server отслеживает смешанные экстенты с помощью страницы SGAM (Shared Global Allocation Map). Каждая страница SGAM отслеживает около 4 ГБ данных.

Чтобы выделить страницу из смешанного экстента, SQL Server сканирует страницу Page Free Space (PFS), чтобы найти свободную страницу в смешанном экстенте. Страница PFS отслеживает свободное место на каждой странице, и каждая страница PFS отслеживает около 8000 страниц. Синхронизация требуется, если SQL Server изменяет страницы PFS и SGAM, а синхронизация может остановить другие модификаторы в течение коротких периодов.

Когда SQL Server ищет смешанную страницу для выделения, он всегда начинает сканирование в одном и том же файле и на одной и той же странице SGAM. Это поведение приводит к сильной конкуренции за доступ к странице SGAM, когда одновременно происходит много операций выделения смешанных страниц, что вызывает описанные ранее симптомы.

Дополнительные сведения о механизмах выделения, которые SQL Server используют (SGAM, GAM, PFS и IAM), см. в разделе Связанное содержимое.

Решение

Период расширенной поддержки SQL Server 2014 завершился 9 июля 2024 г., поэтому большинству читателей следует руководствоваться рекомендациями для SQL Server 2016 и более поздних версий.

SQL Server 2016 и более поздних версий

1. Ознакомьтесь с базой данных tempdb для получения текущих рекомендаций по производительности.
2. Убедитесь, что tempdb имеет несколько файлов данных одинакового размера. В качестве отправной точки используйте один файл данных для каждого логического процессора до восьми файлов. Если конкуренция за ресурсы сохраняется, добавляйте файлы группами по четыре, пока их число не достигнет количества логических процессоров.
3. Сохраняйте текущий экземпляр SQL Server с последним накопительным обновлением, чтобы получить дальнейшие улучшения выделения. Например, см. статью KB 4099472: улучшение алгоритма циклического перебора страниц PFS в SQL Server 2014, 2016 и 2017.
4. Если конкуренция сохраняется в SQL Server 2019 и более поздних версиях, ознакомьтесь с рекомендациями по базе данных tempdb, касающимися параллельных обновлений PFS и метаданных tempdb, оптимизированных для памяти.

SQL Server 2014 и более ранних версий

Чтобы повысить параллельность tempdb, попробуйте следующие способы:

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

• Ознакомьтесь с рекомендациями по передовым методам в Работа с tempdb в SQL Server 2005.

• Если предыдущие шаги не приводят к существенному снижению конкуренции за выделение памяти и конкуренция наблюдается на страницах SGAM, включите флаг трассировки -T1118. При использовании этого флага трассировки SQL Server выделяет полные экстенты для каждого объекта базы данных, что значительно снижает конкуренцию за страницы SGAM.

  Примечание.

  • Этот флаг трассировки влияет на каждую базу данных в экземпляре SQL Server. Чтобы проверить, возникает ли конкуренция при выделении на страницах SGAM, см. Мониторинг конкуренции, вызванной операциями DML.
  • Для SQL Server 2014 установите Service Pack 3, чтобы получить исправление, описанное в KB 4099472, которое дополнительно снижает конкуренцию за счет циклического выделения по страницам PFS между несколькими страницами PFS в одном и том же файле данных.

Дополнительные сведения о выделении tempdb

Поведение tempdb по умолчанию в SQL Server версии 2016 и более поздних версий

Начиная с SQL Server 2016, по умолчанию реализовано несколько улучшений, снижающих конкуренцию при выделении tempdb, поэтому обычно нет необходимости включать флаги трассировки 1118 или 1117:

• Выделения смешанной степени отключены по tempdb умолчанию (поведение, ранее включенное флагом трассировки 1118). Все новые выделения исходят из единообразных экстентов.
• Все tempdb файлы данных автоматически увеличиваются одновременно (поведение, ранее включаемое флагом трассировки 1117).
• Программа установки SQL Server рекомендует использовать несколько файлов данных tempdbодинакового размера в зависимости от количества логических процессоров, но не более восьми.
• Параллельные обновления PFS позволяют дополнительно снизить конкуренцию за защёлки страниц PFS в SQL Server 2019 и более поздних версиях.

Если вы запускаете SQL Server 2016 или более поздней версии, сосредоточьтесь на размерах и количестве файлов tempdb. Используйте флаги трассировки только в SQL Server 2014 или более ранней версии.

Пример равного размера файла данных tempdb

Например, если один tempdb файл данных равен 8 ГБ, а файл журнала составляет 2 ГБ, увеличьте количество файлов данных до восьми (каждый 1 ГБ, чтобы сохранить равный размер) и оставьте файл журнала неизменным. Размещение файлов данных на отдельных дисках может дать дополнительное преимущество производительности, но это не обязательно. Файлы могут сосуществовать на одном томе диска.

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

Используйте равный размер для всех файлов данных. В SQL Server 2000 с пакетом обновления 4 (SP4) было внесено исправление, использующее алгоритм циклического выбора для выделения смешанных страниц. Благодаря этому улучшению начальный файл изменяется для каждого последовательного распределения смешанной страницы при наличии нескольких файлов. Алгоритм распределения SGAM работает строго по принципу round-robin и не поддерживает пропорциональное заполнение, поэтому создайте все tempdb файлы данных одинакового размера.

Как увеличение числа файлов данных tempdb снижает конкуренцию

Добавление файлов данных с равным размером tempdb уменьшает количество спорных действий следующими способами:

• Если у вас есть один файл данных для tempdb, то на каждые 4 ГБ пространства приходится одна страница GAM и одна страница SGAM.
• Дополнительные файлы данных одного и того же размера эффективно создают одну или несколько страниц GAM и SGAM для каждого файла данных.
• Алгоритм распределения GAM выделяет один экстент за раз (восемь смежных страниц) из файлов в режиме циклического перебора при пропорциональном заполнении. Например, если у вас есть 10 файлов одинакового размера, первое выделение — из File1, второй из File2, третий из File3 и т. д.
• Конфликты доступа к страницам PFS уменьшаются, поскольку при выделении страниц модулем GAM сразу по восемь страниц помечаются как FULL.

Как флаг трассировки -T1118 уменьшает конкуренцию за ресурсы

Флаг трассировки -T1118 снижает конкуренцию за ресурсы следующими способами:

• -T1118 — это параметр на уровне сервера.
• Добавьте -T1118 в параметры запуска SQL Server, чтобы флаг трассировки оставался в силе после перезагрузки SQL Server.
• -T1118 устраняет почти все одностраничные выделения памяти на сервере.
• Отключение большинства выделений отдельных страниц уменьшает конкуренцию за страницы SGAM.
• При -T1118 включении почти все новые выделения приходят с страницы GAM (например, 2:1:2), которая выделяет восемь страниц (один экстент) одновременно объекту вместо отдельных страниц из экстентов для первых восьми страниц объекта.
• Страницы IAM по-прежнему используют одностраничные выделения из страницы SGAM, даже если включён -T1118. Но в сочетании с исправлением 8.00.0702 и большим количеством tempdb файлов данных общий эффект заключается в снижении конкуренции за страницы SGAM. Сведения о проблемах с пространством см. в следующем разделе.

Флаг трассировки 1118 в сравнении с поведением по умолчанию в современных версиях SQL Server

Недостатки флага трассировки -T1118

Недостатком использования -T1118 является то, что размер базы данных может увеличиваться, если оба из следующих условий являются истинными:

• Вы создаете новые объекты в пользовательской базе данных.
• Каждый новый объект занимает менее 64 КБ хранилища.

В этих условиях SQL Server может выделить 64 КБ (восемь страниц * 8 КБ) для объекта, который требует только 8 КБ, который тратит 56 КБ хранилища. Если новый объект за время своего существования использует более 64 КБ (восемь страниц), то использование флага трассировки не имеет недостатков. В худшем случае SQL Server при первом выделении выделяет семь дополнительных страниц, но только для новых объектов, размер которых никогда не превышает одной страницы.

Связанный контент

• База данных tempdb
• DBCC TRACEON: флаги трассировки (Transact-SQL)