SQL Server Performance

max worker threads

Discussion in 'Performance Tuning for DBAs' started by kbosse, Feb 2, 2006.

  1. kbosse New Member

    I just finished running an audit of the sql server performance using Perf Mon. Following the advice in this article:

    http://www.sql-server-performance.com/sql_server_performance_audit2.asp

    I've run into conflicting information. The Processor Queue Length average is just over 5 in a 2 processor system, which exceeds the 2 per processor recommendation. It is suggested to reduce the max worker threads to take better advantage of thread pooling.

    However, the numbers reflected in my results for User Connections suggests I should increase the number of maximum worker threads to improve performance.

    My processor time averages around 58 - 60 percent during this same time period. I checked the Microsoft site, and according to Q319942 "If you configure a number of worker threads to a value that is greater than the default, it is almost always counterproductive and slows performance because of scheduling and resource overhead. Only increase this setting under very unusual circumstances and when rigorous methodical testing demonstrates that it is useful to do so." So now I'm thinking changing this setting will not do any good either way. Does anyone have some thoughts regarding this? I do not have a test box to give this a go on, so I'm concerned about making changes on a whim.
  2. mmarovic Active Member

    I would rather run trace and identify offending queries that consume too much cpu.
  3. SQLDBcontrol New Member

    Hi,

    first of all, before you think about changing the max worker threads value, read this article: http://blogs.msdn.com/khen1234/archive/2005/11/07/489778.aspx

    Bottom line though, is that just because you have 1000 connections (for example) doesn't mean that those 1000 connections are simultaneously trying to do something.

    You never mentioned how many user connections you actually had but I've had thousands of user connections before without ever having any performance problems and without ever changing max worker threads.

    Your processor queue length is 2.5 (per processor) and the % processor time is 60% - which is quite high for an average reading. I would suggest that your processor is certainly close to the limit of being a bottleneck and I would be inclined to look at this in more detail.

    Hope that helps,


    quote:Originally posted by kbosse

    I just finished running an audit of the sql server performance using Perf Mon. Following the advice in this article:

    http://www.sql-server-performance.com/sql_server_performance_audit2.asp

    I've run into conflicting information. The Processor Queue Length average is just over 5 in a 2 processor system, which exceeds the 2 per processor recommendation. It is suggested to reduce the max worker threads to take better advantage of thread pooling.

    However, the numbers reflected in my results for User Connections suggests I should increase the number of maximum worker threads to improve performance.

    My processor time averages around 58 - 60 percent during this same time period. I checked the Microsoft site, and according to Q319942 "If you configure a number of worker threads to a value that is greater than the default, it is almost always counterproductive and slows performance because of scheduling and resource overhead. Only increase this setting under very unusual circumstances and when rigorous methodical testing demonstrates that it is useful to do so." So now I'm thinking changing this setting will not do any good either way. Does anyone have some thoughts regarding this? I do not have a test box to give this a go on, so I'm concerned about making changes on a whim.

    Karl Grambow

    www.sqldbcontrol.com
  4. joechang New Member

    what is it you are thinking?
    to increase or decrease this value?
    on very large systems (16-32 proc) under certain work loads, increasing max worker threads can help, but no on your box.
    i am not convinced decreasing this value will help either because SQL Server have never started anywhere near the max allowed worker threads

    i would stick to trying to improve the efficiency of your queries than looking for tuning parameters, they are generally a waste of time unless you have lots of time and resources, and you have exhausted the query tuning work
  5. Mast_dba New Member

    Hi
    I think instead of increasing Threads, it better to used fiber facilities of Server. Its increase the performance of server.
    Thanks
    Mast
  6. joechang New Member

    do not apply fiber mode, unless you really know exactly when it benefits, and this not what MS says it is.
    in fact, it really only benefit apps that were not designed correctly in the first place, so fix the app before resorting to fiber mode

Share This Page