Automatische Optimierung

Die automatische Optimierung ist ein Überwachungs- und Analysefeature, das kontinuierlich über Ihre Workload überwacht und analysiert, um potenzielle Probleme und Verbesserungen zu ermitteln.

Die Empfehlungen zur automatischen Optimierung basieren auf den Daten, die von Abfragespeicher erfasst werden. Ausführungspläne entwickeln sich im Laufe der Zeit aufgrund von Schema-, Index- oder Datenänderungen weiter, die zu Aktualisierungen der Statistiken führen. Diese Weiterentwicklung kann zu einer schlechten Abfrageleistung führen, da der Ausführungsplan nicht mehr den Anforderungen der angegebenen Abfrage entspricht.

Darüber hinaus können mit der automatischen Optimierung Daten erfasst und Machine Learning Services auf Leistungsmetriken angewendet werden, um Verbesserungen vorschlagen zu lassen oder sogar eine Selbstkorrektur zu ermöglichen.

Die automatische Optimierung, egal ob lokal oder in der Cloud, ermöglicht es Ihnen, Probleme zu ermitteln, die durch eine gesunkene Leistung des Abfrageausführungsplans verursacht werden. Außerdem können Sie die Abfrageleistung in Azure SQL-Datenbank durch Indexoptimierung weiter verbessern. Mit der automatischen Optimierung von Azure SQL-Datenbank können Indizes identifiziert werden, die zur Datenbank hinzugefügt oder daraus entfernt werden sollen, um die Abfrageleistung zu verbessern.

Automatische Plankorrektur

Mithilfe der Abfragespeicher-Daten kann das Datenbankmodul bestimmen, wann es zu einem Rückgang der Leistung von Abfrageausführungsplänen gekommen ist. Obwohl Sie einen regredierenden Plan manuell über die Benutzeroberfläche ermitteln können, können Sie sich auch automatisch vom Abfragespeicher benachrichtigen lassen.

Im obigen Beispiel wird ein Häkchen für Plan ID 1 angezeigt. Dies bedeutet, dass der Plan erzwungen wurde. Nachdem das Feature aktiviert wurde, erzwingt das Datenbankmodul in den folgenden Fällen automatisch einen empfohlenen Abfrageausführungsplan:

• Der vorherige Plan hatte eine höhere Fehlerrate als der empfohlene Plan.
• Der geschätzte CPU-Gewinn war größer als 10 Sekunden.
• Die Leistung des erzwungenen Plans war besser als die des vorherigen Plans.

Der Plan wird nach 15 Abfrageausführungen auf den letzten bekannten funktionierenden Plan zurückgesetzt.

Wenn das Erzwingen des Plans automatisch erfolgt, wendet das Datenbankmodul den letzten bekannten guten Plan an und verfolgt die Leistung der Abfrageausführung. Wenn der erzwungene Plan keine bessere Leistung als der vorherige zeigt, wird die Erzwingung aufgehoben und die Kompilierung eines neuen Plans durchgesetzt. Wenn der erzwungene Plan jedoch weiterhin den vorherigen fehlerhaften Plan überschreitet, bleibt er bis zum Erneutkompilieren vorhanden.

Sie können die automatische Plankorrektur über eine T-SQL-Abfrage aktivieren. Der Abfragespeicher muss aktiviert sein und sich im Lese-/Schreibmodus befinden, damit der Befehl erfolgreich ausgeführt werden kann. Wenn eines dieser beiden Kriterien nicht erfüllt ist, schlägt die ALTER-Anweisung fehl.

ALTER DATABASE [WideWorldImporters] SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

Sie können die Empfehlungen zur automatischen Optimierung mithilfe der dynamischen Verwaltungssicht (Dynamic Management View, DMV) sys.dm_db_tuning_recommendations untersuchen, die in SQL Server 2017 oder höher sowie in Azure SQL-Datenbank-Lösungen verfügbar ist. Diese DMV enthält Informationen wie z. B. die Gründe, den Typ und den Status der Empfehlung. Um zu bestätigen, dass die automatische Optimierung für eine Datenbank aktiviert ist, überprüfen Sie die Ansicht sys.database_automatic_tuning_options.

Automatische Indexverwaltung

Azure SQL-Datenbank verfügt über die Möglichkeit, die automatische Indexoptimierung durchzuführen. Im Laufe der Zeit lernt es von vorhandenen Workloads und bietet Empfehlungen zum Hinzufügen oder Entfernen von Indizes zur Verbesserung der Leistung. Ähnlich wie das Erzwingen verbesserter Abfragepläne kann die Datenbank so konfiguriert werden, dass Indizes basierend auf ihrer Leistung automatisch erstellt oder entfernt werden, wie in der folgenden Abbildung dargestellt.

Wenn diese Option aktiviert ist, identifiziert die Seite "Leistungsempfehlungen " Indizes, die je nach Abfrageleistung erstellt oder gelöscht werden können. Beachten Sie, dass dieses Feature nicht für lokale Datenbanken, sondern nur für Azure SQL-Datenbank verfügbar ist.

Verwenden Sie alternativ die folgende Abfrage, um die in Ihrer Datenbank aktivierten Funktionen für die automatische Optimierung anzuzeigen:

SELECT name,
    desired_state_desc,
    actual_state_desc,
    reason_desc
FROM sys.database_automatic_tuning_options

Die Erstellung neuer Indizes kann Ressourcen beanspruchen, und das Timing der Indexerstellung ist fundamental, um sicherzustellen, dass keine negativen Auswirkungen für Ihre Workloads bestehen.

Die Azure SQL-Datenbank überwacht die Ressourcen, die zum Implementieren neuer Indizes erforderlich sind, um Leistungseinbußen zu vermeiden. Die Optimierungsaktion wird verschoben, bis die verfügbaren Ressourcen verfügbar sind, wenn z. B. Ressourcen für vorhandene Workloads benötigt werden und für das Erstellen eines Indexes nicht verfügbar sind.

Durch die Überwachung wird sichergestellt, dass durchgeführte Aktionen nicht die Leistung beeinträchtigen. Wenn ein Index verworfen wird und die Abfrageleistung spürbar beeinträchtigt wird, wird der zuletzt abgelegte Index automatisch neu erstellt.