optimize for ad hoc workloads (Serverkonfigurationsoption)

  • Artikel

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAnalytics Platform System (PDW)

Die Option Optimieren für Ad-hoc-Arbeitsauslastung wird zum Verbessern der Effizienz des Plancaches für Arbeitsauslastungen verwendet, die viele Ad-hoc-Batches für die einmalige Verwendung enthalten. Wenn diese Option auf 1 festgelegt ist, speichert die Datenbank-Engine statt des vollständigen kompilierten Plans einen kleinen Stub des kompilierten Plans in dem Plancache, wenn ein Batch erstmalig ausgeführt wird. Durch diese Option kann der Arbeitsspeicher entlastet werden, da der Plancache nicht mit kompilierten Plänen gefüllt werden darf, die nicht wiederverwendet werden. Die Aktivierung dieser Option wirkt sich jedoch unter Umständen auf Ihre Fähigkeit aus, Probleme mit einmalig genutzten Plänen zu beheben.

Durch den Stub des kompilierten Plans erkennt die Datenbank-Engine, dass der jeweilige Ad-hoc-Batch zuvor kompiliert wurde, und speichert nur einen Stub des kompilierten Plans. Wenn dieser Batch erneut aufgerufen (kompiliert oder ausgeführt) wird, kompiliert die Datenbank-Engine den Batch, entfernt den Stub des kompilierten Plans aus dem Plancache und fügt den vollständig kompilierten Plan dem Plancache hinzu.

Sie können kompilierte Plan-Stubs finden, indem Sie die sys.dm_exec_cached_plans-Katalogansicht abfragen und nach „Kompilierten Plan“ in der Spalte cacheobjtype suchen. Der Stub hat einen eindeutigen plan_handle. Dem Stub des kompilierten Plans ist kein Ausführungsplan zugeordnet. Die Abfrage des Planhandles gibt keinen grafischen oder XML-Showplan zurück.

Das Ablaufverfolgungsflag 8032 setzt die Parameter für das Cachelimit auf die RTM-Einstellung von SQL Server 2005 (9.x) zurück, die im Allgemeinen einen größeren Cache zulässt. Verwenden Sie diese Einstellung, wenn häufig wiederverwendete Cacheeinträge nicht in den Cache passen und das Problem mit dem Plancache durch die Option Für Ad-hoc-Workloads optimieren nicht behoben werden konnte.

Warnung

Das Ablaufverfolgungsflag 8032 kann die Leistung mindern, wenn große Caches weniger Arbeitsspeicher für andere Arbeitsspeicherconsumer, z. B. den Pufferpool, zur Verfügung stellen.

Hinweise

Wenn Optimieren für Ad-hoc-Arbeitsauslastungen auf 1 festgelegt wird, wirkt sich dies ausschließlich auf neue Pläne aus. Pläne, die sich bereits im Plancache befinden, sind davon nicht betroffen.

Um bereits zwischengespeicherte Abfragepläne sofort zu beeinflussen, muss der Plancache mit ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE gelöscht oder SQL Server neu gestartet werden.

Empfehlungen

Achten Sie darauf, dass nur eine geringe Anzahl von Plänen im Plancache enthalten sind. Häufige Ursachen sind:

  • Datentypen von Abfrageparametern, die nicht einheitlich definiert sind. Dies gilt zwar insbesondere für die Länge der Zeichenfolgen, aber auch für jeden Datentyp, für den eine maximale Länge, eine Genauigkeit oder eine Staffelung festgelegt ist. Wenn beispielsweise ein Parameter mit dem Namen @Greeting bei einem Aufruf als nvarchar(10) und bei einem anderen als nvarchar(20) zurückgegeben wird, wird für jede Parametergröße ein anderer Plan erstellt.

  • Abfragen, die nicht parametrisiert sind. Wenn eine Abfrage einen oder mehrere Parameter enthält, für die hartcodierte Werte an die Datenbank-Engine übermittelt werden, kann für jede Abfrage eine große Anzahl von Abfrageplänen vorliegen. Für jede Kombination der verwendeten Datentypen und -längen der Abfrageparameter können eigene Pläne vorliegen.

Wenn die Anzahl von einmal genutzten Plänen einen beträchtlichen Teil des Speichers für SQL Server-Datenbank-Engine in einem OLTP-Server beansprucht, und wenn diese Pläne Ad-hoc-Pläne sind, verwenden Sie diese Serveroption, um die Speichernutzung mit diesen Objekten zu verringern.

Bei aktivierter Option Für Ad-hoc-Workloads optimieren können Sie keine Ausführungspläne für einmalig verwendete Abfragen anzeigen, da nur der Plan-Stub zwischengespeichert wird. Je nach Umgebung und Workload können Sie unter Umständen von den folgenden beiden Features profitieren:

  • Das in SQL Server 2016 (13.x) eingeführte Feature Abfragespeicher hilft Ihnen, Leistungsunterschiede, die durch Änderungen des Abfrageplans verursacht werden, schnell aufzuspüren. Der Abfragespeicher ist in neuen Datenbanken in SQL Server 2022 (16.x) und höheren Versionen standardmäßig aktiviert.

  • Die erzwungene Parametrisierung kann die Leistungsfähigkeit bestimmter Datenbanken erhöhen, indem die Frequenz der Kompilierungen und Neukompilierungen von Anweisungen verringert wird. Von der erzwungenen Parametrisierung profitieren Datenbanken, die in der Regel eine große Anzahl gleichzeitiger Abfragen verarbeiten müssen, wie z. B. Point-of-Sale-Anwendungen.

    Die erzwungene Parametrisierung kann aufgrund der Parametersensitivität zu Leistungsproblemen führen. Weitere Informationen finden Sie unter Untersuchen und Beheben von Problemen mit der Parametersensitivität. Für SQL Server 2022 (16.x) und höhere Versionen können Sie außerdem die Optimierung parametersensitiver Pläne aktivieren.

Beispiele

Um die Anzahl von zwischengespeicherten einmal genutzten Plänen zu ermitteln, führen Sie die folgende Abfrage aus:

SELECT objtype,
    cacheobjtype,
    SUM(refcounts) AS AllRefObjects,
    SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS SizeInMB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc'
    AND usecounts = 1
GROUP BY objtype, cacheobjtype;

Zugehöriger Inhalt