One question that comes up quite frequently is how to determine how much physical RAM should be in a database server running SQL Server 2012. One of the first pieces of information you need to determine this is what version and edition of SQL Server you will be running on your new server.
Different versions and editions of 64-bit SQL Server have different license limits for how much physical RAM they can use for different purposes, as shown in Table 1.
| Version | Edition | Engine Limit | SSAS Limit |
|---|---|---|---|
| SQL Server 2008 | Standard | OS Limit | OS Limit |
| Enterprise | OS Limit | OS Limit | |
| SQL Server 2008 R2 | Standard | 64GB | 64GB |
| Enterprise | 2 TB | 2 TB | |
| Data Center | OS Limit | OS Limit | |
| SQL Server 2012 | Standard | 64GB | 64GB |
| Business Intelligence | 64GB | OS Limit | |
| Enterprise | OS Limit | OS Limit |
Table 1: SQL Server RAM Limits by Version and Edition
As you can see from Table 1, SQL Server 2008 could use up to the operating system limit for physical RAM for both Standard and Enterprise Editions. When Microsoft released SQL Server 2008 R2, they had the bad idea (in my opinion) of introducing lower physical RAM limits for both Standard and Enterprise Editions. They also introduced a new Data Center Edition of SQL Server 2008 R2, which had the old, familiar operating system limit as its license limit for physical RAM.
SQL Server 2012 Memory Limits
When Microsoft released SQL Server 2012, they wisely got rid of the Data Center Edition SKU, but they kept the same artificially low 64GB RAM limit for Standard Edition. I have written at length about why this is a bad idea, so I won’t recap those arguments again here. Suffice it to say that is a little ridiculous to be restricted to be using less than $800.00 worth of DDR3 ECC RAM (64GB times $12/GB) in a new two-socket server that may have cost about $10-$15K for the server itself, plus about $29K for sixteen SQL Server 2012 Standard Edition Core Licenses, not to mention your storage costs.
So far, there is no indication that Microsoft has any intention of changing this RAM limit for SQL Server 2014, so we may just have to continue to deal with it. One small positive aspect of this RAM limit is that it is per SQL Server instance, not per server. That means that you could, with a little thought and planning, decide to install two or three SQL Server 2012 Standard Edition instances on the same physical machine to take advantage of all of the machine’s memory, processor, and storage resources, if you are willing to deal with the extra resource usage and management overhead of named instances. This could save you a significant amount of money in hardware and SQL Server 2012 licensing costs compared to having two or three separate servers.
If you decide to just stick to a single default instance of SQL Server 2012 Standard Edition (which I prefer if I am limited to Standard Edition), should you just get 64GB of RAM for the database server? I think the answer is an emphatic no!
SQL Server 2012 Standard Edition Memory Configuration Example
Since server-class, DDR3 ECC RAM is so affordable; I think a reasonable minimum amount of RAM for a new two-socket server is closer to 96GB of RAM. This will let you set the instance-level max server memory setting to 65536MB, which is exactly 64GB of RAM, while leaving plenty of excess RAM for the operating system, and any other applications that might be running on your database server.
The major server vendors have documentation and online memory configuration tools that help make sure you are getting the best memory configuration possible for a desired amount of physical memory. You can also use a tool like CPU-Z to check your system memory speed to make sure it is running at the speed you expect.
For example, Dell has an online configuration tool here. This tool lets you pick the Dell server model you want, including which processor family you will be using (when you have a choice), and the number of physical processors that will actually be in the server. Then you enter how much physical RAM you want in the server, and choose your desired memory reliability options (such as memory mirroring, rank sparing, etc.), and then the tool will come up with three different possible memory configurations: Nominal configuration, Maximum performance, and Balanced configuration. These configurations show the type, number, and size of the memory modules you should use, and what memory channels and slots you should populate to satisfy the chosen configuration option.
If you pick a Dell PowerEdge R720 two-socket server, with two of the new 22nm Intel Xeon E5-2600 v2 (Ivy Bridge-EP) processors and you select 96GB of RAM with no other memory reliability options, you will get three suggested memory configuration options. The Maximum performance option suggests twelve, 8GB 1866MHz 1R x4 RDIMMs, with Slot 0 on Channels 1-4 populated, and Slot 1 on Channels 1-2 populated for each processor. This gives you a system memory speed of 1866MHz, which is the maximum supported by the new Intel Xeon E5-2600 v2 series of processors.
By the way, if you are going to be using SQL Server 2012 Standard Edition, the exact processor you want in a Dell R720 is the Intel Xeon E5-2667 v2, which has eight physical cores with a base clock speed of 3.3GHz. Having a higher core-count model would run into the 16 physical core license limit for SQL Server 2012 Standard Edition.
The Balanced configuration option suggests twelve, 8GB 1600MHz 1R x4 RDIMMs, with Slot 0 on Channels 1-4 populated, and Slot 1 on Channels 1-2 populated for each processor. This gives you a system memory speed of 1600MHz, which will use slightly less electrical power than the Maximum performance option.
The Nominal configuration option suggests twenty-four, 4GB 1333MHz 1R x8 RDIMMs with all Slots on all channels populated for each processor. This gives you a system memory speed of 1333MHz, with all of your slots populated with quite small 4GB RDIMMs. This is a less than optimal choice that is designed to save some money on memory costs by using smaller capacity, lower speed DIMMs. Unfortunately, there is little real monetary savings here, at a cost of giving up a decent amount of memory performance and not having any empty memory slots.
With the Intel Xeon E5-2600 and E5-2600 v2 series processors, you will get the highest possible memory bandwidth if you only populate one or two slots per memory channel. Populating the third memory slot causes a decrease in system memory speed. The highest amount of memory you can use at maximum system memory speed using economical 16GB RDIMMs is 256GB, which would be sixteen, 16GB RDIMMs, populating all slots on Channels 1 and 2. There is no system memory speed benefit from having less than 256GB of RAM in a two-socket server with two Xeon E5-2600 or E5-2600 v2 series processors.
Even though it is better to try to come up with a memory configuration that yields the highest possible system memory speed for a given amount of RAM, in my experience (and in most actual application benchmarks that I have seen), the real world performance difference between different system memory speeds is much smaller than you might expect, quite often in the 5-10% range or less. For SQL Server Enterprise Edition usage, I would rather have enough RAM to fit my entire workload in the buffer pool rather than picking a lower amount of RAM to get the highest possible memory speed at the cost of having to pull data from the storage subsystem more often. Even “slow” RAM has orders of magnitude less latency than any type of storage subsystem, including flash-based storage.
If my workload did not fit into 256GB of RAM, I would rather add more RAM and take the system memory speed hit rather than taking the hit of having to access the much slower storage subsystem more often. So to recap, I think the memory sweet spot for SQL Server 2012 Standard Edition for this type of server is 96GB, rising to 256GB for SQL Server 2012 Enterprise Edition (unless your workload is larger than 256GB).
Today, we have the IT team determining why we need to drop all SQL Server products and switch to Oracle.
They used the Microsoft SQL Server setup requirements of 1GB and raised it to a whopping 6GB in a Virtual Machine.
As you might guess, the response time and performance is horrible.
That minimum RAM and Virtual Machine requirements are missing here.
SQL Server bad performance when 50 users are trying to work is proof enough for the IT team.
Who really cares what maximum RAM is necessary if it can't work for a small division's needs?
It would be nice from a programmer's point of view if Microsoft had more specific data that could be handed to a young IT network administrator.
Where does Microsoft recommend 1 GB for SQL Server? Are you talking about actual recommendations (e.g. 1 GB is ideal), or absolute minimum requirements (e.g. 1 GB is the least you can have for SQL Server to run)? I can survive two minutes without air, two days without water, two weeks without food. That doesn't mean I should only breathe every two minutes, drink every other day, and eat once every other week… :-)
DDR3 ECC server memory is pretty affordable (about $10-$12/GB with 16GB DIMMs). SQL Server (like any database server) runs much better with an appropriate amount of RAM. Ideally, your entire database(s) will fit in RAM, but not everyone can afford to do that.
Most people with any I.T. experience, even if they have no experience with a database server, usually understand that memory is important for good server performance.
The following guidelines from Microsoft for SQL Server 2012 state:
"At least 4 GB and should be increased as database size increases to ensure optimal performance."
Source: http://msdn.microsoft.com/en-us/library/ms143506.aspx
Is the consensus to get enough RAM for your entire DB to fit into memory plus OS requirements etc if you can afford it? Otherwise I'm not finding any guidelines for how much RAM is needed per unit of database size.
Yes, you should try to have enough physical RAM so that the active part of your database(s) fit into memory if possible, leaving some extra for the operating system and other SQL Server components (besides the Buffer pool). Modern servers have pretty high memory capacities, and DDR3 ECC RAM is quite affordable (when you use 16GB DIMMs).
For example, 256GB of RAM would cost about $3,000.00, which is a relatively minor cost compared to what your SQL Server Enterprise Edition license costs are likely to be.
Thanks for clarifying.
Please for your advice regarding this scenario:
1- SQL 2014 Business Intelligence Edition
2- DB engine, SSAS and SSAR on the same machine
Can I use the RAM 128GB*2 (One for DB engine and one for SSAS)
Regards