Pop quiz: how much memory does SQL Server 2008 R2 Standard Edition support?
If you’re like me, you’d head to Microsoft’s site and check the SQL Server edition comparison:
Looks pretty straightforward, right?
Wrong. As it turns out, the marketing material is incorrect – both Books Online and TechNet show that starting with SQL Server 2008 R2, Standard Edition maxes out at 64GB of memory.
If you bought any licenses of SQL Server 2008 R2 Standard Edition, now would be a good time to call your Microsoft contacts and start asking what’s going to happen when you add memory to your servers over time. I doubt anyone will get free upgrades to Enterprise Edition, but this is definitely something I’d raise if I was negotiating a company-wide licensing agreement. If the marketing materials say “OS Maximum” and the software you sold me doesn’t support that, then give me something that will.
Note – this doesn’t mean you should only use 64GB of RAM in the server. SQL Server will only use 64GB – but you should still leave some for the operating system, file copies, backup agents, etc. I tend to use 96GB of memory (just because it’s a roundish number).
51 Comments. Leave new
Remember Star Wars?
It was R2D2 not R2. Heh. ;o)
Brent –
Do you know if the 64GB memory limit is for the instance or for the server? In other words, can I have 2 standard edition instances with a max memory limit set to 64GB on each (or slightly less to allow for system memory blocks) running on a server with 160GB memory (as an example)?
I know this is an old post but I would like to know the answer to Scott’s question if anyone knows.
Robert – sorry, I don’t have that answer, but I would highly recommend against running multiple instances each with 64GB of memory.
Thanks for the reply, Brent. I also asked this question over on Glenn Barry’s blog and he replied that the 64GB limit is per instance, not server. And no, I won’t be allocating 64GBs of memory to any one instance. I’m going to be running multiple SQL instances on a Windows cluster and I wanted to know if the total memory allocated to all of the instances could be over 64GB.
http://www.sqlservercentral.com/blogs/glennberry/archive/2011/02/25/some-suggested-sql-server-2008-r2-instance-configuration-settings.aspx
I’m a new DBA and I’ve been watching all of your training videos and have found them very informative. Keep up the good work.
Robert
What specific issues would you run into with stacking multiple SQL Server Standard instances on the same large (4p/256GB) physical server? We have to run two different versions of SQL to support twenty different small applications, so stacking instances is the least cost approach. We could go Enterprise, but that would cost more than the sever hardware itself !
Ted – hmm, what makes you think you can pay for one Standard license and use it for multiple instances? You might be right, just haven’t heard someone try that before.
The MS SQL Licensing Guide requires you to pay for all cores in an OSE only if you install Enterprise. If you install Standard, and you are using Server/CAL licensing, then you pay the per server license for Standard. Standard allows for you to install multiple instances. I am double-checking with MS, but it appears so far that this will be fine and is a correct interpretation.
My only concern is on the technical side. If you install two instances of Microsoft SQL Standard, they are limited to 64GB and 16 cores. It is not clear from Microsoft documentation as to how the limit of 16 cores is being handled and which cores it would choose when you have 32 cores on a server. So the question is more of a technical question as to whether there is any potential technical issue with this configuration – 2 SQL standard instances on a 32-core server.
Ted – “If you install Standard, and you are using Server/CAL licensing” – Bad news. There’s no server/CAL licensing anymore for Standard Edition. You may still be able to get it through some resellers, but that isn’t something I’d have experience with.
The following says Server and CAL licensingare still available with SQL Server Standard 2012 unless something has changed.
http://www.microsoft.com/en-us/sqlserver/get-sql-server/how-to-buy.aspx
I am more interested in whether there is a technical issue with running multiple instances of SQL Standard on a 32-core server.
Wow, interesting! Now I’ve seen several conflicting guides from Microsoft. (sigh) Guess this is why I don’t do licensing work!
Brent, Ted,
In case you’ve not tried it yet – there is no issue with running multiple instances in this way. I set up a cluster recently with 4 instances of SQL 2012 Standard; the memory allocation can be left as default, which implies there’s no limit, but the best way, I would suggest, is to hard – allocate each instance an amount of RAM to prevent swapping of memory between instances; that could slow performance (ie if one grabs all of the RAM because it’s being thrashed, then another needs some more, it’ll struggle to get it back even if the one which grabbed it is now quiet).
For the processor affinity, again, default is to use all – how that actually pans out with >16 cores I don’t know. To completely avoid any possible repurcussions, though, you can simply set the affinity so that each instance has a defined (and ideally different) list to use.
Hmm, I guess this will make me always check BOL over MS marketing. Great find Brent!
Number of processors is incorrect too 🙂
You gunning to lose that MVP Brent? 😉
Brent, thank you for pointing out this discrepancy between the marketing Web page and the product documentation. I am contacting our Web Site Manager to get the page corrected as soon as possible.
Tai Yee
Microsoft
Thanks. I also raised it through the MCM/MCA mailing list and Joe Sack brought it to the web team’s attention that way too.
Just checked it, still no change. Is it the last chance to go for the cheap Standard Edition and spend the difference on lawyers?
🙂
Can I conplain about Enterprise as well, that shows the wrong for both memory and CPU. (Limits are 2TB and 8 CPU.)
Just refer to the R2 PDF: http://download.microsoft.com/download/7/B/6/7B65BDD8-9D5E-42A5-A8AA-AD61FD8265E2/SQLServer2008R2EditionsDatasheet.pdf
I see they updated the RAM, but Enterprise still has the wrong CPU limits. Obviously even the text of that page still says “SQL Server 2008” not “SQL Server 2008 R2”. (That and they don’t mention the new Datacenter, and PDW versions.)
Express has the wrong database size limit as well (see http://www.microsoft.com/express/database/ which has updated information, but still refers to SQL Server 2008 and not R2 in one place).
Brent,
Good post! Some follow-up:
– The marketing site has been partially corrected. SQL 2008 R2 Standard Edition now shows as 64 GB maximum memory. SQL 2008 R2 Enterprise Edition now shows as 2 TB maximum memory, but still shows “OS maximum” for CPU instead of 8 CPU (sockets) – in response to one of the other comments to this post.
– There is no mention on this marketing site comparison page of the two new “premium” editions – Datacenter and Parallel Data Warehouse – or their limits. Certainly worth noting somewhere on the marketing site.
– The marketing site appears to be an in-place upgrade of the SQL 2008 pre-R2 site, with some links still pointing to SQL 2008 pre-R2 content (substantiated by the missing editions mentioned above)
– When you go to the Previous Versions link, you would expect to find content on SQL 2008 pre-R2, SQL 2005, and maybe SQL 2000. Instead, you find a link to SQL 2005 only (further suggesting that the SQL 2008 pre-R2 marketing site content was updated in place to SQL 2008 R2). There is no main page content for SQL 2008 pre-R2, even though that version is still supported and is a more recent version than SQL 2005. Perhaps this was a goof-up or oversight, but in my mind, it is a big gap of missing material (especially since there are material differences between SQL 2008 R2 and pre-R2 such as the discussed maximums).
Scott R.
Can anyone let me know how to get a copy of SQL server 2008 R2 “Standard” edition? I ask our licensing team for it but only can get enterprise from MSDN.
If I remember right there’s one DVD image for all versions, and you have to put in the appropriate key during setup. If you put in a Standard key you get Standard Edition. Hope that helps!
Thanks, Brent. It truly works with different SN for different versions.
Great find. Thank you.
On the same page, Developer edition and Web Edition still show Memory limit to be OS Maximum.
Isn’t Developer edition supposed to behave like the Enterprise Edition?
And if Enterprise Edition cannot support OS Maximum in memory how can Developer Edition and Web Edition?
Good question. Developer Edition is probably supposed to behave like the top tier edition. That used to be Enterprise, but now it’s Datacenter.
Hi, if you install two instances on the same host, both running license by processors, are you paying twice licensing fee? We need a lawyer to answer this.
Jason – actually, you need Microsoft to answer that. 😀 Talk to your Microsoft licensing contacts.
As previously noted I recently set up a cluster with 4 2012 Std instances. Once all cores are licensed, there is no more to pay. So if you have an 8 core server, with 4 instances of SQL Server each with default processor affinity, you need to have paid for 8 core licenses (in 2012, that’s 4 of the 2 – core licenses, just to confuse the issue) – even though you could say that you have 32 cores licensed. In reality, it’s like owning 4 cars – you can only drive one at a time, so why would the insurance be 4 x the cost of insuring one? Oh, yes, it is – but it’s still stupid. Microsoft is a bit more sensible than that.
Remember too that hyperthreading does NOT double the number of cores which need licensing. So, if you have hyperthreading switched on (which I don’t where possible for SQL boxes) you still license only the physical core count – so a SQL instance with 4 hyperthreaded cores showing in the affinity list, would only need a 2 core license.
hi Simon, since you are using SQL FCInstances I expect that you have production systems. Don’t you miss Enterprise features like online indexing and parallel indexed operations? it can be pain to have offline index during rebuild and one thread can run for hours
Hi Brent,
For SQL 2008 Standard edition, is the limit 64GB or is it ‘Operating System Maximum’ as the website says?
We are running SQL 2008 Std Edition on VMWare and for some reason 64GB is being spoken as the max memory that can be allocated to SQL Server (without having to purchase Enterprise edition and incurring a huge cost increase).
Also, if that’s the case would it be possible to get a 72GB server and allocate 64GB to SQL server 2008 and remain in compliance with Std Edition? Rather than getting a 64GB server and setting max memory to 56GB, leaving some for the OS but not fully utilizing the max amt SQL can use?
Thanks.
Mark – for SQL 2008 Standard, it’s the OS max. Troubleshooting a specific server’s memory is beyond what I can tackle in a blog post comment, but if you’re interested in a consulting engagement, let me know – just click Contact at the top of the site. Thanks!
Mark,
Yes, you can have 72GB (or higher) in the server itself, and set Max Server Memory to 65536 (64GB), which will give 64GB to the SQL Server Buffer Pool. That is pretty much what Bent was advocating in the post, which I agree with.
Still License confusing… if I have 256 GB ram,Want to run 3 SQL Server R2 STD Edition Instances slicing around 86 GB Ram for Each instance(64 GB(SQL) +22 GB(OS Purpose))..2 Node Cluster(Active passive) on Windows 2008 r2 Enterprise edition then ..Is possible to configure using single License for SQL Server ??? Or i need to Buy 3 Licenses(for each Instance)…..Totally Lost in Licensening Sea
Ravinder – check out the Microsoft SQL Server licensing guide PDF:
http://download.microsoft.com/download/6/4/A/64A1EC8F-F575-41E1-9D34-821FA9F98F8E/SQL_Server_2012_Licensing_Reference_Guide.pdf
Ravinder – you license cores, not RAM, so as long as you have licensed every core which any instance is using, you’re covered. Allocate 64GB of RAM, as mentioned above, to each instance; that leaves 64GB unallocated for the OS – you only have a single OS on a cluster node. If you have 8 cores, with each instance showing default affinity and therefore able to use all 8 cores, you license 8 cores. In total. NOT 8 cores for each instance.
Thanks Simon & Brent !!
SQL Server 2008 R2 Standard have 2 Case(Physical \ Virtualization)
1)Physical processor:one license per physical processor) or It can CAL based
2)virtual OSEs,you will need to license each virtual processor individually
Genrally(virtual processor is the equivalent of one core)
Note: For any virtual OSE, you can calculate the number of Per Processor Licenses required for the SQL Server edition that you are licensing by:
A (number of virtual processors
supporting the virtual OSE) / B (# of cores [if hyperthreading is turned off] or threads [ifhyperthreading is turned on] per physical processor).
If the result is not a whole number, round up to the next whole number.
Note if processor is Dual core\Quad core\Hexa-Core or Octa Core then bit different calculation
ref: http://download.microsoft.com/download/2/7/0/270B6380-8B38-4268-8AD0-F480A139AB19/SQL2008R2_LicensingQuickReference-updated.pdf.
Note:Techinical Limit for Each 2K8 R2 Standard Edt Instances is 64GB
But Again MS Licensening is Big Sea even can customised from customer to customer
Ravinder – did you have a question?
I know this is waaaaay old post but this hit me by surprise:
SQL Server 2008 Std support MAX RAM as per the Host MAX RAM Support, but in R2, they have changed it to 64GB for Standard Edition.
I was wondering why my 2008 instance was using the 80 gig I had configured!
It will only ever address 64GB you can 256GB if you want but each instance can only use 64GB
Hi Brent,
I have a Windows 2008 R2 Standard Server which had 32Gb RAM installed because that was what we thought was the maximum. After reading various posts (including yours) we have now added another 32Gb RAM, making the total now 64GB.
However It’s only showing 32 as usable:
Installed memory (RAM): 64.0 GB (32.0 GB usable)
Also xp_cmdshell ‘Systeminfo’ as showing:
Total Physical Memory: 32,768 MB
How can I utilize all of the RAM now on the server?
Is there some switch or setting that will allow the server to recognize all the RAM?
Kind Regards
James
Windows Server 2008 R2 is still capped at 32GB RAM. This post was about SQL Server, not Windows Server.
Hi Brent,
Is it possible by SQL server to use more than 64 GB of memory (Standard edition) , even though memory not configured /restricted.
i.e. installed memory 256 GB on OS (window Server 2012 )
Three SQL server standard edition running with no min/max configuration but it looks like each SQL server instance is using more than 64 GB of memory (almost 66 GB ) .
Just curious to know because SQL server 2008 R2 standard edition it limited to 64 GB of RAM .
Thanks,
SB
Yep! It’s beyond what I can cover fast in a blog post comment, but for example cached plans can go above the 64GB limit.
Thank you Brent for quick response!
than how much we can trust on the output from the below query during memory utilization analysis?
SELECT (physical_memory_in_use_kb/1024) AS Used_Memory_By_SqlServer_MB
FROM sys.dm_os_process_memory
Thanks,
SB
That’s outside of the scope of this blog post, but feel free to post a question over at http://dba.stackexchange.com.
If SQL Server 2008R2 standard edition is limited to 64 GB of memory , than does it required to restrict the max memory for multiple instances (3) which are running SQL server 2008R2 standard edition with 256 GB or RAM installed on windows server 2012?
SB – for questions, head on over to http://dba.stackexchange.com.
sql 2008 r2 database vb software msg date lock victim