April 16, 2010 at 9:47 am
An OS admin is complaining that at times, SQL Server 2005 x64 is using 99% of system memory. The system has 8GB of ram and the max server memory setting is configured as unlimted.
My argument is that's perfectly OK- if the OS or some application requires more memory, SQL server will give it back. Sure, there might be a delay of a second or two, but we have received no "out of memory" errors or anything like that.
Is there any "best practice" recommendation from MS about setting Max server memory? I've seens oem articles out there specifying 75% of OS memory, but nothing from Microsoft... Is it perfectly fine to leave it at unlimted?
April 16, 2010 at 9:51 am
should have mentioned- would really like to have some offiicial MS statement on this that I can use as ammo for those who might argue that this settiing needs to be set... assuming I am correct. Happy to change my opinion if someone can give me a good reason, but just saying "it's using 99%" is not good enough for me.
best statment I can find is as follows:
"Allowing SQL Server to use memory dynamically is recommended; however, you can set the memory options manually and restrict the amount of memory that SQL Server can access. Before you set the amount of memory for SQL Server, determine the appropriate memory setting by subtracting, from the total physical memory, the memory required for Windows 2000 or Windows Server 2003 and any other instances of SQL Server (and other system uses, if the computer is not wholly dedicated to SQL Server). This difference is the maximum amount of memory you can assign to SQL Server."
That should do it I think, but would like to hear some confirmation from the community and or other points of view if they are out there.
April 16, 2010 at 9:59 am
Yes, using x64 SQL Server you really need to set the maximum memory. If you don't, SQL Server will eventually take it all and starve the OS. Depending on what else may be running on the server, you should leave at least 1 to GB of memory for the OS, more if you have other applications running, such as AV, SSIS, SSAS, SSRS, SSMS (if you need to run it locally for any reason), etc.
April 16, 2010 at 10:09 am
OK- I have heard that before- can you tell me why that is the case?
My understanding from all SQL Server documentation is that SQL Server will dynamically allocate memory based on the needs of the system- so if some application- like AV or some other app, requires more memory, SQL server will yeild that back to the OS.
Is that not the case? Is there any documentation that says otherwise?
April 16, 2010 at 10:23 am
No links I can point you to, just experience with our move to x64 hardware. I failed to set maximum memory on one of the blades we installed SQL Server 2008 EE, and the sys admin contacted me to let me know that the OS was starving on about 128 MB of memory causing serious performance problems on the blade. Once I set max memory, the problem went away.
April 16, 2010 at 10:30 am
Would you agree with me that SQL Server shouldnt do that? there should be no requirement so set max server memory because as designed, sql server should yeild memory back to the OS as needed?
could we say this is a bug then?
April 16, 2010 at 10:36 am
NJ-DBA (4/16/2010)
Would you agree with me that SQL Server shouldnt do that? there should be no requirement so set max server memory because as designed, sql server should yeild memory back to the OS as needed?could we say this is a bug then?
Not really. I'll have to do some research, but I'm pretty sure that MS recommends that you set the maximum memory setting on x64 versions of SQL Server.
April 16, 2010 at 10:45 am
would love to find that recommendation. best I can find is:
The default memory management behavior of the Microsoft SQL Server Database Engine is to acquire as much memory as it needs without creating a memory shortage on the system. The Database Engine does this by using the Memory Notification APIs in Microsoft Windows.
Virtual address space of SQL Server can be divided into two distinct regions: space occupied by the buffer pool and the rest. If AWE mechanism is enabled, the buffer pool may reside in AWE mapped memory, providing additional space for database pages.
The buffer pool serves as a primary memory allocation source of SQL Server. External components that reside inside SQL Server process, such as COM objects, and not aware of the SQL Server memory management facilities, use memory outside of the virtual address space occupied by the buffer pool.
When SQL Server starts, it computes the size of virtual address space for the buffer pool based on a number of parameters such as amount of physical memory on the system, number of server threads and various startup parameters. SQL Server reserves the computed amount of its process virtual address space for the buffer pool, but it acquires (commits) only the required amount of physical memory for the current load.
The instance then continues to acquire memory as needed to support the workload. As more users connect and run queries, SQL Server acquires the additional physical memory on demand. A SQL Server instance continues to acquire physical memory until it either reaches its max server memory allocation target or Windows indicates there is no longer an excess of free memory; it frees memory when it has more than the min server memory setting, and Windows indicates that there is a shortage of free memory.
As other applications are started on a computer running an instance of SQL Server, they consume memory and the amount of free physical memory drops below the SQL Server target. The instance of SQL Server adjusts its memory consumption. If another application is stopped and more memory becomes available, the instance of SQL Server increases the size of its memory allocation. SQL Server can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.
from:
http://technet.microsoft.com/en-us/library/ms178145%28SQL.90%29.aspx
April 16, 2010 at 10:52 am
Well, what they say and what really happens in the real world sometimes doesn't match. Having experienced the problem, I find it easier to prevent it than argue that it shouldn't happen.
April 16, 2010 at 10:52 am
here is a tiny bit of official documentation about why setting the max memory it all depends on what else you got running on the system
However, applications often use whatever memory is available when they start and do not request more if needed. If an application that behaves in this manner runs on the same computer at the same time as SQL Server, set the max server memory server configuration option to a value that guarantees that the memory required by the application is not allocated by SQL Server.
April 16, 2010 at 10:56 am
Perfect! That is my ammo I can use to justify setting max memory... thanks to you both.
April 16, 2010 at 12:13 pm
Lynn Pettis (4/16/2010)
Yes, using x64 SQL Server you really need to set the maximum memory. If you don't, SQL Server will eventually take it all and starve the OS. Depending on what else may be running on the server, you should leave at least 1 to GB of memory for the OS, more if you have other applications running, such as AV, SSIS, SSAS, SSRS, SSMS (if you need to run it locally for any reason), etc.
I'd make this a valid point for x32 as well. Grab as much as you can for SQL but leave enough for the OS and any other apps that may be running on the server.
-- You can't be late until you show up.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply