IT Job search

Increase Max Worker Threads?

Standard advice for SQL Server instances with high numbers of user connections is they would benefit from having their max worker threads setting increased. This is good advice, but depends on a basic understanding between the differences of “user” and “worker”. First, I’ll look at the differences between a User Mode Scheduler (UMS) worker and a user. Second, some practical considerations regarding increasing Max Worker Threads First, a UMS worker and a user are two different things. The relationship allows for single worker to service multiple users. UMS exists to control the execution of SQL Server user requests (SPIDs,session_ids). It reduces thread context switching in the OS kernel by keeping the execution in user mode as long as possible. It does this by having a single worker (e.g a thread) service multiple end user connections (users). For a clearer understanding It is useful to consider UMS’ architecture. A UMS scheduler object is a logical abstraction of a CPU. SQL Server creates a scheduler object for each processor to which it has access. These scheduler objects are not directly linked to specific CPUs, but, based on the way Windows spreads a process’s workload across processors, each scheduler usually ends up being indirectly linked to a different CPU. In other words, SQL Server is assigned a time frame from the Windows Scheduler and the SQL Server UMS manages what user requests are run during this time. On SQL Server 2000/ 7.0, a user remains associated with its scheduler until it disconnects. UMS is designed such that it's typical for just one worker to be active on each scheduler at a time. This means that it's common for one worker to carry out the work of many users simultaneously. Second, don’t increase max worker threads unless you’ve carefully considered all the options. A consideration of increasing max worker threads on releases of SQL Server prior to SQL2005 should be based on gathered data, not confusion about the difference between a user and a worker. Checks like DBCC SQLPERF (UMSSTATS), an undocumented command which returns data about SQL Server thread management, should indicate that you’re running out of workers before you even think about something like this. Further, the fact that you're running out of workers doesn't necessarily mean that you should increase max worker threads. If you are really running out of workers, it makes good sense to find out why before you begin adjusting configurations. Increasing max worker threads has some downsides. For example, SQL Server uses max worker threads to calculate the size of the MTL(MemToLeave) region on startup because it must set aside thread stack space for workers. On SQL Server 2000, besides the 256 MB that is set aside for MTL by default, an additional 127.5 MB is set aside for worker thread stacks (255 workers * .5MB stack space per thread). This ~384 MB is reserved, then freed at start-up in order to set it aside for later use. Consequently it’s ignored by the buffer pool, SQL Server’s primary memory cache. Increasing max worker threads, forces up the amount of virtual memory that must be set aside for thread stacks.Therefore, you depriving the bpool of memory it could otherwise use for other resources – index pages, for example. If those extra workers aren’t actually needed, the bpool is constrained without any benefit. Therefore performance is decreased in a way that can be subtle to identify

Digg This : Click to email a colleague Author details ------------------------------------------------------------------------ Jack Vamvas is a SQL Server Consultant. To contact him about this article , or any issues related to SQL Server register on www.ciquery.com and post a question. For SQL Server Consultancy of any SQL Server task - check SQL Server Specialists Make SQL Server go faster - click here