Überblick
Der SQL-Optimizer, die Komponente zur Ermittlung eines effizienten und performanten Ausführungsplans, kann sich bei komplexen Abfragen unter Umständen hartnäckig weigern, eine Optimierung zu erkennen. Von einem solchen Fall und, was man dagegen tun kann, handelt dieser Beitrag. Die Lösung ist die Verwendung eines Plan Guides. Der Plan Guide ist ein eine Feature, das bereits seit SQL Server 2005 verfügbar ist, aber bei den Applikationsentwicklern bzw. DBAs möglicherweise in Vergessenheit geraten ist.
Das Problem
Ausgangspunkt für das bei einem Kunden aufgetretene Problem waren Datenschutzvorgaben, die erforderten, dass Applikationsbenutzer nur den Teil der verfügbaren Daten sehen dürfen, für die sie verantwortlich sind. Diese Vorgabe wurde durch benutzerspezifische Views umgesetzt. Jeder Benutzer erhielt also nur über seine View Zugriff auf seine Daten. Die Struktur dieser Views war sehr komplex, so dass der Optimizer trotz eines hohen Datenbestands und aktueller Statistiken nicht erkannte, dass Parallelisierung zu einer besseren Performance führt. Nach Einführung der Views wurde festgestellt, dass sich die Performance der Datenbank drastisch verschlechtert hatte. Die Kompatibilitätsstufe der Datenbank war auf SQL Server 2022 (160) eingestellt.
Ein erster Hinweis
Eine zufällige Entdeckung des Kunden ergab, dass ein Zugriff über die View mit einer zusätzlichen where-Bedingung, die aber auch bereits Bestandteil der View war, erheblich schneller lief als der ohne diese Bedingung.
Ein Vergleich des Ausführungsplans dieser beiden Abfragen (View vs. View mit zusätzlichem where) ergab, dass bei der zweiten Form im Gegensatz zur ersten parallelisiert wurde. Hier lag also offensichtlich ein Optimizer-Bug vor.
Die Anzahl der CPUs (12) spiegelte sich in den jeweiligen Kosten der Ausführungspläne wider
- Parallelverarbeitung: etwa 350
- Sequentielle Verarbeitung: etwa 4000
Voraussetzung für Parallelisierung
Die Kosten des vom Optimizer ermittelten seriellen Ausführungsplans lagen weit über dem Default-Wert (5), ab dem im Normalfall die Parallelisierung einsetzt. Der Instanzparameter Max Degree for Parallelism, so hatte es sich im Vorfeld in einem von uns durchgeführten Assessment ergeben, war zwar nicht optimal eingestellt - unterdrückte jedoch die Parallelisierung nicht, ebenso wie der Datenbankparameter Max DOP. Es hatte sich ja auch schon in der View mit der where-Bedingung gezeigt, dass Parallelverarbeitung nicht grundsätzlich durch entsprechende Parametrisierung ausgeschlossen ist.
Und was ist mit dem Query Store?
Sofern es zu einer Abfrage auf Grund der historischen Datenlage unterschiedliche Pläne zu der gleichen Abfrage gibt, kann man den günstigsten dieser Abfragepläne erzwingen. Im vorliegenden Fall gab es aber nur den seriellen Plan, sodass diese Möglichkeit zunächst entfiel.
Da helfen doch Hints!
Nun, mit Hints lassen sich tatsächlich die Abfragepläne beeinflussen. Dies ist jedoch eine nicht empfehlenswerte Aktion, denn Hints bedeuten eine syntaktische Änderung der Statements, im Fall von Views zwar erheblich einfacher, als wenn auf Applikationsebene geändert werden müsste. Diese Möglichkeit der Einflussname auf den Ausführungsplan hat jedoch den Nachteil, dass sie sehr starr ist und sich bei evtl. geänderter Datenkonstellation (geänderte Datenverteilung) nicht ändert, außer wenn man die Abfrage / View erneut ändert. Man müsste also den Ausführungsplan regelmäßig prüfen und ggf. die Query anpassen.
Plan Guide - die Lösung
Die folgende Abbildung zeigt den Ausführungsplan für die Verwendung der View. Hier geht es nicht um Details, sondern lediglich darum, die Komplexität des Plans und die serielle Verarbeitung darzustellen:
Bevor wir diesen Ausführungsplan mit Hilfe des Plan Guide so ändern, dass Parallelverarbeitung stattfindet, führen wir die Abfrage der View aus, so dass sie im Abfrage-Cache enthalten ist.
Nun wird im Management-Studio zu dem Knoten -> “Programmability” -> “Plan Guides” navigiert und mit dem Kontextmenü (rechte Maustaste) ein neuer Plan Guide (“New Plan Guide …”)angelegt.
Der für den neuen Plan Guide automatisch mit einem Zeitstempel vergebene Name kann individuell vergeben werden, hier Beispiel_PlanGuide. Im Eingabefeld Statement wird das Original-Statement verwendet. Im Feld Scope type wird für dieses Beispiel Sql gewählt, der Scope wird unverändert beibehalten und im Feld Hint wird der Hint für Parallelverarbeitung “OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))“ verwendet. Details zu den verschiedenen Eingabefeldern finden Sie unter Erstellen einer neuen Planhinweisliste.
Also doch Hints?
Keine Angst, es geht ja noch weiter!
Zunächst einmal: Was haben wir erreicht?
Nach Anlegen des Plan Guide führen wir die o.a. Abfrage erneut aus und erhalten einen Ausführungsplan, der dem ursprünglichen ähnelt, aber in dem Parallelverarbeitung, symbolisiert durch zwei schwarze Pfeile auf gelbem Hintergrund (siehe einen der Knoten des parallelen Ausführungsplans in der folgenden Abbildung), verwendet wird:
Nun zum Query-Store
Sie kennen doch hoffentlich den Query Store und haben ihn aktiviert?
Im Query Store der Datenbank öffnen wir jetzt den Unterknoten Top Resource Consuming Queries, suchen und selektieren in der linken oberen Grafik den Balken für unser select-Statement und werden auf der rechten Seite zwei Plan Id's sehen: Eine für die ursprüngliche Abfrage und die zweite für die Abfrage, die wir über den Plan Guide erzwungen haben und die die Parallelverarbeitung verwendet.
Wir selektieren die Plan Id für die Parallelverarbeitung und verwenden die Force Plan Schaltfläche, um diesen Plan zu aktivieren. Der farbige Punkt der entsprechenden Plan Id in der Grafik wird mit einem Häkchen versehen. Der Beispiel-Plan Guide kann jetzt bei Bedarf deaktiviert werden, der Ausführungsplan bleibt stabil, bis in der Datenbankänderungen (Löschen von für die Abfrage verwendeten Indizes) durchgeführt werden, die von dem Ausführungsplan verwendet werden.
Fazit
Über Plan Guides und die Verwendung des Query Store mit der Möglichkeit, einen Ausführungsplan zu erzwingen, können Abfragen optimiert werden, ohne Änderungen am Code vornehmen zu müssen. Leider sind aber auch die erzwungenen Ausführungspläne sehr starr in ihrer Struktur, so dass der DBA auch in dem hier vorgestellten Fall von Zeit zu Zeit prüfen muss, ob die Pläne noch zu der aktuellen Datenverteilung passen. Auch erzwungene Pläne können ungültig werden, wenn die zu Grunde liegenden Strukturen (Indizes) geändert werden. Es ist also auch an dieser Stelle eine regelmäßige Überprüfung erforderlich.
Wenn Sie mehr über dieses Thema erfahren möchten, stehen Ihnen unsere Expert:innen gerne zur Verfügung. Vereinbaren Sie gerne ein unverbindliches Beratungsgespräch über unser Kontaktformular.
Wir helfen Ihnen gerne weiter!
