My previous related question was flagged as "Not Constructive", because I asked to compare SQL Server & Oracle. So this time I will ask only about SQL-Server.
My company has built an approximately 200GB data warehouse using SQL Server 2005 Standard edition on a server with 16GB of RAM and spinning hard disks. Performance has become grindingly slow. We now have the budget to purchase a new server and database. For the server we are considering hardware with 256GB of RAM, dual Xeon 8-core processors, and solid state disks that will fit the entire database (which may grow to 1TB). The server will be running Windows Server 2012.
My understanding is that SQL Server 2012 Standard Edition is limited to using 64GB of memory, even if the server has more. We cannot afford the roughly ~$100K it would cost to license SQL Server 2012 Enterprise Edition that could use all of our RAM.
From your experience, will large amounts of additional RAM help with SQL Server performance (e.g. due to Windows Server file caching), even if SQL Server won't use it directly?
Best Answer
If you are only running the database engine, then any RAM above what SQL Server can access is basically wasted (with the exception of a small amount for the OS, say 2-4 GB). If you are also running other Sql Server products (SSAS, SSIS, SSRS, MDM, Stream Insight, etc) then additional RAM will be useful.
The key to great OLAP query performance is to have an IO subsystem that can keep the CPU's fed with DATA. If the SSD's can provide the IO, then RAM limitation isn't as critical.
There are other benefits of enterprise edition beyond the increased RAM capacity, such as data compression, columnstore indexes, larger read-aheads, etc. You may be able to get better performance out of a 4-core, single CPU box with enterprise edition than with 16 cores and standard edition. The OLAP queries typically are not CPU intensive, so you may want to re-evaluate where your money is best spent.