The query processor could not start the necessary thread resources for parallel query execution

Question

When I process a (relatively large) SSAS cube, I get the following message.

OLE DB error: OLE DB or ODBC error: The query processor could not start the necessary thread resources for parallel query execution.; 42000.

I have recently moved from SQL Server 2005 to SQL Server 2008 R2. On the previous server I did not have this problem.

The cube gets its data from the 'main' SQL Server instance, There are two other 'smaller' instances of SQL Server on the box. SSIS is also installed.

Old Server:

Windows Server 2003 R2, Standard x64 SP2

SQL Server 2005 Microsoft SQL Server 2005 - 9.00.3054.00 (X64)  (Build 3790: Service Pack 2)

2 x quad core processors @ 2.33 Ghz (i.e 8 virtual processors @ 2.33 Ghz)

16Gb RAM, of which 4Gb allocated to SQL Server, 30% allocated to SSAS, 2Gb & 2Gb allocated to other SQL Server instances

New Server:

Windows Server 2008 R2, Enterprise x64 SP1

SQL Server 2008 R2 (Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) (Build 7601: Service Pack 1)

2 x quad core processors @ 2.53 Ghz, hyperthreading enabled (i.e 16 virtual processors @ 2.53 Ghz)

38Gb RAM, of which 12Gb allocated to SQL Server, 30% allocated to SSAS, 2Gb & 4Gb allocated to other SQL Server instances

I was advised to set the Maximum Degree of Parallism to 2, which does resolve the problem but causes other performance issues. HELP!

A similar question was asked on this thread http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/01a48415-7937-48a5-85f5-00654593d853 but never answered.

 

Answer 1

The error message usually happened when your SQL Server was very busy. The error message indicates that SQL Server cannot allocate a new thread for starting the parallel query. To check if your SQL Server has reached to the max worker threads, please follow the steps below:

1. When this issue happens, please first check how many threads your SQL Server process is consuming. You can open Task Manager, add a Threads column to the display list, identify your SQL Server process and check the thread count.

2. Run the following query to your SQL Server instance to check what the value of "max worker threads" is:
select max_workers_count from sys.dm_os_sys_info

3. Compare the two values to see if they are very close or equal.

For 16 CPUs on a 64-bit server, SQL Server can only support maximum 704 worker threads, http://msdn.microsoft.com/en-us/library/ms187024.aspx.

Please also run the following T-SQL to check whether or not the max worker threads value has been changed. We strongly recommend that you keep it the default value 0. If the config_value and run_value are not 0, please change it back to 0 so that SQL Server can best manage the worker threads:

sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'max worker threads'

If SQL Server does not reach to the max worker threads, but the issue persists, probably your SQL Server was experiencing memory problem or a product issue. Before we further diagnose this issue, please post out the following information:

1. All of the configuration information of sp_configure

sp_configure 'show advanced options',1
reconfigure
go
sp_configure

2. Check your SQL Server error log to see if there is any error message logged when this issue happens and post the error message here.

 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help

Answer 2

I got the same error when "Max degree of parallelism" setting was set to 0 (default) on a server with 2 NUMA nodes.

Changing it to ("number of virtual processors" / "number of numa nodes") solved the problem.