LINQ to SQL Stored Procedure. Exception says it times out but it is not timing out

Website using .NET Framework v3.5, SQL Server 2008, written in C#

I have a stored procedure which I have added to my DBML by dragging it across from the server explorer.

In it's properties it returns Auto-generated type.

The procedure takes < 1 second to run from within SQL Mgmt Studio for all inputs. However from the code for 1 particular input (which takes < 1 second in the Mgmt studio) it hangs and then throws:

System.Data.SqlClient.SqlException: Timeout expired.

This didn't always happen for this one input! It used 开发者_开发百科to also work fine when called from the code. The last time it didn't work I deleted and re-added the same stored procedure to the DBML. This "fixed" it, and that input ran fine and in the same time as all the others. However this is not an adequate fix! It has happened again and I can't keep deleting and re-adding as required.

I made no changes to the data that's being returned during the point at which it was "fixed", so I can't think what the problem could be. Any help on this would be much appreciated!

Exception says it times out but it is not timing out

If it says it's timing out, it's timing out. The only question is "why"?

Run a SQL Server Profiler trace against your database and see what query is actually going to the server. It's possible that another query is being issued too. It's possible there is another transaction interfering in your production scenario.

It turns out that this is parameter sniffing - this is explained in another post: Executing stored proc from DotNet takes very long but in SSMS it is immediate

Also, be sure that the stored procedure is not being held up inside of a transaction, waiting for another process to complete. I just ran across this with a Linq to Sql stored procedure being called multiple times within a transaction. It gave me a timeout expired error and I just realized it was waiting for a previous call to complete, and thus timing out.