1

We have a query in our C# / LINQ to EF code which consistently caused a timeout when run through our web application (ASP.NET MVC).

Error message:

Execution Timeout Expired.
The timeout period elapsed prior to completion of the operation or the server is not responding.

Running the SQL generated by LINQ directly in SSMS was almost instantaneous, regardless of the size of data returned or queried (we're using date ranges in the query).

This answer solved the timeout issue: we ran 

exec sp_updatestats

on the db and now the timeout no longer occurs.

However, as was mentioned in the comments, does this solve the actual problem?

Does it prevent the problem from happening in the future?

  1. Since the query didn't cause any issues running directly in SSMS, does this indicate a problem with ASP.NET / EF?

  2. Or is a maintenance plan the correct approach? I don't know anything about maintenance plans. I see questions about rebuilding indices, backups etc. If I need a maintenance plan to prevent this timeout issue from occurring in the future, what type of plan must I set up?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Winks
  • 580
  • 1
  • 7
  • 17
  • Answer: no. In my long career as a developer on many different platforms I learned that you should never fight timeouts by changing settings/configuration etc. The only fail-safe thing is: reduce the amount of work you're trying to do in one call. – Gert Arnold Nov 19 '17 at 10:16
  • 1
    @GertArnold I would like to hear more why not? in my case I run on daily basis at 2:00 am a job to update all statistics `sp_updatestats` which takes sometimes 1 hour to finish but next day all users happy, database size ( 30GB +) – Monah Nov 19 '17 at 12:14
  • @GertArnold - thanks for your insight. What approach can I use to optimize my query - or reduce the amount of work I'm doing in it? The query in question isn't any more complex than the other stuff we regularly do. The timeout happened only when we queried our data within a specific date range. The same query with a different date range did NOT produce the timeout. How could we proceed with this information? – Winks Nov 19 '17 at 12:19
  • 1
    @Monah Sure, each database needs maintenance plans (statistics, index defrag/rebuild). We apply the same script as mentioned in the answer to our customers' databases. But that's to prevent gradual degradation when the built-in mechanisms don't suffice (which they hardy ever do). It's not to fight consistently recurring timeouts. Then it's time to tackle the problem at the root: reduce the task size, improve indexes and/or queries, restructure the data, or, as a last resort, buy better hardware. – Gert Arnold Nov 19 '17 at 19:32
  • 2
    @Winks That's hard to tell without knowing the data. Maybe some date ranges cause searches in physically scattered parts of the table. You may have to reevaluate its clustered index: http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key – Gert Arnold Nov 19 '17 at 19:36
  • @GertArnold I opened a new question to address some questions I had on the article you mentioned. https://stackoverflow.com/q/47417056/660223 – Winks Nov 21 '17 at 15:46

1 Answers1

3

I'll first mention that regular database maintenance that includes updating stats and/or index reorgs is a best practice. This can be implemented with a maintenance plan, the popular Ola Hallengren's SQL Server Maintenance Solution, or your own scripts. Ola's solution selectively performs maintenance based on thresholds to avoid unneeded overhead.SQL Server uses a cost-based optimizer so more accurate row count estimates gleaned from statistics will help provide most optimal execution plans.

The significantly different query performance between SSMS and the app code suggests different execution plans. As to why the plans might be different for the same query, I suggest you peruse Erland Sommarskog's article Slow in the Application, Fast in SSMS for details. Common causes for different plans include different session SET options or parameter sniffing as detailed in the article. Also, note you'll need to execute the SSMS query using a parameterized sp_executesql query to better mimic the parameterized query EF executes. Local variables or literals in SSMS are not the same thing as parameters and may result in different plans.

When you run sp_updatestats, not only does it update statistics, it will invalidate cached execution plans so it addresses both parameter sniffing and stale stats. DBCC FREEPROCCACHE alone would address parameter sniffing, which should be targeted to the problem plan alone in a production OLTP environment.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71