SQL Server请求超时为TempGetStateItemExclusive是越来越连续调用 [英] SQL Server requests time out as TempGetStateItemExclusive is getting called continuously

问题描述

我跑体面交通站点（〜每天100,000个页面访问量）和零星的网站已经一蹶不振，由于SQL Server的超时错误。

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.

当我运行SQL事件探查器，我看到一个命令让所谓轻车熟路第二个是这样的：

When I run SQL Profiler, I see a command getting called hundreds of times a second like this:

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

我们使用SQL Server来存储ASP.NET会话状态。以上是叫抢会话状态给定会话存储过程。这似乎是循环，反复询问同样的2或3的会话。

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.

我发现了一个有前途的期待，似乎为解决这一确切的情况烫，但它似乎没有已经解决了这个问题，对我们来说。 （我假设此修复程序包含在最新的.NET服务包，因为它看起来并不像您可以直接安装它了）。我手动添加的注册表项，但我们还是看到了循环调用存储过程像上面（请求相同的会议往往远远超过每500毫秒）

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)

我一直无法重现这个开发机器上。当两个请求对同一个会话ID制成，它似乎正确地阻止，甚至还尝试打SQL，直到第一页释放会话。

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!!!

推荐答案

这可能是地方，我需要一个答案，一个不同的问题的案件之一。这个问题应该是为什么我用我的SQL存储会话状态信息？ SQL是慢得多，并从Web服务器，这两者都可能已对这个问题作出了贡献更断开。我抬头一看我们的ASPStateTempSessions表的大小，并意识到这是只有1MB左右。我们搬回＆LT;的sessionState模式=是InProc... /＆GT; ，问题是固定的（以及网站运行速度更快）。

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)

下一步，当交通使然，会增加另一个服务器，并使用的StateServer模式，这样我们可以发$ P $垫出来的内存使用情况。

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!)

重要编辑：确定，所以事实证明，整个TempGetStateItemExclusive的事情是没有问题的，这只是一个另一个问题的症状。我们有一些人造成阻塞问题的查询，所以每次SQL请求将只是被踢出。实际的修复是确定并解决阻塞问题。 （我仍然相信是InProc是要走的路，虽然）这个环节帮助了很多找出我们的问题：

IMPORTANT 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/

这篇关于SQL Server请求超时为TempGetStateItemExclusive是越来越连续调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！