Results 1 to 14 of 14
  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    220

    Unanswered: Perf Tuning question - gurus only

    Howdy

    I have a server that has the following average readings :

    No. CPUs = 1
    % CPU = 2
    System\CPU Queue Length = 2 to 4
    SQL Server:Buffer Manager\Buffer Cache = 99.85%
    RAM in the box = 1 GB
    Memory\Pages/sec = 1 to 5
    SQL memory in use ( using Task Manager ) = 250 MB
    Max worker threads = 255
    Average number of connection = 60

    So...........all indicators are that the CPU is idling, there is way enough RAM but we still have a ( in theory ) a congested CPU as the queue length is over 2 consistantly. Thing is, I need to work out if the CPU isnt working hard as the queue is long, or whether we can put extra databases/load on the box.

    As the max worker threads are greater than number of connections ( 60 vs 255 ) we could reduce these as the number of users doesnt seem to alter much. BUT.......would this make much difference as if the 255-60=195 worker threads arent doing anything much, they shouldnt put any load on the server, right?

    Any thoughts much appreciated.

    Cheers,

    SG.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hey...leaves me out....but a scan is a scan is a scan....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Number of worker threads hardly ever matches number of user connections. You show the max number, what is your actual count of threads being used by SQLserver? You can get this by running perfmon, choosing process 'SQLServr' and looking at thread count.
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  4. #4
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy,

    * Thread count ( from TM ) is 34
    * Processes ( from within EM ) is 44

    Currently the CPU queue length is about 2.3.
    Now I did change the max worker threads from 230 to 200 on friday and when I can in first thing this morning the CPU queue length was about 4-5.

    Odd, as I thought reducing the max worker threads should reduce the queue given the CPU is only 1% utilisation......

    Any thoughts welcome...

    Cheers,

    SG.
    Last edited by sqlguy7777; 02-22-04 at 19:01.

  5. #5
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    The CPU queue length is the current value and does not represent an average. In your original post you have it as an average of 2-4, how long of a period was that and was that the same period where the average CPU% was that low?

    here are two links that may help you:

    http://www.sql-server-performance.com/q&a2.asp

    http://www.sql-server-performance.co...r_counters.asp

    And there is a new book out entitled "The guru's guide to SQL Server architecture and internals" by Ken Henderson. I am working my way through it right now and it is pretty darn good.
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  6. #6
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy

    I looked at those articles you mentioned in your previous post.

    Here are some values from Perfmon :

    Counter min max average
    % Processor Time 0.1 26 2.59
    Processor Queue Length 1 9 2.6
    Contect Switches/sec 476 4216 545
    SP Recompiles/sec 0 1 0.1
    CPU % Priv. time 0 26 0.5
    Average Disk Queue Length 0 0.2 0.017
    % Disk Time 0 5.6 0.6
    SQL Target Server Memory 692MB 693MB 693MB
    SQL Total Server Memory 605MB 693MB 693MB
    Physical RAM (From TaskMan) - 1 GB -
    Commit Charge(From TaskMan) - 846MB -
    Sqlservr.exe memory usage - 713MB -
    (from TaskMan )
    Number of user connections - 64 -
    Also - the number CPUs in the box is one, but it can take 2.


    Any suggestions? As you can see the Processor Queue is still high and the CPU is much the same although I was noticing the CPU was a little more active that before we lowered the max worker threads to 150.

    Cheers,
    Last edited by sqlguy7777; 02-23-04 at 23:40.

  7. #7
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Unless I'm missing something the only thing that is high is the CPU queue length but your % processor time isn't high. I think they usually say if both of those are high you have a CPU bottleneck.
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  8. #8
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy

    I agree...this is what is doing my head in.....any suggestions?

    Cheers,

    SG

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I also think that you might be mistaking worker threads for number of threads in Task Manager (those two aren't the same!)

    I'd leave the default worker threads alone. Their purpose in life is to process client requests. If you start arbitrarily reducing their number, you may get your server to a "thread starvation" state.

  10. #10
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy


    Good point.

    Problem is though I still cant come up with an explanation as to why on such a lightly loaded server ( CPU = 1 % average ) would I have such a long CPU queue. Its a PIII 1.4 GHz machine.

    Cheers,

    SG.

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Another info I just found states that the threshold for CPU Queue Length is ">5 sustained over 30 mins" (check this site, though it's for NT 4.0)

  12. #12
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy


    Is there much difference between the NT4 & 2000 kernal architecture? If not, then your article would make sense then.....

    Also, does anyone knwo where i can find good DIAGRAMS of how SQL manages memory, CPU queues etc?

    Cheers,

    SG

  13. #13
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    A good book is "The guru's guide to SQL Server Architecture and Internals" by Ken Henderson - admittedly not through it all yet but it's very informative so far
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  14. #14
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy,

    I bought it last night, great if you are a coder, but as a DBA who doesnt really touch DMO & XML ( although I dont know if I am supposed to - I always thought that was the coders thing to do...) it not helped me much for this situation......

    It doesnt shed any light on how you have a PIII CPU queue that averages 2-4 with no disk activity, no CPU activity, piles of available RAM....its doing my head in.....maybe the CPU is duff.....is it possible the system idle process is exaggerating the CPU queue?

    Any thoughts?

    Cheers,

    SG

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •