Como responder por que de repente precisamos de índices ou a consulta precisa ser alterada

Como responder à pergunta alterada por dev?

Essa é uma pergunta muito comum não apenas com DEV, mas também se aplica a todas as equipes de TI e negócios.

O que mudou ? ==> pode ser respondido por fatos e números.

Os fatos referem-se, por exemplo,

• aumentar a quantidade de usuários que estão acessando o banco de dados ?
• Alguma alteração no parâmetro de configuração do servidor?
• Manutenção do banco de dados - estatísticas de atualização, reorganização/reconstrução de índices não sendo executadas? Devido a isso, os planos estão sendo gerados incorretamente!
• A quantidade de dados aumentou?
• Foram feitas alterações no lado da rede, o sistema operacional foi corrigido e/ou um novo service pack ou CU para sql server foi implantado - sem fazer um teste de regressão completo do ciclo de negócios do seu aplicativo ?
• A SAN subjacente tornou-se repentinamente lenta?

As figuras podem ser derivadas se você tiver dados para mostrar. Por exemplo :

• A linha de base do seu servidor é crítica durante essa situação. Isso aliviará o jogo da culpa, pois você pode apoiar os fatos com números sólidos.
• Comece a coletar dados usando DMVs ou sp_whoisactive em uma tabela, para que os dados sejam persistidos após uma reinicialização do servidor SQL.

(você deve treinar com base em seu ambiente e necessidades, com que frequência coletar os dados/ quais dados coletar e quanto será o período de retenção) ou (você pode investir em um software de terceiros como o sqlsentry ou o gerenciador de diagnóstico do idera que fará o trabalho acima para você) .

Bem, você pode estar recebendo um plano diferente porque:

• o plano pode ter sido removido do cache devido a:
  • uma reinicialização do serviço
  • limpeza manual do cache do plano
  • uma reinicialização ou failover de serviço
  • uma alteração inadvertida, por exemplo, certas sp_configurealterações podem liberar o cache
  • alguma alteração em objetos subjacentes, índices, estatísticas ou outras dependências acionou uma recompilação
• você pode estar obtendo um plano diferente do(s) outro(s) usuário(s) ou invocações anteriores porque:
  • o texto da consulta pode não ser idêntico (isso inclui diferenciação de maiúsculas e minúsculas e espaços em branco, não importa colunas diferentes, critérios de junção, filtros, etc.)
  • a consulta pode ser executada por usuários diferentes com opções de conjunto diferentes (ou esquemas padrão diferentes, se algum objeto no plano não tiver um nome totalmente qualificado, incluindo schema )
• a consulta e o plano podem ser os mesmos, mas você pode obter um desempenho diferente porque:
  • o plano foi armazenado em cache usando parâmetros diferentes e esse plano não é ideal para o conjunto atual de parâmetros (isso é comumente chamado de "sniffing de parâmetro")
  • a quantidade de dados com base nos parâmetros ou simplesmente devido à alteração de dados nesse meio tempo é significativamente diferente
  • os dados mudaram o suficiente para alterar a maneira mais eficiente de acessar os dados, mas não o suficiente para acionar atualizações ou recompilações de estatísticas (procure por problema de chave ascendente, bem como algoritmo de estatísticas automáticas)
  • os dados foram removidos do buffer pool e agora precisam ser lidos do disco
  • há maior simultaneidade, bloqueio ou outras tensões nos recursos necessários para satisfazer a consulta

Eu passo por muitos deles com mais detalhes aqui:

• Vários planos para uma consulta "idêntica"

Se eles estiverem sendo executados em ambientes diferentes, tenho uma série de coisas para verificar aqui:

• Planos diferentes para servidores "idênticos"

Além disso, é importante ter em mente que criar um índice ou alterar a consulta pode não ser o motivo direto de uma consulta ter um desempenho melhor de repente - às vezes é apenas porque essas alterações realmente geraram um novo plano e/ou invalidaram o que já existia .

Como sempre , Aaron Bertrand e Kin forneceram respostas excelentes. No entanto, ambas as respostas contêm um fio comum. Se você analisar qualquer uma das respostas, verá que o motivo pelo qual XYZ não está funcionando como funcionou ontem não é por causa de algo que você / eles / a pessoa X fizeram. A razão pela qual as coisas mudaram é porque o banco de dados decidiu fazer as coisas de maneira diferente devido a razões XYZ.

Um banco de dados é uma entidade viva que respira . Os bancos de dados tomarão decisões e mudarão de ideia devido a uma combinação de suposições, estatísticas e outras ferramentas heurísticas. Isso é drasticamente diferente da maioria da programação da camada de aplicativo (aprendizado de máquina sendo uma exceção notável).

_{Vou usar algumas referências militares porque não consigo pensar em algo melhor agora. Uma metáfora mais geral seria apreciada (sem trocadilhos).}

Na maioria das aplicações, o programador atua como um instrutor de exercícios. Eles dizem ao computador exatamente o que fazer, em que ordem e, às vezes, por quanto tempo. Programar um banco de dados é mais como atuar como um Comandante. Você diz a ele o que deseja fazer em alto nível e oferece alguma orientação quando necessário. O banco de dados assume a tarefa de descobrir a melhor maneira de executar o plano com base na inteligência atual, como os suboficiais e suboficiais.

Ao deixar essa distinção clara nas mentes dos outros programadores, eles começarão a ver que você não tem poderes ditatoriais como eles têm sobre seu ambiente. Você está guiando o banco de dados para a solução e, ocasionalmente, o banco de dados se desvia por bons ou maus motivos. Lembre-os de que, no final, não importa por que* o banco de dados saiu dos trilhos, mas sim o que podemos fazer para trazê-lo de volta.

_{* Reconheço que "por que" é muito valioso para prevenção futura, aprendizado, etc., mas parece que o OP está enfrentando resistência de pessoas que não estão tentando aprender ou ajudar no problema.}