Inhalt isiamrobertson Einrichten von PLSQL Developer, Teil 2 Dieser Artikel wurde für PLSQL Developer 8.0.4 mit Oracle 11.2 und Windows XP in einer virtuellen Parallels-Maschine auf meinem Mac geschrieben, weshalb die Screenshots eine Mischung aus XP Silver und Aqua Fenstern zeigen. PLSQL Developer ist eine von mehreren integrierten Entwicklungsumgebungen (IDEs), die für Oracle verfügbar sind. Eines der Dinge, die mir gefällt, ist, wie es konfigurierbar ist - man kann fast alles ändern und mit herunterladbaren Plug-Ins wie zB Browser Extender können Sie Ihre eigene Funktionalität hinzufügen. Nachdem ich mehrere PCs gewechselt habe und PLSQL Developer jedes Mal neu installieren musste, habe ich festgestellt, dass es einige Anpassungen gibt, die ich nicht ohne wohnen konnte, und ich dachte, Id dokumentieren sie. Teil 1 deckte Präferenzen wie Schriftarten und Bildschirm-Layout, und Teil 2 deckt den Session-Browser. Ich würde die benutzerdefinierten rechts-Mausklick-Aktionen, die Ive hinzugefügt mit Browser Extender gehören. Aber es gibt so viel können Sie mit dem Session-Browser, die ich gehen müssen, um das für Teil 3 verlassen. Erweiterung der Session Browser 1. Machen Sie die Session Browser einfacher zu finden In der Standard-Layout, seine vergrabene Weg nach unten die Liste unter dem Tools-Menü, aber als seine etwas youll verwenden die ganze Zeit seine viel besser, um einen Knopf für sie haben. Falls Sie es in Teil 1 verpasst haben, können Sie die Symbolleiste anpassen, indem Sie ein Symbol für den Session Browser hinzufügen. Heres die Art von Dingen, die Sie tun können: Standard-Symbolleiste Kundenspezifische Symbolleiste Beachten Sie die Kreuztasten Symbol zweiten von links in der benutzerdefinierten Symbolleiste. 2. Ein Blick auf die Standardeinstellungen Jetzt öffnen Sie den Session Browser und schauen Sie sich die Standardeinstellung an. (Tatsächlich nicht ganz Standard - Ive änderte die Schriftart auf Corbel 8pt, die mehr Informationen auf dem Bildschirm passt, sowie attraktiver als der Standard meiner Meinung nach. Tahoma funktioniert auch gut. Sie werden auf diesem Bildschirm eine Menge, Immerhin.) Der Bildschirm ist ein Master-Detail-Bericht, wobei sowohl Master - als auch Detailabfragen mit dem Schraubenschlüssel-Symbol konfigurierbar sind. Die Master-Abfrage im oberen Teil des Fensters ist unter Filters definiert und einige Variationen zur Auswahl aus der Vsession werden bereitgestellt. Unter Details gibt es vier sehr einfache Abfragen für offene Cursor, aktuelle SQL-Anweisung, Sitzungsstatistiken und Sperren: Beachten Sie die Bindungsvariable: sid in der Cursors-Abfrage. Die coole Sache über Session-Browser-Detailabfragen ist, können Sie sich auf den aktuellen Wert einer beliebigen Spalte aus dem oberen Abschnitt als Binde-Variable in Detailabfragen beziehen. Also, solange die Hauptabfrage eine Spalte mit dem Namen sid enthält, können wir Ausdrücke wie wo sessionid: sid in jeder Detailabfrage verwenden. (Dies bedeutet jedoch, dass Sie möglicherweise ein paar Spalten in die Master-Abfrage rein, um als Schlüssel in Detail-Abfragen zu verwenden.) Ein weiterer Punkt zu beachten, über die Detail-Abfrage-Box ist, dass das Hinzufügen der Text verkettet, nachdem die Abfrage macht PLSQL-Entwickler verbinden alle Zeilen der Ausgabe in einen großen Block. Während ein ordentliches Merkmal, das verhindert auch das Scrollen, so finde ich es einen gemischten Segen. 3. Schreiben Sie Ihre eigenen VSESSION Abfragen Aktive Sitzungen Die Standardabfragen sind alle aus vsession wo auswählen. . Was natürlich eine sinnvolle Voreinstellung ist, die über alle Oracle-Versionen hinweg funktioniert. Neue und nützliche Attribute werden in jeder Version zur vsession hinzugefügt und natürlich auch explizit in Joins und Lookups codiert bedeutet, dass die Abfrage möglicherweise nicht in einer früheren Version funktioniert.1 Wenn Sie mit mehreren Oracle-Versionen arbeiten, müssen Sie möglicherweise speichern Mehr als eine Abfrage im Bereich "Filter" und wähle die passende nach Bedarf aus (leider kann PLSQL Developer die Version nicht überprüfen und sie für dich auswählen). Heres eine bessere Active Sessions Abfrage für Oracle 10.2.0.2 an (beachten Sie die Spalten plsqlentryobjectid und plsqlentrysubprogramid unter anderem wurden in dieser Version hinzugefügt, damit es nicht in Oracle 10g XE arbeiten). Alle Sessions, die derzeit aktiv sind (außer Oracle-Hintergrundprozesse wie Log Writer), oder die andere Sessions blockieren oder im Besitz von mir sind. Ihr Elternteil, wenn Teil einer parallelen Abfrage Das Objekt, das gerade gewartet wird (normalerweise eine Tabelle oder ein Index) - blickte von dbaobjects mit rowwaitobj auf. Der PLSQL-Eintrag und aktuelle Prozeduren - blickte von dbaprocedures mit den plsql Spalten in Oracle 10.2.0.2 hinzugefügt. Einige Statistiken über CPU, liest, Speicherverbrauch und Abfrage-Parsing, von vsessmetric. Wenn die Ergebnisse angezeigt werden, können Sie auf diese Spalten klicken, um Sitzungen nach CPU-Nutzung zu sortieren Jede Sitzung, die einen anderen blockiert, unabhängig von seinem Status, zusätzlich zu den derzeit aktiven Vordergrundsitzungen. Die RAC-Instanz für Multi-Knoten-Cluster. Wenn Sie nur eine einzige Instanz haben, wird es 1 sein (Sie möchten es an das Ende der Auflistung verschieben, um Platz für andere Spalten zu machen). GV-Ansichten für RAC Die v-Ansichten (eigentlich Synonyme für sys. v-Ansichten) haben alle g-prefixed-Versionen - zum Beispiel gvsession - die die Instanznummer enthalten, für den Einsatz in RAC-Systemen. Für Einzelinstallationssysteme ist dies immer 1. Die Dokumentation listet nur die v-Version auf, also wenn du über gvsession wissen willst. Zum Beispiel, schau nach oben vsession und nehme an, dass es eine zusätzliche Spalte mit dem Namen instid geben wird. Ich habe die regulären v und RAC-fähigen gv Namen austauschbar benutzt. Kopiere die Abfrage unten in das Feld Abfrage (nach dem Testen in einem SQL-Fenster, um sicherzustellen, dass es mit deiner Oracle-Version und Berechtigungen funktioniert - auf die V-Ansichten zugreifen, die du SELECTCATALOGROLE benötigst). Beachten Sie, dass es am Ende kein Semikolon gibt. Vielleicht möchten Sie es auch gegen vsession überprüfen, falls es nützliche Spalten gibt, die für Sie nützlich sind. Meine Sessions Auf einem belebten System willst du manchmal nur deine eigenen Sessions sehen und alles andere ausschließen. Dazu verwende ich eine My Sessions Abfrage, die die gleiche wie die oben ist, außer für die WHERE-Klausel, die ist: Alle Sessions ist auch manchmal praktisch, eine Version zu haben, die alle Sessions zeigt, einschließlich des Oracle Log Schriftstellers, Prozessmonitors etc Machen Sie eine weitere Kopie der Abfrage oben, und lassen Sie einfach die WHERE-Klausel. 4. Jetzt fügen Sie Ihre eigenen Detail Tabs Dies verwendet vsqlstats, um detaillierte Ausführungsstatistiken über die Sitzungen aktuelle SQL-Anweisung (identifiziert durch sqlid) anzuzeigen. Beachten Sie, dass es sich auf alle Instanzen des Cursors bezieht, nicht nur diese Sitzungen aktuellen Anruf. (Auch, da die v-Ansichten nur das widerspiegeln, was jetzt im Speicher ist, kann es anders sein als das, was man in den dbahistischen Ansichten sieht, wenn man das Diagnose-Paket hat.) Die Idee der Prozentsätze ist zu zeigen, wie die gesamte verstrichene Zeit zusammenbricht In CPU, IO, Gleichzeitigkeit wartet etc. Sein nur annähernd und sie sagen nicht immer bis zu 100, weil es andere Faktoren geben kann, die für die Netzübertragungszeit und Anwendungsverarbeitung nicht berücksichtigt werden, aber sie geben Ihnen eine Idee, wie die Aussage ist verarbeitet. Jetzt solltest du einen SQL Stats Tab wie den Screenshot unten für jede Session, die SQL ausgeführt wird. (Binds, Prev SQL etc. sind andere Registerkarten Ill definieren in einem Moment.) Perf Geschichte dieser Cursor Wenn eine SQL-Anweisung eine lange Zeit dauert, möchten Sie vielleicht ihre Performance-Historie (von dbahistsqlstats) zu sehen, ob dies normal für die ist Cursor oder ob sich etwas geändert hat. Die erste Abfrage unten gibt die verschiedenen Ausführungspläne und ihre entsprechenden Laufzeitstatistiken, aggregiert für die gesamte Geschichte des Cursors, so dass Sie die durchschnittliche Ausführungszeit sehen können und ob mehrere Pläne existieren. (Beachten Sie die Verknüpfung zu gvsqlplan - die g, die die RAC-fähige Version anzeigt, die die zuverlässigste Art ist, den Ausführungsplan zu finden, der gerade verwendet wird, da er die untergeordnete Nummer enthält. Als vsqlstats meldet nur eine Zeile pro deutlicher sqlid Die zweite Version, die ich als Perf-Historie diesen Cursor nach Datum markiere, teilt die gleiche Information am Tag auf, also kannst du sehen, ob es am letzten Dienstag schnell läuft oder ob das Plan geändert heute Morgen: Die folgende Abfrage listet alle Bindungsvariablen auf, die in vsqlbindcapture für die aktuelle SQL-Anweisung gehalten werden. Ich habe die Ergebnisse gefiltert, um Duplikate auszuschließen. Beachten Sie, dass Oracle nicht erfassen jeden einzelnen Bind-Wert, und hält nur den letzten Wert im Intervall cursorbindcaptureinterval erfasst und abhängig von der Menge verfügbar Platz bis zu cursorbindcaptureareasize. Eine Alternative ist, die Bindungsdaten, die in der Parsezeit von vsqlplan verwendet werden, zu erhalten. Obwohl dies einige Decodierung, wie es im RAW-Format in einer XML-Spalte gehalten wird - siehe Jonathan Lewis Blog Post Bind Capture. Die Verknüpfungen zum Erstellen von Test-Skripten mit Bind-Variablen von Kerry Osborne und Verfolgen des Bind-Wertes von Dion Cho. Dies führte mich zu der folgenden Abfrage mit einer Idee von Kyle Hailey in den Kommentaren zu Jonathan Lewis Post: In meinen Tests mit Oracle 11.2.0.2 dies weglässt die Bindungsnamen. Jedenfalls ist das Erfassen von Bindungswerten ein großes Thema, also krank dich mit den Fragen ab, um mit zu experimentieren und weiterzumachen. Vorherige SQL, previous SQL stats Gelegentlich ist es nützlich zu sehen, was die vorherige Aussage war. Vsession enthält mehrere prev-Spalten, also nur die Detail-Tabs für SQL Text und SQL Stats duplizieren, aber ersetzen prevsqlid und prevchildnumber. Objektstatistiken Bei der Ermittlung eines Leistungsproblems möchten Sie den aktuellen Stand der Statistik auf den in der Abfrage enthaltenen Tabellen häufig überprüfen. Die Abfrage unten verbindet vsqlplanstatisticsall mit dbatabstatistics, um diese Informationen aufzulisten - es ist nicht perfekt, wenn partitionierte Tabellen beteiligt sind, da das Problem mit Stats für eine einzelne Partition oder Unterpartition liegen könnte, aber es ist ein Start. Ersetzen Sie die Standard-Cursors-Abfrage (wählen Sie aus vopencursor, wo sid: sid) mit dem folgenden, um einige Aktivitätsstatistiken hinzuzufügen. (Beachten Sie, dass die Ausführungen Statistik bezieht sich auf alle Sitzungen, nicht nur die aktuelle Sitzung.) Aktueller Plan PLSQL Entwickler eingebaut Explain Plan Tool (F5) ist alles gut und gut, aber es kann nur so gut wie erklären Plan. Das heißt, das Tool verwendet den Plan, um den Ausführungsplan vorherzusagen, und zeigt dann die Ergebnisse in grafischer Form an. Manchmal ist das nicht das gleiche wie der eigentliche Laufzeitplan. Bei der Betrachtung der derzeit laufenden Sitzungen, ich mag dbmsxplan. displaycursor () verwenden, um zu sehen, was die Datenbank tatsächlich tut. Definiere einen aktuellen Plan-Tab mit folgendem: Der verkettete Kommentar macht PLSQL-Entwickler alle Zeilen der Ausgabe aus der Abfrage in einen großen Block. Dies macht es leichter zu lesen, obwohl es auch verhindern, dass das Scrollen so Im nicht sicher, dass alles, was nützlich hier ist. (Sie können leider keine Monospace-Schrift für einen einzelnen Menüpunkt angeben, also ist die Standardanzeige nicht so toll.) Der beste Weg, um es zu lesen, ist zu kopieren und in ein neues SQL-Fenster einzufügen. Dies ist einfacher, wenn Sie einen Hotkey wie Alt-S für Datei gt New gt SQL-Fenster definiert haben, wie ich in Teil 1 vorgeschlagen habe. (Ich habe auch eine Browser Extender-Erweiterung, um dies in einem Rechtsklick zu tun, was komm später später. ) Ich benutze auch eine andere Variante dieser Abfrage, die Ive beschriftet Current Plan GPS (Gather Plan Statistics - obwohl vielleicht Extended Plan wäre ein besserer Name jetzt denke ich darüber). Dies verwendet ALLSTATS LAST im Formatargument zu dbmsxplan. displaycursor, um die geschätzten und tatsächlichen Zeilenzahlen (Kardinalität) zu erhalten, wenn die Abfrage den Sammlungsplanstatistiken-Hinweis verwendet hat oder wenn der Parameter statisticslevel für die Sitzung auf ALL gesetzt wurde. Der etwas heikle Teil mit diesem ist, dass man es nicht verwenden kann, bis die Abfrage abgeschlossen ist (weil die eigentliche Zeile zählt arent noch bekannt), aber wenn es fertig ist, ist es nicht mehr die aktuell ausgeführte Abfrage und verschwindet daher aus der Vensession und so, wenn Sie Aktualisiere deinen Sitzungsbrowser. Stattdessen müssen Sie den Bildschirm aktualisieren, während die Abfrage ausgeführt wird, aber warten, bis es abgeschlossen ist, bevor Sie auf die aktuelle Plan GPS-Registerkarte. Aktuelle Wartezeit Obwohl die Sitzungen aktuellen Wartezustand bereits in der Master-Abfrage angezeigt wird, oben, ich mag die Informationen auch in der eigenen Registerkarte haben. Ive beschriftet, dass das Warteobjekt von rowwaitobj als objektiv unabhängig als eine Erinnerung aufgeschlagen wurde, obwohl dies das jüngste Objekt ist, auf das die Session wartete, könnte die Verarbeitung nun auf etwas anderes verschoben werden (zB die Sortierausgabe oder eine Warte auf eine Anwendung) Verarbeiten die Abfrageausgabe) und die Sitzung greift momentan nicht auf dieses Objekt zu. Die letzten 10 Warten Die folgenden gibt einen kurzen Blick auf die Sitzungen der letzten Aktivitäten mit vsessionwaithistory (Wartezeit ist in Hundertstelsekunden): vsessionlongops zeigt den Status der verschiedenen Operationen, die länger als 6 Sekunden laufen. Diese Operationen beinhalten derzeit viele Sicherungs - und Wiederherstellungsfunktionen, die Erfassung von Statistiken und die Abfrageausführung und weitere Vorgänge werden für jede Oracle-Version hinzugefügt. Wenn eine Abfrage Hash - oder Sortiervorgänge verwendet, Tabellen-Scans, Partitionsoperationen usw., die mehr als 6 Sekunden dauern, werden diese Operationen in vsessionlongops angezeigt und Sie können ihren Fortschritt verfolgen. (Beachten Sie, dass es nur einzelne Operationen gibt, die verfolgt werden, nicht ganze Abfragen.) Viele langlebige Oracle-Prozesse werden ebenfalls instrumentiert, wie das Handbuch erwähnt. Andere, die oben nicht aufgeführt sind, umfassen Datenbankwiedergabe und SQL Performance Analyzer-Läufe (11g) und Datapump Importexport-Jobs - und natürlich auch alle Ihre eigenen Prozesse, in denen Sie dbmsapplicationinfo. setsessionlongops aufrufen, um die gesamte Arbeit und den bisher verarbeiteten Betrag zu protokollieren. Ich definiere auch eine lange Ops dieser Abfrage Registerkarte, die eine Kopie der oben genannten ist, aber mit einem zusätzlichen Filter, um es auf die derzeit ausgeführte Operation zu begrenzen: ASH Zusammenfassung - Sitzung vactivesessionhistory ist ein Schnappschuss der vsession einmal pro Sekunde genommen, für eine gehalten Begrenzte Zeit (normalerweise 30 bis 60 Minuten) und dann in dbahistactivesesshistory gespeichert. (Um dies zu benutzen, benötigen Sie das Diagnose-Paket, also stellen Sie sicher, dass youre lizenziert ist, auch wenn funktioniert - Sie möchten nicht, dass Ihr Chef eine unerwartete Rechnung nach einem Oracle-Audit bekommt.) Es gibt viele kreative Möglichkeiten, diese Informationen zu machen, und ich verwende drei Abfragen Um zu verfolgen, was eine gerade laufende Sitzung macht. Da ASH jede Sekunde abtastet, kann es sinnvoll sein, es durch die SQL-Anweisung zusammenzufassen und die Ergebnisse nach der Zeit aufzulisten. Wenn Sie eine Prozedur oder einen Batch ansehen, der mehrere Anweisungen aufruft, gibt dies einen Überblick darüber, wo die Session ihre Zeit verbringt (ein bisschen wie die Session verfolgen). Die folgende Abfrage gibt eine Zeile pro sqlid. In absteigender Reihenfolge der Gesamtzeit, mit einer Summe an der Unterseite. ASH Zusammenfassung - Ausführungen habe ich auch eine ausführlichere Version möglich gemacht in 11g von der sqlexecstart Spalte in vactivesessionhistory. Was mir erlaubt, einzelne Ausführungen einer SQL-Anweisung zu sehen, anstatt eine einzelne aggregierte Zeile. ASH-Zusammenfassung - Zeit-Cursor-Objekt Berichte mit Objekten, die von allen SQL-Anweisungen für die angegebene Sitzung gewartet wurden. Dies ist als eine schnelle Möglichkeit zu sehen, was eine Sitzung hat seine Zeit auf, in Bezug auf Objekte statt Abfragen verbracht. ASH Zusammenfassung diese Abfrage mit Anrufer Weiter habe ich eine GROUP-BY Abfrage für die aktuelle sqlid. In absteigender Reihenfolge der Stichprobenanzahl. Die Idee ist zu sehen, wo die Zeit in der aktuell ausgeführten Anweisung ausgegeben wird (anstatt welche Aussagen haben Zeit in der aktuellen Sitzung genommen). Da Active Session History ein 1-Sekunden-Polling-Intervall verwendet, hat etwas, das in 10 Samples auftritt, wahrscheinlich ca. 10 Sekunden gedauert. Beachten Sie, dass es nur auf sqlid filtert. So werden mehrere Ausführungen der gleichen Abfrage durch die Sitzung alle zusammen aggregiert. (In 11g können Sie die neue sqlexecid Spalte verwenden, um zwischen executionen zu unterscheiden.) Beachten Sie auch, dass ASH die Aktivität als On CPU zusammen mit einem Datenbankobjekt ausmachen kann - das bedeutet nur das letzte Objekt, auf das zum Zeitpunkt der Probe zugegriffen wurde, nicht Dass die CPU unbedingt mit diesem Objekt verwandt war. Ich habe zwei Aromen von diesem, mit und ohne Details der anrufenden PLSQL-Prozedur. ASH Zusammenfassung diese Abfrage SQL nur Dies ist die gleiche wie die vorherige Abfrage, aber ohne die aufrufenden PLSQL Details geben einen klareren Blick auf den Datenbankzugriff. ASH Detail dieser Session Schließlich habe ich eine gerade Auflistung der vactivesessionhistory für die aktuelle Sitzung, so dass Sie eine Vorstellung davon bekommen können, was es derzeit tut: Das Standard-Setup kommt mit einem Sperren Tab. Lets sagen, dass eine Sitzung die folgenden Aktionen ausführt: Die Registerkarte "Default Locks" zeigt dies an: Wenn Sie es ändern, finden Sie weitere Details: Optimizer nonstandard Ich finde dies nützlich, um zu überprüfen, welche Optimierereinstellungen von einer bestimmten Sitzung verwendet werden (was nicht der Fall ist Gleiche Einstellungen wie Ihre Sitzung oder die Instanz-Standardwerte). Dies verbindet vsysoptimizerenv (optimizerbezogene Systemparameter) mit vsesoptimizerenv (optimiererbezogene Sessionparameter, die ursprünglich von den Einstellungen auf Systemebene geerbt wurden, aber jegliche Änderungen, die durch Änderungen der Änderungssitzungen vorgenommen wurden, widerspiegeln) und berichtet Unterschiede. Temp-Raum Wie viel Temp-Raum ist diese Sitzung mit Hash-Joins, Sorten usw. Kopieren von William Robertson 2011 Abonnieren von Artikeln Abonnieren Sie Code und ScriptsSQL Correlated Subquery Summary. In diesem Tutorial lernen Sie die SQL-korrelierte Unterabfrage. Das ist eine Unterabfrage, die von der äußeren Abfrage abhängt. Dieses Tutorial erfordert gute Kenntnisse der Unterabfrage. Wenn du nichts über die Unterabfrage weißt. Check it out die Unterabfrage Tutorial, bevor Sie vorwärts mit diesem Tutorial. Einführung in SQL korrelierte Unterabfrage Eine korrelierte Unterabfrage ist eine Unterabfrage, die von der äußeren Abfrage abhängt. Es bedeutet, dass die WHERE-Klausel der korrelierten Unterabfrage die Daten der äußeren Abfrage verwendet. Der Hauptunterschied zwischen einer korrelierten Unterabfrage und einer nicht korrelierten Unterabfrage besteht darin, dass Sie eine korrelierte Unterabfrage nicht alleine wie eine nicht korrelierte Unterabfrage ausführen können. Zusätzlich führt eine korrelierte Unterabfrage einmal für jede ausgewählte Zeile aus der äußeren Abfrage aus. Eine korrelierte Unterabfrage wird auch als Wiederholungsunterabfrage oder synchronisierte Unterabfrage bezeichnet. SQL korrelierte Unterabfrage Beispiele Lets einen Blick auf einige Beispiele, um die Idee der korrelierten Unterabfrage zu verstehen. SQL korrelierte Unterabfrage in der SELECT-Klausel Beispiel Die folgende Abfrage wählt Top fünf Kunden nach Verkäufen: Wie man SQL Server Leistung analysiert 24. Februar 2014 So haben Sie diese SQL Server-Datenbank, die Ihre Anwendung verwendet und es irgendwie scheint langsam zu sein. Wie beurteilen Sie dieses Problem Wo schauen Sie, was Sie messen Ich hoffe, dieser Artikel wird genug Fragen beantworten, um Ihnen den Start zu bringen, damit Sie die Engpässe selbst identifizieren können oder wissen, was Sie suchen, um Ihr Arsenal und Wissen weiter zu erweitern. Wie funktioniert SQL Server Um in der Lage sein, Leistungsprobleme zu beheben, müssen Sie ein Verständnis dafür haben, wie SQL Server funktioniert. Die grob vereinfachte Erklärung ist, dass SQL Server Ihre Abfragen wie folgt ausführt: Die Anwendung sendet eine Anforderung an den Server, die einen gespeicherten Prozedurnamen oder einige T-SQL-Anweisungen enthält. Die Anforderung wird in eine Warteschlange innerhalb des SQL Server-Speichers platziert. Ein freier Thread aus SQL Server8217s eigenen Thread-Pool nimmt die Anfrage auf, kompiliert sie und führt sie aus. Die Anforderung wird nach Anweisung sequentiell ausgegeben. Eine Anweisung in einer Anfrage muss beenden, bevor die nächste beginnt, immer. Gespeicherte Prozeduren führen die gleiche Weise aus, Anweisung nach Anweisung. Aussagen können Daten lesen oder ändern. Alle Daten werden aus dem In-Memory-Cache der Datenbank (der Pufferpool) gelesen. Wenn sich Daten nicht in diesem Cache befinden, muss sie vom Datenträger in den Cache gelesen werden. Alle Updates werden in das Datenbankprotokoll und in den In-Memory-Cache (in den Pufferpool) geschrieben, entsprechend dem Write-Ahead Logging Protokoll. Die Datenverriegelung sorgt für Korrektheit unter Parallelität. Wenn alle Anweisungen in der Anfrage ausgeführt wurden, ist der Thread frei, eine weitere Anfrage abzurufen und auszuführen. Eine Anforderung ist entweder ausgeführt oder wartet Dies mag trivial klingen, aber das Verständnis, dass die Anforderung entweder ausgeführt wird oder auf etwas wartet (ist suspendiert) ist der Schlüssel zur Fehlerbehebung bei der SQL Server-Leistung. Wenn Anfragen, die an SQL Server gesendet werden, eine lange Zeit dauern, um Ergebnisse zurückzugeben, dann nehmen sie entweder eine lange Zeit ausführen oder sie nehmen eine lange Zeit warten. Zu wissen, ob ein Fall oder der andere ist entscheidend, um herauszufinden, die Performance-Engpässe. Darüber hinaus, wenn die Anfragen eine lange Zeit warten, können wir weiter graben und herausfinden, worauf sie warten und wie lange. Verstehen von Anfragen Warten Warteaufträge haben Warte-Info-Daten Wenn eine Anforderung aus irgendeinem Grund gesperrt wird, sammelt SQL Server Informationen darüber, warum es ausgesetzt wurde und wie lange. Im internen SQL Server-Code gibt es einfach keine Möglichkeit, die Funktion aufzurufen, die eine Aufgabe unterbricht, ohne die erforderlichen Warte-Info-Daten zu übermitteln. Und diese Daten werden dann gesammelt und für Sie in vielerlei Hinsicht zur Verfügung gestellt. Diese Warte-Info-Daten sind entscheidend für die Ermittlung von Leistungsproblemen: Eine Anforderung, die derzeit ausgeführt wird, wird abgesperrt, jetzt können Sie sehen, worauf es wartet und wie lange schon gewartet hat. Eine Anforderung, die derzeit ausgeführt wird, die gerade ausgeführt wird, können Sie sehen, was das letzte ist, worauf es wartete. Sie können verstehen, wenn Anfragen auf andere Anfragen warten. Sie können Aggregate für das, was die meisten wartet (beschäftigt) Ressourcen auf dem gesamten Server zu bekommen. Sie können verstehen, welche physischen (Hardware-) Ressourcen gesättigt sind und verursachen Leistungsprobleme. Warte Info für die Ausführung von Anfragen Für jede Anforderung, die auf SQL Server ausgeführt wird, befindet sich eine Zeile in sys. dmexecrequests. Abfragen dieses DMV, zu jeder Zeit, gibt Ihnen einen schnellen Blick auf alles, was rechts ausgeführt wird dann. Der Waittyp Waittime und lastwaittype Spalten geben Ihnen ein sofortiges Gefühl von was ist 8216runnig8217 vs was ist 8216waiting8217 und was wird gewartet: Anforderung auf Session 53 ist ein SELECT und wartet derzeit. Es wartet auf ein Schloss. Session 54 ist ein INSERT und wartet derzeit. Es wartet auf eine Seitenverriegelung. Session 55 ist ein INSERT und läuft derzeit. Vorheriges Warten war eine Seitenverriegelung. Session 56 ist ein INSERT und wartet derzeit Es wartet auf das Datenbankprotokoll zu spülen (ist eine Transaktion begehen). Session 57 ist ein INSERT und wartet derzeit. Es wartet darauf, dass das Datenbankprotokoll spült. Beachten Sie, dass die Anforderung für Session 54 einen Status des Laufens hat, aber es wartet eigentlich. Dies ist, weil Latch-Wartezeiten erwartet werden, kurz zu sein. Die blockingsessionid erzählt uns auch, für einige der wartenden Aufgaben, welche andere Anforderung die aktuell verwaltete Ressource hält. Session 53 wartet auf Session 56 für eine KEY-Lock-Ressource, was bedeutet, dass die SELECT versucht, eine Zeile zu lesen, die durch das INSERT gesperrt ist. Der SELECT wird nicht fortgesetzt, bis der INSERT sich verpflichtet hat. Session 54 wartet auf Session 55 für einen Seiten-Latch, was bedeutet, dass die Session 55 INSERT diese Daten-Seite gerade jetzt ändert und die Daten auf der Seite instabil sind und nicht gelesen werden dürfen. Die Sessions 56 und 57 warten, aber es gibt keine andere Session, die sie blockiert. Sie warten auf das Protokoll auf 8216flush8217, was bedeutet, dass sie sicherstellen müssen, dass der Commit-Log-Datensatz für ihre Transaktion dauerhaft auf Festplatte geschrieben wurde. Sie werden nicht fortgesetzt, bis der Plattencontroller erkennt, dass das Protokoll geschrieben wurde. Hier ist ein weiteres Beispiel: Anforderung auf Session 53 ist ein COMMIT und wartet derzeit. Es wartet darauf, dass das Datenbankprotokoll spült. Session 54 ist ein SELECT und wartet derzeit. Es wartet auf Schloss. Session 55 ist ein SELECT und wartet derzeit. Es wartet auf Schloss. Session 56 ist ein SELECT und wartet derzeit. Es wartet auf Schloss. Session 57 ist ein SELECT und wartet derzeit Es wartet auf Schloss. Beachten Sie, dass in diesem Beispiel jede Anfrage tatsächlich gesperrt ist. In diesem Moment macht der Server grundsätzlich nichts. Wir haben 4 Sessions (54, 55, 56 und 57), die auf die gleiche Sperre warten (die Zeile mit der Schlüsselverriegelungsressource KEY: 5: 72057594039369728 (ac11a2bc89a9)). Session 54 wartet auf Session 55 und Session 55 wartet auf Session 53, was bedeutet, dass Session 54 auf Session 53 wartet. Also wartet jeder auf Session 53, der auf den Plattencontroller wartet, um einen Log-Datensatz auf der Festplatte zu schreiben. Wir können auch sehen, aus der Warte-Spalte, wie lange hat jede Sitzung gewartet: etwa 160 Millisekunden. Beachten Sie, wie im vorherigen Beispiel hatten wir nur eine Anforderung tatsächlich läuft aus 5. I8217m läuft diese Abfragen auf einer Workstation mit 8 Kerne, viel RAM und eine anständige Festplatte, so gibt es jede Menge Hardware, um diese Anfragen zu tragen, aber stattdessen Sind die meisten der Zeiten warten statt auszuführen. Um Ihre Anfragen schneller zu machen, müssen Sie sie ausführen, anstatt zu warten, dass die meisten (oder sogar alle) Anfragen warten, anstatt zu vervollständigen, etwas Ungewöhnliches Nicht einmal, das ist die Norm Jedes Mal, wenn Sie einen SQL Server ausführen werden Eine noch moderate Ladung, Sie sehen, dass die meisten Anfragen warten und nur wenige ausgeführt werden. Was Sie brauchen, um auf eine lange Wartezeit oder kurze, aber wiederholte Wartezeiten, die sich addieren aufpassen. Lange Wartezeiten zeigen eine Ressource an, die für lange Zeit gehalten wird, und normalerweise tritt dies bei Schlössern auf. Wiederholte kurze Wartezeiten geben eine Ressource an, die gesättigt ist, möglicherweise ein Hotspot für die Leistung. Bevor ich weiter gehe, möchte ich nur sys. dmoswaitingtasks zeigen. Das ist ein SQL Server DMV speziell entwickelt, um derzeit wartende Aufgaben zu zeigen: Session 53 wartet das Protokoll zu spülen Session 57 wartet auf 40 ms für eine Sperre auf einer Zeile und wird durch Session 53 gesperrt (die also die Sperre besitzen muss) Session 54 wartet auf 37 ms für eine Sperre auf einer Reihe und wird durch Session 57 gesperrt (aber Session 57 wird wiederum durch Session 53 gesperrt) Die Situation, die wir hier haben, ähnelt dem vorherigen, wo wir 4 SELECT-Sessions hatten Blockiert durch ein INSERT. Er kann hier zwei Anträge sehen, die durch den Versuch, eine Zeile zu lesen, zu sehen (so dass sie wahrscheinlich SELECT) und sie blockieren Sitzung wartet auf seine Transaktion dauerhaft zu begehen. Die Informationen in diesem DMV sind sehr ähnlich zu denen in sys. dmexecrequests und die spätere hat mehr Informationen, aber es gibt einen wichtigen Fall, wo die Informationen in sys. dmexecrequests irreführend ist: parallele Abfragen. Um CXPACKET-Warte-Typen zu verstehen, musst du in parallele Parallel-Aufgaben schauen Wenn eine Anweisung von einer parallelen Ausführung profitieren kann, erzeugt die Engine mehrere Aufgaben für die Anforderung, die jeweils eine Teilmenge der Daten verarbeiten. Jede dieser Aufgaben kann auf einem separaten CPUcore ausgeführt werden. Die Anforderung kommuniziert mit diesen Aufgaben im Grunde eine Produzent-Verbraucher-Warteschlange. Der Abfrage-Operator, der diese Warteschlange implementiert, wird als Exchange-Operator bezeichnet (I8217m, der sich hier wirklich vereinfacht, liest den Parallelitätsoperator (aka Exchange) für eine genauere Beschreibung). Wenn diese Produzent-Verbraucher-Warteschlange leer ist (was bedeutet, dass die Produzenten keine Daten hineinschieben), muss der Verbraucher aussetzen und warten und der entsprechende Warte-Typ ist der CXPACKET-Warte-Typ. Anfragen, die diesen Warte-Typ anzeigen, zeigen wirklich, dass die Aufgaben, die Daten zum Konsum gebracht haben sollten, keine (oder genügend) Daten erzeugen. Diese Produzenten Aufgaben wiederum können suspendiert werden, warten auf einige andere Warte-Typ und das ist, was ist blockiert Ihre Anfrage, nicht der Börsenbetreiber. Hier ist ein Beispiel: Hier sehen wir, dass die Anforderung auf Session 54 3 Tasks wartet und während der Request-Waittyp CXPACKET anzeigt. Eine der parallelen Kinderaufgaben wartet eigentlich auf eine Seitensperre. Aggregierte Warte-Statistiken: sys. dmoswaitstats SQL Server aggregiert Statistiken über alle Warte-Typen und stellt sie in einem sys. dmoswaitstats. Wenn man sich die aktuell ausgeführten Anfragen ansieht und die Warteaufgaben uns zeigten, was in jedem Augenblick gewartet wurde, geben die Aggregatstats einen kumulativen Status seit dem Start des Servers. Abfragen dieses DMV ist einfach, aber das Interpretieren der Ergebnisse ist ein bisschen schwieriger: Erstens, was8217s der Deal mit all den Warte-Typen, die die lion8217s teilen an der Spitze des Ergebnisses DIRTYPAGEPOOL. REQUESTFORDEADLOCKSEARCH. LAZYWRITERSLEEP. Wir haben keine Anforderung gesehen, die jemals auf diese warten. Hier ist der Grund: Durch Hinzufügen der WHERE Sessionid 50 haben wir Hintergrundaufgaben, Threads intern für SQL Server, die mit verschiedenen Wartungsaufgaben beauftragt sind, gefiltert. Viele dieser Hintergrundaufgaben folgen einem Muster wie 8220warr für ein Ereignis, wenn das Ereignis signalisiert wird, etwas Arbeit zu machen, dann warten Sie wieder8221, während andere in einem Schlafzyklusmuster gemustert sind (8220sleep für 5 Sekunden, aufwachen und etwas tun, gehen Sie zurück Zu schlafen 5 Sekunden8221). Da keine Aufgabe sich aussetzen kann, ohne einen Warte-Typ zu geben, gibt es Warte-Typen zu erfassen, wann diese Hintergrund-Tasks sich selbst aussetzen. Da diese Muster der Ausführung dazu führen, dass die Task fast alle Zeiten ausgesetzt wird, trumpft die aggregierte Wartezeit für diese Wartearten oft jeden anderen Warte-Typ. Die SQL Server-Community kam, um diese Warte-Typen zu benennen 8216benign wait types8217 und die meisten Experten haben eine Liste von Warte-Typen, die sie einfach herausfiltern, zum Beispiel sehen Filtern von gutartigen Warten: Jetzt haben wir ein kohärentes Bild der Wartezeiten. Das Bild sagt uns, welche Wartearten am häufigsten auf der SQL Server-Instanz am häufigsten sind. Dies kann ein wichtiger Schritt zur Identifizierung einer Engpassursache sein. Darüber hinaus ist die Interpretation der Daten Daten subjektiv. Ist der aggregierte WRITELOG-Wert von 4636805 Millisekunden gut oder schlecht, ich weiß nicht, ist ein Aggregat, angesammelt, da dieser Prozess läuft, wird also offensichtlich angrenzen. Vielleicht hatte es Werte aus Perioden akkumuliert, als der Server glatt ging und aus Perioden, in denen es schlecht war. Allerdings weiß ich jetzt, dass von allen 8216non bening8217 Warte-Typen, WRITELOG ist die mit der höchsten totalen Wartezeit. Ich weiß auch, dass Maxwaittime für diese Warte-Typen, so dass ich weiß, dass es mindestens ein Mal, wenn eine Aufgabe musste 1,4 Sekunden warten, bis das Protokoll zu spülen. Die Waittaskcount erzählt mir, wie viele Zeit eine Aufgabe wartete auf eine bestimmte Warte-Typ, und das Teilen von Waittimemswaittaskcount wird mir sagen, die durchschnittliche Zeit eine bestimmte Warte-Typ wurde auf gewartet. Und ich sehe mehrere verriegelte Wartearten oben: LCKMS. LCKMIS und LCKMIX. Ich kann schon einige Dinge über das Gesamtverhalten des Servers erzählen, aus einer Leistungsperspektive: das Spülen des Protokolls ist die am meisten gewartete Ressource auf dem Server, und die Sperrkonferenz scheint das nächste Hauptproblem zu sein. CXPACKET-Warte-Typen sind gleich dort oben, aber wir wissen, dass die meisten Male CXPACKET-Warte-Typen nur für andere Warte-Typen surrogieren, wie oben diskutiert. Wie wäre es mit den anderen mit Typen in der Liste, wie SOSSCHEDULERYIELD. PAGELATONSH oder PAGEIOLATCHEXSHUP Häufige Wartearten Um Wartearten und Wartezeiten zu untersuchen, entweder aggregieren oder für eine einzelne Abfrage, muss man verstehen, was die Warte Typnamen bedeuten. Viele der Namen sind selbstbeschreibend, manche sind kryptisch in ihrem Namen und einige sind richtig richtig betrügerisch benannt. Ich denke, die beste Ressource, um eine detaillierte Beschreibung der Namen zu lesen ist die Waits and Queues White Paper. Es hat eine alphabetische Liste von Wartearten, Beschreibung und welche physikalische Ressource korreliert ist. Das weiße Papier ist ein bisschen alt (es deckt SQL Server 2005) aber immer noch sehr relevant. The wait type names for important resources has not changed since SQL server 2005, what you8217ll be missing will be newer wait type names related to post SQL Server 2005 features. The wait types are briefly documented also in the sys. dmoswaittypes topic on MSDN. Disk and IO related wait types PAGEIOLATCH This is the quintessential IO, data read from disk and write to disk wait type. A task blocked on these wait type is waiting for data to be transferred between the disk and the in-memory data cache (the buffer pool). A system that has high aggregate PAGEIOLATCH wait types is very likely memory starved and is spending much time reading data from the disk into the buffer pool. Be careful not to confuse this wait type with the PAGELATCH wait type (not the missing IO in the name). WRITELOG This wait type occurs when a task is issuing a COMMIT and it waits for the log to write the transaction commit log record to disk. High average wait times on this wait type indicate that the disk is writing the log slowly and this slows down every transaction. Very frequent waits on this wait type are indicative of doing many small transaction and having to block frequently to wait for COMMIT (remember that all data writes require a transaction and one is implicitly created for each statement if no BEGIN TRANSACTION is explicitly used). IOCOMPLETION This wait type occurs for tasks that are waiting for something else that ordinary data IO. Eg. loading an assembly DLL, reading and writing TEMPDB sort files. some special DBCC data reading operations. ASYNCIOCOMPLETION This wait type is mostly associated with backup, restore, database and database file operations. If your wait time analysis finds that IO and disk are important wait types then your should focus on analyzing disk activity . Memory related wait types RESOURCESEMAPHORE This wait type indicate queries that are waiting for a memory grant. See Understanding SQL Server memory grant. OLTP type workload queries should not require memory grants. if you see this wait type on an OLTP system then you need to revisit your application design. OLAP workloads often are in need of (sometimes large) memory grants and large wait times on this wait type usually point toward RAM increase upgrades. SOSVIRTUALMEMORYLOW Are you still on 32 bit systems Move on Network related wait types ASYNCNETWORKIO This wait type indicate that the SQL Server has result sets to send to the application but the application odes not process them. This may indicate a slow network connection. But more often the problem is with the application code, it is either blocking while processing the result set or is requesting a huge result set. CPU, Contention and Concurrency related wait types LCK Locks. All wait types that start with LCK indicate a task suspended waiting for a lock. LCKMS wait types indicate a task that is waiting to read data (shared locks) and is blocked by another task that had modified the data (had acquired an exclusive LCKMX lock). LCKMSCH wait types indicate object schema modification locks and indicate that access to an object (eg. a table) is blocked by another task that did a DDL modfication to that object ( ALTER ). PAGELATCH Do not confuse this wait types with the PAGEIOLATCH wait types. High wait times on these wait types indicate a hot spot in the database, a region of the data that is very frequently updated (eg. it could be a single record in a table that is constantly modified). To further analyze, I recommend the Diagnosing and Resolving Latch Contention on SQL Server white paper. LATCH These wait types indicate contention on internal SQL Server resources, not on data (unlike PAGELATCH they do not indicate a hot spot in the data). To investigate these waits you need to dig further using the sys. dmoslatchstats DMV which will detail the wait times by latch type. Again, the best resource is Diagnosing and Resolving Latch Contention on SQL Server white paper. CMEMTHREAD This wait type occurs when tasks block on waiting to access a shared memory allocator. I placed thi here, in concurrency section, and not in 8216memory8217 section because the problem is related to internal SQL Server concurrency. If you see high CMEMTHREAD wait types you should make sure you are at the latest SQL Server available Service Pack and Cumulative Update for your version, because some of these kind of problems denote internal SQL Server issues and are often addressed in newer releases. SOSSCHEDULERYIELD This wait type can indicate spinlock contention. Spinlocks are extremely light wait locking primitives in SQL Server used for protecting access to resources that can be modified within few CPU instructions. SQL Server tasks acquire spinlocks by doing interlocked CPU operations in a tight loop, so contention on spinlocks burns a lot of CPU time (CPU usage counters show 90-100 usage, but progress is slow). Further analysis need to be done using sys. dmosspinlockstats : RESOURCESEMAPHOREQUERYCOMPILE This wait type indicate that a task is waiting to compile its request. High wait times of this type indicate that query compilation is a bottleneck. For more details I recommend reading Troubleshooting Plan Cache Issues. SQLCLRQUANTUMPUNISHMENT This wait type occurs if you run CLR code inside the SQL Server engine and your CLR code does not yield the CPU in time. This results in an throttling of the CLR code (8220punishment8221). If you have CLR code that can potentially hijack the CPU for a long time you must call Thread. BeginThreadAffinity() . For more details I recommend watching Data, Faster: Microsoft SQL Server Performance Techniques with SQLCLR. Special wait types TRACEWRITE This wait type indicate that tasks are being blocked by SQL Profiler. This wait type occurs only if you have SQL Profiler attached to the server. This wait type occur often during investigation if you had set up a SQL Profiler trace that is too aggressive (gets too many events). PREEMPTIVEOSWRITEFILEGATHER This wait type occurs, among other things, when auto-growth of files is triggered. Auto-growth occurs when a insufficiently sized file is grown by SQL Server and is a hugely expensive event. During growth event all activity on the database is frozen. Data file growth can be made fast by enabling instant file growth, see Database File Initialization. But log growth cannot benefit from instant file initialization so log growth is always slow, sometimes very slow. Log Auto-Growth events can be diagnosed by simply looking at the Log Growths performance counter in the SQL Server, Databases Object. if is anything but 0 it means log auto-growth had occurred at least once. Real-time monitoring can be done by watching for Data File Auto Grow Event Class and Log File Auto Grow Event Class events in SQL Profiler. I did not cover many wait types here (eg. CLR . SQLCLR . SOSHOST . HADR . DTC and many more). If you encounter a wait type you do not understand, usually just searching online for its name will reveal information about what that wait type is what potential bottleneck it indicates, if any. Analyze disk activity: IO stats SQL Server needs to read and write to the disk. All writes (inserts, updates, deletes) must be written to disk. Queries always return data from the in-memory cache (the buffer pool) but the cache may not contain the desired data and has to be read from disk. Understanding if the IO is a bottleneck for your performance is a necessary step in any performance investigation. SQL Server collects, aggregates and exposes information about every data and log IO request. First thing I like to look at is sys. dmiovirtualfilestats . This DMV exposes the number of writes and reads on each file for every database in the system, along with the aggregated read and write IO 8216stall8217 times. Stall times are the total time tasks had to block waiting for transfer of data to and from disk. The per file IO stats are aggregated since server start up but be aware that they reset for each file if it ever goes offline. The total number of bytes transferred (reads and writes) is a good indicator of how busy is a database from IO point of view. The stalls indicate which IO subsytem (which disk) is busy and may even be saturated. Analyzing individual query execution When analyzing an individual statement or query for performance allows us to understand what the query is doing, how much is executing vs. what is waiting on. The most simple analysis is to use the execution statistics. These work by first running the appropriate SET STATISTICS. ON in your session and then executing the statement or query you want to analyze. In addition to the usual result, the query will also report execution statistics, as follows: SET STATISTICS TIME ON The query parse time, compile time and execution time are reported. If you execute a batch or a stored procedure with multiple statements then you get back the time for each statement, which helps determining which statement in a procedure is the expensive one. But we8217ll see that analyzing the execution plan reveals the same information, and more. SET STATISTICS IO ON The IO statistics show, for each statement, the number of IO operations. The result shows several metrics for each table touched by the statement : scan count Number of times the scans or seeks were started on the table. Ideally each table should be scanned at most once. logical reads Number of data pages from which rows were read from the in-memory cache (the buffer pool). physical reads Number of data pages from which data was had to be transferred from disk in-memory cache (the buffer pool) and the task had to block and wait for the transfer to finish. read-ahead reads Number of data pages that were asynchronously transferred from disk into the buffer pool opportunistically and the task did not had to wait for the transfer. LOB logicalphysicalread-ahead reads Same as their non-lob counterparts, but referring to reading of large columns (LOBs). There is also the SET STATISTICS PROFILE ON . which provides a detailed execution information for a statement, but much of the information overlaps with the execution plan info and I think analyzing the execution plan is easier and yields better information. Logical Reads are the best measure of a statement cost. A large logical reads value for a statement indicates an expensive statement. For OLTP environments the number of logical reads should be in single digits. Large logical reads count translate to long execution times and high CPU consumption even under ideally conditions. Under less ideal conditions some or all of these logical reads turn into physical reads which mean long wait times waiting for disk transfer of data into the buffer pool. Large logical reads are indicative of large data scans, usually entire table end-to-end scan. They are most times caused by missing indexes. In OLAP, decision and DW environments, scans and large logical reads may be unavoidable as indexing for an OLAP workload is tricky, and indexing for an ad-hoc analytic workload is basically impossible. For these later cases the answer usually relies in using DW specific technologies like columnstores . Information about query execution statistics can also be obtained using SQL Profiler. The SQL:StmtCompleted . SQL:BatchCompleted , SP:StmtCompleted and RPC:Completed capture the number of page reads, the number of page writes and the execution time of an individual invocation of a statement, a stored procedure or a SQL batch. The number of reads is logical reads. Note that monitoring with SQL Profiler for individual statement completed events will have effect on performance and on a big busy server it can cause significant performance degradation. You can also see the number of logical reads, reads and writes as it happens by looking at the sys. dmexecrequests but this information is visible only if while a request is running. Furthermore the number aggregate all statements in a request so you cannot determine the cost of an individual statement using this DMV. Analyzing individual query execution wait times If you need to drill down more detail that execution time and IO statistics for a query or for a stored procedure, the best information is the wait types and wait times that occurred during execution. Obtaining this information is no longer straightforward though, it involves using Extended Events monitoring. The method I8217m presenting here is based on the Debugging slow response times in SQL Server 2008 article. It involves creating an extended event session that captures the sqlos. waitinfo info and filter the extended events session for a specific execution session (SPID): With the Extended Events session created and started, you can now run the query or procedure you want to analyze. After that, stop the Extended Event session and look at the captured data: As you can see the Extended Events session captured each individual wait, each incident when the session was suspended. If we click on an individual captured event we see the event data XML: This is a 4 millisecond wait for an IX mode lock. Another 2 milliseconds had spent until the task resumed, after the lock was granted. You can shred out the XML into columns for a better view: Finally, we can aggregate all the data in the captured Extended Events session: This is an exceptional wealth of information about what happened during the execution of my request. 742 times it waited on the log commit to be written to disk for a total of 14 seconds wait time, 12 times it waited for a lock, 2 times for a page latch etc. If your curious, my workload I captured was a single row INSERT in a loop. Analyzing Execution Plans SQL Server can expose the actual execution plan for queries. The actual query tree gets serialized as XML and there are several ways to obtain it: SET SHOWPLANXML ON in the session you execute your query. Use the SQL Profiler, see Displaying Execution Plans by Using SQL Server Profiler Event Classes . Enable the Include Actual Execution Plan in SSMS, see Displaying Graphical Execution Plans . Use sys. dmexecqueryplan function to obtain the plan XML from a plan handle. The plan handle can be obtained from DMVs like sys. dmexecrequests (currently executing queries) or sys. dmexecquerystats (past executed queries). If you dont know anything about execution plans then focus on thick arrows I will not enter into much detail into how to analyze a query plan because the subject is way too vast for this article. Execution plans capture what the query did . namely what operators it executed, how many times was each operator invoked, how much data did each operator process and how much data it produced in turn, and how much actual time it took for each operation. Unlike the wait times analysis, analyzing the execution focuses on runtime and where was the actual CPU time consumed on. The graphical display of SSMS simplifies the information in an execution plan XML to make it more easily consumable and the graphical representation makes easy to stop the bottlenecks. Thick arrows represent large data transfers between operators, meaning that operators had to look at a lot of rows to produce their output. Needless to say, the more data an operator needs to process, the more time it takes. Hovering over operators display a popup info note with details about the operator. A large actual row count shows large amount of data being processed. The popup include also the estimated row count which is how much data SQL Server had predicted the operator will process, based on the table statistics. A wild discrepancy between actual and estimated often is responsible for bad performance and can be tracked to outdated statistics on the table. Another are of interest in the execution plan are the operators themselves. Certain operators are inherently expensive, for example sort operators. An expensive operator coupled with a large data input is usually the cause of a huge execution time. Even if you do no know which operators are expensive and which are not, the execution plan contains the cost associated with each operator and you can use this information to get an idea for where problems may be. Identifying problem queries SQL Server exposes the runtime execution statistics for most queries it had run in sys. dmexecquerystats . This DMV shows the number of times a query had run, totalmaxminlast active CPU time (worker time), totalmaxminlast elapsed wall-clock time, totalmaxminlast logical reads and logical writes, totalmaxminlast number of rows returned. This kind of information is a real treasure cove to identify problems in an application: Large execution count Queries that are run frequently are the most sensitive for performance. Even if they perform acceptable, if they are run often then small improvements in each individual execution can yield overall significant speed. But even better is to look, with a critical eye, into why is the query executed often. Application changes can reduce the number of execution count. Large logical reads A query that has to scan large amounts of data is going to be a slow query, there is no question about this. The focus should be in reducing the amount of data scanned, typically the problem is a missing index. A bad query plan can also be a problem. A high number of logical reads is accompanied by a high worker time but the issue is not high CPU usage, but the focus should be on large logical reads. Large physical reads This is basically the same problem as large logical reads, but it also indicates that your server does not have sufficient RAM. Luckily this is, by far, the easiest problem to solve: just buy more RAM, max out the server RAM slots. Youre still going to have a large logical reads, and you should address that problem too, but addressing an application design issue is seldom as easy as ordering 1TB of RAM. High worker time with low logical reads This case is not very common and indicates an operation that is very CPU intensive even with little data to process. String processing and XML processing are typical culprits and you should look at moving the CPU burn toward the periphery, in other words perform the CPU intensive operations on the client, not on the server. High elapsed time with log worker time A query that takes a long wall-clock time but utilizes few CPU cycles is indicative of blocking. This is a query that was suspended most of the time, waiting on something. Wait analysis should indicate the contention cause, the resource being waited for. High total rows count Large result sets requested by the application can be justified in an extremely low number of cases. This problem must be addressed in the application design. Finding a problem query using sys. dmexecquerystats becomes a simply exercise of ordering by the desired criteria (by execution count to find frequently run queries, by logical reads, by elapsed time etc). Tho get the text of the query in question use sys. dmexecsqltext . and to get the query execution plan use sys. dmexecqueryplan : If you do not know what kind of problem query to look for to start with, my advice is to focus in this order: High execution count. Identifying what queries are run most often is in my opinion more important that identifying which queries are particularly slow. More often than not the queries found to be run most often are a surprise and simply limiting the execution count yield significant performance improvements. High logical reads. Large data scans are the usual culprit for most performance problems. They may be caused by missing indices, by a poorly designed data model, by bad plans caused by outdated statistics or parameter sniffing and other causes. High elapsed time with low worker time. Blocking queries do not tax the server much, but your application users do not care if the time they waited in front of the screen for a refresh was spent active or blocked. Missing Indexes Repeatedly I said that performance problems are caused by missing indexes, and SQL Server has a feature aptly named Missing Indexes : The missing indexes feature uses dynamic management objects and Showplan to provide information about missing indexes that could enhance SQL Server query performance. Personally I seldom focus my analysis on the built in missing index feature. To start with the feature has known limitations. but more importantly is that the feature is starting with the solution, wo first searching for a cause. I always start with identifying problem queries first, searching for ones which would yield the highest bang for the buck. Once a problem query is identified as causing large scans, the missing index DMVs can be consulted to identify a candidate. But you should always take the recommendations with a grain of salt. The missing index feature does not know what the query is doing from an application business logic point of view. Reviewing the query can potentially find a better solution than what the automated missing index feature may recommend. Experienced administrators have in place automation that periodically inspects the missing index DMVs and find potential hot spots and problems. This is a very good proactive approach. But in most cases the performance problems are hot and immediate, and finding them using wait analysis or query execution stats is a more direct process. For a more in depth discussion of missing indexes DMVs I recommend spBlitzIndex . TEMPDB Performance tempdb is the special SQL Server database that is used to store any transient data, including things like temporary tables, static cursors, table variables, sort runs, internal worktables, the version store and piles more. Even though the problems that can occur with tempdb are ultimately CPU, IO or contention problems, tempdb deserves a special topic because some of these problems can be understood at a higher level as tempdb problems, as opposed to, say, low level latch contention problems. tempdb also tends to manifest a set of problems entirely of its own, specially due to the very high rate of creation and destruction of objects in tempdb. MSDN has an Optimizing tempdb Performance topic which contains some practical advice, and Cindy Gross has compiled a collection of SQL Server TempDB IO Best Practices . Make each tempdb data file the same size In general I tried to emphasize the importance of measuring and understanding SQL Server performance characteristics instead of blindly following advice. But here is one piece of advice that I always give, wo any secondary consideration: make sure tempdb is stored on files of identical size, with identical growth characteristics. Ive spent just too many hours chasing difficult to diagnose issues to discover that theyre ultimately caused by unaligned tempdb files. I would also recommend as mandatory reading Martin Smiths answer to this dba. stackexchange question:Whats the difference between a temp table and table variable in SQL Server. The answer goes in great detail not only from a tempdb usage point of view, but also impact on locking, compilation, parallelism and indexability, all critical components of performance. SQL Server Performance Counters Performance counters offer a different perspective into SQL Server performance than the wait analysis and execution DMVs do. The biggest advantage of performance counters is that they are extremely cheap to collect and store so they lend themselves as the natural choice for long term monitoring. Looking at current performance counter numbers can reveal potential problems, but interpreting the values is subject to experience and there are a lot of lore and myths on certain magical values. For a analyzing performance problems on a server that you have access and is misbehaving right now . a more direct approach using wait analysis and execution stats is my recommended approach. Where performance counters shine is comparing current behavior with past behavior. for which, obviously, you need to have performance counters values collected and saved from said past. These should be part of an established baseline, which has captured the server behavior under workload when the server was behaving correctly and was not experiencing problems. If you do no have a baseline, consider establishing one. The performance counters infrastructure workhorse is the logman. exe utility. For a quick intro, read Two Minute Drill: LOGMAN. EXE. I recommend you familiarize yourself with this tool. I favor it over the graphic interface ( perfmon. exe ) because, as a command line interface (CLI) tool, it allows for easy scripting. Telling someone run the attached script is always easier that telling click the button, select A from the list, then choose menu B, click twice in the middle of the red square etc etc. The graphic perfmon. exe tool is the tool of choice for displaying performance counters. Performance counters, if collected at sufficient resolution, are also an excellent tool for determining correlation. The graphic display makes easy to spot counters that tend to spike together, or in an anti-correlated pattern (one spikes the other dips). Other patterns also become evident in the graphic visualization, like lag (eg. a counter spike is always preceded by another one increasing). Short of having a data scientist doing factorial analysis on your counters, your own eyes are the best analysis tool. SQL Server usage performance counters Batch Requestssec If you want one counter to best represent the overall performance throughput of your server, this is it. It captures the workload pressure (how many requests the application is making), and how is the server coping with the workload. Trends in this counter need to be further analysed: an increase in the value could mean the server is performing better, because it can accept and handle more requests, but it can also mean the application is sending more requests (bigger, heavier workload), for example more your application is serving more client requests. Transactions The number of active transactions (any type). Be aware that there are at least two counters named Transactions . one in the SQL Server:General Statistics object and another one in the SQL Server:Transactions object. Im referring to the later one. Knowing the number of active transactions is important to set a background on your understanding of the workload. Processes blocked This is specially useful when troubleshooting spikes or jagged performance. For example if you can correlate moments where performance drops with moments when the blocking increase you have found an area to focus your investigation on: blocking. Errorssec Understanding whether errors occur in your workload is a key point in performance troubleshooting. Eliminating errors is an easy path toward performance increase. Number of Deadlockssec Related to the number of Errorssec, monitoring the rate of deadlocks can quickly point toward a performance problem. Is hard to believe that a you are not aware that deadlocks are occurring, given the impact they have on the application. But Ive seen cases where the deadlocks were quietly swallowed in the application or simply there was no communication between the organization monitoring the application and the DBA organization monitoring the server. Log Growths I can hardly think of any event in SQL Server execution that has a more dramatic impact on performance than log growth. During log growth every write request effectively freezes as it waits for available log to generate the write-ahead log records necessary to record any update. Log growth cannot use instant file initialization so it must wait until the log file is grown and the newly allocated disk space is filled with 0s. Your application users might as well watch paint dry. Often cases this is aggravated by a log file gone out of control with 10 increments which is now growing in hundreds of megabytes or even gigabytes at a time. The impact on performance is usually devastating, and is also difficult to track down after the fact. It shows as a sudden performance dip (actually often a complete freeze) that lasts few seconds or even minutes, and then everything is again fast until the next log growth event. The article How to shrink the SQL Server log offers an explanation why log growth can occur. Data and log growth can also be tracked by non-zero values for the PREEMPTIVEOSWRITEFILEGATHER wait info statistics. Data File(s) Size (KB) Monitoring the data size is interesting on its own, but there is another reason why this counter should be collected: it can show when database growth events occur. Unlike Log Growths, data growths do not have counter of their own (shame), but the events can be deduced from sudden jumps in the data file size. Note that data growth can leverage instant file initialization so the impact may not be as dramatic. You should always ensure instant file initialization is enabled for SQL Server, if is not the impact may be serious, specially if you encounter tempdb growth events (which in my experience occurs on most deployments). IO related performance counters Page readssec The rate at which data pages are read from disk. This will be the main driver factor for disk read IO. Normally your workload should read only a very small amount of data from disk, the data should be cached in memory. Constant reading is a sign of data scans (missing indexes) andor insufficient memory (RAM). Log Flushessec Together with the Log Flush Waitssec . Log Flush Wait Time and Log Flush Write Time (ms) you can quickly get a feel of how much blocking occurs due to transactions waiting to commit (log flush events). For well written OLTP intensive applications the rate at which transactions can be committed is often the main performance bottleneck. Page writessec I intentionally put data write after log writes, because most times is the log that is the problem. Only few workloads are data-write heavy. However data-write can interfere with disk IO, specially with log writes, and cause delays in log flush waits that are very visible as application performance drops. The typical pattern is some longer time of good performance with periodic short times of performance dip. Long and short are relative, sometime you see 1-2 minutes long and 10-15 seconds as short, but other times is 10-15 minutes long and 1-2 minutes short. The jagged pattern is the key, with a strong correlation between performance dips and data-writes spikes. The pattern is caused by checkpoints and the timing is driven by the recovery interval option. Looking at Checkpoint pagessec will quickly corroborate this hypothesis. Checkpoint pagessec This counter is the usual culprit to blame Page writessec counter spikes. Log write waits This performance counter exposes the wait info object for log writes. The advantage of using performance counters vs. the DMVs is to leverage the performance counters infrastructure, namely collecting the values in a continuous fashion using the operating system collectors. Page IO latch waits Same as above, this is a wait info exposed through performance counters. Strictly speaking this does not measure IO, it measures only when blocking caused by IO occurs. You cannot distinguish between read IO and write IO with this counter. BackupRestore Throughputsec Often the IO requirements of the maintenance backup operations are forgotten when doing performance analysis. Understanding the additional IO pressure a backup adds and tracking it through this counter is important on its own, but is even more important to understand correlation between spikes in this counter and general performance degradation. This is a scenario Ive see all too often: the maintenance plan kicks in and the overall throughput dips significantly, but nobody understands why. PhysicalDisk Object This category is the operating system counter for monitoring the IO operations. The typical focus is not on throughput, but on queue length, indicating IO operations that take a long time. For a more thorough explanation, read Windows Performance Monitor Disk Counters Explained. Monitoring Usage and Monitoring Queue Length. Operating system exposes IO counters for each individual drive separately, always corroborate findings with the SQL Servers own IO stats as reported by the virtual IO stats DMVs. Another good resource to read is this SQL Server Technet wiki page: Monitoring Disk Usage. ProcessIO Data Operationssec The Process counter category has a different instance for each OS executing process and is very important to analyze when the overall IO on the system does not match the IO as reported by SQL Server. Often times youll discover that another process is causing IO making the disks busy and increasing the response time in SQL Server. Unfortunately monitoring this counter is difficult long term because instances come and go as OS executable processes start and terminate. Pages sec Despite being in the Memory counter category, understanding if and when paging occurs is critical in understand IO performance as paging can introduce a large spike in IO requests. See Monitoring Paging. Memory related performance counters Page life expectancy PLE is measuring the average time a page stays cached in memory before being evicted because of the need to read more data from disk. The DBA lore give this counter a magical value of 300 (5 minutes). This originates with the The 5 Minute Rule for Trading Memory for Disc Accesses paper published back in 1985, then revised as The Five-Minute Rule Ten Years Later and again revised as The Five-Minute Rule 20 Years Later. This 2011 SQL Magazine QA with Paul Randal goes on to say: The value of 300 seconds is a Microsoft recommendation in a white paper that was written five years ago. Its not a relevant guideline nowadays, and even back in 2006 I would have argued that aiming for a set value isnt a valid recommendation. Analyzing PLE is useful as it gives a single value to get a feel for the server memory health, but do not panic if the value is 297 and relax is is 302. More important is to understand trends and the dynamic of this value. A jagged pattern (periodic dips) indicates something that comes and creates a great disturbance in the server memory, perhaps a periodic query that causes a scan, evicting much of the cached data. An overall trend in reducing PLE indicates your workload increases but the server hardware is the same and is a great indicator of potential future problems, better to alleviate now. On machines with NUMA nodes, which in this day and age means pretty much every server, you should pay attention to the PLE per node using the SQL Server:Buffer NodePage life expectancy counter. Read Page Life Expectancy isnt what you think. SQL Server:Memory Manager This category tracks the how is memory used inside the SQL Server process. the absolute values themselves are somehow esoteric, but having a baseline to compare against and looking at long term trends is a great way to determine what changed and what components start putting SQL Server under memory pressure. Memory Grants Pending Im calling out this counter in the Memory Manager category because is particularly important in understanding blocking caused by memory grant waits. Memory grants are only needed for certain query operators and queries requesting large memory grants tend to wreak havoc with your SQL Server performance. Read Understanding SQL server memory grant for more details. Memory grant queue waits Related to the above this is the memory grant wait info exposed as a performance counter. ProcessPrivate Bytes The Process category OS performance counter has an instance for each process in the system and allows you to easily track other processes that may consume memory, causing external memory pressure for SQL Server and ultimately performance degradation. CPU performance counters Processor Object I really hope this one doesnt need an explanation. The OS reported CPU usage counter. Page lookupssec Im mentioning this counter here, in the CPU category, because it is a critical counter to analyze when considering CPU usage in SQL Server. When this counter correlates strongly with CPU usage is a clear indicator of why the CPU usage increased: more pages are scanned, the queries simply look at more data and therefore consume more CPU. The usual culprit is, again, table scans (ie. missing index, poor data modeling or a bad query plan). Process Processor Time The Process category OS performance counter has an instance for each process in the system and allows you to easily track other processes that may consume CPU, causing SQL Server performance degradation. SQL Server blocking related performance counters SQL Server:Locks Monitoring and analyzing the locking performance counters can reveal if lock related blocking is a significant cause of performance problems. Look specially at the Lock Wait Time (ms) value, it will give you an insight on how much percent of the time is spent by queries being blocked waiting for locks. You wont be able to determine exactly what query is blocking what, but knowing that lock waits are significant allows you to focus on analyzing the block chains. spwhoIsActive is a great tool for that. SQL Server:Wait Statistics Most important wait info types are reported also as performance counters. Analyzing these is similar to analyzing the wait statistics using the DMVs, but the performance counters have the advantage of being available in the whole performance counters toolset ecosystem, making easy to collect, store and analyze. Not all wait info types are exposed as performance counters. SQL Server:Latches If you see high Average Latch Wait Time (ms) values you should perform an investigation to find the problem latches. I recommend you read Diagnosing and Resolving Latch Contention on SQL Server. Network performance counters ProTip: Network is not your bottleneck. Network performance comes into picture usually only for deployments using Database Mirroring or Availability Groups. These features are indeed network hungry and require a fast, high bandwidth-low latency connection between the nodes. Furthermore, both DBM and AG tend to push their problems up the stack, resulting in performance degradation when the network traffic cannot keep up with the workload. Network IO waits This counter shows the wait info stats related to the server being blocked because the client is not consuming the result sets. Most often this does no indicate a network problem, but an application design problem: the client is requesting large result sets. Send IO bytessec and Receive IO bytessec These counters show the network traffic generated by Service Broker and Database Mirroring. Availability Groups have a more specialized counter category in SQL Server:Availability Replica . Network Interface This is the performance counter where all the network activity is exposed, from all processes. IP Object. TCP Object These categories are important for monitoring the health of the traffic, not the amount. Specifically they can indicate network errors, dropped packets and retries. Defining your own counters You can actually report custom performance counters from T-SQL. The SQL Server:User Settable is specifically designed for this and allows you to report up the 10 user defined values to be monitored and collected by the system performance counters tools. You publish counter values by using spusercounter1 through spusercounter10 : For application developers is also possible to add performance counters to the application itself. Publishing performance counters from managed () application code is trivially easy, and you can automate the somehow boring process of declaring them, see Using XSLT to generate Performance Counters code . Advanced Performance Analysis Developers are familiar with performance analysis using sample profiling, with tools like VTune. F1 or WPA. The sample profiling technique consist of periodically taking a stack sample of each thread in the profiled process, up to several hundred times per second. The process is non-invasive, it does not require a specially instrumented process, any process can be profiled. It is also fairly lightweight, taking several hundred samples per second has no measurable impact on the performance of the process being sampled. The trick is interpreting the observations. Using the Microsoft public symbols server you can resolve the collected samples to actual function names in the source code (note that in my experience F1 and WPA do a much better job at handling the LTCG code of SQL Server than VTune does). With some common sense, one can make an educated guess what exactly was the SQL Server doing during the period being sampled. This technique makes sense only if you are seeing significant CPU usage. If the performance is bad and you see slow queries but low CPU usage it means the queries are waiting and you should focus instead on wait info analysis. Sample profiling of a blocked process will reveal nothing useful. WPA is one of my favorite performance tools and it can do more than just sample profiling. It can visualize XPerf ETW traces and this makes it extremely powerful. Existing documentation and whitepapers for WPA tends to focus on multimedia and game development, but if you are and adventurous spirit and want to learn about how your favorite database works, using WPA to analyse SQL Server works can reveal a great deal of information and help you spot performance improvements that can be leveraged in your application. One of my favorite use cases for WPA is to collect IO activity (this will collect every single IO request ) and then visualize the Disk Offset as a graph. This is an incredibly simple, yet powerful, way to visualize random IO vs. sequential IO patterns. If you have a feeling WPA and XPerfETW are somehow related to SQL Server Extended Events, that is because they are, very much so. Finally, some readings to get you started on using WPA: The USE method USE stands for Utilization . Saturation and Errors . and the USE Method is a formalized approach to investigating performance troubleshooting. Its focus is on identifying resources in a system and then for each one measure the utilization (percent in use compared to maximum bandwith), the saturation of the resource (extra work queued because the resource cannot service due to 100 utilization) and errors. When used in SQL Server context, the tricky part is to identify the resources and how to measure the utilizationsaturationerrors. Some resources are obvious: disk, CPU, memory, network. Sometimes even simply starting with the USE method and measuring these hardware parameters is a good way to start a performance investigation, at the very least youll know what hardware component is saturated, if any. But knowing how SQL Server works can reveal more detailed, internal resources that can be analyzed. In Understanding how SQL Server executes a query I have shown this image: Many of the components in the image above can be investigated as Resources in the USE method. Applying this methodology requires some thinking outside the box since you will not find much references online about how to apply the USE method to SQL Server, what resources to look for and how to measure the utilization, saturation and errors for each. I myself do not have a clear picture yet for each resource involved and Im still collecting facts and know-how on the subject. But I like the USE approach because it gives a method, a strategy to follow in troubleshooting performance. Comments are closed.
No comments:
Post a Comment