Hinweis
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, sich anzumelden oder das Verzeichnis zu wechseln.
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, das Verzeichnis zu wechseln.
Gilt für:
SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAnalytics Platform System (PDW)
Die optimize for ad hoc workloads-Option wird verwendet, um die Effizienz des Plancaches für Workloads zu verbessern, die viele einmalig verwendete Ad-hoc-Batches 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.
Die Ablaufverfolgungskennzeichnung 8032 setzt die Cache-Limit-Parameter auf die RTM-Einstellung von SQL Server 2005 (9.x) zurück, was im Allgemeinen größere Caches ermöglicht. Verwenden Sie diese Einstellung, wenn häufig wiederverwendete Cacheeinträge nicht in den Cache passen und wenn die optimize for ad hoc workloads Option das Problem mit dem Plancache nicht beheben konnte.
Warnung
Trace-Flag 8032 kann zu schlechter Leistung führen, wenn große Caches den für andere Speicherbenutzer verfügbaren Arbeitsspeicher verringern, wie beispielsweise für den Pufferpool.
Hinweise
Das Festlegen der optimize for ad hoc workloads-Option auf 1 wirkt sich nur auf neue Pläne aus; Pläne, die sich bereits im Plan-Cache befinden, sind 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
@Greetingbei 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.
Wenn die optimize for ad hoc workloads Option aktiviert ist, können Sie keine Ausführungspläne für Abfragen mit einmaliger Verwendung anzeigen, da nur der Planstub 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;