Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
onlo
onlo
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 612
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.
Balmukund Lakhani-269523
Balmukund Lakhani-269523
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 367
what is the output of below query?


sp_configure 'max server memory'
go
sp_configure 'Min server memory'
go
onlo
onlo
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 612
We use default value :

min. 0 MB, max : 2147483647 MB
Balmukund Lakhani-269523
Balmukund Lakhani-269523
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 367
Are you saying that SQL is used heavily and still SQL is not using the memory?
onlo
onlo
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 612
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.
Balmukund Lakhani-269523
Balmukund Lakhani-269523
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 367
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.
Balmukund Lakhani-269523
Balmukund Lakhani-269523
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 367
http://support.microsoft.com/kb/274750/en-us
kangeles
kangeles
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
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!
Perry Whittle
Perry Whittle
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11299 Visits: 16844
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" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search