SQL Server Performance

Large Database Performance

Discussion in 'Performance Tuning for DBAs' started by mgoutham, Feb 26, 2003.

  1. mgoutham New Member

    Hi, <br /> We have a large oltp database (~3GB) on a 7CPU server with 8GB ram.<br /> When I run perfmon, I always see Process<img src='/community/emoticons/emotion-7.gif' alt=':s' />qlsrvr counter pegging to<br /> 100% even though my Processor<img src='/community/emoticons/emotion-4.gif' alt=':p' />rocess Time(_total) is on average 33%.<br /> <br /> How do I make SQL to use of this idle CPU cycles? Also SQLServer<br /> has an active thread count of 124. Will it make any better if I<br /> change this to fiber mode?<br /><br />Thanks,<br />Goutham
  2. bradmcgehee New Member

    The reason you see your Process<img src='/community/emoticons/emotion-7.gif' alt=':S' />QLSrvr Counter pegged is because it is calculating the processor usage based on 7 CPUs, not a single CPU, which is what this counter is designed to show. This is a limination of the Performance Monitor counter. Your Processor<img src='/community/emoticons/emotion-4.gif' alt=':p' />rocess Time (_total) is more accurate and is the counter you should focus on, as it assumes multiple processors. Your 33% average CPU usage is quite good.<br /><br />SQL Server will automatically use as many active threads as it thinks it needs, up to the default value of 255 (unless you have changed this value from the default). Given that your active thread count is only 124, you don't need to worry about increasing this value.<br /><br />The only benefit of using fiber mode is if your CPU utilization is very high, like over 90% or so. As your system is only running at about 33%, turning on fiber mode will actually hurt performance (although this performance hit will be small).<br /><br />------------------<br />Brad M. McGehee<br />Webmaster<br />SQL-Server-Performance.Com

Share This Page