Neste post irei abordar alguns recursos para otimizar o desempenho de consultas no SQL Server. Dois recursos simples e bem úteis são:
• set statistics time on
• set statistics io on
Ao executar os comandos acima você habilita a visualização na sua sessão das estatísticas de I/O e Tempo de execução de uma consulta SQL, não é necessário executar os dois, você habilitar apenas um se quiser. Para desabilitar este recurso execute
• set statistics time off
• set statistics io off
Quando executar um select você verá na guia Messages (Caso você esteja como o modo de visuzalção Modo Grade, pressione CRTL + D para habilitar o resultado) ou na guia Result (Caso você esteja no modo de visualização Resultado em texto, pressione CRTL + T para habilitar esse modo)
Tempo de análise e compilação do SQL Server:
Tempo de CPU = 0 ms, tempo decorrido = 1 ms.
(12 linha(s) afetadas)
Tabela ‘categoria’. Número de verificações 1, leituras lógicas 2, leituras físicas 0, leituras read-ahead 0, leituras lógicas lob 0, leituras físicas lob 0, leituras read-ahead lob 0.
Tempos de Execução do SQL Server:
Tempo de CPU = 0 ms, tempo decorrido = 0 ms.
Executei um select de uma tabela com 12 registros para gerar essas estatísticas.
Na primeira parte é mostrado as estatísticas de tempo de execução da consulta, ondes temos:
• Tempo de CPU = Tempo gasto pela CPU do servidor para processar a consulta
• Tempo decorrido = Tempo de execução da consulta
Na segunda parte temos
Tabela = Nome da tabela envolvida na consulta
Número de verificações = Número de buscas iniciadas depois de alcançar o nível folha em qualquer direção para recuperar todos os valores para saída final (em um próximo post pretendo explicar a estrutura b-tree no SQL Server).
leituras lógicas = Número de páginas lidas do cache de dados do SQL Server.
leituras físicas = Número de páginas lidas no disco do servidor (quanto menor melhor).
leituras read-ahead = Número de páginas colocadas no cache para a consulta.
leituras lógicas lob = Número de colunas com valor grande: VARCHAR(MAX), VARCHAR(MAX) e VARBINARY(MAX) lidas do cache.
leituras físicas lob = Número de colunas com valor grande: VARCHAR(MAX), VARCHAR(MAX) e VARBINARY(MAX) lidas do disco do servidor.
leituras read-ahead lob = Número de colunas com valor grande: VARCHAR(MAX), VARCHAR(MAX) e VARBINARY(MAX) adicionadas no cache de dados.
Para melhorar uma consulta você deve analisar se o número de leitura de disco e tempo de execução da consulta diminuiu, e muitas vezes entre uma criação de índice.
Cache
Um outro recurso útil para avaliarmos a estatística corretamente é o comando
DBCC DropCleanBuffers
Esse comando limpa o cache de dados do SQL Server, muitos DBAs reiniciam a instancia para limpar o cache, fazendo com que o banco fique indisponível, com esse comando isso não se faz necessário(o banco irá ficar indisponível por um período menor), porem existem dois detalhes:
• CHECKPOINT = Antes de executar o DropCleanBuffers, você deve executar um CHECKPOINT, ou seja, gravar os dados das páginas sujas para o disco, assim você evita perda de dados, o CHECKPOINT já é executado automaticamento(em algumas situações, pretendo descrever melhor em outro post)
• Executar um DropCleanBuffers em ambiente de produção você perceberá um impacto significativo nas aplicações que consomem o banco.
Ou seja, execute um CHECKPOINT antes de um DBCC DropCleanBuffers.
Visualizando o cache do SQL Server
Para acompanhar o estado do cache podemos utilizar um contador de performance SQLServer:Buffer Manager > Database Pages
Como segue nos passos abaixo
1. Abra o executar do Windows e digite: perfmon e pressione Enter.
2. Após ter aberto o Monitor de performance click em Desempenho de Sistema
3. Click no sinal de mais(o sinal verde, na barra superior) ou pressione CTRL + N, para adicionar um novo contador
4. Na lista da esquerda você deve selecionar o contador desejado, no caso: SQLServer:Buffer Manager, click no sinal de + para expandir depois de localiza-lo
5. Ao expandir todos os itens estão selecionados por padrão, deixe apenas o Database Pages e click em Adicionar
6. Dê OK
Seu Monitor de performance deve estar assim
Você pode desmarcar a opção mostrar na lista de contadores adicionados, foi o que eu fiz para deixar a tela mais limpa, desmarquei o contador: % Tempo do processador.
Agora vamos executar o comando para limpar o cache.
CHECKPOINT GO DBCC DROPCLEANBUFFERS
Repare no gráfico após a execução
Qualquer dúvida é só perguntar, até uma próxima.
André Btoe 