存储过程在特定用户上失败 [英] 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.对于复杂的查询，这可能会很慢
• 超时后重建索引(或只是统计信息)并重试.这会使所有缓存的计划无效

修复:屏蔽参数

DECLARE @MaskedParam varchar(10)
SELECT @MaskedParam = @SignaureParam

SELECT...WHERE column = @MaskedParam

只需谷歌参数嗅探"和参数屏蔽"

Just google "Parameter sniffing" and "Parameter masking"

这篇关于存储过程在特定用户上失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！