New queries assigned to process on Node xxx have not been picked up by a worker

In Microsoft SQL Server 2008 R2, you might get the following error if you run out of threads caused by an incorrect setting, or as in our case,  a poorly-written/designed SSAS query LOL:

New queries assigned to process on Node xxx have not been picked up by a worker thread in the last xxx seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: xxx%. System Idle: xxx%.

I started fixing this problem by first getting on Google and MSDN, and came up with my own formula. Rather than regurgitate someone else’s plagiarism, simply look in Books Online in the max worker threads Option page and do some calculating. Then when you have that figured out, add 20% to that number and then you have a good starting point.  Back it off until whatever you’re doing that SQL Server doesn’t like starts back up, and then you will be good to go.  One thing that I will mention…check out some of the articles that discuss DBCC SQLPERF(UMSSTATS), and see what the threads look like per scheduler, both used and available.

Good luck!

Lee

-----------------------

“Hey what’s up with ‘xxx’ all over this thread?”