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?

  • Have you attempted to profile your current system at all, to actually determine what is causing the slowness? You can just throw hardware at every bottleneck, but that's a waste and still not guaranteed to work as you continue to scale up. – mfinni Jun 21 '13 at 19:47
  • We have done some profiling. Some of it is database design, in the sense that we might be able to split apart some really wide tables given that we usually only operate on a few fields at a time. But a large part is that many of our queries require processing a few GB of data at a minimum. – Milton Jun 22 '13 at 0:44

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.

  • Useful to know, thanks. The SSDs spec out at 1500MB/s peak and roughly 200K IOPS so I am hopeful. I tried out ColumnStore on a SQL Server Developer testbed, and did see roughly a 10x performance increase on our data. But I was wary of putting together what seemed to be a crippled system in order to afford the licensing costs (knowing of course Standard Edition cripples RAM). – Milton Jun 22 '13 at 0:48
  • Download the Enterprise Edition trial and benchmark it there. – mfinni Jun 23 '13 at 18:08

In my experience, you won't be using all that RAM with a single Standard instance, although the machine you describe would definitely be better than the one you have now.

What you COULD do, if you can split up the databases, is create three instances.

http://www.mssqltips.com/sqlservertip/2942/understanding-the-sql-server-2012-licensing-model/ http://sqlmag.com/blog/sql-server-standard-edition-licensing-limits

That's what I'd recommend.

  • I have read that querying across databases on the same server instance has no performance impact, but across instances can have a large impact. Is that your experience? – Milton Jun 22 '13 at 0:45
  • I'm not sure what you mean. What I meant by "if you can split up the databases" was, well. Finding a logical way to split up the data into at least three databases, if you're going to use three instances. – Katherine Villyard Jun 22 '13 at 3:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.