Plan guide validation with fn_validate_plan_guide gives false positives

3 votes

1 answer

402 views

sql-server sql-server-2012 sql-server-2014 plan-guides

                          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

Is this function giving errors that are false positives or are these just warnings that the plan guide may fail or is it something else I haven't thought of?

I have created a Connect item here  with the text above but am yet to have a response.
                        

Asked by James Anderson (5794 rep)

Nov 6, 2015, 02:24 PM
Last activity: Dec 8, 2016, 10:11 PM

Plan guide validation with fn_validate_plan_guide gives false positives

Related Questions