March 15, 2011 at 12:52 pm
Problem: I want to know how to set SQL Server Instance memory settings as well as Windows Server virtual memory settings. I would also like to actually understand how SQL uses RAM. There are many sources of information, but they seem to be either way too advanced and complicated, or just unsubstantiated opinions. It seems there are many people who don’t actually understand how SQL actually works with memory!
Situation: I inherited a very complex system. It is a 2-node 64-bit Windows 2003 cluster, 72GB RAM on each. The cluster runs two instances of 64-bit SQL Server 2005, each instance set to 30GB RAM for maximum server memory. Instance A normally runs on node 1 of the cluster, Instance B normally runs on Node 2. The thinking was to essentially balance the load, since the Instances are normally running on two different physical machines.
I don’t understand: One of the databases is 200GB+ (and there are many more) yet when I fire up Task Manager and look at the sqlservr.exe processes, they are each well under 1GB. These are heavily used SharePoint databases. How can that be?
Questions:
•How does SQL Server actually use physical RAM?
•Why is my sqlservr.exe process using so little RAM?
•Does SQL ever use Virtual Memory? If so, when? Only when physical memory/RAM is filled up? And then what if that fills up too?
•Does SQL Server always read data from a hard disk into RAM, and then work on it? Such as this: imagine you do a big complex JOIN so you are getting data from a number of tables (and there are no helpful indexes), then you do an ORDER BY to sort it - does all that data need to actually be read into Physical RAM first, and then SQL does a bubble sort or something, all in RAM? What if it is millions of rows? Will SQL try to do it in RAM, then resort to Virtual Memory, then just fail if it all fills up??
•How do you configure Maximum Server Memory for each Instance if you have more than one instance on your server (or cluster, in my case)?
•How much RAM does 64-bit Windows Server 2003 need for itself, and how does its virtual memory settings affect SQL Server?
•I know theoretically running Perfmon and SQL Profiler should allow me to see what these SQL servers actually need. But this seems exceptionally complicated without some sort of guidance as to what counters to look at to determine whether or not SQL is actually using RAM or paging out to virtual memory or whatever.
References:
This seems like a great and relevant article, but it seems very complex, and I cannot find the answers to my questions in this, especially on how SQL uses Physical RAM when it reads data from the hard drive and performs operations on that data...
Buffer Management
http://msdn.microsoft.com/en-us/library/aa337525%28v=SQL.90%29.aspx
This seems to have useful info on how SQL uses memory, but doesn’t seem to provide guidance as to how to set memory settings...
Execution Plan Caching and Reuse
http://msdn.microsoft.com/en-us/library/ms181055%28v=SQL.90%29.aspx
Again these are very general recommendations...
Server Memory Options
http://msdn.microsoft.com/en-us/library/ms178067.aspx
This seems promising but it really seems out of date for 64-bit machines with 72GB RAM...
How to determine proper SQL Server configuration settings
http://support.microsoft.com/kb/319942/EN-US
This seems too general to be helpful...
Optimizing Server Performance Using Memory Configuration Options
http://msdn.microsoft.com/en-us/library/ms177455%28v=SQL.90%29.aspx
This is a Microsoft blogger, it seems very helpful and is what I will probably use as a starting point barring any more concrete recommendations. But he really doesn’t explain his formula. (And this article doesn’t address how SQL actually uses RAM.)
Importance of setting Max Server Memory in SQL Server and How to Set it
Another Microsoft blogger, but with less explanation as to his recommendations...
Q & A: Does SQL Server always respond to memory pressure?
Lots of memory setting recommendations, but really no explanation. No way to determine if they are accurate...
Glenn Berry's SQL Server Performance
A little more explanation concerning buffer cache vs procedure cache, but no references - so it is difficult to decide if this information accurate...
How SQL decides how much procedure cache and how much buffer cache to use.
The most comprehensive book is by Kalen Delaney – Inside Microsoft SQL Server 2005: The Storage Engine. Great book, but it has a ridiculous amount of detail on how SQL Server works with memory, starting on about page 49. But she so quickly gets into the weeds, that I am unable to see the big picture. For example, when she says, “You can use a data page or an index page only if it exists in memory” what does “use” mean (update? sort?) and what does “memory” mean - can it be in virtual memory and SQL can “use” it there?
Inside Microsoft SQL Server 2005: The Storage Engine.
https://www.microsoft.com/learning/en/us/book.aspx?ID=7436&locale=en-us
All help appreciated.
Thanks!!
March 16, 2011 at 6:08 am
NB I am no expert, but I will try to respond to a small subset of your questions (I have changed the order in which they appeared)
For example, when she says, “You can use a data page or an index page only if it exists in memory” what does “use” mean (update? sort?) and what does “memory” mean - can it be in virtual memory and SQL can “use” it there?
"Use" means read and/or modify (of course it has to read it to modify it!). Before you can read the page, it has to be loaded into the buffer cache. SQL Server keeps the pages there for as long as it can for frequently used pages, to reduce the I/O needed to load pages from disk into the buffer cache (Page Life Expectancy object in SQL Server: Buffer Manager perfmon counter is related to this).
jpSQLDude (3/15/2011)
•Does SQL Server always read data from a hard disk into RAM, and then work on it? Such as this: imagine you do a big complex JOIN so you are getting data from a number of tables (and there are no helpful indexes), then you do an ORDER BY to sort it - does all that data need to actually be read into Physical RAM first, and then SQL does a bubble sort or something, all in RAM? What if it is millions of rows?
For large HASH JOINs, SORTs etc, intermediate query operations/results are stored on disk in tempdb (as "internal objects", as opposed to #temptables which would be "user objects"). Of course the pages themselves have to be read into the buffer cache. On some systems I look after, where users are running large reports I see the Page Life Expectancy drop very low over that period as pages are thrown out of the buffer cache to make room for the "next batch"
•I know theoretically running Perfmon and SQL Profiler should allow me to see what these SQL servers actually need. But this seems exceptionally complicated without some sort of guidance as to what counters to look at to determine whether or not SQL is actually using RAM or paging out to virtual memory or whatever.
This won't really answer your question, but the general recommendation for starting points to determine whether there is memory pressure are Page Life Expectancy, Target Server Memory (what SQL Server thinks it needs) vs Total Server Memory (what SQL Server is actually getting), Available MBytes
Source is MCM Christian Bolton in the book SQL Server 2008 Internals and Troubleshooting, free chapter available for download here
I know what you mean though, compared to I/O and/or CPU contention I find memory quite a mystery.
I hope this helped a teensy bit, and that the experts will chime in to put me right where I am wrong
March 16, 2011 at 11:53 am
What little I know of SQL Memory Allocation is this:
1) SQL tries to steal as much memory as possible from the operating system. If you don't throttle it in some way, SQL will try to take 99.999999N% of it. -- I'm exaggerating. SQL will leave enough memory for the OS to actually run, but if you have a ton of memory intensive processes running, expect memory usage to go through the roof.
2) SQL doesn't necessarily use all the memory allocated to it. If you have a minimum memory setting (other than zero), SQL might actually use less that what you're trying it allocates to itself or the minimum set. Don't be shocked, surprised or worried. SQL is smarter than it looks. But it's still a memory hog when it wants to be.
3) SQL doesn't always release allocated memory back to the OS. It tends to horde what it thinks it will need to use.
4) SQL uses physical memory and will (if set to dynamic memory settings) periodically poll the system to see what's available and what it can steal.
5) A setting of 0 in the minimum memory settings allows SQL to be dynamic in how it allocates its memory. That's good.
6) A setting of 0 in the maximum memory settings allows SQL to be dynamic in how it allocates its memory. That's BAD. (See #1)
7) Setting both min & max memory settings to the same number (that's not zero) is also BAD because it doesn't allow SQL Server to allocate and deallocate memory as it needs to. And you can really hamper your OS with that sort of setup. People do it all the time, though.
Does that help?
March 16, 2011 at 4:31 pm
jpSQLDude (3/15/2011)
It is a 2-node 64-bit Windows 2003 cluster, 72GB RAM on each. The cluster runs two instances of 64-bit SQL Server 2005, each instance set to 30GB RAM for maximum server memory.
have you enabled the lock pages in memory local policy for the sql server service account?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 17, 2011 at 7:31 am
1) forums are for targeted, usually straight-forward questions or problems. Your post is neither. You cannot possibly expect to get all the information you request via a forum thread.
2) "I would also like to actually understand how SQL uses RAM". That alone is a HUGE and INCREDIBLY COMPLEX topic.
3) Given that you have a very complex environment to take care of I HIGHLY recommend you get a professional mentor on board for a while to teach you the depth of knowledge you seek (and need). This person will also be able to review your system and offer recommendations for configuration changes, MX operations, etc to improve stability, performance, DR/HA, etc, etc.
4) Having 2 instances running on a cluster brings a significant availability problem if you wind up with both instances running on a single box. If you don't do some things correctly you could wind up not meeting your applications' SLAs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply