Abfrageoptimierung mit SQL Server

1. Abfrageoptimierung mit SQL Server Steffen Krause Technical Evangelist Microsoft Deutschland GmbH http://blogs.technet.com/steffenk

2. Agenda

3. Gründen für schlechte Performance…einige • I/O-Subsystem • Log-Schreibperformance • tempdb • RAID-Level • Filegroups/Partitionierung • Zu viele Recompiles • Manchmal auch zu wenige • Parallelitäts-Probleme (Lock-Waits) • Ausführungsreihenfolge • Isolation Level • Bottleneck-Analyse mit sys.dm_os_wait_stats • Fehlende, falsche oder überflüssige Indizes • Fehlende oder nicht aktuelle Statistiken • Ungünstiges Datenbankdesign • Ungünstige Abfragen

4. Welche Abfragen analysieren? • Abfragen mit langer Laufzeit • Sehr häufig ausgeführte Abfragen • Sys.dm_exec_query_stats • Execution_count, total_worker_time, total_elapsed_time • Hilft nicht bei fehlender Parametrisierung • Reports: Performance – Top Queriesby…

5. Warum mit Ausführungsplänen beschäftigen? • Verstehen, wo in wichtigen Abfragen der meiste Aufwand geleistet wird • Optimierungsmöglichkeiten durch Erstellung oder Änderung von Indizes oder Partitionen, abdeckenden Indizes oder indizierten Sichten • Eventuell Datenbankschema überprüfen, denormalisieren usw. • Vergleichen, wie Schätzungen und tatsächliche Kosten aussehen • Spezifische Abfragen mit OptimizerHints optimieren • Alte Hints eliminieren, die nicht mehr helfen • Optimierung der Abfrage selbst

6. Bindung, Views expandieren Kompilierter Plan gefunden Ausführbarer Plan gefunden Auto-Parametrisierung SQL-Sprachverarbeitung (Parse/Bind, Statement/Batch Execution, Plan Cache Management) Parse Lookup im Plan Cache Abfrageausführung (Query Operators, Memory Grants, Parallelism, Showplan) Abfrageoptimierung (Plan Generation, View Matching, Statistics, Costing) NutzenoderErstelleneinesAusführungsplans Abfrageoptimierung Memory Grants & Degree of Parallelism festlegen Ausführen Plan in Cache schreiben Lebenszyklus von Abfragen NeueAbfrage Nichtgefunden

7. Inhalte des Ausführungsplans • Wie Daten zugegriffen werden • Wie gejoint wird • Wie Daten aggregiert werden • Reihenfolge der Operationen • Verwendung von temporären Objekten und Sortierungen • Schätzungen für Zeilenanzahlen, Iterationen und Kosten pro Schritt • Tatsächliche Zeilenzahlen und Iterationen • Wenn Statistics Profile genutzt wird • Verwendung von Parallelität

8. Ausführungspläne sind Bäume • Man kann sich die Ausführung als „Pipeline“ vorstellen • Daten bewegen sich von den Blättern zur Wurzel des Baumes • Joins erzeugen Verzweigungen mit 2 Teilbäumen • Jeder Schritt ist ein „Operator“ • Bei Joins müssen beide Zweige vor dem Join selbst ausgeführt werden (pro Zeile) • Eine Zeile kann durch viele Operatoren fließen bevor die nächste Zeile gelesen wird

9. Agenda

10. Wege zum Ausführungsplan • Management Studio • Geschätzten Ausführungsplan anzeigen • Verwendet SET SHOWPLAN_XML ON • Abfrage wird nicht ausgeführt • Enthält geschätzte Zeilenzahlen • Tatsächlichen Ausführungsplan einschließen • Verwendet SET STATISTICS XML ON • Abfrage wird ausgeführt • Enthält tatsächliche und geschätzte Zeilenzahlen • Eigenschaften oder XML-Plan für Details

11. Ausführungspläne aus dem Plan Cache • Ermöglicht das Auslesen des Plan Cache • Auch SQL aus Anwendungen, für die kein Quelltext zur Verfügung steht • Inhalt des Ausführungsplan-Cache • sys.dm_exec_query_stats • sql_handle in Anweisungen: sys.dm_exec_sql_text • plan_handle gibt XML Ausführungsplan aus dm_exec_query_plan • Details (für Recompile-Analyse) über dm_exec_plan_attributes • Schema-Dokumentation: showplan.xsd, lokal und unter http://schemas.microsoft.com/sqlserver/ • Für grafischen Ausführungsplan in SQL 2005: • XML-Plan in Datei speichern und in .sqlplan umbenennen

12. Dynamic Management ViewsStatement Cache - Ausführungspläne • Plan kann <MissingIndex>-Element enthalten: Welcher Index würde dieser Abfrage helfen select text, query_plan, st.*from Sys.dm_exec_query_stats st cross apply sys.dm_exec_sql_text(sql_handle) s cross apply sys.dm_exec_query_plan(plan_handle) where query_plan.exist(' declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; //sp:MissingIndex') = 1 • Alternativ, wenn nur fehlende Indizes interessieren • Verwenden von sys.dm_db_missing_index_... SELECT migs.*, mid.* FROM sys.dm_db_missing_index_group_statsAS migs INNER JOIN sys.dm_db_missing_index_groupsAS mig ON (migs.group_handle = mig.index_group_handle) INNER JOIN sys.dm_db_missing_index_detailsAS mid ON (mig.index_handle = mid.index_handle)

13. {Ermitteln von Ausführungsplänen } Demo

14. Agenda

15. Worauf man achten sollte • Große Zeilenzahlen • Große geschätzte Kosten • Join-Techniken (Hash, Loop, Merge) • Zugriffstechniken (Seeks, Scans) • Aggregationstechniken (Hash, Stream) • Andere Operationen (Sort, Top, …)

16. Nested Loop Joins • Für jede Zeile der äußeren (oberen) Eingabe wird die innere (untere) Eingabe einmal gescannt • Es wird also so oft iteriert, wie die obere Eingabe Zeilen hat • Nested Loops sind die einzige Quelle von Iterationen in einem Ausführungsplan • APPLY in SQL 2005 ist auch als Nested Loop implementiert • Viele Subqueries werden Nested Loops

17. Nested Loop JoinsWorauf achten • Im inneren (unteren) Zweig sollte ein guter Index Seek verwendet werden • Die Anzahl der Iterationen sollte viel kleiner als die Tabellengröße der unteren Tabelle sein • Sonst wäre ein Hash Join effizienter • Die Kosten sind abhängig von der Anzahl der Iterationen und der gelesenen Zeilenzahl pro Iteration • Am besten, wenn der innere Seek sehr selektiv ist und der äußere wenige Zeilen enthält

18. Hash Joins • Aus der oberen Eingabe wird eine Hashtabelle im Hauptspeicher erstellt • Jede Zeile der unteren Eingabe wird gegen diese Hashtabelle auf Übereinstimmung geprüft • Die kleinere Datenquelle wird zur Erstellung der Hashtabelle genutzt • Hash Joins in häufig benutzten Abfragen deuten auf einen fehlenden Index auf den Join-Schlüssel der inneren Tabelle hin • Missing Index Element im XML-Ausführungsplan überprüfen • Selbst mit Index kann ein Hash Join effizienter als eine Nested Loop sein, wenn die Anzahl der Zeilen in der äußeren Tabelle sehr groß ist

19. MergeJoins • Beide Engaben müssen in Reihenfolge des Join-Schlüssels sortiert sein • Über Index Scan oder Sort-Operation • Ausführung abwechselnd in oberen und unteren Zweig, je nachdem wie der Join Key sich ändert • Wie bei einem Hash Join wird jeder Zweig einmal durchlaufen • Feste Kosten für komplettes Durchlaufen beider Eingaben

20. Scans und Seeks • Verwechseln Sie nicht Index Scans mitIndex Seeks • Seek ist die einzige effiziente Strategie, um einzelne Zeilen oder einen Bereich von gesuchten Zeilen zu finden • Scan liest alle Zeilen eines Index und ist für große Indizes teuer, es sei denn, der größte Teil des Index wird benötigt • Wenn die Tabelle einen Clustered Index hat (gruppierter Index), dann wird ein Table Scan immer als Clustered Index Scan dargestellt

21. Index-Überlegungen • Ein Index Seeks benötigt gültige Suchargumente ab der ersten Spalte im Index • Eine Suche, die die erste Spalte des Index nicht enthält wird nie einen Seek ergeben, kann aber von einem Index Scan profitieren • Mehrspaltige Seeks benötigen Gleichheit in den ersten Spalten des Index

22. Aggregationen • Stream Aggregate • Daten müssen in Group By Reihenfolge sortiert sein • Akkumulieren Werte aus sortiertem Stream bis sich der Group By Wert ändert • Behält Sortierreihenfolge für folgende Operationen bei • Hash Aggregate • Sortierung nicht erforderlich • Muss Hashwert für die Group By Spalte jeder Zeile berechnen • Entscheidend sind CPU- und Speicherverbrauch Hash kontra Sort und die Beibehaltung der Sortierung

23. Teure Operationen • Große Sorts • Table Scans großer Tabellen oder • Index Scans großer Indizes • Außer, wenn die Abfrage einen großen Teil der abgefragten Tabelle benötigt • Achten Sie besonders auf große Scans für Hash Joins, die nur ein paar Zeilen zurückliefern • Oft ist ein Loop Join/Index Seek schneller als ein Hash, wenn der Join sehr selektiv ist und gute Indizes existieren.

24. Parallelismus • Jede parallele Operation wird von allen Threads in einem parallelen Plan ausgeführt • „Partition Parallelism“, nicht „Pipeline Parallelism“ • Gelber Indikator im grafischen Showplan • Mehr Details in XML-Plan und Eigenschaften • Partitionierungsspalten werden verwendet, um unabhängige Bearbeitung der Streams zu erlauben • Repartition Streams ist aufwändig, aber notwendig, um Streams für die nächste Operation auszurichten

25. {Ausführungsplan-Beispiele } Demo

26. Agenda

27. Optimierung des Ausführungsplans • Datenbankstruktur ändern • Abfragen umschreiben • Indizes anlegen, ändern, löschen • Partitionieren • Statistiken aktualisieren • OptimizerHints • Plan Guides

28. Recompiles vermeiden • StoredProcedures • Parametrisierung • Auto-Parametrisierung: SIMPLE vs. FORCED • Tabellenerzeugung • Bestimmte Session-Settings erzwingen Recompile bei jeder Ausführung • Manchmal sind Recompiles für optimalen Ausführungsplan notwendig • Komplexe Abfragen mit sehr unterschiedlicher Selektivität je nach Parameter

29. Ändern der DatenbankstrukturIndizes, Partitionierung, Statistiken • Immer die Gesamtheit der Abfragen betrachten • Trace aufzeichnen • Datenbank-Optimierungsratgeber ausführen • Reports genau untersuchen • Clustered Indizes • Regeln beachten, sonst fragmentiert die DB • „Covering Indizes“ erstellen • Include-Spalten in 2005/2008 • Aktuelle Statistiken sind essenziell • Auto Create Statistics und Auto Update Statistics an • oder Statistiken im Wartungsplan aktualisieren

30. Query HintsDisclaimer • Query Hints sind normalerweise böse • Erzwingung eines Ausführungsplans zerstört über 20 Jahre Fortschritte im Query Optimizer • Nachdem das klar ist…

31. Hints • 3 Arten von Hints • JOIN Hint • Auswahl des Join-Typ: Loop, Hash, Merge • Umkehrung des Joins mit REMOTE • QUERY Hint • Viele verschiedene • Direkte Beeinflussung der Operatoren für Aggregation, Union, Join • FAST Zeilenanzahl • OPTIMIZE FOR Parameterwert • TABLE Hint • Table Scan oder Indexverwendung • Isolation Level • Locking-Verhalten

32. Über Hints hinaus • Hints erfordern Änderung des Quellcodes • Hints können nicht einfach aktiviert oder deaktiviert werden • Hints sind für den Administrator nicht direkt sichtbar • Tendieren dazu, „vergessen“ zu werden • Willkommen, Plan Guides…

33. Plan Guides • Ermöglichen Hints für Queries ohne Quellcode-Änderung • Benannte Objekte in der Datenbank • Erstellt im Management Studio • oder sp_create_plan_guide • Können aktiviert und deaktiviert werden • Erstellen und (de-)aktivieren löscht entsprechende Abfragen aus dem Cache

34. Arten von Plan Guides • Arten: • Object: für Ausführung in Prozeduren, Funktionen und DML Triggern • SQL: für SQL-Anweisungen, die direkt ausgeführt werden • Template: Zur Beeinflussung der Autoparametrisierung (SIMPLE oder FORCED) • Spezialfall: Kompletten Ausführungsplan erzwingen • xml_showplan in sp_create_plan_guide • sp_create_plan_guide_from_cache

35. {Plan Guides } Demo

36. Weitere Informationen • SQL Server 2008 Informationen • http://www.microsoft.com/germany/sql/2008 • SQL Server Performance Website • http://www.sql-server-performance.com/ • Steffens SQL Server 2008 Ressourcenseite • http://blogs.technet.com/steffenk/pages/sql-server-2008-ressourcen.aspx • Blog Steffen Krause • http://blogs.technet.com/steffenk • Webcast-Aufzeichnungen • http://www.microsoft.com/germany/technet/webcasts/default.mspx?tab=2

37. AsktheExperts Wir freuen uns auf Ihre Fragen: Technische Experten stehen Ihnen während der gesamten Veranstaltung in der Haupthalle zur Verfügung.

38. © 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.