Inexplicable query timeout with ASP.NET and SQL Server

Question

Sometimes, my web application is throwing timeout exceptions when trying to execute a specific stored procedure. From that moment on, the stored procedure will never execute again until I reboot the database server.

The strange thing is that I can execute the stored procedure manually from within the SQL Server Management studio, and the execution time is correct (about 0.2 seconds).

But if the same exact call is made from the webserver... Timeout. How is that possible?

I am using SQL Server 2012 and I'm mapping the stored procedure in my code using Linq2Sql.

Additional information: I have tried running the "detect blocking" sql from this blog post: http://blog.sqlauthority.com/2010/10/06/sql-server-quickest-way-to-identify-blocking-query-and-resolution-dirty-solution/ but no rows are returned.

Are there any other specific conditions when it happens? For example, maybe it only happens when debugging? — Alex Filipovici
– Alex Filipovici, Commented Jan 14, 2013 at 9:41
No, it happens also when no process is attached to the debugger. I am running IIS on my machine (Windows 7). — Andrea
– Andrea, Commented Jan 14, 2013 at 9:44
Not on any of the tables interested by the query that blocks.. And by the way, if transactions were the problem, shouldn't I be unable to execute the query also from the management studio? — Andrea
– Andrea, Commented Jan 14, 2013 at 9:49
Can you post the SP definition? And maybe the way you are using it with the LINQ to SQL? — Alex Filipovici
– Alex Filipovici, Commented Jan 14, 2013 at 9:52

Wim · Accepted Answer · 2013-01-14 12:35:32Z

1

Possibly a bad query plan is cached in SQL server. You could try to recompile the Stored Procedure with the Recompile option. With this option the SP is not cached and is recompiled every time it is called.

answered Jan 14, 2013 at 12:35

Wim

1,98512 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Inexplicable query timeout with ASP.NET and SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related