Tuesday, April 20, 2010

Two facts you may not know about storing ASP.Net Session in SQL DB

  1.   If saving of large objects in the Session in InProc mode is bad practice then doing the same in SQLServer mode is real evil. The main problem in this case is serialization/deserialization data on every request to the server. Each user's session is a single record in ASPStateTempSessions table so all objects need to be deserialized even if you request something which is rather small. Symptoms of this problem: you notice using a profiler that usual int userId = Session["UserId"] takes a few seconds!!! to execute.
  2.     If you noticed that ASPStateTempSessions table is rather large and permanently grows and grows then check if the SQL Server agent is running. It is responsible for deleting of expired sessions. It should have a job to execute the DeleteExpiredSessions stored procedure every minute (by default).