Automatische Optimierung

Anwendungsbereich: JaSQL Server 2017 (14.x) und höher JaAzure SQL-Datenbank

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

Bei der automatischen Optimierung, die in eingeführt wurde, werden SQL Server 2017 (14.x) Sie benachrichtigt, sobald ein mögliches Leistungsproblem erkannt wird, und Sie können Korrekturmaßnahmen anwenden, oder die Datenbank-Engine Leistungsprobleme können von automatisch behoben werden. Die automatische Optimierung SQL Server ermöglicht es Ihnen, Leistungsprobleme zu identifizieren und zu beheben, die durch eine Abfrage Ausführungsplan Auswahl Regressionen verursacht werden. Bei der automatischen Optimierung in werden Azure SQL-Datenbank auch erforderliche Indizes erstellt und nicht verwendete Indizes gelöscht. Weitere Informationen zu Abfrage Ausführungsplänen finden Sie unter Ausführungspläne.

SQL Server-Datenbank-EngineÜberwacht die Abfragen, die für die Datenbank ausgeführt werden, und verbessert die Leistung der Arbeitsauslastung automatisch. Datenbank-EngineVerfügt über einen integrierten Intelligence-Mechanismus, mit dem die Leistung Ihrer Abfragen automatisch optimiert und verbessert werden kann, indem die Datenbank dynamisch an ihre Arbeitsauslastung angepasst wird. Es stehen zwei Features für die automatische Optimierung zur Verfügung:

  • Die Automatische Plan Korrektur identifiziert problematische Abfrage Ausführungspläne, wie z. b. eine Parameter Sensitivität oder Parametersniffing -Probleme, und korrigiert Leistungsprobleme im Zusammenhang mit dem Abfrage Ausführungsplan, indem Sie den letzten bekannten guten Plan vor der Regression erzwingt. Gilt für: SQL Server (ab SQL Server 2017 (14.x)) und Azure SQL-Datenbank

  • Die Automatische Index Verwaltung identifiziert Indizes, die in der Datenbank hinzugefügt werden sollen, und Indizes, die entfernt werden sollen. Gilt für: Azure SQL-Datenbank

Gründe für die automatische Optimierung

Zu den drei Hauptaufgaben bei der klassischen Datenbankverwaltung gehören die Überwachung der Arbeitsauslastung, das Identifizieren kritischer Transact-SQL Abfragen und das Identifizieren von Indizes, die hinzugefügt werden sollen, um die Leistung zu verbessern, oder Indizes, die selten verwendet werden und zur Verbesserung der Leistung entfernt werden. SQL Server-Datenbank-EngineBietet detaillierte Einblicke in die Abfragen und Indizes, die Sie überwachen müssen. Eine kontinuierliche Überwachung der Datenbank ist jedoch eine schwierige und aufwendige Aufgabe, insbesondere bei sehr vielen Datenbanken. Die Verwaltung einer großen Anzahl von Datenbanken ist möglicherweise nicht effizient. Anstatt die Datenbank manuell zu überwachen und zu optimieren, empfiehlt es sich ggf., einige der Überwachungs-und Optimierungs Aktionen an die Funktion zur automatischen Optimierung zu delegieren Datenbank-Engine .

Wie funktioniert die automatische Optimierung?

Die automatische Optimierung ist ein kontinuierlicher Überwachungs-und Analyseprozess, der ständig die Merkmale Ihrer Arbeitsauslastung erfährt und potenzielle Probleme und Verbesserungen identifiziert.

Automatischer Optimierungsprozess

Dadurch kann die Datenbank dynamisch an ihre Arbeitsauslastung angepasst werden, indem ermittelt wird, welche Indizes und Pläne die Leistung Ihrer Workloads verbessern können und welche Indizes ihre Workloads beeinflussen. Basierend auf diesen Ergebnissen wendet die automatische Optimierung Optimierungs Aktionen an, die die Leistung Ihrer Arbeitsauslastung verbessern. Außerdem überwacht die automatische Optimierung kontinuierlich die Leistung der Datenbank nach der Implementierung von Änderungen, um sicherzustellen, dass Sie die Leistung Ihrer Arbeitsauslastung verbessert. Jede Aktion, die die Leistung nicht verbessert hat, wird automatisch rückgängig gemacht. Dieser Überprüfungs Vorgang ist ein wichtiges Feature, das sicherstellt, dass alle Änderungen, die durch die automatische Optimierung vorgenommen werden, die Gesamtleistung der Arbeitsauslastung nicht verringern.

Automatische Plankorrektur

Die automatische Plan Korrektur ist eine Funktion zur automatischen Optimierung, die die Regression der Ausführungsplan Auswahl identifiziert und das Problem automatisch durch Erzwingen des letzten bekannten guten Plans korrigiert. Weitere Informationen zu Abfrage Ausführungsplänen und zum Abfrageoptimierer finden Sie im Handbuch zur Architektur der Abfrage Verarbeitung.

Wichtig

Die automatische Plan Korrektur hängt davon ab, Abfragespeicher in der Datenbank für die workloadverfolgung aktiviert wird.

Was ist eine Regression der Ausführungsplan Auswahl?

Der SQL Server-Datenbank-Engine kann verschiedene Ausführungspläne zum Ausführen der Transact-SQL Abfragen verwenden. Abfrage Pläne sind abhängig von den Statistiken, Indizes und anderen Faktoren. Der optimale Plan, der zum Ausführen einer Abfrage verwendet werden sollte, kann sich im Transact-SQL Laufe der Zeit je nach den Änderungen dieser Faktoren ändern. In einigen Fällen ist der neue Plan möglicherweise nicht besser als der vorherige Plan, und der neue Plan kann zu einer Leistungs Regression führen, wie z. b. ein Problem mit der Parameter Sensitivität oder einer Parameter Ermittlung.

Regression der Abfrage Ausführungsplan Auswahl

Wenn Sie feststellen, dass eine Plan Auswahl Regression aufgetreten ist, sollten Sie einen vorherigen guten Plan finden und erzwingen, dass er anstelle der aktuellen verwendet wird. Dies kann mithilfe des sp_query_store_force_plan Verfahrens erfolgen. Die Datenbank-Engine in SQL Server 2017 (14.x) enthält Informationen zu zurück gestellten Plänen und empfohlenen Korrekturmaßnahmen. Außerdem Datenbank-Engine ermöglicht es Ihnen, diesen Prozess vollständig zu automatisieren und das Datenbank-Engine Problem zu beheben, das im Zusammenhang mit der Planänderung zu finden ist.

Wichtig

Die automatische Plan Korrektur sollte im Rahmen eines Upgrades des Datenbank-Kompatibilitäts Niveaus verwendet werden, nachdem eine Baseline aufgezeichnet wurde, um die Risiken bei der Arbeitsauslastung automatisch zu verringern. Weitere Informationen zu diesem Anwendungsfall finden Sie unter aufrechterhalten einer stabilen Leistung während des Upgrades auf neuere SQL Server.

Automatische Korrektur der Planauswahl

Der Datenbank-Engine kann automatisch zum letzten bekannten guten Plan wechseln, wenn eine Plan Auswahl Regression erkannt wird.

Auswahl Korrektur für Abfrage Ausführungsplan

Datenbank-EngineErkennt automatisch jede mögliche Regression der Plan Auswahl, einschließlich des Plans, der anstelle des falschen Plans verwendet werden sollte. Wenn das den Datenbank-Engine letzten bekannten guten Plan anwendet, bevor die Regression aufgetreten ist, wird automatisch die Leistung des erzwungenen Plans überwacht. Wenn der erzwungene Plan nicht besser als der zurück gestellte Plan ist, wird der neue Plan nicht erzwungen, und der Datenbank-Engine kompiliert einen neuen Plan. Wenn überprüft, ob Datenbank-Engine der erzwungene Plan besser als der zurück gestellte Plan ist, wird der erzwungene Plan beibehalten. Sie wird bis zur erneuten Kompilierung beibehalten (z. b. bei der nächsten Statistik Aktualisierung oder Schema Änderung). Weitere Informationen zum Erzwingen von Plänen und Typen von Plänen, die erzwungen werden können, finden Sie unter Plan Erzwingung von Einschränkungen.

Hinweis

Wenn die SQL Server Instanz neu gestartet wird, bevor eine Plan Erzwingungs Aktion überprüft wird, wird der Plan automatisch nicht erzwungen. Andernfalls wird die Plan SQL Server Erzwingung bei Neustarts beibehalten.

Aktivieren der automatischen Korrektur der Planauswahl

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

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

Nachdem Sie diese Option aktiviert haben, Datenbank-Engine wird von automatisch jede Empfehlung erzwungen, bei der der geschätzte CPU-Zuwachs mehr als 10 Sekunden beträgt, oder die Anzahl der Fehler im neuen Plan ist höher als die Anzahl der Fehler im empfohlenen Plan und überprüft, ob der erzwungene Plan besser als der aktuelle ist.

Alternative – manuelle Korrektur der Planauswahl

Ohne automatische Optimierung müssen Benutzer das System in regelmäßigen Abständen überwachen und nach Abfragen suchen, die zurückgeführt haben. Wenn ein Plan rückgängig gemacht wurde, sollte der Benutzer einen vorherigen guten Plan suchen und ihn anstelle des aktuellen Plans mithilfe von sp_query_store_force_plan Procedure erzwingen. Die bewährte Vorgehensweise besteht darin, den letzten als funktionierend bekannten 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, um die Abfrage zu optimieren, z. b. das erneute Schreiben. Manuell erzwungene Pläne sollten nicht immer erzwungen werden, da der Datenbank-Engine in der Lage sein sollte, optimale Pläne anzuwenden. Der Benutzer oder DBA sollte die Erzwingungs Planung des Plans schließlich mithilfe der sp_query_store_unforce_plan Prozedur erzwingen und den Datenbank-Engine optimalen Plan suchen lassen.

Tipp

Verwenden Sie alternativ die Abfragen mit erzwungenen Plänen Abfragespeicher Sicht, um Pläne zu suchen und deren erzwungene aufgängig zu machen.

SQL Server bietet alle notwendigen Sichten und Prozeduren, die zur Überwachung der Leistung und Behebung von Problemen in Abfragespeicher erforderlich sind.

In SQL Server 2016 (13.x) können Sie mit Abfragespeicher System Sichten Plan Auswahl Regressionen suchen. Ab SQL Server 2017 (14.x) Datenbank-Engine erkennt und zeigt mögliche Regressionen der Plan Auswahl und die empfohlenen Aktionen an, die im sys.dm_db_tuning_recommendations (Transact-SQL-) -DMV angewendet werden sollten. Die DMV zeigt Informationen über das Problem, die Wichtigkeit des Problems und Details wie z. b. die identifizierte Abfrage, die ID des zurück gestellten Plans, die ID des Plans, der als Baseline für den Vergleich verwendet wurde, und die Anweisung, die Transact-SQL ausgeführt werden kann, um das Problem zu beheben.

type description datetime Ergebnis Ihrer App Details ...
FORCE_LAST_GOOD_PLAN Die CPU-Zeit wurde von 4 ms auf 14 ms geändert. 3/17/2017 83 queryId recommendedPlanId regressedPlanId T-SQL
FORCE_LAST_GOOD_PLAN Die CPU-Zeit wurde von 37 MS auf 84 MS geändert. 16.3.2017 26 queryId recommendedPlanId regressedPlanId T-SQL

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

  • Der Typ der empfohlenen Aktion FORCE_LAST_GOOD_PLAN .
  • Beschreibung, die Informationen enthält, weshalb der der Datenbank-Engine Meinung ist, dass diese Planänderung eine potenzielle Leistungs Regression ist.
  • DateTime, wenn die potenzielle Regression erkannt wird.
  • Bewertung dieser Empfehlung.
  • Details zu den Problemen, wie z. b. die ID des erkannten Plans, die ID des zurück gestellten Plans, die ID des Plans, der zur Behebung des Problems gezwungen werden soll, das Skript, das ggf Transact-SQL . zur Behebung des Problems verwendet werden kann, usw. Details werden im JSON-Formatgespeichert.

Verwenden Sie die folgende Abfrage zum Abrufen eines Skripts, 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 Resultset.

reason Ergebnis Ihrer App script Abfrage- _ ID ID des aktuellen Plans _ Empfohlene Plan- _ ID Geschätzter _ Gewinn Fehler _ anfällig
Die CPU-Zeit wurde von 3 MS in 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, wenn der empfohlene Plan für die Abfrage Ausführung anstelle des aktuellen Plans verwendet würde. Der empfohlene Plan sollte anstelle des aktuellen Plans erzwungen werden, wenn der Gewinn größer als 10 Sekunden ist. Wenn im aktuellen Plan mehr Fehler (z. b. Timeouts oder abgebrochene Ausführungen) als im empfohlenen Plan vorhanden sind, wird die Spalte error_prone auf den Wert festgelegt YES . Ein fehleranfälliger Plan ist ein weiterer Grund, warum der empfohlene Plan anstelle des aktuellen Plans erzwungen werden sollte.

Obwohl die Datenbank-Engine alle Informationen bereitstellt, die zum Identifizieren von Plan Auswahl Regressionen erforderlich sind, kann die kontinuierliche Überwachung und das Beheben von Leistungsproblemen zu einem mühsamen Prozess werden. Durch die automatische Optimierung wird dieser Prozess erheblich vereinfacht.

Hinweis

Die Daten in der sys.dm_db_tuning_recommendations DMV werden nach einem Neustart der Datenbank-Engine nicht beibehalten. Verwenden sqlserver_start_time Sie die Spalte in sys.dm_os_sys_info , um die Letzte Startzeit der Datenbank-Engine zu ermitteln.

Automatische Indexverwaltung

In Azure SQL-Datenbank ist die Index Verwaltung einfach, da Azure SQL-Datenbank ihre Arbeitsauslastung erfährt und sicherstellt, dass Ihre Daten immer optimal indiziert werden. 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:

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

Gründe für die Nutzung der Indexverwaltung

Indizes beschleunigen einige Ihrer Abfragen, die Daten aus den Tabellen lesen, aber Sie können die Abfragen, mit denen Daten aktualisiert werden, verlangsamen. 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. Daher müssen Sie diese Indizes in regelmäßigen Abständen identifizieren und löschen, die keine Vorteile bieten. Wenn Sie die nicht verwendeten Indizes ignorieren, würde die Leistung der Abfragen, mit denen Daten aktualisiert werden, ohne jeglichen Vorteil der Abfragen, die Daten lesen, verringert werden. 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 und erweiterte Regeln, mit denen Ihre Abfragen analysiert, Indizes identifiziert werden, die für Ihre aktuellen Workloads optimal geeignet sind, und die Indizes identifizieren, die möglicherweise entfernt werden müssen. Azure SQL-Datenbank stellt sicher, dass Sie über einen minimalen erforderlichen Satz von Indizes verfügen, die die Abfragen optimieren, die Daten lesen, mit minimierter Auswirkung auf die anderen Abfragen.

Automatische Indexverwaltung

Zusätzlich zur Erkennung Azure SQL-Datenbank können identifizierte Empfehlungen von automatisch angewendet werden. Wenn Sie feststellen, dass die integrierten Regeln die Leistung Ihrer Datenbank verbessern, können Sie die Azure SQL-Datenbank Indizes automatisch verwalten.

Informationen zum Aktivieren der automatischen Optimierung in Azure SQL-Datenbank und zur vollständigen Verwaltung der Arbeitsauslastung durch die Funktion zur automatischen Optimierung finden Sie unter Aktivieren der automatischen Optimierung in Azure SQL-Datenbank mithilfe von Azure-Portal.

Wenn Azure SQL-Datenbank eine CREATE Index-oder Drop Index-Empfehlung anwendet, wird automatisch die Leistung der Abfragen überwacht, die vom Index betroffen sind. Der neue Index wird nur beibehalten, wenn die Leistung der betroffenen Abfragen verbessert wird. Der gelöschte Index wird automatisch neu erstellt, wenn einige Abfragen aufgrund der Abwesenheit des Indexes langsamer ausgeführt werden.

Aspekte der automatischen Indexverwaltung

Aktionen, die erforderlich sind, um erforderliche Indizes in zu erstellen, Azure SQL-Datenbank können Ressourcen beanspruchen und die workloadleistung temporell Um die Auswirkungen der Indexerstellung auf die workloadleistung zu minimieren, Azure SQL-Datenbank sucht ein geeignetes Zeitfenster für jeden Index Verwaltungsvorgang. Die Optimierungs Aktion wird verschoben, wenn die Datenbank Ressourcen zum Ausführen der Arbeitsauslastung benötigt und neu gestartet wird, wenn die Datenbank über ausreichend nicht verwendete Ressourcen verfügt, die für den Wartungs Task 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 der Arbeitsauslastung, um zu überprüfen, ob die Gesamtleistung durch die Aktion verbessert wurde. Wenn dies nicht zu einer erheblichen Verbesserung geführt hat, wird die Aktion sofort wieder hergestellt. Auf diese Weise wird Azure SQL-Datenbank sichergestellt, dass sich automatische Optimierungs Aktionen nicht negativ auf die Leistung der Arbeitsauslastung 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. Indizes, die automatisch von erstellt werden, Azure SQL-Datenbank werden sofort gelöscht, wenn die verknüpften Tabellen gelöscht werden.

Alternative: manuelle Index Verwaltung

Ohne die automatische Index Verwaltung muss ein Benutzer oder DBA die sys.dm_db_missing_index_details (Transact-SQL-) anzeigen oder den Leistungs Dashboard-Bericht in verwenden, Management Studio um Indizes zu suchen, die möglicherweise die Leistung verbessern, Indizes mithilfe der in dieser Ansicht bereitgestellten Details erstellen und die Leistung der Abfrage manuell überwachen. Um die Indizes zu ermitteln, die gelöscht werden sollen, sollten die Benutzer die Betriebs Verwendungs Statistik der Indizes überwachen, um selten verwendete Indizes zu finden.

Azure SQL-Datenbank vereinfacht diesen Prozess. 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 Identifikation von Indizes, die geändert werden sollten, finden Sie unter Azure SQL Database Advisor im Azure-Portal.

Weitere Informationen

ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL-)
sys.database_automatic_tuning_options (Transact-SQL-)
sys.dm_db_tuning_recommendations (Transact-SQL-)
sys.dm_db_missing_index_details (Transact-SQL-)
sp_query_store_force_plan (Transact-SQL-)
sp_query_store_unforce_plan (Transact-SQL-)
sys.database_query_store_options (Transact-SQL-)
sys.dm_os_sys_info (Transact-SQL-)
JSON-Funktionen
Ausführungspläne
Überwachen und Optimieren der Leistung
Tools für die Leistungsüberwachung und -optimierung
Überwachen der Leistung mit dem Abfragespeicher
Abfrageoptimierungs-Assistent