SharePoint 2007 – Improve ASP.NET Session State performance

Hello @all,

today i want to show you a nice fine tuning option for the ASP.NET Session. You all know the problem in each web portal platform that additional data need to be handled in your Session object, e.g. client side, server side.

So we have two options of store temporary data in session object:

Server Side Session object
(e.g. KB 307598 - ASP.NET State Management Overview)

 //Assign a value to the myvariable session variable.
 Session["myvariable"] = "somevalue";
  
 //Retrieve the value of the myvariable session variable.
 string myString;
 if (Session["myvariable"] != null)
   myString = (string)Session["myvariable"];

Client Side Session object – call them Cookie
Depends on web.config setting of your web application and your WebBrowser configuration if cookies are enabled. High security information should not be used for Cookies. (More…)

 // Assign a value to the myvariable Cookie variable
 Response.Cookies["myvariable "].Value = "myValue";
  
 string temp;
  
 // retriebe the value of the myvariable Cookie variable
 if (Request.Cookies["myvariable"] != null)
 {
     temp = Request.Cookies["myvariable"];
 }

Server Side Session Storage location is a SQL Server

SharePoint 2007 handles the ASP.NET session exactly on the same way like ASP.NET. The only difference is the used database. An Out-Of-the-Box ASP.NET Webapplication store each session into the temp database. There you can find the table ASPNETTempSessions which includes all sessions.

In SharePoint 2007 all sessions are stored into your SharedServicesDB.

How to identify your SharedServicedDB?

1. run stsadm –o enumssp –all
2. search for the database type “ServiceDatabase” and get the name of the Database

To get an overview about the activity of your users and session i use such a SQL query:

 DECLARE @now datetime
      SET @now = GETUTCDATE()
 declare @tempTable table(
   titel nvarchar(20),
   sessionCount int,
   date datetime)
 declare @expiredSessions int,
   @activeSessions int,
   @totalSessions int,
   @nowLocalTime datetime
 set @nowLocalTime = getdate()
 set @expiredSessions = (Select count(*) from ASPStateTempSessions
             WHERE Expires < @now)
 set @activeSessions = (Select count(*) from ASPStateTempSessions
             WHERE Expires > @now)
 set @totalSessions = (Select @expiredSessions + @activeSessions)
 insert into @tempTable (titel, sessionCount, date) values ('expired Sessions',@expiredSessions, @nowLocalTime)
 insert into @tempTable (titel, sessionCount, date) values ('active Sessions',@activeSessions, @nowLocalTime)
 insert into @tempTable (titel, sessionCount, date) values ('total Sessions',@totalSessions, @nowLocalTime)
 Select * from @tempTable

As output you should see something like this:

 

So in my small and easy environment I have only 2 old sessions. In production environment I've seen 1000 or 10000 entries.

A server side session implementation like the sample code above runs well for a single user test. But what happen if your webapplication is used by 100, 1000 or 10000 concurrent users?

Please be aware that you have split the page rendering of ASP.NET by using more then one WebFrontEnd Server. But all these request want to access this single SharedServicesDB to read and write the session data.

The next question you should  also beware is: How many session you have in the ASPNETTEMPSESSIONS table? Normally it’s a bit higher because the session timeout is set to 60 minutes and you will see not only active session.

In the beginning of MOSS 2007 i could see custom code which stores everything (each single info) into a server side session variable. this blow up the size of the session variable and the usage of the “ASPNETTEMPSESSIONS”. The session them self is stored for 60 minutes until it will expire. (search for your web.config)

To reduce the traffic and the data size of the session variable it’s an option to reduce the timeout value.  As next option you can change your code to store not all into the server side session and move some data into client side session variables (Cookies).

All session usage will create IO traffic on this table “ASPNETTEMPSESSIONS”. In case you have many active users and large session objects you will see a high IO traffic and may cause SQL blocking on this table. This blocking can create problems, like delays or page rendering time outs.

Additional IO traffic on this table is caused by a schedule action to clean up table “ASPNETTEMPSESSIONS” by execution of stored procedure “DeleteExpiredSessions” to delete expired session from table.

In case you have more then one expired session variable (maybe 100 or 1000) then the whole delete operation create a lock for the whole table until all expired session entries are deleted. In case the stored procedure “DeleteExpiredSessions” runs a longer time, then the lock cause delays and timeputs of new incoming WebRequests. These Request wait on SQL before they can access to the session object from that table.

To improve your Session speed the .Net Team have published a new implementation to clean up the ASP.NET Sessions. KB 973849 - “Improved DeleteExpiredSessions stored procedure in ASP.NET 2.0”

In case you have identified a long running stored Procedure “DeleteExpiredSessions” please be aware of KB 973849 to optimize this single stored procedure.

ATTENTION: KB 973849 is an exception of KB 841057:”issuing SQL commands against a production database is not supported! Please perform the following steps against an offline copy of the database and not against the live database.

regards

Patrick