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.

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.

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.

Thanks,

回答1:

Some thoughts...

Reading the comments suggests that parameter sniffing is causing the issue.

• For the other users, the cached plan is good enough for the parameter that they send
• For this user, the cached plan is probably wrong

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)

To test for parameter sniffing:

• use RECOMPILE (temporarily) on the call or in the def. This could be slow for complex query
• Rebuild the indexes (or just statistics) after the timeout and try again. This invalidates all cached plans

To fix: Mask the parameter

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

SELECT...WHERE column = @MaskedParam

Just google "Parameter sniffing" and "Parameter masking"

回答2:

I think to answer your question, we may need a bit more information.

For example, are you using Active directory to authenticate your users? Have you used the SQL profiler to investigate? It sounds like it could be an auth issue where SQL Server is having problems authenticating this particular user.

回答3:

Sounds to me like a dead lock issue..

Also make sure this user has execute rights and read rights in SQL Server

But if at the time info is being written as its trying to be read you will dead lock, as the transaction has not yet been committed.

Jeff did a great post about his experience with that and stackoverflow. http://www.codinghorror.com/blog/archives/001166.html

回答4:

Couple of things to check:

1. Does this happen only on that specific user's machine? Can he try it from another machine? - it might be a client configuration problem.
2. Can you capture the actual string that this specific user runs and run it from an ASP page? It might be that user executes the SP in a way that generates either a loop or a massive load of data.
3. Finally, if you're using an intra-organization application, it might be that your particular user's permissions are different than the others. You can compare them at the Active Directory level.

Now, I can recommend a commercial software that will definitely solve your issue. It records end-to-end transactions, and analyzes particular failures. But I do not want to advertise in this forum. If you'd like, drop me a note and I'll explain more.

回答5:

Well, I could suggest that you use SQL Server Profiler and open a new session. Invoke your stored procedure from your ASP page and see what is happening. While this may not solve your problem, it can surely provide a starting point for you to carry out some 'investigation' of your own.