SQL-Ex blog

Что такое TempDB?

TempDB является глобальным ресурсом, который используется всеми и каждым внутри SQL Server. Думайте об этом как о свалке для всего, что не помещается в памяти. Это всегда database_id номер два в sys.databases.

Она недолговечна, что означает, что база данных создается заново всякий раз, когда стартует сервис SQL. Всякий раз воссоздается новый набор файлов данных и журналов. И что это означает для вас? Во-первых, вы не можете поместить какие-либо объекты в базу данных TempDB, которые требуют постоянного хранения. Не используйте TempDB как базу данных для разработки (за исключением, возможно, кратковременных тестов, поскольку после перезапуска сервиса вы потеряете всю свою работу.

TempDB работает в круговой манере, известной как пропорциональное наполнение; Данные записываются во все файлы в соответствии с наличием свободного места в каждом файле, и файлы переключаются в поисках наилучшего места для работы. Я расскажу об этом более подробно ниже в этой статье. TempDB по большей части похожа на другие пользовательские базы данных, за исключением журнализации и надежности. Журнализация выполняется в минимальном объеме. Журнал транзакций имеется, но он используется только для отката транзакций, а не для восстановления. TempDB ненадежна, поскольку её не требуется восстанавливать, т.к. она всякий раз обновляется.

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

Что хранится в TempDB?

Я считаю, что лучше расписать это по пунктам. Глядя на список, помните, что всякий раз, когда вы пишете код, он может потребовать места для данных в TempDB. Определяя тип используемой операции, потратьте минуту, чтобы выяснить, нужна ли она вам, например, сортировка. Кроме того, следите за сбросом в TempDB - это подобно подкачке памяти операционной системы на диск. Сбрасывание на диск означает, что было выделено недостаточно памяти, и операция была вынуждена "слить" или сбросить данные в TempDB, чтобы выполнить свою работу. Я также рекомендую при настройке производительности хранимых процедур использовать SET STATISTICS IO, чтобы увидеть количество обработанных данных в ваших рабочих файлах и таблицах (worktables). Как вы увидите, они также обрабатываются внутри TempDB.

Временные объекты, которые пользователь создает явно

• Глобальные или локальные временные таблицы и индексы.


• Временные хранимые процедуры.


• Табличные переменные.


• Таблицы, возвращаемые табличнозначными функциями.


• Таблицы, используемые в курсорах.

Внутренние объекты

• Имеются объекты, которые создаются движком базы данных.

Рабочие таблицы для сохранения промежуточных результатов

• Спул.


• Курсоры.


• Сортировки.


• Временные хранилища больших объектов (LOB).

Рабочие файлы для хэш-соединений или операций хэш-агрегирования

• Хэш-соединения или операции хэш-агрегирования.

Промежуточные операции сортировки

• Перестройка индексов (если указывается SORT_IN_TEMPDB).


• Определенные запросы с GROUP BY, ORDER BY или UNION.

Хранение версий

• RSCI- Изоляция снимков с чтением зафиксированных транзакций, использующая версионную изоляцию строк или изоляцию снимков. Это крупный пользователь ресурсов TempDB!


• Онлайновые операции с индексами.


• Триггеры AFTER.

Группы доступности (Availability Groups)

Реплики групп доступности используют по умолчанию изоляцию снимка, которая использует версии строк каждой транзакции. Эти транзакции записываются в хранилище версий (Version Store) в TempDB. Они выполняются на вторичных репликах, чтобы избежать блокировок, которые могут препятствовать применению транзакций из первичной реплики. Однако блокирование все же может стать проблемой для длительных транзакций, которые приводят к блокировке процесса очистки хранилища версий и, возможно, к заполнению вашей TempDB. Если это происходит, вы не сможете выполнить аварийное переключение. Убедитесь, что вы знаете как это все работает. Вот отличная ссылка.

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

Что вызывает проблемы в TempDB?

Многие думают, что ввод-вывод является основной причиной проблем в TempDB, но это не так. Ввод-вывод может быть проблемой, когда дело касается диска и задержки, но это на самом деле не самое узкое место. Причина в конкуренции за ресурсы. Все задачи, обращающиеся к TempDB борются за одни и те же ресурсы и рабочее пространство. Эта работа требует захвата трех специальных страниц, которые используются для управления: GAM (глобальная карта размещения), SGAM (разделяемая карта размещения) и PFS (свободное страничное пространство). Эти страницы ответственны за определение места, где выполняется работа в файлах данных TempDB. Операции, ожидающие этого определения, особенно когда множество процессов выполняются одновременно, могут быстро выстроиться в очередь, что приведет к «конфликту». Ниже приводятся три вида конфликтов, которые могут иметь место:
Конфликт размещения объектов - Это происходит, когда система ищет место для работы. Это конфликт страниц метаданных, которые используются для управления выделением пространства.
Конфликт метаданных - Это конфликт страниц, которые принадлежат системным объектам, используемым для отслеживания метаданных. Данные связаны с созданием и разрушением временных таблиц и освобождением памяти.
Конфликт кэша временных таблиц - Кэш временных таблиц помогает с размещением метаданных и объектов. Он позволяет повторно использовать временные таблицы.

Pam Lahoud имеет фантастический блог по этому поводу, где она подробно описывает эти конфликты; не забудьте почитать.

Как решить проблемы с конфликтами?

Теперь, когда вы понимаете причины проблем TempDB, вот несколько вещей, которые стоит рассмотреть в попытках их решения.

Надлежащая конфигурация

Чтобы получить выгоду от круговой обработки TempDB, существенно иметь множество файлов данных с тем, чтобы рабочая нагрузка равномерно распределялась между ними. Чтобы это гарантировать, вы должны убедиться, что ваши файлы также имеют одинаковые размеры и автоматический прирост. Если у вас один файл больше другого, вы получите конфликт. Система разместит всю работу в самый большой файл, полагая, что он имеет больше всего свободного пространства. Кроме того, при добавлении нового файла запланируйте перезапуск. Хотя вам не требуется перезапуск для добавления нового файла, так или иначе движок будет использовать новый файл только потому, что теперь в нем больше всего свободного места. Переустановка TempDB с помощью перезапуска вызвана желанием убедиться, что поддерживается пропорциональное использование файлов. Я регулярно использовала это на практике для проверки, что все они выровнены.

Помимо обеспечения надлежащего использования круговой логики, добавление большего числа файлов дает вам большее число этих специальных страниц для работы с ними. С добавлением каждого файла данных вы получаете одну страницу GAM и одну страницу SGAM на каждые 4Гб пространства, выделенного для этих файлов. Мне всегда задают вопрос, как узнать какого размера должны быть файлы, чтобы гарантировать оптимальное число таких страниц. К сожалению, здесь нет однозначного ответа. Нет ни скрипта, ни алгоритма, который я могла бы вам предложить для вычисления магического числа для вашей среды. Как и со многими другими установками, вам потребуется знать свою рабочую нагрузку, чтобы это определить. Я настоятельно советую вам мониторить использование TempDB и скорость роста, как начальную точку. Есть еще один отличный инструмент, который мне нравится использовать, чтобы следить за виртуальными файлами журнала (vlf) как индикатором этих событий. Вот блог, чтобы лучше узнать об этих вещах.

Новые версии SQL Server упрощают нам это. Во время установки нам помогают определить число необходимых файлов и позволяют создать их в процессе установки.

Наконец, как вы конфигурируете диск? Обработка TempDB происходит в реальном диске, а не в памяти для большинства операций. Поскольку TempDB высоко конкурентен и используется интенсивно движком, критичным является размещение его на наиболее быстром диске из имеющихся. Если можете, используйте флэш-накопители. Имеются высокоскоростные накопители с энергонезависимой памятью; вы можете найти информацию о них по ключевым словам NVMe, Non-volatile Memory Express. Это SSD, твердотельные накопители. Нужно только иметь в виду, что NVMe являются SSD, но не все SSD являются NVMe, т.к. имеются другие типы SSD. Не важно, какого уровня будет тип SSD, они отлично подходят для рабочих нагрузок TempDB. Если вы хотите больше узнать о типах накопителей и что спросить у своего администратора при определении лучшей конфигурации вашей TempDB в SQL Server, посетите мой блог о накопителях для начинающих.

Флаги трассировки

До версии SQL 2016 использование флагов трассировки 1118 и 1117 помогало снизить конфликты, обеспечивая одновременный рост всех файлов, если возрастал один из них, а также измерение алгоритма смешанных экстентов, который уменьшал конфликты с SGAM-страницами. Включение 1118 не имеет недостатков. Что касается 1117, то следует иметь в виду, что этот флаг применяется ко всем файловым группам, а не только к TempDB. При включенном флажке 1117, если у вас имеются другие файловые группы в ваших базах данных, и если один из этих файлов растет, он включает событие роста для всех остальных файлов в этой файловой группе и этой базе данных. Если вы используете более старую версию SQL Server, я НАСТОЯТЕЛЬНО рекомендую добавить их. Это настолько важно, что теперь принимается по умолчанию.

Накопительные обновления

Метаданные не находятся в руках администратора базы данных, но то, как они обрабатываются, имеет решающее значение для наших рабочих нагрузок. Это обязанность команды разработки движка SQL обнаруживать проблемы и вносить улучшения в его работу. Однако вы, как администратор, можете гарантировать, что имеете лучший код, просто применяя самые последние CU (накопительные обновления). Вы можете воспользоваться исправлениями, которые Microsoft предоставляет для решения этой проблемы, применив CU. С годами методы отслеживания метаданных и распределения объектов эволюционировали, снижая конфликты; единственный путь воспользоваться этими изменениями - это выполнять обновления и устанавливать патчи. Имеется несколько значимых CU, в которых исправлены конфликты метаданных: SQL 2016 SP1 CU8, SQL 2017 CU5, например.

SQL Server 2019

TempDB был удостоен некоторого внимания в SQL Server 2019. Были внесены улучшения в кэширование временных таблиц, конкурентное обновление PFS-страниц и введены две захватывающие новые возможности In-Memory OLTP TempDB Metadata (метаданные TempDB для OLTP таблиц, размещаемых в памяти) и Table Variable Deferred Compilation (отложенная компиляция табличных переменных). Как и с другими новыми возможностями, имеются некоторые проблемы с системными таблицами, размещаемыми в памяти, поэтому проверьте, чтобы у вас был установлен 2019 CU2, который исправляет некоторые конфликты, связанные с этой функциональностью.

Чтобы воспользоваться новой возможностью Memory OLTP TempDB Metadata, её нужно включить:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED 

TEMPDB_METADATA = ON;

Как увидеть, что происходит в TempDB?

Вы можете заглянуть в базу данных TempDB, чтобы увидеть, что там происходит. Как вы уже знаете, TempDB - это о распределении места на страницах для выполнения задач. Вам нужно иметь возможность видеть это распределение места и идентифицировать те запросы, которые это место занимают. Приведенные ниже DMV (динамические административные представления) дадут вам направление, чтобы это выяснить. Обратите внимание, что я также включила DMV для просмотра хранилища версий, которое, если вы помните, также находится в TempDB.

sys.dm_db_file_space_usage - Использование места в файлах баз данных.
sys.dm_db_session_space_usage - Исключительно для базы данных TempDB, возвращает число страниц, выделенных и освобожденных в каждой сессии.
sys.dm_db_task_space_usage - Исключительно для базы данных TempDB, возвращает информацию о числе страниц, выделенных и освобожденных каждой задачей.
sys.dm_tran_active_snapshot_database_transactions - Возвращает число активных транзакций, которые генерируют и могут иметь доступ к версиям строк.
sys.dm_tran_version_store - Записи версий в версионном хранилище.

Освоение TempDB

Это введение было просто началом освоения TempDB; понимание основ является хорошим стартом. Много чего предстоит изучить, когда вы вникните во временные таблицы, табличные переменные, сброс в TempDB и способы заглянуть в TempDB, чтобы увидеть транзакции и распределение страниц посредством динамических представлений (DMV). Я советую вам найти время, чтобы глубже ознакомиться с этим.