SQL Server - Creating a Plan Guide without using an alias

Question

I'm trying to create a plan guide in SQL Server 2012 SP3 Enterprise Edition for a specific query run by an application, which means I cannot alter the query in any way.

The query looks like this:

(@P1 nvarchar(5),@P2 bigint)
DELETE FROM INVENTSUMDELTA WHERE ((DATAAREAID=@P1) AND (TTSID=@P2))

To create the plan guide, I used the following query:

EXEC sp_create_plan_guide   
@name = N'INVENTSUMDELTAINDEX', 
@stmt = N'DELETE FROM INVENTSUMDELTA WHERE ((DATAAREAID=@P1) AND (TTSID=@P2))',    
@type = N'SQL',  
@module_or_batch = NULL,  
@params = N'@P1 nvarchar(5),@P2 bigint',  
@hints = N'OPTION (TABLE HINT ( INVENTSUMDELTA, INDEX( I_2397TTSDIMIDX )))';

However, I received an error:

Msg 8724, Level 16, State 1, Line 1 Cannot execute query. Table-valued or OPENROWSET function 'INVENTSUMDELTA' cannot be specified in the TABLE HINT clause.

I checked the documentation and found the following:

TABLE HINT (exposed_object_name [ , [ [, ]...n ] ] ) Applies the specified table hint to the table or view that corresponds to exposed_object_name. [...]

exposed_object_name can be one of the following references:

When an alias is used for the table or view in the FROM clause of the query, exposed_object_name is the alias.

When an alias is not used, exposed_object_name is the exact match of the table or view referenced in the FROM clause. For example, if the table or view is referenced using a two-part name, exposed_object_name is the same two-part name.

From this I gather that it should be possible to create a plan guide for a query that isn't using an alias. However, I cannot get it to work.

So my question is: how do I create a plan guide without using aliasing and without altering the original query?

There is a table (in the dbo schema), but no UDF. – Monzie Jun 07 '17 at 11:41 — Monzie, Jun 07 '17 at 11:41

Jeroen Mostert · Answer 1 · 2017-06-08T18:34:18.310

The error message is misleading. It has nothing to do with the nature of the object (you will get the same error with a nonexistent object). The problem is that it will not work for DELETE statements -- any TABLE HINT referring to a table that is the target of a DELETE will produce this error. This is not restricted to plan guides either -- a plain DELETE with an option will fail as well:

DELETE FROM does_not_exist 
OPTION (TABLE HINT (does_not_exist, INDEX (does_not_exist)))

Cannot execute query. Table-valued or OPENROWSET function 'does_not_exist' cannot be specified in the TABLE HINT clause.

This appears to be a bug, because if the statement is augmented with a WITH (ROWLOCK) hint at both the table and query level, the error disappears:

DELETE FROM does_not_exist WITH (ROWLOCK)
OPTION (TABLE HINT (does_not_exist, ROWLOCK, INDEX (does_not_exist)))

Invalid object name 'does_not_exist'.

The same issue is covered in this question, and the solution is to rewrite the query in a form that does allow applying the hint.

In this case, we can't rewrite the query directly, but we can still get the desired effect by using a fixed query plan guide:

-- Alternate query using hint.
DECLARE @sql NVARCHAR(MAX) = N'WITH T AS (
    SELECT * 
    FROM INVENTSUMDELTA WITH (INDEX (I_2397TTSDIMIDX))
    WHERE ((DATAAREAID=@P1) AND (TTSID=@P2))
)
DELETE T';
DECLARE @params NVARCHAR(MAX) = N'@P1 nvarchar(5),@P2 bigint'

-- Put the execution plan in the cache.
EXEC sp_executesql @sql, @params = @params, @P1=NULL, @P2=NULL;

-- Retrieve it.
DECLARE @query_plan NVARCHAR(MAX);
SELECT @query_plan = query_plan  
FROM sys.dm_exec_query_stats AS qs   
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS st  
CROSS APPLY sys.dm_exec_text_query_plan(qs.[plan_handle], DEFAULT, DEFAULT) AS qp  
WHERE st.[text] LIKE '(' + @params + ')%' + @sql;

-- Create a plan guide associating the query with the new execution plan.
EXEC sp_create_plan_guide   
    @name = N'INVENTSUMDELTAINDEX', 
    @stmt = N'DELETE FROM INVENTSUMDELTA WHERE ((DATAAREAID=@P1) AND (TTSID=@P2))',    
    @type = N'SQL',  
    @module_or_batch = NULL,  
    @params = @params,
    @hints = @query_plan;

As always, plan guides should be the last resort if nothing else helps (updating statistics, creating new indexes, dropping suboptimal indexes). This answer assumes you've reviewed all the other options and the plan guide is necessary.

Thanks for your answer! I figured it had something to do with DELETEs missing a proper FROM-clause, but didn't think of this workaround. I cannot try the solution until next week, so will wait with marking it as accepted until then. — Monzie, Jun 08 '17 at 13:58
@Monzie: I verified that it does work (by creating a second index and comparing execution plans between statements with the plan guide turned on and off), so in case it doesn't work in your situation, be sure to add details. In particular, matching the query text and parameterization can be tricky with plan guides. — Jeroen Mostert, Jun 08 '17 at 14:01
@Monzie: one more thing -- I'm not sure if the execution plan reflects row estimates by the parameters passed in. If it does, it may be necessary to pass in representative parameter values on execution (in a transaction that's rollbacked to prevent actually deleting rows!) or use the `OPTIMIZE FOR` hint to supply representative values. This is a negative consequence of using a fixed query plan, as opposed to a guide with just hints. Note that `OPTIMIZE FOR` on its own might enable use of the index, making this whole fixed query unnecessary. — Jeroen Mostert, Jun 08 '17 at 18:33
Great! This works. This query is from Dynamics AX which requires lots of index hints. Thanks for excellent workaround. After all, whole point of plan guide is to instruct SQL how to execute 3rd party queries, when there is no way to modify them. — Janis Veinbergs, Jan 31 '19 at 10:47

SQL Server - Creating a Plan Guide without using an alias

1 Answers1