TempGetStateItemExclusive3 called repeatedly - SQL Server requests timing out from ASP.NET

UPDATE 1/28/10: Ok, so the solution I provide below, isn't actually the fix. The SQL Agent being off was actually a side-effect of restarting the SQL Server. Turning it back on certainly helped things, but we still have seen this blocking issue since this fix was in. I currently have a question into stack overflow about this.

---

I just spent the last couple days trying to track down an issue with SQL performance where there seemed to be a blocking or hanging issue causing SQL requests to timeout. Doing a profiler trace, I found a stored procedure called TempGetStateItemExclusive3 (TempGetStateItemExclusive in older versions) getting called over and over seemingly grinding the server to a halt.

It turns out, the issue was with the ASP.NET Session State stored in SQL Server. Lots of internet searches didn't turn up much. There is an old bug in .NET 1.0 that had to do with serialization of objects. I also found a promising looking hot fix that seems to address this exact issue (but it wasn't our solution).

Our problem ended up being that the ASPStateTempSessions table (stored in either tempdb or ASPState) was growing out of control because no old sessions were ever deleted. Looking up session data then became uber-slow causing ASP.NET to timeout waiting for the session (at which point it tries again, amplifying the problem). You can check how many active session rows you have by doing a simple query like this:

select COUNT(*) from ASPStateTempSessions

The cause for this out of control growth was that our SQL Server Agent was turned of for some reason, so old sessions were never cleaned up.

The fix was to turn the SQL Server Agent back on and make sure the ASPState_Job_DeleteExpiredSessions job is running. We also had to turn on the "SQL Server Agent (MSSQLSERVER)" in the Services section of Windows and make sure the Startup Type is automatic. Our session count is now hovering around 1500 (but I suspect this number would vary vastly based on traffic, the important thing is to make sure it doesn't grow out of control).

Hopefully this helps someone out there, or at least gives you some ideas for ways to track down the problem!