TechBubbles Microsoft Technology BLOG

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.


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


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


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.


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


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 :

About the author

Kalyan Bandarupalli

My name is kalyan, I am a software architect and builds the applications using Microsoft .NET technologies. Here I am trying to share what I feel and what I think with whoever comes along wandering to Internet home of mine.I hope that this page and its contents will speak for me and that is the reason I am not going to say anything specially about my self here.

Add Comment

TechBubbles Microsoft Technology BLOG

Follow me


Tag Cloud