How to use a SQL Server Plan Guide to Tune Queries

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?

Yes, there is. SQL Server 2005 introduced SQL 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

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:

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.

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.

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:

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:

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

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:

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.