0

We have a production app which runs ok normally, but gave us very poor performance this week. As it was very urgent, I identified the problematic procedure and ran sp_recompile against it (forgot to take the plan first).

After running sp_recompile, the system became fast again. For me, this indicates a parameter sniffing issue. However, as I forgot to take a plan first, hard to be 100% sure. Could there be other explanations that sp_recompile speeds up the performance dramatically?

We use Azure SQL read only replica.

0

It's probably parameter sniffing, but the documentation for sp_recompile does note that performance can drop for other reasons as well:

The queries used by stored procedures, or triggers, and user-defined functions are optimized only when they are compiled. As indexes or other changes that affect statistics are made to the database, compiled stored procedures, triggers, and user-defined functions may lose efficiency. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries.

That is not to say that some of the above things won't trigger an automatic recompile, however. In fact, most of those should trigger a recompile, which is why I wouldn't spend too much time looking down that road.

Depending on your version, enabling Query Store might make it easier to track regressions without having to manually capture plans.

Without the query plans, the stored procedure itself, etc. I would default to assuming parameter sniffing in this case until it happens again. I don't believe there is much more to talk about without more details.

3
  • thanks, we use azure sql read only replica, query store unfortunately doesn't work there. – daxu Jun 4 at 14:43
  • 2
    I wonder if that doc statement is old. To my knowledge, those things should cause a recompile. Perhaps MS left is there in case they forgot to trigger recompile for some of these operations? – Tibor Karaszi Jun 4 at 15:03
  • @daxu if you are satisfied with my answer, you can accept it. Otherwise I recommend adding more details as to what you want out of an answer so that others can work on something more complete for you. – LowlyDBA - John M Jun 8 at 16:10

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.