Waitstates bei Microsofts SQL Server analysieren
Bitte warten
Bei Microsofts Datenbanksystem SQL Server kommt es regelmäßig zu Wartezuständen. Nehmen sie überhand, können sie schlimmstenfalls alle Anwendungen beeinträchtigen. Doch das System stellt Views bereit, mit denen man den Ursachen der Verzögerung auf den Grund gehen kann.
Microsofts Datenbanksystem SQL Server ist ein Betriebssystem mit nicht präemptivem (kooperativem) Multitasking, das Windows lediglich als Host verwendet. Die Software verwaltet somit alle Prozesse und Anforderungen selbst. Beim Bearbeiten der Letzteren kommt es immer wieder zu Situationen, in denen sie nicht unmittelbar weiterarbeiten kann, sondern zunächst Ressourcen anfordern muss. Die Datenbank registriert das Auftreten solcher Vorgänge und protokolliert sie. Hierbei unterscheidet man zwischen aktuellen und historischen Wartezuständen. Beide Informationen können über sogenannte System Views (Dynamic Management Objects, DMO) oder Werkzeuge von Drittanbietern abgerufen werden.
Um zu verstehen, warum Wartezustände eintreten, muss man die Prozesskette kennen, die der SQL Server intern anwendet, wenn er eine Anforderung übers Netz erhält. Sie besteht aus sechs Einzelschritten.
Worker Threads in der Prozesskette
Clientanforderung: Im ersten Schritt sendet ein Clientdienst (etwa eine Applikation oder ein Webserver) eine Anforderung in Form einer Anfrage an den SQL Server, woraufhin die Datenbank einen neuen Task erstellt.
Allozieren eines Worker Thread: Dieser systeminterne Datenbankprozess übernimmt die Anfrage und verarbeitet sie in der Datenbank-Engine. Bereits bei diesem Prozessschritt kann es zu Wartezuständen kommen, weil beispielsweise eine begrenzte Zahl von Worker Threads zum Einsatz kommt oder zu viele Tasks auf einen solchen warten müssen.
Erstellen eines Ausführungsplans: Sobald der SQL Server für den Vorgang einen Worker Thread alloziert hat, wird im dritten Schritt die Abfrage geparst, kompiliert und ein geeigneter Ausführungsplan erstellt. Letzteres kann je nach Komplexität der Abfrage sehr rechenintensiv sein. Daher speichert das System einen geeigneten Ausführungsplan im Plan-Cache, um ihn bei Bedarf (wenn eine identische Abfrage eintrifft) erneut zu verwenden.
Laden der Daten in den Buffer verzögert
Ausführen der Abfrage: Dieser Prozess führt üblicherweise zu den häufigsten Wartezuständen im Gesamtprozess. Wie Abbildung 1 zeigt, müssen sämtliche Daten aus dem Buffer Pool gelesen werden (siehe blauer Kasten „Alle Links“ am Artikelende). Beim Buffer Pool handelt es sich um den Speicherbereich im RAM, in dem der SQL Server die Daten ablegt, die an den Client zu senden sind. Grundsätzlich müssen alle von der Datenbank angeforderten Daten zunächst dorthin geladen werden. Sind sie dort nicht vorhanden, muss das System sie aus dem Storage in den Buffer Pool laden, was zu Wartezeit führt. Alle Wartezustände, die in beliebiger Weise mit Datenseiten zusammenhängen, nennt man Buffer Latches.
Liefern der angeforderten Daten: Nachdem dem System alle angeforderten Daten zur Verfügung stehen, erhält der aufrufende Client sie im fünften Schritt übers Netz zurück. Auch währenddessen kann es zu Wartezeiten (Latenzen) kommen, etwa weil die Leitung überlastet ist oder der Client die Daten nicht schnell genug verarbeiten kann. In beiden Fällen muss der SQL Server warten – und er protokolliert diesen Vorgang.
Freigabe des Worker Thread: Hat der Client alle Daten entgegengenommen, wird der Thread beendet, und die Liste der Worker Threads führt ihn wieder als freies Element.
Dieser vielstufige Prozess findet für jede Anforderung statt, die an den SQL Server gesendet wird. Und bei jedem Schritt einer Anforderung können Wartezeiten entstehen, die das System protokolliert.
Lebenszyklus eines Thread
Seine Prozesse zum Verarbeiten von Daten bindet der SQL Server an eine oder alle zur Verfügung stehenden CPUs und stellt für jeden Kern einen sogenannten Scheduler bereit. Die Datenbank muss selbstständig entscheiden, wie sie unter vielen konkurrierenden Anforderungen die (kostbare und begrenzte) Rechenzeit auf alle Prozesse verteilt – dazu teilt sie jedem Thread vorab definierte Rechenzeit zu. Abbildung 2 zeigt das „wartende Dreieck“ der drei Zustände eines Thread mit seinen verschiedenen Prozessschritten: running, suspended und runnable.
Der Thread-Status running bedeutet, dass SQL Server dem aktuellen Request dedizierte CPU-Rechenzeit zur Verfügung stellt. Ein Thread mit diesem Status ist aktiv, da er arbeitet. Auf einem CPU-Kern kann immer nur ein Thread gleichzeitig Rechenzeit erhalten – sofern weitere Threads an diesen Kern gebunden sind, müssen sie warten.
Ein Thread wechselt in den Status suspended, sobald die Datenbank feststellt, dass nicht alle notwendigen Ressourcen (Datenseiten) zur Verfügung stehen, um die Daten an den Client zu senden. In diesem Fall spricht man von Resource Waits. Werden etwa Daten angefordert, die sich noch nicht im Buffer Pool befinden, muss der Thread warten, bis sie dort zur Verfügung stehen. Während dieser Zeit versetzt SQL Server den aktiven Thread aus dem Status running in den Wartezustand suspended. Diesen hat der wartende Thread so lange inne, bis das System die Verfügbarkeit aller angeforderten Ressourcen meldet: Datenseiten befinden sich im Buffer Pool, und angeforderte Seiten werden nicht mehr durch andere Prozesse blockiert.
Sobald die Daten vorhanden sind, wechselt der Status des Thread automatisch in den Status runnable, in dem alle Threads gleichberechtigt auf CPU-Rechenzeit warten – es sei denn, man verwendet den Resource Governor der Enterprise Edition, der dem Ressourcenverbrauch Limits setzen kann. Während die eine Abfrage lediglich einen Datensatz liefert, sind weitere Threads auf der CPU gebunden, die möglicherweise Millionen von Datensätzen verarbeiten sollen. Für diesen Fall hat man bei Microsoft vorgesorgt und die Rechenzeit für jeden Thread auf 4 Millisekunden begrenzt. Dieser Quantum genannte Wert ist fest programmiert und lässt sich nicht beeinflussen, der Zustand heißt „Signal Wait“. Daraus ergibt sich, dass zwei Arten von Threads auf CPU-Zeit warten können:
– Threads, die zuvor auf ihre Ressourcen gewartet haben;
– Threads, die aufgrund der langen Rechenzeit ihr Quantum überschritten haben.
In letzterem Fall muss der Thread nicht auf weitere Ressourcen warten, sondern kann direkt aus dem Status running in den Status runnable wechseln. Diesen Vorgang protokolliert SQL Server ebenfalls mit einem speziellen Wartezustand, der ausschließlich diesen Umstand berücksichtigt.
Auswerten mit System Views
Zum Auswerten der Wartezustände stellt die Datenbank mehrere Systemansichten (System Views) zur Verfügung, mit denen man wahlweise historische oder aktuelle Wartezustände analysiert (siehe „Alle Links“). Nachfolgend beschreibt der Artikel die Anwendung der wichtigsten Views.
Mit dem System View sys.dm_os_schedulers ermittelt man Informationen zur Auslastung der verfügbaren CPU-Kerne. Eine Übersicht aller aktiven Kerne und deren momentaner Benutzung bietet
SELECT [DOS].[cpu_id], [DOS].[current_tasks_count], [DOS].[runnable_tasks_count], [DOS].[active_workers_count], [DOS].[quantum_length_us] FROM sys.dm_os_schedulers AS DOS WHERE is_online = 1 AND status = ,VISIBLE ONLINE';
Die Ausgabe (Abb. 3) zeigt ein System mit vier aktiven und vom SQL Server verwendbaren CPU-Kernen (Spalte cpu_id). Aktuell befinden sich pro Kern zwischen 3 und 7 Tasks in der Queue (current_tasks_count) – diese Werte beinhalten Threads mit Worker Thread und solche, die noch keinen bekommen haben. Zum Zeitpunkt der Messung gab es keine Threads, die auf Ausführung warten (runnable_tasks_count).