CHAPTER 28 ■ PERFORMANCE TUNING
ON v.VendorID = a.VendorID INNER JOIN Person.Address p ON a.AddressID = p.AddressID'
Looking at the graphical execution plan in Figure 28-27, you’ll now see that the Nested Loop joins have changed into Merge join operators instead—all without changing the actual query being sent from the application to SQL Server.
Figure 28-27. Query execution plan after applying a Plan Guide
If it is decided that this Merge join is no longer more effective than a Nested loop join, you can drop the plan guide using the sp_control_plan_guide system-stored procedure:
EXEC sp_control_plan_guide N'DROP', N'Vendor_Query_Loop_to_Merge'
How It Works
Plan guides allow you to add query hints to a query being sent from an application without having to change the application itself. In this example, a particular SQL Statement was performing Nested Loop joins. Without changing the actual query itself, SQL Server “sees” the plan guide and matches the incoming query to the query in the plan guide. When matched, the hints in the plan guide are applied to the incoming query.
The sp_create_plan_guide allows you to create plans for stand-alone SQL statements, SQL statements within objects (procedures, functions, DML triggers), and for SQL statements that are either being parameterized or not, due to the database’s PARAMETERIZATION setting.
In this recipe, the first parameter sent to sp_create_plan_guide was the name of the new plan guide:
EXEC sp_create_plan_guide @name = N'Vendor_Query_Loop_to_Merge',
The second parameter was the SQL statement to apply the plan guide to (white space characters, comments, and semicolons will be ignored):
@stmt = N'SELECT v.AccountNumber, p.PostalCode FROM Purchasing.Vendor v INNER JOIN Purchasing.VendorAddress a ON v.VendorID = a.VendorID INNER JOIN Person.Address p ON a.AddressID = p.AddressID',
The third parameter was the type of plan guide, which in this case was stand-alone SQL: @type = N'SQL',