ASP.Net Sessions with SQL Server
I recently ran into some problems with ASP.Net session state with a SQL server provider. At times the SQL Database is being hit extremely hard with updates locks and reset timeout events.
Consequently I have spent a fair amount of time researching the optimisation of session state and I thought I’d write up some of my findings for future reference.
Setting up the SQL Session State Provider
I am not going to go into setting up the SQL Session state provider, for more information ion this see: HOW TO: Configure SQL Server to Store ASP.NET Session State.
A bit about the SQL Server Session State DB
The State DB is database provided by Microsoft to manage session state. Session state data is stored in a table called: ASPStateTempSessions. This table is managed via a number of stored procedures, in particular:
- TempGetStateItem3 (there are 3 versions of this suffixed: 1,2 or 3). This proc retreives session data without locking the session row.
- TempGetStateItemExclusive3 (there are 3 versions of this suffixed: 1,2 or 3). This procedure retreives session data with an exclusive lock on the row.
- TempInsertStateItemLong (there are 2 versions of this suffixed: Long or Short). Inserts data into the session DB.
- TempResetTimeout
- And several others………
It is worth mentioning here that the State DB maintains its own locking mechanism. The ASPStateTempSessions table has a column called “Locked”. If the calling client requires an exclusive lock on the session row (i.e. TempGetStateItemExclusive) and this column is set to “1″ then the request will be blocked until the lock is released.
Configuring Session State at the page level
The easiest way (and perhaps) the only way to configure SQL session state is to use the page level directive:
[sourcecode language='c#']
|##@ Page EnableSessionState= ……
[/sourcecode]
This directive has 3 modes: True, False and Readonly. I would recommend that wherever possible this is set to “False” or “Readonly” as it reduces traffic to the Session DB. Only pages which need to update, add or remove session state data require this directive to be set to “True”.
The Impact of the EnableSessionState directive
I spent several days analysing a sample ASP.Net application configured to use a SQL session state provider. Using SQL Profiler I analysed the impact on the session DB of these pages directives. My observations were as follows:
EnableSessionState=”True”
Enabling session state as above has no impact on the Session DB until a session ID is allocated (typically by adding some session state). However, once a session ID has been allocated and is flowing backwards and forwards from the browser, the following stored procs will be invoked against the Session DB on every request:
- TempGetStateItemExclusive3
- TempReleaseStateItemExclusive
These stored procs are called on each request, even if the page does not actually do anything with session sate.
If the page does actually manipulate session state, then the following stored procs are invoked:
- TempGetStateItemExclusive3
- TempUpdateStateItemShort (or one of the other similar proc’s ….)
EnableSessionState=”False”
With this setting configured, the session DB is not hit. However surprisingly if the Session ID is active, the following following calls are made to the Session DB for each request:
- TempResetTimeout
Admittedly this is much lower impact than for full session mode, but I did find it surprising that that Session DB is hit at all. Further investigation revealed that the purpose of this call is simply to keep the session alive.
EnableSessionState=”ReadOnly”
The final option is the “ReadOnly” mode. As it says on the tin this allows the page to read session data but not to update it. Once again this mode has a lower impact that the full session mode. Once again, accessing a “ReadOnly” page when there is no active session has no impact on the Session DB. When a session is active the following stored proc’s are invoked:
- TempGetStateItem3
Other Findings
What surprised me most of all is that once a session is active the session DB is hit for every page request, even when EnableSessionState is set to “False”. This behaviour was particularly problematic when a page is accessed from an Ajax call. Worse still, is that even when you clear out all of your session state (Session.RemoveAll()) and attempt to kill off the session (Session.Abandon()) the SessionID lives on and the Session DB continues to be impacted. I did find a work around for this as detailed below:
Killing off a Session
As mentioned above, cleaning out your session data and abandoning the session is not enough to properly kill of a session. To kill of a session you need to tell the browser to lose the session id. You do this by sending a session cookie with the response. To terminate a session do the following:
- Call Session.RemoveAll();
- Call Session.Abandon();
- Call Response.Cookies.Add(new HttpCookie(“ASP.NET_SessionId”, “”));
That’ll do it….
SessionIDManager
I also spent some time investigating using a custome SessionIDManager as discussed in this article: Fast, Scalable, and Secure Session State Management for Your Web Applications.
At first I thought that I might be able to used this handler to stop certain page requests from accessing the session DB. I did manage to implement one of these, but for me it didn’t quite work in the way the article suggested. I am assuming that this article is slightly out of date as the “GetSessionID” method is not overrideable. I did manage to override the CreateSessionID method, but this method is only called when creating new sessions. If the browser already has a session ID from a previous page request, this method is not invoked…..
Conclusions
Based on my findings, I would recommend the following:
- Whenever possible avoid using session state.
- Use the EnableSessionState page directive appropriately and bear in mid the default behaviour is EnableSessionState=True.
- If your application has used session state but no longer requires it, (e.g. in a shopping cart the user has added some items to a basket and then removes them all) end the session properly as described above.
Other suggestions
Based on other articles that I have read I would also suggest the following:
- Keep session storage to a minimum and keep the data light.
- Don’t store complex object graphs as the seizialization can be expensive.
- If you do need to store object graphs use the serialization attributes to reduce overhead.
- Consider implementing custom serialisation logic for complex object graph.
Useful Links
Here are some links that I found useful when researching this: