The latest release of SQL Server 2014 has several new performance-related features as well as significant enhancements to the existing performance features. With these new performance-related enhancements of SQL Server 2014 we can significantly improve the performance of high transactional OLTP applications.
In this first of the three part article series, we will take a look at SQL Server 2014 in-memory OLTP engine.
Enhanced In-memory OLTP Engine
The SQL Server 2014 in-memory online transaction processing (OLTP) engine, previously code-named "Hekaton", allows you to create in-memory optimized OLTP tables within a conventional relational database. It is one of the key new performance-related architectural enhancements to the SQL Server database engine. Like traditional on disk-based tables, the fully durable transactions on in-memory optimized OLTP tables are fully atomic, consistent, isolated, and durable (ACID). The in-memory OLTP engine solves problems in high-concurrency situations, as it uses data structures that are entirely latch-free (lock-free), meaning there are no latches or spinlocks on performance-critical paths in the system. Instead, it uses an optimistic multi-version concurrency control (MVCC) technique that provides transaction isolation semantics, which helps to avoid interference among transactions. Thus, any user process can access any row in a table without acquiring latches or locks. The combination of this MVCC and latch-free data structures results in a system in which the user processes can run without stalling or waiting. In addition, stored procedures operating on optimized memory tables, though written in Transact-SQL, are actually compiled to highly efficient machine code. This maximizes the runtime performance for certain workloads and certain types of queries, because the generated machine code only contains exactly what is needed to run the request, and nothing more. According to Microsoft, some applications can achieve a 50x performance increase simply by using the in-memory OTLP engine.
There are two main types of in-memory optimized OLTP tables: SCHEMA_AND_DATA and SCHEMA_ONLY.
· SCHEMA_AND_DATA memory optimized OLTP tables – Resides in memory where both schema of the table and data persists after SQL Server crashes or restart.
· SCHEMA_ONLY memory optimized OLTP tables – Resides in memory where only schema of the table persists after SQL Server crashes or restart.
SCHEMA_ONLY memory optimized OLTP tables are useful as staging tables for your database application. Whereas SCHEMA_AND_DATA memory optimized OLTP tables are more useful as transactional OLTP applications, where you would not want to lose data and transactions after SQL Server crashes or restart.
The in-memory OLTP feature is only supported on 64-bit Enterprise, Developer or Evaluation editions of SQL Server 2014.
You can use the Memory Optimization Advisor wizard, which can be launched from SQL Server Management Studio (SSMS), to help identify and migrate fully compatible tables in memory, and select the stored procedures that can be compiled into machine code for high-performance execution.
Creating memory-optimized tables
To create memory-optimized table, the database must have a MEMORY_OPTIMIZED_DATA filegroup. To create MEMORY_OPTIMIZED_DATA filegroup, run the following code:
ALTER DATABASE [AdventureWorks2012]
ADD FILEGROUP [AW_MemoryOptimized]
CONTAINS MEMORY_OPTIMIZED_DATA;
Next, run the following code, to add database file to memory optimized filegroup:
ALTER DATABASE [AdventureWorks2012]
ADD FILE (NAME='AdventureWorks2012_MemoryOptimized',
FILENAME='C:\DB_Data\AdventureWorks2012_MO.ndf')
TO FILEGROUP [AW_MemoryOptimized];
Here is the general syntax to create memory-optimized table:
CREATE TABLE <schema_name, sysname, dbo>.<table_name,sysname,sample_memoryoptimizedtable>
(
<column_in_primary_key, sysname, c1> <column1_datatype,
, int> <column1_nullability, , NOT NULL>,
<column2_name, sysname, c2> <column2_datatype,
, float> <column2_nullability, , NOT NULL>,
<column3_name, sysname, c3> <column3_datatype,
, decimal(10,2)> <column3_nullability, , NOT NULL> INDEX <index3_name, sysname, index_sample_memoryoptimizedtable_c3> NONCLUSTERED (<column3_name, sysname, c3>),
CONSTRAINT <constraint_name, sysname,
PK_sample_memoryoptimizedtable> PRIMARY KEY NONCLUSTERED (<column1_name, sysname, c1>),
INDEX <index2_name, sysname, hash_index_sample_memoryoptimizedtable_c2> HASH (<column2_name, sysname, c2>) WITH (BUCKET_COUNT = <sample_bucket_count, int, 1024>)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = <durability_type, , SCHEMA_AND_DATA>) GO
For example, the run the following code to create memory-optimized table inside AdventureWorks2012 sample database:
CREATE TABLE Sales.SalesOrderDetail_MemoryOptimzied (
[SalesOrderID] [int] NOT NULL
,[SalesOrderDetailID] [int] NOT NULL
,[CarrierTrackingNumber] [nvarchar](25) NULL
,[OrderQty] [smallint] NOT NULL
,[ProductID] [int] NOT NULL
,[SpecialOfferID] [int] NOT NULL
,[UnitPrice] [money] NOT NULL
,[UnitPriceDiscount] [money] NOT NULL
,[LineTotal] [money]
,[rowguid] [uniqueidentifier] NOT NULL
,[ModifiedDate] [datetime]
,CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID2] PRIMARY KEY NONCLUSTERED HASH (
[SalesOrderID]
,[SalesOrderDetailID]) WITH (BUCKET_COUNT = 20000))
WITH (MEMORY_OPTIMIZED = ON
,DURABILITY = SCHEMA_AND_DATA)
In here BUCKET_COUNT parameter is the number of bucket that should be created within the hash indexes. For more information in determining the Correct Bucket Count for Hash Indexes, check MSDB resource here.
Here is the general syntax to create natively compiled stored procedure:
CREATE PROCEDURE <Schema_Name, sysname, dbo>.<Procedure_Name, sysname, Procedure_Name>
-- Add the parameters for the stored
procedure here
<@param1, sysname, @p1> <datatype_for_param1,
, int> = <default_value_for_param1,
, 0>,
<@param2, sysname, @p2> <datatype_for_param2,
, int> = <default_value_for_param2,
, 0>
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
( TRANSACTION ISOLATION
LEVEL = <transaction_isolation_level, , SNAPSHOT>, LANGUAGE = <language, , N'English'>
)
--Insert statements for the stored
procedure here
SELECT <@param1, sysname, @p1>, <@param2, sysname, @p2>
END
GO
The actual code that make the store procedure a natively compile procedure is as follow:
WITH NATIVE_COMPILATION
,SCHEMABINDING
,EXECUTE AS OWNER
AS
BEGIN
ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT
,LANGUAGE = 'English')
For example, here is the code for create natively compile stored procedure, which I created to retrieve the orders detail information from Sales.SalesOrderDetail_MemoryOptimized table.
CREATE PROCEDURE RetriveOrderDetail @SalesOrderDetailID INT
WITH NATIVE_COMPILATION
,SCHEMABINDING
,EXECUTE AS OWNER
AS
BEGIN
ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT
,LANGUAGE = 'English')
SELECT [SalesOrderID]
,[SalesOrderDetailID]
,[CarrierTrackingNumber]
,[OrderQty]
,[ProductID]
,[SpecialOfferID]
,[UnitPrice]
,[UnitPriceDiscount]
,[LineTotal]
,[rowguid]
,[ModifiedDate]
FROM [Sales].[SalesOrderDetail_MemoryOptimzied]
WHERE [SalesOrderDetailID] = @SalesOrderDetailID
END
Limitations of memory-optimized tables
The memory-optimized tables do not support full set of SQL Server and Transact-SQL features that are supported by traditional disk-based tables. Some of the key limitations of memory-optimized tables include no support for Sparse, IDENTITY and computed columns, DML triggers, Filestream data, Columnstore filtered and full-text indexes, ROWGUIDCOL option, FOREIGN KEY, CHECK and UNIQUE constraints, TRUNCATE TABLE, MERGE, and dynamic and key set cursors.
The following data types are not support by memory-optimized table: Datetimeoffset, geography, geometry, LOBs (varchar(max), image, XML, text and ntext). For more full-list of SQL Server features that are not supported with memory-optimized tables, see MSDN resource here.
For additional documentation on memory-optimized table, see MSDN resource here.
]]>