During peak load the web server gets freeze when storing asp.net session in SQL Server?

-

 

When there is more than 1 server in web farm scenario it is an obvious choice to go for session out-of-proc either with SQL server or State server. While storing session with SQLServer if you see issues with SQL deadlock during peak loads involving DeleteExpiredSessions . It goes like this…

Looking at the current implementation of the DeleteExpiredSessions stored procedure:

CREATE PROCEDURE DeleteExpiredSessions
AS
DECLARE @now DATETIME
SET @now = GETUTCDATE()
DELETE ASPState.dbo.ASPStateTempSessions
WHERE Expires < @now
RETURN 0

GO

This would take the locks on all the expired records for deletion and these locks may be promoted to page locks. This can give rise to deadlocks with other ‘session state write statements’ when the number of records marked for deletion increases. By default this stored procedure is supposed to run every minute.

For Example: In one of the case. Looking at no. of records which are marked for deletion

Select count (*) from ASPState.dbo.ASPStateTempSessions WHERE Expires < getdate()

The number of record marked for expiry was 138,000 out of 146,000 records and the size of “ASPStateTempSessions” table was crossing ~20 GB!!

Next step was to check the session timeout value which can play a vital role in mitigating the problem.

If we have timeout value set to a high value (Which in my case was set at: 360 min) then no matter how many times “DeleteExpiredSessions” runs, it won't be able to delete records resulting in a pile-up and hence deadlock.

In a different case I found the session timeout value was set to 60 mins and we could not reduce to default 20 mins due to business requirement .Then we suggested to change the “DeleteExpiredSessions” 

Another option is to reduce amount of data stored in Session (sessions are meant to store small things like ID’s) and lastly increase the number of servers in SQL environment to handle high load that can help in reducing chances of deadlock.

In ASP .NET 4.0 the “DeleteExpiredSessions” stored procedure has already been modified using the same approach so that it does not hold any locks on table level and we delete the records individually

Comments

Post a Comment

Popular posts from this blog

Email Sending through O365 using OAuth Protocol

-
  Microsoft Office365 EWS servers have been extended to support authorization via the industry-standard OAuth 2.0 protocol. Using OAUTH protocol, user can do authentication by Microsoft Web OAuth instead of inputting user and password directly in application Microsoft / Office 365 OAuth + EWS We can use the OAuth authentication service provided by Azure Active Directory to enable our application to connect with IMAP, POP or SMTP protocols to access Exchange Online in Office 365. To use OAuth with our application we need to register application on Azure Active directory and other steps also we need to do to get the access token back. Below are the steps   To use Microsoft/Office365 OAUTH in your application, you must create an application in  https://portal.azure.com . Sign into the Azure portal using either a work or school account or a personal Microsoft account. If your account gives you access to more than one tenant, select your account in the top ...
Read more

IoT Technology

-
Image
IoT technology The Internet of Things (IoT) is a system of interrelated computing devices, mechanical and digital machines.  IoT is a network of sensors where data is exchanged, using different connectivity protocols, with systems. This set up can be leveraged to create a new business application or to enhance an existing process. The exchange of the data can be bi-directional between sensors and systems Examples of sensors can be temperature sensors, motion sensors, flow meters, heart meter monitors, biometric devices, smoke detectors, & etc Examples of connectivity can be wifi, radio, GPRS, GSM, LAN, WAN, & etc. Examples of systems can be a remote health monitoring applications, SCADA applications, etc Look at next slide about the complete flow of IoT process Examples A doctor in US would like to monitor the heart rate and ECG of a patient in Hyderabad. This is done with the patient having sensors attached to him that collect the ECG data and the hea...
Read more

What is reverse proxy?

-
Image
  What is a reverse proxy? A reverse proxy is a server that sits in front of web servers and forwards client (e.g. web browser) requests to those web servers. Reverse proxies are typically implemented to help increase  security ,  performance , and reliability . In order to better understand how a reverse proxy works and the benefits it can provide, let’s first define what a proxy server is. What’s a proxy server? A forward proxy, often called a proxy, proxy server, or web proxy, is a server that sits in front of a group of client machines. When those computers make requests to sites and services on the Internet, the proxy server intercepts those requests and then communicates with web servers on behalf of those clients, like a middleman. For example, let’s name 3 computers involved in a typical forward proxy communication: A: This is a user’s home computer B: This is a forward proxy server C: This is a website’s origin server (where the website data is stored) In a stand...
Read more