Automatische Optimierung

  • Artikel

Gilt für: SQL Server 2017 (14.x) und höher Azure SQL-DatenbankAzure SQL Managed Instance

Die automatische Datenbankoptimierung bietet einen Einblick in die potentiellen Abfrageleistungsprobleme, empfiehlt Lösungen und kann identifizierte Probleme automatisch beheben.

Die automatische Optimierung in SQL Server 2017 (14.x) benachrichtigt Sie, wenn ein mögliches Leistungsproblem erkannt wird. Sie können mit Ihr Korrekturmaßnahme ergreifen. Zudem kann Datenbank-Engine die Leistungsprobleme automatisch beheben. Die automatische Optimierung von SQL Server identifiziert und behebt Leistungsprobleme, die durch Regressionen der Abfrageausführungsplanauswahl verursacht werden. Die automatische Optimierung in Azure SQL-Datenbank erstellt auch erforderliche Indizes und löscht nicht verwendete Indizes. Weitere Informationen zu Abfrageausführungsplänen finden Sie unter Ausführungspläne.

Die SQL Server-Datenbank-Engine überwacht die Abfragen, die in der Datenbank ausgeführt werden, und verbessert automatisch die Leistung bei der Bewältigung der Workload. Die Datenbank-Engine verfügt über ein integriertes Intelligence-Verfahren, mit dem die Leistung Ihrer Abfragen automatisch optimiert und verbessert werden kann, indem die Datenbank dynamisch Ihrer Workload angepasst wird. Zwei automatische Optimierungsfeatures sind verfügbar:

  • Automatische Plankorrektur identifiziert problematische Abfrageausführungspläne, z. B. Probleme bezüglich Parameterempfindlichkeit oder Parameterermittlung, und behebt auf den Abfrageausführungsplan bezogene Leistungsprobleme, indem der letzte bekannte gute Plan vor dem Auftreten der Regression erzwungen wird. Gilt für: SQL Server (ab SQL Server 2017 (14.x)) und Azure SQL-Datenbank und azure SQL Managed Instance]

  • Automatische Indexverwaltung: Diese Funktion identifiziert Indizes, die der Datenbank hinzugefügt, und solche, die entfernt werden sollten. Gilt für: Azure SQL-Datenbank

Gründe für die automatische Optimierung

Drei Hauptaufgaben in der klassischen Datenbankverwaltung sind die Überwachung der Workload, die Identifizierung kritischer Transact-SQL-Abfragen und die Identifizierung von Indizes, die hinzugefügt werden sollten, um die Leistung zu verbessern, oder von Indizes, die selten verwendet werden und entfernt werden könnten, um die Leistung zu verbessern. Die SQL Server-Datenbank-Engine bietet detaillierte Einblicke in die Abfragen und Indizes, die Sie überwachen sollten. Eine kontinuierliche Überwachung der Datenbank ist jedoch eine schwierige und aufwendige Aufgabe, insbesondere bei sehr vielen Datenbanken. Die effiziente Verwaltung einer großen Anzahl von Datenbanken könnte unmöglich sein. Anstelle der manuellen Überwachung und Optimierung der Datenbank sollten Sie erwägen, einige der Überwachungs- und Optimierungsaktionen mithilfe des Features zur automatischen Optimierung an die Datenbank-Engine zu delegieren.

Wie funktioniert die automatische Optimierung?

Die automatische Optimierung ist ein fortlaufender Prozess zum Überwachen und Analysieren der Leistung, bei dem die Merkmale Ihrer Workload ständig verfolgt und potenzielle Probleme und Verbesserungsmöglichkeiten identifiziert werden.

Automatic tuning process.

Dieser Prozess ermöglicht der Datenbank eine dynamische Anpassung Ihrer Workload, indem ermittelt wird, welche Indizes und Pläne die Leistung Ihrer Workloads verbessern können 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. Diese Überprüfung ist ein wichtiges Feature, mit dem dafür gesorgt wird, dass Änderungen, die von der automatischen Optimierung vorgenommen werden, für Ihre Workload insgesamt keine Leistungsverschlechterung bewirken.

Automatische Plankorrektur

Automatische Plankorrektur ist ein automatisches Optimierungsfeature, das die Regression der Ausführungsplanauswahl identifiziert und das Problem automatisch korrigiert, indem der letzte bekannte gute Plan erzwungen wird. Weitere Informationen zu Abfrageausführungsplänen und dem Abfrageoptimierer finden Sie im Handbuch zur Architektur der Abfrageverarbeitung.

Wichtig

Die automatische Plankorrektur hängt von dem Abfragespeicher ab, der in der Datenbank für die Workloadnachverfolgung aktiviert wird.

Was ist die Regression der Ausführungsplanauswahl?

Die SQL Server-Datenbank-Engine kann verschiedene Ausführungspläne verwenden, um die Transact-SQL-Abfragen auszuführen. Abfragepläne hängen von den Statistiken, Indizes und anderen Faktoren ab. Der optimale Plan zur Ausführung einer Transact-SQL-Abfrage könnte sich abhängig von Änderungen dieser Faktoren im Laufe der Zeit ändern. In einigen Fällen ist der neue Plan möglicherweise nicht besser als der vorherige, und der neue Plan könnte zu einer Leistungsregression führen, z. B. einem mit Parameterempfindlichkeit oder Parameterermittlung verwandten Problem.

Query execution plan choice regression.

Wenn Sie feststellen, dass eine Planauswahlregression aufgetreten ist, sollten Sie einen vorherigen guten Plan finden und erzwingen, dass er anstelle der aktuellen Option verwendet wird. Dies kann mithilfe der sp_query_store_force_plan-Prozedur erfolgen. Die Datenbank-Engine in SQL Server 2017 (14.x) enthält Informationen zu verschlechterten Plänen und empfohlenen Korrekturmaßnahmen. Darüber hinaus können Sie diesen Prozess mit der Datenbank-Engine vollständig automatisieren und die Datenbank-Engine alle Probleme beheben lassen, die im Zusammenhang mit der Planänderung entdeckt werden.

Wichtig

Die automatische Plankorrektur sollte im Bereich eines Upgrades auf Datenbankkompatibilitätsebene verwendet werden, nachdem ein Basisplan erfasst wurde, um die Risiken des Workloadupgrades automatisch zu verringern. Weitere Informationen zu diesem Anwendungsfall finden Sie unter Aufrechterhalten einer stabilen Leistung während des Upgrades auf neuere SQL Server-Versionen.

Automatische Korrektur der Planauswahl

Die Datenbank-Engine kann unabhängig davon, wann eine Regression der Planauswahl erkannt wurde, automatisch zum letzten bekannten geeigneten Plan wechseln.

Query execution plan choice correction.

Die Datenbank-Engine erkennt automatisch jede potenzielle Planauswahlregression, einschließlich des Plans, der statt des falschen Plans verwendet werden sollte. Der durch automatische Plankorrektur erzwungene resultierende Ausführungsplan ist identisch mit dem letzten bekannten guten Plan oder ihm ähnlich. 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 beendet die automatische Plankorrektur automatisch den Versuch, den Ersatzplan zu erzwingen.

Wenn die Datenbank-Engine den letzten bekannten geeigneten Plan vor dem Auftreten der Regression anwendet, wird die Leistung des erzwungenen Plans automatisch überwacht. Wenn der erzwungene Plan nicht besser ist als der verschlechterte Plan, wird der neue Plan nicht mehr erzwungen, und die Datenbank-Engine kompiliert einen neuen Plan. Wenn die Datenbank-Engine überprüft, ob der erzwungene Plan besser ist als der verschlechterte Plan, wird der erzwungene Plan beibehalten. Er wird so lange beibehalten, bis eine Neukompilierung erfolgt (z. B. bei der nächsten Statistikaktualisierung oder Schemaänderung). Weitere Informationen zum Erzwingen und Typen von Plänen, die erzwungen werden können, finden Sie unter "Planen der Erzwingung von Einschränkungen".

Hinweis

Wenn die SQL Server-Instanz neu gestartet wird, bevor eine Planerzwingungsaktion überprüft wird, wird dieser Plan automatisch nicht erzwungen. Andernfalls wird die Planerzwingung Neustarts von SQL Server vorbehalten.

Aktivieren der automatischen Planauswahlkorrektur

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

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

Wenn Sie diese Option aktivieren, erzwingt die Datenbank-Engine automatisch jede Empfehlung, bei der der geschätzte CPU-Gewinn größer als 10 Sekunden ist, oder bei der die Anzahl von Fehlern im neuen Plan höher als die Anzahl von Fehlern im empfohlenen Plan ist. Außerdem wird überprüft, ob der erzwungene Plan besser als der aktuelle ist.

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 – manuelle Korrektur der Planauswahl

Ohne die automatische Optimierung müssen Benutzer ihr System regelmäßig überwachen und nach verschlechterten Abfragen suchen. Wenn ein Plan verschlechtert ist, sollte der Benutzer einen vorherigen guten Plan finden und ihn mithilfe der sp_query_store_force_plan-Prozedur anstelle des aktuellen Plans 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 mithilfe des erzwungenen Plans 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. duch Umschreiben. Manuell erzwungene Pläne sollten nicht für immer erzwungen werden, da die Datenbank-Engine in der Lage sein sollten, optimale Pläne anzuwenden. Der Benutzer oder DBA sollte die Erzwingung des Plan schließlich mithilfe der sp_query_store_unforce_plan-Prozedur aufheben und die Datenbank-Engine den optimalen Plan finden lassen.

