tempdb データベースtempdb database
適用対象:Applies to: SQL ServerSQL Server (サポートされているすべてのバージョン)
SQL ServerSQL Server (all supported versions)
Azure SQL データベースAzure SQL Database
Azure SQL データベースAzure SQL Database
SQL ServerSQL Server (サポートされているすべてのバージョン)
SQL ServerSQL Server (all supported versions)
Azure SQL データベースAzure SQL Database
Azure SQL データベースAzure SQL Database
tempdb
システム データベースは、SQL ServerSQL Server のインスタンスまたは Azure SQL Database に接続しているすべてのユーザーが使用できるグローバル リソースです。The tempdb
system database is a global resource that's available to all users connected to the instance of SQL ServerSQL Server or connected to Azure SQL Database. tempdb
には、次のものが保持されています。tempdb
holds:
明示的に作成された一時的な "ユーザー オブジェクト"。Temporary user objects that are explicitly created. グローバルまたはローカルな一時テーブルおよびインデックス、一時ストアド プロシージャ、テーブル変数、テーブル値関数で返されるテーブル、カーソルなどが含まれます。They include global or local temporary tables and indexes, temporary stored procedures, table variables, tables returned in table-valued functions, and cursors.
データベース エンジンによって作成された "内部オブジェクト"。Internal objects that the database engine creates. それには以下が含まれます。They 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 (ifSORT_IN_TEMPDB
is specified), or certainGROUP BY
,ORDER BY
, orUNION
queries.
各内部オブジェクトでは、少なくとも 9 つのページが使用されます (IAM ページと 8 ページ分のエクステント)。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.
重要
tempdb
に保存され、データベース レベルまで調べられるグローバル一時テーブルとグローバル一時ストアド プロシージャは、Azure SQL Database 単一データベースおよびエラスティック プールによってサポートされています。Azure SQL Database single databases and elastic pools support global temporary tables and global temporary stored procedures that are stored intempdb
and are scoped to the database level.グローバル一時テーブルとグローバル一時ストアド プロシージャは、同じ SQL データベース内のすべてのユーザーのセッションで共有されます。Global temporary tables and global temporary stored procedures are shared for all users' sessions within the same SQL database. 他の SQL データベースからのユーザー セッションは、グローバル一時テーブルにアクセスできません。User sessions from other SQL databases can't access global temporary tables. 詳細については、「Database scoped global temporary tables (Azure SQL Database)」(データベース スコープ グローバル一時テーブル (Azure SQL Database)) を参照してください。For more information, see Database scoped global temporary tables (Azure SQL Database). Azure SQL Managed Instance では、SQL Server と同じ一時オブジェクトがサポートされます。Azure SQL Managed Instance supports the same temporary objects as does SQL Server.
Azure SQL Database の単一データベースとエラスティック プールでは、master データベースと
tempdb
データベースのみが適用されます。For Azure SQL Database single databases and elastic pools, only the master database andtempdb
database apply. 詳細については、Azure SQL Database サーバーの概要に関する記事を参照してください。For more information, see What is an Azure SQL Database server?. Azure SQL Database の単一データベースとエラスティック プールのコンテキストでのtempdb
については、Azure SQL Database の単一データベースとエラスティック プールでの tempdb データベースに関する説明を参照してください。For a discussion oftempdb
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 Managed Instance の場合、すべてのシステム データベースが適用されます。For Azure SQL Managed Instance, all system databases apply.
"バージョン ストア"。これは行のバージョン管理のための機能をサポートするデータ行が保持されるデータ ページのコレクションです。Version stores, which are collections of data pages that hold the data rows that support features for row versioning. 共通バージョン ストアとオンライン インデックス ビルド バージョン ストアの 2 種類があります。There are two types: a common version store and an online-index-build version store. バージョン ストアに保持される内容:The version stores contain:
- 行バージョン管理分離トランザクションまたはスナップショット分離トランザクションを通して
READ COMMITTED
を使用するデータベース内のデータ変更トランザクションによって生成される行バージョン。Row versions that are generated by data modification transactions in a database that usesREAD COMMITTED
through 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), andAFTER
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 SQL ServerSQL Server 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 のあるセッションから別のセッションに保存されるものは何もありません。tempdb
never has anything to be saved from one session of SQL ServerSQL Server to another. tempdb
では、バックアップおよび復元の操作は実行できません。Backup and restore operations are not allowed on tempdb
.
SQL Server での tempdb の物理プロパティPhysical properties of tempdb in SQL Server
次の表は、SQL Server での tempdb
のデータ ファイルとログ ファイルの初期構成値の一覧です。The following table lists the initial configuration values of the tempdb
data and log files in SQL Server. 値は、model
データベースの既定値に基づいています。The values are based on the defaults for the model
database. これらのファイルのサイズは、SQL ServerSQL Server のエディションによって多少異なる場合があります。The sizes of these files might 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 MB8 megabytes | ディスクがいっぱいになるまで 64 MB ずつ自動拡張Autogrow by 64 MB until the disk is full |
セカンダリ データ ファイルSecondary data files | temp#temp# | tempdb_mssql_#.ndftempdb_mssql_#.ndf | 8 MB8 megabytes | ディスクがいっぱいになるまで 64 MB ずつ自動拡張Autogrow by 64 MB until the disk is full |
ログLog | templogtemplog | templog.ldftemplog.ldf | 8 MB8 megabytes | 最大 2 TB まで 64 MB ずつ自動拡張Autogrow by 64 megabytes to a maximum of 2 terabytes |
セカンダリ データ ファイルの数は、コンピューター上の (論理) プロセッサの数に依存します。The number of secondary data files depends on the number of (logical) processors on the machine. 一般的なルールとして、論理プロセッサの数が 8 以下の場合、論理プロセッサと同じ数のデータ ファイルを使用します。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. 論理プロセッサの数が 8 より多い場合は、8 つのデータ ファイルが使用されます。If the number of logical processors is greater than eight, use eight data files. そのとき、競合が続く場合は、競合が許容できるレベルに低下するまでデータ ファイルの数を 4 の倍数分ずつ増やすか、ワークロードまたはコードを変更します。Then if contention continues, increase the number of data files by multiples of four until the contention decreases 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.
SQL Server の tempdb のデータ ファイルとログ ファイルの移動Moving the tempdb data and log files in SQL Server
tempdb
のデータ ファイルとログ ファイルを移動するには、「システム データベースの移動」を参照してください。To move the tempdb
data and log files, see Move system databases.
SQL Server での tempdb のデータベース オプションDatabase 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 | SQL ServerSQL Server の新規インストールの場合は CHECKSUMCHECKSUM for new installations of SQL ServerSQL Server SQL ServerSQL Server のアップグレードの場合は NONENONE for upgrades of SQL ServerSQL Server |
はいYes |
PARAMETERIZATIONPARAMETERIZATION | SIMPLESIMPLE | はいYes |
QUOTED_IDENTIFIERQUOTED_IDENTIFIER | OFFOFF | はいYes |
READ_COMMITTED_SNAPSHOTREAD_COMMITTED_SNAPSHOT | OFFOFF | いいえNo |
RECOVERYRECOVERY | SIMPLESIMPLE | いいえNo |
RECURSIVE_TRIGGERSRECURSIVE_TRIGGERS | OFFOFF | はいYes |
Service Broker のオプションService 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).
SQL Database の tempdb データベースtempdb database in SQL Database
DTU に基づくサービス層の tempdb のサイズtempdb sizes for DTU-based service tiers
サービス レベルの目標Service-level objective | tempdb の最大データ ファイル サイズ (GB)Maximum tempdb data file size (GB) |
tempdb のデータ ファイルの数Number of tempdb data files |
tempdb の最大データ サイズ (GB)Maximum tempdb data size (GB) |
---|---|---|---|
BasicBasic | 13.913.9 | 11 | 13.913.9 |
S0S0 | 13.913.9 | 11 | 13.913.9 |
S1S1 | 13.913.9 | 11 | 13.913.9 |
S2S2 | 13.913.9 | 11 | 13.913.9 |
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 | 13.913.9 | 1212 | 166.7166.7 |
P2P2 | 13.913.9 | 1212 | 166.7166.7 |
P4P4 | 13.913.9 | 1212 | 166.7166.7 |
P6P6 | 13.913.9 | 1212 | 166.7166.7 |
P11P11 | 13.913.9 | 1212 | 166.7166.7 |
P15P15 | 13.913.9 | 1212 | 166.7166.7 |
Basic エラスティック プール (すべての DTU 構成)Basic Elastic Pools (all DTU configurations) | 13.913.9 | 1212 | 166.7166.7 |
Standard エラスティック プール (50 eDTU)Standard Elastic Pools (50 eDTU) | 13.913.9 | 1212 | 166.7166.7 |
Standard エラスティック プール (100 eDTU)Standard Elastic Pools (100 eDTU) | 3232 | 11 | 3232 |
Standard エラスティック プール (200 eDTU)Standard Elastic Pools (200 eDTU) | 3232 | 22 | 6464 |
Standard エラスティック プール (300 eDTU)Standard Elastic Pools (300 eDTU) | 3232 | 33 | 9696 |
Standard エラスティック プール (400 eDTU)Standard Elastic Pools (400 eDTU) | 3232 | 33 | 9696 |
Standard エラスティック プール (800 eDTU)Standard Elastic Pools (800 eDTU) | 3232 | 66 | 192192 |
Standard エラスティック プール (1200 eDTU)Standard Elastic Pools (1200 eDTU) | 3232 | 1010 | 320320 |
Standard エラスティック プール (1600-3000 eDTU)Standard Elastic Pools (1600-3000 eDTU) | 3232 | 1212 | 384384 |
Premium エラスティック プール (すべての DTU 構成)Premium Elastic Pools (all DTU configurations) | 13.913.9 | 1212 | 166.7166.7 |
vCore に基づくサービス層の tempdb のサイズtempdb sizes for vCore-based service tiers
仮想コア ベースのリソース制限に関する記事を参照してください。See vCore-based resource limits.
制限事項Restrictions
tempdb
データベースでは、次の操作を実行できません。The following operations can't 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
の実行。RunningDBCC CHECKALLOC
.DBCC CHECKCATALOG
の実行。RunningDBCC CHECKCATALOG
.- データベースの
OFFLINE
への設定。Setting the database toOFFLINE
. - データベースまたはプライマリ ファイル グループの
READ_ONLY
への設定。Setting the database or primary filegroup toREAD_ONLY
.
アクセス許可Permissions
すべてのユーザーが tempdb
内に一時オブジェクトを作成できます。Any user can create temporary objects in tempdb
. ユーザーは追加の権限を付与されない限り、自分で作成したオブジェクトにしかアクセスできません。Users can access only their own objects, unless they receive additional permissions. tempdb
に対する接続アクセス許可を取り消して、ユーザーが tempdb
を使用できないようにすることができます。It's possible to revoke the connect permission to tempdb
to prevent a user from using tempdb
. 一部のルーチン操作で tempdb
を使用する必要があるため、それはお勧めしません。We don't recommend it because some routine operations require the use of tempdb
.
SQL Server の tempdb のパフォーマンスの最適化Optimizing tempdb performance in SQL Server
tempdb
データベースのサイズと物理的な配置場所は、システムのパフォーマンスに影響を与えることがあります。The size and physical placement of the tempdb
database can affect the performance of a system. たとえば、tempdb
に対して定義されているサイズが小さすぎる場合、SQL ServerSQL Server のインスタンスが再起動されるたびに、tempdb
をワークロードのサポートに必要なサイズまで自動的に拡張することに、システム処理の負荷の一部が占有される可能性があります。For example, if the size that's defined for tempdb
is too small, part of the system-processing load might be taken up with autogrowing tempdb
to the size required to support the workload every time you restart the instance of SQL ServerSQL Server.
可能な場合は、ファイルの瞬時初期化を使用して、データ ファイルの拡張操作のパフォーマンスを向上させます。If possible, use instant file initialization to improve the performance of growth operations for data files.
すべての 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 often, which affects performance. tempdb
データベースは、想定外の例外に対してディスク領域が増加するよう、自動拡張が行われるように設定する必要があります。The tempdb
database should be set to autogrow to increase disk space for unplanned exceptions.
データ ファイルのサイズは、各ファイル グループ内で等しくする必要があります。これは、SQL ServerSQL Server では、より多くの空き領域を持つファイル内の割り当てを優先する比例配分アルゴリズムが使用されているためです。Data files should be of equal size within each filegroup, because 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 prevent 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's being written to tempdb
, tempdb
might have to constantly expand. それにより、パフォーマンスが影響を受けます。That will affect performance.
tempdb
の現在のサイズと拡張パラメーターを確認するには、次のクエリを使用します。To check current size and growth parameters for tempdb
, 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
高速な I/O サブシステムに tempdb
データベースを配置します。Put the tempdb
database on a fast I/O subsystem. 直接アタッチされたディスクが多数ある場合は、ディスク ストライピングを使用します。Use disk striping if there are many directly attached disks. I/O ボトルネックも発生しているのでない限り、個々の tempdb
データ ファイルまたはそのグループを、異なるディスクまたはスピンドルに配置する必要は必ずしもありません。Individual or groups of tempdb
data files don't necessarily need to be on different disks or spindles unless you're also encountering I/O bottlenecks.
ユーザー データベースによって使用されるものとは異なるディスクに、tempdb
データベースを配置します。Put the tempdb
database on disks that differ from the disks that user databases use.
SQL Server の tempdb でのパフォーマンスの強化Performance 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 run very quickly. また、キャッシュによって、ページの割り当てやメタデータの競合も減少します。Caching also reduces page allocation and metadata contention.
- 割り当てページ ラッチ プロトコルが改善され、使用される
UP
(更新) ラッチの回数が減っています。The allocation page latching protocol is improved to reduce the number ofUP
(update) latches that are used. tempdb
のログ記録オーバーヘッドが減少し、tempdb
ログ ファイルのディスク I/O 帯域幅消費が減少しました。Logging overhead fortempdb
is reduced to reduce disk I/O bandwidth consumption on thetempdb
log file.- セットアップによって、新しいインスタンスのインストール中に複数の
tempdb
データ ファイルが追加されます。Setup adds multipletempdb
data files during a new instance installation. このタスクを実行するには、 [データベース エンジンの構成] セクションの新しい UI 入力コントロールと、コマンド ライン パラメーター/SQLTEMPDBFILECOUNT
を使用します。You can accomplish this task by using the new UI input control in the Database Engine Configuration section and the command-line parameter/SQLTEMPDBFILECOUNT
. 既定では、セットアップ時に、論理プロセッサ数または 8 のいずれか小さい方と同数のtempdb
データ ファイルが追加されます。By default, setup adds as manytempdb
data files as the logical processor count or eight, whichever is lower. - 複数の
tempdb
データ ファイルがある場合は、拡張設定に応じて、すべてのファイルが同時に同量ずつ自動拡張されます。When there are multipletempdb
data files, all files autogrow at the same time and by the same amount, depending on growth settings. トレース フラグ 1117 は必須ではなくなりました。Trace flag 1117 is no longer required. tempdb
内のすべての割り当てで単一エクステントが使用されます。All allocations intempdb
use uniform extents. トレース フラグ 1118 は必須ではなくなりました。Trace flag 1118 is no longer required.- プライマリ ファイル グループの場合、
AUTOGROW_ALL_FILES
プロパティはオンにされており、プロパティは変更できません。For the primary filegroup, theAUTOGROW_ALL_FILES
property is turned on and the property can't be modified.
tempdb
でのパフォーマンス向上の詳細については、TEMPDB - ファイルとトレース フラグと更新に関するブログ記事を参照してください。For more information on performance improvements in tempdb
, see the blog article TEMPDB - Files and Trace Flags and Updates, Oh My!.
メモリ最適化 tempdb メタデータMemory-optimized tempdb metadata
tempdb
でのメタデータの競合は、従来、SQL ServerSQL Server 上で実行されている多くのワークロードのスケーラビリティに対するボトルネックになっていました。Metadata contention in tempdb
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 メタデータという新機能が導入されています。introduces a new feature that's part of the in-memory database feature family: memory-optimized tempdb metadata.
この機能により、このボトルネックが実質的に除去され、tempdb の負荷が高いワークロードに新しいレベルのスケーラビリティが提供されます。This feature 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.
メモリ最適化 tempdb メタデータを使用する方法とそのタイミングの概要については、この 7 分間のビデオをご覧ください。Watch this seven-minute video for an overview of how and when to use memory-optimized tempdb metadata:
メモリ最適化 tempdb メタデータの構成と使用Configuring and using memory-optimized tempdb metadata
この新しい機能にオプトインするには、次のスクリプトを使用します。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.
次の T-SQL コマンドを使用して、tempdb
がメモリ最適化かどうかを確認できます。You can verify whether or not tempdb
is memory-optimized by using the following T-SQL command:
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');
メモリ最適化 tempdb
メタデータを有効にした後に、何らかの理由でサーバーの起動に失敗した場合は、 -f スタートアップ オプションを使用して 最小構成で SQL Server インスタンスを開始することで、この機能を回避できます。If the server fails to start for any reason after you enable memory-optimized tempdb
metadata, you can bypass the feature by starting the SQL Server instance with minimal configuration through the -f startup option. その後、この機能を無効にして、通常モードで SQL Server を再起動できます。You can then disable the feature and restart SQL Server in normal mode.
サーバーを潜在的なメモリ不足の状態から保護するために、tempdb
をリソース プールにバインドすることができます。To protect the server from potential out-of-memory conditions, you can bind tempdb
to a resource pool. これは、リソース プールをデータベースにバインドするために通常実行する手順の代わりに ALTER SERVER
コマンドを使用して行います。This is done through the ALTER SERVER
command rather than the steps you would normally follow to bind a resource pool to a database.
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON (RESOURCE_POOL = 'pool_name');
メモリ最適化 tempdb メタデータが既に有効になっている場合でも、この変更を有効にするには再起動も必要です。This change also requires a restart to take effect, even if memory-optimized tempdb metadata is already enabled.
メモリ最適化 tempdb メタデータの制限Memory-optimized tempdb limitations
機能のオンとオフの切り替えは、動的ではありません。Toggling the feature on and off is not dynamic.
tempdb
の構造を根本的に変更する必要があるため、この機能を有効または無効にするには再起動が必要です。Because of the intrinsic changes that need to be made to the structure oftempdb
, a restart is required to either enable or disable the feature.1 つのトランザクションで複数のデータベース内のメモリ最適化テーブルにアクセスすることはできません。A single transaction is not allowed to access memory-optimized tables in more than one database. ユーザー データベース内のメモリ最適化テーブルを使用するトランザクションでは、同じトランザクション内で
tempdb
システム ビューにアクセスすることはできません。Any transactions that involve a memory-optimized table in a user database won't be able to accesstempdb
system views in the same transaction. ユーザー データベース内のメモリ最適化テーブルと同じトランザクションでtempdb
システム ビューにアクセスしようとした場合、次のエラーが返されます。If you try to accesstempdb
system views in the same transaction as a memory-optimized table in a user database, you'll 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 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
カタログ ビューに対するクエリでは、ロックと分離のヒントは適用されません。Queries against memory-optimized tables don't support locking and isolation hints, so queries against memory-optimizedtempdb
catalog views won't 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 inREAD COMMITTED
(or in this case,READ COMMITTED SNAPSHOT
) isolation.メモリ最適化
tempdb
メタデータが有効になっている場合、一時テーブルに列ストア インデックスを作成することはできません。Columnstore indexes can't be created on temporary tables when memory-optimizedtempdb
metadata is enabled.列ストア インデックスでの制限により、メモリ最適化
tempdb
メタデータが有効になっている場合は、COLUMNSTORE
またはCOLUMNSTORE_ARCHIVE
データ圧縮パラメーターを指定してsp_estimate_data_compression_savings
システム ストアド プロシージャを使用することはできません。Due to the limitation on columnstore indexes, use of thesp_estimate_data_compression_savings
system stored procedure with theCOLUMNSTORE
orCOLUMNSTORE_ARCHIVE
data compression parameter is not supported when memory-optimizedtempdb
metadata is enabled.
注意
これらの制限は、tempdb
システム ビューを参照している場合にのみ適用されます。These limitations apply only when you're referencing tempdb
system views. 必要に応じて、ユーザー データベース内のメモリ最適化テーブルにアクセスするときに、同じトランザクションで一時テーブルを作成できます。You can create a temporary table in the same transaction as you access a memory-optimized table in a user database, if desired.
SQL Server での tempdb の容量計画Capacity planning for tempdb in SQL Server
SQL ServerSQL Server 運用環境での tempdb
の適切なサイズを判断するには、多くの要因が関係します。Determining the appropriate size for tempdb
in a SQL ServerSQL Server production environment depends on many factors. 前に説明したように、これらの要因には既存のワークロードや使用されている SQL ServerSQL Server の機能などがあります。As described earlier, 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 fortempdb
.- 個々のクエリまたはワークロード トレース ファイルを実行し、
tempdb
領域の使用を監視する。Run individual queries or workload trace files and monitortempdb
space use. - インデックスの再構築などのインデックス メンテナンス操作を実行し、
tempdb
領域を監視する。Execute index maintenance operations such as rebuilding indexes, and monitortempdb
space. - 前の手順の領域使用の値を使用して、ワークロードの総使用量を予測する。Use the space-use values from the previous steps to predict your total workload usage. 予測される同時実行アクティビティに対してこの値を調整した後、それに応じて
tempdb
のサイズを設定します。Adjust this value for projected concurrent activity, and then set the size oftempdb
accordingly.
tempdb の使用の監視Monitoring tempdb use
tempdb
のディスク領域が不足すると、SQL ServerSQL Server 運用環境で重大な中断が発生する可能性があります。Running out of disk space in tempdb
can cause significant disruptions in the SQL ServerSQL Server production environment. また、実行中のアプリケーションが操作を完了できなくなる場合もあります。It can also prevent applications that are running from completing operations. sys.dm_db_file_space_usage 動的管理ビューを使用して、tempdb
ファイルで使用されているディスク領域を監視できます。You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space that's 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 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 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 動的管理ビューを使用できます。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 help you identify large queries, temporary tables, or table variables that are using lots of tempdb
disk space. また、いくつかのカウンターを使用して、tempdb
で使用できる空き領域と、tempdb
を使用しているリソースを監視することもできます。You can also use several counters to monitor the free space that's available in tempdb
and the resources that are using 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;
-- 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;;
関連コンテンツRelated content
インデックスの SORT_IN_TEMPDB オプション SORT_IN_TEMPDB option for indexes
システム データベース System databases
sys.databases sys.databases
sys.master_files sys.master_files
データベース ファイルの移動Move database files