Поделиться через

база данных tempdb

  • Статья

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

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

Обзор

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

  • пользовательские объекты, явно создаваемые. К ним относятся:

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

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

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

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

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

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

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

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

tempdb никогда не требуется сохранять данные с одного периода работы ядра СУБД на другой. Операции резервного копирования и восстановления для tempdb недопустимы.

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

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

Файлы Логическое имя Физическое имя Начальный размер Увеличение размера файлов
Первичные данные tempdev tempdb.mdf 8 МБ Автоматическое увеличение на 64 МБ до заполнения диска.
Вторичные файлы данных temp# tempdb_mssql_#.ndf 8 МБ Автоматическое увеличение на 64 МБ до заполнения диска.
Журнал templog templog.ldf 8 МБ Автоматическое увеличение на 64 МБ до максимального размера в 2 ТБ.

Все файлы данных tempdb всегда должны иметь одинаковый начальный размер и параметры роста.

Количество файлов данных tempdb

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

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

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

Дополнительные сведения см. в рекомендациях по уменьшению конфликтов распределения в базе данных tempdb SQL Server.

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

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

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

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

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

Параметр базы данных Значение по умолчанию Можно изменить
ACCELERATED_DATABASE_RECOVERY OFF Нет
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 Да
AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN) OFF Нет
CHANGE_TRACKING OFF Нет
COMPATIBILITY_LEVEL Зависит от версии ядра СУБД.

Дополнительные сведения см. в разделе ALTER DATABASE (Transact-SQL) о совместимости уровня.		 Да
CONCAT_NULL_YIELDS_NULL OFF Да
CONTAINMENT NONE Нет
CURSOR_CLOSE_ON_COMMIT OFF Да
CURSOR_DEFAULT GLOBAL Да
Состояние базы данных ONLINE Нет
Обновление базы данных READ_WRITE Нет
Доступ пользователя к базе данных MULTI_USER Нет
DATE_CORRELATION_OPTIMIZATION OFF Да
DB_CHAINING ON Нет
DELAYED_DURABILITY DISABLED

Независимо от этого параметра, отложенная устойчивость всегда включена на tempdb.		 Да
ENCRYPTION OFF Нет
MIXED_PAGE_ALLOCATION OFF Нет
NUMERIC_ROUNDABORT OFF Да
PAGE_VERIFY CHECKSUM для новых установок SQL Server

Существующее PAGE_VERIFY значение может храниться при обновлении экземпляра SQL Server.		 Да
PARAMETERIZATION SIMPLE Да
QUOTED_IDENTIFIER OFF Да
READ_COMMITTED_SNAPSHOT OFF Нет
RECOVERY SIMPLE Нет
RECURSIVE_TRIGGERS OFF Да
Брокер услуг ENABLE_BROKER Да
TARGET_RECOVERY_TIME 60 Да
TEMPORAL_HISTORY_RETENTION ON Да
TRUSTWORTHY OFF Нет

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

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

В Базе данных SQL Azure некоторые аспекты поведения и конфигурации tempdb отличаются от SQL Server.

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

Объекты в tempdb, включая представления каталога и динамические административные представления (DMV), доступны через межбазовую ссылку на базу данных tempdb. Например, можно запросить представление sys.database_files:

SELECT file_id,
       type_desc,
       name,
       size,
       max_size,
       growth
FROM tempdb.sys.database_files;

Глобальные временные таблицы в базе данных SQL Azure имеют область видимости, ограниченную базой данных. Дополнительные сведения см. в статье Глобальные временные таблицы с областью действия базы данных в базе данных SQL Azure.

Чтобы узнать больше о размерах в базе данных Azure SQL, см. информацию: tempdb

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

В Управляемом экземпляре SQL Azure некоторые аспекты поведения tempdb и конфигурации по умолчанию отличаются от SQL Server.

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

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

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

tempdb в базе данных SQL в Fabric

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

Аналогично базе данных Azure SQL, глобальные временные таблицы в SQL-базе данных в Microsoft Fabric имеют область действия базы данных. Дополнительные сведения см. в разделе Глобальные временные таблицы с областью действия базы данных в Azure SQL Database.

Ограничения

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

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

Разрешения

Любой пользователь может создавать временные объекты в tempdb.

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

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

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

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

  • Если возможно, используйте мгновенная инициализация файлов для повышения производительности операций роста файлов данных.
    • Начиная с SQL Server 2022 (16.x), события роста файла журнала транзакций до 64 МБ также могут воспользоваться мгновенной инициализацией файла. Дополнительные сведения см. в разделах мгновенной инициализации файла ижурнала транзакций.
  • Заранее выделите место для всех файлов tempdb, установив для файла размер, достаточный для обеспечения обычной рабочей нагрузки в среде. Предварительное выделение предотвращает слишком частое автоматическое увеличение tempdb, что может негативно повлиять на производительность.
  • Файлы в базе данных tempdb должны быть настроены для автоматического увеличения, чтобы обеспечить пространство во время незапланированных событий роста.
  • Разделение tempdb на несколько файлов данных равного размера может повысить эффективность операций, использующих tempdb.
    • Чтобы избежать дисбаланса распределения данных, файлы данных должны иметь одинаковый начальный размер и параметры роста, так как ядро СУБД использует алгоритм пропорциональной заливки, который способствует выделению в файлах с большим объемом свободного места.
    • Установите разумный размер прироста файла, например 64 МБ, и сделайте одинаковым прирост для всех файлов данных, чтобы предотвратить дисбаланс роста.

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

SELECT name AS file_name,
       type_desc AS file_type,
       size * 8.0 / 1024 AS size_mb,
       max_size * 8.0 / 1024 AS max_size_mb,
       CAST(IIF(max_size = 0, 0, 1) AS bit) AS is_autogrowth_enabled,
       CASE WHEN growth = 0 THEN growth
            WHEN growth > 0 AND is_percent_growth = 0 THEN growth * 8.0 / 1024
            WHEN growth > 0 AND is_percent_growth = 1 THEN growth
       END
       AS growth_increment_value,
       CASE WHEN growth = 0 THEN 'Autogrowth is disabled.'
            WHEN growth > 0 AND is_percent_growth = 0  THEN 'Megabytes'
            WHEN growth > 0 AND is_percent_growth = 1  THEN 'Percent'
       END
       AS growth_increment_value_unit
FROM tempdb.sys.database_files;

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

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

Примечание.

Чтобы повысить производительность, отложенная устойчивость всегда включена в tempdb, даже если для параметра базы данных DELAYED_DURABILITY установлено на DISABLED. Так как tempdb создается при запуске, он не проходит процесс восстановления и не обеспечивает гарантию устойчивости.

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

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

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

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

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

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

  • Ядро СУБД не использует параметр FILE_FLAG_WRITE_THROUGH при открытии tempdb файлов, чтобы обеспечить максимальную пропускную способность диска. Так как tempdb повторно создается при запуске, этот параметр не требуется для обеспечения устойчивости данных. Дополнительную информацию о FILE_FLAG_WRITE_THROUGH смотрите в разделе «Ведение журналов и алгоритмы хранения данных», которые повышают надежность данных в SQL Server.
  • оптимизированные для памяти метаданные 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)

Метаданные TempDB, оптимизированные для памяти

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

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

Совет

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

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

SELECT OBJECT_NAME(dpi.object_id, dpi.database_id) AS system_table_name,
       COUNT(DISTINCT(r.session_id)) AS session_count
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS prc
CROSS APPLY sys.dm_db_page_info(prc.db_id, prc.file_id, prc.page_id, 'LIMITED') AS dpi
WHERE dpi.database_id = 2
      AND dpi.object_id IN (3, 9, 34, 40, 41, 54, 55, 60, 74, 75)
      AND UPPER(r.wait_type) LIKE N'PAGELATCH[_]%'
GROUP BY dpi.object_id, dpi.database_id;

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

Примечание.

В настоящее время функция метаданных TempDB, оптимизированная для памяти, недоступна в Базе данных SQL Azure, базе данных SQL в Microsoft Fabric и Управляемом экземпляре SQL Azure.

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

В следующих разделах приведены действия по включению, настройке, проверке и отключению функции метаданных TempDB, оптимизированной для памяти.

Включить

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

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Для получения дополнительной информации см. раздел ALTER SERVER. Чтобы это изменение конфигурации вступило в силу, нужно перезапустить службу.

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

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

Если возвращаемое значение равно 1, а после включения функции произошла перезагрузка, то эта функция включена.

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

Привязка к пулу ресурсов для ограничения использования памяти

Чтобы защитить сервер от потенциального переполнения памяти, рекомендуется привязать tempdb к пулу ресурсов, который ограничивает объем памяти, потребляемый метаданными TempDB, оптимизированными для работы с памятью. Следующий пример скрипта создает пул ресурсов и устанавливает максимальный объем памяти на 20%, включает регулятор ресурсов, и привязывает tempdb к пулу ресурсов.

В этом примере используется 20% в качестве ограничения памяти для демонстрационных целей. Оптимальное значение в вашей среде может быть больше или меньше в зависимости от рабочей нагрузки и может измениться с течением времени, если рабочая нагрузка изменится.

CREATE RESOURCE POOL tempdb_resource_pool
WITH (MAX_MEMORY_PERCENT = 20);

ALTER RESOURCE GOVERNOR RECONFIGURE;

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

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

Проверка привязки пула ресурсов и мониторинг использования памяти

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

WITH resource_pool AS
(
SELECT p.pool_id,
       p.name,
       p.max_memory_percent,
       dp.max_memory_kb,
       dp.target_memory_kb,
       dp.used_memory_kb,
       dp.out_of_memory_count
FROM sys.resource_governor_resource_pools AS p
INNER JOIN sys.dm_resource_governor_resource_pools AS dp
ON p.pool_id = dp.pool_id
)
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS is_tempdb_memory_optimized_metadata_enabled,
       rp.name AS resource_pool_name,
       rp.max_memory_percent,
       rp.max_memory_kb,
       rp.target_memory_kb,
       rp.used_memory_kb,
       rp.out_of_memory_count
FROM sys.databases AS d
LEFT JOIN resource_pool AS rp
ON d.resource_pool_id = rp.pool_id
WHERE d.name = 'tempdb';

Удаление привязки пула ресурсов

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

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Отключить

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

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF;

Ограничения метаданных TempDB, оптимизированных для памяти

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

  • В некоторых случаях вы можете наблюдать, как клерк памяти MEMORYCLERK_XTP вызывает высокую загрузку памяти, что приводит к ошибкам из-за нехватки памяти в вашей рабочей нагрузке.

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

    SELECT SUM(IIF(type = 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS memoryclerk_xtp_pages_mb,
       SUM(IIF(type <> 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS other_pages_mb,
       SUM(committed_target_kb) / 1024. AS committed_target_memory_mb
FROM sys.dm_os_memory_clerks
CROSS JOIN sys.dm_os_sys_info;

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

    Дополнительные сведения см. в разделе метаданных tempdb (HkTempDB), оптимизированных для памяти, об ошибках из-за нехватки памяти.

  • При использовании In-Memory OLTPодна транзакция не может получить доступ к таблицам, оптимизированным для памяти, в нескольких базах данных. Из-за этого любая транзакция чтения или записи, которая включает в себя оптимизированную для памяти таблицу в пользовательской базе данных, также не может получить доступ к системным представлениям tempdb в той же транзакции. Если это происходит, вы получите ошибку 41317:

    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.

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

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

    В следующем примере скрипта возникает эта ошибка при включении метаданных TempDB, оптимизированных для памяти:

    BEGIN TRAN;

-- Create an In-memory OLTP transaction that accesses a system view in tempdb
SELECT name
FROM tempdb.sys.tables;

-- An attempt to create an In-memory OLTP transaction in the user database fails
INSERT INTO <user database>.<schema>.<memory-optimized table>
VALUES (1);

COMMIT TRAN;

    Примечание.

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

  • Запросы к представлениям системного каталога всегда используют уровень изоляции READ COMMITTED. Когда метаданные TempDB с оптимизацией для памяти включены, запросы к представлениям системного каталога в tempdb используют уровень изоляции SNAPSHOT. В любом случае подсказки блокировки не учитываются.

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

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

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

Определение подходящего размера для tempdb зависит от многих факторов. Эти факторы включают рабочую нагрузку и используемые функции ядра СУБД.

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

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

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

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

Например, следующий пример скрипта находит:

  • Свободное место в tempdb (не учитывая свободное место на диске, доступное для увеличения tempdb)
  • Пространство, используемое хранилищем версий
  • Пространство, используемое внутренними объектами
  • Пространство, используемое пользовательскими объектами
SELECT SUM(unallocated_extent_page_count) * 8.0 / 1024 AS tempdb_free_data_space_mb,
       SUM(version_store_reserved_page_count) * 8.0 / 1024 AS tempdb_version_store_space_mb,
       SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS tempdb_internal_object_space_mb,
       SUM(user_object_reserved_page_count) * 8.0 / 1024 AS tempdb_user_object_space_mb
FROM tempdb.sys.dm_db_file_space_usage;

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

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

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 выделенное и используемое в настоящее время пространство внутренними и пользовательскими объектами для каждого сеанса и запроса, как для выполняющихся, так и для завершенных задач.

WITH tempdb_space_usage AS
(
SELECT session_id,
       request_id,
       user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
       user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
FROM sys.dm_db_task_space_usage
UNION ALL
SELECT session_id,
       NULL AS request_id,
       user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
       user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - user_objects_deferred_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
FROM sys.dm_db_session_space_usage
)
SELECT session_id,
       COALESCE(request_id, 0) AS request_id,
       SUM(tempdb_allocations_page_count * 8) AS tempdb_allocations_kb,
       SUM(IIF (tempdb_current_page_count >= 0, tempdb_current_page_count, 0) * 8) AS tempdb_current_kb
FROM tempdb_space_usage
GROUP BY session_id, COALESCE (request_id, 0)
ORDER BY session_id, request_id;

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