Freigeben über

Automatische Optimierung

Gilt für: SQL Server 2017 (14.x) und höhere Versionen von Azure SQL DatabaseAzure SQL Managed InstanceSQL sql database in Microsoft Fabric

Die automatische Optimierung ist ein Datenbankfeature, das Einblicke in potenzielle Abfrageleistungsprobleme bietet, Lösungen empfiehlt und identifizierte Probleme automatisch beheben kann.

Automatische Optimierung, die in SQL Server 2017 (14.x) eingeführt wurde, benachrichtigt Sie immer dann, wenn ein potenzielles Leistungsproblem erkannt wird, und ermöglicht es Ihnen, Korrekturmaßnahmen anzuwenden oder das Datenbankmodul automatisch Leistungsprobleme zu beheben. Die automatische Optimierung von SQL Server identifiziert und behebt Leistungsprobleme, die durch Regressionen der Abfrageausführungsplanauswahl verursacht werden. Automatische Optimierung in Azure SQL-Datenbank und SQL-Datenbank in Microsoft Fabric erstellt auch erforderliche Indizes und legt nicht verwendete Indizes ab. Weitere Informationen zu Abfrageausführungsplänen finden Sie unter Ausführungspläne.

Das SQL Server-Datenbankmodul überwacht die Abfragen, die in der Datenbank ausgeführt werden, und verbessert automatisch die Leistung der Arbeitsauslastung. Das Datenbankmodul verfügt über einen integrierten Intelligence-Mechanismus, der die Leistung Ihrer Abfragen automatisch optimieren und verbessern kann, indem sie die Datenbank dynamisch an Ihre Workload anpasst. Es gibt zwei automatische Optimierungsfunktionen, die verfügbar sind:

  • Bei der automatischen Plankorrektur werden problematische Abfrageausführungspläne identifiziert, z. B. Parameterempfindlichkeits- oder Parameterverkniffungsprobleme , sowie Leistungsprobleme im Zusammenhang mit der Abfrageausführung behoben, indem der letzte bekannte gute Plan erzwungen wird, bevor die Regression aufgetreten ist. Gilt für: SQL Server (ab SQL Server 2017 (14.x)), Azure SQL-Datenbank und SQL-Datenbank in Microsoft Fabric und azure SQL Managed Instance

  • Die automatische Indexverwaltung identifiziert Indizes, die in Ihrer Datenbank hinzugefügt werden sollen, und Indizes, die entfernt werden sollen. Gilt für: Azure SQL-Datenbank und SQL-Datenbank in Microsoft Fabric

Note

In diesem Artikel gelten Features und Verhaltensweisen der Azure SQL-Datenbank auch für sql-Datenbank in Microsoft Fabric.

Warum automatische Optimierung?

Drei der Hauptaufgaben in der klassischen Datenbankverwaltung überwachen die Arbeitsauslastung, identifizieren kritische Transact-SQL Abfragen und identifizieren Indizes, die zur Verbesserung der Leistung hinzugefügt werden sollen, oder Indizes, die selten verwendet werden, und können entfernt werden, um die Leistung zu verbessern. Das SQL Server-Datenbankmodul bietet detaillierte Einblicke in die Abfragen und Indizes, die Sie überwachen müssen. Die ständige Überwachung einer Datenbank ist jedoch eine schwierige und mühsame Aufgabe, insbesondere bei vielen Datenbanken. Das Verwalten einer großen Anzahl von Datenbanken könnte unmöglich sein, effizient durchzuführen. Anstatt Ihre Datenbank manuell zu überwachen und zu optimieren, sollten Sie möglicherweise einige Überwachungs- und Optimierungsaktionen mithilfe der automatischen Optimierungsfunktion an das Datenbankmodul delegieren.

Wie funktioniert das automatische Tuning?

Die automatische Optimierung ist ein kontinuierlicher Überwachungs- und Analyseprozess, der ständig über die Merkmale Ihrer Workload informiert und potenzielle Probleme und Verbesserungen identifiziert.

Automatischer Optimierungsprozess.

Dieser Prozess ermöglicht es der Datenbank, sich dynamisch an Ihre Workload anzupassen, indem sie herausfinden, welche Indizes und Pläne die Leistung Ihrer Workloads verbessern und welche Indizes sich auf Ihre Workloads auswirken. Basierend auf diesen ermittelten Informationen werden bei der automatischen Optimierung Optimierungsaktionen angewendet, um die Leistung Ihrer Workload zu verbessern. Darüber hinaus überwacht die automatische Optimierung kontinuierlich die Leistung der Datenbank nach der Implementierung von Änderungen, um sicherzustellen, dass sie die Leistung Ihrer Workload verbessert. Alle Aktionen, die nicht zu einer Leistungsverbesserung führen, werden automatisch rückgängig gemacht. Dieser Überprüfungsprozess ist ein wichtiges Feature, mit dem sichergestellt wird, dass änderungen, die durch die automatische Optimierung vorgenommen werden, die Gesamtleistung Ihrer Workload nicht verringern.

Automatische Plankorrektur

Die automatische Plankorrektur ist ein Feature zur automatischen Optimierung, das die Regression der Ausführungsplanauswahl identifiziert und das Problem automatisch behebt, indem der letzte bekannte gute Plan erzwungen wird. Weitere Informationen zu Abfrageausführungsplänen und dem Abfrageoptimierer finden Sie im Leitfaden zur Abfrageverarbeitungsarchitektur.

Important

Die automatische Plankorrektur hängt davon ab, dass der Query Store in der Datenbank zur Nachverfolgung von Workloads aktiviert ist.

Was ist die Regressionsanalyse der Auswahl des Ausführungsplans?

Das SQL Server-Datenbankmodul verwendet möglicherweise unterschiedliche Ausführungspläne, um die Transact-SQL Abfragen auszuführen. Abfragepläne hängen von den Statistiken, Indizes und anderen Faktoren ab. Der optimale Plan, der zum Ausführen einer Transact-SQL Abfrage verwendet werden soll, kann sich je nach Änderungen dieser Faktoren im Laufe der Zeit ändern. In einigen Fällen ist der neue Plan möglicherweise nicht besser als der vorherige Plan, und der neue Plan kann eine Leistungsregression verursachen, wie z. B. ein Parameterempfindlichkeits- oder Parameter-Sniffing-Problem.

Regression bei der Auswahl des Abfrageausführungsplans.

Immer wenn Sie feststellen, dass ein Rückgang bei der Auswahl des Plans aufgetreten ist, sollten Sie einen vorherigen guten Plan finden und ihn anstelle des aktuellen erzwingen. Dies kann mithilfe des sp_query_store_force_plan Verfahrens erfolgen. Das Datenbankmodul in SQL Server 2017 (14.x) enthält Informationen zu zurückgetretenen Plänen und empfohlenen Korrekturmaßnahmen. Darüber hinaus ermöglicht Ihnen das Datenbankmodul, diesen Prozess vollständig zu automatisieren und alle Probleme zu beheben, die im Zusammenhang mit der Planänderung gefunden wurden.

Important

Die automatische Plankorrektur sollte im Rahmen eines Upgrades auf Datenbankkompatibilitätsebene verwendet werden, nachdem ein Basisplan erfasst wurde, um die Risiken des Workloadupgrades automatisch zu mindern. Weitere Informationen zu diesem Anwendungsfall finden Sie unter Beibehalten der Leistungsstabilität während des Upgrades auf neuere SQL Server.

Automatische Korrektur der Planauswahl

Das Datenbankmodul kann automatisch zu dem letzten bekannten guten Plan wechseln, wenn eine Planauswahlregression erkannt wird.

Korrektur der Wahl des Abfrageausführungsplans.

Das Datenbankmodul erkennt automatisch jede mögliche Planauswahlregression, einschließlich des Plans, der anstelle des falschen Plans verwendet werden soll. Der resultierende Ausführungsplan, der von der automatischen Plankorrektur erzwungen wird, ist identisch oder ähnlich wie der letzte bekannte gute Plan. Da der resultierende Plan möglicherweise nicht mit dem letzten bekannten guten Plan identisch ist, kann die Leistung des erzwungenen Plans variieren. In seltenen Fällen kann der Leistungsunterschied signifikant und negativ sein; In diesem Fall wird die automatische Plankorrektur automatisch den Versuch beenden, den Ersetzungsplan zu erzwingen.

Wenn das Datenbankmodul den letzten bekannten guten Plan vor dem Auftreten der Regression anwendet, überwacht es automatisch die Leistung des erzwungenen Plans. Wenn der erzwungene Plan nicht besser als der regressierte Plan ist, wird der neue Plan nicht erzwungen, und das Datenbankmodul kompiliert einen neuen Plan. Wenn das Datenbankmodul überprüft, ob der erzwungene Plan besser als der zurückgeschrittene Plan ist, wird der erzwungene Plan beibehalten. Sie wird beibehalten, bis eine erneute Kompilierung erfolgt (z. B. bei der nächsten Statistikaktualisierung oder Schemaänderung). Weitere Informationen über das Erzwingen von Plänen und die Typen von Plänen, die erzwungen werden können, finden Sie unter Einschränkungen des Erzwingens von Plänen.

Note

Wenn die SQL Server-Instanz neu gestartet wird, bevor die erzwungene Ausführung eines Plans überprüft wird, wird der Plan automatisch nicht mehr erzwungen. Andernfalls bleibt die Erzwingung des Plans bei SQL Server-Neustarts erhalten.

Aktivierung der automatischen Planwahlkorrektur

Sie können die automatische Optimierung pro Datenbank aktivieren und angeben, dass der letzte gute Plan erzwungen werden soll, wenn eine Planänderungsregression erkannt wird. Die automatische Optimierung wird mit dem folgenden Befehl aktiviert:

ALTER DATABASE <yourDatabase>
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

Nachdem Sie diese Option aktiviert haben, erzwingt das Datenbankmodul automatisch jede Empfehlung, in der die geschätzte CPU-Verstärkung höher als 10 Sekunden ist, oder die Anzahl der Fehler im neuen Plan ist höher als die Anzahl der Fehler im empfohlenen Plan, und stellen Sie sicher, dass der erzwungene Plan besser ist als die aktuelle.

Informationen zum Aktivieren der automatischen Optimierung in Azure SQL-Datenbank und der verwalteten Azure SQL-Instanz finden Sie unter Aktivieren der automatischen Optimierung in azure SQL-Datenbank mithilfe des Azure-Portals.

Alternative – Korrektur der manuellen Planauswahl

Ohne automatische Optimierung müssen Benutzer das System regelmäßig überwachen und nach den Abfragen suchen, die sich verschlechtert haben. Wenn ein Plan zurückgefallen ist, sollte der Benutzer einen vorherigen guten Plan finden und diesen anstelle des aktuellen Plans mithilfe der sp_query_store_force_plan-Prozedur erzwingen. Die bewährte Methode wäre, den letzten bekannten guten Plan zu erzwingen, da ältere Pläne aufgrund von Statistik- oder Indexänderungen möglicherweise ungültig sind. Der Benutzer, der den letzten bekannten guten Plan erzwingt, sollte die Leistung der Abfrage überwachen, die mit dem erzwungenen Plan ausgeführt wird, und überprüfen, ob der erzwungene Plan erwartungsgemäß funktioniert. Abhängig von den Ergebnissen der Überwachung und Analyse sollte der Plan erzwungen werden, oder der Benutzer sollte eine andere Möglichkeit finden, die Abfrage zu optimieren, z. B. das Umschreiben. Manuell erzwungene Pläne sollten nicht für immer erzwungen werden, da das Datenbankmodul in der Lage sein sollte, optimale Pläne anzuwenden. Der Benutzer oder DBA sollte den Plan schließlich unter Verwendung des Verfahrens sp_query_store_unforce_plan aufheben und das Datenbankmodul den optimalen Plan finden lassen.

Tip

Alternativ können Sie die Abfragespeicheransicht "Abfragen mit erzwungenen Plänen" verwenden, um Pläne zu suchen und deren Erzwingung aufzuheben.

SQL Server stellt alle erforderlichen Ansichten und Prozeduren bereit, die zum Überwachen der Leistung und zum Beheben von Problemen im Abfragespeicher erforderlich sind.

In SQL Server 2016 (13.x) finden Sie Planauswahlregressionen mithilfe von Abfragespeicher-Systemansichten. Ab SQL Server 2017 (14.x) erkennt und zeigt das Datenbankmodul potenzielle Planauswahlregressionen und die empfohlenen Aktionen an, die in der sys.dm_db_tuning_recommendations (Transact-SQL) DMV angewendet werden sollen. Der DMV zeigt Informationen über das Problem, die Wichtigkeit des Problems und Details wie die identifizierte Abfrage, die ID des zurückgeschrittenen Plans, die ID des Plans, der als Basisplan für den Vergleich verwendet wurde, und die Transact-SQL Anweisung, die ausgeführt werden kann, um das Problem zu beheben.

type description datetime score details ...
FORCE_LAST_GOOD_PLAN CPU-Zeit von 4 ms auf 14 ms geändert 3/17/2017 83 queryId recommendedPlanId regressedPlanId T-SQL
FORCE_LAST_GOOD_PLAN CPU-Zeit von 37 ms auf 84 ms geändert 3/16/2017 26 queryId recommendedPlanId regressedPlanId T-SQL

Einige Spalten aus dieser Ansicht werden in der folgenden Liste beschrieben:

  • Typ der empfohlenen Aktion FORCE_LAST_GOOD_PLAN.
  • Beschreibung, die Informationen enthält, warum das Datenbankmodul der Meinung ist, dass diese Planänderung eine mögliche Leistungsregression ist.
  • Datum und Uhrzeit, wenn eine potenzielle Regression erkannt wird.
  • Bewertung dieser Empfehlung.
  • Details zu den Problemen, z. B. ID des erkannten Plans, ID des zurückgesetzten Plans, ID des Plans, der gezwungen werden sollte, das Problem zu beheben, Transact-SQL Skript, das möglicherweise angewendet werden kann, um das Problem zu beheben usw. Details werden im JSON-Format gespeichert.

Verwenden Sie die folgende Abfrage, um ein Skript zu erhalten, das das Problem behebt, und zusätzliche Informationen zum geschätzten Gewinn:

SELECT reason, score,
      script = JSON_VALUE(details, '$.implementationDetails.script'),
      planForceDetails.*,
      estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount)
                  * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage)/1000000,
      error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES','NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
    WITH (  [query_id] int '$.queryId',
            regressedPlanId int '$.regressedPlanId',
            recommendedPlanId int '$.recommendedPlanId',
            regressedPlanErrorCount int,
            recommendedPlanErrorCount int,
            regressedPlanExecutionCount int,
            regressedPlanCpuTimeAverage float,
            recommendedPlanExecutionCount int,
            recommendedPlanCpuTimeAverage float
          ) AS planForceDetails;

Hier ist das Ergebnis.

reason score script query_id aktuelle plan_id empfohlene Plan-ID geschätzter_Gewinn fehleranfällig
CPU-Zeit von 3 ms auf 46 ms geändert 36 EXEC sp_query_store_force_plan 12, 17; 12 28 17 11.59 0

Die Spalte estimated_gain stellt die geschätzte Anzahl von Sekunden dar, die gespeichert werden würden, wenn der empfohlene Plan für die Abfrageausführung anstelle des aktuellen Plans verwendet würde. Der empfohlene Plan sollte durchgesetzt werden, anstelle des aktuellen Plans, wenn der Gewinn größer als 10 Sekunden ist. Wenn im aktuellen Plan mehr Fehler (z. B. Timeouts oder abgebrochene Ausführungen) vorhanden sind als im empfohlenen Plan, wird die Spalte error_prone auf den Wert YESfestgelegt. Ein fehleranfälliger Plan ist ein weiterer Grund, warum der empfohlene Plan anstelle des aktuellen Plans erzwungen werden sollte.

Obwohl das Datenbankmodul alle Informationen bereitstellt, die zum Identifizieren von Planauswahlregressionen erforderlich sind, kann die kontinuierliche Überwachung und Behebung von Leistungsproblemen zu einem mühsamen Prozess werden. Die automatische Optimierung erleichtert diesen Prozess erheblich.

Note

Die Daten im sys.dm_db_tuning_recommendations DMV werden nach einem Neustart des Datenbankmoduls nicht beibehalten. Verwenden Sie die sqlserver_start_time-Spalte in sys.dm_os_sys_info, um die aktuellste Startzeit der Datenbank-Engine zu suchen.

Automatische Indexverwaltung

In der Azure SQL-Datenbank ist die Indexverwaltung einfach, da Die Azure SQL-Datenbank ihre Arbeitsauslastung kennenlernt und sicherstellt, dass Ihre Daten immer optimal indiziert werden. Das richtige Indexdesign ist entscheidend für eine optimale Leistung Ihrer Workload, und die automatische Indexverwaltung kann Ihnen dabei helfen, Ihre Indizes zu optimieren. Die automatische Indexverwaltung kann Leistungsprobleme in falsch indizierten Datenbanken beheben oder Indizes im vorhandenen Datenbankschema verwalten und verbessern. Die automatische Optimierung in der Azure SQL-Datenbank führt die folgenden Aktionen aus:

  • Identifiziert Indizes, die die Leistung Ihrer Transact-SQL Abfragen verbessern könnten, die Daten aus den Tabellen lesen.
  • Identifiziert redundante Indizes oder Indizes, die in längerem Zeitraum nicht verwendet wurden, die entfernt werden konnten. Durch das Entfernen unnötiger Indizes wird die Leistung von Abfragen verbessert, die Daten in Tabellen aktualisieren.

Warum benötigen Sie die Indexverwaltung?

Indizes beschleunigen einige Ihrer Abfragen, die Daten aus den Tabellen lesen; aber sie können die Abfragen verlangsamen, die Daten aktualisieren. Sie müssen sorgfältig analysieren, wann sie einen Index erstellen und welche Spalten in den Index einbezogen werden müssen. Einige Indizes sind möglicherweise nach einiger Zeit nicht mehr erforderlich. Daher müssen Sie diese Indizes regelmäßig identifizieren und ablegen, die keine Vorteile bringen. Wenn Sie die nicht verwendeten Indizes ignorieren, wird die Leistung der Abfragen, die Daten aktualisieren, verringert, ohne den Abfragen, die Daten lesen, irgendeinen Nutzen zu bringen. Nicht verwendete Indizes wirken sich auch auf die Gesamtleistung des Systems aus, da zusätzliche Updates eine unnötige Protokollierung erfordern.

Die Suche nach den optimalen Indizes, die die Leistung der Abfragen verbessern, die Daten aus Ihren Tabellen lesen und minimale Auswirkungen auf Updates haben, kann eine kontinuierliche und komplexe Analyse erfordern.

Azure SQL-Datenbank verwendet integrierte Intelligenz und erweiterte Regeln, die Ihre Abfragen analysieren, Indizes identifizieren, die für Ihre aktuellen Workloads optimal wären, und die Indizes zu identifizieren, die möglicherweise entfernt werden müssen. Die Azure SQL-Datenbank stellt sicher, dass Sie über einen minimalen erforderlichen Satz von Indizes verfügen, mit denen die Abfragen optimiert werden, die Daten lesen, wobei die Auswirkungen auf die anderen Abfragen minimiert sind.

Automatische Indexverwaltung

Zusätzlich zur Erkennung kann die Azure SQL-Datenbank automatisch identifizierte Empfehlungen anwenden. Wenn Sie feststellen, dass die integrierten Regeln die Leistung Ihrer Datenbank verbessern, können Sie azure SQL-Datenbank ihre Indizes automatisch verwalten lassen.

Wenn azure SQL-Datenbank eine CREATE INDEX- oder DROP INDEX-Empfehlung anwendet, überwacht sie automatisch die Leistung der abfragen, die vom Index betroffen sind. Der neue Index wird nur beibehalten, wenn die Leistung der betroffenen Abfragen verbessert wird. Verworfener Index wird automatisch neu erstellt, wenn einige Abfragen aufgrund des Fehlens des Indexes langsamer ausgeführt werden.

Überlegungen zur automatischen Indexverwaltung

Aktionen, die zum Erstellen erforderlicher Indizes in der Azure SQL-Datenbank erforderlich sind, verbrauchen möglicherweise Ressourcen und wirken sich zeitlich auf die Arbeitsauslastungsleistung aus. Um die Auswirkungen der Indexerstellung auf die Arbeitsauslastung zu minimieren, findet Azure SQL-Datenbank ein geeignetes Zeitfenster für jeden Indexverwaltungsvorgang. Die Optimierungsaktion wird verschoben, wenn die Datenbank Ressourcen benötigt, um Ihre Workload auszuführen, und wird neu gestartet, wenn die Datenbank über genügend nicht verwendete Ressourcen verfügt, die für den Wartungsvorgang verwendet werden können. Ein wichtiges Feature bei der automatischen Indexverwaltung ist eine Überprüfung der Aktionen. Wenn Azure SQL-Datenbank einen Index erstellt oder abbricht, analysiert ein Überwachungsprozess die Leistung Ihrer Workload, um zu überprüfen, ob die Aktion die Gesamtleistung verbessert hat. Wenn sie keine erhebliche Verbesserung brachte - die Aktion wird sofort zurückgesetzt. Auf diese Weise stellt Die Azure SQL-Datenbank sicher, dass sich automatische Optimierungsaktionen nicht negativ auf die Leistung Ihrer Workload auswirken. Mit der automatischen Optimierung erstellte Indizes sind für den Wartungsvorgang im zugrunde liegenden Schema transparent. Schemaänderungen wie das Ablegen oder Umbenennen von Spalten werden nicht durch das Vorhandensein automatisch erstellter Indizes blockiert. Indizes, die automatisch von der Azure SQL-Datenbank erstellt werden, werden sofort gelöscht, wenn die verknüpfte Tabelle oder Spalten gelöscht werden.

Alternative – manuelle Indexverwaltung

Ohne die automatische Indexverwaltung müsste ein Benutzer oder DBA die sys.dm_db_missing_index_details (Transact-SQL) -Ansicht manuell abfragen oder den Performance Dashboard-Bericht in Management Studio verwenden, um Indizes zu finden, die die Leistung verbessern, Indizes mithilfe der in dieser Ansicht angegebenen Details erstellen und die Leistung der Abfrage manuell überwachen. Um die Indizes zu finden, die verworfen werden sollen, sollten Die Benutzer die Betriebsnutzungsstatistiken der Indizes überwachen, um selten verwendete Indizes zu finden.

Azure SQL-Datenbank vereinfacht diesen Vorgang. Die Azure SQL-Datenbank analysiert Ihre Arbeitsauslastung, identifiziert die Abfragen, die mit einem neuen Index schneller ausgeführt werden können, und identifiziert nicht verwendete oder duplizierte Indizes. Weitere Informationen zur Identifizierung von Indizes, die geändert werden sollten, finden Sie unter "Indexempfehlungen suchen" im Azure-Portal.

Nächste Schritte

  • Last updated on