3

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,

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
bastos.sergio
  • 6,684
  • 4
  • 26
  • 36
  • What do you mean by a "specific user"? The user under which the SP is ran, or this 006111 you have in the query? – Sunny Milenov Nov 07 '08 at 17:01
  • '006111' is the user id that is failing, although if I invoke this in Query Analyzer the SP finishes in about 10 seconds. – bastos.sergio Nov 07 '08 at 17:03
  • 3
    I was going to ask, Is the user's name "Robert'); DROP TABLE EMPLOYEES;--?" – Echostorm Nov 07 '08 at 17:06
  • Is there something going on with the execution context? That is, can you run this Stored Proc and ONLY this stored proc from an ASP page (not Query Analyzer) and have it work, or does it still time out? – Paul Sonier Nov 07 '08 at 17:15
  • Like I said, other users invoking the page can call the SP just fine. The only other anomality that I can find, is that the sp is loaded into a recorset and this recordset crashed twice doing an "rsUBanks.MoveFirst" on that particular user. – bastos.sergio Nov 07 '08 at 17:27
  • Are u sure that the user has enough rights? – eddy147 Nov 07 '08 at 17:29
  • all the users share the same connection... So it's not a question of permissions... – bastos.sergio Nov 07 '08 at 17:31
  • If all the users share the same connection, then I cannot parse the "other users invoking the page" comment above. Do you have one user account which invokes the SP with different parameters, or do you have different user accounts invoking the SP with their private parameters? – Paul Sonier Nov 07 '08 at 17:58
  • There's one database connection shared across all ASP pages, and all users have a different login (which is gettable from a sql table) to identify them while calling the sp. In this case the user's login is 006111. – bastos.sergio Nov 07 '08 at 18:14
  • OK, if you create a sample ASP page, which just invokes this SP with that user, and then iterates trough the recordset, does it work, or it still times out? – Sunny Milenov Nov 07 '08 at 18:33
  • Just in case, Did you try to log into the SQL Server Instance using the specific user credentials and run the thing manually? It's not clear from your question - This way you'll be able to rule out a number of possible problems. – JohnIdol Nov 07 '08 at 19:00

5 Answers5

5

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"

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
gbn
  • 422,506
  • 82
  • 585
  • 676
0

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.

Chris Gillum
  • 14,526
  • 5
  • 48
  • 61
0

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

dswatik
  • 9,129
  • 10
  • 38
  • 53
0

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.

Traveling Tech Guy
  • 27,194
  • 23
  • 111
  • 159
0

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.

Ghazaly
  • 118
  • 2
  • 8