July 30, 2007 at 12:57 pm
I don't know about anyone else, but I find memory configuration and monitoring to be difficult and confusing. I've read numerous articles and forum posts about this topic and I always seem to come up with a different answer. Perhaps someone could write an article or direct me to an article that gives end to end best practices on how to configure memory on a sql server.
Here are some of the questions that I have about SQL 2005 memory configurations...
Now, take those questions and ask them for SQL std vs. Ent and 32 bit versus 64 bit.
For my environment, SQL 2005 Standard SP2 x64 on windows server 2003 SP1 x64 with 16 GB RAM, I think the answers to the above questions are the following...
Can someone confirm that this is correct? If so, then I am confused. I have a server with the above configuration with the 2 noted exceptions. I have max server memory configured for 12 GB and the page file is configured for 2-4 GB. However... and here is where the confusion comes in.... in task manager I am seeing the sqlservr.exe process using 15.5 GB, and I have seen it above 16 GB. How can that be if sql's max server memory is configured for a max of 12 GB? Is task manager accurately reflecting memory usage?
Thanks in advance for your ideas.
Regards,
Rubes
July 31, 2007 at 10:52 am
Lock pages in memory is needed if you have AWE enabled (if I remember correctly). http://technet.microsoft.com/en-us/library/ms179301.aspx - these people recommend to enable lock pages in 64-bit.
Their conceptual explanation of AWE: http://technet.microsoft.com/en-us/library/ms175581.aspx
This explanation states that
"Support for AWE is available only in the SQL Server 2005 Enterprise, Standard, and Developer editions and only applies to 32-bit operating systems" - this might be why you're going over the configured maximum?
-- http://dbachman.blogspot.com
August 1, 2007 at 2:35 am
your actions for 64 bit are correct. The most important thing is to set a maximum memory setting with 64 bit sql otherwise you end up with no memory and lots of problems! I'd debate you need such a large page file, especially on a dedicated sql server - always fix the size of your page file and if you can put it on a fast disk array. Haven't used std 64bit but you do need to lock pages in memory on ent 64bit. 64 bit does not require setting awe, although you can if you wish. 32 bit does need awe to be set.
Yes on 32 bit you must have lock pages to use awe - this is normally default on 32 bit but for some strange reason not 64 bit.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 1, 2007 at 7:32 am
Thanks for the input.
Any idea why the sql service memory usage keeps climbing in task manager? I have max server memory set to 12 GB but it's currently at 14.9 GB and steadily rising. Lock pages in memory is enabled.
Regards,
Rubes
August 1, 2007 at 10:23 am
you must set max memory for the sql server, dunno about task manager look in sys.sysperfinfo .. that'll give the real value
select
* from sys.sysperfinfo where object_name = 'SQLServer:Memory Manager';
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 2, 2007 at 6:51 pm
The way I read books online is that you only have to set max memory differently than its default setting is if you have other applications running on your SQL box. The default setting will allocate "optimal" memory.... That is just the way I read this (below from books online).
Specifies the maximum amount of memory SQL Server can allocate when it starts and while it runs. This configuration option can be set to a specific value if you know there are multiple applications running at the same time as SQL Server and you want to guarantee that these applications have sufficient memory to run. If these other applications, such as Web or e-mail servers, request memory only as needed, then do not set the option, because SQL Server will release memory to them as needed. 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 option to a value that guarantees that the memory required by the application is not allocated by SQL Server.
August 5, 2007 at 5:36 am
Hi All,
We have a SQL server standard edition on windows 2003 enterprise edition R2 32bit edition with 8GB memory. SQL was using the default max server memory and crashing once in fortnight. I read in updated books online that standard edition 2005 have AWE feature but it doesn't say anything about lock in memory option if it works for SE or not. thats why I didn't use AWE and used /3GB instead and set the max memory to 2.5 GB. I increased the virtual memory to twice the size of RAM. Nothing helped and it crashed again after 2 weeks of making changes. Does anyone has any suggestions why server keep crashing ? Is it standard edition or I need to fix some configuration?
Regards
RB
August 14, 2007 at 1:59 pm
Ok, I've made some progress on this so I figured I'd share. Setting max server memory does not put a cap on sql's memory usage as one might guess. It only puts a cap on sql's buffer pool (data and procedure cache). Controlling sql's non-buffer pool usage appears impossible.
The following query showed me that the buffer pool usage was around 12 GB, as I had configured.
select sum(multi_pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb + awe_allocated_kb) / 1024 as [BPool with AWE, Mb] from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLBUFFERPOOL'
And this query showed me non-buffer pool usage was around 3.5 GB. Task manager was showing around 15.5 GB for sqlserver process in task manager, which makes sense.
select
sum(multi_pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb) / 1024 as [Non BPool, MB] from ys.dm_os_memory_clerks where type <> 'MEMORYCLERK_SQLBUFFERPOOL'
The Memory Bottlenecks section of this article, http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx, was very useful in bringing this to light.
My next question is what factors control the MEMORYCLERK_SQLSTORENG usage. This is usually the dominant consumer of non-buffer pool memory on my servers, but the consumption does not seem consistent with any other gauge like number of databases or number of connections. I'd like to be able to predict what this value might be on a particular server so that I know how high I can configure max server memory as well as how much load I can put on the server.
select MemoryKB = multi_pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLSTORENG'
Regards,
Rubes
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply