Extreme ASP.NET: SQL Server Profiler und Query Analyzer

Veröffentlicht: 10. Okt 2005

Von Rob Howard

In meiner letzten Kolumne habe ich mich mit dem Microsoft Application Center Test befasst und dessen Verwendung zur Leistungsprüfung bei Webanwendungen beschrieben (siehe Extreme ASP.NET: Tools of the Trade: Application Center Test - Artikel in englischer Sprache). Application Center Test ist eines dieser Tools, deren Nutzen erst dann offensichtlich wird, wenn jemand anders einem die wahren Vorteile des Tools aufzeigt. Ich befasste mich mit dem Nutzen von Application Center Test zur Leistungsmessung. Dies ist ein wichtiger Aspekt, da aufgrund solcher Messungen Verbesserungen durchgeführt, Zielsetzungen festgelegt und natürlich vorrangig Kapazität und Skalierung geplant werden können.

In dieser Kolumne möchte ich zwei weitere fachliche Tools behandeln: SQL Server Profiler und SQL Server Query Analyzer. Da viele ASP.NET-Entwickler nicht mit diesen SQL-orientierten Tools vertraut sind, empfehle ich Ihnen sehr, diese Tools in Ihre Sammlung aufzunehmen. Wenn ich persönlich während meiner Zeit bei Microsoft neben ASP.NET bei einem anderen Produktteam hätte mitarbeiten können, wäre dies das SQL Server-Team gewesen. Intern ist SQL Server eine unglaublich komplexe Software, und dennoch sind Programmierung und Verwendung dieser Software sehr einfach. Es ist wichtig, einen Teil dieser Komplexität zu verstehen, da die Datenbank - wie Sie vielleicht selbst wissen - oft einen Engpass Ihrer Anwendung bildet, und je umfassender Sie mit den Interna von SQL Server vertraut sind, desto mehr Möglichkeiten stehen Ihnen offen, um ein Problem zu bewältigen.

Optimieren des Datenzugriffs

Wie viel Zeit verbringen Sie damit, die Zugriffe Ihrer Anwendung auf Datenbankressourcen zu analysieren? Bei Entwicklern ist die häufigste Antwort: "Nicht sehr viel". Üblicherweise werden die meisten Leistungsprobleme auf zwei verschiedene Arten behoben: Durch Hinzufügen neuer Hardware oder durch Optimieren des Codes, der die Anwendung ausführt. Die meisten Entwickler ziehen es vor, den Code zu optimieren; es kann jedoch manchmal effektiver sein, einfach neue Hardware hinzuzufügen. Es sollte allerdings erwähnt werden, dass Leistungsprobleme verstärkt werden können, wenn neue Hardware vor der Analyse der Engpässe hinzugefügt wird. Wenn ein Engpass in der Software vorhanden ist, können Anforderungen durch leistungsstarke Hardware schneller in die Warteschlange eingefügt werden, wodurch der Engpass noch offensichtlicher wird. Der beste Ansatz ist wie immer, die Engpässe (bei Hardware und Software) zu analysieren, bevor Veränderungen vorgenommen werden.

Als Entwickler bevorzuge ich zunächst eine Prüfung des Codes, bevor ich eine Hardwareanalyse in Betracht ziehe. Sobald diese Entscheidung gefallen ist, wird üblicherweise mit Application Center Test ein grundlegendes Profil der Anwendung erstellt, um zu ermitteln, an welchen Stellen der Anwendung die Leistung zu gering ist. Wenn die Problembereiche identifiziert sind, sollte im nächsten Schritt mit SQL Server Profiler ermittelt werden, wie vorhandene Datenbankressourcen von der Anwendung verwendet werden.

Wenn es um die Entwicklung leistungsstarker Webanwendungen geht, gibt es eine Regel: Wenn in Ihrer Anwendung prozessübergreifende Kommunikation (Datenbank, Webdienste, Remoteobjektaufruf) stattfindet, ist es effektiver, diese Kommunikation zu optimieren oder vollständig zu entfernen, bevor Sie versuchen, anderen Code innerhalb der Anwendung zu optimieren. Bei der Datenbankoptimierung gilt für mich die allgemeine Regel: Wenn die Anwendung mehr als ein- oder zweimal pro Anforderung auf die Datenbank zugreift (oder einen Webdienst aufruft), muss es dafür einen sehr guten Grund geben, da diese weitgreifenden Anforderungen meist die Hauptursache für eine schlechte Anwendungsleistung sind.

Mein Team bei Telligent hat gerade drei Wochen damit verbracht, unseren Community Server für forums.asp.net zu optimieren. Wir haben beinahe 40 leistungsbezogene Änderungen vorgenommen, davon 75 Prozent in der Datenbank. Die übrigen 25 Prozent bezogen sich direkt auf Codeänderungen, um den Zeitaufwand bei Datenbankanforderungen zu reduzieren bzw. zu optimieren. Die Funktionen zum Zwischenspeichern in ASP.NET gehören zu den besten Tools zum Verringern von Roundtrips zur Datenbank.

Wenn Sie SQL Server verwenden, verfügen Sie bereits über die großartigen Tools Profiler und Query Analyzer, mit denen Sie die Datenbankverwendung Ihrer Anwendung umfassend analysieren können. Ich werde in dieser Kolumne nicht alle internen Prozesse von Profiler oder Query Analyzer behandeln. Wenn Sie mehr darüber erfahren möchten, wie SQL Server hinter den Kulissen funktioniert, empfehle ich das Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook von Ken England (Digital Press, 2001). Dieses Buch ist eines meiner bevorzugten Referenzmaterialien. Mein eigenes Exemplar ist voll von Hervorhebungen und Kaffeeflecken. Hoffentlich erfährt Ihr Exemplar eine ähnlich sinnvolle Existenz.

SQL Server Profiler

Das Überprüfen von Protokollen oder Ablaufverfolgungsdateien, die von Ihrer Anwendung erstellt werden, ist unabdingbar, um zu verstehen, wie Ihre Anwendung verwendet wird. Beispielsweise ist es für Webentwickler üblich, IIS-Protokolle zu prüfen, um die Verwendung der Anwendung zu analysieren. In Community Server gibt es einen integrierten Ausnahmehandler zum Verfolgen aller erzeugten Ausnahmen. Später können wir diese Protokolle überprüfen, um eine Vorstellung der Problembereiche zu bekommen.

Mit SQL Server Profiler können die Aktivitäten der ausgeführten SQL Server-Datenbank in einer Livesitzung angezeigt werden. Sie können wichtige Informationen abrufen, z. B. wie viele Abfragen von der Datenbank ausgeführt werden, wie viel Zeit für diese Abfragen benötigt wird, welche Abfrage von welcher Datenbank ausgeführt wird usw.

Abbildung 1 zeigt eine Profilersitzung mit der Datenbank, die auf dem ASP.NET-Cluster verwendet wird. Sie können mehrere erfasste SQL-Ablaufverfolgungen sehen, die gespeicherte Prozeduren für weblogs.asp.net (Elemente mit dem Präfix blog_), beta.asp.net (Elemente mit dem Präfix aspnet_), Community Server-Foren (Elemente mit dem Präfix cs_) sowie Download-Indikatoren für www.asp.net-Downloads (IncrementDownloads) darstellen. Durch Auswahl eines Elements kann auch der vollständige Text angezeigt werden, wie hier für die gespeicherte Prozedur cs_forums_Post. Es gibt zusätzliche Spalten für CPU, Lese- und Schreibzugriffe und die Dauer.

Abbildung 1: Profilersitzung mit der Datenbank

Gespeicherte Prozeduren

Es gibt viele leidenschaftliche Diskussionen über die Verwendung gespeicherter Prozeduren. Ein gutes Argument für ihre Verwendung ist in "TheServerSide Debates: Stored Procedures vs Parameterized Queries" (in englischer Sprache) von Peter DeBetta nachzulesen.

Einer der Vorteile gespeicherter Prozeduren in Unternehmensanwendungen ist die Kapselung und Abstraktion. Wenn eine Anwendung dynamisches SQL (dynamisch erstellte SQL-Texte) verwendet, bedeutet die Optimierung in der Regel eine größere Herausforderung. Das Optimieren von dynamischem SQL erfordert ein erneutes Kompilieren der Anwendung. Gespeicherte Prozeduren hingegen kapseln ihre Funktionalität und können bearbeitet werden, ohne dass die öffentliche Signatur davon betroffen ist. So wie Methoden in APIs zum Kapseln der Funktionalität verwendet werden, übernehmen gespeicherte Prozeduren in vielerlei Hinsicht dieselbe Funktion in der Datenbank.

Natürlich gibt es auch einige gute Gründe für die Verwendung von dynamischem SQL. Ein Beispiel sind relationale und Objektzuordnungen, mit denen SQL dynamisch je nach den Anforderungen der Geschäftsobjekte erzeugt werden kann. Würde dynamisches SQL in einem der ASP.NET-Cluster verwendet werden, würde es in SQL Profiler als Standard-SQL angezeigt werden, also z. B. als SELECT spalte1, spalte2 from TabelleA. Dies ist hier jedoch nicht der Fall.

Starten von Profiler

Profiler ist bei jeder Standardinstallation von SQL Server unter Alle Programme im Ordner SQL Server zu finden. Ich empfehle die Installation der SQL Server-Tools in jeder standardmäßigen Entwicklungsumgebung.

Wenn Sie Profiler öffnen, müssen Sie zuerst eine Verbindung mit der Datenbank herstellen, bevor Sie eine Profilerstellungssitzung starten können. Zum Verbinden mit der Datenbank wählen Sie Datei | Neu | Ablaufverfolgung aus. Dadurch wird das Dialogfeld Verbindung mit SQL Server herstellen geöffnet. Geben Sie Ihre Anmeldeinformationen für Windows oder für SQL Server ein. Beachten Sie, dass Sie Mitglied der SQL Server-Gruppe sysadmin sein müssen, um Profiler auszuführen.

Nach Überprüfung der Anmeldeinformationen müssen Sie für die Ablaufverfolgung einige Eigenschaften im Dialogfeld Ablaufverfolgungseigenschaften festlegen. Standardmäßig erfasst Profiler alle Ereignisse, die in der Standardvorlage festgelegt sind. Anstelle aller Ereignisse führt diese Vorlage eine Auswahl der wichtigsten Ereignisse auf. Während die Erfassung aller Ereignisse nützlich sein kann, unterstützen die meisten Server mehrere Datenbanken, und eine Ablaufverfolgung, die den gesamten Datenbankverkehr erfasst, ist weder notwendig noch hilfreich. Auf der Registerkarte Filter können Sie nach Bedarf Filter festlegen, um die Erfassung weiter einzuschränken. Beispielsweise können Sie die Ablaufverfolgung so einschränken, dass nur Verbindungen einer bestimmten Anmeldung, Abfragen mit einer bestimmten Dauer oder Abfragen angezeigt werden, die einen bestimmten Text enthalten (es werden Platzhalterzeichen wie % unterstützt, um SQL-Platzhaltervergleiche durchzuführen).

Nehmen wir z. B. an, dass Sie eine Ablaufverfolgung des gesamten Datenbankverkehrs für Community Server durchführen möchten. Des Weiteren möchten Sie ausschließlich Verbindungen einsehen, die für die Ausführung mehr als 100 ms benötigen (normalerweise werden Elemente, die mehr als 200 ms benötigen, für den Endbenutzer bemerkbar). Dazu richte ich einen Filter mit einem bestimmten Anmeldenamen ein, z. B. csforums, sowie einer Dauer größer oder gleich 100. Abbildung 2 zeigt das Ergebnis dieser Ablaufverfolgung.

Abbildung 2: Ergebnis dieser Ablaufverfolgung

Es gibt mehrere gespeicherte Prozeduren, die häufig ausgeführt werden: cs_forums_Search und cs_Sections_Get. Von Bedeutung ist auch cs_Threads_GetThreadSet mit einer Dauer von 703 ms! Auch die gespeicherte Prozedur cs_Sections_Get erscheint mit über 11.000 Lesezugriffen und einem hohen Wert bei der Dauer eher ungewöhnlich. Diese Zahlen sind nicht schockierend, doch sie sind auch keineswegs perfekt.

Mit den von Profiler bereitgestellten Informationen können Sie nun nachvollziehen, was innerhalb der Datenbank stattfindet und wo Zeit verloren geht. In dieser Ablaufverfolgungssitzung habe ich Prozeduren mit langen Ausführungszeiten und einem Schwerpunkt auf der Forendatenbank herausgefiltert. Es kann aber auch Fälle geben, in denen zu viele Abfragen ausgeführt werden, die das System bremsen. In solchen Fällen empfiehlt es sich dann, die Datenbankauslastung durch Hinzufügen eines weiteren Servers zu verringern oder Tests mit einer lokalen Datenbankkopie durchzuführen, um falsche Positivmeldungen zu eliminieren.

Auf einem stark ausgelasteten System sollten Sie anstelle der SQL-Ablaufverfolgung über die Profiler-Benutzeroberfläche die Verwendung von T-SQL in Erwägung ziehen, um die Ergebnisse an eine Datei auf dem Server zu senden. (Sie können die Benutzeroberfläche weiterhin verwenden, um die Ablaufverfolgungsdefinition zu erstellen und sie in ein Skript einzufügen und auszuführen.) Durch das Senden der Ergebnisse an Ablaufverfolgungsdateien auf dem Server wird sichergestellt, dass keine Ereignisse verloren gehen, was beim Senden von Ablaufverfolgungsereignissen an ein Rowset (wie bei SQL Profiler) nicht garantiert werden kann.

SQL Server Query Analyzer

Da Sie nun über die entsprechenden Tools verfügen, um eine Analyse und ein Profil Ihrer Datenbank zu erstellen, fragen Sie sich wahrscheinlich, was als Nächstes geschieht. Nun, der nächste Schritt ist die Untersuchung der Abfragen in Query Analyzer. Dieses Tool ist für jeden Entwickler bei der Arbeit mit SQL Server nützlich. Während Profiler Ihnen eine allgemeine Übersicht des Systems liefert, gestattet Query Analyzer ausführliche Detailanalysen. Mit Query Analyzer können Sie Abfragen und gespeicherte Prozeduren ausführen und eine visuelle Darstellung des Ausführungsplans von SQL Server anzeigen.

Wie Profiler befindet sich Query Analyzer im Menü Alle Programme im Ordner SQL Server. Auch hier müssen Sie zu Beginn die erforderlichen Informationen im Dialogfeld Verbindung mit SQL Server herstellen eingeben. Sobald die Verbindung hergestellt ist, können Sie mit dem folgenden Befehl zur Datenbank wechseln:

use [datenbankname]

Klicken Sie anschließend auf der Menüleiste auf die Wiedergabeschaltfläche (oder markieren Sie den Text, und drücken Sie F5). Durch beide Optionen wird das markierte SQL ausgeführt.

In Profiler habe ich festgestellt, dass die gespeicherte Prozedur cs_Sections_Get eine ungewöhnlich hohe Datenmenge liest: über 11.000 Lesezugriffe! Da sich die SQL-Ablaufverfolgung aus Profiler sich in den Query Analyzer kopieren lässt, können Sie überprüfen, welche einzelnen Aktionen während dieser Abfrage stattfinden.

Analysieren einer Abfrage

Zum Ausführen der Abfrage in Query Analyzer fügen Sie zuerst den Inhalt in Query Analyzer ein. Wählen Sie dann auf der Symbolleiste Abfrage | Ausführungsplan anzeigen aus. Markieren Sie anschließend die auszuführende SQL-Anweisung und drücken Sie F5. In Abbildung 3 sind meine Ergebnisse dargestellt.

Abbildung 3: Ergebnisse

Unmittelbar unter der ausgeführten SQL-Abfrage befindet sich das Ergebnisraster, in dem alle Rückgabewerte der Abfrage angezeigt werden. In diesem Fall wurden zwei Resultsets zurückgegeben. Beim ersten Resultset handelt es sich um eine Liste mit Abschnitten (Foren) und beim zweiten um einen Satz von Berechtigungen. Community Server verwendet ein rollenbasiertes Berechtigungssystem, um zu steuern, welche Aktionen Benutzer innerhalb des Systems durchführen dürfen oder nicht. Immer wenn eine Liste mit Abschnitten abgerufen wird, aktualisiert das System auch die Liste der Berechtigungen für diese Abschnitte.

In Query Analyzer wird unten rechts folgender Text angezeigt: 5430 Zeilen. Dies gibt die Gesamtzahl der zurückgegebenen Zeilen an. Bei dieser Prozedur gab es einen Fehler in der Berechtigungslogik. Das System hat daher eine Liste zurückgegeben, die der Gesamtzahl der Rollen multipliziert mit der Gesamtzahl der Abschnitte entspricht. Im Fall von forums.asp.net gibt es 29 eindeutige Rollen und 181 Abschnitte, wodurch sich 5.249 Zeilen ergeben. Addieren Sie die 181 Zeilen, die vom ersten Resultset zurückgegeben wurden, erhalten Sie eine Gesamtzahl von 5.430 zurückgegebenen Zeilen.

Natürlich sind dies viel zu viele Zeilen. Mithilfe von Profiler und Query Analyzer konnte diese Abfrage deshalb soweit optimiert werden, dass weniger als 250 Datensätze zurückzugeben werden. Die Ausführungsdauer wurde damit auf weniger als 100 ms verkürzt.

Zusätzlich zur Ausführung von Abfragen können Sie mit Query Analyzer Abfrageanalysen durchführen. Erinnern Sie sich, dass Sie die Anzeige des Ausführungsplans vor der SQL-Ausführung aktiviert haben. Der Plan enthält ein visuelles Diagramm mit Informationen darüber, wie SQL Server Ihre Abfrage ausführt und welche Indizes er verwendet. Außerdem enthält das Diagramm Daten, die bei der Optimierung von Indizes hilfreich sein können und die SQL Server zur Verbesserung der Abfrageleistung benötigt.

In Abbildung 4 sind alle ausgeführten Abfragen, die zugehörigen Ausführungspläne, die verwendeten Indizes, Verknüpfungen sowie die zurückgegebene Datenmenge jeder einzelnen Operation dargestellt. Auch die Pfeile zwischen den einzelnen Elementen haben eine Bedeutung: Die Dicke des Pfeils ist eine visuelle Darstellung der von der Operation zurückgegeben Datenmenge. Das heißt, eine schlecht optimierte Abfrage verfügt möglicherweise über viele dicke Pfeile, die auf Operationen hinweisen, die wahrscheinlich zu viele Daten zurückgeben.

Abbildung 4: Alle ausgeführten Abfragen, die zugehörigen Ausführungspläne, die verwendeten Indizes, Verknüpfungen sowie die zurückgegebene Datenmenge jeder einzelnen Operation

Application Center Test, SQL Server Profiler und SQL Server Query Analyzer gehören zu den leistungsstärksten Tools für die Optimierung der Anwendungsleistung bei ASP.NET- oder SQL Server-Anwendungen. Sie sollten einen festen Platz in der Toolsammlung jedes Entwicklers haben und routinemäßig verwendet werden, um die Gesamtleistung einer Anwendung zu untersuchen. Zu häufig setzen Entwickler den Schwerpunkt auf die Optimierung interner Coderoutinen, obwohl durch das Entfernen oder Optimieren der prozessübergreifenden Kommunikation, z. B. der Datenbankaufrufe, viel nachhaltigere Leistungsverbesserungen erzielt werden können.

Hoffentlich habe ich Ihr Interesse geweckt, so dass Sie mehr über die Interna von SQL Server erfahren möchten. Es ist eine wirklich faszinierende Technologie. Wenn Sie zu den Glücklichen gehören, die einen Datenbankadministrator in Ihrem Team haben, bitten Sie ihn, Ihnen die Interaktion zwischen der Datenbank und Ihrer Anwendung etwas genauer zu erläutern - Sie werden eine Menge lernen!

Senden Sie Fragen und Kommentare für Rob Howard an xtrmasp@microsoft.com.

Rob Howard ist einer der Gründer von Telligent Systems und spezialisiert auf leistungsstarke Webanwendungen, Informationsmanagement und Systeme für die Zusammenarbeit. Zuvor war er Mitarbeiter von Microsoft und in dieser Funktion an der Entwicklung der Infrastrukturfeatures von ASP.NET 1.0, 1.1 und 2.0 beteiligt. Sie erreichen Rob Howard unter rhoward@telligentsystems.com.