tempdb, база данных

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

В этой статье описывается tempdb системная база данных, глобальный ресурс, доступный всем пользователям, подключенным к экземпляру SQL Server, База данных SQL Azure или Управляемый экземпляр SQL Azure.

Обзор

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

• Временные пользовательские объекты, созданные явно. К ним относятся глобальные или локальные временные таблицы и индексы, временные хранимые процедуры, табличные переменные, возвращаемые функциями с табличными значениями таблицы и курсоры.

• Внутренние объекты, создаваемые ядром СУБД. К ним относятся:

  • Рабочие таблицы, хранящие промежуточные результаты буферов, курсоры, сортировки и временное хранилище больших объектов (LOB).
  • рабочие файлы для операций хэш-соединения или статистических хэш-выражений;
  • промежуточные результаты сортировки для таких операций, как создание или перестроение индексов (если указать SORT_IN_TEMPDB), либо определенных запросов GROUP BY, ORDER BY или UNION.

  Каждый внутренний объект использует минимум девять страниц: страницу IAM и восьмистраничный экстент. Дополнительные сведения см. в разделе Страницы и экстенты.

• Хранилища версий. Это коллекции страниц данных со строками данных, которые поддерживают функции управления версиями строк. Существует два типа хранилищ: общее хранилище версий и хранилище версий для построения индекса в подключенном режиме. Хранилища версий содержат следующее:

  • версии строк, создаваемые транзакциями изменения данных в базе данных, которая использует READ COMMITTED через транзакции изоляции моментальных снимков и транзакции изоляции управления версиями строк;
  • версии строк, создаваемые транзакциями изменения данных для таких функций, как операции с индексами в подключенном режиме, функции MARS (множественный активный результирующий набор) и триггеры AFTER.

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

tempdb Никогда не должно сохраняться ничего из одного сеанса SQL Server в другой. Операции резервного копирования и восстановления для tempdb недопустимы.

Физические свойства tempdb в SQL Server

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

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

Количество файлов данных по умолчанию основано на общих рекомендациях, приведенных в статье KB 2154845.

Чтобы проверить текущий размер и параметры увеличения для tempdb, выполнить запрос к представлению tempdb.sys.database_files.

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

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

Параметры базы данных для tempdb в SQL Server

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

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

tempdb в Azure SQL

Поведение в База данных SQL Azure отличается от поведения tempdb SQL Server, Управляемый экземпляр SQL Azure и SQL Server на виртуальных машинах Azure.

tempdb в Базе данных SQL

Отдельные базы данных и базы данных в пуле в службе База данных SQL Azure поддерживают глобальные временные таблицы и глобальные временные хранимые процедуры, которые хранятся в tempdb и имеют область действия на уровне базы данных. Глобальные временные таблицы и глобальные временные хранимые процедуры являются общими для всех сеансов пользователей в рамках одной базы данных. Сеансы пользователей, связанные с другими базами данных, не имеют доступа к глобальным временным таблицам. Дополнительные сведения см. в разделе Глобальные временные таблицы (база данных SQL Azure) в области базы данных.

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

Для отдельных и пуловых баз данных в База данных SQL Azure из всех системных баз данных доступны только master базы данных и tempdb базы данных. Дополнительные сведения см. в статье Что собой представляет логический сервер в Azure.

Чтобы узнать больше о размерах в База данных SQL Azure, ознакомьтесь со следующими сведениями:tempdb

• модель приобретения на основе виртуальных ядер: отдельные базы данных, базы данных в пуле;
• модель приобретения на основе DTU: отдельные базы данных, базы данных в пуле.

tempdb в Управляемом экземпляре SQL

Управляемый экземпляр SQL Azure поддерживает временные объекты так же, как и SQL Server, где все глобальные временные таблицы и глобальные временные хранимые процедуры доступны всем пользовательским сеансам в пределах одного управляемого экземпляра. Аналогично доступны все системные базы данных.

Можно настроить количество tempdb файлов, их увеличение и максимальный размер. Дополнительные сведения о настройке tempdb параметров в Управляемый экземпляр SQL Azure см. в разделе "Настройка параметров tempdb для Управляемый экземпляр SQL Azure".

Чтобы узнать больше о tempdb размерах в Управляемый экземпляр SQL Azure, просмотрите ограничения ресурсов.

Ограничения

С базой данных tempdb нельзя выполнять следующие операции:

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

Разрешения

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

Оптимизация производительности tempdb в SQL Server

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

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

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

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

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

Чтобы проверить текущий размер и параметры увеличения для tempdb, используйте следующий запрос:

 SELECT FileName = df.name,
   current_file_size_MB = df.size*1.0/128,
   max_size = CASE df.max_size
     WHEN 0 THEN 'Autogrowth is off.'
     WHEN -1 THEN 'Autogrowth is on.'
     ELSE 'Log file grows to a maximum size of 2 TB.'
   END,
   growth_value =
     CASE
       WHEN df.growth = 0 THEN df.growth
       WHEN df.growth > 0 AND df.is_percent_growth = 0 THEN df.growth*1.0/128.0
       WHEN df.growth > 0 AND df.is_percent_growth = 1 THEN df.growth
     END,
   growth_increment_unit =
     CASE
       WHEN df.growth = 0 THEN 'Size is fixed.'
       WHEN df.growth > 0 AND df.is_percent_growth = 0  THEN 'Growth value is MB.'
       WHEN df.growth > 0 AND df.is_percent_growth = 1  THEN 'Growth value is a percentage.'
     END
FROM tempdb.sys.database_files AS df;
GO

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

Расположите базу данных tempdb на дисках, отличающихся от используемых пользовательскими базами данных.

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

Представлено в SQL Server 2016 (13.x)

• Временные таблицы и табличные переменные кэшируются. Кэширование позволяет операциям удаления и создания временных объектов выполняться очень быстро. Кэширование также снижает вероятность возникновения состязаний, связанных с метаданными и выделением страниц.
• Усовершенствован протокол кратковременных блокировок выделения страниц для снижения количества используемых кратковременных блокировок UP (обновление).
• Снижены затраты ресурсов на ведение журнала tempdb — уменьшено потребление пропускной способности подсистемы ввода-вывода файлом журнала tempdb.
• Программа установки добавляет множество файлов данных tempdb при установке нового экземпляра. Эту задачу можно выполнить с помощью нового элемента управления для ввода в пользовательском интерфейсе в разделе Настройка ядра СУБД и параметра командной строки /SQLTEMPDBFILECOUNT. По умолчанию программа установки добавляет столько файлов данных tempdb, сколько имеется логических процессоров, но их может быть не больше восьми.
• При наличии множества файлов данных tempdb автоматическое увеличение выполняется для всех файлов в одно время и в равном объеме согласно параметрам увеличения. Флаг трассировки 1117 больше не требуется. Дополнительные сведения см. в статье об изменениях -T1117 и -T1118 для баз данных TEMPDB и пользовательских баз данных.
• Для всех операций распределения в tempdb используются единообразные экстенты. Флаг трассировки 1118 больше не требуется. Дополнительные сведения об улучшениях производительности в tempdb см. в статье блога TEMPDB — Files and Trace Flags and Updates, Oh My! (TEMPDB — файлы, флаги трассировки и обновления).
• Для первичной файловой группы свойство AUTOGROW_ALL_FILES включено и не может быть изменено.

Представлено в SQL Server 2017 (14.x)

• Программа установки SQL улучшает рекомендации по первоначальному tempdb выделению файлов. Программа установки SQL предупреждает клиентов, если начальный размер файла имеет значение больше 1 ГБ, а если мгновенное инициализация файлов не включена, предотвращение задержек запуска экземпляра.
• В SQL Server 2017 появилась новая sys.dm_tran_version_store_space_usage dmV для отслеживания использования хранилища версий для каждой базы данных. Это новое динамическое административное представление будет полезно в мониторинге tempdb использования хранилища версий для баз данных, которые могут заранее спланировать tempdb размер на основе требований к использованию хранилища версий для каждой базы данных.
• Новые функции интеллектуальной обработки запросов, такие как адаптивные соединения и обратная связь предоставления памяти, сокращают разлив памяти при последовательном выполнении запроса, уменьшая ненужную tempdb загрузку.

Представлено в SQL Server 2019 (15.x)

• Начиная с SQL Server 2019 (15.x), SQL Server не использует FILE_FLAG_WRITE_THROUGH параметр при открытии файлов, чтобы tempdb обеспечить максимальную пропускную способность диска. Так как создается повторно при запуске SQL Server, эти параметры не требуются, так tempdb как они предназначены для других системных баз данных и пользовательских баз данных для согласованности данных. Дополнительные сведения см FILE_FLAG_WRITE_THROUGH. в разделе "Ведение журналов и алгоритмы хранения данных", которые расширяют надежность данных в SQL Server.
• Метаданные TempDB, оптимизированные для памяти, удаляют узкие места на ожиданиях PAGELATCH tempdbи разблокируют новый уровень масштабируемости. Дополнительные сведения см. в этой демонстрации видео о том, как (и когда) использовать метаданные TempDB, оптимизированные для памяти. Дополнительные сведения см . в статье о мониторинге и устранении неполадок метаданных tempdb, оптимизированных для памяти.
• Обновление страницы параллельного свободного места на странице (PFS) уменьшает конфликты при блокировке исправлений во всех базах данных, что наиболее часто наблюдается в tempdb. Это улучшение позволяет изменить способ управления параллелизмом с помощью обновлений PFS, чтобы при этом можно было использовать общую кратковременную блокировку, а не монопольную блокировку. Это поведение включено по умолчанию во всех базах данных (включая TempDB) начиная с версии SQL Server 2019 (15.x). Дополнительные сведения о страницах PFS см . на страницах GAM, SGAM и PFS.
• По умолчанию новая установка SQL Server на Linux создает несколько tempdb файлов данных на основе количества логических ядер (с до восьми файлов данных). Это не применимо к обновлениям основной или дополнительной версии на месте. Каждый tempdb файл составляет 8 МБ с автоматическим ростом 64 МБ. Это поведение аналогично поведению установки SQL Server по умолчанию в Windows.

Представлено в SQL Server 2022 (16.x)

• В SQL Server 2022 (16.x) появилась улучшенная масштабируемость с улучшениями параллелизма системной страницы. Параллельное обновление страниц глобальной карты распределения (GAM) и общей глобальной карты распределения (SGAM) сокращает состязание за кратковременную блокировку страниц при выделении и освобождении страниц данных и экстентов. Эти улучшения применяются ко всем пользовательским базам данных и особенно к tempdb тяжелым рабочим нагрузкам. Дополнительные сведения о страницах GAM и SGAM см . на страницах GAM, SGAM и PFS. Дополнительные сведения см. в статьях "Улучшения параллелизма системной страницы" (Ep. 6) | Предоставленные данные.

Оптимизированные для памяти метаданные tempdb

Состязание tempdb метаданных в истории было узким местом для масштабируемости для многих рабочих нагрузок, работающих на SQL Server. SQL Server 2019 (15.x) представляет новую функцию, которая входит в семейство функций базы данных в памяти: метаданные TempDB, оптимизированные для памяти.

Эта функция эффективно удаляет это узкие места и разблокирует новый уровень масштабируемости для tempdbтяжелых рабочих нагрузок. В SQL Server 2019 (15.x) системные таблицы, участвующие в управлении метаданными временной таблицы, можно переместить в незащищенные, не устойчивые, оптимизированные для памяти таблицы.

Просмотрите это семиминутное видео, чтобы узнать, как и когда использовать метаданные TempDB, оптимизированные для памяти:

Настройка и использование метаданных tempdb, оптимизированных для памяти

Чтобы согласиться на применение этой новой функции, используйте следующий скрипт:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Чтобы это изменение конфигурации вступило в силу, нужно перезапустить службу.

Вы можете проверить, является ли tempdb оптимизированной для памяти, используя следующую команду T-SQL:

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

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

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

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON (RESOURCE_POOL = 'pool_name');

Это изменение также требует, чтобы перезапуск вступил в силу, даже если метаданные TempDB, оптимизированные для памяти, уже включены.

Ограничения оптимизированной для памяти базы данных tempdb

