October 10, 2007 at 9:03 am
Running SQL Server 2005, SP2, Standard Edition, 64 bit, with 16 GB RAM. Max Server Memory = 14336, Min Server Memory = 0.
I am trying to understand the difference between the ability to lock pages in memory on 64 bit Enterprise Edition and setting the Min/Max Server Memory setting the same on a 64 bit Standard Edition.
We are experiencing a shortage of memory (I believe we are page swapping) and I believe it is either a
separate application, such as Reporting Services or SSIS that is depleting our memory.
So, if this were an Enterprise addition box (instead of Standard Edition), and I had the ability to Lock Pages in Memory, how would this change my situation?
Or, with my current Standard Edition situation, how would setting the Min and Max Server Memory to the same amount change my situation?
October 10, 2007 at 8:02 pm
I guess if I were to take a stab, I would say that if I could lock pages in memory (64 bit EE), then I would not be having this memory issue? Correct?
If I were to set the Min/Max Server Memory to the same value (say, 14336 MB) then I would not be running out of buffer pool memory, but I might be starving the CPU. Correct?
October 11, 2007 at 1:06 am
Lock pages prevents the OS from swapping SQL out of memory if there's memory pressure on the box.
When the OS gets short of memory, it will ask all processes, including SQL to trim their working sets to release memory. If the app doesn't respond fast enough, then the OS can force that processs's working set into the swap file.
If you have SQL been paged out (you'll have messages in the log about that post SP2) then lock pages may help you. If its other processes on the box that are paging, then it won't.
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
October 11, 2007 at 9:46 am
Thanks Gila. That confirms my first question, when having the ability to Lock Pages in Memory in 64 bit EE, essentially that the OS cannot trim SQL Servers working set, because the pages are locked in memory.
However, we are running 64 bit Standard Edition, where the Lock Pages im Memory setting is ignored. If I were to set the Min/Max Server Memory settings to the same value (Min = 14336, Max = 14336), is that how you essentially lock pages in memory in Standard Edition? What is the downside to setting the Min/Max memory settings to the same value, when compared to 64 bit Lock Pages in Memory?
October 12, 2007 at 1:47 am
rerichards (10/11/2007)
Thanks Gila. That confirms my first question, when having the ability to Lock Pages in Memory in 64 bit EE, essentially that the OS cannot trim SQL Servers working set, because the pages are locked in memory.
That's not quite what I said. If lock pages is enabled, the OS cannot page the SQL Server process memory to disk. (to the swap file)
The OS can, and if necessary will, still ask the SQL service to voluntarily trim it's working set (ie, reduce the amount of memory it is using) if there is memory pressure.
However, we are running 64 bit Standard Edition, where the Lock Pages im Memory setting is ignored. If I were to set the Min/Max Server Memory settings to the same value (Min = 14336, Max = 14336), is that how you essentially lock pages in memory in Standard Edition? What is the downside to setting the Min/Max memory settings to the same value, when compared to 64 bit Lock Pages in Memory?
By setting max and min memory to the same, SQL will not trim its working set to less than the min. Hence, if the OS askes for memory to be freed, SQL won't do so.
The OS can still page SQL's memory to disk, if it needs to clear up some physical memory.
The downside is that if the OS needs memoy, SQL won';t give any up, and you may end up getting paged out to disk as a result.
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
October 12, 2007 at 3:05 am
The advice I have had from PSS is never set the max and min memory the same, as this disables some of SQL's memory cleanup processes. Even if you leave a 200MB difference between max and min it will allow SQL to run more efficiently.
If you are seeing memory shortage with SQL max memory set to 14 GB on a 16 GB box, then you may need to reduce your max memory setting. You need to allow enough memory for all other processes on the box when setting your max memory value for the database engine.
If you run RS (and therefore IIS) on the same box as SQL, these can use significant amounts of memory. AS also uses memory, and by default it will try to use all the memory on the box - there are AS settings to control AS memory use.
If you have Enterprise Edition, you can lock SQL memory, but this will not resolve your memory shortage issues, and may make the impact worse by causing additional paging. The ultimate solution is to only ask for what memory is available.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 12, 2007 at 6:59 am
i'd agree that you might want to reduce allocated memory. I've only used 64 enterprise so have no idea about memory settings for std. is the awe box available in 64 bit std?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 12, 2007 at 8:45 am
With proper monitoring you should be able to KNOW if you are having memory pressure. Things like pages/sec, dbcc memstatus, various DMVs can inform you about this. That memory setting is for the buffer pool, and there are a LOT of other things that take memory on the box just inside the sql app space.
My take without further info is that 14 of 16GB is too high for the box you describe with ancillary processes running on the same box. And I too have heard that you should leave some gap between min/max memory.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 12, 2007 at 9:59 am
Thanks to everybody for their input and clarification on Lock Pages in Memory and Min/Max settings.
As stated previously, we have 16Gb RAM with Max Server Memory = 14336 and Min Server Memory = 0, and since we are running 64 bit Standard Edition we cannot lock pages in memory.
So, I get a notification (a low memory warning when free drops below 20%) from one of our monitors that provides the following information:
Memory usage: total:19867.24 Mb - used: 16266.58 Mb (82%) - free: 3600.66 Mb (18%)
Since we have 16GB RAM, from the above memory warning it appears all or most of the 14GB RAM in the buffer pool is used up, that the OS is starved for memory, and we are now using resources from the page file. At least that is what I read from it.
What performance monitors (Pages/sec, Working Set, Available Bytes???) might be used to prove that lowering my Max Server Memory would prove beneficial, or is the aforementioned Memory usage warning notification proof that the Max Server Memory setting should be lowered?
October 12, 2007 at 10:28 am
Clearly you cannot rely on some monitoring product telling you your memory is being inappropriately used since the threshold could be set to any number.
pages/sec is a simeple and effective counter to use. Also search online for dbcc memstatus and memory pressure to get good documentation on the subject. 2005 has DMVs to replace dbcc memstatus.
note that this is a VERY complex topic. I recommend considering professional assistance or training.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 11, 2008 at 1:05 am
Hi,
I am working on SQL Server 2005 Ent. SP2 x64-bit.
Total memory on SQL Server is 16GB and the min and max setting on SQL Server is 12GB.
My issue is when I look into the Taskmanager Processes the memory currently used by SQL Server is just 300MB. and when I look into the Taskmanager Performance it show that 14GB is used.
I am not sure if SQL Server is using all of the 12GB assigned.
Am I missing any setting in 64bit...?? or it's the default behaviour..??
Perf Counters:
Lock Memory: 5120kb
Maximum Workspace Memory: 7,957,280kb
Target Server Memory: 12,288,000kb
Total Server Memory: 7,312,128kb
Thank you,
Sri
August 11, 2008 at 1:11 am
Task manager's a bad place to look at memory for SQL. Rather use Perfmon and look at things like the process's working set or the target and total server memory (former under process, latter under SQL Buffer manager)
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 11, 2008 at 7:11 am
you can also use dbcc memorystatus to see how much memory is being consumed by SQL
August 12, 2008 at 9:19 am
Hi rerichards,
There's some good info on internal and external memory pressure detection on Slava Oks blog. Here's some of the links:
-- http://blogs.msdn.com/slavao/archive/2005/01/26/360759.aspx
-- http://blogs.msdn.com/slavao/archive/2005/01/27/361678.aspx
-- http://blogs.msdn.com/slavao/archive/2005/01/28/362423.aspx
-- http://blogs.msdn.com/slavao/archive/2005/02/01/364523.aspx
-- http://blogs.msdn.com/slavao/archive/2005/11/15/493066.aspx
-- http://blogs.msdn.com/slavao/archive/2005/11/20/495093.aspx
Also, a good book that covers this is 'SQL Server 2005 Practical Troubleshooting The Database Engine' by Ken Henderson, et al. Chapter 3 covers SQL Server Memory Management and, as it happens, was written by Slava Oks.
HTH
August 12, 2008 at 5:39 pm
Thank you for the reply. That clears all my doubts
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply