7

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?

Stephen Kennedy
  • 20,585
  • 22
  • 95
  • 108
  • 1
    A few things to try, if it is parameter sniffing: http://elegantcode.com/2008/05/17/sql-parameter-sniffing-and-what-to-do-about-it/ and http://stackoverflow.com/questions/6986607/a-5sec-sp-hitting-a-30sec-timeout-through-linq-to-sql/6986902#6986902 – Jeff Ogata Sep 05 '11 at 17:23
  • http://stackoverflow.com/questions/211355/parameter-sniffing-or-spoofing-in-sql-server is pretty good too. – Stephen Kennedy Sep 05 '11 at 17:53
  • Solution: Reboot! - This one was driving me insane too (small SP, timeout with LINQ, instant in SSMS even with the failing SQL debug print call from profiler). I rebooted my VirtualBox an voila. Damn it! I'll never learn. Always the same. It's hard to think about reboot as a solution coming from linux... – Knickedi Oct 18 '13 at 13:03

3 Answers3

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.

shawty
  • 5,729
  • 2
  • 37
  • 71
0

LINQ-to-SQL uses vanilla ADO.NET under the covers. At least as far as ADO.NET and SQL Server are concerned, you're not anywhere close to maxing out the number of parameters they can handle. LINQ-to-SQL is very stored-procedure-friendly, as long as the stored proc isn't doing anything horrible like creating dynamic SQL inside the proc, or returning results from temporary tables.

How are you creating your Linq-to-SQL mapping for the stored proc? There's a line command tool in Visual Studio's SDK called "SQLMETAL" that creates .dbml and .cs and, if specified by a command line switch, will map stored procedures as well. It does enough analysis on stored procedures to verify that LINQ to SQL will handle them, and will give you error messages for stored procs that don't meet its standards. Try running SQLMETAL and see what kind of messages you get back.

Cylon Cat
  • 7,111
  • 2
  • 25
  • 33
  • Thanks Cylon. I'm aware that vanilla ADO is used under the covers (indeed the exception I'm seeing is an ADO exception) but didn't know that SqlMetal gives helpful output so I'll try that. To answer your question, I'm generating the dbml by dragging and dropping the stored procedure from the server explorer to the data context diagram in Visual Studio 2010. – Stephen Kennedy Sep 05 '11 at 16:06
  • I've always found SqlMetal to be a better tool than the drag-and-drop visual tool (which I consider to be demo-ware, not production quality). Linq-to-SQL does have certain constraints for stored procedures, and I know that SqlMetal checks them. – Cylon Cat Sep 05 '11 at 16:11
  • Tried that, just one warning from an unrelated db object. – Stephen Kennedy Sep 05 '11 at 16:13
0

I reverted to vanila ADO and was getting the same exception so the answer has to be "no it's not a LINQ to SQL bug". I also refactored the SP per the parameter sniffing articles and it didn't help. As the code has started working again I can only imagine that this was a temporary system issue or database glitch. For the benefit of others who have similar issues and find this question by search later I'll update further if I find anything more, as a colleague will be testing the code on another system shortly.

EDIT: It was a system issue; specifically, my VMWare disk needed defragmenting.

(Per advice on meta, I should post my own answer rather than update the question).

Stephen Kennedy
  • 20,585
  • 22
  • 95
  • 108
  • DO you have any 'IF' clauses in SQL SP? I have noted sometimes SQL reports 1 result set, and sometimes more. I'll try find the code I used to determine this. – leppie Sep 07 '11 at 09:20
  • Try running `exec sys.sp_procedure_params_rowset 'yoursp'` a few times and see if the output remains the same. – leppie Sep 07 '11 at 09:28
  • Yes, there are some IF clauses. – Stephen Kennedy Sep 07 '11 at 09:46
  • It's pretty much an atomic piece of code which receives raw data and then works out what to insert and where. As stated it doesn't use cursors or temporary tables though, just select, insert, declare, and boolean logic. Thanks for the tip re the system procedure. Waiting for colleague feedback at the moment. – Stephen Kennedy Sep 07 '11 at 09:52
  • I'm now inclined to believe that it's a hard drive issue! I've been given the green light to deploy on Sunday, I'll post back if I learn anything more which will help others with similar issues. Thanks to all who tried to help. – Stephen Kennedy Sep 08 '11 at 12:19