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 のワークロードは、他のユーザー データベースのワークロードとは異なります。オブジェクトとデータは頻繁に作成および破棄され、非常に高いコンカレンシーが存在します。 マネージド インスタンスごとに 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 のマネージド インスタンスに接続します。

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 のマネージド インスタンスに接続します。

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 ファイルに割り当てられた (0 埋めされた) 領域です。 すべての 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 のマネージド インスタンスに接続します。
2. [オブジェクト エクスプローラー] の [データベース] を展開した後に、[システム データベース] を展開します。
3. tempdb を右クリックして [プロパティ] を選択します。
4. [全般ページ] で、[データベース] の [サイズ] の値を確認して、最大の tempdb サイズを決定します。 -1 という値は、tempdb の最大サイズが無制限であることを意味します。

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

1. SSMS のマネージド インスタンスに接続します。
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 の高度な監視については、Database Watcher に関するページをご覧ください。
• 料金情報については、SQL Database の価格に関する記事を参照してください。