You maintain a large production database on an instance of SQL Server 2008. The database is used by a several third-party applications. One of the applications includes a stored procedure defined as follows:
CREATE PROCEDURE Sales.GetSalesByTerritory
(
@Territory nvarchar(30)
)
AS
BEGIN
SELECT *
FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c,Sales.SalesTerritory AS t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND Name = @Territory
END;
Most sales orders are taken in the Northeast territory, and the stored procedure is performing poorly because it was originally optimized for the Southwest territory.
You want to optimize performance of the stored procedure without modifying any third-party code.
Which action should you take?
A. Create a template plan guide that includes the RECOMPILE hint.
B. Create a nonclustered index on the Name column of the SalesTerritory table.
C. Create an object plan guide that includes the OPTIMIZE FOR hint.
D. Partition the SalesOrderHeader table on the TerritoryID column.
Correct Answer: C
Explanation/Reference:
In this scenario, the stored procedure was optimized for a territory Name value of Southwest. However, most sales orders are taken in the Northeast territory.
Therefore, performance would be improved if the query were optimized using a value of Northeast for the territory name. You can use the OPTIMIZE FOR query hint to optimize a query for specific parameter values. However, in this scenario, the stored procedure resides in a third-party application that you do not want to modify. Therefore, you should create a plan guide. Plan guides can be used to optimize queries without modifying the statement directly.
Plan guides can be useful when a small subset of queries in a database application deployed from a third-party vendor are not performing as expected. Plan guides influence optimization of queries by attaching query hints or a fixed query plan to them. In the plan guide, you specify the Transact-SQL statement that you want optimized and either an OPTION clause that contains the query hints you want to use or a specific query plan you want to use to optimize the query. When the query executes, SQL Server matches the Transact-SQL statement to the plan guide and attaches the OPTION clause to the query at run time or uses the specified query plan.
You create plan guides using the sp_create_plan_guide system stored procedure, which accepts the following parameters:
@name - Specifies a unique plan guide name.
@stmt - Specifies the Transact-SQL statement or batch associated with the plan guide.
@type - Specifies the type of plan guide. Valid values are OBJECT, SQL, and TEMPLATE.
@module_or_batch - Specifies the module name if the plan guide applies to a specific module, such as a stored procedure.
@params - Specifies an optional list of parameters to be used for SQL and template plan guide types.
@hints -Specifies an OPTION clause that includes hints to be used during optimization.
After you create a plan guide for a SQL statement, SQL Server matches the Transact-SQL statement to the plan guide when the query executes and uses the specified OPTION clause hints or query plan for the statement. In this scenario, you wanted to optimize the query in the stored procedure for a specific parameter value. You could use the following statement to create the plan guide:
sp_create_plan_guide
@name = N’SalesByTerritoryGuide’, @stmt = N’SELECT * FROM Sales.SalesOrderHeader AS h,Sales.Customer AS c,Sales.SalesTerritory AS t WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND Name = @Territory’,
@type = N’OBJECT’, @module_or_batch = N’Sales.GetSalesByTerritory’,
@module_or_batch = N’Sales.GetSalesByTerritory’, @params = NULL,
@params = NULL, @hints = N’OPTION (OPTIMIZE FOR (@Territory = N”Northeast”))’
When the query executes, it will be optimized using an @Territory parameter value of ‘Northeast’. To view details of plan guides created in a database, you can query sys.plan_guides.
You should not create a template plan guide that includes the RECOMPILE hint. The RECOMPILE hint is used to force recompilation, which was not applicable in this scenario. In addition, template guides are used to override the PARAMETERIZATION database setting for a group of queries, not to optimize a single query for a specific parameter value.
You should not create a nonclustered index on the TerritoryName column of the SalesTerritory table. In this scenario, you needed to optimize the query for a specific parameter value. Creating a nonclustered index would not provide this.
You should not partition the SalesOrderHeader table on the TerritoryID column. Partitioning makes large tables easier to manage. Partitioning also speeds up access because you can access smaller subsets of table’s data more quickly and maintain the data integrity, as well as spread the partitions across file groups to improve I/O performance.
