Saturday , September 23 2023

Increase Query Performance in SQL Server Using sp_create_plan_guide System Stored Procedure

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.

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.

Plan Guide Types

There are 3 types of plan quide.

  1. Object Plan Guides
  2. SQL Plan Guides
  3. 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

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.

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.

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.

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.


About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *