ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)

適用対象: SQL Server 2016 以降 Azure SQL Database Azure Synapse Analytics Parallel Data Warehouse

このコマンドを使うと、複数のデータベース構成設定を 個別のデータベース レベルで設定できます。

引数セクションの各設定の 適用対象 で示されているように、次の設定は Azure SQL データベース、Azure SQL Managed Instance および SQL Server でサポートされています。

  • プロシージャ キャッシュをクリアします。
  • プライマリ データベースに対して、MAXDOP パラメーターを特定のデータベースに最適な内容に基づいて任意の値 (1、2、...) に設定し、(クエリ レポートなどに) 使用されるすべてのセカンダリ データベースに対して別の値 (0 など) を設定します。
  • データベースに依存しないクエリ オプティマイザーのカーディナリティ推定モデルを互換性レベルに設定します。
  • データベース レベルでパラメーター スニッフィングを有効または無効にします。
  • データベース レベルでのクエリ最適化の修正プログラムを有効または無効にします。
  • データベース レベルで ID キャッシュを有効または無効にします。
  • バッチが初めてコンパイルされるとき、コンパイルしたプラン スタブのキャッシュ保存を有効または無効にします。
  • ネイティブ コンパイル Transact-SQL モジュールの実行統計コレクションを有効または無効にします。
  • ONLINE = 構文に対応している DDL ステートメントの既定のオプションでオンラインの有効/無効を変更します。
  • RESUMABLE = 構文に対応している DDL ステートメントの既定のオプションで再開可能の有効/無効を変更します。
  • インテリジェントなクエリ処理の機能を有効または無効にします。
  • 高速プラン強制を有効または無効にします。
  • グローバル一時テーブルの自動削除機能を有効または無効にします。
  • 軽量クエリ プロファイリング インフラストラクチャを有効または無効にします。
  • 新しい String or binary data would be truncated のエラー メッセージを有効または無効にします。
  • sys.dm_exec_query_plan_stats の最後の実際の実行プランのコレクションを有効または無効にします。
  • 再開可能なインデックス操作を一時停止してから、データベース エンジン エンジンによって自動的に中止されるまでの一時停止される時間を分単位で指定します。
  • 統計の非同期更新で低優先度のロックの待機を有効または無効にします。

この設定は、Azure Synapse Analytics でのみ使用できます。

  • ユーザー データベースの互換性レベルを設定する

リンク アイコン Transact-SQL 構文表記規則

構文

-- Syntax for SQL Server, Azure SQL Database and Azure SQL Managed Instance

ALTER DATABASE SCOPED CONFIGURATION
{
    { [ FOR SECONDARY] SET <set_options>}
}
| CLEAR PROCEDURE_CACHE [plan_handle]
| SET < set_options >
[;]

< set_options > ::=
{
    MAXDOP = { <value> | PRIMARY}
    | LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY}
    | PARAMETER_SNIFFING = { ON | OFF | PRIMARY}
    | QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY}
    | IDENTITY_CACHE = { ON | OFF }
    | INTERLEAVED_EXECUTION_TVF = { ON | OFF }
    | BATCH_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF }
    | BATCH_MODE_ADAPTIVE_JOINS = { ON | OFF }
    | TSQL_SCALAR_UDF_INLINING = { ON | OFF }
    | ELEVATE_ONLINE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }
    | ELEVATE_RESUMABLE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }
    | OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }
    | XTP_PROCEDURE_EXECUTION_STATISTICS = { ON | OFF }
    | XTP_QUERY_EXECUTION_STATISTICS = { ON | OFF }
    | ROW_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF }
    | BATCH_MODE_ON_ROWSTORE = { ON | OFF }
    | DEFERRED_COMPILATION_TV = { ON | OFF }
    | ACCELERATED_PLAN_FORCING = { ON | OFF }
    | GLOBAL_TEMPORARY_TABLE_AUTO_DROP = { ON | OFF }
    | LIGHTWEIGHT_QUERY_PROFILING = { ON | OFF }
    | VERBOSE_TRUNCATION_WARNINGS = { ON | OFF }
    | LAST_QUERY_PLAN_STATS = { ON | OFF }
    | PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES = <time>
    | ISOLATE_SECURITY_POLICY_CARDINALITY  = { ON | OFF }
    | EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS = { ON | OFF }
    | ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = { ON | OFF }
}

重要

Azure SQL データベース と Azure SQL Managed Instance のSQL Server 2019 (15.x) 以降では、一部のオプション名が変更されています。

  • DISABLE_INTERLEAVED_EXECUTION_TVFINTERLEAVED_EXECUTION_TVF に変更しました
  • DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACKBATCH_MODE_MEMORY_GRANT_FEEDBACK に変更しました
  • DISABLE_BATCH_MODE_ADAPTIVE_JOINSBATCH_MODE_ADAPTIVE_JOINS に変更しました
-- Syntax for Azure Synapse Analytics

ALTER DATABASE SCOPED CONFIGURATION
{
    SET <set_options>
}
[;]

< set_options > ::=
{
    DW_COMPATIBILITY_LEVEL = { AUTO | 10 | 20 } 
}

引数

セカンダリの場合

セカンダリ データベースの設定を指定します (すべてのセカンダリ データベースに同じ値を与える必要があります)。

CLEAR PROCEDURE_CACHE [plan_handle]

データベースのプロシージャ (プラン) キャッシュがクリアされ、プライマリとセカンダリの両方で実行することができます。

プラン キャッシュから 1 つのクエリ プランをクリアするクエリ プラン ハンドルを指定します。

適用対象:クエリ プラン ハンドルの指定は、Azure SQL データベース と Azure SQL Managed Instance の SQL Server 2019 (15.x) 以降で使用できます。

MAXDOP = {<value> | PRIMARY } <value>

ステートメントで使用される 並列処理の最大限度 (MAXDOP) 設定の既定値を指定します。 0 が初期設定値であり、サーバー構成が代わりに使用されることを示します。 データベース スコープの MAXDOP は、サーバー レベルで設定されている 並列処理の最大限度 を p_configure によってオーバーライドします (0 に設定されていない限り)。 別の設定を必要とする特定のクエリを調整する目的で、クエリ ヒントでは引き続き、データベース スコープの MAXDOP をオーバーライドできます。 これらすべての設定の上限は、ワークロード グループに設定されている MAXDOP によって決定されます。

MAXDOP オプションを使用すると、並列プラン実行で使用するプロセッサの数を制限できます。 SQL Server は、クエリ、インデックス データ定義言語 (DDL) の操作、並列挿入、オンライン列変更、並行統計コレクション、静的およびキーセット ドリブン カーソルの作成の場合に並列実行プランを検討します。

注意

並列処理の最大限度 (MAXDOP) の制限は タスクごとに設定されます。 この設定は、要求ごとまたはクエリ制限ごとではありません。 つまり、並列クエリ実行中に、1 つの要求で、スケジューラに割り当てられてた複数のタスクを生成することができます。 詳細については、「スレッドおよびタスクのアーキテクチャ ガイド」を参照してください。

インスタンス レベルでこのオプションを設定する方法については、「max degree of parallelism サーバー構成オプションの構成」を参照してください。

注意

Azure SQL データベース では、新しいシングル データベースとエラスティック プール データベースのデータベース スコープ構成 MAXDOP が既定で 8 に設定されています。 MAXDOP は、現在の記事で説明されているように、データベースごとに構成できます。 MAXDOP の最適な構成に関する推奨事項については、「その他のリソース」を参照してください。

ヒント

これをクエリ レベルで行うには、MAXDOP クエリ ヒントを使用します。
これをサーバー レベルで行うには、並列処理の最大限度 (MAXDOP) サーバー構成オプションを使用します。
これをワークロード レベルで行うには、MAX_DOP Resource Governor ワークロード グループ構成オプションを使用します。

PRIMARY

データベースがプライマリにあるとき、セカンダリに対してのみ設定できます。構成はプライマリに設定されている構成になることを示します。 プライマリの構成が変更されると、セカンダリの値も適宜変更されます。セカンダリの値を明示的に設定する必要はありません。 PRIMARY はセカンダリの初期設定です。

LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }

データベースの互換性レベルに関係なく、クエリ オプティマイザーのカーディナリティ推定モデルを SQL Server 2012 以前のバージョンに設定できます。 既定値は OFF であり、クエリ オプティマイザーのカーディナリティ推定モデルがデータベースの互換性レベルに基づいて設定されます。 LEGACY_CARDINALITY_ESTIMATION を ON に設定することは、トレース フラグ 9481 を有効にすることと同じです。

ヒント

これをクエリ レベルで行うには、QUERYTRACEON クエリ ヒントを追加してください。 SQL Server 2016 (13.x) SP1 以降、クエリ レベルでこれを行うには、トレース フラグの代わりに、USE HINT クエリ ヒントを追加してください。

PRIMARY

データベースがプライマリにあるとき、この値はセカンダリでのみ有効になります。すべてのセカンダリのクエリ オプティマイザーのカーディナリティ推定モデル設定がプライマリに設定されている値になることを示します。 クエリ オプティマイザーのカーディナリティ推定モデルの構成がプライマリで変更された場合、セカンダリの値も適宜変更されます。 PRIMARY はセカンダリの初期設定です。

PARAMETER_SNIFFING = { ON | OFF | PRIMARY }

パラメーター スニッフィングを有効にするか無効にします。 既定値は ON です。 PARAMETER_SNIFFING を OFF に設定することは、トレース フラグ 4136 を有効にすることと同じです。

ヒント

クエリ レベルでこれを行う方法については、「OPTIMIZE FOR UNKNOWN クエリ ヒント」を参照してください。 SQL Server 2016 (13.x) SP1 以降、クエリ レベルでこれを行うには、USE HINT クエリ ヒントも利用できます。

PRIMARY

データベースがプライマリにあるとき、この値はセカンダリでのみ有効になります。すべてのセカンダリでこの設定の値がプライマリに設定されている値になることを示します。 パラメーター スニッフィングの使用に関するプライマリの構成が変更されると、セカンダリの値も適宜変更されます。セカンダリの値を明示的に設定する必要はありません。 PRIMARY はセカンダリの既定の設定です。

QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY }

データベースの互換性レベルに関係なく、クエリ最適化修正プログラムを有効または無効にします。 既定値は OFF です。特定のバージョンで利用できる最高の互換性レベルが導入された後に公開されたクエリ最適化修正プログラムが無効になります (RTM 後)。 これを ON に設定することは、トレース フラグ 4199 を有効にすることと同じです。

適用対象:SQL Server (SQL Server 2016 (13.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance

ヒント

これをクエリ レベルで行うには、QUERYTRACEON クエリ ヒントを追加してください。 SQL Server 2016 (13.x) SP1 以降、クエリ レベルでこれを行うには、トレース フラグの代わりに、USE HINT クエリ ヒントを追加してください。

PRIMARY

データベースがプライマリにあるとき、この値はセカンダリでのみ有効になります。すべてのセカンダリでこの設定の値がプライマリに設定されている値になることを示します。 プライマリの構成が変更されると、セカンダリの値も適宜変更されます。セカンダリの値を明示的に設定する必要はありません。 PRIMARY はセカンダリの既定の設定です。

IDENTITY_CACHE = { ON | OFF }

適用対象:SQL Server (SQL Server 2017 (14.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance

データベース レベルで ID キャッシュを有効または無効にします。 既定値は ON です。 ID キャッシュは、ID 列が含まれるテーブルでの INSERT パフォーマンスを改善するために使用されます。 サーバーが突然再起動したか、セカンダリ サーバーにフェールオーバーしたときに ID 列の値に隔たりができることを回避するには、IDENTITY_CACHE オプションを無効にします。 このオプションは、サーバー レベルのみならずデータベース レベルで設定可能という点を除き、既存のトレース フラグ 272 と似ています。

注意

このオプションはプライマリにのみ設定できます。 詳細については、「ID 列」を参照してください。

INTERLEAVED_EXECUTION_TVF = { ON | OFF }

適用対象:SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance

複数ステートメントのテーブル値関数のインターリーブ実行は、データベースの互換性レベル 140 以上を維持しながら、データベースまたはステートメント範囲で有効または無効にできます。 インターリーブ実行は、Azure SQL データベース のアダプティブ クエリ処理の一部の機能です。 詳細については、インテリジェントなクエリ処理に関する記事をご覧ください。

注意

データベース互換性レベルが 130 以下である場合は、このデータベース スコープの構成に影響がありません。

SQL Server 2017 (14.x) のみでの、オプション INTERLEAVED_EXECUTION_TVF には DISABLE _INTERLEAVED_EXECUTION_TVF の古い名前があります。

BATCH_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF }

適用対象:SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance

バッチ モード メモリ許可フィードバックは、データベースの互換性レベル 140 以上を維持しながら、データベース範囲で有効または無効にできます。 バッチ モード メモリ許可フィードバックは、SQL Server 2017 (14.x) で導入されたインテリジェントなクエリ処理の一部の機能です。

注意

データベース互換性レベルが 130 以下である場合は、このデータベース スコープの構成に影響がありません。

BATCH_MODE_ADAPTIVE_JOINS = { ON | OFF }

適用対象:SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance

バッチ モードの適応結合は、データベースの互換性レベル 140 以上を維持しながら、データベース範囲で有効または無効にできます。 バッチ モードの適応結合は、SQL Server 2017 (14.x) で導入されたインテリジェントなクエリ処理の一部の機能です。

注意

データベース互換性レベルが 130 以下である場合は、このデータベース スコープの構成に影響がありません。

TSQL_SCALAR_UDF_INLINING = { ON | OFF }

適用対象:SQL Server (SQL Server 2019 (15.x) 以降) と Azure SQL データベース (機能はパブリック プレビュー段階)

データベースの互換性レベル 150 以上を維持しながら、データベース範囲で T-SQL スカラー UDF のインライン化を有効または無効にできます。 T-SQL スカラー UDF のインライン化は、インテリジェント クエリの処理機能ファミリの一部です。

注意

データベース互換性レベルが 140 以下である場合は、このデータベース スコープの構成に影響がありません。

ELEVATE_ONLINE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }

適用対象:SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance

サポートされている操作からオンラインにエンジンを自動的に昇格させるオプションを選択できます。 既定は OFF であり、ステートメントで指定されない限り、操作はオンラインに昇格されません。 sys.database_scoped_configurations は ELEVATE_ONLINE の現在の値を反映します。 これらのオプションは、オンラインでサポートされている操作にのみ適用されます。

FAIL_UNSUPPORTED

この値のとき、サポートされているすべての DDL 操作が ONLINE に昇格されます。 オンライン実行に対応していない操作は失敗し、警告が出ます。

WHEN_SUPPORTED

この値のとき、ONLINE 対応の操作が昇格されます。 オンライン対応ではない操作はオフラインで実行されます。

注意

ONLINE オプションが指定されたステートメントを送信することで、既定の設定をオーバーライドできます。

ELEVATE_RESUMABLE= { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }

適用対象:SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance

サポートされている操作から再開可能にエンジンを自動的に昇格させるオプションを選択できます。 既定は OFF であり、ステートメントで指定されない限り、操作は再開可能に昇格されません。 sys.database_scoped_configurations は ELEVATE_RESUMABLE の現在の値を反映します。 これらのオプションは、再開可能実行でサポートされている操作にのみ適用されます。

FAIL_UNSUPPORTED

この値のとき、サポートされているすべての DDL 操作が RESUMABLE に昇格されます。 再開可能実行に対応していない操作は失敗し、警告が出ます。

WHEN_SUPPORTED

この値のとき、RESUMABLE 対応の操作が昇格されます。 再開可能対応ではない操作は再開不可能として実行されます。

注意

RESUMABLE オプションが指定されたステートメントを送信することで、既定の設定をオーバーライドできます。

OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }

適用対象:SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance

バッチが初めてコンパイルされるとき、コンパイルしたプラン スタブのキャッシュ保存を有効または無効にします。 既定値は OFF です。 あるデータベースに対してデータベース スコープ構成 OPTIMIZE_FOR_AD_HOC_WORKLOADS を有効にすると、バッチを初めてコンパイルしたとき、コンパイル済みのプラン スタブがキャッシュに保存されます。 プラン スタブのメモリ領域は、完全なコンパイル済みプランのサイズに比べて小さくなります。 バッチが再度コンパイルまたは実行されると、コンパイル済みプラン スタブは削除され、完全なコンパイル済みプランと置換されます。

XTP_PROCEDURE_EXECUTION_STATISTICS = { ON | OFF }

適用対象: Azure SQL データベース および Azure SQL Managed Instance

現在のデータベース内のすべてのネイティブ コンパイル T-SQL モジュールに対し、モジュール レベルで実行統計コレクションを有効また無効にします。 既定値は OFF です。 実行統計は sys.dm_exec_procedure_stats に反映されます。

このオプションが ON の場合、または統計コレクションが sp_xtp_control_proc_exec_stats によって有効化されている場合は、ネイティブ コンパイル T-SQL モジュールのモジュール レベルの実行統計が収集されます。

XTP_QUERY_EXECUTION_STATISTICS = { ON | OFF }

適用対象: Azure SQL データベース および Azure SQL Managed Instance

現在のデータベース内のすべてのネイティブ コンパイル T-SQL モジュールに対し、ステートメント レベルで実行統計コレクションを有効また無効にします。 既定値は OFF です。 実行統計は、sys.dm_exec_query_stats およびクエリ ストアに反映されます。

このオプションが ON の場合、または統計コレクションが sp_xtp_control_query_exec_stats によって有効化されている場合は、ネイティブ コンパイル T-SQL モジュールのステートメント レベルの実行統計が収集されます。

ネイティブ コンパイル Transact-SQL モジュールのパフォーマンスの監視の詳細については、「ネイティブ コンパイル ストアド プロシージャのパフォーマンスの監視」を参照してください。

ROW_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF }

