Memory Setting for SS 2005 Standard Edition 64 bit on Win 2008 64 Bit Server

Question

Memory Setting for SS 2005 Standard Edition 64 bit on Win 2008 64 Bit Server

Mohan Kumar VS

SSCommitted

Points: 1724
More actions
January 4, 2010 at 8:04 am

#143864

Hi
Kindly let me know the setting Memory Setting for SS 2005 Standard Edition 64 bit on Win 2008 64 Bit Server.
Windows Edition
Windows Server 2008 (R) Enterpriser without Hyper-V
Service Pack 2
System
Processor Quad-Core AMD Opteron (tm) processor 2356 2.45 GHz
Memory (RAM): 15.7 Gb
System Type : 64-bit Operating System
Server Server Properties
Memory : Use AWE to allocate memory enabled
Minimum Server memory in (MB) : 4096 (4gb)
Maximum Server memory in (MB) : 12288 (12gb)
Maximum worker Threads : 0
Boost Sql Server Priority Enabled
But when I execute the following queries, The total Server memory is shown only 4gb in the cntr_value.
SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Target Server Memory (KB)','Total Server Memory (KB)')
select * from
sys.sysperfinfo
Where counter_name
in('Target Server Memory (KB)','Total Server Memory (KB)')
What else settings has to be done so that Sql Server uses Max 12 GB RAM?
Kindly recommend.
Rgds
Mohan Kumar VS

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GRE (Gethyn Ellis) SSCrazy Eights Points: 9602 More actions · Answer 1

Firstly there is no need to to enable AWE on 64bit systems...Thats a setting to allow 32bit systems to access more than 4GB

When you set the Min and Max memory setting in SQL server you are telling the system that it should always consume the Min setting in your case 4GB and not use more than than the Max, in your case 12GB. So when SQL Server starts up it will grab the min amount of RAM specified (4GB) it will dynamically allocate memory as needed up to 12GB...It will only take the extra memory if needed, if it doesn't need it it won't take.

I generally find it is better to let SQL look after the memory allocation but if you really want SQL server to use 12GB you need to set both the min and max setting to 12GB. Just be sure you don't starve the OS or any other process of RAM expecially if SQL is not using all 12GB.

Gethyn Elliswww.gethynellis.com

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 2

I totally agree with Ellis on this. Get AWE off of that instance and ensure your min and max memory settings are what are going to be adequate for the workload the server is handling.

Joie Andrew
"Since 1982"

Mohan Kumar VS SSCommitted Points: 1724 More actions · Answer 3

Thanks for the tips. I will make changes & let you know.

Rgds

Mohan Kumar VS

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

Slight correction in what Ellis said.

Note:

SQL Server is not guaranteed to allocate the amount of memory specified in min server memory. If the load on the server never requires allocating the amount of memory specified in min server memory, SQL Server will run with less memory.

http://msdn.microsoft.com/en-us/library/ms178067.aspx

MJ