Was ist der Execution Plan?

Der Execution Plan / Ausführungsplan / Abfrageplan / Abfrageausführungsplan ist das Ergebnis des Query Optimizers (Abfrageoptimierers) – also das Ergebnis der Analyse der Anweisungen und die aus Sicht des Query Optimizers effizienteste Methode des Zugriffs auf die Daten.

  • Reihenfolge des Zugriffs auf die Quelltabellen
  • die Methode, die verwendet wird um Daten aus den TAbellen zu extrahieren
  • Methoden für die Berechnung von Filtern, Aggregieren von Daten aus den einzelnen Tabellen.

Es gibt hierbei 3 Ausführungspläne im SSMS

  • geschätzter Ausführungsplan (Plancache)
  • tatsächlicher Ausführungsplan (kompilierter Plan und Ausführungskontext)
  • Liveabfragestatistik (inkl. Laufzeitinformationen)

Was ist „optimal“?

Wer sich mich Query Optimierung beschäftigt, steht immer vor der Frage, wann ist genug. Es gibt sehr viele Stellschrauben einer Query, sodass man sich nicht alle ansehen kann. zentrale Frage: Was soll die Abfrage machen?

  • Wie groß ist das Resultset / Ergebnis?
  • Gibt es Parameter, die das Ergebnis einschränken?
  • Wie oft wird die Abfrage ausgeführt?
  • Was ist eine akzeptable Laufzeit?

Tools, um Queries zu analysieren

  • Execution Plan
  • Statistics IO (SET STATISTICS IO ON;)
  • Query Duration

Bewertung von Execution Plänen

Der Query Optimizer kann natürlich nur innerhalb eines gewissen Rahmens die Abfrage optimieren, da er einerseits nur bedingt Zeit hat und auch nicht an allen Stellschrauben Anpassungen vornimmt. Wie kann man nun aber Ausführungen vergleichen? Hierzu verwendet man das Attribut „Cost“ eines jeden Schrittes. Hierbei handelt es sich um eine relative Größe.

Wie kommt der Query Optimizer nun zum „richtigen“ Execution Plan? Der Query Optimizer ermittelt potentielle Execution Plans und ordnet diese anhand der Costs. Es kann aber der Query Optimizer zum Ergebnis kommen, dass es zeitlich nicht möglich den optimalen Plan zu ermitteln, dann vergleicht er nur ein paar, die er am besten erachtet. Danach wird der Plan ausgeführt.

Plan Reuse: Da es ressourcen technisch sehr aufwändig ist, einen „richtigen“ Execution Plan zu ermitteln, wird der Plan in einem Cache geschrieben, damit bei erneuter Verwendung auf diesen zurückgegriffen werden kann. Eine Liste aller gespeicherten Execution Pläne findet man mit folgender Abfrage

select plan_handle, creation_time, last_execution_time, execution_count, qt.text
FROM 
   sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt

Recompile Hints

Es gibt eine Reihe von Recompile Hints – also Ausführungshinweise, die zum Recompilieren der Query führen.

Runtime Recompile Hint

Wird eine SP mit dem Recompile Hint ausgeführt, wird die SP recompiliert, jedoch nur die äußerste. Dh nested Stored Procedures bleiben gleich.

EXEC dbo.Proc3 @parameter1=80 WITH RECOMPILE;

sp_recompile

Recompile in SP Headers

Recompile Hints in SQL Statements

FreeProcCache

Um einen einzelnen schlechten Execution Plan zu löschen verwendet man folgende Abfrage

DBCC FREEPROCCACHE (plan_handle_id_goes_here)

Um alle Execution Pläne zu löschen, verwendet man folgende Abfrage

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

Tip: sp_updatestats

Es ist zusätzlich angeraten, dass man sp_updatestats vor dem Recompilieren zu verwenden. Hierbei werden die Query Optimization Statistics aktualisiert werden. (UPDATE STATISTICS)

EXEC sp_updatestats

Links: