ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)

  • Artikel

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse Analytics

Dieser Befehl aktiviert mehrere Einstellungen für die Datenbankkonfiguration auf der Ebene einzelner Datenbanken.

Wichtig

Verschiedene DATABASE SCOPED CONFIGURATION-Optionen werden in verschiedenen Versionen von SQL Server oder Azure-Diensten unterstützt. Auf dieser Seite werden alleDATABASE SCOPED CONFIGURATION-Optionen beschrieben. Falls zutreffend, werden Versionen angegeben. Achten Sie darauf, dass Sie die Syntax verwenden, die in der von Ihnen verwendeten Version des Diensts verfügbar ist.

Die folgenden Einstellungen werden, wie durch die Zeile Gilt für für jede Einstellung im Abschnitt Argumente angegeben, in Azure SQL-Datenbank, Azure SQL Managed Instance und SQL Server unterstützt:

  • Löschen des Prozedurcaches.
  • Legen Sie den MAXDOP-Parameter auf einen empfohlenen Wert (1,2, ...) für die primäre Datenbank fest (basierend auf dem, was am besten für diese Workload ist), und legen Sie einen anderen Wert für sekundäre Datenbanken fest, die für Berichtsabfragen verwendet werden. Einen Leitfaden zum Auswählen eines MAXDOP-Vorgangs finden Sie unter Konfigurieren der Serverkonfigurationsoption „Max. Grad an Parallelität“.
  • Festlegen des Kardinalitätsschätzungsmodells für den Abfrageoptimierer unabhängig von der Datenbank auf den Kompatibilitätsgrad.
  • Aktivieren oder Deaktivieren der Parameterermittlung auf Datenbankebene.
  • Aktivieren oder Deaktivieren der Abfrageoptimierungs-Hotfixes auf Datenbankebene.
  • Aktivieren oder Deaktivieren des Identitätscache auf Datenbankebene.
  • Aktivieren oder Deaktivieren eines Stubs des kompilierten Plans, der bei der erstmaligen Kompilierung eines Batches im Cache gespeichert werden soll.
  • Aktivieren oder Deaktivieren der Sammlung von Ausführungsstatistiken für nativ kompilierte Transact-SQL-Module.
  • Aktivieren oder Deaktivieren von „online by default“-Optionen (Standardmäßig online) für DDL-Anweisungen, die die ONLINE =-Syntax unterstützen.
  • Aktivieren oder Deaktivieren von „resumable by default“-Optionen (Standardmäßig fortsetzbar) für DDL-Anweisungen, die die RESUMABLE =-Syntax unterstützen.
  • Aktivieren oder Deaktivieren der Features der intelligenten Abfrageverarbeitung
  • Aktivieren oder Deaktivieren des beschleunigten Erzwingens des Plans.
  • Aktivieren oder Deaktivieren der Autodrop-Funktionalität von globalen temporären Tabellen.
  • Aktivieren oder Deaktivieren der einfachen Profilerstellungsinfrastruktur für Abfragen
  • Aktivieren oder Deaktivieren der neuen String or binary data would be truncated-Fehlermeldung
  • Aktivieren oder Deaktivieren des letzten tatsächlichen Ausführungsplans in sys.dm_exec_query_plan_stats
  • Geben Sie an, wie viele Minuten ein angehaltener reaktivierbarer Indexvorgang angehalten wird, bevor er vom Datenbank-Engine automatisch abgebrochen wird.
  • Aktivieren oder Deaktivieren des Wartens auf Sperren mit niedriger Priorität für asynchrone Statistikupdates.
  • Aktivieren oder Deaktivieren des Hochladens von Ledgerdigests in Azure Blob Storage

Diese Einstellung ist nur in Azure Synapse Analytics verfügbar.

  • Festlegen des Kompatibilitätsgrads von Benutzerdatenbanken

Transact-SQL-Syntaxkonventionen

Syntax

-- 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 }
}

Wichtig

Ab SQL Server 2019 (15.x), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz haben sich einige Optionsnamen geändert:

  • DISABLE_INTERLEAVED_EXECUTION_TVF wurde in INTERLEAVED_EXECUTION_TVF geändert
  • DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK wurde in BATCH_MODE_MEMORY_GRANT_FEEDBACK geändert
  • DISABLE_BATCH_MODE_ADAPTIVE_JOINS wurde in BATCH_MODE_ADAPTIVE_JOINS geändert
-- Syntax for Azure Synapse Analytics

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

< set_options > ::=
{
    DW_COMPATIBILITY_LEVEL = { AUTO | 10 | 20 | 30 | 40 | 50 | 9000 }
}

Argumente

FOR SECONDARY

Gibt die Einstellungen für sekundäre Datenbanken an (alle sekundären Datenbanken müssen identische Werte aufweisen).

CLEAR PROCEDURE_CACHE [plan_handle]

Löscht den Prozedurcache (Plan) für die Datenbank und kann sowohl in den primären als auch den sekundären Datenbanken ausgeführt werden.

Geben Sie ein Abfrageplanhandle an, um einen einzelnen Abfrageplan aus dem Plancache zu löschen.

Gilt für: Die Angabe eines Abfrageplanhandles ist ab SQL Server 2019 (15.x), in Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz verfügbar.

MAXDOP = {<value> | PRIMARY }

<value>

Gibt die Standardeinstellung Max. Grad an Parallelität (MAXDOP) an, die für Anweisungen verwendet werden sollte. 0 ist der Standardwert und gibt an, dass die Serverkonfiguration stattdessen verwendet wird. Der MAXDOP im Datenbankbereich überschreibt (es sei denn, er ist auf 0 festgelegt) den maximalen Grad der Parallelität, der auf Serverebene festgelegt ist.sp_configure Abfragehinweise können die MAXDOP-Einstellung im Datenbankbereich weiterhin überschreiben, damit bestimmte Abfragen optimiert werden können, für die andere Einstellungen erforderlich sind. All diese Einstellungen werden durch die MAXDOP-Einstellung für die Arbeitsauslastungsgruppe begrenzt.

Sie können mithilfe der MAXDOP-Option die Anzahl der Prozessoren beschränken, die für die Ausführung paralleler Pläne verwendet werden. SQL Server berücksichtigt die Ausführung paralleler Pläne für Abfragen, DDL-Indizierungsoperationen (Datendefinitionssprache, Data Definition Language, DDL), parallele Einfügevorgänge, Onlineausführung von ALTER COLUMN, parallele Sammlung von Statistiken sowie die statische und keysetgesteuerte Cursorauffüllung.

Hinweis

Der Grenzwert für Max. Grad an Parallelität wird taskbezogen festgelegt. Es handelt sich nicht um einen anforderungs- oder abfragebezogenen Grenzwert. Das bedeutet, dass während einer parallelen Abfrageausführung eine einzelne Abfrage mehrere Tasks erzeugen kann, die einem Planer zugeordnet sind. Weitere Informationen finden Sie im Handbuch zur Thread- und Taskarchitektur.

Informationen zum Festlegen dieser Option auf Instanzebene finden Sie unter Konfigurieren der Serverkonfigurationsoption „Max. Grad an Parallelität“.

Hinweis

In Azure SQL-Datenbank ist die datenbankweite Konfiguration von MAXDOP für neue Singletons und elastische Datenbanken in Pools standardmäßig auf 8 begrenzt. Der maximale Grad an Parallelität kann für jede Datenbank, wie im aktuellen Artikel beschrieben, konfiguriert werden. Empfehlungen zur optimalen Konfiguration des maximalen Grads an Parallelität finden Sie im Abschnitt Weitere Ressourcen.

Tipp

Verwenden Sie den AbfragehinweisMAXDOP, um dies auf Abfrageebene zu erreichen.
Verwenden Sie den MAXDOP-Wert (maximaler Parallelitätsgrad) der Serverkonfigurationsoption, um dies auf Serverebene zu erreichen.
Verwenden Sie die Resource Governor-Konfigurationsoption MAX_DOP für die Arbeitsauslastungsgruppe, um dies auf Arbeitsauslastungsebene zu erreichen.

PRIMARY

Kann nur für die sekundären Datenbanken festgelegt werden, während die betreffende Datenbank die primäre ist, und gibt an, dass diese Konfiguration für die primäre Datenbank festgelegt wird. Wenn sich die Konfiguration für die primäre Datenbank ändert, ändert sich der Wert für die sekundären Datenbanken entsprechend, ohne dass dieser Wert explizit festgelegt werden muss. PRIMARY ist die Standardeinstellung für die sekundären Datenbanken.

LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }

Damit können Sie das Kardinalitätsschätzungsmodell für den Abfrageoptimierer unabhängig vom Kompatibilitätsgrad der Datenbank in SQL Server 2012 und früheren Versionen festlegen. Die Standardeinstellung ist OFF. Sie legt das Kardinalitätsschätzungsmodell für den Abfrageoptimierer basierend auf dem Kompatibilitätsgrad der Datenbank fest. Das Festlegen von LEGACY_CARDINALITY_ESTIMATION auf ON entspricht der Aktivierung des Ablaufverfolgungsflags 9481.

Tipp

Fügen Sie den AbfragehinweisQUERYTRACEON hinzu, um dies auf Abfrageebene zu erreichen. Ab SQL Server 2016 (13.x) SP1 müssen Sie den AbfragehinweisUSE HINT hinzufügen, anstatt das Ablaufverfolgungsflag zu verwenden, um dies auf Abfrageebene zu erreichen.

PRIMARY

Dieser Wert ist nur für sekundäre Datenbanken gültig, während die betreffende Datenbank die primäre ist, und gibt an, dass es sich bei der Einstellung des Kardinalitätsschätzungsmodells für den Abfrageoptimierer für alle sekundären Datenbanken um den für die primäre Datenbank festgelegten Wert handelt. Wenn sich die Konfiguration für die primäre Datenbank im Kardinalitätsschätzungsmodell des Abfrageoptimierers ändert, ändert sich der Wert für die sekundären Datenbanken entsprechend. PRIMARY ist die Standardeinstellung für die sekundären Datenbanken.

PARAMETER_SNIFFING = { ON | OFF | PRIMARY }

Aktiviert oder deaktiviert die Parameterermittlung. Der Standardwert ist ON. Das Festlegen von PARAMETER_SNIFFING auf OFF entspricht der Aktivierung des Ablaufverfolgungsflags 4136.

Tipp

Informationen darüber, wie Sie dies auf Abfrageebene erreichen, finden Sie unter dem AbfragehinweisOPTIMIZE FOR UNKNOWN. Ab SQL Server 2016 (13.x) SP1 ist auch der AbfragehinweisUSE HINT verfügbar, damit dies auf Abfrageebene erreicht werden kann.

PRIMARY

Dieser Wert ist nur für sekundäre Datenbanken gültig, während die betreffende Datenbank primär ist, und gibt an, dass es sich bei dem Wert für diese Einstellung für alle sekundären Datenbanken um den für die primäre Datenbank festgelegten Wert handelt. Wenn sich die Konfiguration für die primäre Datenbank bei der Verwendung der Parameterermittlung ändert, ändert sich der Wert für die sekundären Datenbanken entsprechend, ohne dass dieser Wert explizit festgelegt werden muss. PRIMARY ist die Standardeinstellung für die sekundären Datenbanken.

QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY }

Aktiviert oder deaktiviert Hotfixes für die Abfrageoptimierung unabhängig vom Kompatibilitätsgrad der Datenbank. Die Standardeinstellung ist OFF. Sie deaktiviert Hotfixes für Abfrageoptimierer, die nach der Einführung des höchsten verfügbaren Kompatibilitätsgrads für eine bestimmte Version (post-RTM) veröffentlicht wurden. Ein Festlegen dieser Einstellung auf ON entspricht der Aktivierung des Ablaufverfolgungsflags 4199.

Gilt für: SQL Server (ab SQL Server 2016 (13.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

Tipp

Fügen Sie den AbfragehinweisQUERYTRACEON hinzu, um dies auf Abfrageebene zu erreichen. Ab SQL Server 2016 (13.x) SP1 müssen Sie den Abfragehinweis USE HINT hinzufügen, statt das Ablaufverfolgungsflag zu verwenden, um dies auf Abfrageebene zu erreichen.

PRIMARY

Dieser Wert ist nur für sekundäre Datenbanken gültig, während die betreffende Datenbank primär ist, und gibt an, dass es sich bei dem Wert für diese Einstellung für alle sekundären Datenbanken um den für die primäre Datenbank festgelegten Wert handelt. Wenn sich die Konfiguration für die primäre Datenbank ändert, ändert sich der Wert für die sekundären Datenbanken entsprechend, ohne dass dieser Wert explizit festgelegt werden muss. PRIMARY ist die Standardeinstellung für die sekundären Datenbanken.

IDENTITY_CACHE = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2017 (14.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

Aktiviert oder deaktiviert den Identitätscache auf Datenbankebene. Der Standardwert ist ON. Identitätszwischenspeichern wird verwendet, um die Leistung von INSERT in Tabellen mit Identitätsspalten zu verbessern. Deaktiviert die Option IDENTITY_CACHE, um Lücken in einer Identitätsspalte zu vermeiden, wenn der Server unerwartet neu gestartet oder ein Failover zu einem sekundären Server ausgeführt wird. Diese Option ist mit dem vorhandenen Ablaufverfolgungsflag 272 vergleichbar. Der einzige Unterschied besteht darin, dass sie auf Datenbankebene und nicht nur auf Serverebene festgelegt werden kann.

Hinweis

Diese Option kann nur für PRIMARY festgelegt werden. Weitere Informationen finden Sie unter Identitätsspalten.

INTERLEAVED_EXECUTION_TVF = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2019 (15.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

Ermöglicht Ihnen das Aktivieren bzw. Deaktivieren der verschachtelten Ausführung für Tabellenwertfunktionen mit mehreren Anweisungen im Datenbank- oder Anweisungsbereich. Dabei kann ein Datenbank-Kompatibilitätsgrad von mindestens 140 beibehalten werden. Der Standardwert ist ON. Verschachtelte Funktionen stellen ein Feature der adaptiven Abfrageverarbeitung in Azure SQL-Datenbank dar. Weitere Informationen finden Sie unter Intelligente Abfrageverarbeitung in SQL-Datenbanken.

Hinweis

Für Datenbank-Kompatibilitätsgrade von 130 oder weniger hat diese datenbankbezogene Konfiguration keine Auswirkungen.

Nur in SQL Server 2017 (14.x) wurde für die Option INTERLEAVED_EXECUTION_TVF der ältere Name DISABLE_INTERLEAVED_EXECUTION_TVF verwendet.

BATCH_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2019 (15.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

Ermöglicht Ihnen das Aktivieren bzw. Deaktivieren von Feedback zur Speicherzuweisung im Batchmodus im Datenbankbereich. Dabei kann ein Datenbank-Kompatibilitätsgrad von mindestens 140 beibehalten werden. Der Standardwert ist ON. Das Feedback zur Speicherzuweisung im Batchmodus stellt einen Bestandteil der Suite mit Features zur intelligenten Abfrageverarbeitung dar, die in SQL Server 2017 (14.x) eingeführt wurde. Weitere Informationen finden Sie unter Feedback zur Speicherzuweisung.

Hinweis

Für Datenbank-Kompatibilitätsgrade von 130 oder weniger hat diese datenbankbezogene Konfiguration keine Auswirkungen.

BATCH_MODE_ADAPTIVE_JOINS = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2019 (15.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

Ermöglicht Ihnen das Aktivieren bzw. Deaktivieren von adaptiven Joins im Batchmodus im Datenbankbereich. Dabei kann ein Datenbank-Kompatibilitätsgrad von mindestens 140 beibehalten werden. Der Standardwert ist ON. Adaptive Joins im Batchmodus stellen einen Bestandteil der intelligenten Abfrageverarbeitung dar, die in SQL Server 2017 (14.x) eingeführt wurde.

Hinweis

Für Datenbank-Kompatibilitätsgrade von 130 oder weniger hat diese datenbankbezogene Konfiguration keine Auswirkungen.

TSQL_SCALAR_UDF_INLINING = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2019 (15.x)) und Azure SQL-Datenbank (Feature befindet sich in der Vorschau)

Ermöglicht Ihnen das Aktivieren bzw. Deaktivieren des Inlining benutzerdefinierter T-SQL-Skalarfunktionen im Datenbankbereich. Dabei kann ein Datenbank-Kompatibilitätsgrad von mindestens 150 beibehalten werden. Der Standardwert ist ON. Das Inlining von benutzerdefinierten T-SQL-Skalarfunktionen gehört zur Featurefamilie der intelligenten Abfrageverarbeitung.

Hinweis

Für Datenbank-Kompatibilitätsgrade von 140 oder weniger hat diese datenbankbezogene Konfiguration keine Auswirkungen.

ELEVATE_ONLINE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }

Gilt für: SQL Server (ab SQL Server 2019 (15.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

Ermöglicht es Ihnen, Optionen auszuwählen, die das Modul dazu veranlassen, unterstützte Vorgänge automatisch in den Onlinezustand zu erhöhen. Der Standardwert ist OFF, was bedeutet, dass Vorgänge nicht in den Onlinezustand erhöht werden, es sei denn, dies ist in der Anweisung angegeben. sys. database_scoped_configurations enthält den aktuellen Wert von ELEVATE_ONLINE. Diese Optionen gelten nur für Vorgänge, die für den Onlinezustand unterstützt werden.

FAIL_UNSUPPORTED

Dieser Wert erhöht alle unterstützten DDL-Vorgänge in ONLINE. Vorgänge, die die Onlineausführung nicht unterstützen, schlagen fehl und lösen einen Fehler aus.

Hinweis

Im Allgemeinen erfolgt das Hinzufügen einer Spalte zu einer Tabelle in einem Onlinevorgang. In einigen Szenarien ist dies jedoch nicht der Fall. Beispielsweise kann eine Spalte, die keine Nullwerte zulässt, nicht online hinzugefügt werden. In diesen Fällen führt der Vorgang zu einem Fehler, falls FAIL_UNSUPPORTED festgelegt ist.

WHEN_SUPPORTED

Dieser Wert erhöht Vorgänge, die ONLINE unterstützen. Vorgänge, die den Onlinezustand nicht unterstützen, werden offline ausgeführt.

Hinweis

Sie können die Standardeinstellung überschreiben, indem Sie eine Anweisung senden, in der die ONLINE-Option angegeben ist.

ELEVATE_RESUMABLE= { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }

Gilt für: SQL Server (ab SQL Server 2019 (15.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

Ermöglicht es Ihnen, Optionen auszuwählen, die das Modul dazu veranlassen, unterstützte Vorgänge automatisch in fortsetzbar zu erhöhen. Der Standardwert ist OFF, was bedeutet, dass Vorgänge nicht in fortsetzbar erhöht werden, es sei denn, dies ist in der Anweisung angegeben. sys. database_scoped_configurations enthält den aktuellen Wert von ELEVATE_RESUMABLE. Diese Optionen gelten nur für Vorgänge, die für fortsetzbar unterstützt werden.

FAIL_UNSUPPORTED

Dieser Wert erhöht alle unterstützten DDL-Vorgänge in RESUMABLE. Vorgänge, die keine fortsetzbare Ausführung unterstützen, können nicht ausgeführt werden und lösen einen Fehler aus.

WHEN_SUPPORTED

Dieser Wert erhöht Vorgänge, die RESUMABLE unterstützen. Vorgänge, die die Fortsetzung nicht unterstützen, werden nicht aufzählbar ausgeführt.

Hinweis

Sie können die Standardeinstellung überschreiben, indem Sie eine Anweisung senden, in der die RESUMABLE-Option angegeben ist.

OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2019 (15.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

Aktiviert oder deaktiviert einen Stub des kompilierten Plans, der bei der erstmaligen Kompilierung eines Batches im Cache gespeichert werden soll. Der Standardwert ist OFF. Sobald die datenbankweite Konfiguration OPTIMIZE_FOR_AD_HOC_WORKLOADS für eine Datenbank aktiviert ist, wird ein Stub des kompilierten Plans zwischengespeichert, wenn ein Batch zum ersten Mal kompiliert wird. Planstubs weisen im Vergleich zur Größe des vollständigen kompilierten Plans einen niedrigeren Speicherbedarf auf. Wenn ein Batch erneut kompiliert oder ausgeführt wird, wird der Stub des kompilierten Plans entfernt und durch einen vollständigen kompilierten Plan ersetzt.

XTP_PROCEDURE_EXECUTION_STATISTICS = { ON | OFF }

Gilt für: Azure SQL-Datenbank und Azure SQL Managed Instance

Aktiviert oder deaktiviert die Sammlung von Ausführungsstatistiken auf Modulebene für nativ kompilierte T-SQL-Module in der aktuellen Datenbank. Der Standardwert ist OFF. Die Ausführungsstatistiken werden in sys.dm_exec_procedure_stats wiedergegeben.

Ausführungsstatistiken auf Modulebene für nativ kompilierte T-SQL-Module werden gesammelt, wenn diese Option auf „ON“ festgelegt ist, oder die Sammlung von Statistiken durch sp_xtp_control_proc_exec_stats aktiviert ist.

XTP_QUERY_EXECUTION_STATISTICS = { ON | OFF }

Gilt für: Azure SQL-Datenbank und Azure SQL Managed Instance

Aktiviert oder deaktiviert die Sammlung von Ausführungsstatistiken auf Anweisungsebene für nativ kompilierte T-SQL-Module in der aktuellen Datenbank. Der Standardwert ist OFF. Die Ausführungsstatistik wird in sys.dm_exec_query_stats und im Abfragespeicher wiedergegeben.

Ausführungsstatistiken auf Anweisungsebene für nativ kompilierte T-SQL-Module werden gesammelt, wenn diese Option auf „ON“ festgelegt ist, oder die Sammlung von Statistiken durch sp_xtp_control_query_exec_stats aktiviert ist.

Weitere Informationen zur Leistungsüberwachung nativer kompilierter Transact-SQL-Module finden Sie unter Monitoring Performance of Natively Compiled Stored Procedures.

ROW_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2019 (15.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

Ermöglicht Ihnen das Aktivieren bzw. Deaktivieren von Feedback zur Speicherzuweisung im Zeilenmodus im Datenbankbereich. Dabei kann ein Datenbank-Kompatibilitätsgrad von mindestens 150 beibehalten werden. Der Standardwert ist ON. Das Feedback zur Speicherzuweisung im Zeilenmodus stellt einen Bestandteil der intelligenten Abfrageverarbeitung dar, die in SQL Server 2017 (14.x) eingeführt wurde. Der Zeilenmodus wird in SQL Server 2019 (15.x) und Azure SQL-Datenbank unterstützt. Weitere Informationen zum Feedback zur Speicherzuweisung finden Sie unter Feedback zur Speicherzuweisung.

Hinweis

Für Datenbank-Kompatibilitätsgrade von 140 oder weniger hat diese datenbankbezogene Konfiguration keine Auswirkungen.

MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2022 (16.x)) und Azure SQL-Datenbank

Ermöglicht das Deaktivieren des Perzentils für das Feedback zur Speicherzuweisung für alle Abfrageausführungen, die von der Datenbank stammen. Die Standardeinstellung lautet EIN. Vollständige Informationen finden Sie unter Feedback zur Speicherzuweisung im Perzentil- und Persistenzmodus.

Hinweis

Für Datenbank-Kompatibilitätsgrade von 140 oder weniger hat diese datenbankbezogene Konfiguration keine Auswirkungen.

MEMORY_GRANT_FEEDBACK_PERSISTENCE = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2022 (16.x)) und Azure SQL-Datenbank

Ermöglicht das Deaktivieren der Persistenz für das Feedback zur Speicherzuweisung für alle Abfrageausführungen, die von der Datenbank stammen. Die Standardeinstellung lautet EIN. Vollständige Informationen finden Sie unter Feedback zur Speicherzuweisung im Perzentil- und Persistenzmodus.

Hinweis

Für Datenbank-Kompatibilitätsgrade von 140 oder weniger hat diese datenbankbezogene Konfiguration keine Auswirkungen.

BATCH_MODE_ON_ROWSTORE = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2019 (15.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

Ermöglicht Ihnen das Aktivieren bzw. Deaktivieren des Batchmodus bei Rowstore im Datenbankbereich. Dabei kann ein Datenbank-Kompatibilitätsgrad von mindestens 150 beibehalten werden. Der Standardwert ist ON. Der Batchmodus bei Rowstore gehört zur Funktionsfamilie für die intelligente Abfrageverarbeitung.

Hinweis

Für Datenbank-Kompatibilitätsgrade von 140 oder weniger hat diese datenbankbezogene Konfiguration keine Auswirkungen.

DEFERRED_COMPILATION_TV = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2019 (15.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

Ermöglicht Ihnen das Aktivieren bzw. Deaktivieren der verzögerten Kompilierung von Tabellenvariablen im Datenbankbereich. Dabei kann ein Datenbank-Kompatibilitätsgrad von mindestens 150 beibehalten werden. Der Standardwert ist ON. Die verzögerte Kompilierung von Tabellenvariablen gehört zur Funktionsfamilie für die intelligente Abfrageverarbeitung.

Hinweis

Für Datenbank-Kompatibilitätsgrade von 140 oder weniger hat diese datenbankbezogene Konfiguration keine Auswirkungen.

ACCELERATED_PLAN_FORCING = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2019 (15.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

Aktiviert einen optimierten Mechanismus für das Erzwingen von Abfrageplänen, der sich auf alle Formen des Erzwingens von Plänen anwenden lässt, wie etwa Query Store Force Plan, Automatische Optimierung oder den Abfragehinweis USE PLAN. Der Standardwert ist ON.

Hinweis

Es ist nicht empfehlenswert, das beschleunigte Erzwingen von Plänen zu deaktivieren.

GLOBAL_TEMPORARY_TABLE_AUTO_DROP = { ON | OFF }

Gilt für: Azure SQL-Datenbank und Azure SQL Managed Instance

Ermöglicht das Festlegen der Autodrop-Funktionalität für globale temporäre Tabellen. Der Standardwert ist ON, was bedeutet, dass die globalen temporären Tabellen automatisch gelöscht werden, wenn sie von keiner Sitzung verwendet werden. Wenn diese Einstellung auf OFF festgelegt ist, müssen globale temporäre Tabellen explizit mithilfe einer DROP TABLE Anweisung gelöscht werden oder automatisch beim Serverneustart gelöscht werden.

  • Für Azure SQL-Datenbank Singletons und Pools für elastische Datenbanken kann diese Option in den einzelnen Benutzerdatenbanken des SQL-Datenbankservers festgelegt werden.
  • In SQL Server und Azure SQL Managed Instance wird diese Option in tempdb festgelegt, und die Einstellungen der individuellen Benutzerdatenbanken haben keine Auswirkungen.

LIGHTWEIGHT_QUERY_PROFILING = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2019 (15.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

Ermöglicht das Aktivieren oder Deaktivieren der einfachen Profilerstellungsinfrastruktur für Abfragen Die LWP-Abfrageinfrastruktur (Lightweight Profiling) stellt Abfrageleistungsdaten effizienter bereit als standardmäßige Profilerstellungsmechanismen. Sie ist standardmäßig aktiviert. Der Standardwert ist ON.

VERBOSE_TRUNCATION_WARNINGS = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2019 (15.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

Ermöglicht Ihnen das Aktivieren oder Deaktivieren der neuen String or binary data would be truncated-Fehlermeldung. Der Standardwert ist ON. SQL Server 2019 (15.x) führt eine neue, spezifischere Fehlermeldung (2628) für dieses Szenario ein:

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

Wenn ON bei einem Datenbank-Kompatibilitätsgrad unter 150 festgelegt ist, lösen Kürzungsfehler die neue Fehlermeldung 2628 aus, um mehr Kontext bereitzustellen und die Problembehandlung zu vereinfachen.

Wenn OFF bei einem Datenbank-Kompatibilitätsgrad unter 150 festgelegt ist, lösen Kürzungsfehler die alte Fehlermeldung 8152 aus.

Bei einem Datenbank-Kompatibilitätsgrad von 140 oder niedriger ist die Fehlermeldung 2628 weiterhin eine optionale Fehlermeldung, für die das Ablaufverfolgungsflag 460 aktiviert sein muss. Diese datenbankweite Konfiguration hat keine Auswirkungen.

LAST_QUERY_PLAN_STATS = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2019 (15.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

Ermöglicht Ihnen das Aktivieren oder Deaktivieren der Collection der Abfrageplanstatistiken (entspricht einem tatsächlichen Ausführungsplan) in sys.dm_exec_query_plan_stats. Der Standardwert ist OFF.

PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES

Gilt für: SQL Server (ab SQL Server 2022 (16.x)), Azure SQL-Datenbank und Azure SQL Managed Instance

Die Option PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES legt fest, wie lange (in Minuten) der fortsetzbare Index angehalten wird, bevor er automatisch von der Engine abgebrochen wird.

  • Der Standardwert ist auf einen Tag (1440 Minuten) festgelegt.
  • Die minimale Dauer ist auf 1 Minute gesetzt.
  • Die maximale Dauer beträgt 71.582 Minuten.
  • Wenn der Wert auf 0 festgelegt ist, wird ein angehaltener Vorgang nie automatisch abgebrochen.

Der aktuelle Wert für diese Option wird in sys.database_scoped_configurations angezeigt.

ISOLATE_SECURITY_POLICY_CARDINALITY = { ON | OFF}

Gilt für: SQL Server (ab SQL Server 2019 (15.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

Ermöglicht es Ihnen, zu steuern, ob ein Prädikat für die Sicherheit auf Zeilenebene (Row-Level Security, RLS) die Kardinalität des Ausführungsplans für die gesamte Benutzerabfrage beeinflusst. Der Standardwert ist OFF. Wenn für ISOLATE_SECURITY_POLICY_CARDINALITY ON festgelegt ist, beeinflussen RLS-Prädikate die Kardinalität von Ausführungsplänen nicht. Angenommen, es gibt eine Tabelle mit 1 Million Zeilen und ein RLS-Prädikat, das das Ergebnis für einen bestimmten Benutzer, der die Abfrage durchführt, auf 10 Zeilen beschränkt. Wenn für diese datenbankweite Konfiguration OFF festgelegt ist, schätzt dieses Prädikat die Kardinalität auf 10. Wenn diese Datenbankbereichskonfiguration aktiviert ist, schätzt die Abfrageoptimierung 1 Millionen Zeilen. Für die meisten Arbeitsauslastungen wird empfohlen, den Standardwert zu verwenden.

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

Gilt für: nur Azure Synapse Analytics

Dieses Argument legt fest, dass das Transact-SQL- und Abfrageverarbeitungsverhalten jeweils mit der angegebenen Version der Datenbank-Engine kompatibel sein muss. Sobald sie festgelegt ist, werden beim Ausführen einer Abfrage in dieser Datenbank nur die kompatiblen Features ausgeübt. Bei jedem Kompatibilitätsgrad werden verschiedene Verbesserungen der Abfrageverarbeitung unterstützt. Jeder Grad übernimmt die Funktionalität des vorangehenden Grads. Beim Erstellen einer Datenbank wird für den Kompatibilitätsgrad standardmäßig AUTO festgelegt. Dies ist auch die empfohlene Einstellung. Der Kompatibilitätsgrad wird auch nach dem Anhalten/Fortsetzen einer Datenbank sowie Sicherungs-/Wiederherstellungsvorgängen beibehalten. Die Standardeinstellung ist AUTO.

Kompatibilitätsgrad Kommentare
AUTO Standard. Der Wert wird automatisch von der Synapse Analytics-Engine aktualisiert und wird von 0 in sys.database_scoped_configurations dargestellt. AUTO wird derzeit der Funktionalität des Kompatibilitätsgrads 30 zugeordnet.
10 Bei diesem Wert wird das Transact-SQL- und Abfrage-Engine-Verhalten ausgeführt, bevor Unterstützung für den Kompatibilitätsgrad eingeführt wird.
20 Hierbei handelt es sich um den ersten Kompatibilitätsgrad mit geschlossenem Transact-SQL- und Abfrage-Engine-Verhalten. Die gespeicherte Systemprozedur sp_describe_undeclared_parameters wird unter diesem Grad unterstützt.
30 Dieser Wert bietet neue Verhaltensweisen der Abfrage-Engine.
40 Dieser Wert bietet neue Verhaltensweisen der Abfrage-Engine.
50 Die mehrspaltige Verteilung wird auf dieser Ebene unterstützt. Weitere Informationen finden Sie unter CREATE TABLE, CREATE TABLE AS SELECT und CREATE MATERIALIZED VIEW.
9000 Vorschau für Kompatibilitätsgrad. Vorschaufeatures unter diesem Grad werden in der featurespezifischen Dokumentation beschrieben. Dieser Grad umfasst auch Fähigkeiten des höchsten Nicht-9000-Grads.

EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS = { ON | OFF }

Gilt für: Azure SQL-Datenbank und Azure SQL Managed Instance

Hiermit können Sie steuern, ob Ausführungsstatistiken für benutzerdefinierte Skalarfunktionen (UDF) in der Systemsicht sys.dm_exec_function_stats angezeigt werden. Bei einigen intensiven Workloads, die skalare UDF-schwer sind, kann das Sammeln von Funktionsausführungsstatistiken zu einem spürbaren Leistungsaufwand führen. Dies kann vermieden werden, indem die Konfiguration auf Datenbankebene, EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS, auf OFF festgelegt wird. Der Standardwert ist ON.

ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2022 (16.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

Wenn die asynchrone Statistikaktualisierung aktiviert ist, bewirkt die Aktivierung dieser Konfiguration, dass die Statistiken zum Aktualisieren von Hintergrundanforderungen auf eine Sperre in einer Sch-M Warteschlange mit niedriger Priorität warten, um zu vermeiden, dass andere Sitzungen in Szenarien mit hoher Parallelität blockiert werden. Weitere Informationen finden Sie unter AUTO_UPDATE_STATISTICS_ASYNC. Der Standardwert ist OFF.

OPTIMIZED_PLAN_FORCING = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2022 (16.x))

Dadurch wird der Kompilierungsaufwand für wiederholte erzwungene Abfragen reduziert. Der Standardwert ist ON. Nachdem der Abfrageausführungsplan generiert wurde, werden bestimmte Kompilierungsschritte für eine Wiederverwendung als Replay-Optimierungsskript gespeichert. Ein Replay-Optimierungsskript wird als Teil des komprimierten Showplan-XML im Abfragespeicher in einem ausgeblendeten Attribut vom Typ OptimizationReplay gespeichert. Weitere Informationen finden Sie unter Erzwingen des optimierten Plans mit dem Abfragespeicher.

DOP_FEEDBACK = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2022 (16.x))

Identifiziert Ineffizienzen bei der Parallelität von sich wiederholenden Abfragen, basierend auf verstrichener Zeit und Wartevorgängen. Wenn die Parallelitätsnutzung als ineffizient erachtet wird, verringert das DOP-Feedback den DOP für die nächste Ausführung der Abfrage ausgehend vom konfigurierten DOP, und überprüft, ob dies hilfreich war. Der Abfragespeicher muss aktiviert sein und sich im Modus „READ_WRITE“ befinden. Weitere Informationen finden Sie unter Feedback zum Grad der Parallelität (DOP). Der Standardwert ist OFF.

CE_FEEDBACK = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2022 (16.x))

Das Feedback zur Kardinalitätsschätzung befasst sich mit wahrgenommenen Regressionsproblemen, die sich aus falschen Modellannahmen der Kardinalitätsschätzung ergeben, wenn der Standardwert für die Kardinalitätsschätzung (CE120 oder höher) verwendet wird. Das Feedback kann selektiv unterschiedliche Modellannahmen verwenden. Der Abfragespeicher muss aktiviert sein und sich im Modus „READ_WRITE“ befinden. Weitere Informationen finden Sie unter Feedback zur Kardinalitätsschätzung (CE). Der Standardwert beträgt bei einem Datenbankkompatibilitätsgrad von mindestens 160 EIN.

PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2022 (16.x))

Die Optimierung des Parameterempfindlichkeitsplans (PSP) wurde für Szenarios entwickelt, in denen ein einzelner zwischengespeicherter Plan für eine parametrisierte Abfrage nicht für alle möglichen eingehenden Parameterwerte optimal ist. Dies ist bei nicht uniformen Datenverteilungen der Fall. Der Standardwert beträgt ab einem Datenbankkompatibilitätsgrad von mindestens 160 EIN. Weitere Informationen finden Sie unter Optimierung des Parameterempfindlichkeitsplans.

LEDGER_DIGEST_STORAGE_ENDPOINT = { <Zeichenfolge_mit_Endpunkt-URL> | OFF }

Gilt für: SQL Server (ab SQL Server 2022 (16.x))

Aktiviert oder Deaktiviert das Hochladen von Ledgerdigests in Azure Blob Storage. Geben Sie den Endpunkt eines Azure Blob Storage-Kontos an, um das Hochladen von Ledgerdigests zu ermöglichen. Um das Hochladen von Ledger-Digests zu deaktivieren, legen Sie den Optionswert auf OFF fest. Der Standardwert ist OFF.

FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2022 (16.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

Veranlasst SQL Server zum Generieren eines Showplan-XML-Fragments mit „ParameterRuntimeValue“ beim Verwenden der einfachen Profilerstellungsinfrastruktur für Abfrageausführungsstatistiken oder beim Ausführen der sys.dm_exec_query_statistics_xml-DMV während der Problembehandlung zeitintensiver Abfragen

Wichtig

Die FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION Konfigurationsoption mit Datenbankbereich soll nicht kontinuierlich in einer Produktionsumgebung aktiviert werden, sondern nur für zeitlich begrenzte Problembehandlungszwecke. Die Verwendung dieser Konfigurationsoption mit Datenbankbereich führt zu einem zusätzlichen und möglicherweise erheblichen CPU- und Arbeitsspeicheraufwand, da wir ein Showplan-XML-Fragment mit Laufzeitparameterinformationen erstellen, unabhängig davon, ob die sys.dm_exec_query_statistics_xml DMV- oder einfache Abfrageausführungsstatistik-Profilinfrastruktur aktiviert ist oder nicht.

Berechtigungen

ALTER ANY DATABASE SCOPED CONFIGURATION ist auf der Datenbank erforderlich. Diese Berechtigung kann von einem Benutzer mit CONTROL-Berechtigung für eine Datenbank erteilt werden.

Bemerkungen

Während Sie sekundäre Datenbanken so konfigurieren können, dass sie verschiedene bereichsbezogene Konfigurationseinstellungen von ihrer primären Datenbank aufweisen, wird für alle sekundären Datenbanken die gleiche Konfiguration verwendet. Für einzelne sekundäre Datenbanken können keine unterschiedlichen Einstellungen konfiguriert werden.

Durch die Ausführung dieser Anweisung wird der Prozedurcache in der aktuellen Datenbank geleert. Dies bedeutet, dass alle Abfragen erneut kompiliert werden müssen.

Bei dreiteiligen Namensabfragen werden die Einstellungen für die aktuelle Datenbankverbindung für die Abfrage berücksichtigt, außer für SQL-Module (z. B. Prozeduren, Funktionen und Trigger), die in einem anderen Datenbankkontext kompiliert werden und daher die Optionen der Datenbank verwenden, in der sie sich befinden. Entsprechend wird beim asynchronen Aktualisieren von ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY Statistiken die Einstellung für die Datenbank, in der sich Statistiken befinden, berücksichtigt.

Das Ereignis ALTER_DATABASE_SCOPED_CONFIGURATION wird als DLL-Ereignis hinzugefügt, mit dem ein DDL-Trigger ausgelöst werden kann, und ist ein untergeordnetes Ereignis der Triggergruppe ALTER_DATABASE_EVENTS.

Wenn eine bestimmte Datenbank wiederhergestellt oder angefügt wird, werden konfigurationseinstellungen mit Datenbankbereich übertragen und erneut Standard mit der Datenbank.

Ab SQL Server 2019 (15.x), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz haben sich einige Optionsnamen geändert:

  • DISABLE_INTERLEAVED_EXECUTION_TVF wurde in INTERLEAVED_EXECUTION_TVF geändert
  • DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK wurde in BATCH_MODE_MEMORY_GRANT_FEEDBACK geändert
  • DISABLE_BATCH_MODE_ADAPTIVE_JOINS wurde in BATCH_MODE_ADAPTIVE_JOINS geändert

Begrenzungen

MAXDOP

Die differenzierten Einstellungen können die globalen Einstellungen überschreiben. Zudem kann die Ressourcenkontrolle alle anderen MAXDOP-Einstellungen begrenzen. Die Logik für die MAXDOP-Einstellung lautet wie folgt:

  • Der Abfragehinweis überschreibt die Einstellung sp_configure und die datenbankweit gültige Konfiguration. Wenn die Ressourcengruppe MAXDOP für die Arbeitsauslastungsgruppe festgelegt ist:

    • Wenn der Abfragehinweis auf „0 (null)“ festgelegt ist, wird er von der Einstellung der Ressourcenkontrolle überschrieben.

    • Wenn der Abfragehinweis nicht auf „0 (null)“ festgelegt ist, wird er von der Einstellung der Ressourcenkontrolle begrenzt.

  • Die datenbankweit gültige Einstellung überschreibt die Einstellung sp_configure (wenn sie nicht auf „0 (null)“ festgelegt ist), sofern kein Abfragehinweis vorhanden ist und sie nicht von der Einstellung der Ressourcenkontrolle begrenzt wird.

  • Die Einstellung sp_configure wird von der Einstellung der Ressourcenkontrolle überschrieben.

QUERY_OPTIMIZER_HOTFIXES

Wenn der Hinweis QUERYTRACEON zur Aktivierung des Standardabfrageoptimierers von SQL Server 7.0 bis SQL Server 2012 (11.x) oder der Hotfixes für den Abfrageoptimierer verwendet wird, bestünde zwischen dem Abfragehinweis und der datenbankweit gültigen Konfigurationseinstellung eine OR-Bedingung. Das heißt, wenn eines davon aktiviert ist, werden die datenbankweiten Konfigurationen angewendet.

Georeplikation von Datenbanken

Lesbare sekundäre Datenbanken (Always On-Verfügbarkeitsgruppen, Azure SQL-Datenbank und georeplizierte Verwaltete Azure SQL-Instanz-Datenbanken) verwenden den sekundären Wert durch Überprüfung des Datenbankstatus. Obwohl es bei einer erneuten Kompilierung nicht zu einem Failover kommt und die neue primäre Datenbank eigentlich Abfragen aufweist, die Einstellungen für die sekundären Datenbanken verwenden, ist die Idee, dass die Einstellungen zwischen der primären und der sekundären Datenbank nur bei unterschiedlicher Arbeitsauslastung variieren. Daher verwenden die zwischengespeicherten Abfragen die optimalen Einstellungen, während neue Abfragen die neuen, für sie geeigneten Einstellungen auswählen.

DacFX

Da ALTER DATABASE SCOPED CONFIGURATION ein neues Feature in Azure SQL-Datenbank, Verwaltete Azure SQL-Instanz und SQL Server (ab SQL Server 2016 (13.x)) ist, das sich auf das Datenbankschema auswirkt, können Exporte des Schemas (mit oder ohne Daten) nicht in ältere Versionen von SQL Server (z. B. SQL Server 2012 (11.x) oder SQL Server 2014 (12.x)) importiert werden. Ein Export in ein DACPAC oder ein BACPAC aus einer Datenbank von SQL-Datenbank oder SQL Server 2016 (13.x), in der dieses neue Feature verwendet wird, könnte nicht in einen Server der Vorgängerversion importiert werden.

ELEVATE_ONLINE

Diese Option gilt nur für DDL-Anweisungen, die WITH (ONLINE = <syntax>) unterstützen. XML-Indizes sind nicht betroffen.

ELEVATE_RESUMABLE

Diese Option gilt nur für DDL-Anweisungen, die WITH (RESUMABLE = <syntax>) unterstützen. XML-Indizes sind nicht betroffen.

Metadaten

Die Systemsicht sys.database_scoped_configurations (Transact-SQL) enthält Informationen zu bereichsbezogenen Konfigurationen innerhalb einer Datenbank. Datenbankbezogene Konfigurationsoptionen werden nur in sys.database_scoped_configurations angezeigt, da sie serverweite Standardeinstellungen überschreiben. In der Systemsicht sys.configurations (Transact-SQL) werden nur serverweite Einstellungen angezeigt.

Beispiele

Folgende Beispiele veranschaulichen die Verwendung von ALTER DATABASE SCOPED CONFIGURATION

A. Berechtigung erteilen

In diesem Beispiel wird dem Benutzer Joe die Berechtigung erteilt, die zum Ausführen von ALTER DATABASE SCOPED CONFIGURATION erforderlich ist.

GRANT ALTER ANY DATABASE SCOPED CONFIGURATION to [Joe] ;

B. Festlegen von MAXDOP

In diesem Beispiel wird in einem Georeplikationsszenario bei einer primären Datenbank MAXDOP = 1 und bei einer sekundären Datenbank MAXDOP = 4 festgelegt.

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

In diesem Beispiel wird in einem Georeplikationsszenario der Wert für MAXDOP bei einer sekundären Datenbank auf den gleichen Wert wie für die zugehörige primäre Datenbank festgelegt.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY ;

C. Festlegen von LEGACY_CARDINALITY_ESTIMATION

In diesem Beispiel wird LEGACY_CARDINALITY_ESTIMATION in einem Georeplikationsszenario bei einer sekundären Datenbank auf ON festgelegt.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = ON ;

In diesem Beispiel wird der Wert für LEGACY_CARDINALITY_ESTIMATION in einem Georeplikationsszenario bei einer sekundären Datenbank auf den gleichen Wert wie für die zugehörige primäre Datenbank festgelegt.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY ;

D: Festlegen von PARAMETER_SNIFFING

In diesem Beispiel wird PARAMETER_SNIFFING in einem Georeplikationsszenario bei einer primären Datenbank auf OFF festgelegt.

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF ;

In diesem Beispiel wird PARAMETER_SNIFFING für eine sekundäre Datenbank in einem Georeplikationsszenario auf OFF festgelegt.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = OFF ;

In diesem Beispiel wird der Wert für PARAMETER_SNIFFING in einem Georeplikationsszenario bei einer sekundären Datenbank auf den gleichen Wert festgelegt wie für die zugehörige primäre Datenbank.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY ;

E. Festlegen von QUERY_OPTIMIZER_HOTFIXES

Legt QUERY_OPTIMIZER_HOTFIXES in einem Georeplikationsszenario bei einer primären Datenbank auf ON fest.

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON ;

F. Leeren des Prozedurcache

In diesem Beispiel wird der Prozedurcache geleert (dies ist nur bei einer primären Datenbank möglich).

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

G. Festlegen von IDENTITY_CACHE

Gilt für: SQL Server (ab SQL Server 2017 (14.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

In diesem Beispiel wird der Identitätscache deaktiviert.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF ;

H. Festlegen von OPTIMIZE_FOR_AD_HOC_WORKLOADS

Gilt für: SQL Server (ab SQL Server 2019 (15.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

In diesem Beispiel wird ein Stub des kompilierten Plans aktiviert, der bei der erstmaligen Kompilierung eines Batches im Cache gespeichert werden soll.

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;

I. Festlegen von ELEVATE_ONLINE

Gilt für: SQL Server (ab SQL Server 2019 (15.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

In diesem Beispiel wird ELEVATE_ONLINE auf FAIL_UNSUPPORTED festgelegt.

ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE = FAIL_UNSUPPORTED ;

J. Festlegen von ELEVATE_RESUMABLE

Gilt für: SQL Server (ab SQL Server 2019 (15.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

In diesem Beispiel wird ELEVATE_RESUMABLE auf WHEN_SUPPORTED festgelegt.

ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_RESUMABLE = WHEN_SUPPORTED ;

K. Löschen eines Abfrageplans aus dem Plancache

Gilt für: SQL Server (ab SQL Server 2019 (15.x)), Azure SQL-Datenbank und Verwaltete Azure SQL-Instanz

In diesem Beispiel wird ein bestimmter Plan aus dem Prozedurcache gelöscht.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE 0x06000500F443610F003B7CD12C02000001000000000000000000000000000000000000000000000000000000;

L. Festlegen der Pausendauer

Gilt für: Azure SQL-Datenbank und Azure SQL Managed Instance

In diesem Beispiel wird die Pausendauer des fortsetzbaren Index auf 60 Minuten festgelegt.

ALTER DATABASE SCOPED CONFIGURATION
SET PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES = 60

m. Aktivieren und Deaktivieren des Hochladens von Ledger-Digests

Gilt für: SQL Server (ab SQL Server 2022 (16.x))

In diesem Beispiel wird das Hochladen von Ledger-Digests in ein Azure-Speicherkonto aktiviert.

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

In diesem Beispiel wird das Hochladen von Ledger-Digests deaktiviert.

ALTER DATABASE SCOPED CONFIGURATION
SET LEDGER_DIGEST_STORAGE_ENDPOINT = OFF

Weitere Ressourcen

Ressourcen von MAXDOP

Ressourcen von LEGACY_CARDINALITY_ESTIMATION

Ressourcen von PARAMETER_SNIFFING

Ressourcen von QUERY_OPTIMIZER_HOTFIXES

ELEVATE_ONLINE-Ressourcen

Richtlinien für Onlineindexvorgänge

ELEVATE_RESUMABLE-Ressourcen

Richtlinien für Onlineindexvorgänge

Zugehöriger Inhalt