Azure SQL Managed Instance の tempdb 設定を構成する

適用対象:Azure SQL Managed Instance

この記事では、Azure SQL Managed Instance の tempdb 設定の構成ついて説明します。

Azure SQL Managed Instance を使用すると、次の構成を行うことができます。

• tempdb ファイルの数
• tempdb ファイルの拡張増分
• 最大 tempdb サイズ

tempdb 設定は、インスタンスの再起動、更新、またはフェールオーバーが発生した場合でも保持されます。

概要

tempdb は、Azure SQL Managed Instance に付属するデフォルトのシステム データベースの 1 つです。 tempdb の構造はその他のユーザー データベース構造と同じです。違いは、tempdb が非持続的ストレージに使用されるため、 トランザクションのログ記録が最小限に抑えられる点です。

tempdb は、削除、デタッチ、オフライン化、名前の変更、復元はできません。 これらの操作のいずれかを試みると、エラーが返されます。 tempdb はサーバー インスタンスの開始ごとに再生成され、前のセッション中に tempdb 内に作成されたオブジェクトがあったとしても、インスタンスの更新管理操作またはフェールオーバー後にサービスが再起動される場合は保持されません。

tempdb のワークロードはその他のユーザー データベースのワークロードとは異なります。オブジェクトとデータは頻繁に作成および破棄され、高いコンカレンシーが存在します。 Managed Instance ごとに 1 つだけの tempdb があります。 複数のデータベースとアプリケーションがインスタンスに接続している場合でも、それらはすべて同じ tempdb データベースを使用します。 サービスが頻繁に使用される tempdb 内にページを割り当てようとすると、競合が発生する可能性があります。 競合の程度によっては、tempdb に関わるクエリと要求が応答しなくなる可能性があります。 これが、tempdb がサービスのパフォーマンスにとって重要な理由です。

tempdb ファイルの数

tempdb データ ファイルの数を増やすと、データ ファイルごとに 1 つ以上の GAM ページと SGAM ページが作成されます。これにより、tempdb のコンカレンシーの向上と、PFC ページ競合の軽減に役立ちます。 ただし、tempdb のデータ ファイルの数を増やすと、その他のパフォーマンスに影響を与える可能性があるため、運用環境内に実装する前に十分にテストしてください。

既定では、Azure SQL Managed Instance は 12 個の tempdb データ ファイルと 1 個の tempdb ログ ファイルを作成しますが、この構成を変更することはできます。

tempdb ファイルの数の変更には、次の制限があります。

• 新しいファイルの論理名では大文字と小文字が区別されず、最大 16 文字でスペースはなしです。
• tempdb ファイルの最大数は 128 です。

注意

新しいファイルを追加した後にサーバーを再起動する必要はありません。ただし、より空に近いファイルはより高い優先順位で書き込まれ、システムが再調整されるまでページを割り当てるラウンドロビン アルゴリズムは機能しません。

SQL Server Management Studio (SSMS) と Transact-SQL (T-SQL) の両方を使用して、Azure SQL Managed Instance 内の tempdb 用のファイル数を変更できます。

SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) を使用して、tempdb ファイルの数を変更できます。 これを行うには、次の手順に従います。

1. SSMS の Managed Instance に接続します。

2. オブジェクト エクスプローラーで、[データベース] を展開した後に、[システム データベース] を展開します。

3. tempdb を右クリックして [プロパティ] を選択します。

4. [ページの選択] で [ファイル] を選択して、既存の tempdb ファイル の数を表示します。

5. ファイルを追加するには、[追加] を選択した後に、行の中で新しいデータ ファイルに関する情報を入力します。

   

6. tempdb ファイルを削除するには、データベース ファイルのリストから削除するファイルを選択した後で、[削除] を選択します。

Transact-SQL (T-SQL)

Transact-SQL (T-SQL) を使用して、既存の tempdb ファイルの数を確認し、ファイルを追加または削除できます。

既存のすべての tempdb ファイルの数をカウントするには、次のコマンドを使用します。

USE tempdb
SELECT COUNT(*) TempDBFiles FROM sys.database_files

tempdb データ ファイルの数のみをカウントするには、次のコマンドを使用します。

USE tempdb
SELECT COUNT(*) TempDBFiles FROM sys.database_files where type = 0

新しい tempdb ファイルを追加するには、次のコマンドを使用します。

ALTER DATABASE tempdb ADD FILE (NAME = 'file_name')

既存の tempdb ファイルを削除するには、次のコマンドを使用します。

ALTER DATABASE tempdb REMOVE FILE [file_name]

拡張増分

tempdb ファイルの拡張は、tempdb を使用する クエリのパフォーマンスに影響を与える可能性があります。 そのため、tempdb データ ファイルの拡張増分が小さすぎると、エクステントの断片化を引き起こす可能性がある一方で、増分が大きすぎると、拡張が遅れるという結果、または拡張を行うのに十分な領域がない場合は、拡張の失敗を招く可能性があります。 tempdb ファイルの拡張増分に最適な値は、ワークロードによって異なります。

SQL Managed Instance のデフォルトの拡張増分は、tempdb データ ファイルの場合は 254 MB、tempdb ログ ファイルの場合は 64 MB ですが、ワークロードに適応するように拡張増分を構成してパフォーマンスのチューニングを行うことができます。

以下、具体例に沿って説明します。

• ファイル拡張パラメーターでは int_growth_increment の単位として、KB、MB、GB、TB、% がサポートされています。
• 拡張増分は、すべての tempdb データ ファイルに対して同じである必要があります。そうでないとページを割り当てるラウンド ロビン アルゴリズムが影響を受ける可能性があるからです。

SQL Server Management Studio (SSMS) と Transact-SQL (T-SQL) の両方を使用して、tempdb ファイルの拡張増分を変更できます。

SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) を使用して、tempdb ファイルの拡張増分を変更できます。 これを行うには、次の手順に従います。

1. SSMS の Managed Instance に接続します。

2. オブジェクト エクスプローラーで、[データベース] を展開した後に、[システム データベース] を展開します。

3. tempdb を右クリックして [プロパティ] を選択します。

4. [ページの選択] で [ファイル] を選択して、既存の tempdb ファイル の数を表示します。

5. データ ファイルの横にある省略記号 (...) を選択して、[自動拡張プロパティの変更] ダイアログ ウィンドウを開きます。

6. [自動拡張を有効にする] の横にあるボックスにチェックを入れた後に、ファイル拡張の値をパーセントまたはメガバイトのどちらかで指定して、自動拡張設定を変更します。

   

7. [OK] を選択して設定を保存します。

Transact-SQL (T-SQL)

次の T-SQL コマンドを使用して、tempdb データ ファイル用の拡張増分を変更します。

ALTER DATABASE tempdb ADD FILE (NAME = 'file_name', FILEGROWTH = int_growth_increment [KB|MB|GB|TB|%])

最大サイズ

tempdbサイズ は、すべての tempdb ファイルの合計サイズです。 tempdb ファイル サイズは、その tempdb ファイルに割り当てられた (ゼロになった) 領域です。 すべての tempdb ファイルの初期ファイル サイズは 16 MB です。これは、インスタンスの再起動時またはフェールオーバー時のすべての tempdb ファイルのサイズです。 1 つの tempdb データ ファイルの使用領域がファイル サイズに達すると、すべての tempdb データ ファイルは、構成された拡張増分によって自動的に拡張されます。

tempdb使用領域 は、すべての tempdb ファイルの使用領域の合計です。 tempdb ファイルの使用領域は、tempdb ファイル サイズのうち 0 以外の情報が入っている分と等しくなります。 tempdb使用領域と tempdb空き領域の合計は、tempdb サイズと同じです。

T-SQL を使用して、tempdb ファイルの現在の使用領域と空き領域を確認できます 。

tempdb データ ファイルの使用領域、空き領域、サイズを取得するには、次のコマンドを実行します。

USE tempdb
SELECT SUM((allocated_extent_page_count)*1.0/128) AS TempDB_used_data_space_inMB, 
	SUM((unallocated_extent_page_count)*1.0/128) AS TempDB_free_data_space_inMB, 
	SUM(total_page_count*1.0/128) AS TempDB_data_size_inMB 
FROM sys.dm_db_file_space_usage

次のスクリーンショットは、この出力の例を示しています。

tempdb ログ ファイルの使用領域、空き領域、サイズを取得するには、次のコマンドを実行します。

USE tempdb
SELECT used_log_space_in_bytes*1.0/1024/1024 AS TempDB_used_log_space_inMB,
     (total_log_size_in_bytes- used_log_space_in_bytes)*1.0/1024/1024 AS TempDB_free_log_space_inMB,
     total_log_size_in_bytes*1.0/1024/1024 AS TempDB_log_size_inMB
FROM sys.dm_db_log_space_usage

次のスクリーンショットは、この出力の例を示しています。

tempdb 最大サイズは、tempdb をそれ以上拡張できない制限です。

SQL Managed Instance の tempdb 最大サイズには、次の制限があります。

• General Purpose サービス レベルでは、tempdb の最大サイズは 24 GB/仮想コア (96 - 1920 GB) に制限され、ログ ファイルは 120 GB です。
• Business Critical サービス レベルでは、tempdb はリソースに関してその他のデータベースと競合するため、予約済みストレージは tempdb とその他のデータベース間で共有されます。 tempdb ログ ファイルの最大サイズは 2 TB です。

tempdb ファイルは、サービス レベルによって、または手動で構成された最大の tempdb ファイル サイズによって許容される制限のどちらかに達するまで拡張されます。

SQL Server Management Studio (SSMS) と Transact-SQL (T-SQL) の両方を使用して、tempdb ファイルの最大サイズを変更できます。

SQL Server Management Studio (SSMS)

SSMS の現在の tempdb 最大サイズを確認するには、次の手順に従います。

1. SSMS の Managed Instance に接続します。
2. オブジェクト エクスプローラーで、[データベース] を展開した後に、[システム データベース] を展開します。
3. tempdb を右クリックして [プロパティ] を選択します。
4. [全般ページ] で、[データベース] の [サイズ] の値を確認して、最大の tempdb サイズを決定します。 -1 という値は、tempdb の最大サイズが無制限であることを意味します。

SSMS の現在の tempdb 最大サイズを変更するには、次の手順に従います。

1. SSMS の Managed Instance に接続します。
2. オブジェクト エクスプローラーで、[データベース] を展開した後に、[システム データベース] を展開します。
3. tempdb を右クリックして [プロパティ] を選択します。
4. [ページの選択] で [ファイル] を選択して、既存の tempdb ファイル の数を表示します。
5. データ ファイルの横にある省略記号 (...) を選択して、[自動拡張プロパティの変更] ダイアログ ウィンドウを開きます。
6. [最大ファイル サイズ] の値を変更することで、tempdb 最大サイズの設定を変更します。
7. [OK] を選択して設定を保存します。

Transact-SQL (T-SQL)

tempdb 最大サイズを確認するには、次のコマンドを実行します。

USE tempdb
SELECT name, max_size FROM sys.database_files

合計 tempdb サイズを MB 単位で取得するには、次のコマンドを実行します。

USE tempdb
SELECT (SUM(size)*1.0/128) AS TempDB_size_InMB FROM sys.database_files

次のスクリーンショットは、実行サンプルでの出力を表示しています。

新しい tempdb データ ファイル用の最大サイズを設定するには、次のコマンドを実行します。

ALTER DATABASE tempdb ADD FILE (NAME = 'file_name', MAXSIZE = int_maxsize[KB|MB|GB|TB])

既存の tempdb ファイルの最大サイズを変更するには、次のコマンドを実行します。

ALTER DATABASE tempdb MODIFY FILE (NAME = file_name, MAXSIZE = int_maxsize[KB|MB|GB|TB])

tempdb の制限

次の表では、さまざまな tempdb 構成設定の制限を定義します。

構成設定	値
tempdb ファイルの論理名	最大 16 文字
tempdb ファイルの数	最大 128 個のファイル
tempdb ファイルの既定数	13 (ログ ファイル 1 個プラス データ ファイル 12 個)
tempdb データ ファイルの初期サイズ	16 MB
tempdb データ ファイルのデフォルトの拡張増分	256 MB
tempdb ログ ファイルの初期サイズ	16 MB
tempdb ログ ファイルのデフォルトの拡張増分	64 MB
初期の tempdb サイズの最大値	-1 (無制限)
tempdb の最大サイズ	ストレージ サイズまで

次のステップ

• 最初のマネージド インスタンスを作成する方法については、クイック スタート ガイドを参照してください。
• 機能比較一覧については、SQL 共通機能に関する記事をご覧ください。
• VNet の構成の詳細については、SQL Managed Instance VNet の構成に関するページを参照してください。
• マネージド インスタンスを作成し、バックアップ ファイルからデータベースを復元するためのクイック スタートについては、マネージド インスタンスの作成に関するページを参照してください。
• Azure Database Migration Service を使用して移行する方法のチュートリアルについては、Database Migration Service を使用した SQL Managed Instance の移行に関するページを参照してください。
• 組み込みのトラブルシューティング インテリジェンスを使用した SQL Managed Instance データベースの高度なパフォーマンス監視については、Azure SQL Analytics を使用した Azure SQL Managed Instance の監視に関するページを参照してください。
• 料金情報については、SQL Database の価格に関する記事を参照してください。