Category Archives: In-Memory

Boosting Transaction Performance in Windows Azure Virtual Machines with In-Memory OLTP

With the release of SQL Server 2014 CTP2, you can now significantly boost the performance of your OLTP workloads in Windows Azure Virtual Machines. By creating a new VM with our preloaded image of SQL Server 2014 CTP2 on Windows Server 2012 R2, or installing SQL Server 2014 CTP2 on your VM, In-Memory OLTP functionalities are immediately available to you. This blog post provides a good guide on how to create a Windows Azure VM.

However, since the transition to In-Memory OLTP is not as simple as flipping a switch, you must carefully evaluate your application scenario and see if it is the right solution for you.

Recommended Scenarios

For SQL Server 2014 CTP 2, we recommend the following scenarios for SQL Server In-Memory OLTP on a Windows Azure Virtual Machine:

  • Development and Test scenarios, e.g. familiarizing with In-Memory OLTP’s capabilities. The on-demand provisioning of Windows Azure VMs and its low cost make it easy to gain full access to In-Memory OLTP functionalities without a large capital investment. Once you are comfortable with its capabilities and understand its limitations, you can move to deploy In-Memory OLTP on your local server or keep using the VM if it suits your business needs.
  • Scenarios with relaxed data persistence requirements, e.g. web browser session state. SQL Server In-Memory OLTP provides non-durable tables that are perfect for transient data regardless of the transaction read/write mix. By completely eliminating I/O operations, non-durable tables could provide amazing boosts to performance for your non-persistent workload. If some persistence is still required but strict durability is not, you can leverage the new Delayed Durability features we have added to CTP2. Delayed Durability commits transactions but do not immediately write the log records to disk, lightening log I/O pressure by allowing larger and less frequent log writes to disk. For more details see the Books Online topic here.
  • Read-mostly and read-only scenarios, e.g. an online product catalogue. In-Memory OLTP provides extreme performance and parallelism in read-mostly and read-only scenarios due to new algorithms and data structures optimized for memory-resident data. Furthermore, native compilation for stored procedures can dramatically increase CPU efficiency and throughput.
  • Durable read-write scenarios not under log I/O pressure, e.g. workloads with heavy contention. In-Memory OLTP can also provide benefits for workloads with full durability requirements that is not under pressure from log I/O latency or throughput. Because In-Memory OLTP eliminates page contention by using an optimistic concurrency system, it could provide significant boosts to parallelism for your workload if it is suffering from contention problems. In addition, native compilation can improve the speed of a stored procedure with heavy business logic processing.

However, if your workload is suffering from long log I/O latency or if it is under pressure from log I/O throughput, and at the same time you require strict durability of your data, In-Memory OLTP on Windows Azure VM will not alleviate these problems.

SQL Server 2014 CTP2 on Windows Azure VM is not suitable for testing the performance of applications deployed in machines with similar configuration on premise.

Selecting the Right Windows Azure VM Size

The selection of VM sizes is important for any workload running in the IaaS space, whether you are provisioning a new VM using our preloaded CTP2 image or adding CTP2 onto an existing instance. Windows Azure provides a selection of virtual machine sizes for a variety of purposes, as listed in this article.

Since In-Memory OLTP is designed to serve extreme high session concurrency and the nature of memory-resident data requires sufficient memory space, we recommend the following Windows Azure VM sizes for adopting In-Memory OLTP:

Compute Instance Name

Virtual Cores

RAM

Extra Large (A4)

8

14 GB

A6

4

28 GB

A7

8

56 GB

The exact instance size that you will choose will depend on the scenario you wish to run and the size of data you wish to make memory-resident in SQL Server In-Memory OLTP. We recommend provisioning 100% more memory than the estimated size of data in memory and leave sufficient space for the buffer pool used by disk-based workloads on the same machine. This article on SQL Server Books Online has more information on how to estimate the size of a table in memory, and we have published a blog post on hardware considerations for In-Memory OLTP, some of which apply to provisioning VMs as well.

Virtual Machine Configuration

To configure a Windows Azure Virtual Machine for best performance in In-Memory OLTP, we suggest that you should follow the best practices outlined in this whitepaper. A summary of key considerations, plus some unique attributes for In-Memory OLTP, are listed below:

  • Use a single storage account for all disks attached to the same VM.
  • Do not turn on disk host caching for the data and log drives.
    When creating new drives for a Windows Azure VM, the disk host caching option is turned off by default. We do not recommend you change this option.
  • Do not use the Temporary Disk (D: Drive) for log storage.
    This will sacrifice durability for your database, as the content on the Temporary Disk is transient and may be lost on a VM failure or reboot. Please use a persistent Windows Azure Storage disk for your log storage needs.
  • Do not use native operating system striping for storing the log of an In-Memory OLTP-enabled database.
    Logging memory-optimized tables is latency-sensitive, and striping of drives increase the latency of I/O operations. If you wish to use any form of striping for your log drive, you should test your configuration to ensure that the I/O latency is sufficiently low.
  • Compression is not supported for In-Memory OLTP tables. Compression will still work with regular tables in an In-Memory OLTP-enabled database.

SQL Server 2014 In-Memory OLTP: Memory Management for Memory-Optimized Tables

Memory-optimized tables must fully reside in memory and can’t be paged out. Unlike disk-based tables where insufficient memory can slowdown an application, the impact to  memory-optimized tables upon encountering out-of-memory can be severe, causing  DML (i.e. delete, insert or update) operations to fail. While this adds a new dimension to managing memory, the application failure due to resource errors is not something new. For example, applications using disk-based tables can fail with resource errors such as running out of transaction log or TempDB or out of storage. It is the responsibility of DBAs/Administrators to make sure resources are provisioned and managed appropriately to avoid such failures. SQL Server provides a rich set of monitoring tools, including DMVs, PerfMon and XEvents to help administrators identify problems earlier so that a corrective action can be taken. Similarly, for memory-optimized tables, SQL Server provides a rich set of monitoring capabilities and configuration options so that you can manage your database/instance well and keep your application running smoothly.  The remainder of this blog walks thru each of the challenges and details how it can be addressed.

How do I estimate the memory needed?

This is the first question that you need consider when migrating an existing table(s) to memory-optimized table(s) or when you are considering a new application using memory-optimized tables. When migrating a disk-based table, you know, for example using sp_spaceused as described in http://technet.microsoft.com/en-us/library/ms188776.aspx ,  its current size so it is just a simple mathematical calculation to find the corresponding size for memory-optimized tables.  The key differences to be aware of are that memory-optimized tables cannot compressed like disk-based tables with ROW and PAGE compression, so, the memory-optimized table will likely be bigger. However, unlike indexes for disk-based tables, the indexes on memory tables are much smaller. For example, the index key is not stored with hash indexes and all indexes are, by definition, covered indexes. Please refer to http://msdn.microsoft.com/en-us/library/dn247639(v=sql.120).aspx for details. A more challenging task is to estimate the data growth.  While you can make a reasonable guess, the best way is the continuous to monitor the table size and the memory consumed by memory-optimized table (s) in your database and instance.  The same monitoring approach holds for new applications that are created with in-memory OLTP in mind.

How does SQL Server manage memory for memory-optimized tables?

The in-memory OLTP engine is integrated with SQL Server. Memory allocations to memory-optimized tables are managed by SQL Server Memory Manager and the allocated memory is tracked using familiar constructs and tools such as memory clerks and DMVs. The following DMV shows XTP memory clerks. The first row shows the memory allocated by system threads. The second row with name DB_ID_5 represents the consumers in the database objects and the third row with memory node-id 64 represents memory allocated to DAC (Dedicated Admin Connection).

-- Show the memory allocated to in-mempory OLTP objects

select type, name, memory_node_id, pages_kb/1024 as pages_MB

from sys.dm_os_memory_clerks where type like '%xtp%'

type                 name       memory_node_id pages_MB

-------------------- ---------- -------------- --------------------

MEMORYCLERK_XTP      Default    0              18

MEMORYCLERK_XTP      DB_ID_5    0              1358

MEMORYCLERK_XTP      Default    64             0

Also, there are new DMVs that can be used to monitor the memory consumed by the in-memory OLTP engine and memory-optimized tables. Please refer to http://msdn.microsoft.com/en-us/library/dn133203(v=sql.120).aspx for details.

Like any other memory consumer, the in-memory OLTP engine responds to memory-pressure, but to a limited degree. For example, the memory consumed by data and indexes can’t be released even under memory pressure. This is different than disk-based tables where an external memory pressure may cause the buffer pool to shrink which simply means there will be fewer data/index pages in memory. For this reason, it is all the more important to provision the memory for memory-optimized tables appropriately, otherwise in-memory OLTP engine  can starve other memory consumers including the memory needed by SQL Server for its operations which can ultimately leads to slow or unresponsive application. To address this, SQL provides a configuration option to limit the memory consumed by memory-optimized tables.

How can I limit memory consumed by memory-optimized tables?

Starting with SQL Server 2014, you can bind a database to a Resource Pool. This binding is only relevant when the database has one or more memory-optimized table. The memory available in the resource pool controls the total memory available to memory-optimized tables in the database.

For example, create a resource pool, mem_optpool as follows

CREATE RESOURCE POOL mem_optpool WITH (MAX_MEMORY_PERCENT = 40);

Now map the database, mydatabase, to this resource pool by executing the following command. With this command, you are specifying that the total memory taken by memory-optimized tables and indexes cannot exceed the limit in the resource pool. So for this case, the other 60% memory is available to other consumers.

EXEC sp_xtp_bind_db_resource_pool 'mydatabase', 'mem_optpool'

When configuring memory for memory-optimized tables, the capacity planning should be done based on MIN_MEMORY_PERCENT, not on MAX_MEMORY_PERCENT. This provides more predictable memory availability for memory-optimized tables as pools that have the min_memory_percent option set can cause memory pressure notifications against other pools to ensure the minimum percentage is honored.. To ensure that memory is available for the In-Memory OLTP database and help avoid OOM (Out of Memory) conditions, the values for MAX_MEMORY_PERCENT and MIN_MEMORY_PERCENT should be the same. SQL Server target memory is dynamic relative to the OS and setting a minimum memory would be recommended only if the server is not dedicated. For details, please refer to http://msdn.microsoft.com/en-us/library/dn465873(v=sql.120).aspx.

How does SQL Server reclaim memory taken by deleted/updated rows

The rows for memory-optimized tables are stored in-memory and are linked through Hash and non-clustered indexes as described http://msdn.microsoft.com/en-us/library/dn133190(v=sql.120).aspx. Concurrent access to memory-optimized table uses optimistic concurrency control  based on row versions.   Over time, the existing rows may get updated and deleted but these rows can’t immediately be removed as there may be concurrent transactions that need these rows versions. These older row versions are garbage collected (GC’d) asynchronously when it is determined, based on the active transactions, that they are no longer needed. There is a GC system thread that shares the row version cleanup (i.e. GC) with user transaction activity to ensure that SQL Server is able to keep up with the GC.   When you configure the memory for your workload, you must account for additional memory needed for stale row versions. You can roughly estimate the memory needed for stale row versions using 2*(longest running transaction in secs)*(number of row versions generated/sec).  You can use DMVs and Perfmon counters to monitor the progress of Garbage collection. Please refer to http://msdn.microsoft.com/en-us/library/dn133203(v=sql.120).aspx.

SQL Server 2014 In-Memory OLTP: Memory Management for Memory-Optimized Tables

Memory-optimized tables must fully reside in memory and can’t be paged out. Unlike disk-based tables where insufficient memory can slowdown an application, the impact to  memory-optimized tables upon encountering out-of-memory can be severe, causing  DML (i.e. delete, insert or update) operations to fail. While this adds a new dimension to managing memory, the application failure due to resource errors is not something new. For example, applications using disk-based tables can fail with resource errors such as running out of transaction log or TempDB or out of storage. It is the responsibility of DBAs/Administrators to make sure resources are provisioned and managed appropriately to avoid such failures. SQL Server provides a rich set of monitoring tools, including DMVs, PerfMon and XEvents to help administrators identify problems earlier so that a corrective action can be taken. Similarly, for memory-optimized tables, SQL Server provides a rich set of monitoring capabilities and configuration options so that you can manage your database/instance well and keep your application running smoothly.  The remainder of this blog walks thru each of the challenges and details how it can be addressed.

How do I estimate the memory needed?

This is the first question that you need consider when migrating an existing table(s) to memory-optimized table(s) or when you are considering a new application using memory-optimized tables. When migrating a disk-based table, you know, for example using sp_spaceused as described in http://technet.microsoft.com/en-us/library/ms188776.aspx ,  its current size so it is just a simple mathematical calculation to find the corresponding size for memory-optimized tables.  The key differences to be aware of are that memory-optimized tables cannot compressed like disk-based tables with ROW and PAGE compression, so, the memory-optimized table will likely be bigger. However, unlike indexes for disk-based tables, the indexes on memory tables are much smaller. For example, the index key is not stored with hash indexes and all indexes are, by definition, covered indexes. Please refer to http://msdn.microsoft.com/en-us/library/dn247639(v=sql.120).aspx for details. A more challenging task is to estimate the data growth.  While you can make a reasonable guess, the best way is the continuous to monitor the table size and the memory consumed by memory-optimized table (s) in your database and instance.  The same monitoring approach holds for new applications that are created with in-memory OLTP in mind.

How does SQL Server manage memory for memory-optimized tables?

The in-memory OLTP engine is integrated with SQL Server. Memory allocations to memory-optimized tables are managed by SQL Server Memory Manager and the allocated memory is tracked using familiar constructs and tools such as memory clerks and DMVs. The following DMV shows XTP memory clerks. The first row shows the memory allocated by system threads. The second row with name DB_ID_5 represents the consumers in the database objects and the third row with memory node-id 64 represents memory allocated to DAC (Dedicated Admin Connection).

-- Show the memory allocated to in-mempory OLTP objects

select type, name, memory_node_id, pages_kb/1024 as pages_MB

from sys.dm_os_memory_clerks where type like '%xtp%'

type                 name       memory_node_id pages_MB

-------------------- ---------- -------------- --------------------

MEMORYCLERK_XTP      Default    0              18

MEMORYCLERK_XTP      DB_ID_5    0              1358

MEMORYCLERK_XTP      Default    64             0

Also, there are new DMVs that can be used to monitor the memory consumed by the in-memory OLTP engine and memory-optimized tables. Please refer to http://msdn.microsoft.com/en-us/library/dn133203(v=sql.120).aspx for details.

Like any other memory consumer, the in-memory OLTP engine responds to memory-pressure, but to a limited degree. For example, the memory consumed by data and indexes can’t be released even under memory pressure. This is different than disk-based tables where an external memory pressure may cause the buffer pool to shrink which simply means there will be fewer data/index pages in memory. For this reason, it is all the more important to provision the memory for memory-optimized tables appropriately, otherwise in-memory OLTP engine  can starve other memory consumers including the memory needed by SQL Server for its operations which can ultimately leads to slow or unresponsive application. To address this, SQL provides a configuration option to limit the memory consumed by memory-optimized tables.

How can I limit memory consumed by memory-optimized tables?

Starting with SQL Server 2014, you can bind a database to a Resource Pool. This binding is only relevant when the database has one or more memory-optimized table. The memory available in the resource pool controls the total memory available to memory-optimized tables in the database.

For example, create a resource pool, mem_optpool as follows

CREATE RESOURCE POOL mem_optpool WITH (MAX_MEMORY_PERCENT = 40);

Now map the database, mydatabase, to this resource pool by executing the following command. With this command, you are specifying that the total memory taken by memory-optimized tables and indexes cannot exceed the limit in the resource pool. So for this case, the other 60% memory is available to other consumers.

EXEC sp_xtp_bind_db_resource_pool 'mydatabase', 'mem_optpool'

When configuring memory for memory-optimized tables, the capacity planning should be done based on MIN_MEMORY_PERCENT, not on MAX_MEMORY_PERCENT. This provides more predictable memory availability for memory-optimized tables as pools that have the min_memory_percent option set can cause memory pressure notifications against other pools to ensure the minimum percentage is honored.. To ensure that memory is available for the In-Memory OLTP database and help avoid OOM (Out of Memory) conditions, the values for MAX_MEMORY_PERCENT and MIN_MEMORY_PERCENT should be the same. SQL Server target memory is dynamic relative to the OS and setting a minimum memory would be recommended only if the server is not dedicated. For details, please refer to http://msdn.microsoft.com/en-us/library/dn465873(v=sql.120).aspx.

How does SQL Server reclaim memory taken by deleted/updated rows

The rows for memory-optimized tables are stored in-memory and are linked through Hash and non-clustered indexes as described http://msdn.microsoft.com/en-us/library/dn133190(v=sql.120).aspx. Concurrent access to memory-optimized table uses optimistic concurrency control  based on row versions.   Over time, the existing rows may get updated (update operation generates a row version(s)) and deleted but these rows can’t immediately be removed as there may be concurrent transactions that need these rows versions. These older row versions are garbage collected (GC’d) asynchronously when it is determined, based on the active transactions, that they are no longer needed. There is a GC system thread that shares the row version cleanup (i.e. GC) with user transaction activity to ensure that SQL Server is able to keep up with the GC.   When you configure the memory for your workload, you must account for additional memory needed for stale row versions. You can roughly estimate the memory needed for stale row versions using the following steps:

  1. Find the average number of rows updates/deleted by querying DMV dm_db_xtp_object_stats  at the beginning and end of a desired duration for your peak workload, and then compu
  2. Estimate the number (NR) of row versions = (duration of the longest transaction accessing HK tables in seconds) * (average number rows updated/deleted/sec)
  3. Assuming that delete/update operations were uniformly distributed across all memory-optimized tables, find the average row length RLen (including row header and index pointers).  You can now estimate the memory for row versions as (NR * Nlen)

You can use DMVs and Perfmon counters to monitor the progress of Garbage collection. Please refer to http://msdn.microsoft.com/en-us/library/dn133203(v=sql.120).aspx.

SQL Server 2014 In-Memory OLTP: Nonclustered Indexes for Memory-Optimized Tables

SQL Server 2014 CTP1 introduced hash indexes for memory-optimized tables. Hash indexes are very efficient for point lookups, when you know exactly the value you are looking for. However, they do not perform well if you need a range of value, for example a date range, or if you need to retrieve the rows in a particular order.

Memory-optimized nonclustered indexes are a new index type for memory-optimized tables in CTP2. Nonclustered indexes support retrieving ranges of values, and also support retrieving the table rows in the order that was specified when the index was created. They can be used to optimize the performance of queries on memory-optimized tables that use inequality predicates like ‘<’ and ‘>’, and queries using an ORDER BY clause.

Nonclustered indexes also support point lookups, but hash indexes still offer far better performance. You should continue to use hash indexes to optimize the performance of queries with equality predicates.

Hash Index Limitations

Consider, for example, the following (simplified) CREATE TABLE statement. The table SalesOrderHeader_inmem has a hash index on the primary key column ‘SalesOrderID’, as well as a hash index on the column ‘OrderDate’. The index definitions are bold-faced. Note that the BUCKET_COUNT should typically be set between one and two times the number of unique index key values.

CREATE TABLE [Sales].[SalesOrderHeader_inmem](
[SalesOrderID] uniqueidentifier NOT NULL
PRIMARY KEY
NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000)
DEFAULT (NEWID()),
[RevisionNumber] [tinyint] NOT NULL CONSTRAINT 
[IMDF_SalesOrderHeader_RevisionNumber] DEFAULT ((0)),
[OrderDate] [datetime2] NOT NULL
INDEX ix_OrderDate HASH WITH (BUCKET_COUNT=1000000)
) WITH (MEMORY_OPTIMIZED=ON)

If you run a query of the form:

SELECT * FROM Sales.SalesOrderHeader_inmem WHERE SalesOrderID = @ID

SQL Server will use the hash index on the column SalesOrderID to quickly identify the row corresponding to the parameter @ID. Witness the Index Seek operation in the query plan:

 

Hash indexes are very efficient for point lookup operations, because they require only a simple lookup in a hash table rather than traversing an index tree structure, as is required for traditional (non)clustered indexes. If you are looking for the rows corresponding to a particular index key value, using a hash index is the way to go.

Now, hash indexes do have some limitations. Because of the nature of hash tables, rows appear in the index in random order. This means it is not possible to retrieve ranges of values, or to scan rows in any particular order. Therefore, hash indexes do not support Index Seek operations on inequality predicates, and do not support ordered scans. The former results in a full index scan, and the latter results in a scan followed by a(n expensive) sort operator. All this results in a potential performance degradation when using such queries with hash indexes.

Consider the following two example queries:

Query with an inequality predicate

SELECT * FROM Sales.SalesOrderHeader_inmem 
WHERE OrderDate > @Date

The plan for this query does not use the index on OrderDate; it simply includes full index scan for the primary index. This means that SQL Server will process all the rows in the table, and only later filter out the ones with OrderDate > @Date.

 

Query with an ORDER BY

SELECT * FROM Sales.SalesOrderHeader_inmem 
ORDER BY OrderDate

The plan for this query includes a sort operator, which is very costly: after scanning the rows, all rows will need to be ordered to obtain the desired sort-order.

 

Nonclustered indexes

The new memory-optimized nonclustered indexes support both Index Seek operations using inequality predicates, and ordered scans. Consider the following amended CREATE TABLE statement, which now includes a nonclustered index on the column OrderDate.

‘SalesOrderID’, as well as a hash index on the column ‘OrderDate’. The index definitions are bold-faced.

CREATE TABLE [Sales].[SalesOrderHeader_inmem](
[SalesOrderID] uniqueidentifier NOT NULL
PRIMARY KEY
NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000)
DEFAULT (NEWID()),
[RevisionNumber] [tinyint] NOT NULL CONSTRAINT 
[IMDF_SalesOrderHeader_RevisionNumber] DEFAULT ((0)),
[OrderDate] [datetime2] NOT NULL
INDEX ix_OrderDate NONCLUSTERED
) WITH (MEMORY_OPTIMIZED=ON)

Note that the keyword NONCLUSTERED is optional in this example. Also note that the syntax for memory-optimized nonclustered indexes is similar to the traditional disk-based nonclustered indexes. The only difference is that with memory-optimized indexes you need to specify the index with the CREATE TABLE statement, while with traditional indexes, you can create the index after creating the table.

Consider now the following two queries:

Query with an inequality predicate

SELECT * FROM Sales.SalesOrderHeader_inmem 
WHERE OrderDate > @Date

The plan for this query now uses the index on OrderDate. And when inspecting the properties of the Index Seek operator, you will see the inequality predicate OrderDate > @Date. This means that SQL Server will only need to process the rows with OrderDate > @Date.

Query with an ORDER BY

SELECT * FROM Sales.SalesOrderHeader_inmem 
ORDER BY OrderDate

The plan for this query does not include a sort operator. SQL Server will scan the rows in the index in order; in this case the sort-order of the index is the same as the sort-order required by the query, thus no additional sort operator is needed.

 

Note that, in contrast to disk-based nonclustered indexes, memory-optimized nonclustered indexes are uni-directional. This means that they support index scans only in the order that was specified when the index was created. If the ORDER BY clause in the above example would require OrderDate in DESC order, the index ix_OrderDate could not be used.

Limitations on memory-optimized indexes

Memory-optimized indexes do have a few limitations in SQL Server 2014 that we hope to address in future versions. You do need to consider these limitations when deciding on an indexing strategy for your memory-optimized tables.

  • At most 8 indexes – you cannot specify more than 8 indexes on a single memory-optimized table.
  • BIN2 collations – when using n(var)char columns in an index key, the columns must have a _BIN2 collation. Using BIN2 allows very fast lookup, as this can be based simply on binary comparison. However, you need to consider the implications of using a BIN2 collation, such as case and accent sensitivity. For more details see the Books Online topic on Collations and Code Pages.
  • NOT NULL columns in the index key – memory-optimized indexes do not support nullable columns; all columns in the index key must be declared as NOT NULL.

Guidelines for choosing indexes

A few simple guidelines to help choose the type of index you need for your application:

  • If you need to perform only point lookups, meaning you need to retrieve only the rows corresponding to a single index key value, use a hash index.
  • If you need to retrieve ranges of rows, or need to retrieve the rows in a particular sort-order, use a nonclustered index.
  • If you need to do both, particularly if point lookups are frequent, you can consider creating two indexes: it is possible to create both a hash and a nonclustered index with the same index key.

Hash indexes require an estimate of the number of unique index key values, to determine an appropriate value for the BUCKET_COUNT – typically between 1X and 2X. Because nonclustered indexes do not require setting the BUCKET_COUNT, it can be tempting to simply use a nonclustered index instead of a hash index. However, this could lead to sub-optimal performance for point lookup operations. In many scenarios where point lookup performance is critical and there is no good estimate for the number of unique index key values, it is better to over-provision the BUCKET_COUNT: i.e., pick a very high bucket count that you know is going to be larger than the number of index keys. Note that over-provisioning does not affect the performance of point lookups, but under-provisioning does. However, over-provisioning does increase memory-consumption and it slows down full index scans.

For more details on usage of memory-optimized indexes see the Books Online article on Guidelines for Using Indexes on Memory-Optimized Tables.

SQL Server 2014 In-Memory OLTP: Nonclustered Indexes for Memory-Optimized Tables

SQL Server 2014 CTP1 introduced hash indexes for memory-optimized tables. Hash indexes are very efficient for point lookups, when you know exactly the value you are looking for. However, they do not perform well if you need a range of value, for example a date range, or if you need to retrieve the rows in a particular order.

Memory-optimized nonclustered indexes are a new index type for memory-optimized tables in CTP2. Nonclustered indexes support retrieving ranges of values, and also support retrieving the table rows in the order that was specified when the index was created. They can be used to optimize the performance of queries on memory-optimized tables that use inequality predicates like ‘<’ and ‘>’, and queries using an ORDER BY clause.

Nonclustered indexes also support point lookups, but hash indexes still offer far better performance. You should continue to use hash indexes to optimize the performance of queries with equality predicates.

Hash Index Limitations

Consider, for example, the following (simplified) CREATE TABLE statement. The table SalesOrderHeader_inmem has a hash index on the primary key column ‘SalesOrderID’, as well as a hash index on the column ‘OrderDate’. The index definitions are bold-faced. Note that the BUCKET_COUNT should typically be set between one and two times the number of unique index key values.

CREATE TABLE [Sales].[SalesOrderHeader_inmem](
[SalesOrderID] uniqueidentifier NOT NULL
PRIMARY KEY
NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000)
DEFAULT (NEWID()),
[RevisionNumber] [tinyint] NOT NULL CONSTRAINT 
[IMDF_SalesOrderHeader_RevisionNumber] DEFAULT ((0)),
[OrderDate] [datetime2] NOT NULL
INDEX ix_OrderDate HASH WITH (BUCKET_COUNT=1000000)
) WITH (MEMORY_OPTIMIZED=ON)

If you run a query of the form:

SELECT * FROM Sales.SalesOrderHeader_inmem WHERE SalesOrderID = @ID

SQL Server will use the hash index on the column SalesOrderID to quickly identify the row corresponding to the parameter @ID. Witness the Index Seek operation in the query plan:

 

Hash indexes are very efficient for point lookup operations, because they require only a simple lookup in a hash table rather than traversing an index tree structure, as is required for traditional (non)clustered indexes. If you are looking for the rows corresponding to a particular index key value, using a hash index is the way to go.

Now, hash indexes do have some limitations. Because of the nature of hash tables, rows appear in the index in random order. This means it is not possible to retrieve ranges of values, or to scan rows in any particular order. Therefore, hash indexes do not support Index Seek operations on inequality predicates, and do not support ordered scans. The former results in a full index scan, and the latter results in a scan followed by a(n expensive) sort operator. All this results in a potential performance degradation when using such queries with hash indexes.

Consider the following two example queries:

Query with an inequality predicate

SELECT * FROM Sales.SalesOrderHeader_inmem 
WHERE OrderDate > @Date

The plan for this query does not use the index on OrderDate; it simply includes full index scan for the primary index. This means that SQL Server will process all the rows in the table, and only later filter out the ones with OrderDate > @Date.

 

Query with an ORDER BY

SELECT * FROM Sales.SalesOrderHeader_inmem 
ORDER BY OrderDate

The plan for this query includes a sort operator, which is very costly: after scanning the rows, all rows will need to be ordered to obtain the desired sort-order.

 

Nonclustered indexes

The new memory-optimized nonclustered indexes support both Index Seek operations using inequality predicates, and ordered scans. Consider the following amended CREATE TABLE statement, which now includes a nonclustered index on the column OrderDate.

‘SalesOrderID’, as well as a hash index on the column ‘OrderDate’. The index definitions are bold-faced.

CREATE TABLE [Sales].[SalesOrderHeader_inmem](
[SalesOrderID] uniqueidentifier NOT NULL
PRIMARY KEY
NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000)
DEFAULT (NEWID()),
[RevisionNumber] [tinyint] NOT NULL CONSTRAINT 
[IMDF_SalesOrderHeader_RevisionNumber] DEFAULT ((0)),
[OrderDate] [datetime2] NOT NULL
INDEX ix_OrderDate NONCLUSTERED
) WITH (MEMORY_OPTIMIZED=ON)

Note that the keyword NONCLUSTERED is optional in this example. Also note that the syntax for memory-optimized nonclustered indexes is similar to the traditional disk-based nonclustered indexes. The only difference is that with memory-optimized indexes you need to specify the index with the CREATE TABLE statement, while with traditional indexes, you can create the index after creating the table.

Consider now the following two queries:

Query with an inequality predicate

SELECT * FROM Sales.SalesOrderHeader_inmem 
WHERE OrderDate > @Date

The plan for this query now uses the index on OrderDate. And when inspecting the properties of the Index Seek operator, you will see the inequality predicate OrderDate > @Date. This means that SQL Server will only need to process the rows with OrderDate > @Date.

Query with an ORDER BY

SELECT * FROM Sales.SalesOrderHeader_inmem 
ORDER BY OrderDate

The plan for this query does not include a sort operator. SQL Server will scan the rows in the index in order; in this case the sort-order of the index is the same as the sort-order required by the query, thus no additional sort operator is needed.

 

Note that, in contrast to disk-based nonclustered indexes, memory-optimized nonclustered indexes are uni-directional. This means that they support index scans only in the order that was specified when the index was created. If the ORDER BY clause in the above example would require OrderDate in DESC order, the index ix_OrderDate could not be used.

Limitations on memory-optimized indexes

Memory-optimized indexes do have a few limitations in SQL Server 2014 that we hope to address in future versions. You do need to consider these limitations when deciding on an indexing strategy for your memory-optimized tables.

  • At most 8 indexes – you cannot specify more than 8 indexes on a single memory-optimized table.
  • BIN2 collations – when using n(var)char columns in an index key, the columns must have a _BIN2 collation. Using BIN2 allows very fast lookup, as this can be based simply on binary comparison. However, you need to consider the implications of using a BIN2 collation, such as case and accent sensitivity. For more details see the Books Online topic on Collations and Code Pages.
  • NOT NULL columns in the index key – memory-optimized indexes do not support nullable columns; all columns in the index key must be declared as NOT NULL.

Guidelines for choosing indexes

A few simple guidelines to help choose the type of index you need for your application:

  • If you need to perform only point lookups, meaning you need to retrieve only the rows corresponding to a single index key value, use a hash index.
  • If you need to retrieve ranges of rows, or need to retrieve the rows in a particular sort-order, use a nonclustered index.
  • If you need to do both, particularly if point lookups are frequent, you can consider creating two indexes: it is possible to create both a hash and a nonclustered index with the same index key.

Hash indexes require an estimate of the number of unique index key values, to determine an appropriate value for the BUCKET_COUNT – typically between 1X and 2X. Because nonclustered indexes do not require setting the BUCKET_COUNT, it can be tempting to simply use a nonclustered index instead of a hash index. However, this could lead to sub-optimal performance for point lookup operations. In many scenarios where point lookup performance is critical and there is no good estimate for the number of unique index key values, it is better to over-provision the BUCKET_COUNT: i.e., pick a very high bucket count that you know is going to be larger than the number of index keys. Note that over-provisioning does not affect the performance of point lookups, but under-provisioning does. However, over-provisioning does increase memory-consumption and it slows down full index scans.

For more details on usage of memory-optimized indexes see the Books Online article on Guidelines for Using Indexes on Memory-Optimized Tables.

IO Resource Governance in SQL Server 2014

Resource Governor was introduced in SQL Server 2008 to achieve predictable performance in a multi-tenant environment, to support mixed workload patterns, to provide mechanisms and tools to deal with runaway queries, and to enforce resource limits for CPU and memory. It enables customers to implement database consolidation or to configure their own database as a service. Since then, we’ve been incrementally enhancing this feature in major releases to address the top customer requests in this area. In SQL Server 2014, we are excited to add support for IO resource governance.

What is new?

  • Resource Pools now support configuration of minimum and maximum IOPS per volume in addition to CPU/Memory settings enabling more comprehensive resource isolation controls. See syntax details for Create and Alter resource pool with IO support.
  • Ability to configure the maximum outstanding IO per volume (at instance scope). This knob is meant to calibrate the resource governor feature for your storage subsystem.
  • As a note of clarification, by volume we refer to the disk volume as identified by Windows File system APIs
  • We’ve added new columns in the sys.dm_resource_governor_resource_pools and sys.dm_resource_governor_configuration dynamic management views to surface the IO usage and configuration. In addition, we’ve also added a new DMV called sys.dm_resource_governor_resource_pool_volumes that captures IO usage stats across different volumes used by the instance.
  • Two new XEvents (file_write_enqueued, file_read_enqueued) were added to monitor IO requests to the IO Resource Manager queues. These XEvents follow the conventions of the existing IO related events, such as issuing IO and IO completion
  • Last but not least, we’ve added relevant performance counters such as Disk Read IO/sec, Disk Read Bytes/sec, Avg Disk Read IO (ms), Disk Write IO/sec, Disk Write Bytes/sec, Avg Disk Write IO (ms), Disk Read IO Throttled/sec, Disk Write IO Throttled/sec to the SQLServer:Resource Pool Stats.

How to use it?

Let’s take the following scenario as an example of how IO Resource Governance can be used to control the resource usage in a SQL Server instance:

Let’s suppose we are a Database hoster or running a Private cloud for database consolidation and we need to host multiple databases from different tenants (or customers) within a single SQL Server instance to achieve better density and COGS. If one of the tenants is running a very IO intensive workload, this can saturate the IO subsystem, causing performance problems to concurrent workloads that need to perform IO.

The first step would be to create a Resource Pool for each tenant/database and a classifier function that will map the sessions from each tenant to the corresponding Resource Pool. For example, sessions for Customer 1 can be mapped to Resource Pool 1 and sessions for Customer 2 to Resource Pool 2.

If you want to use IO Resource Governance, it is important to set the min or max IOPS setting for every Resource Pool so that the IO requests are redirected to the governance subsystem and minimum reservations can be honored. In the example below, we set the MAX_IOPS_PER_VOLUME to the maximum value for every Pool:

-- Create 2 resource pools & 2 workload groups.
CREATE RESOURCE POOL Customer1Pool;
CREATE RESOURCE POOL Customer2Pool;
GO

CREATE WORKLOAD GROUP Customer1Group USING Customer1Pool;
CREATE WORKLOAD GROUP Customer2Group USING Customer2Pool;
GO

-- Create classifier function
CREATE FUNCTION fnUserClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
IF ORIGINAL_DB_NAME() = 'Customer1DB'
BEGIN
RETURN 'Customer1Group'
END

IF ORIGINAL_DB_NAME() = 'Customer2DB'
BEGIN
RETURN 'Customer2Group'
END

RETURN 'default'
END;
GO

-- Set the classifier function and enable RG.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fnUserClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

-- Set default values for the resource pools so that IO RG is enabled.
ALTER RESOURCE POOL Customer1Pool WITH (MIN_IOPS_PER_VOLUME=0, MAX_IOPS_PER_VOLUME=2147483647);
ALTER RESOURCE POOL Customer2Pool WITH (MIN_IOPS_PER_VOLUME=0, MAX_IOPS_PER_VOLUME=2147483647);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Classifying each workload to a different Resource Pool allows us to configure the resource limits we want to provide for each tenant and also monitor the resource usage generated by their workload. The graph below (Performance Monitor) shows that the workload from Customer 1 is issuing too many IO requests causing a performance drop for Customer 2:

 

In order to protect Customer 2 and guarantee that he gets consistent performance, regardless of the activity from other tenants, we can set the MIN_IOPS_PER_VOLUME setting for the corresponding Resource Pool. From the graph above, it seems that the system can handle around 1300 IOPS, so we decide to reserve 650 IOPS for Customer 2:

ALTER RESOURCE POOL Customer2Pool WITH (MIN_IOPS_PER_VOLUME=650);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

With this configuration, SQL Server will try to throttle the workloads that are running in other Resource Pools, in order to satisfy the 650 IOPS reservation for Customer 2. In the graph below, we can see that the IOPS of the system are now fairly distributed among the tenants and that the performance for Customer 2 is back to normal despite its noisy neighbor:

 

The MIN_IOPS_PER_VOLUME setting will make a reservation for the Resource Pool, but it won’t set any limit for the maximum IOPS it can use. This means that the tenants will still get performance variation depending on how active the rest of the tenants on the instance are. To avoid this problem and guarantee predictable performance, we can set the MAX_IOPS_PER_VOLUME setting for each of the tenants. This will set a hard limit for the maximum IOPS the tenant’s workload can use, guaranteeing predicable performance for the tenant, but also protecting the rest of the tenants on the instance:

ALTER RESOURCE POOL Customer2Pool WITH (MAX_IOPS_PER_VOLUME=750)
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

By configuring the IO settings on the Resource Pools we can control the resources we want to provide for each tenant. This allows us to guarantee predictable performance regardless of the activity from other tenants or even provide differentiation in SLA for the Database Service based on the amount of the resources customers sign up to reserve.

Another scenario that many of you might find applicable is isolating your OLTP workload from any maintenance operations that need to run in the database. Rebuilding an index, for example, is a common operation that can trigger a large number of IO requests, as it needs to scan the whole index or table. By using IO Resource Governance we can limit the number of IO operations these tasks can perform and guarantee predictable performance for concurrent OLTP workload.

In this case, we need a Resource Pool dedicated for maintenance operations and a classifier function that will map maintenance sessions to the corresponding Resource Pool. Running these operation as a different user might be a good way to distinguish between regular and maintenance sessions. By configuring the MAX_IOPS_PER_VOLUME setting on the “maintenance” Resource Pool, we can limit the number of IO operations these tasks can perform and protect concurrent OLTP workload from being impacted.

I hope the example scenarios above demonstrate the core value of this feature.

Call to Action

Please try this feature right away by downloading the SQL Server 2014 CTP2. Even more easily test it in on the SQL Server 2014 CTP2 images that are now available in Windows Azure. We look forward to hearing your feedback.

IO Resource Governance in SQL Server 2014

Resource Governor was introduced in SQL Server 2008 to achieve predictable performance in a multi-tenant environment, to support mixed workload patterns, to provide mechanisms and tools to deal with runaway queries, and to enforce resource limits for CPU and memory. It enables customers to implement database consolidation or to configure their own database as a service. Since then, we’ve been incrementally enhancing this feature in major releases to address the top customer requests in this area. In SQL Server 2014, we are excited to add support for IO resource governance.

What is new?

  • Resource Pools now support configuration of minimum and maximum IOPS per volume in addition to CPU/Memory settings enabling more comprehensive resource isolation controls. See syntax details for Create and Alter resource pool with IO support.
  • Ability to configure the maximum outstanding IO per volume (at instance scope). This knob is meant to calibrate the resource governor feature for your storage subsystem.
  • As a note of clarification, by volume we refer to the disk volume as identified by Windows File system APIs
  • We’ve added new columns in the sys.dm_resource_governor_resource_pools and sys.dm_resource_governor_configuration dynamic management views to surface the IO usage and configuration. In addition, we’ve also added a new DMV called sys.dm_resource_governor_resource_pool_volumes that captures IO usage stats across different volumes used by the instance.
  • Two new XEvents (file_write_enqueued, file_read_enqueued) were added to monitor IO requests to the IO Resource Manager queues. These XEvents follow the conventions of the existing IO related events, such as issuing IO and IO completion
  • Last but not least, we’ve added relevant performance counters such as Disk Read IO/sec, Disk Read Bytes/sec, Avg Disk Read IO (ms), Disk Write IO/sec, Disk Write Bytes/sec, Avg Disk Write IO (ms), Disk Read IO Throttled/sec, Disk Write IO Throttled/sec to the SQLServer:Resource Pool Stats.

How to use it?

Let’s take the following scenario as an example of how IO Resource Governance can be used to control the resource usage in a SQL Server instance:

Let’s suppose we are a Database hoster or running a Private cloud for database consolidation and we need to host multiple databases from different tenants (or customers) within a single SQL Server instance to achieve better density and COGS. If one of the tenants is running a very IO intensive workload, this can saturate the IO subsystem, causing performance problems to concurrent workloads that need to perform IO.

The first step would be to create a Resource Pool for each tenant/database and a classifier function that will map the sessions from each tenant to the corresponding Resource Pool. For example, sessions for Customer 1 can be mapped to Resource Pool 1 and sessions for Customer 2 to Resource Pool 2.

If you want to use IO Resource Governance, it is important to set the min or max IOPS setting for every Resource Pool so that the IO requests are redirected to the governance subsystem and minimum reservations can be honored. In the example below, we set the MAX_IOPS_PER_VOLUME to the maximum value for every Pool:

-- Create 2 resource pools & 2 workload groups.
CREATE RESOURCE POOL Customer1Pool;
CREATE RESOURCE POOL Customer2Pool;
GO

CREATE WORKLOAD GROUP Customer1Group USING Customer1Pool;
CREATE WORKLOAD GROUP Customer2Group USING Customer2Pool;
GO

-- Create classifier function
CREATE FUNCTION fnUserClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
IF ORIGINAL_DB_NAME() = 'Customer1DB'
BEGIN
RETURN 'Customer1Group'
END

IF ORIGINAL_DB_NAME() = 'Customer2DB'
BEGIN
RETURN 'Customer2Group'
END

RETURN 'default'
END;
GO

-- Set the classifier function and enable RG.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fnUserClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

-- Set default values for the resource pools so that IO RG is enabled.
ALTER RESOURCE POOL Customer1Pool WITH (MIN_IOPS_PER_VOLUME=0, MAX_IOPS_PER_VOLUME=2147483647);
ALTER RESOURCE POOL Customer2Pool WITH (MIN_IOPS_PER_VOLUME=0, MAX_IOPS_PER_VOLUME=2147483647);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Classifying each workload to a different Resource Pool allows us to configure the resource limits we want to provide for each tenant and also monitor the resource usage generated by their workload. The graph below (Performance Monitor) shows that the workload from Customer 1 is issuing too many IO requests causing a performance drop for Customer 2:

 

In order to protect Customer 2 and guarantee that he gets consistent performance, regardless of the activity from other tenants, we can set the MIN_IOPS_PER_VOLUME setting for the corresponding Resource Pool. From the graph above, it seems that the system can handle around 1300 IOPS, so we decide to reserve 650 IOPS for Customer 2:

ALTER RESOURCE POOL Customer2Pool WITH (MIN_IOPS_PER_VOLUME=650);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

With this configuration, SQL Server will try to throttle the workloads that are running in other Resource Pools, in order to satisfy the 650 IOPS reservation for Customer 2. In the graph below, we can see that the IOPS of the system are now fairly distributed among the tenants and that the performance for Customer 2 is back to normal despite its noisy neighbor:

 

The MIN_IOPS_PER_VOLUME setting will make a reservation for the Resource Pool, but it won’t set any limit for the maximum IOPS it can use. This means that the tenants will still get performance variation depending on how active the rest of the tenants on the instance are. To avoid this problem and guarantee predictable performance, we can set the MAX_IOPS_PER_VOLUME setting for each of the tenants. This will set a hard limit for the maximum IOPS the tenant’s workload can use, guaranteeing predicable performance for the tenant, but also protecting the rest of the tenants on the instance:

ALTER RESOURCE POOL Customer2Pool WITH (MAX_IOPS_PER_VOLUME=750)
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

By configuring the IO settings on the Resource Pools we can control the resources we want to provide for each tenant. This allows us to guarantee predictable performance regardless of the activity from other tenants or even provide differentiation in SLA for the Database Service based on the amount of the resources customers sign up to reserve.

Another scenario that many of you might find applicable is isolating your OLTP workload from any maintenance operations that need to run in the database. Rebuilding an index, for example, is a common operation that can trigger a large number of IO requests, as it needs to scan the whole index or table. By using IO Resource Governance we can limit the number of IO operations these tasks can perform and guarantee predictable performance for concurrent OLTP workload.

In this case, we need a Resource Pool dedicated for maintenance operations and a classifier function that will map maintenance sessions to the corresponding Resource Pool. Running these operation as a different user might be a good way to distinguish between regular and maintenance sessions. By configuring the MAX_IOPS_PER_VOLUME setting on the “maintenance” Resource Pool, we can limit the number of IO operations these tasks can perform and protect concurrent OLTP workload from being impacted.

I hope the example scenarios above demonstrate the core value of this feature.

Call to Action

Please try this feature right away by downloading the SQL Server 2014 CTP2. Even more easily test it in on the SQL Server 2014 CTP2 images that are now available in Windows Azure. We look forward to hearing your feedback.

In-Memory OLTP: High Availability for Databases with Memory-Optimized Tables

Starting with SQL Server 2012, the new offering of AlwaysOn Availability Groups and AlwaysOn Failover Cluster  Instances (FCI) suite of features have enabled Tier-1 customers with mission critical applications to achieve their availability goals with SQL Server with an easy to deploy and manage solution. SQL Server 2014 builds on this success and offers enhanced AlwaysOn Availability Groups with up to 8 replicas, ability to access secondary replica for offloading reporting workload in disconnected scenario and hybrid scenario with Windows Azure.  

In-memory OLTP is also targeting mission critical applications to deliver up to 30x better performance, and is integrated well with High Availability offerings SQL Server provides. The sections below review each of the High Availability offerings in the context of the in-memory OLTP solution. SQL Server 2014 offers four high-availability choices to customers as follows:

  • AlwaysOn Failover Cluster Instances (FCI): An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering (WSFC) nodes and, possibly, across multiple subnets. On the network, an FCI appears to be an instance of SQL Server running on a single computer, but the FCI provides failover from one WSFC node to another if the current node becomes unavailable.

In-Memory OLTP is fully integrated with FCI.  A database with memory-optimized tables can be configured and managed similar to how you would manage a database with no memory-optimized objects. However, memory-optimized tables in the database will likely add to the recovery time because these tables need to be loaded into memory before the database can be accessed. In-memory OLTP engine loads data/delta file in parallel to minimize the time taken. The time to load the memory_optimized tables depends upon the size of memory-optimized tables, the number of data/delta files, IOPS available to read data/delta files and number of CPUs/Cores available. It is a best practice to configure SQL Server instances with same amount of memory as, unlike for disk-based tables, insufficient memory will cause database recovery to fail on the new instance.  Please refer to How to manage your Memory for In-Memory OLTP for managing memory for database with memory-optimized tables

  • AlwaysON Availability Groups:  SQL Server 2014 allows up to 8 secondary replicas including 2 sync secondary replicas with options to auto-failover. You can offload reporting workloads and database/log backups to one or more secondary replicas. This allows all resources on the primary replica to be solely used by the production workload leading to increased throughput.  Each replica has its own copy of the databases in the availability group

In-memory OLTP is fully integrated with AlwaysOn Availability. You can setup a database with memory-optimized tables in an availability group following exactly the same step that you would for a database without memory-optimized tables. Key points

    • Impact on failover time: The memory-optimized tables are maintained in-memory and kept up-to date by the redo thread as it processes transaction log records. This ensures that the failover to secondary replica will not incur additional overhead of loading memory-optimized tables’ in-memory. Hence, unlike with FCI, there is no impact to failover time.
    • Readable Secondary:  Querying memory-optimized tables both with natively compiled stored procedures and with SQL InterOP is supported on secondary replica. Unlike disk-based tables, the access to memory-optimized tables is done using optimistic concurrency therefore there is no need to map of isolation levels to snapshot isolation level done when accessing these tables. For this reason, reporting workload accessing memory-optimized tables are run without any isolation level mapping.  The restrictions on isolation levels and hints are the same both on primary and secondary replica. Please refer to Guidelines for Transaction Isolation Levels with Memory-Optimized Tables for details. This ensures any query or reporting workload that can be run on secondary replica will run without requiring any changes on the primary replica and vice-versa.  However, independent of the isolation level specified for reporting workload, SQL Server provides only read-committed isolation level consistency on secondary replicas for memory-optimized tables.  Please refer to Active Secondaries: Readable Secondary Replicas (AlwaysOn Availability Groups) for details. In CTP2, you need to enable TF-9989 to query in-memory OLTP tables on the secondary replica.
    • Non-durable tables: AlwaysOn Availability groups use transaction log transport to populate tables on the secondary replica. Since the changes to non-durable tables are not logged, these tables are not populated on the secondary replica. However, their schema will exist on replicas as part of the objects contained within the database.
    • Performance:  There are two aspects of performance. First, with the increased transactional throughput with in-memory OLTP, can the secondary replica keep up?  The short answer is that it will depend on the workload. A workload with high transactional throughput can put pressure on the log transport as well as on redo thread. With in-memory OLTP, to apply changes to memory-optimized tables, the redo thread will not incur IO related latency like it does for disk-based tables because the data is already in-memory. Second is the performance of reporting workload on the secondary replica. Its performance should be comparable to the performance on the primary replica. One limitation is that there is no auto-stats update on memory-optimized tables. You will need to manually update the statistics on the primary replica to generate the up to date statistics which can then be available on secondary replica after redo thread processes the log records associated with the statistics. Please refer to Statistics for Memory-Optimized Tables for details.
  • Log Shipping: SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually.

In-memory OLTP: Log shipping is fully supported on database with memory-optimized tables. A database with memory-optimized tables can be configured and managed similar to how you would manage a regular database

  • Transactional Replication:  It is a popular solution for offloading read workload one or more subscribers especially when only a subset of data, not the whole database, is needed on the subscriber. It is also used to a lesser extent for High Availability.

In-memory OLTP: Replication is supported on databases with memory-optimized tables but the limitation is that you cannot use a memory-optimized table as an article in a publication or as part of a subscription. We will look into addressing this in a future release. In spite of this restriction, you can use memory-optimized tables in read-scale scenarios for read-mostly workloads as described below

Scenario:  Let us say you have couple of disk-based tables t_disk1 and t_disk2 that are accessed heavily by read workloads. For your workload, you want to replicate these two tables on the subscriber however you want read-workload to run memory-optimized tables. You can achieve this by creating corresponding memory_optimized t_memopt1 and t_memopt2 and populating them using DML triggers on disk1 and t_disk2 tables.  Any insert/delete or the update of the row, the triggered action will update the t_memopt1 and t_memopt2 accordingly. Essentially, you have two copies of the same table, one is disk-based and other is memory-optimized. Now you can direct your read-workload to the memory-optimized tables.

In-Memory OLTP: High Availability for Databases with Memory-Optimized Tables

Starting with SQL Server 2012, the new offering of AlwaysOn Availability Groups and AlwaysOn Failover Cluster  Instances (FCI) suite of features have enabled Tier-1 customers with mission critical applications to achieve their availability goals with SQL Server with an easy to deploy and manage solution. SQL Server 2014 builds on this success and offers enhanced AlwaysOn Availability Groups with up to 8 replicas, ability to access secondary replica for offloading reporting workload in disconnected scenario and hybrid scenario with Windows Azure.  

In-memory OLTP is also targeting mission critical applications to deliver up to 30x better performance, and is integrated well with High Availability offerings SQL Server provides. The sections below review each of the High Availability offerings in the context of the in-memory OLTP solution. SQL Server 2014 offers four high-availability choices to customers as follows:

  • AlwaysOn Failover Cluster Instances (FCI): An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering (WSFC) nodes and, possibly, across multiple subnets. On the network, an FCI appears to be an instance of SQL Server running on a single computer, but the FCI provides failover from one WSFC node to another if the current node becomes unavailable.

In-Memory OLTP is fully integrated with FCI.  A database with memory-optimized tables can be configured and managed similar to how you would manage a database with no memory-optimized objects. However, memory-optimized tables in the database will likely add to the recovery time because these tables need to be loaded into memory before the database can be accessed. In-memory OLTP engine loads data/delta file in parallel to minimize the time taken. The time to load the memory_optimized tables depends upon the size of memory-optimized tables, the number of data/delta files, IOPS available to read data/delta files and number of CPUs/Cores available. It is a best practice to configure SQL Server instances with same amount of memory as, unlike for disk-based tables, insufficient memory will cause database recovery to fail on the new instance.  Please refer to How to manage your Memory for In-Memory OLTP for managing memory for database with memory-optimized tables

  • AlwaysON Availability Groups:  SQL Server 2014 allows up to 8 secondary replicas including 2 sync secondary replicas with options to auto-failover. You can offload reporting workloads and database/log backups to one or more secondary replicas. This allows all resources on the primary replica to be solely used by the production workload leading to increased throughput.  Each replica has its own copy of the databases in the availability group

