Debug stored procedure that times out from year to year

Question

We have a stored procedure that joins a couple of tables and returns json. We've added indexes and it returned the expected results without any issues for months. Btw, here's a snapshop of the live query statistics for an execution:

Today, and after being in production for more than a couple of years, it suddenly stopped working. Whenever it gets executed, it times out and doesn't return anything. Restarting the instance solved the issue and it's fast again. When it was timing out (ie, before we restarted the instance), I've tried running the query to see if there was any index suggestions, but it kept timing out.My colleagues say that CPU and memory weren't issues (CPU was pretty low and more than 50% of memory was available).

As expected, logs (sql and machine) show nothing out of the ordinary.

Interestigly, the reamining queries work as expected.

Any clues on how to debug this issue? I'd really like to understand what happened, but not sure on what to check...

Answer 1

If you are using parameters, then it sounds like a classic case of parameter sniffing. This article gives a better explanation that I could do right now: https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/

Another thing to check is, in the execution plan, the estimated number of rows vs. the actual number of rows. If you find they are mismatching then you can do an update statistics on that table (or index) which will do 2 things: update the statistics (obviously) + clear out the bad plan so that a new and hopefully good one will get created. This is the official documentation for the update statistics command: https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-ver15 If you find this is what is causing the problem then for a long term fix take a look at what and when is causing your statistics to be out of date and why SQL Server is not updating them automatically. In my experience it's either somebody messing around with the settings and turning off the auto-update stats, or that table has grown very big and the threshold for the auto-update of stats is not reached anymore (This is a nice article about that: https://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/ )