Threadpool Waits und Scheduler Deadlocks

In diesem Artikel erklären wir Ihnen, wie man einen Scheduler Deadlock löst und die Threadpool Wartezeit verkürzen kann. Bevor wir jedoch zu den Lösungen kommen, wollen wir zuerst auf die Funktionsweise eines SOS Schedulers näher eingehen.

Begriffsdefinition

Wir beginnen mit dem SOS Scheduler (SQL on OS Scheduler). Dieser ist dafür verantwortlich, eingehende Tasks des Client anzunehmen und die Bearbeitung einzuleiten. Dabei agiert er als Master - was bedeutet, dass er nicht selbst Aufgaben ausführt, sondern diese an Worker Threads abgibt. Jeder Worker Thread erhält eine Teilaufgabe und soll diese anschließend ausführen. Jedoch kann immer nur genau ein Thread auf einmal auf die CPU zugreifen. Daher wird der Zugriff mit dem Prinzip First-In-First-Out geregelt.

Wenn wir von Worker Threads sprechen, denkt man vielleicht an “normale” Threads. Jedoch gibt es hier eine Unterscheidung. Die Worker Threads sind ein Konstrukt des Schedulers. Sie sind aufgebaut wie Threads, enthalten aber noch weitaus mehr Informationen zu dem System. Das hilft dabei, dass ein Kontextwechsel nicht notwendig ist. Jedoch lässt sich genau ein Worker auf genau einen Thread abbilden.

Exkurs: Kontextwechsel

Ein Kontextwechsel tritt ein, wenn ein Thread nicht fertig gestellt werden kann, da auf neue Dateien gewartet werden muss. In der Zwischenzeit würde jedoch Zeit verschwendet werden, in der die CPU nicht arbeitet. Aus diesem Grund wird einem anderen Thread Zugang gewährt. Um den Fortschritt des ursprünglichen Threads nicht zu verlieren, wird dieser gespeichert. Sobald dieser Thread wieder weiter arbeiten kann, wird der entsprechende Status geladen und an derselben Stelle wieder weiter ausgeführt. Dieses Speichern und Laden nennt man einen Kontextwechsel.

Eine Task ist eine Aufgabe auf der Ebene des Schedulers. Beispiele dafür sind Queries, Login oder Logout Events. Der Scheduler erhält diese Task und zerlegt sie in kleinere Aufgaben. Da jede Teilaufgabe einem Worker übergeben wird, besitzt jede Task unterschiedliche Kosten. Die Verteilung von Worker Threads schauen wir uns später an.

Leistungspakete der Mainzer Datenfabrik

Als professioneller SQL Server Support und zertifizierter Microsoft Partner unterstützen wir Sie in allen Fragen und individuellen Problemen rund um Ihre Serverumgebung, egal ob vor Ort oder remote. Überzeugen Sie sich selbst von unserem vielfältigen Angebot und den individuellen Leistungspaketen.

IT-Consulting Troubleshooting Assessments & Support_backup

Beispielhafter Ablauf

Wie sieht nun ein Ablauf mit einem SOS Scheduler auf der Serverebene aus?

Zunächst muss eine physische Verbindung zwischen Client und Server hergestellt werden. Hierfür stellt der Client eine Login Request, die an den Scheduler geht. Dieser verarbeitet die Anfrage und schickt eine Confirmation, wenn der Login korrekt ist. Mit dem erfolgreichen Login startet der Server eine neue Session mit dem Client. In diesem Schritt wird eine SessionID zugewiesen. Diese hilft dabei, gestellte Tasks wieder dem Client zuzuordnen und somit einen Überblick über die Tasks zu bewahren. In dem Fall, dass von zwei Clients dieselbe Task gestellt wird, können beide Vorgänge anhand der SessionID identifiziert werden.

Besteht nun eine aktive Session zwischen Client und Server, kann der Client Tasks stellen. Diese werden an den Scheduler geschickt. Er verarbeitet die Anfrage und macht eine Kostenberechnung. Es wird danach geschaut, welche Teilaufgaben mit der gestellten Task anfallen und wie viele Worker Threads dafür benötigt werden. Hat der Scheduler nicht genügend Threads, befindet sich aber noch unter seinem maximalen Limit an erlaubten Workern, erstellt er sich die benötigte Menge. Kann er nicht genügend Worker erstellen, kommt es zu einem Threadpool Wait. Darauf gehen wir später genauer ein.

