Hi Geeks,

Think of a situation when you want to apply query hint to a query that is part of application code but development team is unwilling to touch application code. Does this mean there is no way other than changes to code, the answer is NO. We can force a query to use a specific plan with the help of SQL Server plan guide.

We can use sp_create_plan_guide stored procedure to create a plan guide. If you want to drop or disable a plan guide sp_control_plan_guide can help you with. All plan guide details can be listed from sys.plan_guides catalog view.

Let’s see an example of plan guide now.


Now we are going to create a plan guide and force our query statement to use LOOP JOIN.


From the above figure, same stored procedure now returns Nested Loop (read here Part1, Part2) join instead of Hash Join in the execution plan. We are now safe to drop our plan guide and stored procedure.

You can find index to the execution plan series here and click on One operator a day to visit exclusive page for this series.

Signing off for today, Happy Learning!



Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook