Post reply

Memory Counters

  • September 30, 2003 at 3:02 am

    #151938

    There's 2 counters in the SQLServer:Memory Manager Class:

    Total Server Memory

    Target Server memory

    I read this as Target being how much memory SQL would like to use and Total being how much it's using at present. Question being what should the ratio of these counters be. I've read in places that these counters should come close to each other but I'm not quite following that reason. Here's 2 scenarios:

    Scenario 1

    2GB of RAM in server, Target Memory is 1.7GB, Total Memory is 700MB

    This server works fine as I'm assuming SQL doesn't need any more than the 700MB it already has.

    Scenario 2

    512MB Ram in server. Target memory is 512MB, Total memory is 512MB This server is experiencing "not enough memory to execute query" and "cannot allocate locks" errors.

    Sticking some more RAM in the second server will no doubt solve this issue but according to some books my memory counters are fine.

    Slightly confused.....!

  • September 30, 2003 at 8:01 am

    #476370

    quote:

    Question being what should the ratio of these counters be. I've read in places that these counters should come close to each other but I'm not quite following that reason.

    As SQL Server uses memory (usually for cache), the Total will approach Target. I guess the "optimal ratio" would be where the numbers are close after the database applications equilibrate, as that means you didn't waste money on superfluous memory.

    --Jonathan



    --Jonathan

  • October 7, 2003 at 5:55 am

    #477346

    I also thought the two should be about equal, but if you get "not enough memory to execute query", you clearly have not enough memory. Have you looked at the paging counters and the amount of free memory in the system?

  • October 7, 2003 at 7:36 am

    #477367

    Did you run same query on both scenarios and received 'not enough memory to execute query' on Scenario2?

    What is the value of counter of ' SQL Server:Memory Manager Counter: Memory Grants Pending'? when experiencing the problem?

    Have a look at http://support.microsoft.com/default.aspx?scid=kb;en-us;243588&Product=sql2k

    http://support.microsoft.com/default.aspx?scid=kb;en-us;309256&Product=sql2k

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply