База данных tempdbTempDB Database

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL Azure нетAzure Synapse Analytics (хранилище данных SQL) нетParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Системная база данных TempDB — это глобальный ресурс, доступный всем пользователям, подключенным к экземпляру SQL ServerSQL Server или Базе данных SQL.The TempDB system database is a global resource that is available to all users connected to the instance of SQL ServerSQL Server or connected to SQL Database. База данных tempdb служит для хранения следующих объектов:Tempdb is used to hold:

  • Временные пользовательские объекты, созданные явно, такие как глобальные или локальные временные таблицы и индексы, временные хранимые процедуры, табличные переменные, таблицы, возвращаемые функциями с табличными значениями, и курсоры.Temporary user objects that are explicitly created, such as: global or local temporary tables and indexes, temporary stored procedures, table variables, Tables returned in table-valued functions, or cursors.

  • Внутренние объекты, созданные ядром СУБД. Internal objects that are created by the database engine. К ним относятся следующие объекты.These include:

    • Рабочие таблицы, хранящие промежуточные результаты буферов, курсоры, сортировки и временное хранилище больших объектов (LOB).Work tables to store intermediate results for spools, cursors, sorts, and temporary large object (LOB) storage.
    • рабочие файлы для операций хэш-соединения или статистических хэш-выражений;Work files for hash join or hash aggregate operations.
    • промежуточные результаты сортировки при таких операциях, как создание или перестроение индексов (если указан параметр SORT_IN_TEMPDB), либо определенных запросах GROUP BY, ORDER BY или UNION.Intermediate sort results for operations such as creating or rebuilding indexes (if SORT_IN_TEMPDB is specified), or certain GROUP BY, ORDER BY, or UNION queries.

    Примечание

    Каждый внутренний объект использует минимум девять страниц: страницу IAM и восьмистраничный экстент.Each internal object uses a minimum of nine pages; an IAM page and an eight-page extent. Дополнительные сведения о страницах и экстентах см. в разделе Страницы и экстенты.For more information about pages and extents, see Pages and Extents.

    Важно!

    Отдельные базы данных и эластичные пулы Базы данных SQL Azure поддерживают глобальные временные таблицы и глобальные временные хранимые процедуры, которые хранятся в TempDB и имеют область действия на уровне базы.Azure SQL Database single databases and elastic pools support global temporary tables and global temporary stored procedures that are stored in TempDB and are scoped to the database level. Глобальные временные таблицы и глобальные временные хранимые процедуры являются общими для всех сеансов пользователей в рамках одной базы данных SQL Azure.Global temporary tables and global temporary stored procedures are shared for all users' sessions within the same Azure SQL database. Сеансы пользователей, связанные с другими базами данных SQL Azure, не имеют доступа к глобальным временным таблицам.User sessions from other Azure SQL databases cannot access global temporary tables. Дополнительные сведения см. в разделе Глобальные временные таблицы (база данных SQL Azure) в области базы данных.For more information, see Database scoped global temporary tables (Azure SQL Database). Управляемый экземпляр Базы данных SQL Azure) поддерживает те же временные объекты, что и SQL Server. Azure SQL Database Managed Instance) supports the same temporary objects as does SQL Server. Для отдельных баз данных и эластичных пулов Базы данных SQL Azure применяются только базы master и TempDB.For Azure SQL Database single databases and elastic pools, only master database and TempDB database apply. Дополнительные сведения см. в разделе Что являет собою сервер Базы данных SQL Azure?.For more information, see What is an Azure SQL Database server. Также см. раздел База данных TempDB в отдельных базах и эластичных пулах Базы данных SQL Azure.For a discussion of TempDB in the context of Azure SQL Database single databases and elastic pools, see TempDB Database in Azure SQL Database single databases and elastic pools. Для Управляемого экземпляра Базы данных Azure SQL применяются все системные базы данных.For Azure SQL Database Managed Instance, all system databases apply.

  • Хранилища версий — это коллекции страниц данных, содержащих строки данных, которые необходимы для поддержки возможностей, применяющих управление версиями строк. Version stores, which are a collection of data pages that hold the data rows that are required to support the features that use row versioning. Существует два хранилища версий: общее хранилище версий и хранилище версий построения индексов в сети.There are two version stores: a common version store and an online-index-build version store. Хранилища версий содержат следующее:The version stores contain:

    • версии строк, сформированные транзакциями изменения данных в базе данных, в которой используются транзакции изоляции моментальных снимков с зафиксированным чтением и транзакции изоляции моментальных снимков;Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
    • версии строк, создаваемые транзакциями изменения данных для таких функций, как операции с индексами в сети, функции режима MARS и триггеры AFTER.Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Операции в TempDB в минимальном объеме записываются в журнал, что позволяет откатывать транзакции.Operations within TempDB are minimally logged so that transactions can be rolled back. TempDB создается заново при каждом запуске SQL ServerSQL Server, чтобы система всегда запускалась с чистой копией базы. TempDB is re-created every time is started so that the system always starts with a clean copy of the database. Временные таблицы и хранимые процедуры удаляются автоматически при отключении, и при выключении системы нет активных соединений.Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Поэтому сохранять что-либо в TempDB между сеансами SQL ServerSQL Server не требуется.Therefore, there is never anything in TempDB to be saved from one session of SQL ServerSQL Server to another. Операции резервного копирования и восстановления для TempDB недопустимы.Backup and restore operations are not allowed on TempDB.

Физические свойства TempDB в SQL ServerPhysical Properties of TempDB in SQL Server

В следующей таблице приведены начальные значения конфигурации для файлов журналов и данных TempDB в SQL Server, основанные на значениях по умолчанию для шаблона базы данных.The following table lists the initial configuration values of the TempDB data and log files in SQL Server, which are based on the defaults for the Model database. Размеры этих файлов могут немного изменяться в зависимости от выпуска SQL ServerSQL Server.The sizes of these files may vary slightly for different editions of SQL ServerSQL Server.

ФайлFile Логическое имяLogical name Физическое имяPhysical name Начальный размерInitial size Увеличение размера файловFile growth
Первичные данныеPrimary data tempdevtempdev tempdb.mdftempdb.mdf 8 МБ8 megabytes Автоматическое увеличение на 64 МБ до заполнения диска.Autogrow by 64 MB until the disk is full
Вторичные файлы данных*.Secondary data files* temp#temp# tempdb_mssql_ # .ndftempdb_mssql_#.ndf 8 МБ8 megabytes Автоматическое увеличение на 64 МБ до заполнения диска.Autogrow by 64 MB until the disk is full
ЖурналLog templogtemplog templog.ldftemplog.ldf 8 МБ8 megabytes Автоматическое увеличение на 64 МБ до максимального размера в 2 ТБ.Autogrow by 64 megabytes to a maximum of 2 terabytes

* Количество файлов зависит от числа (логических) процессоров на компьютере.*The number of files depends on the number of (logical) processors on the machine. Как правило, если число логических процессоров меньше или равно восьми, используйте равное ему число файлов данных.As a general rule, if the number of logical processors is less than or equal to eight, use the same number of data files as logical processors. Если число логических процессоров больше восьми, используйте восемь файлов данных, а затем, если состязание сохраняется, увеличивайте число файлов данных на значение, кратное 4, пока состязание не уменьшится до приемлемого уровня, или внесите изменения в рабочую нагрузку или код.If the number of logical processors is greater than eight, use eight data files and then if contention continues, increase the number of data files by multiples of 4 until the contention is reduced to acceptable levels or make changes to the workload/code.

Примечание

Количество файлов данных по умолчанию основано на общих рекомендациях, приведенных в статье KB 2154845.The default value for the number of data files is based on the general guidelines in KB 2154845.

Перемещение файлов журналов и данных TempDB в SQL ServerMoving the TempDB data and log files in SQL Server

Сведения о перемещении файлов журналов и данных TempDB см. в статье Перемещение системных баз данных.To move the TempDB data and log files, see Move System Databases.

Параметры базы данных TempDB в SQL ServerDatabase Options for TempDB in SQL Server

В следующей таблице приведены значения по умолчанию для всех параметров базы данных TempDB и указана возможность их изменения.The following table lists the default value for each database option in the TempDB database and whether the option can be modified. Чтобы просмотреть текущие настройки этих параметров, используйте представление каталога sys.databases .To view the current settings for these options, use the sys.databases catalog view.

Параметр базы данныхDatabase option Значение по умолчаниюDefault value Можно ли изменитьCan be modified
ALLOW_SNAPSHOT_ISOLATIONALLOW_SNAPSHOT_ISOLATION OFFOFF ДаYes
ANSI_NULL_DEFAULTANSI_NULL_DEFAULT OFFOFF ДаYes
ANSI_NULLSANSI_NULLS OFFOFF ДаYes
ANSI_PADDINGANSI_PADDING OFFOFF ДаYes
ANSI_WARNINGSANSI_WARNINGS OFFOFF ДаYes
ARITHABORTARITHABORT OFFOFF ДаYes
AUTO_CLOSEAUTO_CLOSE OFFOFF нетNo
AUTO_CREATE_STATISTICSAUTO_CREATE_STATISTICS ONON ДаYes
AUTO_SHRINKAUTO_SHRINK OFFOFF нетNo
AUTO_UPDATE_STATISTICSAUTO_UPDATE_STATISTICS ONON ДаYes
AUTO_UPDATE_STATISTICS_ASYNCAUTO_UPDATE_STATISTICS_ASYNC OFFOFF ДаYes
CHANGE_TRACKINGCHANGE_TRACKING OFFOFF нетNo
CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL OFFOFF ДаYes
CURSOR_CLOSE_ON_COMMITCURSOR_CLOSE_ON_COMMIT OFFOFF ДаYes
CURSOR_DEFAULTCURSOR_DEFAULT GLOBALGLOBAL ДаYes
Параметры доступности базы данныхDatabase Availability Options ONLINEONLINE

MULTI_USERMULTI_USER

READ_WRITEREAD_WRITE
нетNo

нетNo

нетNo
DATE_CORRELATION_OPTIMIZATIONDATE_CORRELATION_OPTIMIZATION OFFOFF ДаYes
DB_CHAININGDB_CHAINING ONON нетNo
ENCRYPTIONENCRYPTION OFFOFF нетNo
MIXED_PAGE_ALLOCATIONMIXED_PAGE_ALLOCATION OFFOFF нетNo
NUMERIC_ROUNDABORTNUMERIC_ROUNDABORT OFFOFF ДаYes
PAGE_VERIFYPAGE_VERIFY Значение CHECKSUM для новых установок SQL ServerSQL Server.CHECKSUM for new installations of SQL ServerSQL Server.

Значение NONE для обновлений SQL ServerSQL Server.NONE for upgrades of SQL ServerSQL Server.
ДаYes
PARAMETERIZATIONPARAMETERIZATION ПРОСТОЙSIMPLE ДаYes
QUOTED_IDENTIFIERQUOTED_IDENTIFIER OFFOFF ДаYes
READ_COMMITTED_SNAPSHOTREAD_COMMITTED_SNAPSHOT OFFOFF нетNo
RECOVERYRECOVERY ПРОСТОЙSIMPLE нетNo
RECURSIVE_TRIGGERSRECURSIVE_TRIGGERS OFFOFF ДаYes
Параметры компонента Service BrokerService Broker Options ENABLE_BROKERENABLE_BROKER ДаYes
TRUSTWORTHYTRUSTWORTHY OFFOFF нетNo

Описание этих баз данных см. в статье Параметры ALTER DATABASE SET (Transact-SQL).For a description of these database options, see ALTER DATABASE SET Options (Transact-SQL).

База данных TempDB в Базе данных SQLTempDB database in SQL Database

Размеры TempDB для уровней служб на основе DTUTempDB sizes for DTU-based service tiers

SLOSLO Максимальный размер файлов данных TempDB (ГБ)Max TempDB Data File Size (GBs) Число файлов данных TempDB# of TempDB data files Максимальный размер данных TempDB (ГБ)Max TempDB data size (GB)
BasicBasic 1313 11 1313
S0S0 1313 11 1313
S1S1 1313 11 1313
S2S2 1313 11 1313
S3S3 3232 11 3232
S4S4 3232 22 6464
S6S6 3232 33 9696
S7S7 3232 66 192192
S9S9 3232 1212 384384
S12S12 3232 1212 384384
P1P1 1313 1212 156156
P2P2 1313 1212 156156
P4P4 1313 1212 156156
P6P6 1313 1212 156156
P11P11 1313 1212 156156
P15P15 1313 1212 156156
Эластичные пулы уровня "Премиум" (все конфигурации DTU)Premium Elastic Pools (all DTU configurations) 1313 1212 156156
Эластичные пулы ценовой категории "Стандартный" (S0–S2)Standard Elastic Pools (S0-S2) 1313 1212 156156
Эластичные пулы ценовой категории "Стандартный" (S3 и выше)Standard Elastic Pools (S3 and above) 3232 1212 384384
Эластичные пулы уровня "Базовый" (все конфигурации DTU)Basic Elastic Pools (all DTU configurations) 1313 1212 156156

Размеры TempDB для уровней служб на основе виртуальных ядерTempDB sizes for vCore-based service tiers

См. пределы для ресурсов на основе виртуальных ядерSee vCore-based resource limits

ОграниченияRestrictions

В базе данных TempDB невозможно выполнять следующие операции:The following operations cannot be performed on the TempDB database:

  • добавление файловых групп;Adding filegroups
  • резервное копирование и восстановление базы данных;Backing up or restoring the database
  • Изменение параметров сортировки.Changing collation. (параметрами сортировки по умолчанию являются параметры сортировки сервера);The default collation is the server collation
  • Изменение владельца базы данных.Changing the database owner. (владельцем TempDB является sa); TempDB is owned by sa
  • Создание моментального снимка базы данныхCreating a database snapshot
  • удаление базы данных;Dropping the database
  • удаление пользователя guest из базы данных;Dropping the guest user from the database.
  • включение отслеживания измененных данных;Enabling change data capture
  • участие в зеркальном отображении базы данных;Participating in database mirroring
  • удаление первичной файловой группы, первичного файла данных или файла журнала;Removing the primary filegroup, primary data file, or log file
  • переименование базы данных или первичной файловой группы;Renaming the database or primary filegroup
  • выполнение инструкции DBCC CHECKALLOC;Running DBCC CHECKALLOC
  • выполнение инструкции DBCC CHECKCATALOG;Running DBCC CHECKCATALOG
  • перевод базы данных в режим "вне сети" (OFFLINE);Setting the database to OFFLINE
  • перевод базы данных или первичной файловой группы в режим "только для чтения" (READ_ONLY).Setting the database or primary filegroup to READ_ONLY

РазрешенияPermissions

Любой пользователь может создавать временные объекты в TempDB.Any user can create temporary objects in TempDB. Если не предоставлены какие-либо дополнительные разрешения, то пользователи могут производить доступ только к тем объектам, которыми они владеют.Users can only access their own objects, unless they receive additional permissions. Существует возможность отменить разрешение на подключение к TempDB, чтобы пользователь не мог работать с этой базой, но делать это не рекомендуется, так как TempDB необходима для некоторых стандартных операций.It is possible to revoke the connect permission to TempDB to prevent a user from using TempDB, but is not recommended as some routine operations require the use of TempDB.

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

Размер и физическое размещение базы данных TempDB может влиять на производительность системы.The size and physical placement of the TempDB database can affect the performance of a system. Например, если для TempDB установлен слишком малый размер, часть системной нагрузки по обработке может приходиться на автоматическое увеличение TempDB до размера, который необходим для поддержки рабочей нагрузки при каждом перезапуске экземпляра SQL ServerSQL Server.For example, if the size that is defined for TempDB is too small, part of the system-processing load may be taken up with auto growing TempDB to the size required to support the workload every time you restart the instance of SQL ServerSQL Server.

Если возможно, используйте мгновенную инициализацию файлов базы данных, чтобы улучшить производительность операций увеличения файлов данных.If possible, use database instant file initialization to improve the performance of data file grow operations.

Чтобы заранее выделить место для всех файлов TempDB, задайте достаточно большое значение размера файлов для типичных рабочих нагрузок в среде.Preallocate space for all TempDB files by setting the file size to a value large enough to accommodate the typical workload in the environment. Предварительное выделение позволяет избежать слишком частого расширения TempDB, способного повлиять на производительность.Preallocation prevents TempDB from expanding too frequently, which affects performance. Для базы данных TempDB должно быть установлено автоувеличение, но все равно следует заранее увеличить место на диске для незапланированных исключений.The TempDB database should be set to autogrow, but this should be used to increase disk space for unplanned exceptions.

Файлы данных внутри каждой файловой группы должны иметь одинаковый размер, поскольку SQL ServerSQL Server использует алгоритм пропорционального заполнения, который повышает вероятность выделения памяти в файлах с большим объемом свободного пространства.Data files should be of equal size within each filegroup, as SQL ServerSQL Server uses a proportional-fill algorithm that favors allocations in files with more free space. Разделение TempDB на множество файлов данных равного размера обеспечивает эффективное выполнение использующих TempDB операций с высокой степенью параллелизма.Dividing TempDB into multiple data files of equal size provides a high degree of parallel efficiency in operations that use TempDB.

Установите достаточный шаг приращения для увеличения размера файлов базы данных TempDB.Set the file growth increment to a reasonable size to avoid the TempDB database files from growing by too small a value. Если увеличение размера файлов будет слишком малым по сравнению с объемом записываемых в TempDB данных, база TempDB может постоянно требовать расширения, что скажется на производительности.If the file growth is too small, compared to the amount of data that is being written to TempDB, TempDB may have to constantly expand and affect performance.

Чтобы проверить текущий размер и параметры увеличения TempDB, используйте следующий запрос:To check current TempDB size and growth parameters, use the following query:

 SELECT name AS FileName,
    size*1.0/128 AS FileSizeInMB,
    CASE 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 AS 'GrowthValue',
    'GrowthIncrement' =
        CASE
            WHEN growth = 0 THEN 'Size is fixed.'
            WHEN growth > 0 AND is_percent_growth = 0
                THEN 'Growth value is in 8-KB pages.'
            ELSE 'Growth value is a percentage.'
        END
FROM tempdb.sys.database_files;
GO

Используйте базу данных TempDB с быстрой подсистемой ввода-вывода.Put the TempDB database on a fast I/O subsystem. Если имеется много непосредственно присоединенных дисков, то используйте чередование дисков.Use disk striping if there are many directly attached disks. Отдельные файлы данных TempDB или их группы не обязательно должны располагаться на разных дисках или шпинделях, если только у вас не возникают какие-то узкие места в подсистеме ввода-вывода.Individual or groups of TempDB data files do not necessarily need to be on different disks or spindles unless you are also encountering I/O bottlenecks.

Размещайте TempDB на дисках, которые не используются пользовательскими базами данных.Put the TempDB database on disks that differ from those that are used by user databases.

Улучшения производительности TempDB для SQL ServerPerformance improvements in TempDB for SQL Server

Начиная с версии SQL Server 2016 (13.x);SQL Server 2016 (13.x), производительность TempDB дополнительно оптимизирована следующим образом:Starting with SQL Server 2016 (13.x);SQL Server 2016 (13.x), TempDB performance is further optimized in the following ways:

  • Временные таблицы и табличные переменные кэшируются.Temporary tables and table variables are cached. Кэширование позволяет операциям по удалению и созданию временных объектов выполняться очень быстро и снижает число состязаний из-за выделения страниц.Caching allows operations that drop and create the temporary objects to execute very quickly and reduces page allocation contention.
  • Усовершенствован протокол кратковременных блокировок выделения страниц для снижения количества используемых кратковременных блокировок UP (обновление).Allocation page latching protocol is improved to reduce the number of UP (update) latches that are used.
  • Снижены затраты ресурсов на ведение журнала TempDB — уменьшено потребление пропускной способности подсистемы ввода-вывода файлом журнала TempDB.Logging overhead for TempDB is reduced to reduce disk I/O bandwidth consumption on the TempDB log file.
  • Программа установки добавляет множество файлов данных TempDB при установке нового экземпляра.Setup adds multiple TempDB data files during a new instance installation. Эту задачу можно выполнить с помощью нового элемента управления для ввода в пользовательском интерфейсе в разделе Настройка ядра СУБД и с помощью параметра командной строки /SQLTEMPDBFILECOUNT.This task can be accomplished with the new UI input control on the Database Engine Configuration section and a command-line parameter /SQLTEMPDBFILECOUNT. По умолчанию программа установки добавляет столько файлов данных TempDB, сколько имеется логических процессоров, но их может быть не больше восьми.By default, setup adds as many TempDB data files as the logical processor count or eight, whichever is lower.
  • При наличии множества файлов данных TempDB автоматическое увеличение выполняется для всех файлов в одно время и в равном объеме согласно параметрам увеличения.When there are multiple TempDB data files, all files autogrow at same time and by the same amount depending on growth settings. Флаг трассировки 1117 больше не требуется. Trace flag 1117 is no longer required.
  • Для всех операций распределения в TempDB используются единообразные экстенты.All allocations in TempDB use uniform extents. Флаг трассировки 1118 больше не требуется. Trace flag 1118 is no longer required.
  • Для первичной файловой группы свойство AUTOGROW_ALL_FILES включено и не может быть изменено.For the primary filegroup, the AUTOGROW_ALL_FILES property is turned on and the property cannot be modified.

Дополнительные сведения об улучшениях производительности TempDB см. в следующей статье блога:For more information on performance improvements in TempDB, see the following blog article:

TEMPDB — Files and Trace Flags and Updates, Oh My! (TEMPDB — файлы, флаги трассировки и обновления) TEMPDB - Files and Trace Flags and Updates, Oh My!

Оптимизированные для памяти метаданные TempDBMemory-Optimized TempDB Metadata

Состязание метаданных TempDB всегда было узким местом для масштабируемости многих рабочих нагрузок, выполняющихся в SQL ServerSQL Server.TempDB metadata contention has historically been a bottleneck to scalability for many workloads running on SQL ServerSQL Server. В SQL Server 2019 (15.x)SQL Server 2019 (15.x) появилась новая функция из семейства функций для выполняющейся в памяти базы данных: оптимизированные для памяти метаданные TempDB. В сущности это позволяет устранить узкие места и обеспечить новый уровень масштабируемости для рабочих нагрузок, активно использующих TempDB. introduces a new feature that is part of the In-Memory Database feature family, memory-optimized TempDB metadata, which effectively removes this bottleneck and unlocks a new level of scalability for TempDB-heavy workloads. В SQL Server 2019 (15.x)SQL Server 2019 (15.x) системные таблицы, связанные с управлением метаданными временных таблиц, можно переместить в неустойчивые таблицы без кратковременной блокировки, оптимизированные для памяти.In SQL Server 2019 (15.x)SQL Server 2019 (15.x), the system tables involved in managing temporary table metadata can be moved into latch-free non-durable memory-optimized tables.

Просмотрите это 7-минутное видео, чтобы получить общие сведения о том, как и когда следует использовать метаданные TempDB, оптимизированные для операций в памяти:Watch this 7-minute video for an overview of how and when to use memory optimized TempDB metadata:

Чтобы согласиться на эту новую функцию, используйте следующий скрипт:In order to opt-in to this new feature, use the following script:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON 

Чтобы это изменение конфигурации вступило в силу, нужно перезапустить службу.This configuration change requires a restart of the service to take effect.

У этой реализации имеются некоторые ограничения, которые нужно учитывать:There are some limitations with this implementation that are important to note:

  1. Включение и отключение функции не является динамическим.Toggling the feature on and off is not dynamic. Из-за внутренних изменений, которые необходимо внести в структуру TempDB, для включения или отключения этой функции требуется перезапуск.Because of the intrinsic changes that need to be made to the structure of TempDB, a restart is required to either enable or disable the feature.

  2. Отдельная транзакция не может обратиться к оптимизированным для памяти таблицам в более чем одной базе данных.A single transaction may not access memory-optimized tables in more than one database. Это означает, что все транзакции, связанные с оптимизированной для памяти таблицей в пользовательской базе данных, не смогут обратиться к системным представлениям TempDB в той же транзакции.This means that any transactions that involve a memory-optimized table in a user database will not be able to access TempDB system views in the same transaction. Если вы попытаетесь обратиться к системным представлениям TempDB в той же транзакции, где участвует оптимизированная для памяти таблица в пользовательской базе данных, произойдет следующая ошибка:If you attempt to access TempDB system views in the same transaction as a memory-optimized table in a user database, you will receive the following error:

    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.
    

    ПримерExample:

    BEGIN TRAN
    SELECT *
    FROM tempdb.sys.tables  -----> Creates a user In-Memory OLTP Transaction on Tempdb
    INSERT INTO <user database>.<schema>.<mem-optimized table>
    VALUES (1)  ----> Attempts to create user In-Memory OLTP transaction but will fail
    COMMIT TRAN
    
  3. Запросы к таблицам, оптимизированным для памяти, не поддерживают указания блокировки и изоляции, поэтому запросы к представлениям каталога оптимизированной для памяти TempDB не будут учитывать указания блокировки и изоляции.Queries against memory-optimized tables do not support locking and isolation hints, so queries against memory-optimized TempDB catalog views will not honor locking and isolation hints. Как и в случае с другими системными представлениями каталога в SQL ServerSQL Server, все транзакции для системных представлений будут находиться в изоляции READ COMMITTED (или READ COMMITTED SNAPSHOT в данном случае).As with other system catalog views in SQL ServerSQL Server, all transactions against system views will be in READ COMMITTED (or in this case READ COMMITTED SNAPSHOT) isolation.

  4. При включении оптимизированных для памяти метаданных TempDB индексы columnstore невозможно создавать во временных таблицах. Columnstore indexes cannot be created on temporary tables when Memory-Optimized TempDB Metadata is enabled.

  5. В связи с ограничением на индексы columnstore использование системной хранимой процедуры sp_estimate_data_compression_savings с параметром сжатия данных COLUMNSTORE или COLUMNSTORE_ARCHIVE не поддерживается, если включены оптимизированные для памяти метаданные TempDB.Due to the limitation on columnstore indexes, use of the sp_estimate_data_compression_savings system stored procedure with the COLUMNSTORE or COLUMNSTORE_ARCHIVE data compression parameter is not supported when Memory-Optimized TempDB Metadata is enabled.

Примечание

Эти ограничения действуют только при ссылке на системные представления TempDB, при необходимости вы сможете создать временную таблицу в той же транзакции, где обращаетесь к оптимизированной для памяти таблице в пользовательской базе данных.These limitations only apply when referencing TempDB system views, you will be able to create a temp table in the same transaction as you access a memory-optimized table in a user database if desired.

Вы можете проверить, является ли TempDB оптимизированной для памяти, используя следующую команду T-SQL:You can verify whether or not TempDB is memory-optimized by using the following T-SQL command:

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized')

Если не удается запустить сервер по какой-либо причине после включения оптимизированных для памяти метаданных TempDB, можно обойти эту функцию, запустив SQL Server в минимальной конфигурации с помощью параметра запуска -f.If the server fails to start for any reason after enabling Memory-Optimized TempDB Metadata, you can bypass the feature by starting the SQL Server with minimal configuration using the -f startup option. Это позволит отключить функцию, а затем перезапустить SQL Server в нормальном режиме.This will enable you to disable the feature and then restart SQL Server in normal mode.

Планирование ресурсов для TempDB в SQL ServerCapacity Planning for TempDB in SQL Server

Определение требуемого размера TempDB в рабочей среде SQL ServerSQL Server зависит от многих факторов.Determining the appropriate size for TempDB in a SQL ServerSQL Server production environment depends on many factors. Как описано выше в этой статье, эти факторы включают текущую рабочую нагрузку и используемые компоненты SQL ServerSQL Server.As described previously in this article, these factors include the existing workload and the SQL ServerSQL Server features that are used. Рекомендуется проанализировать текущую рабочую нагрузку, выполнив следующие задачи в среде тестирования SQL Server:We recommend that you analyze the existing workload by performing the following tasks in a SQL Server test environment:

  • Включите для TempDB автоувеличение.Set autogrow on for TempDB.
  • Запускайте отдельные запросы или файлы трассировки рабочей нагрузки и следите за использованием диска базой данных TempDB.Execute individual queries or workload trace files and monitor TempDB space use.
  • Выполняйте операции обслуживания индексов, например перестроение индексов, и следите за использованием диска базой данных TempDB.Execute index maintenance operations, such as rebuilding indexes and monitor TempDB space.
  • Используйте значения используемого места на диске из предыдущих шагов для прогнозирования общей рабочей нагрузки, скорректируйте полученное значение с учетом предполагаемой параллельной обработки и задайте соответствующий размер TempDB.Use the space-use values from the previous steps to predict your total workload usage; adjust this value for projected concurrent activity, and then set the size of TempDB accordingly.

Мониторинг использования TempDBHow to Monitor TempDB use

Нехватка места на диске для TempDB может привести к существенным сбоям рабочей среды SQL ServerSQL Server и помешать работающим приложениям завершить операции.Running out of disk space in TempDB can cause significant disruptions in the SQL ServerSQL Server production environment and can prevent applications that are running from completing operations. Для отслеживания места на диске, используемого в файлах TempDB, можно применять динамическое административное представление sys.dm_db_file_space_usage:You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space that is used in the TempDB files:

 -- 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 sys.dm_db_file_space_usage;

-- Determining the Amount 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 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 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 sys.dm_db_file_space_usage;

Кроме того, для мониторинга действий выделения и освобождения страниц в TempDB на уровне сеансов или задач можно использовать динамические административные представления sys.dm_db_session_space_usage и sys.dm_db_task_space_usage.Additionally, to monitor the page allocation or deallocation activity in TempDB at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. Эти представления позволяют выявлять большие запросы, временные таблицы или табличные переменные, которые используют много места на диске для TempDB.These views can be used to identify large queries, temporary tables, or table variables that are using lots of TempDB disk space. Кроме того, предусмотрено несколько счетчиков, которые можно использовать для мониторинга свободного места в базе данных TempDB, а также использующих ее ресурсов.There are also several counters that can be used to monitor the free space that is available in TempDB and also the resources that are using TempDB. Дополнительные сведения см. в следующем разделе.For more information, see the next section.

-- 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;

-- 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 для индексов SORT_IN_TEMPDB Option For Indexes
Системные базы данных System Databases
sys.databases sys.databases
sys.master_files sys.master_files
Перемещение файлов базы данных Move Database Files