適用対象:SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance

行モード メモリ許可フィードバックは、データベースの互換性レベル 150 以上を維持しながら、データベース範囲で有効または無効にできます。 行モード メモリ許可フィードバックは、SQL Server 2017 (14.x) で導入されたインテリジェントなクエリ処理の一部の機能です (行モードは SQL Server 2019 (15.x) と Azure SQL データベース でサポートされています)。

注意

データベース互換性レベルが 140 以下である場合は、このデータベース スコープの構成に影響がありません。

BATCH_MODE_ON_ROWSTORE = { ON | OFF }

適用対象:SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance

行ストアのバッチ モードは、データベースの互換性レベル 150 以上を維持しながら、データベース範囲で有効または無効にできます。 行ストアのバッチ モードは、インテリジェント クエリの処理機能ファミリの一部の機能です。

注意

データベース互換性レベルが 140 以下である場合は、このデータベース スコープの構成に影響がありません。

DEFERRED_COMPILATION_TV = { ON | OFF }

適用対象:SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance

テーブル変数の遅延コンパイルは、データベースの互換性レベル 150 以上を維持しながら、データベース範囲で有効または無効にできます。 テーブル変数の遅延コンパイルは、インテリジェント クエリの処理機能ファミリの一部の機能です。

注意

データベース互換性レベルが 140 以下である場合は、このデータベース スコープの構成に影響がありません。

ACCELERATED_PLAN_FORCING = { ON | OFF }

適用対象:SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance

クエリ ストアのプラン強制自動チューニングUSE PLAN クエリ ヒントなど、あらゆる形式のプラン強制に適用される、クエリ プラン強制のために最適化されたメカニズムを有効にします。 既定値は ON です。

注意

高速プラン強制を無効にしないことをお勧めします。

GLOBAL_TEMPORARY_TABLE_AUTO_DROP = { ON | OFF }

適用対象: Azure SQL データベース および Azure SQL Managed Instance

グローバル一時テーブルの自動削除機能を設定できます。 既定値は ON で、グローバル一時テーブルはどのセッションでも使用中でないときに自動的に削除されることを意味します。 OFF に設定すると、グローバル一時テーブルは、DROP TABLE ステートメントを使用して明示的に削除する必要があります。または、サーバーの再起動時に自動的に削除されます。

  • Azure SQL データベース 単一データベースおよびエラスティック プールでは、このオプションを SQL Database サーバーの個々のユーザー データベース内で設定できます。
  • SQL Server および Azure SQL Managed Instance では、このオプションは TempDB 内で設定され、個々のユーザー データベースの設定に影響を与えません。

LIGHTWEIGHT_QUERY_PROFILING = { ON | OFF }

適用対象:SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance

軽量クエリ プロファイリング インフラストラクチャを有効または無効にできます。 軽量クエリ プロファイリング インフラストラクチャ (LWP) は、標準のプロファイリング メカニズムよりも効率的にクエリのパフォーマンス データを提供するもので、既定で有効になっています。

VERBOSE_TRUNCATION_WARNINGS = { ON | OFF }

適用対象:SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance

新しい String or binary data would be truncated のエラー メッセージの有効と無効が切り替えられるようになります。 SQL Server 2019 (15.x) では、次のシナリオに対してより具体的な新しいエラー メッセージ (2628) が導入されています。

String or binary data would be truncated in table '%.*ls', column '%.*ls'. Truncated value: '%.*ls'.

データベース互換性レベルが 150 の状態で ON に設定すると、切り捨てエラーにより、詳しいコンテキストを提供し、トラブルシューティングのプロセスを簡略化する新しいエラー メッセージ 2628 が発生します。

