Techbubbles

Creating a Plan Guide in SQL Server 2012

Plan Guide is a very nice feature in SQL Server that allows you to apply hints to your queries without having to change the query type or text in the application. Plan Guides influence query optimization by using the query hints. This post shows you how to create a Plan Guide and apply to a query in SQL Server 2012.

Take a look at the below query and it’s execution plan, it basically performs some lookups to return the number of rows.

image

The execution plan for the above query having index seek followed  by Key lookup.

image

If this query returns large number of rows then it will have some performance issues. To scale or optimize this query we will create a cluster index scan on this query without changing the query text. so we use Plan Guide to optimize this query.

In order to create Plan Guide in SQL Server 2012 go to Management Studio and choose PlanGuide under the programmability section in the database as shown below

image

Enter a name for your Plan Guide in the new dialogue box and enter your query text that you want to create  a Plan Guide and select the scope type as SQL.

image

Use the Option clause in Hints section and specify the table alias and index that you want to do the full scan. After applying the Plan Guide using hint to your query then you can see the change in the query execution plan

image

You can notice the query now using Cluster Index Scan to grab the data from the table. This is very useful functionality if you want influence certain query execution plans without changing the query text.


Share this post :


Related Posts:

%d bloggers like this: