Рекомендации по уменьшению содержимого выделения в базе SQL Server tempdb

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

Исходная версия продукта:   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.
  • Work files that are associated with 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 и более поздних версий:

    Проверка

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

    • TEMPDB — файлы и флаги трассировки и обновления, Куа Мой!

    • Применив соответствующий cu для SQL Server 2016 и 2017, чтобы воспользоваться преимуществами следующего обновления. В 2016 и SQL Server 2017 было усовершенствовано решение, которое еще больше сокращает количество SQL Server 2017. В дополнение к выделению с помощью кругового перерисовки для всех файлов данных tempdb это исправление улучшает выделение страниц PFS, выполняя выделение с помощью кругового перерисовки на нескольких страницах PFS в одном файле данных. Дополнительные сведения см. в 2014, 2016 и 2017 годах в KB4099472 — усовершенствование алгоритма кругового переорфирования страницы PFS в SQL Server 2014, 2016 и 2017годах.

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

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

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

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

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

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

      Примечание

      • Этот флаг трассировки влияет на каждую базу данных в экземпляре SQL Server. Сведения о том, как определить, находится ли разлиение выделения на страницах SGAM, см. в мониторинге, вызванном операциями DML.

      • В SQL Server 2014 убедитесь, что вы применяли Пакет обновления 3, чтобы воспользоваться исправлением, задокументированным в следующей статье КБ. Это улучшение еще больше сокращает количество SQL Server 2014. В дополнение к выделению с помощью кругового перерисовки для всех файлов данных tempdb это исправление улучшает выделение страниц PFS, выполняя выделение с помощью кругового перерисовки на нескольких страницах PFS в одном файле данных.

        KB4099472 — усовершенствование алгоритма кругового переорфирования страницы PFS в SQL Server 2014, 2016 и 2017 гг.

      • Блог группы msSQL: файлы и флаги трассировки и обновления в SQL Server tempdb

Увеличение числа файлов данных 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) страниц объекта без флага трассировки.
  • Страницы IAM по-прежнему используют выделение одной страницы со страницы SGAM, даже если -T1118 включен. Однако при совместном с файлами данных tempdb и 8.00.0702 и увеличенных файлах данных tempdb, в результате на странице SGAM происходит уменьшение числа разных точеных файлов. Вопросы, которые касается пространства, см. в следующем разделе.

Недостатки

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

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

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

Ссылки