SET STATISTICS IO

Há 20 anos quando comecei a estudar banco de dados, imaginava que o SQL era uma caixa-preta. Bastava inserir os dados e não precisaria me preocupar mais. Eu era um desenvolvedor sem conhecimento de banco de dados.

Para relembrar esse cenário, vou criar um banco de dados chamado LOJADB usando um pendrive (meu storage de 8GB).

Usei um script simples com o único cuidado de deixar o espaço pré-alocado e sem Auto-Growth para evitar surpresas nos testes.

Nesse banco de dados, havia uma tabela de cadastro de clientes. Simplificando, seria algo assim:

Voltando ao passado, comecei a inserir os dados no banco de dados. Era uma rotina aparentemente simples, mas demorava demais. A rotina levou cerca de 85 segundos para completar e inserir 10.000 registros – dividindo, dá 117 linhas por segundo. Na época isso chamou minha atenção, pois imaginava que tudo no banco de dados fosse rápido!

Comecei a executar alguns comandos e eles rodavam imediatamente!

Em seguida fazia consulta de cliente e o resultado era instantâneo!

Essa foi minha primeira experiência com banco de dados: aparentemente as inserções eram lentas, mas as consultas eram rápidas.

SET STATISTICS

Se você já foi desenvolvedor e vivenciou essa situação, então preste atenção ao comando SET STATISTICS. Esse foi o comando que abriu os meus olhos para entender como que um banco de dados funciona.

Primeiro vamos rodar o comando SET STATISTICS TIME.

SET STATISTICS TIME
https://msdn.microsoft.com/en-us/library/ms190287.aspx

O resultado do SET STATISTICS TIME mostra que a execução durou apenas 1ms, que é um resultado excelente!

Em seguida, rodei o comando SET STATISTICS IO.

SET STATISTICS IO
https://msdn.microsoft.com/en-us/library/ms184361.aspx

Isso me surpreendeu, pois não imaginava ver 1257 logical reads (parece um valor alto?). Fiquei curioso para entender o significado de cada um dos valores.

• Scan count: Número de scan em tabelas
• Logical reads: Quantidade de páginas lidas (memória ou disco)
• Physical reads: Quantidade de páginas lidas do disco
• Read-ahead reads: Quantidade de páginas lidas com prefetch
• Lob logical reads, lob physical reads, lob read-ahead reads: Contador de acesso aos large object (ex: TEXT) – desconsidere, por enquanto.

As consultas no banco de dados eram feitas todas em memória sem a necessidade de ir ao disco. Por isso, repeti os testes com SET STATISTICS TIME e IO, mas antes rodei o comando DBCC DROPCLEANBUFFERS. Isso eliminaria o cache em memória e forçaria o acesso ao disco.

Nesse cenário, a consulta demorou 369ms. Executando o comando novamente sem limpar o cache:

O tempo voltava para 1ms como esperado.

Conclusão

Surpresa? Espero que não. Trabalhar com dados em cache é muito mais rápido.

Termino esse artigo relembrando que o disco é o principal recurso do SQL Server. Eu poderia perder muito tempo fazendo otimizações com base no SET STATISTICS TIME ao invés de usar o STATISTICS IO. Monitore o disco. Priorize o disco. Minha dica é que, se você não conhece o SET STATISTICS IO, então invista parte do tempo para entender esse comando. Ele é uma das melhores ferramentas de aprendizado.

Nota: Você notou que um dos resultados do SET STATISTICS IO retornou 1257 logical reads e 823 read-aheads? Não deveriam ser iguais? No próximo post vamos explorar melhor o comando DBCC DROPCLEANBUFFERS e explicar esse comportamento.