Four Key New Performance Features SQL Server 2014 – Part 1
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
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
two main types of in-memory optimized OLTP tables: SCHEMA_AND_DATA and SCHEMA_ONLY.
optimized OLTP tables –
in memory where both schema of the table and data persists after SQL Server
crashes or restart.
optimized OLTP tables –
in memory where only schema of the table persists after SQL Server crashes or
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.
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
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
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)
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
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')
here is the code for create natively compile stored procedure, which I created
to retrieve the orders detail information from
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
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.
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