I want to automate a way to detect when a stored procedure plan changed for worse. I am planning to do it using a list of critical SPs and checking when their plans changed. Then compare the execution metrics with the previous executions collected in extended events.

Does anyone have any recommendations to monitor plans without using a lot of server resources?

  • Do you need to know the plans? Or just the fact that they changed? Is it sufficient to know that the query is much less (or much more) efficient on one day than another? Or do you only want to look for cases where the plan changed? Jun 16 '16 at 3:49
  • I would like capture the plan, recompile the SP and alert me. Is not common that this happens but usually is caused by bad statistics or edge case of parameters sniffing.
    – Jose Chama
    Jun 16 '16 at 3:58
  • 3
    2016 has the query store that would help for this simple-talk.com/sql/database-administration/… Jun 19 '16 at 9:05

I've had that problem for only on query:
1. Scheduled job was looking in query cache for particular query.
2. When query is found it looked for bad pattern in plan. For instance: Index Seek for index XYZ.
3. If bad pattern is found, that query will be automatically deleted from query cache by it's plan-handle using DBCC FREEPROCCACHE

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.