Memory Counters

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Performance Tuning » Memory Counters

Memory Counters

Rate Topic

Display Mode

Topic Options

Author

Message

beath

Posted Tuesday, September 30, 2003 3:02 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 24, 2012 8:08 AM
Points: 257, Visits: 475

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.....!

Post #16791

Jonathan

Posted Tuesday, September 30, 2003 8:01 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, September 23, 2009 9:40 AM
Points: 940, Visits: 54

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

Post #81472

joachim.verhagen

Posted Tuesday, October 07, 2003 5:55 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 28, 2012 1:44 AM
Points: 224, Visits: 106

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?

Post #81473

Allen Cui-55137

Posted Tuesday, October 07, 2003 7:36 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, November 02, 2005 11:49 AM
Points: 2,611, Visits: 1

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

Post #81474

« Prev Topic | Next Topic »

Permissions