Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

"Total Server Memory" & "Target Server Memory" are very low Expand / Collapse
Author
Message
Posted Wednesday, July 2, 2008 1:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:12 AM
Points: 193, Visits: 552
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
Posted Wednesday, July 2, 2008 1:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 6:29 PM
Points: 344, Visits: 290
what is the output of below query?


sp_configure 'max server memory'
go
sp_configure 'Min server memory'
go
Post #527063
Posted Wednesday, July 2, 2008 1:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:12 AM
Points: 193, Visits: 552
We use default value :

min. 0 MB, max : 2147483647 MB


Post #527068
Posted Wednesday, July 2, 2008 1:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 6:29 PM
Points: 344, Visits: 290
Are you saying that SQL is used heavily and still SQL is not using the memory?
Post #527076
Posted Wednesday, July 2, 2008 1:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:12 AM
Points: 193, Visits: 552
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
Posted Wednesday, July 2, 2008 2:02 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 6:29 PM
Points: 344, Visits: 290
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
Posted Wednesday, July 2, 2008 2:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 6:29 PM
Points: 344, Visits: 290
http://support.microsoft.com/kb/274750/en-us
Post #527081
Posted Thursday, September 25, 2008 2:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 9, 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
Posted Thursday, September 25, 2008 4:54 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 6,380, Visits: 13,720
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"
Post #576461
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse