January 25, 2018 at 11:12 am
We have an issue currently and need to pick community's advise on this. Under extended SQL system health, we see maxWorkers="1088" workersCreated="1182". Does this mean SQL exhausting worker thread? Application timeouts and SQL SSMS fail to connect to SQL instance with pre-login handshake error message. Any guidance will be helpful.
<queryProcessing maxWorkers="1088"workersCreated="1182"workersIdle="9"tasksCompletedWithinInterval="100214" pendingTasks="1923"oldestPendingTaskWaitingTime="1203"
hasUnresolvableDeadlockOccurred="0"hasDeadlockedSchedulersOccurred="0" trackingNonYieldingScheduler="0x0">
January 25, 2018 at 11:34 am
It is VERY seldom advisable to mess with that setting.
What does sp_whoisactive show for current activity and spid status?
What changed from when this did type of issue did not exist to when it did?
Configuration of your server/vm is critical and I am guessing suboptimal for your workload. That or your application/database is just horribly bad. Or both ...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 25, 2018 at 12:36 pm
SQLGuru,
thanks for your response. we are going through other stuff and not looking at changing any settings. What I wanted to understand below number, Am I reaching my maxworker thread which cause not allow to connection? WorkerIdle = 9 means only 9 ideal thread to process SQL request?
maxWorkers="1088"workersCreated="1182"workersIdle="9"
January 25, 2018 at 9:30 pm
Not much documentation to be found readily about those elements. But logically what you say sounds correct.
HOWEVER, I would be WAY less worried about the fact that you are over a the number by a few than WHY you are at that level in the first place. Outside of some EXCEPTIONALLY high-scale clients the only times I have come across those levels were with on systems that were way under powered, misconfigured, REALLY bad apps or some combination of those.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply