How to use a SQL Server Plan Guide to Tune Queries

By:   |   Updated: 2018-06-02   |   Comments (5)   |   Related: 1 | 2 | > Query Plans


Problem

My company uses a vendor purchased application to process orders. Lately, one of the vendor's application queries has been running very slowly and is starting to cause problems. My testing has determined that the SQL statement can benefit from a query hint. Is there a way I can apply this query hint though I don't have access to the vendor's application code?

Solution

Yes, there is. SQL Server 2005 introduced plan guides which allow you to add hints to specific queries without the invasiveness of directly changing any queries themselves. In situations where you cannot modify source code (either because it's not available or because it's contractually prohibited), this can be a very useful tool. However, it should be noted that this is an advanced feature and should be used with care.

There are 3 types of plan guides that you can create. The following excerpt from the SQL Server Books Online summarizes them:

  • OBJECT plan guides match queries that execute in the context of Transact-SQL stored procedures, scalar functions, multistatement table-valued functions, and DML triggers.
  • SQL plan guides match queries that execute in the context of stand-alone Transact-SQL statements and batches that are not part of a database object. SQL-based plan guides can also be used to match queries that parameterize to a specified form.
  • TEMPLATE plan guides match stand-alone queries that parameterize to a specified form. These plan guides are used to override the current PARAMETERIZATION database SET option of a database for a class of queries.

Defining a plan guide is done through system stored procedure sp_create_plan_guide:

sp_create_plan_guide parameters

EXEC sp_create_plan_guide @name, @stmt, @type, @module_or_batch, @params, @hints

Here is an explanation of the parameters:

  • @name - name of the plan guide
  • @stmt - a T-SQL statement or batch
  • @type - indicates the type of guide (OBJECT, SQL, or TEMPLATE)
  • @module_or_batch - the name of a module (i.e. a stored procedure)
  • @params - for SQL and TEMPLATE guides, a string of all parameters for a T-SQL batch to be matched by this plan guide
  • @hints - OPTION clause hint to attach to a query as defined in the @stmt parameter

Let's move on to an example that mimics a vendor application executing a prepared query embedded within their application code. The following parameterized dynamic SQL statements retrieve SalesOrderDetail information for a ProductID:

USE AdventureWorks
GO
EXEC sp_executesql
@stmt = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID',
@params = N'@ProductID int', @ProductID = 870
GO

EXEC sp_executesql 
@stmt = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID',
@params = N'@ProductID int', @ProductID = 897
GO

Initially running this query for ProductID = 870 produces a table scan. Since this ProductID appears in the table in 4,688 rows out of 121,317 total rows, the optimizer has determined that this is the best plan. The plan will be cached and prepared for use for all subsequent executions of this query.

results

However, the 2nd execution for ProductID = 897 also used a table scan though only 2 rows exist in the Sales.SalesOrderDetail for this ProductID. An index seek would be a better option in this case but we're now tied to the execution plan that was generated the first time the prepared query was executed. In cases where this would become a problematic performance issue, a DBA could add a RECOMPILE option to the query which would force the optimizer to recompile the statement and optimize it every time it is executed.

Creating a SQL plan guide gets around the issue of not being able to or allowed to add hints to these database queries. The following plan guide is created and named GETSALESPRODUCTS_RECOMPILE_Fix and it applies a RECOMPILE to the SELECT statement that's causing us grief.

USE AdventureWorks
GO
EXEC sp_create_plan_guide
@name = N'GETSALESPRODUCTS_RECOMPILE_Fix',
@stmt = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@ProductID int',
@hints = N'OPTION (RECOMPILE)'
GO

Note: the parameters of sp_create_plan_guide expects Unicode strings. As a result, all parameter entries must always be preceded with the "N" prefix which stands for National Language in the SQL-92 standard; it must be upper case.

Re-running the queries, the plan guide in place forces the SQL statement to recompile generating the most optimal query plan for a passed in ProductID. This comes without having to change a stitch of code. Examining the execution plans, we now see that a table scan is used for ProductID = 870 but an index seek is used on ProductID = 897. A profiler trace also shows that the statements are re-compiled prior to execution:

query cost
jester

As you can see, this is really powerful way to tune queries that you may not have access to. Prior to SQL Server 2005, queries similar to these that were buried deep in application code that you don't have access to were practically impossible to tune.

You can view a listing of all plan guides stored in the database using the following query:

SELECT * FROM sys.plan_guides
GO
name

You may have been thinking: how do we get rid of a plan guide if it's determined that we no longer need it or want it? This can be controlled with another system stored procedure named sp_control_plan_guide. Using this procedure, you can DROP the plan guide, DISABLE it, or re-ENABLE it if it had been previously disabled.

sp_control_plan_guide parameters

EXEC sp_control_plan_guide @operation, @name

Explanation of its parameters:

  • @operation - a control option; one of DROP, DROP ALL, DISABLE, DISABLE ALL, ENABLE, ENABLE ALL
  • @name - name of the plan guide to control

We can then issue the following statement to DROP the plan guide, if necessary:

EXEC sp_control_plan_guide N'DROP', N'GETSALESPRODUCTS_RECOMPILE_Fix'
GO

Since plan guides affect the optimizer's choice of execution plan, they should be carefully leveraged (and documented!) in very specific situations where access to database queries is limited.

Next Steps
  • If you're in a situation where a query is performing poorly and you can't modify the offending query, consider if a plan guide can help
  • Read more about the various plan guide types in SQL Server Books Online
  • Read the following Technet whitepaper Forcing Query Plans


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Armando Prato Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2018-06-02

Comments For This Article




Friday, January 3, 2020 - 8:31:17 AM - Pablo Back To Top (83616)

Hi, how can use hint nolock in plan_guide?, because i have some queries that run from SSAS (tabular) and with these tool can't put with nolock into query...and some queries generate locks in database...

Regards


Saturday, April 2, 2016 - 4:43:46 PM - Armando Prato Back To Top (41125)

Hitendra,  there's a lot to performance tuning and your question is too vague to offer specific advice.

If this is a single table of 300k rows and queries get progressively gets slower as the table grows,  you might
want to see if any indexes are defined at all on this table and what the WHERE clause of queries on this table look
like.


Saturday, April 2, 2016 - 1:09:00 AM - Hitendra Deria Back To Top (41122)

My Company develop one sales application. Lately , in this application i have table which have 300K data . When i use this table in query its take more time for execution. So , what step i do for query performance. Table Partition is best way for tuning  ?   

 


Monday, September 24, 2012 - 12:27:29 AM - isql2012 Back To Top (19641)

Good article!

Quest SQL Optimizer for SQL Server 8.0 provides a new module Plan Control which can automatically generate Plan Guide alternatives for your query.

For more infromation, you can refer to:

http://toadworld.com/Blogs/tabid/67/EntryId/937/Plan-Guide-in-Quest%c2%ae-SQL-Optimizer-for-SQL-Server-8-0-Part-I.aspx

http://toadworld.com/Blogs/tabid/67/EntryId/945/Plan-Guide-in-Quest%c2%ae-SQL-Optimizer-for-SQL-Server-8-0-Part-II.aspx

 


Tuesday, July 3, 2012 - 5:33:30 PM - mohan Back To Top (18331)

 Nice article..   What happens if for the same query  we have Plan_guide and if u use   some other options in a procedure  . which will take effect ??

 

If u change  select column list in a statement  then we have to change  the plan_guide  too ??

 

 

 Thnaks















get free sql tips
agree to terms