Sql Server Requests Time Out As Tempgetstateitemexclusive Is Getting Called Continuously

I run a site with decent traffic (~100,000 page views per day) and sporadically the site has been brought to its knees due to SQL Server timeout errors. When I run SQL Profiler,

Solution 1:

This may be one of those cases where I needed an answer to a different question. The question should have been "Why am I using SQL to store session state information?" SQL is much slower, and much more disconnected from the web server, both of which may have contributed to this problem. I looked up the size of our ASPStateTempSessions table and realized it was only about 1MB. We moved back to <sessionState mode="InProc" ... /> and the problem is fixed (And the site runs faster)

The next step, when traffic dictates, would be to add another servers and use the "StateServer" mode so we can spread out the memory usage.

I think I originally made this move to deal with a memory bottle neck which is no longer an issue. (This is not a good solution to dealing with a memory bottle neck, FYI!)

IMPORTANT EDIT: Ok, so it turns out that the whole "TempGetStateItemExclusive" thing was not the problem, it was just a symptom of another problem. We had some queries that were causing blocking issues, so every SQL request would just get kicked out. The actual fix was to identify and fix the blocking issues. (I still believe that "InProc" is the way to go, though) This link helped a lot to identify our issues:

http://www.simple-talk.com/sql/sql-tools/how-to-identify-blocking-problems-with-sql-profiler/

Solution 2:

It's been some time, but its there not a cleanup job that runs to remove stale sessions? Is it enabled.

This old KB mentions it. Like I said, it's been a while.

Solution 3:

Just out of curiosity. Have you opened up that proc to see what it does?

If it's just making a select statement, you might look to see if it is using NOLOCK or not. If not, add NOLOCK to it and see what happens.