July 2, 2008 at 1:27 am
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.
July 2, 2008 at 1:31 am
what is the output of below query?
sp_configure 'max server memory'
go
sp_configure 'Min server memory'
go
July 2, 2008 at 1:37 am
We use default value :
min. 0 MB, max : 2147483647 MB
July 2, 2008 at 1:48 am
Are you saying that SQL is used heavily and still SQL is not using the memory?
July 2, 2008 at 1:54 am
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.
July 2, 2008 at 2:02 am
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.
July 2, 2008 at 2:03 am
September 25, 2008 at 2:40 pm
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!
September 25, 2008 at 4:54 pm
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"
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy