SQL Server 2014 Takes Advantage of Low Price Memory

It may seem like only yesterday that SQL Server 2012 was launched, but SQL Server 2014 is already on its way. A preview copy is to be soon made available and the production release is due before the end of 2013. This was all announced at TechEd 2013 conference.

Whilst there are a number of new features being announced for SQL Server 2014 it has certain features that take advantage of lower memory prices and these are of particular interest.

One option for storing databases in memory is to just use Solid State Disks (SSDs) and you don’t need SQL Server 2014 to do this. Just add SSD disk drives to the server and create databases on them. It is that simple.

SSDs are faster because they are based on Flash memory and have no moving parts. Reads in particular are very fast when compared to a normal disk with sub-millisecond reads compared to many milliseconds. The cost of SSD storage is about three or four times that of a traditional hard drive at present and it is probably not viable to replace all the disks in your SQL Server with SSDs just yet, unless your databases are relatively small (i.e. about 500GB or less).

Also note that SQL Server treats an SSD drive just like any other disk drive, and not like memory, and so caches the data in normal RAM anyway and applies database locks for managing multi user access as per usual. As a result although replacing physical disks with SSDs will increase speed, it will not be as much as you may expect.

The additional features in SQL Server 2014 that are specifically designed to take advantage of lower memory costs are: caching frequently used data on SSDs and memory-optimized tables.

Caching frequently used data on SSDs: This feature allows you to specify an SSD device or a set of SSD devices to be used to cache data in addition to that cached in the buffer cache in server RAM. Data that is read (i.e. not data that is updated) is cached on the SSDs and so this form of caching works best for data retrieval queries.

If you have used all your normal server RAM for your buffer cache, you can add SSDs to your server and use them for additional read caching. This will vastly improve the speed of large retrieval queries. Great for reporting / data warehouse systems.

Memory-optimized tables: These allow tables to be stored and managed directly in memory. As well as getting the speed gains from having the table in memory SQL Server uses a light weight locking strategy for these tables and this greatly improves concurrent access avoiding the blocking problems that often occur with tables that are simultaneously accessed by many users.

Memory-optimized tables are stored in memory the whole time, and never on disk. Only data changes are written sequentially to disk for recovery purposes, and even this is optional because tables can be designated as being non-durable and non-logged. After a server restart non-durable tables will be empty.  The on disk transaction log is still used for memory-optimized and normal tables. Indexes are never stored on disk.

Memory-optimized tables are designed to offer vastly superior performance for online transaction processing (OLTP) systems with high numbers of reads and updates and a high degree of concurrency.

To create a memory-optimized table first the database has to have a file group for MEMORY_OPTIMIZED_DATA. This is done with the following commands:

ALTER DATABASE databasename
ADD FILEGROUP filegroupname
CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE databasename
ADD FILE (NAME='logicalname', FILENAME='foldername')
TO FILEGROUP filegroupname;

The next step is to create a table using the MEMORY_OPTIMIZED option. The DURABILITY option can be SCHEMA_AND_DATA or just SCHEMA in which case the data is non-durable:

CREATE TABLE tablename (

[col1] varchar(32) NOT NULL PRIMARY KEY NONCLUSTERED
HASH WITH (BUCKET_COUNT = 1024),
[col2] datetime NOT NULL)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
 
Note that the table must have at least one index, which is the PRIMARY key in this case.

As an additional performance gain SQL Server 2014 also supports native compiled stored procedures. These procedures are compiled into the native machine code for the CPU of the server and offer additional gains above normal Transact-SQL. Native compiled stored procedures only work for memory-optimized tables.

However after all the good news please note that that there are a number of restrictions for memory-optimized tables including:
·         No IDENTITY columns
·         No Triggers
·         No FOREIGN KEY or CHECK constraints
·         And quite a few others

All in all, memory is getting cheaper and SQL Server is changing to take advantage of that. SQL Server 2014 allows SSDs to be used as additional buffer cache for retrieval operations and dedicated memory-optimized tables allow very high performance OLTP applications to be developed.