SQL Server 2012 - Plan Guides

You will be wondering what is this new item called "Plan Guides" in Programmability section of a database in SSMS 2012 as shown below:

SQL Server 2012 comes with many features, Plan Guides is one among them.

Plan Guides will let you optimize the performance of the query without modifying the actual query. In most of the real time scenarios the queries might be embedded in the application code itself and we will not have flexibility to modify/optimize the query within the application, this feature definitely helpful in these kind of scenarios.

There are three types of Plan Guides:
SQL Plan Guide
     This matches the queries which are executed in standalone statements.

Object Plan Guide
     This matches the queries which are executed within the contest of User Defined Functions, Stored Procedures and Triggers.
 
Template Plan Guide
     This matches the standalone queries which can be parametrized to a specified form.

Note that the Plan Guide can be created either by using Management Studio or Transact SQL. I will Transact SQL for all the examples.

Lets take an example to see how SQL Plan Guide works.Let starts with exploring the sp_create_plan_guide procedure.

sp_create_plan_guide procedure is used to create a plan guide for associating the hints to the queries. This built in procedure takes following parameters:

@name -> To provide the name for the Plan Guide
@stmt -> SQL Query Statement where you want to associate the hints
@type -> To Specify the type of the Plan Guide (SQL | Object| Template)
@module_or_batch -> To Specify the object name from the SQL Statement
@params -> To specify query parameters (it is must in case of Type is Template)
@hints -> To specify the hint Option

Now lets create a table and load with dummy data using the following script:

Create Table dbo.Employee (     Empno int Identity(1,1) Primary Key,     EmpName varchar(200) Not Null,     DOB DateTime Not Null,     Salary float ) -- Load Dummy Data for Testing INSERT INTO dbo.Employee (EmpName, DOB, Salary) SELECT TOP 10000                    Enames.FullName,      CAST(RAND(CHECKSUM(NEWID()))*3653.0+CAST(CAST('01-Jan-1950' as DateTime) - CAST('01-Jan-1900' as DateTime) AS INT) AS DATETIME) AS Date,                     CAST(RAND(CHECKSUM(NEWID())) * 10000 as Float) FROM sys.all_columns T1          CROSS JOIN sys.all_columns T2 CROSS APPLY (     SELECT EName AS 'data()'     FROM (         SELECT TOP (CAST(RAND(CHECKSUM(NEWID())) * T1.column_id * T2.column_id as INT) %2 + 1) EName         FROM (         SELECT EName = CAST('Sridhar ' AS VARCHAR(200)) UNION ALL         SELECT 'Kolapalli ' UNION ALL         SELECT 'David ' UNION ALL         SELECT 'Robert ' UNION ALL         SELECT 'Rajesh ' UNION ALL         SELECT 'Naresh ' UNION ALL         SELECT 'Babu ' UNION ALL         SELECT 'Thomas ' UNION ALL         SELECT 'Rich ' UNION ALL         SELECT 'Anthony' UNION ALL         SELECT 'Richard'         ) ENames ORDER BY NEWID()) u2     FOR XML PATH('')         ) Enames(FullName) -- Create a Index on Employee Name Create Index Indx_Emp_Name on dbo.Employee(EmpName)

Lets execute a sample query and see how is the execution plan for the same query:

SELECT count(*) from dbo.Employee E1 INNER JOIN dbo.Employee E2 on E1.EmpName = E2.EmpName

If you closely observe the execution plan for this query, it uses Merge Join.

Now lets see how we can use the Plan Guides to add the hint to the query without modifying the underlying query. Lets create the plan guide with the following TSQL snippet:

EXEC sp_create_plan_guide     @name =  N'SriSampleGuide1',     @stmt = N'SELECT count(*) from dbo.Employee E1 INNER JOIN dbo.Employee E2 on E1.EmpName = E2.EmpName',     @type = N'SQL',     @module_or_batch = NULL,     @params = NULL,     @hints = N'OPTION (TABLE HINT( E1, FORCESEEK))'

This plan guides adds table hint to the query statement mentioned as one of the parameter. Now let try executing the same and see the execution plan, it uses the Table Hint provided in the plan guide.

SELECT count(*) from dbo.Employee E1 INNER JOIN dbo.Employee E2 on E1.EmpName = E2.EmpName

The execution plan for this query looks as:

Note that this sample is only to show how we leverage use plan guides.

Plan Guide is one of the good feature in SQL Server 2012 which helps in optimizing the query without modifying the underlying query. I am sure every application developer appreciate this feature.

Note that creating plan guide either on Object/SQL/template removes the query plan from the plan cache which might impact the overall query performance and also the performance depends on the plan guide too. I will suggest to use plan guide cautiously.