由于连续调用 TempGetStateItemExclusive，SQL Server 请求超时
我运行的网站流量不错（每天约 100,000 次页面浏览量），但该网站偶尔会由于 SQL Server 超时错误而瘫痪。
当我运行 SQL Profiler 时，我看到一个命令每秒被调用数百次，如下所示：
...
exec dbo.TempGetStateItemExclusive3 @id=N'ilooyuja4bnzodienj3idpni4ed2081b',...
...
我们使用 SQL Server 来存储 ASP.NET 会话状态。上面是为获取给定会话的会话状态而调用的存储过程。它似乎在循环，一遍又一遍地要求相同的 2 或 3 个会话。
我发现了一个看起来很有希望的修补程序，它似乎可以解决这个确切的情况，但它并没有似乎已经为我们解决了问题。 （我假设此修补程序包含在最新的 .NET 服务包中，因为它看起来不再可以直接安装）。我手动添加了该注册表项，但我们仍然看到像上面这样的循环存储过程调用（请求同一个会话的频率远高于每 500 毫秒），
我无法在开发计算机上重新创建它。当对同一个会话ID发出两个请求时，它似乎正确地阻塞，甚至尝试命中SQL，直到第一页释放会话。
有什么想法吗？先感谢您！！！
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!!!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这可能是我需要不同问题的答案的情况之一。问题应该是“为什么我使用 SQL 来存储会话状态信息？” SQL 速度慢得多，并且与 Web 服务器的连接更加断开，这两者都可能导致此问题。我查看了 ASPStateTempSessions 表的大小，发现它只有 1MB 左右。我们回到
下一步，当流量需要时，将添加另一个服务器并使用“StateServer”模式，这样我们就可以分散内存使用。
我想我最初采取这一举措是为了解决不再是问题的内存瓶颈。 （这不是处理内存瓶颈的好解决方案，仅供参考！）
重要编辑：好吧，事实证明整个“TempGetStateItemExclusive”并不是问题，它只是一个问题。另一个问题的症状。我们有一些查询导致阻塞问题，因此每个 SQL 请求都会被踢出。实际的修复是识别并修复阻塞问题。 （不过，我仍然相信“InProc”是可行的方法）此链接对识别我们的问题有很大帮助：
http://www.simple-talk.com/sql/sql-tools/how-to-identify-blocking-problems -with-sql-profiler/
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/
已经有一段时间了，但是没有运行清理作业来删除过时的会话吗？是否已启用。
此旧知识库提到了它。就像我说的，已经有一段时间了。
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.
只是出于好奇。你打开那个进程看看它做了什么吗？
如果它只是创建一个 select 语句，您可能会查看它是否使用 NOLOCK。如果没有，添加 NOLOCK 看看会发生什么。
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.