"Total Server Memory" & "Target Server Memory" are very low

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » "Total Server Memory" & "Target Server...

"Total Server Memory" & "Target Server Memory" are very low

Rate Topic

Display Mode

Topic Options

Author

Message

onlo

Posted Wednesday, July 02, 2008 1:27 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 10:34 PM
Points: 187, Visits: 516

Dear all,

My server's configuration :

Windows 2003 Enterprise Edition 5.2 Service Pack 2 3790.srv03_sp2_gdr.070304-2240
Physical memory : 3,407,160 KB
SQL 2005 9.0.3042

There were no difference between "Total Server Memory" & "Target Server Memory"
However, I found both of its "Total Server Memory" & "Target Server Memory" are far below the physical memory : 214960 KB - 605368 KB.

How can we increase these 2 values ?
Do I need to modify the boot.ini file by adding : /3GB ?
Or sp_configure ‘awe enabled’, 1 ?

Please give me advices. Thanks.

Post #527060

Balmukund Lakhani-269523

Posted Wednesday, July 02, 2008 1:31 AM

Old Hand

Group: General Forum Members
Last Login: Monday, April 01, 2013 3:41 AM
Points: 339, Visits: 240

what is the output of below query?

sp_configure 'max server memory'
go
sp_configure 'Min server memory'
go

Post #527063

onlo

Posted Wednesday, July 02, 2008 1:37 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 10:34 PM
Points: 187, Visits: 516

We use default value :

min. 0 MB, max : 2147483647 MB

Post #527068

Balmukund Lakhani-269523

Posted Wednesday, July 02, 2008 1:48 AM

Old Hand

Group: General Forum Members
Last Login: Monday, April 01, 2013 3:41 AM
Points: 339, Visits: 240

Are you saying that SQL is used heavily and still SQL is not using the memory?

Post #527076

onlo

Posted Wednesday, July 02, 2008 1:54 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 10:34 PM
Points: 187, Visits: 516

Exactly.

In fact, its Total & Target Server Memory are far below its Physical Memory, and high in Paging File(Total) % Usage : around 28 %, high PhysicalDisk (Total) % Disk Time and high PhysicalDisk (Total) Avg. Disk Queue Length.

I think it might be due to : low memory usage, thus, the server will use page file very frequently, so, it got a high Paging File % and high Physical Disk % Disk time and disk queue length.

So, I want to know how to increase its Target and Total Memory usage.

Post #527077

Balmukund Lakhani-269523

Posted Wednesday, July 02, 2008 2:02 AM

Old Hand

Group: General Forum Members
Last Login: Monday, April 01, 2013 3:41 AM
Points: 339, Visits: 240

right now, the SQL is set to use dynamic memory. SQL will use memory when it feels it should be used.

Try AWE enabled to 1 and set min and max server memory to around 2 GB.

Post #527080

Balmukund Lakhani-269523

Posted Wednesday, July 02, 2008 2:03 AM

Old Hand

Group: General Forum Members
Last Login: Monday, April 01, 2013 3:41 AM
Points: 339, Visits: 240

http://support.microsoft.com/kb/274750/en-us

Post #527081

kangeles

Posted Thursday, September 25, 2008 2:40 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, March 09, 2009 12:07 PM
Points: 19, Visits: 31

Hi, at first I just thought that perhaps SQL simply wasn't needing to use more memory. Its ok for target/ total to be less than physical RAM.

Then you mentioned the paging. Are you certain it is SQL server that is causing the paging?

What are other counters saying like "page life expectancy" and "buffer cache hit ratio?" If these numbers look normal ~ +300 and +90, respectively, you may not have any problems from a SQL server perspective. Basically, what does the big picture say? Target=Total < Physical in itself isn't a bad thing...

Anyhow, I'm sure you have resolved the situation by now. So, I would like to hear what became of this!

Post #576424

Perry Whittle

Posted Thursday, September 25, 2008 4:54 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 5,204, Visits: 11,165

onlo (7/2/2008)

There were no difference between "Total Server Memory" & "Target Server Memory"
However, I found both of its "Total Server Memory" & "Target Server Memory" are far below the physical memory : 214960 KB - 605368 KB.

How can we increase these 2 values ?
Do I need to modify the boot.ini file by adding : /3GB ?
Or sp_configure ‘awe enabled’, 1 ?

Please give me advices. Thanks.

Total memory should always ideally be less than target memory.

you dont need AWE enabled with under 4GB RAM, so leave that alone.
It may be worth using the /3GB switch in the boot.ini (you could certainly try it).
As already mentioned what do the other counters show?

Balmukund Lakhani (7/2/2008)

set min and max server memory to around 2 GB.

always ensure that min memory is less than max memory never the same or higher

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #576461

« Prev Topic | Next Topic »

Permissions