Desvendando a atualização assíncrona de estatísticas no SQL Server

Todos sabem a importância de sempre termos estatísticas atualizadas nos bancos de dados para que o query optimizer do SQL Server faça boas escolhas e gere bons planos de execução, fazendo com que suas queries retornem mais rápido.

Há muito tempo, o SQL Server conta com a opção de gerenciamento automático dessas atualizações (AUTO_UPDATE_STATISTICS), e faz isso muito bem por sinal.

Legal! Então é só eu deixar essa opção habilitada no meu banco de dados que as atualizações serão feitas automaticamente e eu não preciso mais me preocupar com isso.

ALTER DATABASE [DatabaseName] SET AUTO_UPDATE_STATISTICS ON

Mais ou menos…

Com essa opção habilitada, sempre que o otimizador detectar estatísticas desatualizadas, ele vai disparar uma atualização. Por padrão, esse update é disparado quando temos modificação de mais de 20% do número de linhas de uma tabela. Sendo que a partir do SQL Server 2016, esse threshold é dinâmico em tabelas com mais de 25 mil linhas. Isso quer dizer que quanto mais linhas a tabela tiver, menor será esse threshold. Em versões anteriores ao SQL Server 2016 o mesmo comportamento era conseguido com a TF 2371.

Talvez isso não seja tão legal caso você tenha um ambiente de alto volume transacional (OLTP). Quando uma estatística é marcada como desatualizada, ela não é imediatamente atualizada (o que por si só já seria ruim em certos ambientes), ao invés disso ela será atualizada quando o query optimizer decidir usar um plano de execução que use aquela estatística, fazendo com que este aguarde o término da atualização (que pode demorar dependendo do tamanho da tabela).

Para “contornar” este problema, podemos utilizar a opção AUTO_UPDATE_STATISTICS_ASYNC:

ALTER DATABASE [DatabaseName] SET AUTO_UPDATE_STATISTICS_ASYNC ON

Dessa forma, o query optimizer executa a consulta sem esperar pelas estatísticas atualizadas e dispara a atualização em outra thread, de forma que a próxima consulta utilizará as novas estatísticas. Lembrando que geralmente é ruim fazer consultas com as estatísticas desatualizadas, porém em determinados ambientes essa opção pode trazer ganhos de performance. Para cada caso devem ser mensurados os prós e contras antes da decisão.