SQL Server 2014 was released in early April and there are a number of new features that will prove useful for your business. One of these is in-memory technology, which has been one of Microsoft’s major areas of focus, resulting in In-Memory OLTP for SQL Server 2014. This allows you to now store entire databases in-memory without the need to go to disk. Another feature is Buffer Pool extensions which allows you to increase the buffer pool by using non-volatile storage such as a SSD (Solid state disk).
In-Memory OLTP
In-memory OLTP is fully integrated with the Database Engine component. It is possible to fit an entire database to fit into memory and this reduces I/O expense significantly. Load tests are showing up to a 30 times performance benefit.
In-Memory OLTP has meant that SQL Server’s query processing engine has been built from scratch using a new “Lock-free” design. It doesn’t use locks or latches to maintain transactional consistency. In contrast the In-Memory OLTP engine uses a new optimistic multi-version concurrency control mechanism. In addition to this a stored procedure compilation process is used that takes interpreted T-SQL code and compiles it into native 64 bit code.
To enable In-Memory OLTP there are some key parts of syntax that must be adhered to. Firstly the database must be created (or Altered) to include a filegroup with the keyword “CONTAINS MEMORY_OPTIMIZED_DATA” and when creating the table we must also include the keyword with “BUCKET COUNT” specified and with “MEMORY OPTIMIZED = ON” after doing this we can run a query or TSQL statement against this memory optimised table and see an improvement. We can additionally take advantage of a stored procedure with native compilation and get further performance gains. To use this we need to add the keyword with “NATIVE_COMPILATION” and also include the keyword “WITH ATOMIC” when writing the procedure.
Using In-Memory OLTP does include quite a list of limitations. These include a limit of eight non-clustered indexes per table; not all data types are supported as these tables have an 8060 byte row limit which means no LOB datatypes or XML; replication cannot be performed on in-memory tables; also DML triggers are not allowed as well as check constraints and foreign keys. For a full list of limitations visit the Microsoft msdn article dealing with In-Memory OLTP. There is a tool which can be used to evaluate the database and identify tables and stored procedures that would benefit from In-Memory OLTP called “Analysis, Migrate and Report” or AMR.
Despite the limitations, the performance benefits of using In-Memory OLTP make it an attractive feature of SQL Server 2014.
Example:
Below is an example of setting up and using In-Memory OLTP:
–Create Database
Create database SqlTest on primary
(name = SqlTest_data,
filename = ‘C:\Data\SqlTest_Data.mdf’, size=200MB)
,FILEGROUP SqlTest_FG contains memory_optimized_data(
name = [SQLTest_FG_Container],
FILENAME = ‘c:\data\SQLTest_FG_Container’)
log on (NAME = SqlTest_log,
filename=’C:\Data\SqlTest_Log.ldf’, Size=200MB)
–Create Table
| Create Table dbo.TestTable
(Id int not null primary key nonclustered Hash with (Bucket_Count = 1000000), TestText nvarchar(50) not null) with (memory_optimized = on, durability = schema_only) |
GO
Create table dbo.TestTable1
(Id int not null primary key nonclustered
Hash with (bucket_count = 1000000),
TestText nvarchar(50) not null
)with (memory_optimized = on, durability = schema_only)
GO
create table
(c1 int not null primary key,
c2 nchar(48) not null)
–Create Native Procedure
CREATE PROCEDURE TestProc
@rowcount int,
@c nchar(48)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N’us_english’)
declare @i int = 1
while @i <= @rowcount
begin
INSERT INTO [dbo].[TestTable1] values (@i, @c)
set @i += 1
end
END
GO
— Test
set statistics time off
set nocount on
— inserts – 1 at a time
declare @starttime datetime2 = sysdatetime(),
@timems int
declare @i int = 1
declare @rowcount int = 100000
declare @c nchar(48) = N’12345678901234567890123456789012345678′
—————————–
— disk-based table and interpreted Transact-SQL
—————————–
begin tran
while @i <= @rowcount
begin
insert into values (@i, @c)
set @i += 1
end
commit
set @timems = datediff(ms, @starttime, sysdatetime())
select ‘Disk-based table and interpreted Transact-SQL: ‘ + cast(@timems as varchar(10)) +’ ms’
—————————–
— Interop Hash
—————————–
set @i = 1
set @starttime = sysdatetime()
begin tran
while @i <= @rowcount
begin
insert into [TestTable] values (@i, @c)
set @i += 1
end
commit
set @timems = datediff(ms, @starttime, sysdatetime())
select ‘ memory-optimized table with hash index and interpreted Transact-SQL: ‘ + cast(@timems as varchar(10)) + ‘ ms’
—————————–
— Compiled Hash
—————————–
set @starttime = sysdatetime()
exec TestProc @rowcount, @c
set @timems = datediff(ms, @starttime, sysdatetime())
select ‘memory-optimized table with hash index and native Stored Procedure:’ + cast(@timems as varchar(10)) + ‘ ms’
The Results (Using Quad Core Server with 16GB of RAM)
Disk-based table and interpreted Transact-SQL: 1641 ms
memory-optimized table with hash index and interpreted Transact-SQL: 916 ms
memory-optimized table with hash index and native Stored Procedure:90 ms
Buffer Pool Extension
Another performance-based feature in SQL Server 2014 is Buffer Pool Extension. This is primarily for the purpose of where memory is a limiting factor; either in terms of hardware or the SQL Server Edition (e.g. Standard Edition has a 128GB memory limit). You can attach non-volatile storage such as a SSD (Solid State Disk) and this can be used to expand SQL Server 2014’s Buffer Pool. The main benefits are flexibility of storage options over memory options; another is with Solid State Disk prices decreasing, and therefore greater price efficiency.
To use Buffer Pool Extensions you use the syntax “ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON”. The main benefits found are using read-heavy OLTP; there’s no risk of data loss as it’s only dealing with clean pages; also it’s ready to go – no Application modification required. There are some situations where the benefits will be more limited, such as Data warehouses, Servers with over 64GB and write heavy OLTP workloads. Please bear in mind also this will require x64 architecture.
Example:
use master
go
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON
(FILENAME = ‘G:\SSD\EXAMPLE.BPE’,SIZE = 16 GB)
Other SQL Server 2014 Enhancements
A backup encryption option is included, when backing up a database on the option screen there is an option “ENCRYPT BACKUP” and then you specify either a certificate or a symmetric key.
An enhancement to Always-on Availability groups has meant that the amount of secondary replicas has been increased from 4 to 8. Also secondary replicas are available for read-only workloads even when the primary is unavailable.
Other new features are tighter integration with cloud technologies and the ability to offload High Availability using AlwaysOn Windows Azure Integration. This means that you can use Azure Virtual Machines as Availability Group Secondaries. This just involves specifying “Add Azure Replica” button that integrates with your Azure subscription logins.
SQL Server 2014 also includes smart backup to Azure which allows you to backup databases to Azure Storage.
Failover Cluster support for clustered shared volumes has been added, in the past only one node can own the volume at any given time, and it owns the whole volume, now multiple cluster nodes can be connected to the same volume.
Updateable Columnstore indexes, which allows updates to be performed on the underlying table has been added.
Big Data
Finally there are improvements in the area of Big Data, a term generally used to characterise very large sets of data; HDInsight is a feature which is an implementation of Hadoop available as a Windows Azure Service. For an on-premises solution Microsoft Polybase gives integration between relational data stored in Microsoft Parallel Data Warehouse (PDW) with nonrelational data stored in a Hadoop Distributed File System (HDFS)
There are numerous other new features and feature enhancements in SQL Server 2014 not mentioned here, but the two main features of In-Memory OLTP and Buffer Pool Extensions means that SQL Server 2014 may give real benefits to the database engine in particular.
By Alistair Bartrop, Senior SQL Consultant
Have a comment? Let us know below, or send an email to [email protected]
