Increase Query Performance in SQL Server Using sp_create_plan_guide System Stored Procedure

dbtut May 9, 2019 MSSQL

sp_create_plan_guide introduced with sql server 2005. With this sp, you can increase the performance of your queries that you can’t intervene or change, by adding a plan guide. (we’re adding query hints to the relevant sp or tsql in the plan guide).

After you create the plan guide and run sp or sql in the normal way, it will now work as you specified in the plan guide.

You can use the following script to see if there is a plan guide in the database.

SELECT * FROM sys.plan_guides

1	SELECT * FROM sys.plan_guides

If you want to delete an existing plan guide, you can delete it from the programmability at the bottom of the database and then from the Plan Guides tab.

You can also use the following script to delete a specific plan guide.

EXEC sp_control_plan_guide N'DROP', N'Guide3';

1	EXEC sp_control_plan_guide N'DROP', N'Guide3';

Plan Guide Types

There are 3 types of plan quide.

Object Plan Guides
SQL Plan Guides
Template Plan Guides

Object Plan Guides

A type of plan guide applied to an object in the database. For example, there is parameter sniffing problem about a stored procedure, and you can not add the OPTIMIZE FOR query hint to the end of the query for certain reasons.

I recommend you read the article “What is Parameter Sniffing” to understand parameter sniffing before starting the examples.

You can create a plan guide to the stored procedure as follows.

Example Stored Procedure

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader h, Sales.Customer c, 
        Sales.SalesTerritory t
    WHERE h.CustomerID = c.CustomerID
        AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country
END

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country nvarchar(60))

BEGIN

SELECT *

FROM Sales.SalesOrderHeader h, Sales.Customer c,

Sales.SalesTerritory t

WHERE h.CustomerID = c.CustomerID

AND c.TerritoryID = t.TerritoryID

AND CountryRegionCode = @Country

END

As you can see, the stored procedure has a Country parameter as a parameter. Consider that 90% of the values in the Country column in the table are “Turkey”, and 10% is “United States”. When the query first executes, it generates a query plan. Suppose the Query Plan is generated for the “United States” value. When the query is then executed for the “Turkey” value, the parameter sniffing will occur.

Therefore, by creating a plan guide and adding a query hint to sp, we can generate the query plan for “Turkey” value. This will result in faster results.

sp_create_plan_guide 
@name = N'Guide1',
@stmt = N'SELECT * FROM Sales.SalesOrderHeader h,
        Sales.Customer c,
        Sales.SalesTerritory t
        WHERE h.CustomerID = c.CustomerID 
            AND c.TerritoryID = t.TerritoryID
            AND CountryRegionCode = @Country',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country = N''Turkey''))'

sp_create_plan_guide

@name = N'Guide1',

@stmt = N'SELECT * FROM Sales.SalesOrderHeader h,

Sales.Customer c,

Sales.SalesTerritory t

WHERE h.CustomerID = c.CustomerID

AND c.TerritoryID = t.TerritoryID

AND CountryRegionCode = @Country',

@type = N'OBJECT',

@module_or_batch = N'Sales.GetSalesOrderByCountry',

@params = NULL,

@hints = N'OPTION (OPTIMIZE FOR (@Country = N''Turkey''))'

SQL Plan Guides

As the name implies, it is a kind of plan guide that is created for an sql statement rather than an object.

Imagine that you have a query as follows.

SELECT TOP 1 * FROM Sales.SalesOrderHeader h ORDER BY OrderDate DESC

1	SELECT TOP 1 * FROM Sales.SalesOrderHeader h ORDER BY OrderDate DESC

Let us assume that the maxdop setting on the instance on which the query is running is set to 8, and this query runs slowly due to the maxdop setting in instance. By adding OPTION (MAXDOP 1) query hint to the end of the query, we can have the query run with a single cpu.

To learn what MAXDOP is, you may want to read the article “Numa Nodes, MAX / MIN Server Memory, Lock Pages In Memory, MAXDOP“.

In the example below, you can see how to add maxdop query hint to the end of the query using the sql plan guide.

sp_create_plan_guide 
@name = N'Guide1', 
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader h ORDER BY OrderDate DESC',  
@type = N'SQL',
@module_or_batch = NULL, 
@params = NULL, 
@hints = N'OPTION (MAXDOP 1)'

sp_create_plan_guide

@name = N'Guide1',

@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader h ORDER BY OrderDate DESC',

@type = N'SQL',

@module_or_batch = NULL,

@params = NULL,

@hints = N'OPTION (MAXDOP 1)'

Template Plan Guides

The behavior of some queries changes when we set parameterization in the database. With the Template plan guide, we can change the parameterization setting for specific queries.

For parameterization, you may want to read “Optimize for ad hoc workloads and Parameterization in SQL Server“.

For example, if the parameterization setting in the database is set to FORCE and you want a query to run as simple parameterization, this plan guide will be useful.

In the following example, you can see how this is done.

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template 
    N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
      FROM Production.ProductModel AS pm 
      INNER JOIN Production.ProductInventory AS pi ON pm.ProductModelID = pi.ProductID 
      WHERE pi.ProductID = 101 
      GROUP BY pi.ProductID, pi.Quantity 
      HAVING sum(pi.Quantity) > 50',
    @stmt OUTPUT, 
    @params OUTPUT;
EXEC sp_create_plan_guide 
    N'TemplateGuide1', 
    @stmt, 
    N'TEMPLATE', 
    NULL, 
    @params, 
    N'OPTION(PARAMETERIZATION FORCED)';

DECLARE @stmt nvarchar(max);

DECLARE @params nvarchar(max);

EXEC sp_get_query_template

N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total

FROM Production.ProductModel AS pm

INNER JOIN Production.ProductInventory AS pi ON pm.ProductModelID = pi.ProductID

WHERE pi.ProductID = 101

GROUP BY pi.ProductID, pi.Quantity

HAVING sum(pi.Quantity) > 50',

@stmt OUTPUT,

@params OUTPUT;

EXEC sp_create_plan_guide

N'TemplateGuide1',

@stmt,

N'TEMPLATE',

NULL,

@params,

N'OPTION(PARAMETERIZATION FORCED)';

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux