Проблемы с TempDB

База данных TempDB доступна всем пользователям SQL Server для размещения временных объектов, здесь же SQL Server создает разнообразные внутренние объекты для сортировки, буферизации, модификации индексов. И здесь может образоваться узкое место, если какие-то процессы выйдут из-под контроля.

Разработчики и DBA стараются избежать потенциальных проблем, стараясь не использовать тяжелого кода при работе с TempDB. Но помимо кода, к замедлению работы могут привести и ошибки конфигурации. Самые распространенные из них:

• Только один файл данных TempDB
• Слишком много файлов данных TempDB (например, по одному на каждое из 40 ядер)
• Несколько файлов данных TempDB, но разного размера или с неравномерным приростом

Почему все эти случаи являются проблемными? Ответ на этот вопрос слишком технический, чтобы объяснять его здесь. При желании можно почитать в интернете про SGAM и PFS, а краткая версия – так уж в SQL реализована работа с хранением данных.

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

Избежать проблем с TempDB можно используя хорошо подобранное количество файлов данных, причем одинакового размера. Советуем начать с четырех файлов одинакового размера и с одинаковым уровнем авто-инкремента. После чего нужно постоянно мониторить уровень активности TempDB и следить за возможными конфликтами (например, отслеживать ожидания PAGELATCH на TempDB).

Причем нужен именно постоянный мониторинг. Приложения меняются и могут внезапно создавать более сильную нагрузку на TempDB. Меняется и SQL Server, и новый функционал может изменить работу с TempDB.

Раздутые таблицы

По мере заполнения таблиц, как только очередная страница достигает порога в 8Кб, SQL сервер ее разделяет на две. Разделение страниц – интенсивная в плане операций чтения и записи деятельность, и при этом образуется низкая плотность страниц и логическая фрагментация (логический порядок не совпадает с физическим). От этого страдают и индексы, в основном кластеризованные.

Сюда же можно отнести случай, когда в куче обновляется строка, и она уже не помещается – сервер ее тут же переносит в новое место, а в старом оставляет ссылку. При большом количестве подобных ссылок скорость загрузки данных существенно падает.

Чтобы избежать разделения страниц, часто играются с настройкой заполнения Fill Factor, которая определяет, насколько плотно SQL сервер упаковывает страницы данных при перестройке индексов. По умолчанию выставляется 100%, при этом сервер упаковывает как можно больше данных на одну страницу. Однако можно менять уровень заполнения для каждого индекса (лучше не трогать уровень всего сервера). Например, когда в таблице много полей инициализируются в Null и лишь позже заполняются, лучше выставить уровень заполнения пониже, например, 90%, чтобы иметь возможность делать эти обновления полей без излишнего разделения страниц.

То же самое можно делать, если ключ кластеризованного индекса не инкрементный (например, фамилия). Если только всех людей в эту таблицу не вносят изначально в алфавитном порядке, то вставки в индекс будут в произвольных местах, и уменьшение фактора заполнения поможет уменьшить фрагментацию.
При этом ставить уровень заполнения низким (например, 50%) тоже плохо: да, будет меньше разделения страниц, но будет использовано больше памяти в кэше и на дисках, дольше будут производиться бэкапы.

Заключение

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

Команда ALG DevOps Team будет благодарна за Ваши ответы на следующие вопросы: