1

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.

4
  • 3
    You might want to read this article sommarskog.se/query-plan-mysteries.html
    – Peter
    Commented Feb 6 at 15:04
  • 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.
    – J.D.
    Commented Feb 6 at 17:07
  • 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. Commented Feb 8 at 14:54
  • Indeed it seems to be caused by parameter sniffing. Inspecting this further. Thanks a lot for the direction. Commented Feb 18 at 17:19

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.