Innerhalb des Scheduler sieht es demnach folgendermaßen aus: Es gibt eine Liste mit Threads, die erledigt werden können, auch Runnable Queue genannt. In dieser Liste befinden sich alle Threads die den Status RUNNABLE haben. Wenn die CPU frei ist, wechselt der erste Thread in der Liste zu RUNNING und führt seine Task aus. Wenn er während der Bearbeitung feststellt, dass ihm noch Daten fehlen, oder er nicht weitermachen kann, wechselt er in einen SUSPENDED Status und wird in eine Waiting Liste geschoben. Hier befinden sich alle Threads, die momentan nicht alle Ressourcen besitzen, um mit ihrer Bearbeitung weitermachen zu können. Steht jetzt die fehlende Datei zur Verfügung, wechselt der Thread erneut den Status zu RUNNABLE und wird an das Ende der Runnable Queue gesetzt. Dort muss er warten, bis er an der Reihe ist, um auf die CPU zuzugreifen. Schließt er seine Aufgabe ab, wird der Worker wieder abgebaut, also gelöscht.

Scheduler

Bisher wurde immer vom SOS Scheduler gesprochen, aber wie unterscheidet dieser sich vom Scheduler des Betriebssystems und warum nutzen wir diesen nicht?

Jedes Betriebssystem hat einen Scheduler, der wie oben schon erwähnt, die anfallenden Tasks managed. Diese Scheduler arbeiten preemptive. Das heißt, dass der Scheduler in den Ablauf der arbeitenden Threads eingreifen kann. Das Grundprinzip bleibt beim First-In-First-Out, jedoch kann der Scheduler Tasks mit höherer Wichtigkeit in den Vordergrund schieben. Dafür wird der aktuelle Thread pausiert und von dem Priorisierten abgelöst. Außerdem erhält jeder Thread eine Zeit, in der die Aufgabe zu erledigen ist. Wird diese Zeit überschritten, macht der Thread Platz und muss warten, bis er wieder an der Reihe ist.

Es kann daher vorkommen, dass manche Threads kurz vor der Fertigstellung ihrer Aufgabe sind, aber trotzdem ihren Platz an der CPU aufgeben müssen. Im Gegensatz kann es dazu kommen, dass der Thread zu Beginn nicht alle Ressourcen besitzt und somit seine Zeit “absitzt”, ohne dass irgendeine Arbeit ausgeführt wird. Darum ist das System der CPU Nutzung nicht optimal für SQL Server. Stattdessen wird ein non-preemptive Prinzip benutzt: Der Scheduler ist davon abhängig, dass jeder Thread freiwillig seinen Platz wieder hergibt. So kann die CPU besser genutzt werden. Denn wenn ein Thread noch nicht arbeiten kann, gibt er seinen Zugriff ab und ein anderer kann in der Zwischenzeit seine Aufgabe erledigen.

Threadpool und Threadpool Waits

Wir wissen bereits, dass jeder Scheduler Worker Threads kontrolliert und dass jede Task eine bestimmte Anzahl an Threads benötigt. Was wir bisher nicht behandelt haben ist, wie viele Worker es überhaupt gibt.

Jeder Scheduler hat eine maximale Anzahl an Worker Threads. Er kann keine Weiteren darüber hinaus erstellen. Die Anzahl an aktiven Threads variiert jedoch. Sobald das Programm gestartet wird, wird eine bestimmte Menge an Worker Threads erstellt, die sich um Hintergrundaufgaben kümmern. Diese Menge ist meistens fest und ändert sich nicht. Darüber hinaus kann der Scheduler dynamisch bestimmen, wie viele Threads er momentan benötigt. Abhängig ist das von den Tasks, die zu erledigen sind. Wenn eine neue Task eingeht, werden neue Threads erzeugt und mit dem Erledigen der Aufgabe wieder gelöscht. Diese variable Menge ist der Threadpool.

Ein Problem entsteht nun, wenn eine Task eingeht, die mehr Worker Threads benötigt, als der Threadpool hergeben kann. Also wenn die Menge an Threads die maximale Menge überschreitet. Wenn dies der Fall ist, spricht man von einem Threadpool Wait. Denn die Aufgabe wird zunächst hinten angestellt, bis genügend Worker Threads zur Verfügung stehen.

Wann können Threadpool Waits auftreten?

Wenn ein Thread ein Lock erhält, welches alle anderen Threads blockiert. Somit können andere Threads ihre Aufgaben nicht abschließen und die Anzahl an Threads wird nicht abgebaut. Stattdessen werden durch neue Tasks immer mehr Threads erstellt. Die Folge daraus ist, dass der Threadpool immer weiter ausgereizt wird, bis er schließlich erschöpft ist. -> Wenn das der Fall ist, muss die SessionID identifiziert werden, die alle wartenden Tasks blockiert. Löscht man den Task zur SID, können die anderen Tasks wieder abgebaut werden.
Das Problem entsteht ebenfalls, wenn viele Connections eine parallel arbeitende Query, oder Queries die länger als gewöhnlich brauchen, aufrufen. Abhängig davon, wie diese Queries aussehen, können bei der Ausführung Verzögerungen kommen. Diese sind bis zu einem gewissen Grad normal, erschöpfen aber den Threadpool. Dieses Problem kennt man auch unter dem Namen CXPACKET Wait. Wenn zu viele dieser Queries gleichzeitig laufen, kann daraus ein Threadpool Wait resultieren.

Exkurs: CXPACKET Wait

Dieser Wait ist ein Zeichen für eine parallele Query. Schaut man sich den Aufbau einer solchen parallelen Ausführung an, gibt es die Situation, dass eine Gruppe Threads Daten produzieren und über einen Buffer an eine weitere Gruppe Threads schicken, die diese Daten verarbeiten. Selbst wenn dieser Plan optimal ist, kann es hier zu minimalen Waits kommen, da die Threads nicht gleichzeitig fertig werden, bzw. der Zugriff auf den Buffer nicht reibungslos stattfindet.

-> Findet man also einen CXPACKET Wait, ist das zunächst nichts Schlimmes. Falls es doch signifikant sein sollte, empfehlen wir dringend eine genauere Untersuchung der Ursache, bevor mit der Einstellung MAXDOP = 1 die Brechstange ausgepackt wird. Im Fall der lang laufenden Queries, sollte sich die Ursache dafür angeschaut werden.

Gibt es genauso viele aktive Sessions wie Worker Threads, könnte dies ebenfalls eine mögliche Ursache darstellen. Da bei so vielen Sessions jede Task nur einem Thread zugewiesen werden kann, führt dies erneut zu einem Threadpool Wait. Da aber eine Task meist anspruchsvoller ist, kommt es zu einem Wait.
-> Wir lösen dies, indem dass wir entweder die Anzahl aktiver Sessions verringern oder die Anzahl maximaler Threads erhöhen. Jedoch kann es auch hier zu Nebeneffekten kommen, weshalb das meistens keine empfehlenswerte Lösung ist.

Deadlock

Bisher haben wir immer von dem einen Scheduler gesprochen, aber tatsächlich besitzt ein System mehrere. Dies ist abhängig von den vorhandenen Kernen. Denn jeder Kern wird von einem Scheduler verwaltet. Für den Fall, dass bei einem Scheduler ein Problem auftritt, gibt es einen Scheduler Monitor. Dieser überwacht von Zeit zu Zeit den Status jedes Schedulers und meldet aufkommende Fehler. Denn es kann passieren, dass ein Scheduler stuck ist. Das bedeutet, dass er seit dem letzten Check immer noch wartende Tasks besitzt, aber nicht in der Lage war neue Threads zu erstellen und die aktiven Threads keinen Fortschritt in ihren Aufgaben gemacht haben. Wenn einer dieser Fälle für alle Scheduler gilt, hat man einen Deadlock. Es können somit keine Tasks mehr abgeschlossen werden und das System stürzt mit hoher Wahrscheinlichkeit ab, oder es kommt zu einem Failover.

In dem Moment, in dem der Monitor den Deadlock entdeckt, initialisiert er einen Memory Dump. Mithilfe dieses Memory Dumps kann man herausfinden, an welcher Stelle sich das System aufgehängt hat, bzw. welche Task dafür verantwortlich war. Eine der häufigsten Ursachen sind Locks. Das sind Restriktionen auf Daten, die den Zugriff beschränken. Hier kann es zu großen Block-Ketten kommen, in denen sich alle gegenseitig blockieren und somit keiner mehr Fortschritte machen kann. Weitere Ursachen können auch Latches oder Spinlocks sein.

Meistens erkennt man einen Deadlock erst, wenn das System bereits gecrasht ist oder das Problem sich selbst löst. In diesem Fall bleibt nichts anderes übrig, als die Ursache im Memory Dump zu finden und das System durch Verbesserungen vor weiteren Abstürzen zu schützen. Für einen seltenen Fall, dass man einen aktiven Deadlock erwischt, lässt sich über eine DAC (dedicated admin connection) auf den Server zugreifen. Wenn man schnell genug ist, ist es möglich die Session ausfindig zu machen, die alle anderen Tasks blockiert und diese zu beenden (z.B. mit dem KILL Command). Aber meist kommt es zu einem Failover oder das System stürzt schlimmstenfalls ab, bevor ein Eingriff möglich ist.

Aber auch der Microsoft Support bietet in diesem Fall ein paar Lösungsvorschläge an. So sollte man die neuesten Updates auf dem Server installieren, die Anzahl an maximalen Worker Threads erhöhen, die Datenbank auf READ_COMMITED_SNAPSHOT einstellen oder die MAXDOP nach ihren Empfehlungen einstellen. Diese Lösungen können funktionieren, bieten aber keine dauerhafte Lösung. Die Wurzel des Problems befindet sich noch immer im System und kann daher weiterhin Probleme bereiten. Aus diesem Grund ist immer eine Untersuchung des Memory Dumps zu empfehlen.

Troubleshooting

Damit kontrolliert werden kann, wie der aktuelle Status einer SQL Instanz aussieht, gib es die Prozedur sp_server_diagnostics. Mit diesem Tool werden diagnostische Daten und Health Informationen des Systems ausgelesen und es soll dabei helfen, mögliche Fehler zu erkennen. Dabei werden die Informationen komponentenweise in xml Dateien gespeichert. In Bezug auf unser Problem sind die Daten zu der Komponente query_processing sehr hilfreich. Hier wird beschrieben bei welcher Grenze die maximalen Worker gesetzt wurden (maxWorkers), wie viele Worker aktuell erstellt wurden (workersCreated) und wie viele Tasks momentan ausstehen. Ein besonderes Augenmerk sollte auf dem blocked-pocess-report liegen. Dies ist eine Liste, in der aktuell blockierte Prozesse (blocked-process) aufgeführt werden und von welchem Prozess diese blockiert werden (blocking-process).

Alles in allem bildet dies ein sehr hilfreiches Tool, jedoch muss diese Prozedur manuell ausgeführt werden. Alternativ besteht die Möglichkeit, dies über eine Extended Event Session zu automatisieren. Hier legen wir uns einen Speicherort an, an dem die Daten gespeichert werden sollen. Dabei achten wir darauf, sie möglichst unabhängig von der Server Instanz anzulegen, damit auch bei einem Ausfall ein Zugriff möglich ist. Somit können ab dem Moment des Erstellens an alle Diagnosedaten ausgewertet werden.

Hier ist ein beispielhaftes Skript für ein solches Event. Bei filename muss lediglich der gewünschte Speicherort angegeben werden.

CREATE EVENT SESSION [diag]  
ON SERVER  
           ADD EVENT [sp_server_diagnostics_component_result] (set collect_data=1)  
           ADD TARGET event_file (set filename='c:\temp\diag.xel');  
GO  
ALTER EVENT SESSION [diag]  
      ON SERVER STATE = start;  
GO

Befindet sich der SQL Server aber in einem Failover Cluster oder einer Availabilty Group, hat man Glück. Denn hier werden diese Daten bereits automatisch gespeichert. Die Datei ist daran zu erkennen, dass in der Mitte des Namens SQLDIAG steht. Zu finden sind sie in dem Ordner, an dem die Logs gespeichert werden. Das Format der Dateien ist .xel und lässt sich mit dem Befehl sys.fn_xe_file_target_read_file in eine Tabelle einlesen und auswerten. Für diese Funktion haben wir für Sie ein Skript bereitgestellt.

Um nicht die Datenbank der Availability Group zu ändern, haben wir eine neue (Hilfs-)Datenbank erstellt. In dieser Datenbank erstellen wir eine Tabelle, in die wir alle Daten aus der Datei übertragen. Das Skript ist dabei so aufgebaut, dass sowohl die SQLDIAG-Dateien, als auch manuell erstellte .xel Dateien verwertet werden. Am Ende haben wir eine Ausgabe der Daten, gefiltert nach der Komponente query_processing und zeitlich sortiert.

Auch wenn die Kerndaten in .xml konvertiert werden, unterscheidet sich der Aufbau der Dateien leicht. Aus diesem Grund muss je nach Quelle der Dateien (entweder über SQLDIAG oder das Extended Event) bei der Filterung aufgepasst werden. Denn der Name der Komponente steht an unterschiedlichen Stellen. In unserem Select Statement werden daher beide Fälle geprüft.

create database help
go

use help
go

drop table data --falls die Tabelle bereits gefüllt ist
go

create table data(
	timestamp datetime2,
	[object_name] sysname,
	data_xml xml,
	[file_name] nvarchar(300),
	file_offset int);
go

insert into data 
SELECT
  timestamp_utc
, [object_name]
, data_xml
, [file_name]
, file_offset
FROM sys.fn_xe_file_target_read_file('YourFilePath\*_SQLDIAG_*.xel', default, null, null)
CROSS APPLY (SELECT data_xml = TRY_CONVERT(xml, event_data))
option(recompile)

select * from data 
where [object_name] in ('component_health_result','sp_server_diagnostics_component_result')
  AND
	'query_processing' IN (
		data_xml.value('(event/data[@name="component"])[1]', 'varchar(256)'),
		data_xml.value('(event/data[@name="component"]/text)[1]', 'varchar(256)')
		)
order by timestamp desc

Die Ausgabe in dem oberen Skript gibt jedoch noch keinen genaueren Aufschluss darüber, denn die wertvollen Daten werden momentan noch in ihren .xml Dateien ausgegeben. Damit dies gut zu lesen ist, haben wir ein zweites Skript bereitgestellt. In diesem dient die eben eingelesene Tabelle als Grundlage und wertet die .xml Datei noch weiter aus.

Auch hier muss auf eine unterschiedliche Formatierung der Dateien geachtet werden. In unserem Skript decken wir wieder die Fälle des Extended Events und der SQLDIAG Dateien ab.

use help
select
	timestamp as timestamp,
	coalesce(data_xml.value('(event/data[@name="component"]/text)[1]', 'varchar(256)'),data_xml.value('(event/data[@name="component"])[1]', 'varchar(256)')) as 'component',
	coalesce(data_xml.value('(event/data[@name="state_desc"])[1]', 'varchar(256)'),data_xml.value('(event/data[@name="state"]/text)[1]', 'varchar(256)')) as 'component_state',
	data_xml.value('(event/data[@name="data"]/value/queryProcessing/@maxWorkers)[1]','int') as 'maxWorkers',
	data_xml.value('(event/data[@name="data"]/value/queryProcessing/@workersCreated)[1]','int') as 'workersCreated',
	data_xml.value('(event/data[@name="data"]/value/queryProcessing/@workersIdle)[1]','int') as 'workersIdle',
	data_xml.value('(event/data[@name="data"]/value/queryProcessing/@pendingTasks)[1]','int') as 'pendingTasks',
	data_xml.value('(event/data[@name="data"]/value/queryProcessing/@hasUnresolvableDeadlockOccurred)[1]','int') as 'hasUnresolvableDeadlockOccurred',
	data_xml.value('(event/data[@name="data"]/value/queryProcessing/@hasDeadlockedSchedulersOccurred)[1]','int') as 'hasDeadlockedSchedulersOccurred',
	case when data_xml.exist('(event/data[@name="data"]/value/queryProcessing/blockingTasks/blocked-process-report/blocked-process)') = 1 then
		data_xml.query('
		let $items := *//blocked-process-report/blocked-process/process/inputbuf
		return
		<blocked total="{count($items)}">
			{for $item in $items return
				<unique> {$item} </unique>}
		</blocked>
		')
		end as 'blockedProcesses',
	case when data_xml.exist('(event/data[@name="data"]/value/queryProcessing/blockingTasks/blocked-process-report/blocking-process)') = 1 then
		data_xml.query('
		let $items := *//blocked-process-report/blocking-process/process/inputbuf
		return
		<blocking total="{count($items)}">
			{for $item in $items return
				<unique> {$item} </unique>}
		</blocking>
		')
		end as 'blockingProcesses',
	case when data_xml.exist('(event/data[@name="data"]/value/queryProcessing/blockingTasks/blocked-process-report/blocked-process)') = 1 then
		data_xml.query('
		let $items := *//blocked-process-report/blocked-process/process[empty(../../blocking-process/process/inputbuf)]/inputbuf
		return
		<blockedByNonSession total="{count($items)}">
			{for $item in $items return
				<unique> {$item} </unique>}
		</blockedByNonSession>
		')
		end as 'blockedByNonSession',
	case when data_xml.exist('(event/data[@name="data"]/value/queryProcessing/blockingTasks/blocked-process-report/blocking-process)') = 1 then
		data_xml.query('
		let $items := distinct-values(*//blocked-process-report/blocking-process/process/@spid)
		return
		<blockers totalCount="{count($items)}">
		{
			for $spid in $items
				let $blockedByResource := *//blocked-process-report/blocked-process/process[@spid = $spid and empty(../../blocking-process/process/inputbuf/text())]/../..
				let $isBlockedByResource := not(empty(*//blocked-process-report/blocked-process/process[@spid = $spid]))
				return
				<blocker spid="{$spid}" is-blocked-by-non-session="{$isBlockedByResource}">
				{$blockedByResource}
				</blocker>
		}
		 </blockers>').query('let $items := *//blocker[not(@is-blocked-by-non-session) or not(empty(*//process/inputbuf/text()))]
		return
			<head-blockers totalCount="{count($items)}">{$items}</head-blockers>
		')
		end as 'potentialHeadBlocker',
	data_xml as 'details'
from data
where [object_name] in ('component_health_result','sp_server_diagnostics_component_result')
  AND
	'query_processing' IN (
		data_xml.value('(event/data[@name="component"])[1]', 'varchar(256)'),
		data_xml.value('(event/data[@name="component"]/text)[1]', 'varchar(256)')
		)
order by timestamp desc

Die wichtigsten Ausgaben sind:

maxWorkers: maximale Anzahl an Worker Threads
workersCreated: Anzahl der erstellten Worker
workersIdle: Anzahl der inaktiven Worker
pendingTasks: ausstehende Aufgaben aufgrund eines Threadpool Wait
blockedProcesses: Anzahl blockierter Prozesse und Angabe dieser Prozesse
blockingProcesses: Anzahl an Prozessen, die andere blockieren und Angabe dieser Prozesse
blockedByNonSession: blockierte Prozesse, die aber nicht von anderen Prozessen blockiert werden
possibleHeadBlockers: blockierte Prozesse, die andere blockieren, selbst aber nicht blockiert werden

Meist ist es einfacher die SQLDIAG Datei zu analysieren, da zu dieser in einem größerem zeitlichen Abstand Einträge hinzufügt werden. So muss man sich durch eine kleinere Menge an Daten arbeiten, verglichen zu dem Extended Event.

Fazit

Mit diesem Wissen und den bereitgestellten Skripts sollten Sie nun in der Lage sein, ihr System auf Threadpool Waits und Deadlocks untersuchen zu können und entsprechende Maßnahmen dagegen zu ergreifen.

Gerne helfen Ihnen unsere Experten speziell zu diesem Thema bei Rückfragen weiter. Kontaktieren Sie uns dafür gerne über unser Kontaktformular. Wir freuen uns von Ihnen zu hören.