Service broker and bad plans - any way (besides reboot) to correct it if FREEPROCCACHE doesn't?

Question

We have a Service Broker activated stored procedure that's constantly running on 30 spids on a sql server 2012 server (yes, I know, we're migrating it next month). Every once in a while (like, it happened last week, but not in the past 9 months) it gets a bad plan and can't process fast enough. The stored procedure is a combination of SQL and a CLR. Doing a checkpoint/DROPCLEANBUFFERS/freeproccache doesn't seem to fix it, even run multiple times, but a reboot almost always does.

Is there any reason why that doesn't fix it? Is there any way TO fix it? Thanks.

Answer 1

It is impossible to answer the question with the information you have shared. You need to track down what is the slow statement. And for that matter, you need to investigate if there is some blocking.

If you don't know what is slow and why it is slow, you cannot fix it confidence. DBCC FREEPROCCACHE and things like that are just shots in the dark.

Maybe you should wait until you have a newer version of SQL Server installed. Not the least because then you can activate Query Store which will help you track down slow queries a lot more easily.

Query Store is also good to catch regressions that may occur when you move to a higher compatibility level. So a possible strategy is:

1. Install the new version of SQL Server. Keep the compat level.
2. Enable Query Store.
3. Run for a week or two.
4. Change the compat level to the new one.
5. If a query regresses, you can find it with help of Query Store.

There is even an option for this in SSMS, you find it at the bottom of the Tasks context menu for a database.

Answer 2

Did you try adding OPTION (RECOMPILE) to that stored procedure or to some of its queries?