Рекомендации по сокращению состязания за выделение в базе данных 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), используйте то же количество файлов данных, что и логические процессоры. Если число логических процессоров больше восьми (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 сокращается, так как восемь страниц одновременно помечаются как ПОЛНЫе, так как GAM выделяет страницы.

Как реализация флага трассировки -T1118 снижает количество состязаний

Примечание.

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

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

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

Недостатки

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

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

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

Ссылки