Переполнение TempDB
Один из частых вопросов, встающих перед нашими клиентами, состоит в том, как почистить TempDB в SQL. TempDB – это системная база данных, в которой содержатся как временные, так и системные объекты, а также данные для сортировки и результирующие наборы. В этой статье мы расскажем, как TempDB работает и, как «ДБ-сервис» диагностирует переполнение этой БД.
Как работает TempDB?
В TempDB хранятся два типа данных:
- Временные пользовательские объекты: локальные таблицы пользователей MS SQL Server, которые возникают в ходе выполнения запросов.
- Внутренние служебные объекты, создаваемые ядром MS SQL Server.
Большое число активных транзакций, использующих объекты TempDB, делают затруднительным, а то и невозможным автоматическую очистку журнала и повторное использование свободного пространства. Это и приводит к переполнению TempDB, что является одной из наиболее распространенных проблем для приложений «1С» и Microsoft Dynamics AX.
Переполнение TempDB влечет за собой проблемы на сервере, вплоть до остановки MS SQL Server. Это означает недоступность БД на этом сервере и, как следствие, паралич в работе их пользователей.
Диагностика и решение проблем
Переполнение TempDB влечет за собой две основные проблемы: потерю свободного места на диске из-за разрастания базы и снижение скорости работы. В общем случае работа с этими проблемами выглядит следующим образом.
Недостаточно места
- Поиск ответа на вопрос, как в MS SQL уменьшить TempDB, начинается с диагностики проблемы. Проводите мониторинг запросов на СУБД с помощью Extended Events. Эта система обработки событий для серверных систем поможет вам сохранять все запросы на MS SQL Server, фильтруя их по длительности (мы рекомендуем ставить 3 секунды).
- Включите запись событий DBMSSQL в технологический журнал: его анализ позволит понять контекст запроса и точно установить, откуда именно из «1С» он пришел на СУБД.
Низкая скорость работы
- Выделите для TempDB отдельный быстрый SSD-диск (в идеале с NVMe-интерфейсом) и мониторьте время его отклика.
- Чтобы избежать постоянного прироста TempDB, выдайте ей достаточный начальный размер (совет, актуальный для любых БД).
Ниже мы расскажем об инструментах и процедурах диагностики, которые применяет «ДБ-сервис».
Просмотр информации с помощью процедуры собственной разработки
В выводе процедуры можно увидеть степень загруженности TempDB пользовательскими объектами, внутренними объектами SQL Server и версиями строк открытых активных транзакций.
- Пользовательские объекты. Чаще всего к ним относятся локальные или глобальные временные таблицы или табличные переменные, созданные с помощью пользовательских запросов. Однако, как показывает наш опыт работы с торговой сетью по продаже косметики и парфюмерии, в качестве пользовательских объектов в TempDB могут выступать и постоянные таблицы.
- Внутренние объекты. Обычно речь идет об объектах сортировки, группировки, хэширования. Сюда также включаются рабочие таблицы для операций курсора или спулинга, а также временное хранилище LOB-объектов.
- Версионность. MS SQL TempDB занимает много места? Существование длительных или открытых транзакций (состояние ожидания), которые препятствуют очистке, может являться причиной неконтролируемого роста TempDB. Хранилище версий содержит зафиксированные строки, поэтому операция SELECT не блокируется, когда другая операция UPDATE / DELETE работает с той же строкой, поскольку SELECT считывает строку из хранилища версий, а не из фактической базовой таблицы. Когда включена версионность (изоляция моментальных снимков (с фиксацией или без), а также перестроение индекса онлайн), строка хранится в базе данных TempDB. Строка сохраняется в хранилище версий, когда в запросах с этой строкой выполняются транзакции. После того как транзакция фиксируется, строка очищается из таблиц хранилища версий.
Общая информация о заполнении TempDB
В результате выполнения первого скрипта становится доступной общая информация о заполненности TempDB. Ниже приводятся пояснения по результирующему набору.
- «Usr_obj_gb – занято пользовательскими объектами в Gb (временные таблицы, курсоры и т.д.). Высокое значение в колонке usr_obj_gb может означать, к примеру, что кто-то из пользователей запустил большую выборку данных.
- «Internal_obj_gb» – занято внутренними объектами SQL Server в Гб. (результаты сортировки, группировки и т.д.).
- «Version_store_gb» – занято версиями строк открытых транзакций. Означает, что существуют очень длительные открытые транзакции. Иногда возникает на вторичной реплике из-за большого отставания и огромной транзакции с Primary.
- «Mixedextent_gb» – занято смешанными экстентами в Гб.
- «Свободно ГБ» – свободное место внутри БД SQL Server в Гб.
- «Свободно процентов» – свободное место внутри БД в процентах.
- «Занято процентов» – занятое место внутри БД в процентах.
- «Всего ГБ» – общий размер TempDB SQL.
Расширенная информация о заполнении TempDB
В результате выполнения второго скрипта становится доступной расширенная информация о заполненности TempDB. Она представлена в виде четырех результирующих наборов.
- Общая информация. Подробно описано выше.
- Топ-10 активных транзакций, использующих TempDB. Здесь нужно обращать внимание на высокое значение usr_obj_gb в первом запросе: возможно кто-то запустил большую выборку.
- Топ-10 активных транзакций и созданные ими объекты в TempDB. Анализируется, если есть высокое значенье internal_obj_gb в первом запросе.
- Топ-10 активных транзакций, использующих версии строк. Анализируется, если есть высокое значенье version_store_gb в первом запросе.
Как избегать переполнения TempDB?
В зависимости от того, что послужило причиной переполнения TempDB, «ДБ-сервис» предпринимает те или иные меры. Чаще всего требуется в TempDB SQL почистить кэш, завершить пользовательские сессии и подождать пока уйдет отставание вторичной реплики.
Однако обращаем внимание на то, что в отсутствие угрозы остановки сервиса мы завершаем пользовательские сессии только по согласованию с нашими клиентами.
Если ничего предпринять нельзя или клиенту нужно чтобы процесс выполнялся любой ценой, мы пытаемся решить вопрос расширением существующих файлов или их пересозданием на другом диске. Инженеры «ДБ» сервис специализируются на удаленном администрировании серверов БД SQL Server, поэтому, воспользовавшись нашими услугами, вы можете быть уверены в том, что ваша СУБД не остановится, а БД не окажутся недоступными из-за переполнения TempDB.
Резюме
В этой статье мы рассмотрели механизм работы системной глобальной базы данных MS SQL TempDB, причины ее переполнения и инструменты мониторинга, которые использует «ДБ-сервис».
Эксперт ДБ-сервис
