1

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!

bean
  • 1,091
  • 1
  • 12
  • 23

3 Answers3

1

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.

Dave Markle
  • 95,573
  • 20
  • 147
  • 170
  • Thanks for the quick response. I will try this and see what comes up. – bean Feb 17 '11 at 10:43
  • Hi Dave, I tried the profiler on 4 different templates. What in particular should I be looking for? As it seems to tell me what I already know - that the SP is being called at x, and ends at x + 30s. There are other queries going on in the mean time, however the same SPs occur _(roughly same; some are heartbeats from different areas of the application and will not occur at exactly the same time)_ when I trace the same SP with different parameters. – bean Feb 17 '11 at 11:51
  • Do you see a different number of reads and writes when you call the stored proc inside SSMS vs Linq to SQL? Take a look at the parameters and make sure you're passing the exact same parameters that your code is. If you really are calling the same stored proc with the exact same parameters on the exact same database, and you aren't seeing any blocking, that would indeed be strange. – Dave Markle Feb 17 '11 at 12:43
  • Yes. There is a different amount of reads and writes. From SSMS it is 37708 Reads, 11 Writes, 281 CPU. Linq-to-SQL has 9482492 Reads, 29 Writes, 29569 CPU. They seem very different! This doesn't seem like a good sign. The Parameters are definitely exactly the same. – bean Feb 18 '11 at 09:45
  • I "Fixed" the stored procedure in a different way last night - I modified the SP to include TOP(x), and then modified it again to remove the TOP(x), and then it started working through LINQ-to-SQL! I don't know what this could point to but it seems like it could be useful information! – bean Feb 18 '11 at 09:54
1

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

Community
  • 1
  • 1
bean
  • 1,091
  • 1
  • 12
  • 23
0

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.

webtrifusion
  • 4,536
  • 2
  • 14
  • 9