ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)

適用対象: SQL Server 2016 (13.x) 以降 Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

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

重要

SQL Server または Azure サービスのバージョンが異なると、サポートされる DATABASE SCOPED CONFIGURATION オプションが異なります。 このページでは、すべてのDATABASE SCOPED CONFIGURATION オプションについて説明します。 該当するバージョンが記載されています。 お使いのサービスのバージョンで利用できる構文を使っていることを確認してください。

引数」セクションの各設定の「適用対象」で示されているように、以下の設定は Azure SQL Database、Azure SQL Managed Instance、SQL Server でサポートされています。

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

この設定は、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 }
    | MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = { ON | OFF }
    | MEMORY_GRANT_FEEDBACK_PERSISTENCE = { 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 }
    | OPTIMIZED_PLAN_FORCING = { ON | OFF }
    | DOP_FEEDBACK = { ON | OFF }
    | CE_FEEDBACK = { ON | OFF }
    | PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = { ON | OFF }
    | LEDGER_DIGEST_STORAGE_ENDPOINT = { <endpoint URL string> | 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 | 30 | 40 | 50 | 9000 }
}

引数

セカンダリの場合

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

CLEAR PROCEDURE_CACHE [plan_handle]

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

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

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

MAXDOP = {<value> | PRIMARY }

<value>

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

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

Note

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

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

Note

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

ヒント

これをクエリ レベルで行うには、MAXDOPクエリ ヒントを使用します。
これをサーバー レベルで行うには、並列処理の最大限度 (MAXDOP)サーバー構成オプションを使用します。
これをワークロード レベルで行うには、MAX_DOPResource 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 Database、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 Database、Azure SQL Managed Instance

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

Note

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

INTERLEAVED_EXECUTION_TVF = { ON | OFF }

適用対象: SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL Database、Azure SQL Managed Instance

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

Note

データベース互換性レベルが 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 Database、Azure SQL Managed Instance

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

Note

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

BATCH_MODE_ADAPTIVE_JOINS = { ON | OFF }

適用対象: SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL Database、Azure SQL Managed Instance

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

Note

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

TSQL_SCALAR_UDF_INLINING = { ON | OFF }

適用対象: SQL Server (SQL Server 2019 (15.x) 以降) と Azure SQL Database (機能はプレビュー段階)

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

Note

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

ELEVATE_ONLINE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }

適用対象: SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL Database、Azure SQL Managed Instance

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

FAIL_UNSUPPORTED

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

Note

テーブルへの列の追加は、一般的なケースではオンライン操作です。 ただし、Null 非許容列を追加するときなど、オンラインで列を追加できないシナリオもあります。 このようなケースで、FAIL_UNSUPPORTED が設定されていると、操作は失敗します。

WHEN_SUPPORTED

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

Note

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

ELEVATE_RESUMABLE= { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }

適用対象: SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL Database、Azure SQL Managed Instance

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

FAIL_UNSUPPORTED

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

WHEN_SUPPORTED

この値のとき、RESUMABLE 対応の操作が昇格されます。 再開可能な操作をサポートしていない操作は、実行できません。

Note

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

OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }

適用対象: SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL Database、Azure SQL Managed Instance

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

XTP_PROCEDURE_EXECUTION_STATISTICS = { ON | OFF }

適用対象: Azure SQL Database と 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 Database と 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 Database、Azure SQL Managed Instance

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

Note

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

MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = { ON | OFF }

適用対象: SQL Server (SQL Server 2022 (16.x) 以降)、Azure SQL Database

データベースに端を発するあらゆるクエリ実行に対し、メモリ許可フィードバック パーセンタイルを無効にできます。 既定値はオンです。 詳細については、「パーセンタイルと永続化モードのメモリ許可フィードバック」を参照してください。

Note

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

MEMORY_GRANT_FEEDBACK_PERSISTENCE = { ON | OFF }

適用対象: SQL Server (SQL Server 2022 (16.x) 以降)、Azure SQL Database

データベースに端を発するあらゆるクエリ実行に対し、メモリ許可フィードバック永続化を無効にできます。 既定値はオンです。 詳細については、「パーセンタイルと永続化モードのメモリ許可フィードバック」を参照してください。

Note

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

BATCH_MODE_ON_ROWSTORE = { ON | OFF }

適用対象: SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL Database、Azure SQL Managed Instance

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

Note

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

DEFERRED_COMPILATION_TV = { ON | OFF }

適用対象: SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL Database、Azure SQL Managed Instance

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

Note

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

ACCELERATED_PLAN_FORCING = { ON | OFF }

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

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

Note

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

GLOBAL_TEMPORARY_TABLE_AUTO_DROP = { ON | OFF }

適用対象: Azure SQL Database と 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 Database、Azure SQL Managed Instance

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

VERBOSE_TRUNCATION_WARNINGS = { ON | OFF }

適用対象: SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL Database、Azure SQL Managed Instance

新しい String or binary data would be truncated のエラー メッセージの有効と無効が切り替えられるようになります。 既定値は ON です。 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 Database、Azure SQL Managed Instance

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

PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES

適用対象: SQL Server (SQL Server 2022 (16.x) 以降)、Azure SQL Database、Azure SQL Managed Instance

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

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

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

ISOLATE_SECURITY_POLICY_CARDINALITY = { ON | OFF}

適用対象: SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL Database、Azure SQL Managed Instance

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

DW_COMPATIBILITY_LEVEL = { AUTO | 10 | 20 | 30 | 40 | 50 | 9000 }

適用対象: Azure Synapse Analytics のみ

指定したバージョンのデータベース エンジンと互換性があるように、Transact-SQL およびクエリ処理の動作を設定します。 設定すると、そのデータベースでクエリが実行されると、互換性のある機能のみが実行されます。 互換性レベルごとに、さまざまなクエリ処理の機能強化がサポートされています。 各レベルは、前のレベルの機能を吸収しています。 データベースの互換性レベルは、最初の作成時に既定で AUTO に設定され、これが推奨される設定です。 互換性レベルは、データベースの一時停止/再開、バックアップ/復元操作の後でも保持されます。 既定値は [自動] です。

互換性レベル 説明
AUTO 既定値。 その値は Synapse Analytics エンジンによって自動的に更新され、sys.database_scoped_configurations0 として表されます。 AUTO は現在、互換性レベル 30 の機能にマップされています。
10 互換性レベルのサポートを導入する前に、Transact-SQL とクエリ エンジンの動作を実行します。
20 1 番目の互換性レベル。ゲート Transact-SQL とクエリ エンジンの動作が含まれます。 システム ストアド プロシージャ sp_describe_undeclared_parameters は、このレベルでサポートされています。
30 新しいクエリ エンジンの動作が含まれます。
40 新しいクエリ エンジンの動作が含まれます。
50 複数列の分散は、このレベルでサポートされています。 詳しくは、「CREATE TABLE」、「CREATE TABLE AS SELECT」、「CREATE MATERIALIZED VIEW」をご覧ください。
9000 プレビューの互換性レベル。 このレベルでゲートされたプレビュー機能が、機能固有のドキュメントで呼び出されます。 このレベルには、9000 レベル以外の最高レベルの能力も含まれます。

EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS = { ON | OFF }

適用対象: Azure SQL Database と Azure SQL Managed Instance

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

ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = { ON | OFF }

適用対象: SQL Server (SQL Server 2022 (16.x) 以降)、Azure SQL Database、Azure SQL Managed Instance

非同期統計更新が有効になっている場合、この構成を有効にすると、バックグラウンド要求の更新統計が優先順位の低いキューのロックを待機 Sch-M し、コンカレンシーの高いシナリオで他のセッションがブロックされないようにします。 詳細については、「AUTO_UPDATE_STATISTICS_ASYNC」を参照してください。 既定値は OFF です。

OPTIMIZED_PLAN_FORCING = { ON | OFF }

適用対象: SQL Server (SQL Server 2022 (16.x) 以降)

プラン強制を最適化すると、強制クエリを繰り返すためのコンパイル オーバーヘッドが減ります。 既定値は ON です。 クエリ実行プランが生成されると、最適化再生スクリプトとして再利用するために特定のコンパイル手順が格納されます。 最適化再生スクリプトは、圧縮されたプラン表示 XML の一部としてクエリ ストアの非表示 OptimizationReplay 属性に保管されます。 詳細については、クエリ ストアによるプラン強制の最適化に関するページを参照してください。

DOP_FEEDBACK = { ON | OFF }

適用対象: SQL Server (SQL Server 2022 (16.x) 以降)

経過時間と待機に基づいて、繰り返されるクエリの並列処理の非効率性が特定されます。 並列処理の利用が非効率であると思われる場合、構成された DOP の出所が何であれ、DOP フィードバックによってクエリの次回の実行で DOP が下がり、それが役立つか検証されます。 クエリ ストアを有効にし、READ_WRITE モードにする必要があります。 詳細については、「並列処理の次数 (DOP) のフィードバック」を参照してください。 既定値は OFF です。

CE_FEEDBACK = { ON | OFF }

適用対象: SQL Server (SQL Server 2022 (16.x) 以降)

CE フィードバックは、既定の CE (CE120 以上) を使用する場合に、不適切な CE モデル前提条件によって発生する回帰の問題に対処するものです。これにより、異なるモデル前提条件を選択的に使用できます。 クエリ ストアを有効にし、READ_WRITE モードにする必要があります。 詳細については、「カーディナリティ推定 (CE) フィードバック」を参照してください。 データベース互換性レベル 160 以上では、既定値は [ON] です。

PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = { ON | OFF }

適用対象: SQL Server (SQL Server 2022 (16.x) 以降)

パラメーター センシティビティ プラン (PSP) 最適化では、パラメーター化クエリに対してプランが 1 つキャッシュされているだけでは、入ってくる可能性があるあらゆるパラメーター値に最適ではないシナリオに対処します。 これは一様でないデータ分散のケースです。 データベース互換性レベル 160 での既定値は、[ON] です。 詳細については、「パラメーターに依存するプランの最適化」を参照してください。

LEDGER_DIGEST_STORAGE_ENDPOINT = { <endpoint URL string> | OFF }

適用対象: SQL Server (SQL Server 2022 (16.x) 以降)

Azure Blob Storage への台帳ダイジェストのアップロードを有効または無効にします。 台帳ダイジェストのアップロードを有効にするには、Azure Blob ストレージ アカウントのエンドポイントの台帳を指定します。 台帳ダイジェストのアップロードを無効にするには、オプションの値を OFF に設定します。 既定値は OFF です。

FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION = { ON | OFF }

適用対象: SQL Server (SQL Server 2022 (16.x) 以降)、Azure SQL Database、Azure SQL Managed Instance

軽量のクエリ実行統計プロファイル インフラストラクチャを使用する場合、または実行時間の長いクエリのトラブルシューティング中に sys.dm_exec_query_statistics_xml を実行する場合、SQL Server で ParameterRuntimeValue を含むプラン表示 XML フラグメントが生成されます。

重要

データベース スコープ構成オプションは FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION 、運用環境で継続的に有効にすることを意図したものではなく、時間制限付きのトラブルシューティングのみを目的としています。 このデータベース スコープ構成オプションを使用すると、DMV または軽量クエリ実行統計プロファイル インフラストラクチャが有効かどうかに関係なく sys.dm_exec_query_statistics_xml 、ランタイム パラメーター情報を含む Showplan XML フラグメントを作成するため、CPU とメモリのオーバーヘッドが増える可能性があります。

アクセス許可

データベースに対する 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 Database、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 Database、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 Database、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 Database、Azure SQL Managed Instance

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

ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_RESUMABLE = WHEN_SUPPORTED ;

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

適用対象: SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL Database、Azure SQL Managed Instance

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

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE 0x06000500F443610F003B7CD12C02000001000000000000000000000000000000000000000000000000000000;

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

適用対象: Azure SQL Database と Azure SQL Managed Instance

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

ALTER DATABASE SCOPED CONFIGURATION
SET PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES = 60

M. 台帳ダイジェストのアップロードを有効または無効にする

適用対象: SQL Server (SQL Server 2022 (16.x) 以降)

この例では、Azure ストレージ アカウントへの台帳ダイジェストのアップロードを有効にします。

ALTER DATABASE SCOPED CONFIGURATION
SET LEDGER_DIGEST_STORAGE_ENDPOINT = 'https://mystorage.blob.core.windows.net'

この例では、台帳ダイジェストのアップロードを無効にします。

ALTER DATABASE SCOPED CONFIGURATION
SET LEDGER_DIGEST_STORAGE_ENDPOINT = OFF

その他のリソース

MAXDOP リソース

LEGACY_CARDINALITY_ESTIMATION リソース

PARAMETER_SNIFFING リソース

QUERY_OPTIMIZER_HOTFIXES リソース

ELEVATE_ONLINE リソース

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

ELEVATE_RESUMABLE リソース

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