SQL Server Performance

2 CPUs

Discussion in 'Performance Tuning for DBAs' started by c_maldon, Mar 1, 2003.

  1. c_maldon Member

    I have a server with 2 CPUs.

    I used the counter "% Processor Time" to watch for each CPU on Performance Monitor (during 24 hour monitoring period).

    Way is always one CPU between 80% and 90% and the other between 10% and 20% all the time.
    How can I do to work both in the same way?
  2. Chappy New Member

    Is the sql server running any other applications or services ? Use task manager to see if the cpu usage is primarily being consumed by sql server.

    Then in Enterprise Manager, right clik the server -> Processor -> Ensure both cpu's are ticked as usable by sql server.
  3. bradmcgehee New Member

    Keep in mind that SQL Server will not always use multiple CPUs in a server evenly. There are a lot of factors that affect this, but what you are seeing is more or less expected (assuming that you are using the defaul affinity setting, which allows SQL Server to use both CPUs). While you have control to specify which CPUs can't SQL Server can't use, you can't make SQL Server use CPUs evenly. This is all done internally.

    Instead of watching each CPU individually, instead watch them as a total, which will provide you with more useful data for performance tuning.

    Brad M. McGehee
  4. thomas New Member

    Also bear in mind that it may not be SQL Server causing the uneveness. Some documentation recommends if you have a multi-processor server, e.g. 8 processors, that you only let SQL Server have 7 and leave one for "DPC activity" which I believe is some kind of low-level interrupt thing that Windows does. So it might be something like that, not SQL Server, which is causing the behaviour you see.<br /><br />In your case, I'd let SQL Server use both processors. A useful counter to monitor is System&gt<img src='/community/emoticons/emotion-4.gif' alt=';P' />rocessor Queue Length. If you've got queueing for processor, you have a bottleneck. But if your processors are working hard, but hard enough, and there is no queue (backlog), you should not be unduly concerned. In my experience, disk I/O is more likely to produce performance issues.<br /><br />Tom Pullen<br />DBA, Oxfam GB
  5. gaurav_bindlish New Member

    Also you can see if increasing the number of worker threads helps to resolve the issue

  6. bytehd New Member

    Adding CPUs does not automatically mean the APP will use them.
    I remember these discussions back when Novell added SMP to Netware in 1993.
    Only IPX and Groupwise used extra CPUs.
    (Now the whole darn OS and ALL Apps use them)

    When I added my 2nd pIII-800 to my Dell 1400, I NEVER got
    CPU #2 to 100% running every query my apps could muster.
    Doing DTS or running IIS (dont get me started) would peg it though....

Share This Page