August 12, 2010 at 3:15 am
Hi there
In one of your blog posts (http://www.sqlservercentral.com/blogs/glennberry/archive/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008.aspx), the suggestion is to set the max memory to 68000 for a server with 72GB memory.
We have set the max memory option to 64000 when the server was installed not too long ago. The server is used for BI & reporting purposes, thus uses a lot of memory and starves the operating system quickly.
Setting the max memory to 64000 did not help however, SQL keeps starving the OS and keeps changing the max memory - it was (re)set to 69268 this morning and OS is struggling.
Why is this happening? Why does SQL change it's own config settings and not keep that what it has been set to?
Server: Dell PowerEdge R710
Memory: 72GB
OS: W2008 ENT x64
SQL: 2008 SP1 STD x64
Any information will be MUCH appreciated.
Regards,
Theresa
August 12, 2010 at 9:44 am
How did you set up the Max memory option? Did you use the GUI or did you use sp_configure?
-Roy
August 12, 2010 at 12:51 pm
Was the SQL Server Service restarted? That is a requirement.
August 12, 2010 at 1:09 pm
You do not need to restart the SQL Server service if you are using sp_configure with reconfigure option and override.
-Roy
August 13, 2010 at 12:46 am
Memory was set with sp_configure.
Services was not stopped and started as it is not required, SQL immediately adjusts memory when using reconfigure in 2008, which it did with no problem when I re-set it again, this time to 62000.
So it works and it is set correctly and I can see SQL only using 65GB on the server, which is perfect - my concern is not in setting the max memory, but setting the memory and then SQL adjusting the setting to some strange setting.
Not sure if it's going to happen again, but I hope not - never had this issue in 2005, would think 2008 would be better. 😉
August 13, 2010 at 1:41 am
The Max Server Memory setting is the total size of the buffer pools only - it doesn't include other uses that SQL Server has for memory.
From http://support.microsoft.com/kb/321363 (specific to 2005 and earlier, but you get the idea):
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
It sounds like you're using a fair bit of non-pooled memory, so examining the sys.dm_os_memory_clerks DMV may come in handy with something like:
SELECT * FROM sys.dm_os_memory_clerks
ORDER BY single_pages_kb + multi_pages_kb DESC
Either you have some object that is misbehaving (memory leaks, etc), or your system just needs extra space (3-5 GB) to perform its usual processing (large XML parsing, CLR.NET functions, etc?).
August 13, 2010 at 2:35 am
TDP (8/12/2010)
Setting the max memory to 64000 did not help however, SQL keeps starving the OS and keeps changing the max memory - it was (re)set to 69268 this morning and OS is struggling. Why is this happening? Why does SQL change it's own config settings and not keep that what it has been set to?
My guess is that someone has set up Policy Based Management, which is resetting the max server memory value from time to time.
Check with your colleagues. SQL Server (PBM aside) does not change memory configuration settings itself.
Failing that, check the SQL Server log for entries saying that the max server memory (MB) setting has been changed, and correlate that to the time a SQL Server Agent job runs (for example).
Either PBM or a job is doing this. Unless one of your colleagues is trying to mess with your mind 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 13, 2010 at 3:03 am
Ah, Paul raises a point that I missed, as it's not too clear. Is the Max Server Memory setting actually changing, or is the memory usage of the server changing? If the former, then this should be a great story 🙂
August 13, 2010 at 3:15 am
Thanks Paul. I will look into your suggestions.
Jim, yes, it is the actual setting for the max memory that changed. 🙂 It is also very odd that it was set from 64000 (which I set it to) to a very odd number, 69268 - which is not generally something a human will set it to. 🙂
August 15, 2010 at 5:11 pm
Set the min and max. Also do the windows lock in memory. Without this set I noticed that setting the max did nothing. Hope that helps.
August 15, 2010 at 6:22 pm
TDP (8/13/2010)
Thanks Paul. I will look into your suggestions.Jim, yes, it is the actual setting for the max memory that changed. 🙂 It is also very odd that it was set from 64000 (which I set it to) to a very odd number, 69268 - which is not generally something a human will set it to. 🙂
Cool, we can disregard my first post. I'd agree with Paul regarding a colleague or PBM. You've mentioned BI, so it's unlikely an application is deciding to play with your server configuration for you - but I have seen apps do this sort of thing (a good reason for not letting applications use the sa account!)
69268 sounds like a value I'd use, as 69268/1024 = 67. That is, there's 1024 MB in a GB, so 67 GB = 69268 MB.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply