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...