Post reply

Server Performance Tuning - paging, workerthreads

  • November 3, 2006 at 8:47 am

    #114912

    Hi,

    I'm new on the job and have been given the task of improving the performance of a server.  The version is:

    Microsoft SQL Server  2000 - 8.00.2040 (Intel X86)   May 13 2005 18:33:17   Copyright (c) 1988-2003 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: )  - which seems to be SP4.

    We are having some slow application performance and some slow response when working in enterprise manager(or so my supervisor tells me).

    I've gone through a tuning guide and after monitoring hardware performance the memory: pages/m seem to be too high, an average of 240 over 10 hours.  There was some massive peaking overnight, I would guess due to loads.  I also found that the max server memory is set to 6.1gb, and we have 6.5gb.  I'm guessing someone is trying to 'reserve' room for the OS.  Is this standard practice?  The tuning guide recommended 2147483647 - the default value. 

    Also, max worker threads are set to 255 and we are getting 455+ connections at times.  I was going to set this to 460 and hopefully this will improve response time.  Perfmon is showing 200 available mb in memory.  I'm wondering if this is total memory or what has been allocated for sql server. 

    The senior dba seems ready to put these changes in asap, but i'm not so sure!

    Any advice?

  • November 3, 2006 at 9:01 am

    #669822

    I read another post and a guy said he used target server memory and total server memory to see if there was enough memory to increase worker threads - they are about equal on my machine at ~6.1gb.  I'm guessing it would be a bad idead to increase worker threads now without more memory(which will be going in sometime soon).

    extensive thanks!

  • November 3, 2006 at 9:20 am

    #669833

    I have had servers with over 1200 concurrent connections that work fine with the default value for worker threads.  Worker threads are a pooled resource and you might not even be using all that are avaialble. 

    Check out this article http://blogs.msdn.com/khen1234/archive/2005/11/07/489778.aspx

    If response time is an issue you have to do some homework and find out where the bottleneck is.  Most often, the problem can be reduced if not eliminated completely by optimizing a few of your most common queries/transactions.

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

  • November 3, 2006 at 9:32 am

    #669839

    Thanks alot - please consider this post answered.

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

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