Entscheiden, ob der Abfrageoptimierungs-Assistent (Query Tuning Assistant, QTA) für Sie geeignet ist
Sie sind sich der möglichen Abfrageplanregression und des möglichen Leistungsverlusts nach den Upgrades von Datenbankversionen bewusst. Um die Leistung nach Datenbankupgrades aufrechtzuerhalten, müssen Sie die beste Methode zum Identifizieren und Behandeln von zurückgestellten Abfragen finden. In dieser Lerneinheit wird beschrieben, wie Sie mithilfe des Abfragespeichers und des Abfrageoptimierungs-Assistenten (Query Tuning Assistant, QTA) sicherstellen können, dass nach den Upgrades keine Leistungsprobleme auftreten.
Erste Schritte mit dem Abfragespeicher und dem Abfrageoptimierungs-Assistenten
Der QTA ist auf Abfragespeicherdaten angewiesen, um Abfragen zu ermitteln, die nach einem Upgrade zurückgestellt werden. Mit dem Abfragespeicher können Sie Metriken für die ältere Datenbankversion sammeln, bevor Sie ein Upgrade durchführen.
Der Abfragespeicher wurde in SQL Server 2016 und der QTA in SQL Server 2017 eingeführt. Jede Datenbankversion, die in einer Instanz von SQL Server 2022 ausgeführt wird, kann beide Features verwenden. Diese Tools sind in SQL Server Management Studio (SSMS) integriert und arbeiten auf Datenbankebene.
Der Kompatibilitätsgrad der Datenbank bestimmt die Version, und die Version bestimmt die verwendete Version der Kardinalitätsschätzung. Die Kardinalitätsschätzung prognostiziert, wie viele Zeilen eine Abfrage wahrscheinlich zurückgeben wird, sodass der Abfrageoptimierer den Plan mit den niedrigsten Kosten auswählen kann. In SQL Server 2014 wurde ein aktualisierter Algorithmus für die Kardinalitätsschätzung eingeführt, die für die meisten Abfragen von Vorteil ist, aber selten negative Auswirkungen auf die Leistung haben kann.
Zum Messen der Auswirkungen auf die Leistung meldet Abfragespeicher zurückgestellte Abfragen und Abfragen, die die meisten Systemressourcen verbrauchen. Der QTA vergleicht die Abfrageleistungsdaten des Abfragespeichers vor und nach dem Datenbankupgrade und experimentiert mit den Abfragen, um die Leistung zu verbessern.
Hinweis
Der QTA ist für Azure SQL-Datenbank oder SQL Managed Instance-Datenbanken nicht verfügbar. Verwenden Sie für diese Datenbanken ggf. die Azure SQL-Migrationserweiterung für Azure Data Studio.
QTA und automatische Plankorrektur
Beim Ausführen einer Transact-SQL-Abfrage (T-SQL) analysiert SQL Server mögliche Pläne, die die Abfrage ausführen können. SQL Server speichert Pläne für Abfragen zwischen, die erfolgreich ausgeführt werden, und verwendet sie wieder, wenn die Abfragen erneut ausgeführt werden.
SQL Server wählt den optimalen Plan für eine Abfrage aus und verwendet ihn so lange, bis ein neuer Umstand dazu zwingt, einen neuen auszuwählen. Zu diesen Umständen kann gehören, dass die Datenbank-Engine den Plan neu kompiliert, dass ein Index hinzugefügt oder entfernt wird oder dass Statistiken geändert werden.
Es trifft nicht immer zu, dass der neue Plan eine Verbesserung des alten Plans ist. Wenn Sie nach Abfragen mit einem zurückgestellten Plan suchen möchten, können Sie den folgenden Befehl ausführen.
SELECT * FROM sys.dm_db_tuning_recommendations
Anschließend können Sie die gespeicherte Prozedur sp_force_plan verwenden, um SQL Server zu erzwingen, einen empfohlenen spezifischen Plan zu verwenden.
EXEC sp_force_plan @query_id = 1187, @plan_id = 1975
Die Prozedur sp_force_plan ist ein manueller Prozess, der möglicherweise mühsam ist, wenn viele Abfragen für eine aktualisierte Datenbank zurückgestellt werden. In SQL Server 2017 wurde ein neues Feature namens automatische Plankorrektur eingeführt, um Abfragen automatisch zu optimieren und den manuellen Eingriff überflüssig zu machen. Sie können die automatische Plankorrektur für eine Datenbank aktivieren, indem Sie die folgende Anweisung ausführen:
ALTER DATABASE <database-name> SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)
Wenn die automatische Plankorrektur auf Datenbankebene festgelegt wird, weist sie SQL Server an, den letzten guten Abfrageplan zu verwenden. SQL Server überwacht weiterhin den Plan, um Regressionen zu ermitteln, während der Plan ausgeführt wird, und um sicherzustellen, dass er eine optimale Leistung liefert.
Die automatische Plankorrektur funktioniert anders als der QTA. Die Verwendung des letzten guten Plans kann einen Rollback auf eine frühere Kardinalitätsschätzung bedeuten. Der QTA hingegen führt seine Experimente mit der Version der Kardinalitätsschätzung aus, die dem Zielkompatibilitätsgrad der Datenbank zugeordnet ist.
Zusammenfassung
Der QTA steht in SQL Server 2022 zur Verfügung und ist abhängig vom Abfragespeicher. Der QTA benötigt Abfragespeicher-Baselinedaten für eine Datenbank auf seinem vorherigen Kompatibilitätsgrad, damit er Abfragen beobachten und nach einem Upgrade Vergleiche durchführen kann.
Durch die in SQL Server 2017 eingeführte automatische Plankorrektur muss ein Abfrageplan nicht mehr manuell ermittelt und erzwungen werden. Die automatische Plankorrektur kann auf Datenbankebene aktiviert werden, führt aber möglicherweise ein Rollback für die Version der Kardinalitätsschätzung aus. Der QTA verwendet immer die Kardinalitätsschätzung, die dem Zielkompatibilitätsgrad zugeordnet ist.