存储过程在特定用户上失败
我有一个存储过程不断失败，并在特定用户上显示错误消息超时已过期".
I have a Stored Procedure that is constantly failing with the error message "Timeout expired," on a specific user.
所有其他用户都可以正常调用 sp，甚至我也可以使用查询分析器正常调用 sp——它只需 10 秒即可完成.但是对于有问题的用户，日志显示 ASP 总是挂起大约 5 分钟，然后超时中止.
All other users are able to invoke the sp just fine, and even I am able to invoke the sp normally using the Query Analyzer--it finishes in just 10 seconds. However with the user in question, the logs show that the ASP always hangs for about 5 minutes and then aborts with a timeout.
我像这样从 ASP 页面调用EXEC SP_TV_GET_CLOSED_BANKS_BY_USERS '006111'"
I invoke from the ASP page like so "EXEC SP_TV_GET_CLOSED_BANKS_BY_USERS '006111'"
有人知道如何诊断问题吗?我已经尝试查看数据库中的死锁，但没有找到.
Anybody know how to diagnose the problem? I have already tried looking at deadlocks in the DB, but didn't find any.
谢谢，
推荐答案
一些想法...
阅读评论表明参数嗅探导致了问题.
Reading the comments suggests that parameter sniffing is causing the issue.
- 对于其他用户，缓存的计划对于他们发送的参数来说已经足够了
- 对于这个用户，缓存的计划可能是错误的
如果此用户的行数远多于其他用户，或者在另一个表中有行(因此不同的表/索引查找/扫描会更好)，则可能会发生这种情况
This could happen if this user has far more rows than other users, or has rows in another table (so a different table/index seek/scan would be better)
测试参数嗅探:
- 在调用或定义中(临时)使用 RECOMPILE.对于复杂的查询，这可能会很慢
- 超时后重建索引(或只是统计信息)并重试.这会使所有缓存的计划无效
修复:屏蔽参数
DECLARE @MaskedParam varchar(10)
SELECT @MaskedParam = @SignaureParam
SELECT...WHERE column = @MaskedParam
只需谷歌参数嗅探"和参数屏蔽"
Just google "Parameter sniffing" and "Parameter masking"
相关文章