Memory is Oxygen for Large Database Systems

I am frequently invited in to assist with database (OLTP, DW and OLAP) performance, and often the issue is IO throughput related. So, the solution tends to involve tuning queries (and indexes) to reduce IO or improving the IO capacity of the SAN (or both). Of course, in all of these IO related performance issues, RAM can be a great elixir. Databases love memory. They are designed to use RAM by holding popular pages, compiled plans and work tables in memory. So, if you have a large database with a performance issue; very quick, and relatively cheap relief might be gained simply by adding RAM.

SQL Server is particularly good at exploiting available RAM - and (just as important) reducing memory demands if the operating system is paging. With the default SQL install options, all you need to do is give RAM to the OS and SQL will use it to hold more data and plans in memory. Sometimes this can be an exponential beneficial effect since, by reducing IO demands, the SAN is no longer a bottleneck and remaining IO is executed much faster.

Multidimensional OLAP databases also like to have lots of RAM. Even larger than the combined OLAP database size! See earlier blog. Unfortunately, SQL Server OLAP is not as memory effective as the relational SQL, but it will hold data (and aggregations) in memory, improving query and processing performance. SQL Tabular is an exception, as it requires the entire cube to reside in memory. It will simply not work without sufficient memory.

RAM, by and large, has no licencing cost with SQL Server, which can be a big consideration. Adding CPUs will generally incur significant OS and SQL licensing costs. I said, by and large, since with SQL 2012 and SQL 2014, there are, unfortunately, limits on SQL Server Standard Edition memory.

• SQL 2008 Standard Edition has no memory limit
• SQL 2012 Standard Edition has 64GB memory limit
• SQL 2014 Standard Edition has 128GB memory limit
• (SQL Server Enterprise Edition versions support their OS limits)

So, if you have a performance issue with a large database, and can't wait for a database tuning expert; before adding CPUs, Fusion IO cards etc.; try giving your system more RAM.