Post reply

Setting max worker threads

  • October 3, 2007 at 8:41 am

    #125870

    Hello,

    I got two SQL Server 2000 Std with 2 GB RAM each.

    Buffer cache hit ratio is 99% in average, and connections at peak time are over 700. The I/O is in the max. We are going to spend some money with new servers, but in the meanwhile I'm looking for some way to improve performance. I guess that changing the default value of max worker threads from the default 255 to something like 500 would improve in some way. But I'm not much experienced and don't know if changing this setting would be worse than better.

    Thanks.

  • October 3, 2007 at 12:21 pm

    #738739

    sql-server-performance has a nice list of articles for setting a baseline / performance

    http://www.sql-server-performance.com/articles/audit/main.aspx

    Buffer cache hit ration 99% -> OK no more memory is needed, otherwise the cache wouldn't maintain 99% hit ratio

    I/O max: add more spindles (harddisk / san luns). Recheck the code. (taking advantage of indexes, avoiding cursors)

  • October 7, 2007 at 9:30 pm

    #739891

    From what I've heard from the PSS guys at Microsoft, don't change the worker threads unless they help you determine this. You can get unexpected results.

    That's not really a place to increase performance. What is slow or what do you want to run faster?

  • October 8, 2007 at 6:46 am

    #739975

    DO NOT increase worker threads - guessing is not a good reason - use umsstats to see if your workers are waiting, my guess is you'll have a bottleneck elsewhere.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • October 8, 2007 at 6:50 am

    #739977

    sorry just noticed you're using std edition so there's not much you improve memory wise. I never use the cache hit ratios - check page life expectancy that'll give a much better indication of memory pressures ( not that you can do anything with STD edition )

    For disks check your io completion time.

    have a read of my basic post here - it may help

    http://sqlblogcasts.com/blogs/grumpyolddba/archive/2007/08/10/what-s-in-a-counter.aspx

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply