Ao validar um guia de plano para uma parte do SQL em um procedimento armazenado que faz referência a uma tabela temporária chamada "#test", a função fn_validate_plan_guide retorna o erro: Nome de objeto inválido '#teste'.
Mas o guia de plano ainda envia a dica de consulta para o SQL e a execução desejada é alcançada.
Isso destaca um problema com a função fn_validate_plan_guide?
O script abaixo recria o problema.
--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
Esta função está dando erros que são falsos positivos ou são apenas avisos de que o guia do plano pode falhar ou é outra coisa que não pensei?
Criei um item Connect aqui com o texto acima, mas ainda não recebi uma resposta.
Sim, esta é uma limitação de sys.fn_validate_plan_guide que pode causar um falso negativo (não um falso positivo).
O servidor tenta compilar apenas a instrução no guia de plano, não o lote inteiro (procedimento armazenado neste caso). A solicitação falha porque a definição da tabela temporária não faz parte da compilação.
Não consegui reproduzir o mesmo problema com uma variável de tabela em:
A validação do plano e a aplicação do guia funcionaram corretamente.
Soluções alternativas
A solicitação de um plano estimado para uma chamada para o procedimento é bem-sucedida, com o plano de lote resultante mostrando o plano guiado, com as propriedades do nó raiz da instrução mostrando as propriedades PlanGuideDB e PlanGuideName corretas no SSMS:
Uma alternativa é criar a tabela temporária (copiada da definição do procedimento) na sessão que chama sys.fn_validate_plan_guide .
Qualquer um deles pode ser usado para validar o resultado negativo de sys.fn_validate_plan_guide quando o erro se referir a um #object ausente.