Поделиться через

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

  • Статья

Эта статья поможет устранить проблему, из-за которой вы заметили серьезную блокировку при возникновении тяжелой нагрузки сервера.

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

Симптомы

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

Примечание.

Если страница равномерно делится на 8088, это страница PFS. Например, страница 2:3:905856 — это PFS в file_id=3 в tempdb.

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

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

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

Причина

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

Во время создания объекта два (2) страницы должны быть выделены из смешанной экстенты и назначены новому объекту. Одна страница предназначена для карты распределения индекса (IAM), а вторая — для первой страницы объекта. SQL Server отслеживает смешанные экстенты с помощью страницы "Общая глобальная карта выделения" (SGAM). Каждая страница SGAM отслеживает около 4 гигабайт данных.

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

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

Примечание.

Действия по отмене выделения также должны изменять страницы. Это может способствовать увеличению состязания.

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

Решение

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

    Отзыв

    Дополнительные сведения об этих рекомендациях и других изменениях, представленных в обзоре SQL 2016

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

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

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

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

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

      Примечание.

Увеличьте количество файлов данных tempdb, имеющих равный размер

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

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

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

Увеличение числа файлов данных tempdb сокращает количество разных файлов данных

В следующем списке объясняется, как увеличивается число файлов данных tempdb с равным изменением размера.

  • Если у вас есть один файл данных для tempdb, у вас есть только одна страница GAM и одна страница SGAM для каждого 4 ГБ пространства.

  • Увеличение числа файлов данных с одинаковыми размерами для tempdb эффективно создает одну или несколько страниц GAM и SGAM для каждого файла данных.

  • Алгоритм распределения для GAM выделяет один экстент за раз (восемь смежных страниц) от количества файлов в круглой робе, учитывая пропорциональную заливку. Таким образом, если у вас есть 10 файлов одинакового размера, первое выделение — из File1, второй из File2, третий из File3 и т. д.

  • Состязание ресурсов страницы PFS уменьшается, так как восемь страниц за раз помечаются как FULL, так как GAM выделяет страницы.

Как реализация флага трассировки -T1118 снижает результаты.

Примечание.

Этот раздел относится только к SQL Server 2014 и более ранним версиям.

В следующем списке объясняется, как с помощью флага трассировки -T1118 уменьшается количество спорных действий:

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

Недостатки

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

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

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

Ссылки