Como posso medir a distância de uma consulta do limite aceitável (em relação ao desempenho) com base no número de leituras relatadas após sua execução?
Exemplo: Só para me esclarecer, se estivéssemos falando de milhas por galão de um carro cujo consumo de combustível é de 40 mpg e devemos percorrer uma distância de 40 milhas, o limite para o desempenho ideal é de 1 galão para viajar aquela distância. Como nem todo mundo dirige como Max Verstappen ou Lewis Hamilton , é aceitável gastar 1,3 ou até 2 galões para percorrer essa distância se você for um mau piloto. Mas se você gastar 30 galões para chegar a um lugar que fica a 40 milhas de distância, posso dizer que você certamente está indo na direção errada.
Background: às vezes eu executo sp_whoisactive apenas para encontrar uma consulta relatando algo como 1.248.909 leituras (já que cada página tem 8 KB, isso significa que cerca de 9,7 GB de dados foram processados de um banco de dados de 4,4 GB). Isso significa que uma consulta lê o equivalente a todo o banco de dados duas vezes. Quando vejo algo assim, meu instinto me diz que algo está errado, mas o desenvolvedor às vezes argumenta que " é uma consulta complexa e era esperado que se comportasse assim ". Então eu tenho que melhorar a consulta para provar que, embora uma consulta complexa possa consumir mais recursos, há um limite que mostra que você está fora do caminho.
Existe uma fórmula para calculá-lo com base no número de tabelas envolvidas, tipos de junções, uso de funções e assim por diante? Se não, existe uma regra prática que poderia ser usada para fazer um argumento lógico?
Infelizmente não, eu não acho que você encontrará uma fórmula ou mesmo uma regra prática. É muito variado e abstrato para ter um padrão, porque varia caso a caso. Para a consulta A, 1.248.909 leituras para 9,7 GB de dados em um banco de dados com 4,4 GB de tamanho podem estar totalmente corretos, enquanto a consulta B com as mesmas métricas exatas pode ser motivo de preocupação e motivo de otimização. Somente você, como DBA/desenvolvedor de banco de dados/cara de quase banco de dados, pode realmente usar seu conhecimento íntimo do banco de dados e dos aplicativos de consumo, e intuição, para determinar se uma consulta individual está utilizando uma quantidade válida de recursos.
Dito isto, eu geralmente seria suspeito de grandes quantidades de consultas de processamento de dados, especialmente em um banco de dados tão pequeno, como a consulta A acima mencionada. Então, eu consideraria examinar exemplos como esse, mas você também precisa considerar quais são as implicações de desempenho de tal consulta no resto do servidor. Ou seja, está consumindo recursos de outras consultas em um servidor ocupado? Está bloqueando excessivamente outras consultas / bloqueando as tabelas subjacentes envolvidas? Tem um tempo de execução muito lento ou volta em um prazo muito aceitável? Se passar no teste nesses tipos de perguntas, embora possa valer a pena registrar para referência futura, pode não valer a pena tentar otimizar agora , pois geralmente há peixes maiores para fritarcom outros problemas reais de desempenho no servidor (outras consultas que realmente causam problemas ou ajuste de banco de dados ou servidor que pode ser feito).
Além disso, acrescentarei que uma maneira de determinar se determinadas consultas estão consumindo mais recursos do que o necessário é observando a Memória Solicitada e a Memória Concedida versus a Memória Usada de suas consultas. Deve ser parte do plano de execução real, e as ferramentas Plan Explorer do SentryOne permitem destacar um operador individual (embora o SSMS também possa mostrar você em destaque?) e ver as métricas desse operador. Geralmente, descobrirei que, se tiver um problema de estimativa de cardinalidade em meu plano de execução, em que ele acredita que uma quantidade significativa de mais linhas será retornada do que realmente é, a quantidade de Memória Solicitada e Memória Concedida excede significativamente a Memória Usada. Isso significa que não apenas uma quantidade desnecessária de recursos foi alocada fora do servidor para essa consulta, mas essa consulta em si teve que esperar mais tempo para que esses recursos fossem alocados. Você também pode se deparar com o problema oposto com uma consulta sendo executada mais lentamente porque subestimou a quantidade de memória necessária, solicitando muito pouco do servidor. Brent Ozar tem um bom artigo Uma introdução à memória de consulta que entra em mais detalhes sobre essas métricas de memória.