O SQL Server disponibiliza estatísticas de I/O que auxiliam nesta análise básica. Com o comando SET STATISTICS IO ON é possível medir a quantidade de vezes que sua query necessita ir ao disco físico ou mesmo a quantidade de leituras lógicas em cache para retornar o resultado. Com estas duas informações você pode avaliar a performance e corrigi-la antes mesmo de entrar em produção. Quanto menor for o Scan Count e o Logical Reads, melhor será a performance da query, procedure ou view. O ideal é que todas as buscas a dados sejam realizadas no CACHE e em disco apenas aqueles dados que nunca foram lidos ou que são necessários esporadicamente. Para exemplificar, vamos considerar uma tabela de teste onde é disparado o SELECT apresentado abaixo:
SET STATISTICS IO ON
SELECT * FROM TB_TESTE WHERE CD_CLIENTE= 1800
SET STATISTICS IO OFF
Dos resultados obtidos com a execução da query, a parte que realmente interessa é a parte sobre as estatísticas.
Interpretação do Resultado:
Table ‘TB_TESTE’. Scan count 2, Logical Reads 46808, Physical Reads 0, Read-Ahead Reads 0.
Coluna | Descrição | Valor | Valor ideal |
Table | Nome da Tabela envolvida no processo | TB_TESTE | |
Scan Count | Número vezes que o SQL acessou a tabela para localizar os dados de sua query. | 2 | · Mais próximo de 1. · Se >1, possível table scan.Verificar os índices desta tabela. |
Logical Reads | Número de páginas lógicas lidas do cache. (Cada página = 8Kb) | 46808 (46808*8)/1024 = 365mb | · < 100 · Este valor depende do volume de linhas afetadas |
Physical Reads | Números de páginas lidas direto do Disco. (Cada página = 8Kb) | 0 | · Apenas na primeira execução deve ser <> 0. · Se <> de 0 por mais de duas execuções, a criação de índices pode resolver. |
Read-Ahead Reads | Número de páginas lidas no cache e que não necessariamente serão utilizadas para resolver a query. | 0 | · Ideal que seja sempre igual a 0. |
Observem que para o SQL Server resolver esta query, foi necessário a leitura de 46.808 páginas em cache. Na prática, foram lidos 365MB de dados para trazer apenas um registro.
Como melhorar:
Com a criação de um índice não cluster sobre a coluna CD_CLIENTE é possível diminuir a quantidade de páginas lidas ou pesquisadas em memória e melhorar significativamente o tempo de resposta da query. Exemplo:
CREATE NonClustered INDEX Idx_Teste ON TB_TESTE (CD_CLIENTE)
GO
SET STATISTICS IO ON
SELECT * FROM TB_TESTE WHERE CD_CLIENTE= 1800
SET STATISTICS IO OFF
–Resultado:
Table ‘teste’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
Coluna | Valor | Valor Ideal |
Table | teste | |
Scan Count | 1 | · Mais próximo de 1. · Se >1, possível table scan, verificar os índices desta tabela. |
Logical Reads | 3 (3*8)/1024 = 0,02mb | · < 100 · Este valor depende do volume de linhas afetadas |
Physical Reads | 0 | · Apenas na primeira execução deve ser <> 0. · Se <> de 0 por mais de duas execuções, entrar em contato com a Equipe SQL Server. |
Read-Ahead Reads | 0 | · Ideal que seja igual a 0. |
A redução do scan count de 2 para 1 e o logical reads de 46808 para 3 demonstra um ganho tanto em tempo de execução, como no acesso aos dados no CACHE, resultando em uma query mais performática e uma redução significativa de leitura e busca para o SQL Server. Desta vez o SQL Server precisou ler apenas 24Kb para ler o mesmo registro.
Vantagens de uma Query bem escrita:
Minimiza o acesso ao disco ou cache para que o SQL retorne o resultado esperado com maior velocidade;
Menor volume de dados em memória do servidor SQL;
Melhora o desempenho do servidor SQL;
Menor tráfego de dados pela rede;
Melhora significativa de performance em sua aplicação;
Satisfação de quem utiliza sua aplicação
Bom, isso mostra o quanto a criação de um índice adequado é importante!!