存储过程就没有一个特定的用户 [英] Stored Procedure failing on a specific user
问题描述
我有一个存储过程,是不断随着错误消息没有超时,在一个特定的用户。
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(暂时)在电话会议上或在DEF。这可能是缓慢的复杂查询
- 重建超时后指数(或只是统计),然后再试一次。这会使所有缓存计划
要解决:
面膜参数
To fix: Mask the parameter
DECLARE @MaskedParam varchar(10)
SELECT @MaskedParam = @SignaureParam
SELECT...WHERE column = @MaskedParam
只是谷歌参数嗅探和参数掩蔽
Just google "Parameter sniffing" and "Parameter masking"
这篇关于存储过程就没有一个特定的用户的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!