База данных tempdb

Статья
2017-03-06

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

временные объекты, созданные явно, такие как глобальные или локальные временные таблицы, временные хранимые процедуры, табличные переменные и курсоры;
Внутренние объекты, созданные SQL Server ядром СУБД, например рабочие таблицы для хранения промежуточных результатов для очереди или сортировки.
версии строк, сформированные транзакциями изменения данных в базе данных, в которой используются транзакции изоляции моментальных снимков с зафиксированным чтением и транзакции изоляции моментальных снимков;
версии строк, создаваемые транзакциями изменения данных для таких функций, как операции с индексами в сети, функции режима MARS и триггеры AFTER.

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

Физические свойства базы данных tempdb

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

Файл	Логическое имя	Физическое имя	Увеличение размера файлов
Первичные данные	tempdev	tempdb.mdf	Автоувеличение на 10 % до заполнения диска.
Журнал	templog	templog.ldf	Автоувеличение на 10 % до максимального размера в 2 ТБ.

Размер базы данных tempdb может повлиять на производительность системы. Например, если размер базы данных tempdb слишком мал, системная обработка может быть слишком занята автоматическим увеличением базы данных для поддержки требований к рабочей нагрузке при каждом запуске SQL Server. Вы можете избежать этой нагрузки, увеличив размер базы данных tempdb.

Увеличение производительности базы данных tempdb

В SQL Server производительность базы данных tempdb улучшается следующими способами.

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

Перемещение данных и файлов журналов базы данных tempdb

Сведения о перемещении файлов данных и журналов базы данных tempdb см. в статье Перемещение системных баз данных.

Параметры базы данных

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

Параметр базы данных	Значение по умолчанию	Можно ли изменить
ALLOW_SNAPSHOT_ISOLATION	OFF	Да
ANSI_NULL_DEFAULT	OFF	Да
ANSI_NULLS	OFF	Да
ANSI_PADDING	OFF	Да
ANSI_WARNINGS	OFF	Да
ARITHABORT	OFF	Да
AUTO_CLOSE	OFF	нет
AUTO_CREATE_STATISTICS	ON	Да
AUTO_SHRINK	OFF	нет
AUTO_UPDATE_STATISTICS	ON	Да
AUTO_UPDATE_STATISTICS_ASYNC	OFF	Да
CHANGE_TRACKING	OFF	нет
CONCAT_NULL_YIELDS_NULL	OFF	Да
CURSOR_CLOSE_ON_COMMIT	OFF	Да
CURSOR_DEFAULT	GLOBAL	Да
Параметры доступности базы данных	ONLINE MULTI_USER READ_WRITE	Нет Нет Нет
DATE_CORRELATION_OPTIMIZATION	OFF	Да
DB_CHAINING	ON	нет
ENCRYPTION	OFF	нет
NUMERIC_ROUNDABORT	OFF	Да
PAGE_VERIFY	CHECKSUM для новых установок SQL Server. НЕТ для обновлений SQL Server.	Да
PARAMETERIZATION	ПРОСТОЙ	Да
QUOTED_IDENTIFIER	OFF	Да
READ_COMMITTED_SNAPSHOT	OFF	нет
RECOVERY	ПРОСТОЙ	нет
RECURSIVE_TRIGGERS	OFF	Да
Параметры компонента Service Broker	ENABLE_BROKER	Да
TRUSTWORTHY	OFF	нет

Описание этих баз данных см. в статье Параметры ALTER DATABASE SET (Transact-SQL).

Ограничения

В базе данных tempdb невозможно выполнить следующие операции:

Добавление файловых групп.
Резервное копирование и восстановление из копии.
Изменение параметров сортировки. Параметрами сортировки по умолчанию являются параметры сортировки сервера.
Изменение владельца базы данных. Tempdb принадлежит sa.
Создание моментального снимка базы данных.
Удаление базы данных.
Удаление пользователя guest из базы данных.
Включение системы отслеживания измененных данных.
Участие в зеркальном отображении базы данных.
Удаление первичной файловой группы, первичного файла данных или файла журнала.
Переименование базы данных или первичной файловой группы.
Выполнение инструкции DBCC CHECKALLOC.
Выполнение инструкции DBCC CHECKCATALOG.
Перевод базы данных в режим «вне сети» (OFFLINE).
Перевод базы данных или первичной файловой группы в режим READ_ONLY.

Разрешения

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

Параметр SORT_IN_TEMPDB для индексов

Системные базы данных

sys.databases (Transact-SQL)

sys.master_files (Transact-SQL)

Перемещение файлов базы данных