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?

Answer 1

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.