Перечень рекомендаций по оптимизации работы TempDB

TempDB, как и другие базы, состоит из первичных и вторичных данных и журнала транзакций.

• первичные данные хранят информацию, необходимую для запуска базы данных, они расположены в файле tempdb.mdf;
• вторичные данные содержат информацию, отсутствующую в первичных данных. Они используются для распределения данных по нескольким файлам для распределения нагрузки. Данные находятся в файле tempdb_mssql_#.ndf;
• журнал транзакций содержит информацию о выполненных действиях. Данные находятся в файле templog.ldf.

База данных TempDB используется для:

• временных объектов, созданных пользователем явно. Это временные таблицы, индексы, табличные переменные, курсоры;
• выполнения запросов, использующих операторы GROUP BY, ORDER BY, UNION, SORT, DISTINCT;
• хранений версий строк версионированных таблиц.

Проблемы в работе с TempDB можно обнаружить, используя монитор производительности (Performance Monitor, perfmon) и счетчики "Среднее время записи данных на диск" (Average Disk sec/Write) и "Средний размер очереди на запись" (Avg. disk write queue length). Оптимальными значениями являются 25 мс для времени записи и 2 для размера очереди соответственно.

На рисунке 1 приведен пример данных счетчиков одного из наших клиентов. Данные указаны за неделю. На оси X указано время от 1:30 2024/02/05 до 16:50 2024/02/12, на оси Y значение от 0 до 100.
Синим цветом обозначен счетчик "Среднее время" в секундах записи данных на диск, красным - "Средний размер очереди на запись".

Синим пунктиром изображено рекомендованное значение для среднего времени записи, равное 25 мс. Красным - для среднего размера очереди, равного 2.

Пиковые значения среднего времени записи на диск, изображенные синим цветом, превышают оптимальные каждый день в 5:30 утра. Размер очереди также превышает рекомендованные, причем в десятки раз, что видно по красным линиям. Эти проблемы указывают на неправильную настройку TempDB.

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

Оптимальная настройка базы обеспечит работу с минимальными задержками. Для этого нужно ускорить операции загрузки и чтения данных с диска, на котором расположена база.

Первой рекомендацией будет выбор наиболее производительного диска. Не желательно размещать файлы TempDB на медленных жестких дисках HDD, стоит отдать предпочтение более скоростным SSD. Скорости считывания и загрузки на дисках SSD могут в десятки раз превосходить HDD.

Стоит обратить внимание на количество файлов с данными базы. Для серверов с количеством логических процессоров меньше 8, следует создать столько файлов, сколько процессоров. Если процессоров больше, нужно создать 8 файлов и проанализировать конкуренцию на чтение и запись. Рекомендуется добавлять по 4 файла пока конкуренция не будет минимальна. Уменьшение конкуренции можно наблюдать в уменьшении ожидания с типом PAGELATCH_UP.

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

Вместе с количеством файлов необходимо правильно определить их размер. Рекомендуется выбрать такой размер, чтобы избежать частых операций автоувеличения файлов данных TempDB из-за недостатка места для временных объектов. Каждая операция увеличения приводит к выделению памяти и переносу данных, что увеличивает среднее время записи.

Для того, чтобы отследить изменение размера базы TempDB можно использовать счетчик Размер файла данных (Data File(s) Size (KB)) Монитора производительности. Данный счетчик позволяет проанализировать занимаемое базой место в реальном времени.

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

В SQL Server 2022 добавлена возможность переноса метаданных базы в память в виде Memory-Optimized Tempdb Metadata. Рекомендуется произвести перенос для ускорения операций, связанных сTempDB. Дополнительные преимущества включают улучшение параллелизма и уменьшение конкуренции. Для того, чтобы перенести метаданные в память, используется следующий скрипт:

После выполнения запроса следует перезапустить сервер.

Нами была проведена оптимизация базы TempDB на сервере клиента, пример которого был приведен в статье. Сервер содержит 72 логических процессора, 3 Тб оперативной памяти и 13 SSD. На сервере 4 NUMA-ноды по 18 процессоров, поэтому оптимальное количество файлов базы TempDB равно 18. Для уменьшения очередей на запись распределили файлы базы по SSD и перевели метаданные в память.

В результате нам удалось добиться сокращения времени выполнения ежедневного наполнения хранилища данных на 20%.