Увеличить количество tempdb файлов

Обновлено: 06.07.2024

Системная база данных tempdb активно используется базами данных 1С:Предприятие 8.3 для хранения временных таблиц, промежуточных расчетов, версий строк при использовании режима версионирования и прочих временных данных. То есть для задач 1С:Предприятие интенсивность обращений к базе tempdb находится на высоком уровне, поэтому нужно подумать о размещении этой базы на выделенном быстром дисковом устройстве.Подходящими кандидатами на роль диска под tempdb будут выделенные быстрые дисковые RAID-группы уровня RAID1, выделенные накопители SSD или вообще RAM-диск.

В большинстве сценариев рекомендуется разбивать базу tempdb на несколько файлов данных с одинаковом начальным размером (Initial size) от 1GB и больше и увеличенным показателем прироста, например, в 512MB.

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

Относительно 1С:Предприятие 8.3 можно встретить рекомендацию о том, что общий размер Initial size для всех файлов БД tempdb должен быть от 25% до 40% от размера рабочей БД 1С:Предприятие.

Саму процедуру переноса файлов tempdb на другой диск мы рассматривали ранее в заметке SQL Server 2008 - Перенос файлов БД tempdb на отдельный диск. Эта процедура может использоваться и на новых версиях СУБД, вплоть до SQL Server 2016.

Рассмотрим частный пример распределения файлов tempdb по разным дисковым томам, имеющим разные показатели производительности. В нашем примере имеется два тома NTFS:

R: менее производительный, но больший по объёму диск

Часть файлов данных в файловой группе tempdb, а также файл лога размещены на быстром диске. Файлы данных, расположенные на быстром диске установлены фиксированного размера без возможности авторасширения (исключением здесь является только файл лога). Другая часть файлов данных размещена на менее производительном дисковом томе, но при этом для файлов включено авторасширение.

В результате такой конфигурации, файлы tempdb в нашем случае будут распределены по дисковым томам следующим образом: 4 файла данных и лог tempdb окажутся на быстром томе T:

Другие 4 файла данных tempdb окажутся на менее производительном дисковом томе R:

В случае если в ходе работы экземпляра SQL Server потребуется дополнительное расширение ёмкости tempdb, то файлы начнут прирастать на меньшем по производительности, но большем по объёму дисковом томе R.

К операциям, которые могут вызвать бурный рост tempdb при работе БД 1С:Предприятие 8.3 можно отнести, например, регламентные процедуры с конфигурацией 1С, выполняемые из конфигуратора (обновление конфигурации, перерасчёт итогов и т.п.). Кроме того, активный рост tempdb может вызвать построение в 1С каких-то тяжёлых отчётов с большим количеством данных и за длительные периоды при условии, что код отчётов неоптимален или вообще содержит ошибки. В практической среде при размере БД около 170GB во время построения подобного отчёта мы наблюдали рост tempdb до 350GB. Учитывая эти моменты стоит подумать о полной изоляции файлов tempdb на выделенных дисковых томах, чтобы их возможный бурный рост не смог нарушить функционирования других БД SQL Server.

Используемый в нашем примере дисковый том T: представляет собой RAM-диск, подключенный к серверу SQL Server по методике, описанной в отдельной статье нашего Блога : Организуем RAM-диск для кластера Windows Server с помощью Linux-IO FC Target

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

Оригинальная версия продукта: 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 необходимо просмотреть страницу Page Free Space (PFS), чтобы определить, какая смешанная страница может быть выделена бесплатно. На странице PFS отслеживается свободное пространство, доступное на каждой странице, и каждая страница PFS отслеживает около 8000 страниц. Для внесения изменений на страницы PFS и SGAM поддерживается соответствующая синхронизация; и это может привести к срыву других модификаторов в течение коротких периодов времени.

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

Кроме того, необходимо изменить страницы. Это может способствовать увеличению содержимого.

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

Решение

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

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

Применяйте соответствующий ТС для SQL Server 2016 и 2017 годов, чтобы воспользоваться следующим обновлением. В 2016 и SQL Server 2017 года было сделано дополнительное снижение SQL Server. В дополнение к распределению круговой развязки во всех файлах данных tempdb исправление улучшает распределение страниц PFS путем выполнения распределений кругового робина на нескольких страницах PFS в одном файле данных. Дополнительные сведения см. в странице KB4099472 - усовершенствование алгоритма кругового робина страницы PFS в 2014, 2016 и 2017 годах 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, чтобы воспользоваться исправлением, задокументированным в следующей статье KB. Дальнейшее улучшение снижает уровень содержимого в SQL Server 2014 г. В дополнение к распределению круговой развязки во всех файлах данных tempdb исправление улучшает распределение страниц PFS путем выполнения распределений кругового робина на нескольких страницах PFS в одном файле данных.

Блог команды тигра MSSQL: файлы и флаги трассировки и обновления в SQL Server tempdb

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

Например, если размер одного файла данных tempdb составляет 8 ГБ, а размер файла журнала — 2 ГБ, необходимо увеличить количество файлов данных до восьми (каждый из 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. Однако, когда он сочетается с hotfix 8.00.0702 и увеличенными файлами данных tempdb, чистым эффектом является уменьшение раздора на странице SGAM. Что касается пространства, см. в следующем разделе.

Недостатки

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

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

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

Хотелось бы продемонстрировать вам небольшую вырезку из статьи и немного дополнить:

Во время установки SQL Server 2016, теперь можно указать размер и количество файлов БД

Предлагаемое количество файлов по-умолчанию можно узнать по следующей таблице:

Ядер CPU	Количество файлов в tempdb
2	2
4	4
8	8
32	8

Улучшения в производительности при работе с tempdb

Кэширование временных объектов позволяет запросам, которые постоянно удаляют и создают временные объекты работать быстрее и уменьшают конкуренцию за системные ресурсы. В последних версиях SQL Server можно было регулярно видеть изменения и улучшения этого механизма.
Уменьшена нагрузка на журнал транзакций в tempdb, снижено количество требуемых I\O операций.
Доработан алгоритм накладывания latch’ей при выделении страниц, уменьшено их количество.
При приращении tempdb теперь одновременно будет увеличен размер всех файлов (отпадает необходимость включать флаг трассировки 1117). Опция AUTOGROW_ALL_FILES включена по умолчанию и не может быть изменена. Это поможет избежать разбалансирования размеров файлов при постоянно приросте tempdb.
Для временных объектов идет выделение только экстентами (блоками по 8 страниц, 64 кб). Отпадает необходимость включать флаг трассировки 1118. Это также поможет в большей части случаев.

Дополнение:

Давайте рассмотрим так же как можно узнать есть ли у вас проблемы с количеством файлов tempdb. Для этого достаточно собирать данные следующего запроса:

Несомненно, правильная или, наоборот, неправильная настройка tempdb может очень сильно повлиять на производительность SQL Server. Эта системная база данных является глобальным ресурсом и доступна всем пользователям, которые подключаются к SQL Server, и предназначена, чтобы хранить следующие данные:

Временные объекты, созданные пользователями: временные локальные или глобальные таблицы, табличные переменные, временные процедуры и курсоры.
Внутренние объекты, которые создаются движком SQL Server, например, рабочие таблицы (worktable), которые используются для хранения временных результатов сортировки, а также скрытые буферные таблицы (spool).
Версии строк, которые генерируются при модификации данных в базе, где используются оптимистичные уровни изоляции.
Версии строк, которые генерируются при онлайн перестроении индексов, AFTER триггеров или MARS (Multiple Active Result Sets).

Т.е. база tempdb может активно использовать даже запросами, которые явно не создают никаких временных объектов. Я сейчас не планирую описывать все тонкости конфигурации этой базы, а лишь сосредоточусь на тех изменениях, которые появились в SQL Server 2016.

В первую очередь, уже при установке, на одном из этапов нам предлагают выбрать, сколько файлов и какого размера будет создано для tempdb.

Многие из вас должны быть в курсе, что раньше по умолчанию создавался всего 1 файл данных под tempdb. В ситуациях, когда несколько сессий активно работают с этой системной базой, возникала конкуренция за внутренние ресурсы (contention). Поэтому появилось очень много рекомендаций, сколько же файлов лучше создавать под базу tempdb. По умолчанию инсталлятор руководствуется следующей формулой: минимум из 2х значений, количество ядер на вашей системе и 8. Т.е., если у вас меньше 8 ядер, то будет предложено создать столько файлов, сколько у вас в системе ядер. В остальных случаях будет просто предложено создать 8 файлов, независимо от того, сколько у вас ядер.

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

Далее, что еще бросается в глаза. По умолчанию размер файлов составляет 8 Мб и прирост 64 Мб. Опять же, в большинстве случаев эти цифры необходимо будет увеличить в зависимости от ваших потребностей. Хорошо, на мой взгляд, что в инсталляторе отсутствуют параметры прироста в процентах, что практически всегда является плохой практикой.

Еще стоит отметить, что вы можете указать несколько директорий, в которых будут созданы файлы данных. Файлы будут распределены по этим директориям по алгоритму round-robin. Например, вы указали создать 8 файлов данных и разместить их в 3х директориях. В этом случае установщик расположит их следующим образом:

Улучшения в производительности при работе с tempdb

Также в работу tempdb были внесены следующие изменения, направленные на оптимизацию и ускорение выполнения запросов:

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

Читайте также: