Início / dba / Perguntas / 343432
Accepted
jerry xu
Asked: 2024-11-06 05:38:16 +0800 CST

por que o sql server não está reutilizando o plano na minha consulta ad hoc?

  • 772

Parece que meu entendimento do plano de consulta não está correto. Se eu executar esta consulta pela primeira vez e verificar o cache do plano de consulta. Vejo um plano em cache.

declare @codes varchar(max)='BHVD7,BHVDE,BHVDF'
SELECT OFST010020 AS [OFST010020], OFST730010 AS [OFST730010], Identifier AS [Identifier]   FROM STATIC_FUNDSHARECLASSUNIT_NEXT as f WITH(NOLOCK)  Inner join STATIC_FUNDSHARECLASSUNITEXTENSION_NEXT as fex WITH(NOLOCK) on fex.Identifier2 = f.Identifier    WHERE OFST900174 in (select value from string_split(@codes,','))

Se eu apenas alterar o parâmetro do código e executar a consulta novamente na mesma janela do SSMS, por exemplo

declare @codes varchar(max)='BHVD7,BHVDF'
SELECT OFST010020 AS [OFST010020], OFST730010 AS [OFST730010], Identifier AS [Identifier]   FROM STATIC_FUNDSHARECLASSUNIT_NEXT as f WITH(NOLOCK)  Inner join STATIC_FUNDSHARECLASSUNITEXTENSION_NEXT as fex WITH(NOLOCK) on fex.Identifier2 = f.Identifier    WHERE OFST900174 in (select value from string_split(@codes,','))

Achei que o plano de consulta teria 1 linha e o useCount seria 2. Mas no cache do plano de consulta, na verdade vi dois planos. O texto sql de cada plano é, na verdade, o texto completo (começando com declare @codes). Estou fazendo algo errado ou a consulta ad hoc não reutiliza realmente o plano, mesmo que o parâmetro esteja envolvido?

sql-server

2 respostas

  1. Best Answer

    Estou fazendo algo errado ou a consulta ad hoc não reutiliza realmente o plano, mesmo que haja parâmetros envolvidos?

    Não há parâmetro envolvido. Você está usando uma variável local , não um parâmetro . Não há muita diferença na linguagem casual, mas para o SQL Server são coisas completamente separadas.

    Em geral, o SQL Server não reutiliza planos para lotes ad hoc , a menos que todo o texto do lote (não instruções individuais no lote) corresponda exatamente.

    Há algumas sutilezas quando as instruções dentro do lote podem ser parametrizadas no lado do servidor por meio de parametrização simples ou forçada , mas esse não parece ser o caso aqui.

    Independentemente disso, mesmo com a parametrização do lado do servidor, apenas o formulário parametrizado será armazenado em cache e reutilizado para diferentes valores de parâmetros. O texto ad hoc original é quase sempre armazenado em cache separadamente e reutilizado apenas em uma correspondência exata.

    Para incentivar a reutilização do plano, você precisa usar um procedimento armazenado, uma instrução preparada ou sp_executesql. Todos eles permitem que você parametrize explicitamente suas instruções para reutilização do plano.

    Para obter mais informações sobre parametrização e reutilização de planos, consulte o White Paper da Microsoft Cache de planos e recompilação no SQL Server 2012 .

    • 6

  2. Tem certeza de que as duas consultas são iguais (até mesmo a localização do espaço)?

    Fiz um pequeno teste usando o banco de dados StackOverflow.

    Observação: não é recomendado limpar o cache do procedimento armazenado em produção:

    DBCC FREEPROCCACHE WITH NO_INFOMSGS;

    Tenho a seguinte dúvida

    DECLARE @PostTypeID AS int = 3;
SELECT * FROM dbo.Posts WITH (NOLOCK)
WHERE PostTypeId = @PostTypeID

    Na primeira execução, um novo plano é criado, o usecountsvalor é 1.

    Na segunda execução, o plano é reutilizado, o usecountsvalor é 2.

    Entretanto, quando executei a consulta pela terceira vez com um espaço na frente , assim:

     DECLARE @PostTypeID AS int = 3;
SELECT * FROM dbo.Posts WITH (NOLOCK)
WHERE PostTypeId = @PostTypeID

    Recebi um plano de execução diferente.

    planos em cache

    • 1

relate perguntas