Execution timeout exception in ASP.NET core

Question

My problem is a quite hard to define. We have an SQL Server (version 15.0.2104.1) stored procedure that fills out our web site home page with data. When executed from SSMS it takes abount 0.5 second to finish. Our web site uses ASP.NET Core (Entity Framework) to execute it. Now, the strange part - after creating the procedure it runs fine on our web site. But after a while (a few hours) it starts to get timeout exceptions, while still it runs fine from SSMS. If I only resubmit the procedure code (ALTER PROCEDURE) without changing anything in it, the procedures works fine again on our web site. Then after a few hours the problem returens. I have no clue to what cause it, and since I can not reproduce it on SSMS I have no tools to correct it. Thanks.

• Peter: You might want to read this article sommarskog.se/query-plan-mysteries.html
• J.D.: The stored procedure is likely getting a bad plan eventually from when EF Core executes it, which can be related to parameter sniffing issues. You can use the Profiler to capture the exact query that's running from EF Core and hopefully reproduce it by running that query directly in SSMS which will allow you to grab the execution plan. With that information, we can help you troubleshoot the issues.
• Eyal Hasson: Using the profiler I see the query is a simple call to the stored procedure. Running it from the SSMS have no problems. Also, many times when executed from EF is fine. Only after a few hours it starts blocking.
• Eyal Hasson: Indeed it seems to be caused by parameter sniffing. Inspecting this further. Thanks a lot for the direction.

Answer 1

Another way to troubleshoot this problem is by using Query Store. After enabling Query Store on the database your problem query will most likely appear on the "Regressed Queries" report. Tip: There's a configuration on this report for "Minimum number of query plans". You can set that value to 2 so that only queries with multiple plans show. Once you find the query and identify optimal plan, there's an option to "Force Plan". This temporary solution will tell the engine to force the optimal plan until you can fix the issues with the query.