July 7, 2008 at 4:06 am
This is upon a confusion I recently came across on the min server memory and max server memory. When I open SSMS, chose the Memory page of server properties and click the help button, I will get the description on the min /max server memory as follows (Ignoring the Max description)-
Minimum server memory (in MB)
Specifies that SQL Server should start with at least the minimum amount of allocated memory and not release memory below this value. Set this value based on the size and activity of your instance of SQL Server. Always set the option to a reasonable value to ensure that the operating system does not request too much memory from SQL Server and inhibit Windows performance.
If I script the changes I do on the page, the script shows that the changes are carried out as follows -
EXEC sys.sp_configure N'min server memory (MB)', N'1'
GO
EXEC sys.sp_configure N'max server memory (MB)', N'2147483646'
GO
RECONFIGURE WITH OVERRIDE
GO
It is obvious that sp_configure is being used.
Now, if I go to the documentation for min/max memory setting at sp_configure, it shows as below-
Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) in the buffer pool used by an instance of Microsoft SQL Server.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/29ce373e-18f8-46ff-aea6-15bbb10fb9c2.htm.
Inside SQL Serevr 2005 Performance eTuning & Optimization also says that the setting is for Buffer Pool.
So, what exactly is the setting for; is that for buffer pool or sqlservr.exe service?
July 7, 2008 at 7:38 am
It's for the buffer pool only.
Next time please post in an appropriate forum. Also, there's no need to bump after 30 minutes. It's a forum, not a chat room.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2008 at 8:00 am
if thats for buffer pool, how can we configure the max memory for the entire service?
July 7, 2008 at 8:12 am
No need. The buffer pool is the majority of SQL's memory allocation. The remaining portion (often referred to as Mem To Leave) is by default 256 MB. You can change that, using the -g parameter, but I strongly suggest that you leave it at the default value unless you have a very, very good reason to change it.
BoL has the following to say
Specifies an integer number of megabytes (MB) of memory that SQL Server will leave available for memory allocations within the SQL Server process, but outside the SQL Server memory pool. The memory outside of the memory pool is the area used by SQL Server for loading items such as extended procedure .dll files, the OLE DB providers referenced by distributed queries, and automation objects referenced in Transact-SQL statements. The default is 256 megabytes (MB).
Incorrect use of this option can lead to conditions under which an instance of SQL Server may not start or may encounter run-time errors.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2008 at 8:41 am
then, where will the procedure cache come in?
July 7, 2008 at 9:14 am
The buffer pool.
You might want to read up here: http://msdn.microsoft.com/en-us/library/ms187499.aspx
August 14, 2008 at 3:21 am
why is the max set so high as a default (2147483647 MB)? If I am getting paging problem would that be because this is too high?
August 14, 2008 at 4:27 am
bodhilove (8/14/2008)
why is the max set so high as a default (2147483647 MB)? If I am getting paging problem would that be because this is too high?
It's actually the server memory in bytes (or it should be). That corresponds to 2 GB (2147483647 / (1024 * 1024 * 1024)), which is the max. virtual memory space without resorting to a /3GB switch or use of AWE memory (both of which require Enterprise Edition OSes) on x86 OS builds. If you are getting paging problems, you can look at reducing this max. value. It is recommended in some cases when SQL Server shares a server with another application. However, if SQL Server is all by itself, you're probably better off looking at increasing memory on the server to at least 3 GB.
K. Brian Kelley
@kbriankelley
August 14, 2008 at 4:36 am
bodhilove (8/14/2008)
why is the max set so high as a default (2147483647 MB)? If I am getting paging problem would that be because this is too high?
The default basically means unlimited (since I doubt there are any servers with 2047 TB of memory). If you leave max memory at the default essentially you're saying that SQL can take as much memory as it wants, with no restrictions.
On 32 bit (without AWE), the virtual address limit (2GB) prevents SQL from taking more than that (assuming there's more in the system), on 64 bit (or 32 bit with AWE) it is a very, very good idea to set the max memory yourself, as it ensures that SQL won't take too much memory and starve the OS (which I have seen once)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 14, 2008 at 4:46 am
This is why I shouldn't reply to posts on 2 hours sleep. Gail's right. Not bytes and GB but MB and TB.
K. Brian Kelley
@kbriankelley
August 14, 2008 at 4:54 pm
hehe.. you had me freakin out there for a minute - I'm glad Gail is right cos I would be in all sorts of trouble!
December 18, 2008 at 4:32 pm
Hi Gail,
We have windows 2003 EE R2 x64 with SP2 and SQL Server 2005 EE x64 with SP2.
We have 16GB RAM.In Our SQL Server instance sharepoint Application databases are sitting. I do not what kind of Queries it (sharepoint)will execute in SQL Server.
Now, we would like to set the Min and Max Memory settings. Could you please advice me what will be the best values considering 16GB RAM and also the page file size.
Thanks
December 18, 2008 at 5:11 pm
Mani (12/18/2008)
Hi Gail,We have windows 2003 EE R2 x64 with SP2 and SQL Server 2005 EE x64 with SP2.
We have 16GB RAM.In Our SQL Server instance sharepoint Application databases are sitting. I do not what kind of Queries it (sharepoint)will execute in SQL Server.
Now, we would like to set the Min and Max Memory settings. Could you please advice me what will be the best values considering 16GB RAM and also the page file size.
Thanks
how many sql instances?
is sql server the only application running on this server?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 18, 2008 at 5:38 pm
Yes, only one instance of SQL server is running
December 19, 2008 at 2:40 am
Mani
i would start with 11GB RAM for SQL and the rest for the OS. Monitor the server and check the memory usage\requirements, adjust as necessary
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply