SQL Server Performance

Stubborn CPU queue

Discussion in 'Performance Tuning for DBAs' started by woke, Feb 22, 2004.

  1. woke New Member

    Howdy

    I have a machine with a stubborn CPU queue :


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

    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?

    I have reduced max worker threads from :
    255 to 230 ( no noticeable change )
    230 to 200 ( appeared to make the server more sensitive to load i.e. the queue length seemed to go to 1 (min) to 11 (max) in length ( average of 3 ) , later it dropped to 1-2 )
    My next step is to drop mwt to 150 & see what happens.

    I have followed this approach after seeing this on the SSP website, but what frustrates me is WHY the queue is so high if the CPU is doing basically NOTHING. Weird.

    Any thoughts much appreciated.

    Cheers,
  2. satya Moderator

    Also monitor the PROCESS counter which gives you the current process alongwith PROCESSOR,MEMORY & other SQL counters.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. woke New Member

    Howdy

    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 -

    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,


  4. satya Moderator

    How about PROCESS counter?

    Thread pooling helps optimize performance when large numbers of clients are connected to the server. Usually, a separate operating system thread is created for each client connection to consume fewer system resources.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. Luis Martin Moderator

    Is the box SQL server dedicated?. I mean are others process runing?
    There is memory not used by SQL (690 of 1Gb).



    Luis Martin
    Moderator
    SQL-Server-Performance.com
  6. satya Moderator

  7. woke New Member

    Howdy,


    (1) Memory settings are - use all 1 GB dynamically for SQL Server
    (2) Process CounterThread Count ( for sqservr.exe ) - processes are 67
    Also, from process Info under Management, there are 55 processes.
    (3) The box is dedicated for SQL Server.

    Also, the box can take another CPU ( taking it upto 2 CPUs )

    Any suggestions welcome.

    Cheers,


  8. Luis Martin Moderator

    Are you using 2 processor for paralelims?


    Luis Martin
    Moderator
    SQL-Server-Performance.com
  9. satya Moderator

    Right how about schedule for database maintenance checks and other DBCC checks to keepup the integrity and performance?

    If you're getting slow response for the queries then you may take help of PROFILER to find them.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. woke New Member

    Howdy

    I ran checks on all the databases & reindexed all table sin all databases but have seen no change to behaviour, sadly.

    What is biting my ass is there seesm NO sensible reason why if CPU is low and disk is not working hard and memory is OK, why why why should the CPU queue be high?

    Now I have seen articles for NT4 that describe a CPU queue > 5 as being a problem ( not 2 ) so I'm just wondering.......I'm pretty sure the W2K & NT4 kernals are similar.

    Also, does anyone know where I can find good DIAGRAMS of the memory structure of SQL and also diagrams of how CPU queues work please?

    Cheers,
  11. satya Moderator

    What kind of issues you're facing currently, any slow queries or process hanging on CPU?

    Read thru Kalen Delaney's Inside SQL Server for more details about CPU queues working methods.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  12. gaurav_bindlish New Member

    When CPU utilization is low, I would first suspect that the disk is running slow. But this does not seem to be the case. What about network parameters? Can that be a reason for CPU waiting on something?

    Just my 2 cents...

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  13. satya Moderator

    Valid point by Gaurav, any virus scanner or other application using OS resources?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  14. gaurav_bindlish New Member

    But that would have been apparent in resource monitor. I believe the posted numbers are for machine and not SQL Server process. Is this the case Woke?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  15. woke New Member

    Hi gaurav_bindlish,


    Yes, the figures are for the machine.

    Heres another odd thing too :

    If I monitor using Perfmon using a term services logon to the box, the queue is 2-3 , sometimes as high as 4-5 and occasionally as low as 1 ( but rarely )

    If I monitor using a remote PC, the queue says almost completely at 1, with occasional small spikes.

    Seriously weird. Its a single CPU PIII, 512 L2 cache, 1.4 GHz , 1 GB RAM.

    Cheers,

    SG
  16. satya Moderator

    Again, try to capture PROCESS counter also when logging with TS client and remotely.
    This will give information about which process is taking too long that may be affecting CPU spike.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  17. woke New Member

    Howdy

    I checked Process % Processor Time and found that the only process was taking CPU time was sqlsrvr.exe with 0.4%. Idle Process was 99.6% %CPU time.

    Cheers,

  18. gaurav_bindlish New Member

    My wuestion about the network performance is still unanswered.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  19. woke New Member

    Howdy,

    Network parameters :

    Network Interface Bytes Total/Sec 11990
    Network Interface Bytes Recieved/Sec 2434
    Network Interface Bytes Sent/Sec 9562

    %CPU 1.9%

  20. Twan New Member

    Hi ya,

    are you actually having performance problems, or just curious as to what might be causing the queue?

    If you are using perfmon over TS, then presumably that would account for two processes in the queue, since both are likely to be active whenever a poll is taken...? TS will want screen updates, and perform would be doing the actual poll

    over what length of time have you seen the Queue being between 2-4, if you monitor for a week, what is the average (using perfmon trace log)

    is it Windows 2000 or 2003?

    Cheers
    Twan
  21. woke New Member

    Howdy,


    Well, no performance problems as such...BUT....most of the literature says that 2 in the queue = trouble. Hence my posts here.....

    When I monitor remote the queue only ever sits at 1 and occasionally has small spikes.
    Other dual/quad CPU servers never seem to have this problem.

    Is it possible the CPU , even on very light CPU loads, just cant cope with the basics of running the box?

  22. woke New Member

Share This Page