Top 13 SQL Server Mistakes and Missteps – #11 Memory Settings
#11 – Memory Settings
Unless you have been working with SQL Server for a while, you may not go into the Server Properties or Facets and tweak much of anything after a successfull install. After all, there are a great deal of things out there and it can be really confusing. Let’s keep it really simple then shall we? In terms of memory settings for Microsoft SQL Server:
- Don’t let SQL Server have all the memory it wants.
- Don’t starve SQL Server for memory it needs.
There, move onto the next item in your Google Reader. I’m sure the there is a new lol cat just waiting to be read.
What? You want a few more details? Ok then.
Min Memory and Max Memory
The two settings I’ll cover here are the Min Server Memory and Max Server Memory. These settings pertain to the amount of memory dedicated to the SQL Server Buffer Pool. Extended stored procedures and other constructs may push the amount of memory used by SQL Server past the max server Memory setting configured in the facets for the SQL Server instance. After installation in a default setup, Min Server Memory will default to 0 mb. Likewise Max Server Memory defaults to 2 Tb ( 2147483647 Mb). This is regardless of the edition of SQL Server you install, and whether you install 32 or 64-bit versions of the O/S and SQL Server.
The four factors you need to consider most when configuring your settings for Min and Max Server Memory are:
- The bit-version of the O/S and SQL Server
- Whether the server is dedicated to SQL (no applications hosted on the server alongside the RDBMS)
- The total amount of memory installed on the server
- How many instances of SQL Server are running on the server
Bit Version
32-bit systems are losing relevance; right along with cassette tapes, parachute pants, and my visibility to women in their 20’s. However, there are still 32-bit systems out there so we must address what to do with them in terms of configuring SQL Server memory. The important thing to understand is that when dealing with 32-bit systems, SQL Server can only directly address 3GB of memory. You can pile on as much memory as the server will allow, but in order to utilize the memory within SQL Server you need to enable Address Windowing Extensions (AWE) and set the Physical Addressing Extensions switch (/PAE switch) on the server. I am not going to rehash all of the wonderful posts out there by people far smarter than I about what the ramifications, processes, and effects are of 32-bit / AWE / and PAE. I will point you to the following links on the subject though:
- Enabling AWE Memory for SQL Server
- SQL Server Memory Options
- CSS Blog: Fun With Locked Pages, AWE, Task Manager, and the Working Set…
The important thing to take away from dealing with 32-bit servers: if you plan on utilizing more than 3 Gb of memory for SQL you’ll need to enable AWE, flip the /PAE switch, and grant the SQL Server service account the local policy right to Lock Pages in Memory to allow it to swap memory between SQL Server and the VAS (virtual address space). Furthermore the memory allocated through AWE is only able to be utilized by SQL Server’s buffer cache.
64-bit systems have none of this mess to deal with. 64-bit SQL can address the O/S limits for RAM without the need to deal with AWE. Just like 32-bit environments though: you need to grant the SQL Server service account the ability to Lock Pages In Memory local policy on the server.
Dedicated Database Server?
Unless you can’t avoid it, your SQL Servers should be just that – servers dedicated to SQL. Co-hosting applications on your database servers should be avoided at all costs. Unless you can’t afford otherwise, or like me, you deal with a great number of vendors who just don’t understand that databases can applications can speak across a network, you should never host applications and databases on the same server. That being said, what happens if you do? It simply means that when you’re done junk-punching the person who signed off on the order for this product you take into consideration the application’s memory requirements when assessing how much memory to leave for the application and O/S to run on your server. Typically I try to leave 2-3GB of memory for the O/S when setting my Max Server Memory. This accounts for the O/S and any supporting processes such as Anti-Virus, backup software agents, etc. to run on the server. It goes without saying that if there are any applications that would not normally reside on a bare-bones server, their memory requirements need to be subtracted from the amount of total memory that SQL can use.
Total Memory Installed On The Server
As I mentioned, I attempt to leave 2-3 GB of RAM available for the O/S and supporting processes. There is no problem with doing so on a server with 32 or 64GB RAM on a 64-bit O/S. However, what happens if you’re working on 32-bit systems with 4 GB of total RAM and you’re also running application on the same server (welcome to my world the last few weeks!) In that case you’re lucky to eek out 1-2GB of RAM for SQL Server. You really need to walk a fine line in those cases and perhaps set your min and max to 1GB and 2GB at most, respectively.
Number of SQL Server Instances
Just like virtualizing servers on VMWare, you can, but should not, over-subscribe RAM. What do I mean by that? Well, let’s say you have 3 instances of SQL Server installed on the server in question. Total RAM for the server is 8GB and you plan on reserving 2Gb for the O/S. That means you have 6Gb to allocate between the 3 SQL instances. If you allocate 3Gb to each of the 3 instances’ Max Server Memory (which you can do) then you have the possibility of running the server out of memory. The wise move is to make sure that the total number of instances on the server are only alotted a maximum of the amount of RAM that is available after satisfying the requirements of the O/S and any non-SQL applications.
The Min Memory Conundrum
Keep in mind that when you set the Min Server Memory setting, SQL may not utilize that memory. It will only consume the minimum amount of memory once it needs to.
There you have it. #11 on the list. I even managed to make it through a blog post without mentioning bacon bikinis, monkeys riding dogs, or any other awesome signs of the impending apocolypse. I promise to try harder in the next post in the series, particularly because it crashes the top 10.