Is there a bug or limitation in LINQ to SQL which would cause stored procedures to timeout?

Question

I'm using the .NET Framework 4.0, C#, and SQL Server 2008 R2 on Windows Server 2008 R2. My LINQ to SQL data context is in a seperate library and the code in question is running in a Windows service.

I have a well-tested, mission-critical stored procedure which takes some 19 parameters (I know I know), performs some simple "if" logic and builds a few variables, and inserts data into 3 tables. It doesn't use cursors or temporary tables. I've described what the SP does as I'm not at liberty to post the sql code.

I'm seeing lots of posts across the internet about SqlException due to command timeout, and the responses rarely go beyond "increase the command timeout". Example

I was getting the above-mentioned exception so tried increasing the command timeout when creating the data context to 10 minutes. I still get the exception after it's sat there waiting for those 10 minutes. I then added some debug logging to capture the output from LINQ to SQL and ran the SP in SQL Server Management studio with the same parameter values. It completed successfully in a fraction of a second.

Here's the LINQ to SQL Log output (with timeouts back at default) mixed in with some other log output, I've obfuscated the SP name in this post:

16:01:37 15269 Irrelevant log line, deleted for StackOverflow
EXEC @RETURN_VALUE = [dbo].[NAMEHIDDENONSTACKOVERFLOW] @Eastings = @p0, @Northings = @p1, @Speed = @p2, @UpdateDate = @p3, @UserId = @p4, @Postion = @p5, @Direction = @p6, @VehicleId = @p7, @Status = @p8, @Confidence = @p9, @Latitude = @p10, @Longitude = @p11, @PosLatitude = @p12, @PosLongitude = @p13, @WatchBoxId = @p14, @LastWatchBoxId = @p15, @WatchBoxIdAlert = @p16, @ImbolizationState = @p17, @TowAwayAlertState = @p18
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [560120]
-- @p1: Input Int (Size = -1; Prec = 0; Scale = 0) [5754714]
-- @p2: Input Int (Size = -1; Prec = 0; Scale = 0) [0]
-- @p3: Input DateTime (Size = -1; Prec = 0; Scale = 0) [02/08/2011 20:45:08]
-- @p4: Input Int (Size = -1; Prec = 0; Scale = 0) [11]
-- @p5: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [Swindon United Kingdom]
-- @p6: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [5]
-- @p7: Input Int (Size = -1; Prec = 0; Scale = 0) [15269]
-- @p8: Input Int (Size = -1; Prec = 0; Scale = 0) [901]
-- @p9: Input Int (Size = -1; Prec = 0; Scale = 0) [0]
-- @p10: Input Float (Size = -1; Prec = 0; Scale = 0) [51.939899]
-- @p11: Input Float (Size = -1; Prec = 0; Scale = 0) [-2.125414]
-- @p12: Input Float (Size = -1; Prec = 0; Scale = 0) [51.9333333]
-- @p13: Input Float (Size = -1; Prec = 0; Scale = 0) [-2.1]
-- @p14: Input Int (Size = -1; Prec = 0; Scale = 0) [-1]
-- @p15: Input Int (Size = -1; Prec = 0; Scale = 0) [-1]
-- @p16: Input Int (Size = -1; Prec = 0; Scale = 0) [0]
-- @p17: Input Int (Size = -1; Prec = 0; Scale = 0) [0]
-- @p18: Input Int (Size = -1; Prec = 0; Scale = 0) [0]
-- @RETURN_VALUE: Output Int (Size = -1; Prec = 0; Scale = 0) [Null]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

16:02:23 0 Error in DoPoll 1 Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

The only clue I'm getting from searching Stack Overflow is that there may be an issue with something called "parameter sniffing", but I'm still reading up on that to work out what it's about.

This is really critical stuff and I'll get a lot of hassle if it fails in production, so I'm tempted to roll back the LINQ and return to vanilla ADO. My question is: Is there anything wrong with my approach (put another way: am I being an idiot?) or is there some issue or bug in LINQ to SQL that can be causing this problem? Is there anything I can do to troubleshoot this or would it better to revert to vanilla ADO?

Answer 1

I'll make the assumption that you've tried to increase the time out on you Linq data context, as you mentioned that most of the posts you found suggested that.

However...

What trips a lot of people up in cases like this is the web server, particularly if your running this on IIS7.

Iv'e had cases in the past where I've spent countless hours chasing things down to find out where somthing is timing out, only to fix it in five seconds by changing the time out values for the server application in IIS7 managment panel.

I'm not saying this is definately your issue, but if you only have the web server set to 30 seconds (Which is the default) you can sit and change time outs in your data context all day long, and you'll never get anywhere.

If you want to give this a try, log into the machine the web server is running on, expand the server / website in the left hand pane to drill down to the app your stored proc is run from.

At this point you have a few places to look. If you go back up to Website level EG: "Default Web Site" and click on that, then click on advanced settings to the far right and expand connection limits, you can set the number of seconds for the entire web site in there.

If your calling your SP from a classic ASP file, then click on your webapp, and double click on the ASP icon in the center pane, expand "Limits Properties" and change the script time out value to suit.

Lastly, if your doing this using some kind of CGI operation, click on your web app then double click on CGI, and change the time out in there.

Last but not least if none of the above help, you could try adding the following XML snippet to your apps web.config file in the appropriate place, to control the .NET script execution time out:

    <system.web>
      <httpRuntime executionTimeout="180"/>
    <\system.web>

If your not running on IIS7 then you'll need to look into what ever control panel your server uses for similar settings.