At #SQLBits, I was quite excited to attend this session because of the presenter. Christian Bolton of COEO (Twitter) was the head author on our upcoming book SQL Server 2008 Internals and Troubleshooting, and he’s one of the only 8 Microsoft Certified Masters on SQL Server that work outside of Microsoft.
Physical vs Virtual Memory
Physical memory means the fast, volatile storage of the chips themselves. We’re finding 64GB is very common these days. If every app tried to access this memory directly, we’d run into performance problems quickly. That’s why virtual address space came about. On 32-bit OSs, the default settings leave 2GB for the kernel and 2GB for applications. On 64-bit OSs, that breaks out to up to 8TB of kernel memory and 8TB of application (user mode) space.
All applications just work with virtual address space first – they don’t know whether they’re working with physical memory or the page file. The allocation of memory to either chips or the page file is the job of the Virtual Memory Manager.
What Uses SQL’s Virtual Memory
The Buffer Pool is SQL’s main memory consumer, and that’s managed by the min and max memory settings in SQL Server.
Another consumer is VirtualAlloc – which just means anything that doesn’t use the buffer pool. The heap manager, thread stacks, and multi-page allocations (things that need more than 1 page to store data). For example, execution plans are normally quite small, but they can exceed one page, and we need to store them in memory. Linked servers and extended stored procs also use VirtualAlloc to grab memory.
The buffer pool will never be bigger than physical memory. If you’ve got 32GB of memory and a 64GB page file, your buffer pool won’t be bigger than 32GB. On an x86 box, this can be problematic since you won’t have more than 2GB of user mode space by default. Because there’s not much memory, SQL Server has to reserve some memory before the buffer pool grabs hold of the rest, and that’s called memtoleave. It’s calculated with MemReserved + (NumThreads * StackSize). By default, MemReserved is 256mb. NumThreads is the max number of worker threads configured. StackSize is .5mb on x86, 2mb on x64. Christian had an excellent visual demo showing how the memory gets used up on different CPUs and memory amounts – I can’t begin to convey that here, but hey, that’s where his great memory chapter in the book comes in.
Christian demoed the RMLUtils to stress test SQL Server and affect the memtoleave space. He asked how many attendees were using wait stats for performance analysis, and the number was about the same as my presentation’s attendees – around 10%. Folks just aren’t using wait stats yet. But if you turn around and ask how many of those 10% use it as their primary troubleshooting tool, it’s usually 100%. Folks who use them, love them, as I’ve blogged before about wait stats.
SQL Server’s Memory Model
Memory nodes are the lowest level allocator, and you can view info about them in sys.dm_os_memory_nodes.
Memory clerks are the next level, and they’re used to access nodes. When something in SQL wants memory, they ask the clerks, and the clerks allocate nodes. There’s a general MEMORYCLERK_SQLGENERAL, and then heavy memory users get their own clerks, like MEMORY_CLERK_SQLBUFFERPOOL and MEMORYCLERK_SQLQUERYPLAN. You can check what they’re using with the DMV sys.dm_os_memory_clerks.
He talked about the different kinds of caches, and you can query them in sys.dm_os_memory_cache_counters. He touched on how the plan cache has hard-coded maximum sizes based on your memory size – you don’t want plans running you out of buffer cache. When you throw in the buffer pool and query memory too (for joins/hashes), it’s easy to see how SQL Server really needs all the memory it can possibly get.
Christian tied it all together by showing a slick DMV query that breaks out cache space usage by database. I glanced through his blog but I couldn’t find it – I’ll hit him up for that later.
Best Practice: Lock Pages in Memory
Locking pages in memory makes sure SQL Server’s memory is not paged out to disk. Otherwise, Windows may push it out to disk – often with bad drivers. The first thing to do when running into SQL’s memory getting swapped out is to check for updated drivers, then tweak down SQL’s max memory setting, then use AWE/lock pages in memory, then consider upgrading to Windows 2008. Even if you have bad drivers, Win 2008 won’t be as aggressive with trimming SQL Server’s address space. Memory allocated using the AWE mechanisms can’t be trimmed. Should you lock pages in memory on 64-bit systems? Yes, because it locks pages in the working set. Only the data cache is locked, though – other memory like the plan cache can still be trimmed.
You can lock pages in memory on Standard Edition, but you need to get on the right patch levels – see this knowledge base article for details.
Christian asked how many DBAs set the max server memory, and I was surprised that the vast majority of DBAs haven’t. I’m a big cheerleader for setting that. Glenn Berry recently wrote a set of guidelines for max server memory. Christian recommended leaving 2GB for the OS, plus the memory for the worker threads we calculated earlier, plus 1GB for the multi-page allocations/linked servers/etc, plus 1-3GB for other applications (if necessary.) This is a worst-case-scenario guideline because we just want to make sure our server isn’t paging to disk.
Bottom line – I learned a lot from this session, and this one alone was worth the price of admission!
24 Comments. Leave new
I’ve had this debate with other DBAs before and I’d like to get some solid answers from you about this…
1) Should I use AWE on 64bit systems?
2) According to your post, I should Lock Pages in Memory on 64bit systems. Am I using AWE on 64bit systems just so I can enable this policy?
Mel,
That is a common question. AWE does not do anything in 64bit mode. You can set it but SQL ignores it. Another source of misconception is usually around the fact that the locked pages in memory uses the awe api. The CSS engineers had a nice blog about it here http://blogs.msdn.com/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx.
-Jeremy
I commend people read what Slava Oks has to say:
“To some people it comes as a surprise that AWE mechanism is still present and actually could be useful on 64 bit platforms.”
http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx
I should have added that I am only trying to promote further study, I’m not advocating anything.
Anything Slava has to say and this post by Bob Duffy are some of my favorite things written on memory:
http://blogs.msdn.com/boduff/archive/2008/03/31/sql-server-memory-architecture-myth-busting-and-how-sad-is-awe-32-bit.aspx
I would be careful on setting Lock Pages in memory on Standard Edition. This is not a feature for Windows Standard and I believe Windows has a bug in it that causes dumps. It is a feature for Windows Enterprise and Datacenter editions only. I had a client 6 months ago that was running SQL Server 2005 Standard on Windows 2003 Standard and had lock pages in memory set. I fixed the dumps for them by removing the Lock Pages in memory setting. They were experiencing memory dumps multiple times a week and now are no longer experiencing any dumps at all.
John – can you elaborate a little more on your statement that “This is not a feature for Windows Standard”? I’ve never heard that before, and when I run secpol.msc on a Windows 2008 and 2003 Standard machine, I have the ability to set this permission. Have you got any documentation showing this isn’t a feature in Windows Standard?
You can look in SQL Books on line
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/62021d0b-1b8d-4276-b373-a01184006d42.htm
If you look under Providing the Maximum Amount of Memory to SQL Server
in the 64 bit section you can see it only applies to Enterprise or Developer.
You can set it still in the security policy but it can’t use it.
I’m not sure if it has changed for 2008 but it still holds for 2005 installations.
In my SQL Server 2005 Books Online, that section says:
“SQL Server 2005 Enterprise Edition supports Address Windowing Extensions (AWE) allowing use of physical memory over 4 gigabytes (GB) on 32-bit versions of Microsoft Windows operating systems. Up to 64 GB of physical memory is supported. Instances of SQL Server running on Microsoft Windows 2000 use static AWE memory allocation, while instances running on Microsoft Windows Server 2003 use dynamic AWE memory allocation.”
That refers to SQL Server Enterprise Edition, but it doesn’t say anything about Windows Enterprise Edition. If yours says something about which Windows edition, can you copy/paste it here? Thanks!
I don’t know what to tell you. It’s in the link I sent you and I experienced it personally about 6-10 months ago. I see an update a few months ago from Microsoft so perhaps they fixed it. I would still be cautious with it personally though knowing how Microsoft bug fixes go.
http://blogs.msdn.com/psssql/archive/2009/05/19/an-update-for-standard-sku-support-for-locked-pages.aspx
John – again, that’s SQL Server, not Windows. Your comment said, “This is not a feature for Windows Standard”. Do you have any documentation that shows the ability to lock pages in memory depends on the Windows edition? I just want to make sure – if there is, that’s something I’d love to raise with Microsoft, because I’ve never seen that before.
It was not a feature previously. Try and find some old documentation and you will see for yourself. I was shocked to find that out at the time I experienced it, and that is why I remember it so clearly today. I wanted to share that experience with everyone. This was a really bad bug and obviously Microsoft felt so too as they have “fixed it” since then. Look on the web. I was not the only one to experience this. SQL Server is predominantly the only app that uses Locked pages in memory and they must have changed the current documentation for both Windows and SQL Server when they fixed the issue on the Windows side. This “feature” is more of an OS group policy setting then a SQL Server mishap anyhow. They couldn’t implement it properly in SQL until the OS was fixed. Think about it. You can reccommend doing it if you like, but I still hold my reservations as it was only a few months ago for me.
John – last chance. It’s not fair for you to say “try and find some old documentation” – that’s not how we do SQL Server best practices and support. Either back up your claims with documentation, or stop spreading urban legends like this around the web. The onus is not on ME to prove YOUR claims.
It’s not an urban legend. Look at the FACTS! I clearly gave you the links from MICROSOFT proving my points.
You gave me two links. The first pointed to your local copy of Books Online. You do understand, don’t you, that the link format ms-help://MS isn’t a web-based link? It points to Books Online on your hard drive. I opened that on my own local machine, and I copy/pasted in the exact content from my machine. I asked you to copy/paste the content from your machine proving your point, and you didn’t do it.
The second link from the PSS blog doesn’t have the word “Windows” anywhere in the entire entry. That entry refers to the SQL Server edition, not the Windows edition.
John,
Lock Pages in Memory is available on all operating systems starting with Windows XP. It is present in the local security policy in Windows 2000 but is non-functional. Here is a link about Lock Pages in Memory in XP:
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/544.mspx?mfr=true
Here is the equivalent documentation for Windows Server 2003. Note that it does not specify editions such as Enterprise Edition or Datacenter Edition:
http://technet.microsoft.com/en-us/library/cc783969(WS.10).aspx
The article you cited was from SQL Server Books Online and was in regards to the SQL Server editions. Previously only SQL Server 2005 Enterprise Edition supported the Lock Pages in Memory option WITH RESPECT TO SQL SERVER. However, because of a known memory bug, that was expanded to also include SQL Server 2005 Standard Edition. This was not speaking of the operating system at all.
Debating the “feature” in the OS is going off on a tangent and is besides the point I was trying to make anyway. You have not proved me incorrect and I can’t find the documentation I found when I was experiencing the issue so let’s just take that out of the equation. You agreed with me that it WAS indeed an issue in SQL Server though, corect? If it was an issue with SQL Server then WHY would it be an issue? The ONLY reason why would be if the OS, GROUP POLICY and SQL Server could not handle the aspect correctly. HENCE IT WAS A BUG!!!!!
It was an issue because it was originally a feature only available in SQL Server 2005 and 2008 Enterprise Edition. Microsoft initially positioned it that way as a reason to get customers to pay for Enterprise Edition, as documented here:
http://sqlblog.com/blogs/aaron_bertrand/archive/2009/04/24/big-news-lock-pages-in-memory-for-standard-edition.aspx
There wasn’t a bug – it just wasn’t enabled for Standard Edition users. Due to overwhelming feedback from users (as documented on that page), Microsoft reversed their position and enabled the feature in the Cumulative Update.
Just because things don’t work the way you want them to doesn’t make them a bug. 😉
No, I did not agree with you that Lock Pages in Memory was CAUSING an issue in SQL Server. Rather, it was ENABLED as a workaround because of a bug. That differs from your scenario.
And one thing to keep in mind. Lock Pages in Memory is an operating system feature. It is not a SQL Server feature. SQL Server provides support for that operating system feature, but any application can do so. It wasn’t put in just for SQL Server. It’s just that we see SQL Server make the most use of it. For instance, Oracle recommends it on x64 systems to get large page support:
http://download.oracle.com/docs/html/B10163_01/ap_64bit.htm
Here is the Microsoft KB article that recommends the use of Lock Pages in Memory to prevent the OS from paging memory away from SQL Server. The only way to prevent that is to allow SQL Server’s service account to be able to lock the pages, keeping them away from the OS:
http://support.microsoft.com/kb/918483
No need to throw down fisticuffs, campadres.
http://www.youtube.com/watch?v=Hjbyo9y9UII
Those who can, do. Those who can’t, teach. I have been doing this long enough to value my own experiences as well as what you can read in books or on the internet. You can read all the books and internet articles in the world and still be completely incompetent in REAL world situations. I was trying to share my experience here and that’s all I was trying to point out.
Love the fisticuffs Chuck.
Man, my first BLOG comment and it’s a heated debate! Gotta love it!!!
Easy with throwing down on people who disagree with you, John.
As they used to say (and maybe still do) at Microsoft: attack the idea, not the person.
Brent is about the most competent and knowledgeable SQL pro as I’ve ever met.
For what it’s worth, I completely agree with Brent.
I wasn’t throwing down on anyone. I’m from Philly and I fight dirty! 🙂
I was really only trying to make a point about how the documentation changed. Since it changed there must have been a reason. Trying to find documentation to prove myself is silly. It seems that Microsoft has fixed the issue since I had it so the point is mute. I had an issue and fixed it the way I did 6 months ago. If it happened today perhaps we would have had a different outcome.
I know Brent is very competent and knowledgeable. We all are or else we wouldn’t be sharing our knowledge and experiences with one another today. It’s the spirit of it all and if you aren’t making people angry then your not doing your job. I am new to this whole Blogging thing and I have a tendency to be very RAW. Your either going to love or hate me, but I will surely have a lot to add.
So long for now. Gotta go put out a fire! It seems someone thinks that doing Referential integrity in cursors is a good idea!!! Oh boy..
I’m from Philly and I fight fair.