Shulou(Shulou.com)06/02 Report--

How to use SQL Server In-Memory to store the session state of ASP.NET? for this question, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a simpler and easier way.

From the previous "classic" ASP to the current ASP.NET 4.5 Web Forms, many developers rely on ASP.NET session state as an important primary means of temporarily preserving each user's data. It is characterized by allowing developers to store and read users' data during the user's access to web applications. Session data is automatically continuously saved and recovered from storage, and automatically expired and deleted.

problem

Using alternatives to Session State is beyond the scope of this article. There are also pitfalls for applications that rely on Session State, and the most common is to access the Session underlying data per user, per request. This unique access is a way to maintain the consistency of the Session State and is implemented by design. If you are interested in the design of such brutal details, they explain it here in the section entitled "locking session-state data". Session state is common in ASP.NET Web forms applications, while ASP.NET MVC uses TempData to a lesser extent (POST data to GET as an example).

Web applications mainly use Session state to coordinate their work with each other. In contrast, heavyweight web applications with more client scripts usually have higher concurrent requests. In this case, using Session state to access resources requires locking and unlocking Session, which becomes the bottleneck of Web applications. Unrestricted types of Web applications will be another bottleneck because sufficient storage space is needed to maintain the state of their sessions. There are three ways to optimize access to the Session state so that some requests do not require session or use read-only, but if the application continues to grow after loading, there will still be bottlenecks in the end.

The current situation

Based on these considerations, the current session state of ASP.NET is still very common. In many areas, I continue to see many consumers using session state in Web applications with a large number of extensions. For a large number of enterprise users, internal use of ASP.NET forms applications is more common. For these consumers, how to choose a Session State storage provider is very critical. These providers must serialize the contents of the Session dictionary on a durable device and deserialize to extract data from it (typically using BLOB applications). There are many providers to choose from, including tools provided by Microsoft and third-party developers. Microsoft currently provides the following Session storage tools, assuming that ASP.NET applications are deployed within the enterprise:

Session Provider

Can be Highly Available?

Can be Geo Redundant?

Can be used in Web Farms?

Performance?

In-Proc

No

No

No

Excellent

State Server

No

No

Yes

Good

SQL Server (Traditional)

Yes

Yes

Yes

Fair

AppFabric Caching

Yes

No

Yes

Good

SQL Server (In-Memory)

Yes*

Yes

Yes

Excellent

* need to mark schemas and data as persistent in the in-memory table

If your application requires high availability of Session State and supports cross-web farm deployment, you can provide options from Microsoft, limited to SQL Server or AppFabric Caching.SQL Server, which has an added advantage that it can provide geographic redundancy (geo-redundancy) across data centers. AppFabric, on the other hand, is limited by a single data center. In practical application, both solutions work well. However, traditional SQL Server implementations often encounter bottlenecks due to competition for table storage based on a single disk. Competition leads to blockages, deadlocks, or other unfriendly changes. This affects the time it takes to store and resume sessions. In addition, during the delete operation, there will be problems when the previous session data is cleared due to lock expansion and contention extension.

New options for SQL Server 2014

In order to solve the performance problem of the old SQL Server installation package, the SQL Server team recently released a new installation package "Microsoft ASP.NET Session State provider for SQL Sever In-Memory" as the NugGet package. There is proof of the incredible performance improvement of this installation package in this case study. It uses Session State in ASP.NET applications and processes 250000 requests per second! This new implementation uses a memory-optimized table feature called "Hekaton" in SQL Server 2014. This requires version 2014 of this product. How does this installation package improve on the previous version of the SQL Server session state installation package?

Session storage persistence is achieved through memory-optimized tables rather than disk tables. For heavy access models, such as storing session state, the memory optimization table is transactional, sustainable and ideal. Such tables use lock-free data structures and optimized, multi-version concurrency control.

To further improve performance, locally compiled stored procedures can be used to recover and store session data. In essence, this is a new type of stored procedure that is compiled into native machine code.

These two SQL 2014 product features solve major performance and competition issues that exist in the old traditional SQL Server installation package based on disk implementation. It is quite straightforward to install and configure this program. Through the NuGet package management console, you can install as follows:

Install-Package Microsoft.Web.SessionState.SqlInMemory.

In your application, the NuGet package will add a reference to Microsoft.Web.SessionState.SqlInMemory and a script file named ASPStateInMemory.sql to install the SQL Server 2014 SessionState database. This file contains the necessary DDL to install the database. There are some items in the SQL script that you want to review or are most likely to review or modify:

The default name of this database is ASPStateInMemory.

The primary filegroup path of the database.

The MEMORY_OPTIMIZED_DATA filegroup path of the database.

The size information of the BUCKET_COUNT, which is based on the expected size of the items in the session.

The decision to make tables in a session durable or non-durable (involving the need for session high availability)

The fifth part above requires some analysis of the existing SQL Server session database, which may be as simple as calculating the DATALENGTH () of the BLOB column in the traditional ASP.NET SQL Server session mode. It is more difficult for InProc or StateServer to determine the average size of session items, but it can be achieved by capturing the memory dump of the w3wp.exe or StateServer process and checking the number and size of items in the session dictionary. For InProc or StateServer, there is a performance count on the number of items in the session. * recommendations are always tested and adjusted.

Make memory-based sessions highly available

By default, the memory optimization table of SQL Server 2014 based on memory sessions is marked as non-persistent. This means that the data changes in these tables are transitional and consistent. These changes are not recorded in the log, which means that if SQL Server restarts, the server restarts, or any form of failure recovery occurs (FCI or AlwaysOn), all session data will be lost. This default value is set because of performance. To make these memory-optimized tables sustainable, you need to make three changes in the ASPStateInMemory.sql script. There are some comments in the script explaining why these changes need to be made.

Modify the SessionItems table as follows.

Modify WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)

For WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)

Uncomment the statement (note the comma of * *): Id bigint IDENTITY

Uncomment the statement (note the comma of * *, change 1000000 * 2 to realvalue as needed, read the T-SQL comment before this statement to select a starting value): CONSTRAINT [PK_SessionItems_Id] PRIMARY KEY NONCLUSTERED HASH (Id) WITH (BUCKET_COUNT = 2000000)

Modify the session table

Modify WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)

For WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)

As long as we make these changes, we can make the database a partial SQL Server AlwaysOn availability group. When the failure recovers, the session data will be retained. Due to the added retry logic, when an automatic or man-made failure recovery occurs, expired connections in the connection pool will not be thrown to the end user.

Note that even if we set the table to be unsustainable and put the session database into the SQL Server AlwaysOn availability group, the data in the session table is not available for replication (only schema is available). For customer loads, this "schema only" replication model is sufficient to ensure performance improvement by using non-sustainable memory optimized tables.

This simplest high-availability topology is most appropriate for SQL Server In-Memory and is similar to the following:

SQL Server 2014 Node 1 in Subnet (data Center) A

SQL Server 2014 Node 2 on Subnet (data Center) B

File sharing in Subnet (data Center) C

This topology provides geographic redundancy, automatic failure recovery and maintains a complete lost connection in the data center of 1 Server 3. Windows Server 2012 R2's dynamic characteristics make it possible to automatically maintain lost connectivity in 2 data centers. (* male scenario [last man standing scenario]).

ASP.NET profile

In the configuration file web.config of the ASP.NET web application, configure a new provider and edit it as follows.

In the code snippet above, 'AGAspNet' is the always available listener name in SQL Server 2014.

A quick example

Using the ASP.NET Web form 4.5 application and writing a simple timestamped string in the session, the following data is generated in SQL Server 2014:

Note the location of the AspStateInMemory database in SQLNode1-2014. Next, we manually perform the failure recovery availability group.

On SQLNode2-2014, sessions are now available and do not interfere with ASP.NET applications. Simply tap F 5 of the web application to get data from the session without throwing an exception to the client.

What happens to expired sessions?

In the old SQL Server session, a SQL Agent job was created to delete the expired session. In the new version, a stored procedure [dbo]. [DeleteExpiredSessions] that must be called by the job is provided. By default, the session timeout is 20 minutes. Each time a session item is accessed, the timeout is reset to keep the user session alive.

Overview

There are many interesting details in the new session state. I encourage you to delve into the code for yourself. You will find it a wonderful learning journey about the performance and limitations of SQL Server 2014's memory-based OLTP "Hekaton" feature. A special property is included in the code to simulate storing data of type BLOB in memory. Memory optimization is shown in that the BLOB type is not supported. What's the difference between serialized session dictionaries and possible large BLOB data types? The preprocessor (sprocs) is used to split the serialized session into 7000-byte blocks to enhance the storage of large session item data.

Savvy readers may have found that in my screenshot, there is no row in the [SessionItems] table, but there is a row in the [Sessions] table. If my session exceeds 7000 bytes, you should see a spill over line in the SessionItems table. In this regard, there are many other potential applications beyond ASP.NET session storage, which I will probably dig deeper in the next article.

Locally compiled stored procedures are also worth a look. There are some techniques to deal with the limitations of locally compiled stored procedures, such as the lack of support for CASE statements. This limitation is because branching is not allowed as long as the preprocessor (sproc) is compiled into native code!

If you are considering using this new feature, there are the following key points and issues to consider:

Memory optimization tables are supported by memory! Does your SQL Server have enough memory to contain all the session data at peak load?

The default table is not sustainable. Please carefully consider your high availability requirements. Whether durable or non-durable, performance will exceed the session and mode of the traditional SQL Server database you use today.

Read the comments in the SQL file and adjust the BUCKET_COUNT to a non-clustered HASH index. The following is a code snippet of the SessionItems table DDL statement.

This is the answer to the question on how to use SQL Server In-Memory to store the session state of ASP.NET. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.