SQL Server Plan Guide – How To Create One – And Why You Would Want To!

I have to be honest with you, until I began studying for exam 70-461 (Querying Microsoft SQL Server 2012) I had little to do with SQL Server Plan Guides.

It became clear to me however, that if I wanted to pass the exam, I needed to figure out exactly what they are, and why they are used.

What Is A SQL Server Plan Guide?

A Plan Guide is something that applies either a fixed query plan, and/or query hints to a given query.

Note: Plan guides should not be overused – the recommendation is to ONLY use them for mission critical code, that must be optimized for better performance.

Why Would You Use A Plan Guide?

If you have a query that needs optimiztion yet you are unable to (or don’t want to) change the actual query itself, you can use a plan guide.

How To Create A Plan Guide With SSMS

With most scenarios such as this, the easiest way to grasp the concept is by way of a simple example.

In this case we will be provided with a simple query, which we want to optimize.

Simple Query

For our example we have kept it simple. This will show you how you can add query hints to a simple query.

-----------------------------------------------
SELECT *
  FROM [DBAFire_DB].[dbo].[Dogs]
  Where TypeOfDog Like '%e%'
-----------------------------------------------
  

To create the plan navigate to the database you wish to create the plan under and expand the programmability option.

Right click on Plan Guides and select New Plan Guide.
sql server plan guide

How To Create A Plan Guide With T-SQL

If I script out the prior plan guide via SSMS this is the code that is created.

-----------------------------------------------
USE [DBAFire_DB]
GO

/****** Object:  PlanGuide My Guide    Script Date: 02/12/2015 10:01:48 ******/
EXEC sp_create_plan_guide @name = N'[My Guide]', @stmt = N'SELECT *
  FROM [DBAFire_DB].[dbo].[Dogs]
  Where TypeOfDog Like ''%e%''', @type = N'SQL', @module_or_batch = N'SELECT *
  FROM [DBAFire_DB].[dbo].[Dogs]
  Where TypeOfDog Like ''%e%''', @hints = N'OPTION (MAXDOP 1)'
GO
-----------------------------------------------

How To Check Your Query Is Using SQL Plan Guide

The easiest way I have found is to show the execution plan in SSMS and then right click and view the XML. Make sure the plan guide name is being used, as below.

sql plan guide

I hope this has helped you with a basic understanding of what a plan guide is, and a situation where you could use one. For more indepth explanation please check out the MS articles below.

Links and Resources

MS – Designing and Implementing Plan Guides
MS – Understanding Plan Guides

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.