Plan guide validation with fn_validate_plan_guide gives false positives

When validating a plan guide for a piece of SQL in a stored procedure that references a temporary table named "#test" the function fn_validate_plan_guide returns the error: Invalid object name '#test'.

But the the plan guide still pushes the query hint into the SQL and the desired execution is achieved.

Does this highlight a problem with the fn_validate_plan_guide function?

The script below recreates the problem.

``
--Enable the actual execution plan before running the query so the plans can be compared

USE [msdb]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[test]
AS
BEGIN
CREATE TABLE #test
(
ID INT
)

INSERT INTO #test
SELECT ROW_NUMBER() OVER(ORDER BY job_id)
FROM dbo.sysjobs

SELECT *
FROM #test t
JOIN #test t2 ON t.ID = t2.ID

DROP TABLE #Test
END
GO

--Execution before the plan guide is created will have a hash join in the second batch
EXEC msdb.dbo.test
GO

--Create the plan guide
EXEC sp_create_plan_guide 'test',
' SELECT *
FROM #test t
JOIN #test t2 ON t.ID = t2.ID',
'OBJECT', 'dbo.test', NULL, 'OPTION (MERGE JOIN)'
GO

--Validate the plan guide. This returns the error "Invalid object name '#test'."
SELECT
plan_guide_id, msgnum, severity, state, message,
name, create_date, is_disabled, query_text, scope_type_desc, scope_batch, parameters, hints
FROM sys.plan_guides
CROSS APPLY fn_validate_plan_guide(plan_guide_id);
GO

--Execution after the plan guide is created will have a merge join in the second batch
EXEC msdb.dbo.test
GO

EXEC sp_control_plan_guide 'DISABLE', 'test'
GO

--Execution after the plan guide is disabled will go back to having a hash join in the second batch
EXEC msdb.dbo.test
GO

EXEC sp_control_plan_guide 'ENABLE', 'test'
GO

--Execution after the plan guide is re-enabled will go back to having a merge join in the second batch
EXEC msdb.dbo.test
GO

--Clean up
EXEC sp_control_plan_guide 'DROP', 'test'
GO

DROP PROCEDURE test
GO
`

Yes, this is a limitation of sys.fn_validate_plan_guide that can cause a false negative (not a false positive).

The server attempts to compile just the statement in the plan guide, not the whole batch (stored procedure in this case). The request fails because the temporary table definition is not part of the compilation.

I could not reproduce the same issue with a table variable on:

Microsoft SQL Server 2012 (SP3-CU2) (KB3137746) - 11.0.6523.0 (X64)
Mar 2 2016 21:29:16
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3 (Build 10586: )

The plan validation and guide application worked correctly.

Workarounds

Requesting an estimated plan for a call to the procedure succeeds, with the resulting batch plan showing the guided plan, with the properties of the statement root node showing the correct PlanGuideDB and PlanGuideName properties in SSMS:

An alternative is to create the temporary table (copied from the procedure definition) on the session that calls sys.fn_validate_plan_guide.

CREATE TABLE #test 
(
    ID INT
);

-- Succeeds
SELECT 
    PG.plan_guide_id,
    PG.name,
    PG.scope_type_desc,
    PG.hints,
    FVPG.msgnum,
    FVPG.severity,
    FVPG.[state],
    FVPG.[message]
FROM sys.plan_guides AS PG
CROSS APPLY sys.fn_validate_plan_guide(PG.plan_guide_id) AS FVPG;

DROP TABLE #test;

Either of these can be used to validate the negative result from sys.fn_validate_plan_guide when the error refers to a missing #object.

StackExchange Database Administrators Q#120316, answer score: 2