Blitz Result: Query Plan Guides Enabled
Some people think they know more about how to execute a query than the SQL Server, and they want to boss SQL Server around. SQL Server’s plan guide feature lets you influence a query’s execution plan without changing the query itself – useful if you need to tune a query without touching it. This part of our SQL Server sp_Blitz script checks sys.plan_guides looking for these kinds of surprises.
The problem is, very few people really know this much.
And even worse, your data changes over time. SQL Server changes over time. Hardware changes over time. The plan guides made 5-6 years ago might be completely irrelevant to how the server needs to perform today. That’s why we named plan guides one of the 2 features of SQL Server you should avoid.
If you’ve been tuning your server and you just aren’t getting the results you expect, or queries aren’t responding to new indexes, there might just be plan guides involved. (And since you’re reading this page, they are!)
To Fix the Problem
Review the DMV sys.plan_guides to learn more about the plan guides in place on this server and learn more about how plan guides work. Most of the time, the fix is to disable the plan guide and see if the query’s performance improves.