Max Worker Threads and when you should change it (SQL Server)


To totally unlock this section you need to Log-in


Login

This is a get started post for this topic and I will be adding more content as and when it is available

Worker threads are the agents of SQL Server which are scheduled in CPU and they carry out the tasks

Memory for Worker threads come from Non-Buffer Pool region of SQL Server.

You can configure maximum worker threads SQL Server can spawn using:

sp_configure 'max worker threads'

If you leave 'Max. Worker threads' to 0 then SQL Server will decide the worker thread count based on formula below:

For 32 bit operating system:

Total available logical CPU’s <= 4 :     max worker threads = 256
Total available logical CPU’s > 4 :        max worker threads = 256 + ((logical CPUS’s - 4) * 8 )

For 64 bit operating system:

Total available logical CPU’s <= 4 :     max worker threads = 512
Total available logical CPU’s > 4 :        max worker threads = 512 + ((logical CPUS’s - 4) * 16)

If you have set Max. Worker thread to 0, you can check the worker thread count calculated by SQL Server using the query:


To totally unlock this section you need to Log-in


Login

1 thought on “Max Worker Threads and when you should change it (SQL Server)”

Comments are closed.