データベース互換性レベルが 150 の状態で OFF に設定すると、切り捨てエラーにより前のエラー メッセージ 8152 が発生します。

データベース互換性レベルが 140 以下の場合、エラー メッセージ 2628 はオプトインのエラー メッセージとして残ります。このエラー メッセージではトレース フラグ 460 を有効にする必要があり、このデータベース スコープ構成に影響がありません。

LAST_QUERY_PLAN_STATS = { ON | OFF }

適用対象:SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance

sys.dm_exec_query_plan_stats の最後の実際の実行プラン (実際の実行プランに相当) のコレクションを有効または無効にすることができます。

PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES

適用対象: Azure SQL データベース および Azure SQL Managed Instance

PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES オプションでは、エンジンによって自動的に中止される前に、再開可能なインデックスが一時停止される期間 (分単位) を決定します。

  • 既定値は、1 日 (1440 分) に設定されています。
  • 最小期間は 1 分に設定されています。
  • 最大期間は 71582 分です。
  • 0 に設定すると、一時停止された操作が自動的に中止されることはありません

このオプションの現在の値は、sys.database_scoped_configurations に表示されます。

ISOLATE_SECURITY_POLICY_CARDINALITY = { ON | OFF}

適用対象:SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance

行レベル セキュリティ (RLS) 述語がユーザー クエリ全体の実行プランのカーディナリティに影響するかどうかを制御できます。 ISOLATE_SECURITY_POLICY_CARDINALITY が ON の場合、RLS 述語は、実行プランのカーディナリティに影響しません。 たとえば、100 万行を含むテーブルがあり、RLS 述語で、クエリを発行する特定のユーザーに対して結果を 10 行に制限する場合について考えてみましょう。 このデータベース スコープ構成が OFF に設定されている場合、この述語の推定カーディナリティは 10 になります。 このデータベース スコープ構成が ON の場合、クエリ最適化により 100 万行と推定されます。 ほとんどのワークロードでは、既定値を使用することをお勧めします。

DW_COMPATIBILITY_LEVEL = {AUTO | 10 | 20 }

適用対象: Azure Synapse Analytics のみ

指定したバージョンのデータベース エンジンと互換性があるように、Transact-SQL およびクエリ処理の動作が設定されます。 設定が完了すると、そのデータベースでクエリが実行されるときに、互換性のある機能だけが実行されます。 データベースの互換性レベルは、最初の作成時に既定で AUTO に設定されます。 互換性レベルは、データベースの一時停止/再開、バックアップ/復元操作の後でも保持されます。

互換性レベル 説明
AUTO 既定値。 この値は、Synapse Analytics エンジンによって自動的に更新されます。 現在の値は 20 です。
"10" 互換性レベルのサポートを導入する前に、Transact-SQL とクエリ処理の動作を実行します。
20 1 番目の互換性レベル。ゲート Transact-SQL とクエリ処理の動作が含まれます。

EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS = { ON | OFF }

適用対象: Azure SQL データベース および Azure SQL Managed Instance

スカラー ユーザー定義関数 (UDF) の実行統計を sys.dm_exec_function_stats システム ビューに表示するかどうかを制御できます。 スカラー UDF を多用する負荷の高いワークロードの場合、関数実行の統計情報を収集すると、顕著なパフォーマンスのオーバーヘッドが発生する可能性があります。 これを回避するには、EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS データベース スコープの構成を OFF に設定します。

ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = { ON | OFF }

適用対象: Azure SQL データベース と Azure SQL Managed Instance (機能はパブリック プレビュー段階)

統計の非同期更新が有効になっている場合、この構成を有効にすると、高同時実行のシナリオで他のセッションのブロックを防ぐために、統計を更新するバックグラウンド要求が優先度の低いキューの Sch-M ロックを待機するようになります。 詳細については、「AUTO_UPDATE_STATISTICS_ASYNC」を参照してください。

Permissions

データベースに対する ALTER ANY DATABASE SCOPED CONFIGURATION が必要です。 この権限は、データベース上で CONTROL 権限を持つユーザーが付与できます。

全般的な解説

セカンダリ データベースにはプライマリとは異なるスコープ構成を設定できますが、すべてのセカンダリ データベースで同じ構成が使用されます。 個々のセカンダリに異なる設定を構成することはできません。

このステートメントを実行すると、現在のデータベースのプロシージャ キャッシュが消去されます。つまり、すべてのクエリを再コンパイルする必要があります。

3 部構成の名前のクエリの場合、現在のデータベース コンテキストでコンパイルされる SQL モジュール (プロシージャ、関数、トリガーなど) ではなく、クエリに対する別のデータベース接続の設定が適用されます。そのため、そのような設定が置かれているデータベースのオプションが使用されます。 同様に、統計を非同期的に更新する場合は、統計が存在するデータベースの ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY の設定が使用されます。

ALTER_DATABASE_SCOPED_CONFIGURATION イベントは、DDL トリガーの始動に使用できる DDL イベントとして追加されます。ALTER_DATABASE_EVENTS トリガー グループの子です。

データベース スコープ構成設定がデータベースに継承されるので、特定のデータベースが復元またはアタッチされたときに、既存の構成設定が残ります。

Azure SQL データベース と Azure SQL Managed Instance のSQL Server 2019 (15.x) 以降では、一部のオプション名が変更されています。

  • DISABLE_INTERLEAVED_EXECUTION_TVFINTERLEAVED_EXECUTION_TVF に変更しました
  • DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACKBATCH_MODE_MEMORY_GRANT_FEEDBACK に変更しました
  • DISABLE_BATCH_MODE_ADAPTIVE_JOINSBATCH_MODE_ADAPTIVE_JOINS に変更しました

制限事項と制約事項

MAXDOP

詳細設定はグローバル設定をオーバーライドします。その Resource Governor が他のすべての MAXDOP 設定の上限となります。 MAXDOP 設定のロジックは次のようになります。

  • クエリ ヒントは sp_configure とデータベース スコープ構成の両方をオーバーライドします。 ワークロード グループにリソース グループ MAXDOP が設定されている場合:

    • クエリ ヒントが 0 に設定されている場合、Resource Governor 設定でオーバーライドされます。

    • クエリ ヒントが 0 ではない場合、Resource Governor 設定が上限となります。

  • データベース スコープ構成 (0 ではない限り) は、クエリ ヒントがある場合を除き、sp_configure 設定をオーバーライドし、Resource Governor 設定が上限となります。

  • sp_configure 設定は、Resource Governor 設定でオーバーライドされます。

QUERY_OPTIMIZER_HOTFIXES

QUERYTRACEON ヒントを使用して SQL Server 7.0 から SQL Server 2012 (11.x) バージョンの既定のクエリ オプティマイザーまたはクエリ オプティマイザー修正プログラムを有効にすると、それがクエリ ヒントとデータベース スコープ構成設定の OR 条件になります。つまり、いずれかが有効になっている場合、データベース スコープ構成が適用されます。

Geo DR

読み取り可能なセカンダリ データベース (Always On 可用性グループや Azure SQL データベース と Azure SQL Managed Instance の geo レプリケートされたデータベース) では、データベースの状態を確認することでセカンダリ値が使用されます。 フェールオーバーで再コンパイルが行われず、技術的に、セカンダリ設定を使用しているクエリが新しいプライマリに与えられる場合でも、プライマリとセカンダリの間の設定はワークロードが異なるときにのみ変わるというのがその考えです。そのため、キャッシュされたクエリでは最適設定が使用されるが、新しいクエリはそれに適した新しい設定を選択します。

DacFx

ALTER DATABASE SCOPED CONFIGURATION は Azure SQL データベース、Azure SQL Managed Instance、SQL Server (SQL Server 2016 (13.x) 以降) の新しい機能であり、データベース スキーマに影響を与えます。スキーマのエクスポートは (データがあってもなくても)、SQL Server 2012 (11.x) や SQL Server 2014 (12.x) など、以前のバージョンの SQL Server にはインポートできません。 たとえば、SQL Database または SQL Server 2016 (13.x) データベースから DACPAC または BACPAC にエクスポートしたものは、下位レベルのサーバーにインポートできません。

ELEVATE_ONLINE

このオプションは、WITH (ONLINE = <syntax>) 対応の DDL ステートメントにのみ適用されます。 XML インデックスは影響を受けません。

ELEVATE_RESUMABLE

このオプションは、WITH (RESUMABLE = <syntax>) 対応の DDL ステートメントにのみ適用されます。 XML インデックスは影響を受けません。

Metadata

sys.database_scoped_configurations (Transact-SQL) システム ビューには、データベース内のスコープ構成に関する情報が表示されます。 データベース スコープ構成オプションはサーバー全体の初期設定にオーバーライドするため、sys.database_scoped_configurations にのみ表示されます。 sys.configurations (Transact-SQL) システム ビューは、サーバー全体の設定にのみ表示されます。

以下は ALTER DATABASE SCOPED CONFIGURATION の使用例です

A. アクセス許可の付与

この例では、ALTER DATABASE SCOPED CONFIGURATION の実行に必要な権限をユーザー Joe に与えています。

GRANT ALTER ANY DATABASE SCOPED CONFIGURATION to [Joe] ;

B. MAXDOP の設定

この例では、geo レプリケーション シナリオでプライマリ データベースに MAXDOP = 1 を、セカンダリ データベースに MAXDOP = 4 を設定します。

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1 ;
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = 4 ;

この例では、geo レプリケーション シナリオで、セカンダリ データベースの MAXDOP をそのプライマリ データベースと同じ値に設定します。

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY ;

C. LEGACY_CARDINALITY_ESTIMATION の設定

この例では、geo レプリケーション シナリオで、セカンダリ データベースの LEGACY_CARDINALITY_ESTIMATION を ON に設定します。

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = ON ;

この例では、geo レプリケーション シナリオで、セカンダリ データベースの LEGACY_CARDINALITY_ESTIMATION をそのプライマリ データベースと同じ値に設定します。

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY ;

D. PARAMETER_SNIFFING の設定

この例では、geo レプリケーション シナリオで、プライマリ データベースの PARAMETER_SNIFFING を OFF に設定します。

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF ;

この例では、geo レプリケーション シナリオで、セカンダリ データベースの PARAMETER_SNIFFING を OFF に設定します。

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = OFF ;

この例では、geo レプリケーション シナリオで、セカンダリ データベースの PARAMETER_SNIFFING をプライマリ データベースと同じ値に設定します。

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY ;

E. QUERY_OPTIMIZER_HOTFIXES の設定

geo レプリケーション シナリオで、プライマリ データベースの QUERY_OPTIMIZER_HOTFIXES を ON に設定します。

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON ;

F. プロシージャ キャッシュの消去

この例では、プロシージャ キャッシュを消去します (プライマリ データベースのみ可能)。

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

G. IDENTITY_CACHE の設定

適用対象:SQL Server (SQL Server 2017 (14.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance

この例では、ID キャッシュを無効にします。

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF ;

H. OPTIMIZE_FOR_AD_HOC_WORKLOADS の設定

適用対象:SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance

この例では、バッチが初めてコンパイルされるとき、コンパイルしたプラン スタブのキャッシュ保存を有効にします。

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;

I. ELEVATE_ONLINE を設定する

適用対象:SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance

この例では、ELEVATE_ONLINE が FAIL_UNSUPPORTED に設定されます。

ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE = FAIL_UNSUPPORTED ;

J. ELEVATE_RESUMABLE を設定する

適用対象:SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance

この例では、ELEVATE_RESUMABLE が WHEN_SUPPORTED に設定されます。

ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_RESUMABLE = WHEN_SUPPORTED ;

K. プラン キャッシュからクエリ プランを削除する

適用対象:SQL Server (SQL Server 2017 (14.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance

この例では、プロシージャ キャッシュから特定のプランを削除します。

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE 0x06000500F443610F003B7CD12C02000001000000000000000000000000000000000000000000000000000000;

L. 一時停止される期間を設定する

適用対象: Azure SQL データベース および Azure SQL Managed Instance

この例では、再開可能なインデックスの一時停止される期間を 60 分に設定します。

ALTER DATABASE SCOPED CONFIGURATION
SET PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES = 60

その他のリソース

MAXDOP リソース

LEGACY_CARDINALITY_ESTIMATION リソース

PARAMETER_SNIFFING リソース

QUERY_OPTIMIZER_HOTFIXES リソース

ELEVATE_ONLINE リソース

オンライン インデックス操作のガイドライン

ELEVATE_RESUMABLE リソース

オンライン インデックス操作のガイドライン

詳細情報

sys.database_scoped_configurations
SQL Server の "並列処理の最大限度" 構成オプションの推奨事項とガイドライン
sys.configurations
データベースとファイルのカタログ ビュー
サーバー構成オプション
オンライン インデックス操作の動作原理
オンラインでのインデックス操作の実行
ALTER INDEX (Transact-SQL)
CREATE INDEX (Transact-SQL)