Erste Schritte mit dem Abfrageoptimierungs-Assistenten (QAT)
Sie möchten zwei Produktionsdatenbanken von SQL Server 2012 zu einer neuen SQL Server 2022-Instanz migrieren. Da die Datenbanken verwendet werden, möchten Sie zunächst die ursprünglichen Einstellungen der Kompatibilitätsgrade beibehalten und die Anwendungen auf die neue Datenbank verweisen. Die Kardinalitätsschätzung hat sich seit SQL Server 2014 geändert, und Sie möchten die Abfrageleistung messen, bevor Sie den Kompatibilitätsgrad der Datenbanken in SQL Server 2022 ändern (160).
Sie müssen alle Abfragen suchen und korrigieren, die zurückgestellt werden, wenn der Kompatibilitätsgrad verschoben und der neue Algorithmus für die Kardinalitätsschätzung angewendet wird. Bei dieser Vorgehensweise wird eine Baseline für die Leistung gemessen, die nach dem Upgrade des Kompatibilitätsgrads für den Vergleich herangezogen werden kann.
Wenn Sie den Abfragespeicher aktivieren, werden die Metriken erfasst, die Sie zum Suchen nach zurückgestellten Abfragen benötigen. Bei der Verwendung des Abfrageoptimierungs-Assistenten (Query Tuning Assistant, QTA) werden Sie durch die Korrektur der zurückgestellten Abfragen geführt. Diese Lerneinheit bietet eine Übersicht über den Abfragespeicher und den QTA.
Übersicht zum Abfragespeicher
Das Feature Abfragespeicher wurde in SQL Server 2016 eingeführt, um kontinuierlich Informationen zur Ausführung und Leistung von Abfragen innerhalb einer Datenbank zu erfassen. Der Abfragespeicher funktioniert wie ein Flugdatenschreiber, um Runtimeinformationen zu Abfragen und Plänen zu sammeln. Wenn Sie diese Runtimedaten speichern, können Sie die Leistung im Laufe der Zeit nachverfolgen. Falls ein Problem auftritt, steht Ihnen ein Verlauf von Informationen zur Verfügung, um dessen Ursache zu ermitteln.
Ab SQL Server 2022 und in Azure SQL-Datenbank und SQL Managed Instance ist der Abfragespeicher standardmäßig für neue Datenbanken aktiviert. In SQL Server 2016, SQL Server 2017 und SQL Server 2019 ist der Abfragespeicher nicht standardmäßig aktiviert, aber jede Datenbank in einer Instanz von SQL Server 2016 oder höher kann den Abfragespeicher aktivieren, deaktivieren und konfigurieren. Anweisungen zum Aktivieren des Abfragespeichers oder zum Überprüfen seiner Konfiguration in einer Datenbank finden Sie in der nächsten Lerneinheit.
Der Abfragespeicher kann Datenbanken mit früheren Kompatibilitätsgraden als die SQL Server-Instanz nutzen. Wenn Sie beispielsweise eine Datenbank von SQL Server 2012 zu SQL Server 2022 migrieren und den Kompatibilitätsgrad „110“ beibehalten, kann der Abfragespeicher weiterhin für die Datenbank verwendet werden.
Viele Features der intelligenten Abfrageverarbeitung und andere automatische Leistungsverbesserungen sind jedoch nur für neuere Datenbank-Kompatibilitätsgrade aktiviert. Daher sollten Sie versuchen, die Anwendungsleistung mit dem neuesten Datenbank-Kompatibilitätsgrad von SQL Server zu testen. Die Abfragespeicher und der QTA können Sie bei diesen Leistungstests unterstützen.
Wenn der Abfragespeicher für eine Datenbank aktiviert ist, erfasst er die folgenden Statistiken für Ihre Abfragen und meldet diese:
- Zurückgestellte Abfragen
- Ressourcenverbrauch gesamt
- Abfragen mit dem höchsten Ressourcenverbrauch
- Abfragen mit erzwungenen Plänen
- Abfragen mit hoher Variation
- Statistik der Abfragewartezeit
- Nachverfolgte Abfragen
Zu einer zurückgestellten Abfrage kommt es, wenn der Abfrageoptimierer einen neuen Abfrageplan verwendet, der die Leistung beeinträchtigt. Regression kann nach wichtigen Änderungen erfolgen, z. B. nach dem Hinzufügen, Löschen oder Ändern eines Indexes, dem Aktualisieren von Statistiken oder dem Ändern der Datenkardinalität.
Vor dem Abfragespeicher bot SQL Server keinen Einblick in die Ursache von Regressionen, und das Identifizieren von Problemen war für Datenbankentwickler*innen und Administrator*innen schwierig. Jetzt können Sie mithilfe des Abfragespeichers zurückgestellte Abfragen suchen und erzwingen, dass der Optimierer einen bestimmten Plan aus dem Verlauf verwendet.
Es ist üblich, dass einige Abfragen aus möglicherweise Tausenden die meisten Systemressourcen verbrauchen. Der Abfragespeicher identifiziert Abfragen mit dem höchsten Verbrauch entweder aufgrund einer Regression oder einer schlechten Optimierung. Abhängig von der Konfiguration können Sie die Ergebnisse nach Dauer, CPU, Arbeitsspeicher, E/A oder Anzahl von Ausführungen filtern.
Sie können den Abfragespeicher verwenden, um die aktuelle Leistung und A/B-Tests zu überwachen und so die Leistung vor und nach der Anwendung einer einzelnen Änderung zu vergleichen. Sie können beispielsweise die Leistung einer Abfrage optimieren, indem Sie einer Tabelle, auf die die Abfrage verweist, einen Index hinzufügen, um die Verknüpfungssuche zu beschleunigen. Wenn Sie die Statistiken im Abfragespeicher vor und nach dem Hinzufügen des Indexes vergleichen, erfahren Sie, ob sich der Index auf die Leistung auswirkt. Sie können auch Statistiken vergleichen, nachdem Sie neue Hardware hinzugefügt oder eine Anwendung aktualisiert haben.
Übersicht über den Abfrageoptimierungs-Assistenten
Der Abfrageoptimierungs-Assistent (QTA) verwendet die Daten aus dem Abfragespeicher für die Suche nach Abfragen, deren Zurückstellung beginnt. Der QTA experimentiert automatisch, um nach einer Lösung zu suchen, die die Abfrage beschleunigt, bevor ein Leistungsdefizit eintritt, das Benutzer*innen beeinträchtigt.
Sie können die Datenbankleistung nach dem Upgrade mit dem Abfragespeicher und dem QTA überwachen und optimieren. Lassen Sie nach dem Migrieren einer Datenbank zu SQL Server 2016 oder höher den Kompatibilitätsgrad der Datenbank unverändert, und aktivieren Sie den Abfragespeicher um Baselinestatistiken zur Abfrageleistung zu sammeln.
Anschließend ändern Sie den Kompatibilitätsgrad und verwenden weiterhin Abfragespeicherdaten, um die Leistungsstatistiken Ihrer Abfragen zu messen. Sie können die Statistiken vergleichen, um herauszufinden, ob die einzelnen Abfragen besser, gleich oder schlechter als vor dem Upgrade abschneiden.
Wenn Sie den Kompatibilitätsgrad zum Aktualisieren der Datenbank ändern, ändert SQL Server die verwendete Version der Kardinalitätsschätzung. Der QTA findet aufgrund der Änderung in der Kardinalitätsschätzung mögliche Muster der Abfrageregression und experimentiert, um Leistungsverbesserungen zu ermitteln. Sie können dann Planhinweislisten für Abfragen erstellen, bei denen es eine Verbesserung gab.
Zusammenfassung
Der Abfragespeicher misst kontinuierlich die Leistungsstatistik Ihrer Abfragen, so wie ein Flugdatenschreiber in einem Flugzeug die Aktivitäten erfasst. Sie können den Abfragespeicher für jede beliebige Datenbank in SQL Server 2016 oder höher aktivieren – unabhängig vom Kompatibilitätsgrad. Verwenden Sie den Abfragespeicher zum kontinuierlichen Überwachen der Abfrageleistung und für A/B-Tests, bei denen Sie die Auswirkungen einer einzelnen Änderung messen.
Wenn Sie eine Datenbank auf SQL Server 2014 oder höher aktualisieren, verlangsamen Änderungen an der Kardinalitätsschätzung möglicherweise Abfragen, die in der vorherigen SQL Server-Version schnell waren. Im Idealfall möchten Sie Regressionen finden und beheben, bevor sie sich auf Benutzer*innen auswirken. Wenn Sie den Abfragespeicher für Ihre Datenbanken aktivieren, bedeutet dies, dass Statistiken zu den Abfragen fortlaufend erfasst werden. Anschließend können Sie den QTA verwenden, um zurückgestellte Abfragen zu identifizieren und zu beheben, bevor sie zu einem Problem werden.