Advanced Query Tuning Hints and Plan Guides - Microsoft SQL Server 2012 Performance Tuning - page 365

How it works...
In this recipe, we first created a stored procedure Sales.GetSalesOrderByCountry_
TestPlanGuide . In a real-life example, this stored procedure can be compared to a
database object developed by a third party and which you don't have access to modify. The
procedure accepts a country region as parameter and retrieves sales data by joining tables
Sales.SalesOrderHeader , Sales.Customer , and Sales.SalesTerritory . The data
is filtered by the @Country_region parameter.
We then created a plan guide named MyObjectPlanGuide , by calling the system stored
procedure sp_create_plan_guide . The @stmt parameter in sp_create_plan_guide
represents the exact query text in the stored procedure, which is to be optimized. The @type
parameter is set to OBJECT , as we wanted to create an object plan guide. The @module_or_
batch parameter specifies the name of the stored procedure for which the plan guide is to
be created, which happens to be Sales.GetSalesOrderByCountry_TestPlanGuide
in this example. The @params parameter was set to null in our example, as this parameter is
not relevant to our case. Finally, we specified the query hint OPTIMIZE FOR with the OPTION
clause and with the value US to be optimized for the @Country_region parameter. This
option instructs the SQL server to optimize the query for the US country region. Because
majority of the orders are from the US country region, the overall performance of the query
will be optimized whenever the sales data for the US country region is requested.
We verified the newly created plan guide in Object Explorer in SQL Server Management Studio.
Finally, we executed the stored procedure Sales.GetSalesOrderByCountry_
TestPlanGuide and examined the execution plan of the query to verify that the
execution plan used the plan guide we created.
Implementing a fixed execution plan using
SQL plan guide
As plan guides can be used to specify query hints for SQL statements, they can also specify an
execution plan instead of query hints to force an execution plan.
In this recipe, we first observe that even though there is a non-clustered index on ProductID
column, some of the values of ProductID cause an index scan operation when data is
retrieved. We will create an SQL plan guide so that all the queries as specified by the plan
guide performs the index seek operation for every ProductID by forcing a previously saved
execution plan.