SQL Server In-Memory OLTP as ASP.NET Session State Provider

Key Takeaways

• In-Memory OLTP, introduced with SQL Server 2014, offers a high-performance database engine that stores data in server memory, uses a native stored procedure compiler, and is free from lock/latch contentions. This results in a performance that is 30-40 times faster than its predecessor and can improve an application’s throughput by 16 times after moving to an In-Memory database.
• The ASP.NET framework, widely used for building web applications, employs sessions as a state management technique. The two types of session modes, InProc (In Process) and OutProc (Out of Process), differ in where they store session data. InProc stores data in the web server’s memory, while OutProc allows data storage in a State Server, SQL Server, or any other custom location.
• SQL Server In-Memory OLTP can be used for ASP.NET Session State Management by using a package available via NuGet. This package performs several changes to your application, including adding an assembly reference of Microsoft.Web.SessionState.SqlInMemory, updating the sessionState configuration in your web.config to use SqlInMemoryProvider, and providing a SQL script named ASPStateInMemory.sql to configure the In-Memory database.
• In-Memory OLTP boosts the performance of SQL Server Session State by 30-40 times compared to traditional SQL Server Session State. It can be used in Web Farm and Web Garden scenarios, can leverage the SQL Server AlwaysOn feature for high availability, and can make use of the Geo-redundancy feature of the SQL Server for durability and high availability.

Special thanks to Matthew Wilkin for kindly helping to peer review this article.

Internet applications use the HTTP protocol, following a request-response paradigm. As each request is independent of the other, we need to find a way to maintain some information across multiple requests. For that, either we put all the common data in each request, or we put key(s) in request(s) to store the data on the server against each key.

State management is a basic requirement for any web application. As a user interacts with an application, we need to maintain the user’s preferences — the application state with its corresponding actions and responses, and potentially other metadata related to the application. Therefore, most of the web application frameworks today provide various out-of-the-box features for state management.

ASP.NET Session State Management

The ASP.NET framework is one of the most used frameworks for building web applications. It provides rich features and tools for rapid application development. Sessions are commonly used as a state management technique in numerous ASP.NET applications due to their simplicity and flexibility. Let’s have a quick overview of session state management.

At a high level, the diagram above shows there are two types of session modes: InProc (In Process) and OutProc (Out of Process). While using InProc mode, session data is stored in the web server’s memory, while OutProc allows us to store the session data either in a State Server, SQL Server or at any other custom location. InProc is the fastest, as data is available in the web server’s memory itself, with no serialization/deserialization or I/O required.

But this doesn’t fit into a Web Farm/Web Garden scenario, commonly found in Enterprise applications, where SQL Server is used for storing Session State. A Web Farm is a group of web servers hosting the application, where a load balancer routes the request based on the load of a server. On the other hand, a Web Garden involves multiple worker processes (w3wp.exe) assigned to the same application pools, which is used by the hosting application. Both provide a way to scale the application if traffic load can’t be handled by one server or one worker process.

Whenever the need for reliable sessions becomes a necessity, Enterprise applications tend to leverage the High Availability features found in SQL Server, such as redundancy and clusters. However, using SQL Server has an extra performance cost, because we need to serialize/de-serialize session data while reading/writing to SQL. This requires an I/O operation as data resides in disk-based files.

Session data is generally small in size but very dynamic in nature. In times of heavy load, many threads could be writing session data while others read the data. As the threads actively access the data in a small region, lock and latch contentions start creating issues. Reading and writing data starts to take more time, which makes for a bad user experience. So there’s a trade off in each mode, but as discussed, we end up using SQL in many scenarios and bear the performance cost. For better performance, we invest more dollars by adding more powerful servers into the system, but are benefitted only to a certain extent, and all the available resources are not used optimally.

What is In-Memory OLTP?

In-Memory OLTP is Microsoft’s (relatively) new online transaction processing technology, which was introduced with SQL Server 2014 to provide a high-performance database engine with the help of memory optimized tables that permanently stays in memory and uses a native stored procedure compiler.

These tables don’t suffer from latching, because the In-Memory OLTP engine uses lock-free algorithms and structures.

Microsoft started working on a project with code name Hekaton that was aimed to provide 100 times faster database engine than the disk based one. In SQL Server 2014, they introduced this new engine, which is 30–40 times faster than the traditional one. They’ve made a good amount of changes in SQL Server 2016 (which is in preview currently) and made it more robust. So if we leverage this feature of SQL Server then we can almost mitigate the performance cost of IO-bound SQL as Session state provider.

Using SQL Server In-Memory OLTP for Session State Management

To use In-Memory OLTP, we can write our own custom provider. However, there’s already one package available via NuGet that leverages this feature, so instead of rolling our own, we’ll use it. Configuring this package is very straightforward. The NuGet package may be found on the NuGet site, or it can be installed using Package Manager Console with

PM> Install-Package Microsoft.Web.SessionState.SqlInMemory

This package performs the following changes to your application:

1. Adds an assembly reference of Microsoft.Web.SessionState.SqlInMemory

2. Updates the sessionState configuration in your web.config to use SqlInMemoryProvider:

   <sessionState mode="Custom" customProvider="SqlInMemoryProvider">
       <providers>
           <add name="SqlInMemoryProvider" type="Microsoft.Web.SessionState.SqlInMemoryProvider" 
                connectionString="data source=***;initial catalog=ASPStateInMemory;User ID=user;Password=password;" />
       </providers>
   </sessionState>

   Change the connection string accordingly.

3. Provides a SQL script named ASPStateInMemory.sql, which can be used to configure the In-Memory database. Before executing, be sure to update the script with any configuration changes as noted below.

   a. Change the name of the database, which is by default named as ASPStateInMemory. Replace

   CREATE DATABASE [ASPStateInMemory] to CREATE DATABASE <your database name>

   b. Configure the path of the database primary group.
   Replace

   NAME = ASPStateInMemory, FILENAME 'D:\SQL\data\ASPStateInMemory_data.mdf' with NAME = ASPStateInMemory, FILENAME <path of mdf file>

   c. Change the file name that specifies the path for the memory-optimized file group according to your own server. Replace

   NAME = ASPStateInMemory_xtp, FILENAME = 'D:\SQL\data\ASPStateInMemory_xtp' with NAME = ASPStateInMemory_xtp, FILENAME = <path of xtp file>

   d. In-Memory OLTP provides two durability options:

   • SCHEMA_ONLY: in case of server restart, the tables would be recreated and previous data would be lost. It is faster and used when we have transient data. By default it is enabled.
   • SCHEMA_AND_DATA: in case of server restart, the schema and data are maintained, similar to disk-based tables. To configure it, change the DURABILITY attribute from SCHEMA_ONLY to SCHEMA_AND_DATA.

After execution, it creates a new database [ASPStateInMemory] (default) with two tables as dbo.[Sessions] and dbo.[SessionItems].

Dealing with Expired Sessions

When we use SQL for session state management, we need to delete the expired session data. In the traditional way, we used an SQL Server Agent Job to delete the expired session periodically.

Similarly here, we have a new Stored Procedure DeleteExpiredSessions, which can be configured in a job to delete the expired sessions. By default, session timeout is 20 minutes, which can be configured based on our needs, by adding the timeout attribute to the sessionState tag in our web.config, and each time a session is accessed this timeout gets reset.

The Key Benefits

In-Memory OLTP will boost the performance when using SQL Server Session State by a factor of 30–40 times when compared to using a traditional SQL Server Session State. We can also leverage the following features of SQL using In-Memory OLTP:

1. We can make our session highly available with the help of the SQL Server AlwaysOn feature. We can also leverage the traditional way of making high availability using failover clustering. In order to do that, we need to make some changes in the script, like setting durability as SCHEMA_AND_DATA and changing the Id constraint for the SessionItems table.

2. We can make use of the Geo-redundancy feature of the SQL Server, which makes it durable and highly available. Geo-redundancy replicates data between two geographically distant sites, so that applications can switch from one site to another. Using this, in case of a failure on one site for whatever reason, the other site can be used, as it has all the data and configuration available.

3. In-Memory OLTP can be used in Web Farm and Web Garden scenarios.

4. Performance-wise, In-Memory OLTP is as good as InProc mode.

As demonstrated in a recent case study (which you can download as a Word document from Microsoft.com), the throughput of an ASP.NET application – which was using a traditional SQL Server for maintaining the session state – got increased 16 times (15000 to 250000 request per sec) after migration. It allowed for consolidation of many logically partitioned servers into one. It did not require any code changes at all.

Summary

This article has looked at the ASP.NET session state provider and its available options, key challenges, and how each option has its own drawbacks. It also looked at the In-Memory OLTP option, introduced with SQL Server 2014, that can be leveraged for storing session state information.

In-Memory OLTP stores the data in server memory, uses a native stored procedure compiler, and is free from lock/latch contentions. It provides 30–40 times faster performance than its predecessor and can improve an application’s throughput by 16 times after moving to an In-Memory database.

However, there are limitations in SQL Server 2014, such as the size of memory optimized tables, absence of parallel plans, and some issues in native compilations. All of these are expected to be addressed in SQL Server 2016, which will make this feature more powerful and flexible.

Frequently Asked Questions (FAQs) on SQL Server In-Memory OLTP as ASP.NET Session State Provider