Por que minha consulta está repentinamente mais lenta do que ontem?

Caro [seu nome aqui]!

Oh não, lamento ouvir isso! Vamos começar com algumas noções básicas para você se arrumar em um instante.

A coisa que você está encontrando é chamada de Parameter Sniffing

É uma saída para o problema esquisito. O nome rola direto da língua. Como a palavra alemã para esquilo.

E geralmente é seu amigo.

Quando uma consulta atinge seu servidor, um plano deve ser compilado. Para economizar tempo e recursos posteriormente, um plano de execução é armazenado em cache com base nas linhas estimadas que o parâmetro fará com que seu código seja processado e retornado.

A maneira mais fácil de imaginar isso dando errado é imaginar um procedimento armazenado que precisa contar coisas de duas populações desiguais.

Por exemplo:

• Pessoas vestindo camisas CrossFit que não estão feridas: Zero

• Pessoas vestindo camisas CrossFit que estremecem quando estremecem: todas

Obviamente, uma execução desse código teria que fazer muito mais trabalho do que outra, e os planos de consulta que você desejaria fazer com quantidades totalmente diferentes de trabalho seriam totalmente diferentes.

Contra o que estou lutando?

Este é um problema genuinamente difícil de encontrar, testar e corrigir.

• É difícil encontrar porque não acontece de forma consistente
• É difícil testar porque você precisa saber quais parâmetros causam planos diferentes
• É difícil de corrigir porque às vezes requer ajuste de consulta e índice
• É difícil de corrigir porque talvez você não consiga alterar consultas ou índices
• É difícil corrigir porque mesmo que você altere consultas ou índices, ele ainda pode voltar

Correções rápidas

Às vezes, tudo que você precisa é de um pouco de clareza. Ou melhor, o cache do seu plano sim.

Se for um procedimento armazenado

Experimente correr EXEC sys.sp_recompile @objname = N'schema.procname'. Isso fará com que o procedimento recompile um novo plano na próxima vez que for executado.

O que isso não vai corrigir:

• Processos atualmente em execução.

O que isso não garante:

• O próximo processo executado após a recompilação usará um parâmetro que fornece um bom plano.

Você também pode apontar sp_recompilepara uma tabela ou exibição, mas esteja avisado de que todo o código que toca essa tabela ou exibição será recompilado. Isso pode tornar o problema muito mais difícil.

Se for uma consulta parametrizada

Seu trabalho é um pouco mais difícil. Você precisará rastrear o SQL Handle. Você não quer liberar todo o cache do plano - assim como usar sp_recompileem uma tabela ou exibição, você pode acionar (ha ha ha) um monte de consequências não intencionais.

A maneira mais fácil de descobrir esse comando é executar sp_BlitzWho *! Há uma coluna chamada "fix parameter sniffing" que possui um comando para remover um único plano do cache. Isso tem as mesmas desvantagens da recompilação, no entanto.

O que isso não vai corrigir:

• Processos atualmente em execução.

O que isso não garante:

• O próximo processo executado após a recompilação usará um parâmetro que fornece um bom plano.

Ainda preciso de ajuda!

Vamos precisar das seguintes coisas:

• O bom plano de consulta, se possível
• O plano de consulta ruim
• Os parmetros usados
• A consulta em questão
• Definições de tabela e índice

Obtendo os planos de consulta e consulta

Se a consulta estiver em execução, você poderá usar sp_BlitzWho * ou sp_WhoIsActive para capturar as consultas em execução no momento.

EXEC sp_BlitzWho;

EXEC sp_WhoIsActive @get_plans = 1;

Se a consulta não estiver em execução no momento, você poderá verificá-la no cache do plano, usando sp_BlitzCache *.

Se você estiver no SQL Server 2016+ e tiver o Query Store ativado, poderá usar sp_BlitzQueryStore *.

EXEC dbo.sp_BlitzCache @StoredProcName = 'Your Mom';

EXEC dbo.sp_BlitzQueryStore @StoredProcName = 'Your Mom';

Isso o ajudará a rastrear as versões em cache do seu procedimento armazenado. Se for apenas código parametrizado, sua busca é um pouco mais difícil. Isso pode ajudar, no entanto:

EXEC dbo.sp_BlitzCache @QueryFilter = 'statement';

Você deve ver uma saída bastante semelhante de qualquer um deles. Mais uma vez, o plano de consulta convidando a coluna cool blue clicky é seu amigo.

A maneira mais fácil de compartilhar planos é usando Paste The Plan *, ou despejando o XML no pastebin. Para conseguir isso, clique em qualquer uma daquelas colunas azuis convidativas. Seu plano de consulta deve aparecer em uma nova guia do SSMS.

Se você está preocupado em compartilhar o código e a consulta de sua empresa, você pode usar a ferramenta gratuita Plan Explorer do Sentry One para anonimizar seu plano. Lembre-se de que isso dificulta a obtenção de ajuda - o código anônimo é muito mais difícil de ler e descobrir.

Todas essas ferramentas que falamos devem retornar o Query Text. Você não precisa fazer mais nada aqui.

Obter o(s) parâmetro(s) é um pouco mais difícil. Se você estiver usando o Plan Explorer , há uma guia na parte inferior que lista todos eles para você.

Se você estiver usando sp_BlitzCache *, há uma coluna clicável que fornece a instrução de execução para procedimentos armazenados.

Obtendo as definições de tabela e índice

Você pode facilmente clicar com o botão direito do mouse no SSMS para criar scripts.

Se você deseja obter tudo de uma vez, sp_BlitzIndex * pode ajudar se você apontar diretamente para uma mesa.

EXEC dbo.sp_BlitzIndex @DatabaseName = 'StackOverflow2010',
                       @SchemaName = 'dbo',
                       @TableName = 'Users';

Isso fornecerá a definição da tabela (embora não como uma instrução de criação) e criará instruções para todos os seus índices.

Coletar e adicionar essas informações à sua pergunta deve fornecer às pessoas informações suficientes para ajudar ou indicar a direção certa.

Eu mesma quero fazer!

Bem, legal. Estou feliz por você. Seu louco.

Há muitas maneiras pelas quais as pessoas pensam que "consertam" o sniffing de parâmetros:

• Recompilar dicas
• Otimizar para desconhecido
• Otimizar para um valor

Mas estes realmente apenas desabilitam o sniffing de parâmetros de maneiras diferentes. Isso não quer dizer que eles não podem resolver o problema, eles simplesmente não chegam à causa raiz.

Isso porque chegar à causa raiz geralmente é meio difícil. Você tem que procurar aqueles "problemas de qualidade do plano" irritantes.

Começando com os planos rápido versus lento, procure diferenças como:

• Índices usados
• Junte-se ao pedido
• Serial x Paralelo

Procure também por diferentes operadores que tornam seu código sensível à detecção de parâmetros:

• Pesquisas
• Classificações
• Tipo de associação
• Concessões de memória (e, por extensão, spills)
• Carretéis

Não fique muito envolvido em busca vs varredura, fragmentação de índice ou qualquer uma das coisas de culto de carga sobre as quais as pessoas se debatem.

Normalmente, há um problema de indexação bastante básico. Às vezes, o código precisa de uma pequena reescrita.

Se você quiser saber mais sobre sniffing de parâmetros:

• Lento no aplicativo, rápido no SSMS? - Erland Sommarskog

• Solução de problemas Sniffing de parâmetros 1 , 2 , 3 - Tara Kizer

• Por que você está ajustando os procedimentos armazenados de forma errada (o problema com variáveis ​​locais) - Kendra Little

• Como usar parâmetros como um profissional e aumentar o desempenho - Guy Glantser

• Sniffing, Incorporação e Opções de Recompilação de Parâmetros - Paul White

Se você está lendo isso e acha que perdi um link ou ferramenta útil, deixe um comentário. Farei o possível para manter isso atualizado.

A detecção de parâmetros não é a única causa possível para o desempenho variável de uma consulta. Qualquer um dos seguintes motivos comuns pode apresentar os mesmos sintomas:

1. Distribuição/volume de dados alterado, cruzando um ponto de decisão da árvore de busca do otimizador
2. Índices/arquivos ficaram fragmentados
3. As estatísticas foram atualizadas / adicionadas / descartadas ou se tornaram obsoletas e enganosas devido a alterações de dados
4. A utilização de memória do Windows foi alterada
5. Os logs de transações estão cheios e não truncados, causando expansão repetida de arquivos físicos
6. Esquema alterado - índice / exibição indexada / coluna / restrição adicionada, modificada ou descartada, tipo de dados alterado etc.
7. As configurações do sinalizador de rastreamento foram alteradas
8. A atualização do Windows foi aplicada
9. A configuração do banco de dados ou do servidor foi alterada
10. Nível de CU do servidor alterado
11. As configurações da sessão do aplicativo cliente foram alteradas

Os itens 6 a 11 desta lista só podem acontecer depois que alguma ação explícita for tomada. Acho que você pretendia excluí-los, mas muitas vezes aquele que está enfrentando o desafio não sabe que outra pessoa fez alterações, e vale a pena verificar antes de embarcar no caminho de limpar as entradas do cache do plano.

Apenas para complementar as respostas existentes caso elas não tenham ajudado, quando "de repente" suas consultas se comportarem de forma diferente no dia seguinte, verifique:

• O esquema das tabelas usadas mudou desde a última vez? No caso do SSMS, você pode clicar com o botão direito do mouse no servidor no Pesquisador de Objetos e escolher Reports → Standard Reports → Schema Changes History.
• A contagem de itens aumentou drasticamente? Talvez sua consulta seja muito mais lenta quando há muitos dados nas tabelas usadas.
• Alguém está utilizando o banco de dados ao mesmo tempo que você? Talvez escolha intervalos de tempo em que você não interfira no trabalho um do outro.
• Como estão as estatísticas do sistema? Talvez o servidor esteja esquentando e estrangulando a CPU ou os discos rígidos estão ficando sem espaço ou trocando. Talvez haja outro problema de hardware, como um incêndio ou uma inundação na sala do servidor.

Outra possibilidade é que sua equipe de infraestrutura esteja usando ferramentas como vMotion no VMware e a VM que dá suporte à sua instância SQL esteja sendo movida perfeitamente de host para host sem que o DBA saiba disso.

Este é um problema real quando sua infraestrutura é terceirizada... Estou tendo um verdadeiro pesadelo com isso.