SQL Server Performance

Why isn't SQL using all avail memory?

Discussion in 'Performance Tuning for DBAs' started by funkyd, Mar 20, 2003.

  1. funkyd New Member

    Our SQL server has about 20 dbs from 1MB to 50GB.

    Memory usage never really goes above 300MB even though we have 500MB and SQL is set to use all available memory.

    My buffer cache hit ratio is always 98-99% and so no memory bottleneck.

    What I can't understand is why I don't have a bottle neck with such large databases. I thought SQL likes to load as much data into memory as it can? If that is true then our server should be struggling on 512MB?

    I am wondering if there is some option I have missed or perhaps if my cache hit is okay I have nothing to worry about and should let it be. I just think that 512MB is a bit on the low side for such a large system....
  2. satya Moderator

    How does other PERFMON counters looks like?

    _________
    Satya SKJ
  3. funkyd New Member

    quote:Originally posted by satya

    How does other PERFMON counters looks like?

    _________
    Satya SKJ


    SQL Page reads Av = 400/sec
    SQL Page writes Av = 140-160/sec
    SQL Total server mem Av = 317000
    Mem pages/sec = 2/3 Max of 80

    Not been monitoring for too long to be honest so these figures are snapshot rather than over a period of time.
  4. satya Moderator

    Well if your server is not performing well then you can think, if not just leave the settings as it is. In any case if you feel that its lacks performance then come back with events.

    _________
    Satya SKJ
  5. bradmcgehee New Member

    SQL Server uses a sophisticated algorithm to manage memory, and to use it as efficiently as possible. If additional memory is needed, and it is available, SQL Server will make use of it. But if it doesn't need it, then it will give it up. In your case, it appears that even though you have multiple database, and some very large, on your server, SQL Server doesn't feel it is needed. As long as you have SQL Server set to manage memory dynamically, there should be no issue with SQL Server not getting the memory it wants.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page