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?
Since the query didn't cause any issues running directly in SSMS, does this indicate a problem with ASP.NET / EF?
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?
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.