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:
Adds an assembly reference of
Microsoft.Web.SessionState.SqlInMemory
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.
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
. ReplaceCREATE DATABASE [ASPStateInMemory]
toCREATE DATABASE <your database name>
b. Configure the path of the database primary group. Replace
NAME = ASPStateInMemory, FILENAME 'D:\SQL\data\ASPStateInMemory_data.mdf'
withNAME = 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'
withNAME = 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 theDURABILITY
attribute fromSCHEMA_ONLY
toSCHEMA_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:
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 theSessionItems
table.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.
In-Memory OLTP can be used in Web Farm and Web Garden scenarios.
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
What is the significance of SQL Server In-Memory OLTP in ASP.NET Session State Management?
SQL Server In-Memory OLTP is a significant feature in ASP.NET Session State Management because it enhances the performance of transaction-intensive applications. It achieves this by storing session state data in memory rather than on a disk, which significantly reduces the time taken to read and write data. This feature is particularly beneficial for applications that require high-speed data access and processing, such as e-commerce websites or financial systems.
How does SQL Server In-Memory OLTP compare to traditional disk-based storage?
SQL Server In-Memory OLTP offers several advantages over traditional disk-based storage. Firstly, it provides faster data access because it eliminates the need for disk I/O operations. Secondly, it reduces contention and blocking issues that can occur in disk-based systems. Lastly, it offers a higher degree of concurrency, which is beneficial for applications with a high volume of simultaneous users or transactions.
How can I implement SQL Server In-Memory OLTP in my ASP.NET application?
Implementing SQL Server In-Memory OLTP in your ASP.NET application involves several steps. Firstly, you need to create a memory-optimized database and table in SQL Server. Then, you need to configure your ASP.NET application to use this database for session state management. This can be done by modifying the sessionState element in the web.config file of your application.
Are there any limitations or drawbacks of using SQL Server In-Memory OLTP?
While SQL Server In-Memory OLTP offers several benefits, it also has some limitations. For instance, it requires a significant amount of memory, which can be a constraint for some systems. Additionally, it does not support all SQL Server features, such as foreign keys and triggers. Therefore, it’s important to evaluate these factors before deciding to use In-Memory OLTP in your application.
Can I use SQL Server In-Memory OLTP with other state management techniques in ASP.NET?
Yes, you can use SQL Server In-Memory OLTP in conjunction with other state management techniques in ASP.NET. For instance, you can use it with cookie-based state management for storing small amounts of data, or with database-based state management for storing large amounts of data. The choice of state management technique depends on the specific requirements of your application.
How does SQL Server In-Memory OLTP improve the scalability of my ASP.NET application?
SQL Server In-Memory OLTP improves the scalability of your ASP.NET application by allowing it to handle a larger number of simultaneous users or transactions. This is because it reduces contention and blocking issues that can occur in disk-based systems, and offers a higher degree of concurrency. Therefore, if your application needs to scale to accommodate a high volume of users or transactions, In-Memory OLTP can be a beneficial feature to implement.
What types of applications can benefit the most from SQL Server In-Memory OLTP?
Applications that require high-speed data access and processing can benefit the most from SQL Server In-Memory OLTP. This includes transaction-intensive applications, such as e-commerce websites or financial systems, as well as applications with a high volume of simultaneous users or transactions.
How does SQL Server In-Memory OLTP affect the performance of my ASP.NET application?
SQL Server In-Memory OLTP can significantly enhance the performance of your ASP.NET application by reducing the time taken to read and write data. This is because it stores session state data in memory rather than on a disk, which eliminates the need for disk I/O operations. Therefore, if your application requires fast data access and processing, In-Memory OLTP can be a beneficial feature to implement.
Can I use SQL Server In-Memory OLTP with ASP.NET MVC?
Yes, you can use SQL Server In-Memory OLTP with ASP.NET MVC. The process of implementing it is similar to that of a regular ASP.NET application. You need to create a memory-optimized database and table in SQL Server, and then configure your ASP.NET MVC application to use this database for session state management.
How can I monitor the performance of SQL Server In-Memory OLTP?
SQL Server provides several tools and features that you can use to monitor the performance of In-Memory OLTP. For instance, you can use the Performance Monitor tool to track various performance counters related to In-Memory OLTP, such as memory usage, transaction rate, and latency. Additionally, you can use the Dynamic Management Views (DMVs) provided by SQL Server to monitor the state and performance of In-Memory OLTP objects.
Brij Bhushan Mishra is a 5-time Microsoft MVP, author, blogger, and speaker. He is currently associated with an MNC as an Architect in Microsoft technologies. He is passionate about learning and sharing knowledge in Web Technologies. He is associated with various online/offline community activities, regularly speaks in conferences and user group events, and blogs regularly on his personal site.