Thursday, November 10, 2005

ASP.NET for Persistent SQL Server Session

I gave a problem with ASP.NET application with SQL Server to hold session state. Everyday encounter blocking on the ASPState database. On busy days waiting times increase.
sessionState element in web.config file :
cookieless="false"
sqlConnectionString="Server=iMySQLServer;Integrated Security=SSPI;"
timeout="5"/>
TempGetStateItemExclusive2 is seen in the connection buffers.
1. Why do we see KEY range locks? e.g. KEY: 7:133575514:1 (0a027a6605d7)
2. Why do the connections have two open transactions?


1) Use the persistent version of AspState db which does not use the temp db

HOW TO: Configure ASP.NET for Persistent SQL Server Session State Management:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q311209

2) Add an index to the Expires column in the AspState db

3) Configure the DeleteExpiredSessions job to run every two or three minutes instead of every minute

No comments: