SQL Server Performance

Quantifying memory bottlenecks

Discussion in 'Performance Tuning for DBAs' started by Chappy, Nov 7, 2002.

  1. Chappy New Member

    I have 1GB RAM in my server, to service ~70 users. This server runs ~10 databases, of 12 gig, 4 gig, 2 gig and the rest below a gig. Im pretty sure the server would benefit massively from more memory, but im struggling to quantify this. Ive check perfmon stats, and query traces, but im struggling to differentiate normal disk IO from IO caused by paging. Naturally SQL server consumes most the RAM, but im sure it would do this anyway because it has no reason to give it up (dedicated server).

    What would be the best approach to demonstrating that the server is short of RAM?

  2. HarryArchibald New Member

    I found myself in a similar situation and I used the perfmon executable to log Memory Pages/sec for a day. If this is consistently over 20, then the server is paging and requries more RAM.

  3. satya Moderator

    BTW what are settings for min and max memory?
    Also look from OS side about virtual memory settings, where this plays a vital role in pagefile.

    Satya SKJ
  4. bradmcgehee New Member

    One of the keep factors I look at when determining whether or not a SQL Server has enough RAM or not is to check the SQLServer:Buffer Manager:Buffer Cache Hit Ratio. What is measures is how effective your cache is. If the ratio is over 99%, then your server won't perform any better if you add more RAM. But if the ratio is less than 99%, then most likely adding RAM will help performance, especially if the ration is currently less than 90%.

    If you find that the ratio is above 99%, then your hardware performance problems are most likely I/O related. If this the case, you can get faster hardware (probably not practical) or find some way to tune your queries in order to reduce the load on your server.

    Brad M. McGehee
  5. joechang New Member

    if all you are running on this server is sql, then most likely your server is not paging and the bulk of disk activity is application related.
    if your disk loading is not heavy as indicated by disk queue length or avg sec/read, write or transfer, then most likely the server is not memory limited

Share This Page