SQL Server wants memory the way a runner wants food after a marathon: it wants it all and it wants it right now. You can configure max memory settings, but will that cover all of SQL Server’s uses for memory? Jes Schultz Borland will uncover the truth about how SQL Server is handling memory in this free 30 minute video. Single page and multi page allocations will be discussed, along with changes in SQL Server 2012. This talk is designed for DBAs with a couple years of experience poking under the hood who are interested in learning more about how SQL uses memory.
Download Scripts from the Webcast for SQL Server Memory Analysis
You can download the .sql scripts used here!
Links from Webcast Q&A
Some great questions were raised during the webcast! To learn more about Lock Pages in Memory, read Brent’s blog, Consulting Lines: SQL Server Needs a Dog. If you’re curious about where to start with your maximum and minimum memory settings, check out our SQL Server Setup Checklist!
Frank Gill June 6, 2012 | 12:41 pm
Jes,
Congratulations on your first tech webcast for Brent Ozar PLF. I’ve got the KB article for DBCC MEMORYSTATUS open and ready to read. Thanks to Jeremiah for the database allocation query.
Mark August 27, 2012 | 3:35 pm
Hi Jes,
Great video! I am interested in multipage allocations as i have a SQL server with 200GB total physical memory and i’ve set a Max Memory Setting for 184GB (leaving 16GB RAM to the OS)….and now i know, to multi-page allocations. I am using SQL 2008R2 64-bit.
However, when i check Task Manager: Physical Memory: Free Memory, the value is extremely low, i.e. less than 400MB.
Does this mean that i have not left enough for multi-page allocations? Should i reduce the max memory setting?
Is there a way in DBCC MEMORYSTATUS to check which multipage allocation(s) is consuming the most memory outside of the 184GB (max mem)?
Thanks!
Kendra Little August 27, 2012 | 8:27 pm
Hey Mark,
I just happened to be in the comments and saw your question.
Slava Oks wrote up a good summary of DBCC MemoryStatus, which includes where it covers what the multipage allocator is using: http://blogs.msdn.com/b/slavao/archive/2005/02/14/372309.aspx
If you’ve left 16GB free and there’s only 400MB available, my first step would be to track down what’s using the difference. It may or may not be SQL Server!
Mark August 27, 2012 | 10:15 pm
Ah so that’s where i’m in a grey area. I know that Max Memory setting is for the buffer pool (data and procedure cache = single-page allocations) and that’s set at 184GB. Now i am trying to figure out how much multi-page allocations is using and if it’s the reason the 16GB i’ve left free is being chewed up…it’s a dedicated db server so i can’t really think of anything else using that 16GB..
Sudha March 27, 2013 | 12:16 pm
Hi Jes, it’s great video, kudo to whole your team.
Need your suggestion how much max memory can i set for each instance in my case?
here the details
OS: windows 2008 R2 server
DB version: sql 2008 R2
Processor core/threads: 2
Logical processors : 24
it’s a 3 node active-active cluster, node1,node2,node3 and allotted memory on each node is 144gb.
total db instances are 16 ( 5 + 5 instances running on 1st and 2nd node, 6 instances running on 3rd node )
Lets assume each db instance load is approximately same.
all nodes are dedicated for database instances only, no other application or reporting (SSIS,SSRS,etc) tools are not installed.
1. how much max memory can suggest for each instance ? and why do you suggest ?
2. what could be best Quorum configuration value ? either node majority or node and disk majority ? and why?
Brent Ozar March 27, 2013 | 12:18 pm
Hi, Sudha. That’s a great question, but with that many variables – especially with 16 instances running on 3 nodes – this is beyond something we can really diagnose in a blog comment. We would want to ask a lot more followup questions and look at DMV output, and that’s probably something you don’t want to post publicly. If you’d like to talk about a personalized consulting engagement, shoot us an email at Help@BrentOzar.com and we can talk about getting you detailed answers for these.
Sorry that I don’t have a quick answer, but when you’re dealing with 16 database instances on 3 nodes, there are no quick answers.
Jonathan April 11, 2013 | 9:04 am
Hello, I loved this post and have been using the queries to get a feel for what kinds of things are consuming the most memory. When I was looking at one of my servers with this query I noticed one thing that was odd, The USERSTORE_TOKENPERM item was using 2,750MB of memory, roughly 30% of what SQL server had in the pool. This number is extraordinarily high compared to the other servers (similar specifications, different workloads). It is also the single largest consumer of memory according to the queries above.
I see references in web searches to a bug with SQL 2005 and the recommendation of using a hotfix or frequent DBCC FREESYSTEMCACHE (‘TokenAndPermUserStore’) but I don’t like that as an option because I’m running a much more recent version of SQL. The other possibility I saw online that specifically referenced a 2008 version was for sql auditing but that is not enabled on this server.
Would it be possible to get some advice on an avenue to pursue?
Details:
Windows 2008 R2 Enterprise (cluster active-passive)
SQL 2008 R2 Enterprise, Service Pack 2 (10.50.4000)
28GB memory for the server.
16GB max memory setting in SQL (no other instances)
This server is going to be destined as one of our SAP SQL Servers although not yet production but installations, configurations, etc. are currently on-going.
Kendra Little April 11, 2013 | 10:14 am
Hi Jonathan,
There are some levers you can pull to adjust how the tokenstore is managed, but all of the recommendations are based upon having a performance problem that you’re trying to solve.
If you don’t have a performance problem you’re solving, you can easily end up in the situation where you start changing things in the hope of preventing a problem, but then cause one later down the road by accident. And that’s not a fun place to be! Since you’re running a completely different workload on the instance than you’ll have when it goes into production, that makes it especially dicey to make long term changes at this point.
Hope this helps,
Kendra