July 7, 2009 at 3:49 am
I have seen a trend that most of the DBAs leave value for Maximum server memory: 2147483647. Is this justifiable? I think that this is ok unless and until AWE is enabled. Because when AWE is enabled then the sql server tries to allot maximum allocated memory to the sql server. So as soon as we should enable AWE (of course for 32 bit server), we should change the value of maximum server memory by using sp_configure. Am I Correct? Any headsup!!
Should we also consider configuring following memory options:
1. Index creation memory
2. Min memory per query
I have always seen Index creation memory = 0 and Min memory per query = 1024 configured. Is this correct?
-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
July 7, 2009 at 6:05 am
Your settings are correct (in my opinion). I have seen some DBAs increase the minimum memory per query setting, but if there are many connections you can face memory pressure...
July 7, 2009 at 6:44 am
I'll leave 2 GB memory for the OS
July 7, 2009 at 7:50 am
Hello David,
Is my understanding correct for the first para?
-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
July 7, 2009 at 8:00 am
I think the other settings are fine but the Max Memory one is troubling. SQL has a history and a tendency to use all available memory is many cases, this tends to starve out the OS and cause thrashing. One of the later posters said leave 2GB for the OS, I think that is excessive for most systems. The right number is dependent on a number of factors, but the biggest is what else you are running on that box, I try to leave 256MB free no matter what and usually more. Lets use a theoretical box, 4GB RAM, SQL, SSIS, and SSAS all on the same box, in this case I'd probably set SQL at 2GB and set the TotalMemoryLimit on SSAS to 40 which is 40% of the total memory. Even with this config you could face memory pressure when a BIG SSIS package was running.. Most of the SQL boxes I work with have 8GB or more of memory and only run SQL and SSIS, in that configuration I try to leave 1GB for OS and set SQL to 6-7GB.
But that is me..
CEWII
July 7, 2009 at 8:05 am
Michael,
First you need to clarify 32bit v 64bit as so many systems are moving to 64. Second, I think you are correct for 32 bit. Once you go with AWE (or PAE), then you need to set the max memory for the server. This is so, AFAIK, it will go above the 2GB and page into the AWE space.
The OS needs 1GB up to 8GB of physical RAM, if I remember correctly. After that, or near that, it's using almost 1GB to page above, so you want to make sure that you haven't set the /3GB switch.
July 7, 2009 at 5:42 pm
So far I've had very few compelling arguments NOT to goto 64-bit..
CEWII
July 7, 2009 at 10:04 pm
The only issues I have come across in 64 bit is that there are still some 32 bit components (such as legacy DTS) as well as the Microsoft driver for Oracle is no longer available.
July 7, 2009 at 11:29 pm
If you are on SQL Server 2000 then setting up max server memory before enabling AWE is required sice we do not have dynamic AWE settings .So as soon as you enable AWE it takes the entire workign set for SQL Server in case SQL Server memory is not capped .
Starting with SQL Server 2005 we have Dynamic memory allocation with AWE enabled .So its not going to grab the entire RAM even if max server memory setting is default .But as soon as it reaches the max it will nto trim it back unless OS asks to do so .
with AWE enabled and Max server memory not set , i have sometimes seen the false lazywriter errors "Lazywriter : No free buffers found"
So , i would recommend :
On 32 bit enable /PAE and AWE and cap max server memory .
On 64 bit noting is needed .So its your choice to Cap SQL or not .Use it if you have more than 1 instances .
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
July 8, 2009 at 3:14 am
I've also noted that SQL has exceeded the max settings on a numebr of our severs. See below from kb321363
Note that the max server memory option only limits the size of the SQL Server buffer pool. The max server memory option does not limit a remaining unreserved memory area that SQL Server leaves for allocations of other components such as extended stored procedures, COM objects, non-shared DLLs, EXEs, and MAPI components. Because of the preceding allocations, it is normal for the SQL Server private bytes to exceed the max server memory configuration.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply