5

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, I see a command getting called hundreds of times a second like this:

...
exec dbo.TempGetStateItemExclusive3 @id=N'ilooyuja4bnzodienj3idpni4ed2081b',...
...

We use SQL Server to store ASP.NET session state. The above is the stored procedure called to grab the session state for a given session. It seems to be looping, asking for the same 2 or 3 sessions over and over.

I found a promising looking hot fix that seems to address this exact situation, but it doesn't seem to have solved the problem for us. (I'm assuming this hotfix is included in the most recent .NET service pack, because it doesn't look like you can install it directly anymore). I added that registry key manually, but we still see the looping stored procedure calls like above (requesting the same session much more often than every 500ms)

I haven't been able to recreate this on a development machine. When two requests are made for the same session ID, it seems to block correctly, and even try to hit SQL until the first page releases the session.

Any ideas? Thank you in advance!!!

JerSchneid
  • 5,817
  • 4
  • 34
  • 37

3 Answers3

4

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/

JerSchneid
  • 5,817
  • 4
  • 34
  • 37
  • 2
    There are clearly pros and cons to both InProc and out of process session states - if you're not really storing anything in SessionState, or more importantly, there's nothing that can't easily be rebuilt in session state, then InProc is fine. If however your sessions need to persist app restarts, or server switching (in a load balanced environment) - for example shopping carts, etc. then InProc isn't really "the way to go" as it will fail in those scenarios. – Zhaph - Ben Duguid Mar 22 '13 at 13:30
  • 2
    ***Performance issues*** about `DeleteExpiredSessions`: https://sqlperformance.com/2013/01/t-sql-queries/optimize-aspstate – Kiquenet Oct 27 '16 at 13:50
  • i like inProc, too but you cant do that in a web-farm. – visual Oct 20 '17 at 00:51
  • @Zhaph-BenDuguid : What if the sopping carts are stored in database and the site does not have load balancers ( only 1 server ) , which mode you will recommend ? – Sagar Sep 17 '19 at 18:11
  • @Sagar I'd probably still consider using an out of process store for the session data, but would probably use a NoSQL option like Redis Cache or similar, if only to persist session data over app restarts and deployments. – Zhaph - Ben Duguid Sep 17 '19 at 19:09
0

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.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • 1
    Yes, our SQL Server Agent is running, along with the ASPState_Job_DeleteExpiredSessions job. The number of sessions stored in ASPStateTempSessions seems to be consistent, hovering around 1500. – JerSchneid Jan 28 '10 at 18:37
0

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.

NotMe
  • 87,343
  • 27
  • 171
  • 245
  • I have looked at that proc. It is an auto-generated one provided by Microsoft, so I haven't gone as far as messing with it just yet. The locking mechanism is actually based on a "Locked" column of the ASPStateTempSessions table. That proc sets the Locked column and another one called by ASP.NET (TempReleaseStateItemExclusive) releases it. – JerSchneid Jan 28 '10 at 20:01
  • Based on your question it sounds like you guys aren't running all of the service packs. why not just updating everything? – NotMe Jan 28 '10 at 22:05
  • What makes you think we aren't running all of the service packs? We are totally up to date. (That line in my description about the hotfix was just to imply that we didn't manually install the hotfix, because we have installed the most recent service pack) – JerSchneid Jan 29 '10 at 15:42