Tipp

Alternativ können Sie mit der Abfragespeicheransicht Abfragen mit erzwungenen Plänen Pläne finden und deren Erzwingung aufheben.

SQL Server bietet alle erforderlichen Ansichten und Prozeduren zur Überwachung der Leistung und Behebung von Problemen im Abfragespeicher.

In SQL Server 2016 (13.x) finden Sie Planauswahlregressionen mithilfe der Abfragespeicher-Systemansichten. Ab SQL Server 2017 (14.x) erkennt und zeigt die Datenbank-Engine potenzielle Planauswahlregressionen und die empfohlenen Aktionen an, die in der sys.dm_db_tuning_recommendations (Transact-SQL)-DMV angewendet werden sollten. Die DMV zeigt Informationen zum Problem an, die Wichtigkeit des Problems und Details wie die identifizierte Abfrage, die ID des verschlechterten 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.

Typ Beschreibung datetime Ergebnis Ihrer App details ...
FORCE_LAST_GOOD_PLAN CPU-Zeit von 4 ms auf 14 ms geändert 17.03.2017 83 queryId recommendedPlanId regressedPlanId T-SQL
FORCE_LAST_GOOD_PLAN CPU-Zeit von 37 ms auf 84 ms geändert 16.3.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 dazu enthält, warum die Datenbank-Engine diese Planänderung als eine potenzielle Leistungsregression bewertet.
  • Datum und Uhrzeit der Erkennung der potenziellen Regression.
  • Bewertung dieser Empfehlung.
  • Details zu den Problemen wie die ID des erkannten Plans, ID des verschlechterten Plans, ID des Plans, der zur Behebung des Problems erzwungen werden sollte, Transact-SQL-Skript, das zur Behebung des Problems angewendet werden könnte, usw. Die Details werden im JSON-Format gespeichert.

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

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 Resultset.

reason Ergebnis Ihrer App script query_id aktuelle plan_id empfohlene plan_id estimated_gain error_prone
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 eingespart werden würden, wenn der empfohlene Plan anstelle des aktuellen Plans für die Abfrageausführung verwendet würde. Der empfohlene Plan sollte anstelle des aktuellen Plans erzwungen werden, wenn der Gewinn 10 Sekunden überschreitet. Wenn der aktuelle Plan mehr Fehler (z. B. Timeouts oder abgebrochene Ausführungen) enthält als der empfohlene Plan, wird die Spalte error_prone auf den Wert YES festgelegt. Ein fehleranfälliger Plan ist ein weiterer Grund, den empfohlenen Plan anstelle des aktuellen Plans zu erzwingen.

Obwohl die Datenbank-Engine alle zum Identifizieren von Planauswahlregressionen erforderlichen Informationen bereitstellt, können kontinuierliche Überwachung und Behebung von Leistungsproblemen zu einem mühsamen Prozess werden. Die automatische Optimierung erleichtert diesen Prozess erheblich.

Hinweis

Daten in der sys.dm_db_tuning_recommendations-DMV werden nach einem Neustart der Datenbank-Engine 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 Azure SQL-Datenbank ist die Indexverwaltung einfach, weil Azure SQL-Datenbank Informationen zu Ihrer Workload erhält und sicherstellt, dass Ihre Daten immer optimal indiziert sind. Das richtige Indexdesign ist von entscheidender Bedeutung für die optimale Leistung Ihrer Workload, und die automatische Indexverwaltung kann Ihnen bei der Optimierung Ihrer Indizes behilflich sein. Mit der automatischen Indexverwaltung können entweder Leistungsprobleme in falsch indizierten Datenbanken behoben werden, oder es können Indizes im vorhandenen Datenbankschema verwaltet und verbessert werden. Die automatische Optimierung in Azure SQL-Datenbank führt die folgenden Aktionen aus:

  • Identifizieren von Indizes, die die Leistung Ihrer Transact-SQL Abfragen verbessern könnten, die Daten aus den Tabellen lesen.
  • Identifizieren redundanter Indizes oder länger nicht verwendeter Indizes, die entfernt werden könnten. Das Entfernen unnötiger Indizes verbessert die Leistung von Abfragen, die Daten in Tabellen aktualisieren.

Gründe für die Nutzung der Indexverwaltung

Mit Indizes werden einige Ihrer Abfragen beschleunigt, die Daten aus Tabellen lesen. Sie können aber Abfragen verlangsamen, die Daten aktualisieren. Sie müssen sorgfältig analysieren, wann Sie einen Index erstellen und welche Spalten Sie in den Index einbinden müssen. Einige Indizes werden nach einiger Zeit unter Umständen nicht mehr benötigt. Aus diesem Grund müssen Sie die Indizes, die keine Vorteile bringen, regelmäßig identifizieren und löschen. Wenn Sie die nicht genutzten Indizes ignorieren, verringert sich die Leistung der Abfragen, mit denen Daten aktualisiert werden, ohne dass sich Vorteile für die Abfragen zum Lesen von Daten ergeben. Nicht verwendete Indizes wirken sich außerdem auf die Gesamtleistung des Systems aus, da für zusätzliche Updates eine unnötige Protokollierung erforderlich ist.

Zur Ermittlung des optimalen Satzes mit Indizes, mit denen die Leistung der Abfragen zum Lesen von Daten aus Ihren Tabellen verbessert wird und die eine minimale Auswirkung auf Updates hat, ist ggf. eine fortlaufende und komplexe Analyse erforderlich.

Azure SQL-Datenbank verwendet integrierte Intelligence und erweiterte Regeln, mit denen Ihre Abfragen analysiert und die Indizes identifiziert werden, die für Ihre aktuellen Workloads optimal geeignet wären bzw. die ggf. entfernt werden müssten. Mit Azure SQL-Datenbank wird sichergestellt, dass Sie über einen erforderlichen Mindestsatz von Indizes verfügen, mit denen die Abfragen zum Lesen von Daten optimiert werden und sich nur geringe Auswirkungen auf andere Abfragen ergeben.

Automatische Indexverwaltung

Zusätzlich zur Erkennung kann Azure SQL-Datenbank identifizierte Empfehlungen automatisch anwenden. Wenn Sie herausfinden, dass die integrierten Regeln die Leistung Ihrer Datenbank verbessern, können Sie sich für die automatische Verwaltung Ihrer Indizes durch Azure SQL-Datenbank entscheiden.

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 Leistungen der betroffenen Abfragen verbessert werden. Der gelöschte Index wird automatisch neu erstellt, wenn einige Abfragen vorhanden sind, die aufgrund des Fehlens des Indexes langsamer ausgeführt werden.

Aspekte der automatischen Indexverwaltung

Aktionen, die zum Erstellen von erforderlichen Indizes in Azure SQL-Datenbanken durchgeführt werden müssen, verbrauchen unter Umständen Ressourcen und wirken sich vorübergehend negativ auf die Workloadleistung aus. Zur Verringerung der Auswirkung von Indexerstellungen auf die Workloadleistung ermittelt Azure SQL-Datenbank für alle Vorgänge der Indexverwaltung das passende Zeitfenster. Ein Optimierungsvorgang wird verschoben, wenn die Datenbank Ressourcen zum Ausführen Ihrer Workload benötigt, und neu gestartet, wenn die Datenbank über genügend ungenutzte Ressourcen verfügt, die für die Wartungsaufgabe verwendet werden können. Ein wichtiges Feature der automatischen Indexverwaltung ist die Überprüfung der Aktionen. Wenn Azure SQL-Datenbank einen Index erstellt oder löscht, analysiert ein Überwachungsprozess die Leistung Ihrer Workload, um zu überprüfen, ob die Gesamtleistung durch die Aktion verbessert wurde. Falls keine signifikante Verbesserung erkennbar ist, wird die Aktion sofort rückgängig gemacht. Auf diese Weise stellt Azure SQL-Datenbank sicher, dass sich automatische Optimierungsaktionen nicht negativ auf die Leistung Ihrer Workload auswirken. Indizes, die mit der automatischen Optimierung erstellt werden, sind für den Wartungsvorgang des zugrunde liegenden Schemas transparent. Schemaänderungen, wie das Verwerfen oder Umbenennen von Spalten, werden durch das Vorhandensein von automatisch erstellten Indizes nicht blockiert. Von Azure SQL-Datenbank automatisch erstellte Indizes werden sofort gelöscht, wenn dazugehörige Tabellen oder Spalten gelöscht werden.

Alternative – manuelle Indexverwaltung

Ohne die automatische Indexverwaltung muss ein Benutzer oder DBA die sys.dm_db_missing_index_details-Ansicht (Transact-SQL) manuell abfragen oder den Leistungsdashboardbericht in Management Studio verwenden, um Indizes zu finden, die die Leistung verbessern könnten, Indizes mithilfe der in dieser Ansicht angegebenen Details zu erstellen und die Leistung der Abfrage manuell zu überwachen. Um die Indizes zu finden, die gelöscht werden sollten, sollten Benutzer die Betriebsnutzungsstatistiken der Indizes überwachen, um selten verwendete Indizes zu finden.

Azure SQL-Datenbank vereinfacht diesen Prozess. Azure SQL-Datenbank führt eine Analyse Ihrer Workload durch, identifiziert die Abfragen, die mit einem neuen Index ggf. schneller ausgeführt werden können, und identifiziert nicht verwendete oder duplizierte Indizes. Weitere Informationen zur Identifikation von Indizes, die geändert werden sollten, finden Sie unter Azure SQL Database Advisor im Azure-Portal.

Nächste Schritte