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:

Live query statistics

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

  • 2
    Sounds like parameter sniffing issue. If you restart the service, execution plans are removed from te cache. You can try to add option(recompile) to the stored procedure – HoneyBadger Oct 15 '20 at 11:31

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/ )

  • hum...Maybe that is it...Theproblem is that I'm unable to reproduce it on my dev machine, so not sure...It seems like I'll have to wait another year to get the same error :) – Luis Abreu Oct 15 '20 at 13:58
  • If indeed this is the issue, then you can't reproduce it, but you can fake it. :) However, I will start with the disclaimer: this is a good trick for demo and learning, but you should NOT do this on a PROD server (t can cause major issues). There is this command that allows you to play around with your statistics: UPDATE STATISTICS testtable WITH ROWCOUNT = <insert _number_here>, PAGECOUNT = <insert _number_here> This article has more details than I can write in a comment: mssqltips.com/sqlservertip/2870/… – LadyBug1 Oct 16 '20 at 14:18
  • Also, in case you have a Pluralsight account, there is a good training from Joe Sack: Troubleshooting Query Plan Quality Issues; in the first part he explains how this works. Probably you can find something similar on Youtube, I don't have the time to search right now and I don't want to recommend something I did not watch or find useful myself. – LadyBug1 Oct 16 '20 at 14:23
  • Thanks @ladybug. For now, and since we weren't able to colect data or debug it throroughly at the time, we've went with the wrong query plan issue and I've went the kitchen sink solutionhttps://www.sentryone.com/blog/aaronbertrand/backtobasics-updated-kitchen-sink-example. There's still the parameter sniffing issue that might affect the query, but lets see how it goes – Luis Abreu Oct 20 '20 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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