• Включение и отключение функции не является динамическим. Из-за внутренних изменений, которые необходимо внести в структуру tempdb, для включения или отключения этой функции требуется перезапуск.

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

  A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.
  

  Пример:

  BEGIN TRAN;
  
  SELECT *
  FROM tempdb.sys.tables;  -----> Creates a user in-memory OLTP transaction in tempdb
  
  INSERT INTO <user database>.<schema>.<mem-optimized table>
  VALUES (1); ----> Tries to create a user in-memory OLTP transaction in the user database but will fail
  
  COMMIT TRAN;
  

• Запросы к таблицам, оптимизированным для памяти, не поддерживают указания блокировки и изоляции, поэтому запросы к представлениям каталога оптимизированной для памяти tempdb не будут учитывать указания блокировки и изоляции. Как и в случае с другими представлениями системного каталога в SQL Server, все транзакции с системными представлениями находятся в READ COMMITTED изоляции (или в данном случае READ COMMITTED SNAPSHOT).

• Индексы columnstore нельзя создавать во временных таблицах при включении метаданных TempDB, оптимизированных для памяти.

• Из-за ограничения индексов columnstore использование sp_estimate_data_compression_savings системной хранимой процедуры с COLUMNSTORE COLUMNSTORE_ARCHIVE параметром сжатия данных не поддерживается при включении метаданных TempDB, оптимизированных для памяти.

• Системная хранимая процедура доступна вручную, чтобы подсистема памяти освобождала память, связанную с удаленными строками данных в памяти, которые имеют право на сборку мусора. Это может помочь в устранении неполадок с определенными метаданными tempdb, оптимизированными для памяти (HkTempDB), из-за ошибок памяти. Дополнительные сведения см. в разделе sys.sp_xtp_force_gc (Transact-SQL).

Планирование ресурсов для tempdb в SQL Server

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

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

• Установите для параметра autogrow on tempdb.
• Запускайте отдельные запросы или файлы трассировки рабочей нагрузки и следите за использованием диска базой данных tempdb.
• Выполняйте операции обслуживания индексов, например перестроение индексов, и следите за использованием диска базой данных tempdb.
• Используйте значения используемого пространства на диске из предыдущих шагов для прогнозирования общей рабочей нагрузки. Скорректируйте полученное значение с учетом предполагаемой параллельной обработки и задайте соответствующий размер tempdb.

Мониторинг использования tempdb

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

Например, в следующих четырех примерах скриптов можно найти объем свободного пространства, объем пространства tempdb, используемого хранилищем версий, объем пространства, используемого внутренними объектами, и объем пространства, используемого пользовательскими объектами:

 -- Determining the amount of free space in tempdb
SELECT SUM(unallocated_extent_page_count) AS [free pages],
  (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by the version store
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
  (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by internal objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
  (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by user objects
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
  (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

Для мониторинга действий выделения и освобождения страниц в tempdb на уровне сеансов или задач можно использовать динамические административные представления sys.dm_db_session_space_usage и sys.dm_db_task_space_usage. Эти представления позволяют выявлять большие запросы, временные таблицы или табличные переменные, которые используют много места на диске для tempdb. Кроме того, вы можете использовать несколько счетчиков для мониторинга свободного места в базе данных tempdb и ресурсов, использующих tempdb.

Например, используйте следующий скрипт для получения tempdb пространства, потребляемого внутренними объектами во всех выполняемых задачах в каждом сеансе:

-- Obtaining the space consumed by internal objects in all currently running tasks in each session
SELECT session_id,
  SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
  SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id;

Используйте следующий скрипт, чтобы найти tempdb пространство, потребляемое внутренними объектами в текущем сеансе, для выполнения и выполнения задач:

-- Obtaining the space consumed by internal objects in the current session for both running and completed tasks
SELECT R2.session_id,
  R1.internal_objects_alloc_page_count
  + SUM(R2.internal_objects_alloc_page_count) AS session_internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count
  + SUM(R2.internal_objects_dealloc_page_count) AS session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1
INNER JOIN sys.dm_db_task_space_usage AS R2 ON R1.session_id = R2.session_id
GROUP BY R2.session_id, R1.internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count;

Связанный контент

• Параметр SORT_IN_TEMPDB для индексов
• Системные базы данных
• sys.databases
• sys.master_files
• Перемещение файлов базы данных