In-memory OLTP is fully integrated with AlwaysOn Availability. You can setup a database with memory-optimized tables in an availability group following exactly the same step that you would for a database without memory-optimized tables. Key points

    • Impact on failover time: The memory-optimized tables are maintained in-memory and kept up-to date by the redo thread as it processes transaction log records. This ensures that the failover to secondary replica will not incur additional overhead of loading memory-optimized tables’ in-memory. Hence, unlike with FCI, there is no impact to failover time.
    • Readable Secondary:  Querying memory-optimized tables both with natively compiled stored procedures and with SQL InterOP is supported on secondary replica. Unlike disk-based tables, the access to memory-optimized tables is done using optimistic concurrency therefore there is no need to map of isolation levels to snapshot isolation level done when accessing these tables. For this reason, reporting workload accessing memory-optimized tables are run without any isolation level mapping.  The restrictions on isolation levels and hints are the same both on primary and secondary replica. Please refer to Guidelines for Transaction Isolation Levels with Memory-Optimized Tables for details. This ensures any query or reporting workload that can be run on secondary replica will run without requiring any changes on the primary replica and vice-versa.  However, independent of the isolation level specified for reporting workload, SQL Server provides only read-committed isolation level consistency on secondary replicas for memory-optimized tables.  Please refer to Active Secondaries: Readable Secondary Replicas (AlwaysOn Availability Groups) for details. In CTP2, you need to enable TF-9989 to query in-memory OLTP tables on the secondary replica.
    • Non-durable tables: AlwaysOn Availability groups use transaction log transport to populate tables on the secondary replica. Since the changes to non-durable tables are not logged, these tables are not populated on the secondary replica. However, their schema will exist on replicas as part of the objects contained within the database.
    • Performance:  There are two aspects of performance. First, with the increased transactional throughput with in-memory OLTP, can the secondary replica keep up?  The short answer is that it will depend on the workload. A workload with high transactional throughput can put pressure on the log transport as well as on redo thread. With in-memory OLTP, to apply changes to memory-optimized tables, the redo thread will not incur IO related latency like it does for disk-based tables because the data is already in-memory. Second is the performance of reporting workload on the secondary replica. Its performance should be comparable to the performance on the primary replica. One limitation is that there is no auto-stats update on memory-optimized tables. You will need to manually update the statistics on the primary replica to generate the up to date statistics which can then be available on secondary replica after redo thread processes the log records associated with the statistics. Please refer to Statistics for Memory-Optimized Tables for details.
  • Log Shipping: SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually.

In-memory OLTP: Log shipping is fully supported on database with memory-optimized tables. A database with memory-optimized tables can be configured and managed similar to how you would manage a regular database

  • Transactional Replication:  It is a popular solution for offloading read workload one or more subscribers especially when only a subset of data, not the whole database, is needed on the subscriber. It is also used to a lesser extent for High Availability.

In-memory OLTP: Replication is supported on databases with memory-optimized tables but the limitation is that you cannot use a memory-optimized table as an article in a publication or as part of a subscription. We will look into addressing this in a future release. In spite of this restriction, you can use memory-optimized tables in read-scale scenarios for read-mostly workloads as described below

Scenario:  Let us say you have couple of disk-based tables t_disk1 and t_disk2 that are accessed heavily by read workloads. For your workload, you want to replicate these two tables on the subscriber however you want read-workload to run memory-optimized tables. You can achieve this by creating corresponding memory_optimized t_memopt1 and t_memopt2 and populating them using DML triggers on disk1 and t_disk2 tables.  Any insert/delete or the update of the row, the triggered action will update the t_memopt1 and t_memopt2 accordingly. Essentially, you have two copies of the same table, one is disk-based and other is memory-optimized. Now you can direct your read-workload to the memory-optimized tables.

SQL Server 2014: Pushing the Boundaries of In-Memory Performance

This morning, during my keynote at the Professional Association of SQL Server (PASS) Summit 2013, I discussed how customers are pushing the boundaries of what’s possible for businesses today using the advanced technologies in our data platform. It was my pleasure to announce the second Community Technology Preview (CTP2) of SQL Server 2014 which features breakthrough performance with In-Memory OLTP and simplified backup and disaster recovery in Windows Azure.

Pushing the boundaries

We are pushing the boundaries of our data platform with breakthrough performance, cloud capabilities and the pace of delivery to our customers. Last year at PASS Summit, we announced our In-Memory OLTP project “Hekaton” and since then released SQL Server 2012 Parallel Data Warehouse and public previews of Windows Azure HDInsight and Power BI for Office 365. Today we have SQL Server 2014 CTP2, our public and production-ready release shipping a mere 18 months after SQL Server 2012. 

Our drive to push the boundaries comes from recognizing that the world around data is changing.

  • Our customers are demanding more from their data – higher levels of availability as their businesses scale and globalize, major advancements in performance to align to the more real-time nature of business, and more flexibility to keep up with the pace of their innovation. So we provide in-memory, cloud-scale, and hybrid solutions. 
  • Our customers are storing and collecting more data – machine signals, devices, services and data from outside even their organizations. So we invest in scaling the database and a Hadoop-based solution. 
  • Our customers are seeking the value of new insights for their business. So we offer them self-service BI in Office 365 delivering powerful analytics through a ubiquitous product and empowering users with new, more accessible ways of gaining insights. 

In-memory in the box for breakthrough performance

A few weeks ago, one of our competitors announced plans to build an in-memory column store into their database product some day in the future. We shipped similar technology two years ago in SQL Server 2012, and have continued to advance that technology in SQL Server 2012 Parallel Data Warehouse and now with SQL Server 2014. In addition to our in-memory columnar support in SQL Server 2014, we are also pushing the boundaries of performance with in-memory online transaction processing (OLTP). A year ago we announced project “Hekaton,” and today we have customers realizing performance gains of up to 30x. This work, combined with our early investments in Analysis Services and Excel, means Microsoft is delivering the most complete in-memory capabilities for all data workloads – analytics, data warehousing and OLTP. 

We do this to allow our customers to make breakthroughs for their businesses. SQL Server is enabling them to rethink how they can accelerate and exceed the speed of their business.

 Sven Lowry TPP

  • TPP is a clinical software provider managing more than 30 million patient records – half the patients in England – including 200,000 active registered users from the UK’s National Health Service.  Their systems handle 640 million transactions per day, peaking at 34,700 transactions per second. They tested a next-generation version of their software with the SQL Server 2014 in-memory capabilities, which has enabled their application to run seven times faster than before – all of this done and running in half a day. 
  • Ferranti provides solutions for the energy market worldwide, collecting massive amounts of data using smart metering. With our in-memory technology they can now process a continuous data flow up to 200 million measurement channels making the system fully capable of meeting the demands of smart meter technology.
  • SBI Liquidity Market in Japan provides online services for foreign currency trading. By adopting SQL Server 2014, the company has increased throughput from 35,000 to 200,000 transactions per second. They now have a trading platform that is ready to take on the global marketplace.

A closer look into In-memory OLTP

Previously, I wrote about the journey of the in-memory OLTP project Hekaton, where a group of SQL Server database engineers collaborated with Microsoft Research. Changes in the ratios between CPU performance, IO latencies and bandwidth, cache and memory sizes as well as innovations in networking and storage were changing assumptions and design for the next generation of data processing products. This gave us the opening to push the boundaries of what we could engineer without the constraints that existed when relational databases were first built many years ago. 

Challenging those assumptions, we engineered for dramatically changing latencies and throughput for so-called “hot” transactional tables in the database. Lock-free, row-versioning data structures and compiling T-SQL and queries into native code, combined with making the programming semantics consistent with SQL Server means our customers can apply the performance benefits of extreme transaction processing without application rewrites or the adoption of entirely new products. 

Transformational In-Memory Performance

The continuous data platform

Windows Azure fulfills new scenarios for our customers – transcending what is on-premises or in the cloud. Microsoft is providing a continuous platform from our traditional products that are run on-premises to our cloud offerings. 

With SQL Server 2014, we are bringing the cloud into the box. We are delivering high availability and disaster recovery on Windows Azure built right into the database. This enables customers to benefit from our global datacenters: AlwaysOn Availability Groups that span on-premises and Windows Azure Virtual Machines, database backups directly into Windows Azure storage, and even the ability to store and run database files directly in Windows Azure storage. That last scenario really does something interesting – now you can have an infinitely-sized hard drive with incredible disaster recovery properties with all the great local latency and performance of the on-premises database server. 

We’re not just providing easy backup in SQL Server 2014, today we announced backup to Windows Azure would be available for all our currently supported SQL Server releases. Together, the backup to Windows Azure capabilities in SQL Server 2014 and via the standalone tool offer customers a single, cost-effective backup strategy for secure off-site storage with encryption and compression across all supported versions of SQL Server.

By having a complete and continuous data platform we strive to empower billions of people to get value from their data. It’s why I am so excited to announce the availability of SQL Server 2014 CTP2, hot on the heels of the fastest-adopted release in SQL Server’s history, SQL Server 2012. Today, more businesses solve their data processing needs with SQL Server than any other database. It’s about empowering the world to push the boundaries.

Quentin Clark
Corporate Vice President
Data Platform Group