Versão curta
Depois de habilitar AUTO_CREATE_STATISTICS& AUTO_UPDATE_STATISTICS_ASYNCem um banco de dados, qual métrica de desempenho exibirá melhor as alterações?
Versão longa
Acabamos de perceber que o banco de dados de um sistema legado nunca teve o AUTO_CREATE_STATISTICS& AUTO_UPDATE_STATISTICS_ASYNCativado ( AUTO_UPDATE_STATISTICSfoi ativado).
Como seria de esperar, depois de ativá-los, nossos aplicativos tiveram um desempenho muito melhor. Os tempos de resposta são significativamente melhores e os usuários não esperam que a tela seja atualizada ou as páginas sejam carregadas.
O banco de dados está bem indexado e já havíamos criado estatísticas manualmente onde achamos que eram necessárias. Imediatamente após a ativação, verifiquei sys.stats(juntei-me ao sys.stats_columns& sys.objects) e só precisava criar estatísticas em três objetos. (Eu tenho isso registrado caso a quantificação dessas mudanças esteja com eles.)
Capturo muitos dados de desempenho neste servidor e examinei esses dados antes e depois da alteração, mas ainda não identifiquei onde estão os ganhos reais.
Eu tenho outros sistemas semelhantes em que as estatísticas automáticas ainda estão desativadas para esse banco de dados. Esses sistemas também se beneficiariam de habilitá-los, mas estou aguardando caso haja alguma métrica que ainda não tenha, posso adicioná-la antes de fazer as alterações.
Minha verdadeira pergunta
Além da experiência do usuário, como quantifico os aumentos feitos alterando as configurações de Auto-Stats em um sistema?
SQL Server 2008R2 no Windows Server 2008.
Se
SET AUTO_CREATE_STATISTICS ONo desempenho melhorou (casualmente) para seus usuários finais, isso provavelmente significa que as consultas com predicados em colunas sem estatísticas obtiveram melhores planos de execução e, portanto, tempos de execução mais curtos. Um plano de execução "melhor" pode resultar em uma ou mais destas melhorias:Não tenho certeza de quais métricas você está capturando. Se você conseguir chegar ao nível de tempo de execução de consulta individual, poderá encontrar exemplos das mesmas consultas antes e depois da alteração e confirmar que elas reduziram os tempos de execução (ou menos CPU e leituras, etc.).
Por exemplo, se você estiver registrando o conteúdo de
sys.dm_exec_query_statsuma tabela, poderá encontrar consultas que tenham a mesmasql_handleCPU, leituras, etc., mas inferiores, depois que a configuração for alterada.Se você estiver registrando o XML do plano de execução, poderá procurar a presença e, em seguida, a ausência de avisos como este:
Ele aparece no XML assim:
Se você tiver apenas métricas agregadas no nível do servidor para examinar, ainda poderá ver as leituras gerais ou a CPU cair, mas provavelmente é muito mais difícil ver isso com certeza se for diluído por outras cargas de trabalho e atividades de manutenção.