Eu tenho uma consulta do seguinte formulário
exec sp_executesql N' UPDATE table SET column1 = 1, modify_date=N''2020-02-12 04:55:59.000'' WHERE (column5=@P1 AND column6=@P2 )',N'@P1 nvarchar(36),@P2 int',N'458986156148',87
Eu não posso mudar nada sobre esta consulta em si. Ele vem de um aplicativo.
Toda vez que ele é executado, os valores de atualização mudam, fazendo com que um novo plano de consulta seja gerado em vez de o plano anterior ser reutilizado.
A parametrização forçada está ativada, mas não parece ter efeito sobre essa consulta, provavelmente pela forma como ela é executada.
Ao executar a própria consulta vejo um total de 4 leituras para a atualização. Nesse caso, a execução em si não causou uma atualização, pois não há linhas retornadas para a cláusula where.
Usando o criador de perfil, vejo que o tempo entre a entrada e o início da consulta é quase todo o tempo da consulta (1 segundo). A execução real é quase imediata.
O perfilador mostra 455.000 leituras!! que não são mostrados na própria execução da consulta.
Então agora eu estou me perguntando
- Posso forçar a consulta a usar o mesmo plano aqui? Um guia de plano parece utilizável apenas para a mesma consulta ou para uma consulta afetada pela parametrização forçada.
- Podemos aumentar a velocidade de compilação desta consulta? De onde vêm as 455.000 leituras? Existem muitas estatísticas nessa tabela (+- 100), mas 455.000 leituras parecem um tanto excessivas.
Isso está no SQL Server 2019, ainda sem atualizações cumulativas. Eu escaneei o changelog CU para qualquer coisa que possa ter a ver com isso.
edit/ Investigações posteriores mostraram que existem muitos bloqueios em outras tabelas durante o tempo de compilação. Tenho 300 tabelas com chave estrangeira referenciando a chave primária na minha tabela onde quero inserir.
Todos os relacionamentos de chave estrangeira são confiáveis. Haveria alguma maneira de evitar essas verificações durante a fase de compilação?
edit 2/ As dependências não são restrições de chave estrangeira, mas visualizações na tabela. Todas as visualizações que usam essa tabela têm o bloqueio SCH-S durante a execução, o que é esperado. Não está claro se isso também está causando as leituras ...
edit 3/ Aparentemente as 455.000 leituras são feitas varrendo a tabela do sistema sys.sysmultiobjrefs mais de um milhão de vezes. Isso não parece um comportamento adequado.
Isso mesmo, a parametrização forçada não funciona aqui porque a consulta "já foi parametrizada no aplicativo do lado do cliente" ( source ). Em outras palavras, o problema é que existem dois parâmetros reais, portanto, os parâmetros date e int incorporados não podem ser forçados.
Vamos ao que está acontecendo com as leituras/CPU...
Estatísticas criadas/atualizadas automaticamente
No que diz respeito às leituras mostradas no criador de perfil, elas incluem atualizações e criação de estatísticas. Isso explicaria o problema se acontecesse apenas ocasionalmente. Como você mencionou esses longos tempos de compilação e leituras altas ocorrem em cada execução, não parece que esse seja o culpado (a menos que esse aplicativo esteja fazendo algo realmente incomum, como excluir agressivamente estatísticas automáticas).
Leituras de armazenamento de versões
Existem outros tipos de leituras que não são relatadas em
SET STATISTICS IO ON, sobre as quais falei no meu blog . Isso inclui leituras de armazenamento de versão, portanto, se você tiverSNAPSHOTouREAD COMMITTED SNAPSHOThabilitado nesse banco de dados, isso pode explicar algumas das discrepâncias de CPU / leitura. Especialmente se você tiver transações de longa duração.Solução de problemas adicionais
Pode ser útil se você puder compartilhar um "Plano de Execução Real" para a consulta em questão ao reproduzir o problema. Pode haver pistas além das informações que você já forneceu.
Forçar Plano
Até onde eu sei, não há nenhuma maneira de impedir que um plano seja compilado toda vez que uma dessas consultas for recebida (como você observou, os guias de plano não funcionarão e o Repositório de consultas também não será forçado).
Cargas de trabalho ad hoc
Como uma observação lateral, como você tem esses parâmetros incorporados, sua carga de trabalho provavelmente pode se beneficiar bastante da
optimize for ad hoc workloadsopção de configuração no nível do servidor . Isso evitará que o cache do plano fique inchado, o que também pode estar contribuindo para problemas de CPU (já que o SQL Server precisa remover itens do cache do plano ao longo do tempo devido a todos os "novos" planos que chegam para cada consulta).Temos uma solução alternativa, resumindo, a fase de vinculação durante a compilação da consulta foi a mais demorada e desapareceu quando a propriedade SCHEMABINDING nas visualizações da tabela foi removida.
As informações do repositório de consultas indicaram que o tempo de compilação da consulta foi ocupado principalmente pela associação, em vez de otimização ou análise.
Embora ainda não esteja claro se isso é um bug ou um comportamento esperado, as leituras são feitas na tabela sys.sysmultiobjrefs. Esta tabela de acordo com a documentação "Existe em todos os bancos de dados. Contém uma linha para cada referência geral N-para-N." Ao examinar os valores, ele também contém as visualizações (não indexadas) na tabela. A tabela na qual queremos inserir registros possui cerca de 3.000 visualizações.
Ao excluir as visualizações, o tempo de compilação diminuiu linearmente. É claro que não podemos excluir as visualizações e impedir que o estágio de vinculação verifique as visualizações, removi a propriedade SCHEMABINDING das visualizações nessa tabela. A compilação e a inserção da consulta agora são reduzidas a milissegundos em vez de 1-2 segundos.
Claro que precisamos levar em conta as implicações durante futuras atualizações do aplicativo.