| 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 : |  |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
