Eine auf den ersten Blick verwirrende Einstellung des SQL Servers, nämlich die Maximum Worker Threads, will ich hier etwas genauer beleuchten, denn sie kann bei viel frequentierten SQL Servern dazu führen, dass der SQL Server keine weiteren Threads mehr abarbeiten kann. Um hier einen Performance Flaschenhals zu vermeiden, kann es nötig sein, die Maximum Worker Threads zu erhöhen.
Um die Maximum Worker Threads sinnvoll zu konfigurieren schauen wir uns zuerst die Properties der SQL Instanz an.
Schaut man direkt in der Rubrik Processors sieht man, dass die Standardeinstellung für die Maximum Worker Threads bei 0 liegt. Diese Einstellung wird oft falsch verstanden, da man schnell annehmen könnte, dass der SQL Server in diesem Fall beliebig viele Worker Threads erzeugen kann. Das ist aber nicht der Fall! Es ist vielmehr so, dass das Maximum in dem Fall automatisch vom SQL Server eingestellt wird, wie man anhand dieser Tabelle sehen kann:
https://technet.microsoft.com/en-us/library/ms187024(v=sql.105).aspx
Will man direkt sehen, wie viele Worker Threads auf dem eigenen System maximal existieren dürfen, so kann man diese Info direkt in der DMV sys.dm_os_sys_info einsehen.
|
1 |
SELECT max_workers_count FROM sys.dm_os_sys_info |
In diesem Fall sieht man, dass für diese Beispielinstanz bei der Standardeinstellung 0 eigentlich maximal 576 Worker Threads existieren können.
Maximum Worker Threads ändern
Jetzt wird es spannend. Wir wollen herausfinden ob die Standardeinstellung bei uns Sinn bringt oder ob der Wert für die maximalen Worker Threads erhöht werden muss. Die folgende T-SQL Query liefert uns, wie viele Worker Threads aktuell laufen und Berechnungen durchführen:
|
1 |
SELECT COUNT(*) FROM sys.dm_os_workers WHERE state='RUNNING' |
In diesem Beispiel laufen aktuell wie man sieht nur 11 Worker Threads und lässt man den WHERE Teil der Query weg, würde man sehen, dass insgesamt sogar nur 57 Worker Threads existieren. Insgesamt also deutlich weniger als das Maximum von 576 Worker Threads. Daher gibt es keinen Grund das Maximum zu erhöhen. Es ist auch davon abzuraten vom Standardwert abzuweichen, wenn dies nicht nötig ist, da mehr Worker Threads den Verwaltungsaufwand für den SQL Server unnötig erhöhen.
Kommen die verwendeten Worker Threads allerdings sehr nahe (d.h. 80-90% der Maximalen Worker Threads) an die Maximum Worker Threads heran, sollte man sich eine schrittweise Erhöhung der Worker Threads überlegen. Eine Erhöhung sollte dabei immer in möglichst kleinen Schritten erfolgen.
Die Erhöhung kann direkt per T-SQL erfolgen:
|
1 2 3 4 5 6 7 8 |
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'max worker threads', N'600' GO RECONFIGURE GO EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE GO |
In diesem Beispiel wurden die maximalen Worker Threads auf 600 erhöht. Anstatt der 600 lässt sich aber natürlich auch eine andere Zahl einsetzen.
Zu beachten ist auch, dass die meisten SQL Server Installationen keine Veränderung der Maximalen Worker Threads benötigen, aber in den Fällen wo es nötig, kann die Änderung wahre Wunder bewirken. In jedem Fall ist es eine gute Idee die eigenen Systeme einmal zu überprüfen, wie viele Worker Threads hier denn letztendlich laufen. Wer will kann die Prüfung auch automatisieren und beispielsweise periodisch in einem MDW (Management Data Warehouse) aufzeichnen.