SQL Server Performance

More memory and/or another server

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by bryan42, Mar 19, 2007.

  1. bryan42 New Member

    When monitoring the performance of our production SQL Server 2005 system, it's now running much better thanks to help from people here. Now that things are under control, I'm reviewing performance again. Should I consider adding another 2GB of memory to this system? <br /><br />Below are some typical performance specs. The Pages/sec worries me, as I read that it should be close to zero. Looking at the task manager, there's around 50MB of available memory.<br /><br />What do you think? Is this a machine that would benefit greatly with another 2GB?<br /><br />thanks,<br />Bryan<br /><br />2.00GHz 2GB memory system running Windows Server 2003 R2<br />SQL Server 2005 Production machine<br /><br />Object: Memory - Pages/sec101.105<br /><br />Object: PhysicalDisk_Total<br />% Disk Read Time645.743<br />% Disk Write Time2.083<br />% Idle Time37.633<br />Avg. Disk Queue Length6.478<br /><br />Object: Process sqlservr <br />% Processor Time165.000<br />Working Set1707356160<br /><br />Object: Processor_Total<br />% Processor Time83.047<br /><br />Object: SQLServer:Buffer Manager<br />Page life expectancy11.000<br />Page reads/sec1213.159<br />Page writes/sec2.500<br /><br />Object: SQLServer:General Statistics<br />User Connections206.000<br /><br />Object: SQLServer<img src='/community/emoticons/emotion-4.gif' alt=':p' />lan Cache_Total<br />Cache Hit Ratio92.981<br /><br />Object: SQLServer<img src='/community/emoticons/emotion-7.gif' alt=':S' />QL Statistics<br />Batch Requests/sec51.102<br /><br />Object: System<br />Context Switches/sec6127.596<br />Processor Queue Length3.000<br />
  2. Luis Martin Moderator

    Before begin to think in other server I'll add 2GB more.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. bradmcgehee New Member

    Is the data provided above a snapshot of a typical production time period, or an average over time? Also, what is your buffer cache hit ratio? Also, are you generally happy with the current servers' overall performance, are do you still need a performance boost?

    I also noticed that your % Processor time was 83%, and the Process Queue Length was 3. If this is a single CPU server, which it appears to be, and if this is a typical load you have? If so, then I think you either need to reduce your load (by turning queries better and adding necessary indexes), or getting a server with multiple CPUs more memory, and a faster disk IO system. While adding more memory may help, it won't overcome the CPU problem you have. Then again, if this 83% is not typical, then I would provide different advice.

    I don't really have enough data to make a good recommendation. Generally, I want my servers to have a buffer hit cache ratio of 98% or higher, 70% or lower CPU utilization, a processor queue length of 2 or less (per CPU), and Avg. Disk Queue Length of 2 or less per spindle, and a Pages/sec of 20 or less. These are average figures and can vary depending on lots of different circumstances.

    --------------------------------
    Brad M. McGehee, SQL Server MVP
    http://www.sqlbrad.com
  4. joechang New Member

    per brad, my main concern is the high cpu
    adding memory may not reduce cpu

    so does it make sense to add memory to find out afterwards you still need a new machine?

    then again, if this is a desktop system, for which memory is cheap, why not

    on the assumption that the reason for paging is memory contention with other apps
    you might consider setting max sql server memmory to 1500-1600MB, to see if this reduces paging
  5. bryan42 New Member

    Thanks much for your replies. This machine is a dual-core system and that's a snapshot of performance. The CPU ranges from 30% to 80% and probably averages close to 50%. The system runs only SQL Server 2005, no other apps, and communicates with one IIS web server.

    Since the machine is hosted by a provider, increasing to 4GB will cost $75 per month more and sounds well worth it.

    I do have concerns about the CPU. We likely have a lot of cleaning and tuning to do on the SQL and stored procedure areas, but right now I'm looking for any easy fixes such as increasing memory to 4GB. Performance is generally good, but once in a while we get 'server too busy' errors. I'm concerned that a hundred more users could push the system over the edge and cause more errors.
  6. MohammedU New Member

    Run the sql trace and feed the trace to DATABASE TUNING ADVISOR for index recomendations...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  7. WesBrown New Member

    I agree with Joe, Memory can be cheap and easy way to address problems these days.

    The two counters that you put up that I would sweat are these:

    Object: SQLServer:Buffer Manager
    Page life expectancy 11.000

    way way low. Generally I like to see this counter avg 300 or better on a busy machine you may get memory pressure causing large page flushes out of the buffer. There is another counter I like to look at as well that you don't have on your list, Database pages out of the buffer manager. Generally, if you have a high level of turnover this is also a good indicator that you are under memory pressure.

    Object: Memory - Pages/sec 101.105

    Paging is never good, is there anything else running on this server that may be in contention for memory space with mssql?

    Also, giving sql server enough memory to do it's caching effectively can reduce the pressure on your disks by servicing more request from memory instead of having to go all the way back to your slowest component in the server.

    All that said, adding more memory and freeing up the disk can expose a cpu bottleneck that you may also be dealing with as Brad pointed out.

    Cheers,
    Wes

Share This Page