Quando o SQL Server Query Optimizer está Errado

Na maioria dos casos, o SQL Server Optimizer gera planos ótimos. É impossível competir com o seu conhecimento interno do custo de acesso ao disco, tamanho de página e também quanto a taxa de preenchimento de uma página. Mas, há uma área onde a expertise humana é sempre superior.

Você  pode pode pular direto para os resultados ou se quiser crie as tabelas abaixo em um banco vazio:

                                         Create table Sales
                                         (
                                         Id int primary key,
                                        Amount money not null,
                                        Date datetime not null,
                                        Comment varchar(128)
                                        )
                                        GO

                                       Create table SaleItems
                                       (
                                       SaleId int,
                                       ItemId int not null,
                                      Quantity int
                                       )
                                       GO

A tabela de vendas terá 10.000 registros aleatórios. A tabela SalesItems vai ter de 1 a 10 itens para cada registro de venda na tabela Sales. Vamos executar os scripts a seguir para preencher as duas tabelas e criar índices:

set nocount on
GO
declare @n int, @m int
set @n=10000
while @n>0 begin
insert into Sales select @n,$10.0*(@n%100)+$100.,dateadd(hh,-@n,'20070507'),
'This is sale N '+convert(varchar,@n)
set @m=@n%10
while @m>0 begin
insert into SaleItems select @n,@m,@m+@n
set @m=@m-1
end
set @n=@n-1
end
GO
create index SaleItems_Id on SaleItems (SaleId)
GO
create index Sales_Dates on Sales (Date)
GO

Notemos que Sales.Date varia de 2006-03-16 08:00 até 2007-05-06 23:00.
Agora, fazemos uma primeira tentativa de escrever um procedimento  que recupera a quantidade máxima de venda por um período de tempo especificado.

create procedure GetMaxQuantity
@p1 datetime, @p2 datetime
as
select max(Quantity) from SaleItems where SaleId in (
select Id from Sales where Date between @p1 and @p2)
GO

Antes de executar o procedimento, vamos habilitar as Estatísticas de IO, executando:

set statistics io on

Também é importante que o cache do buffer seja limpo, antes de executar o procedimento. Caso contrário, o número de leituras físicas não será preciso:

DBCC DROPCLEANBUFFERS 

Agora, vamos executar o procedimento:

exec GetMaxQuantity '20070301' , '20070302'

No ambiente de teste,  recebo as seguintes estatísticas de IO:

Table 'SaleItems'. Scan count 25, logical reads 191, physical reads 1, read-ahead reads 4.
Table 'Sales'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0.

Vamos executar o procedimento novamente com um intervalo de datas diferentes:

dbcc dropcleanbuffers
exec GetMaxQuantity '20060301','20070302

Desta vez, as seguintes estatísticas de IO são retornadas:

Table 'SaleItems'. Scan count 8417, logical reads 55564, physical reads 90, 
read-ahead reads 173.
Table 'Sales'. Scan count 1, logical reads 17, physical reads 1, read-ahead reads 16.

É importante que você execute a primeira consulta antes da segunda. Não há nenhuma surpresa quanto a segunda execução, para todo o ano, exija muito mais leituras lógicas e físicas. Embora, 55.564 leituras lógicas seja muito alto. Então o que aconteceu?

Quando verificamos o plano de exução percebemos que o problema está no Row count, onde o valor esperado é 23. Mas, quando executamos a instrução abaixo encontramos o problema:

select count(*) from Sales where Date between '20060301' and '20070302'

Na verdade, há 8.417 registros em vez de 23, o que era esperado pelo Query Optimizer.
Existem 37.884 registros em vez dos 118 esperados!

select count(*) from SaleItems where SaleId in (
select Id from Sales where Date between '20060301' and '20070302')

Como podemos ver, quando as constantes são fornecidas, o SQL Server pode obter informações das tabelas estatísticas. Quando o Query Optimizer percebe que há muitos registros, ele muda o plano de execução e, usa um Hash Join.

Este é o cenário mais comum: o SQL Server subestimou o número de linhas. O Query Optimizer pode superestimar o número de linhas, e usar um Hash Join e ler toda a tabela quando realmente precisaria de apenas algumas linhas.

Técnicas de análise e otimização requerem abordagem individual, mas também de todo o conjunto de consultas. Em geral, recompilar os planos de execução deve ser evitado devido à perda de desempenho. Isto pode ser evitado pelo uso de consultas com parâmetros e procedimentos, evitando também cursores sobre tabelas temporárias. No entanto, recompilar planos pode trazer benefícios quando o Query Optimizer é capaz de criar um plano de execução mais eficiente.
É muito importante que monitoremos por meio de estatísticas (se forem mantidas atualizadas) e usar o Profiler para execução de consultas custosas, bem como para verificação e fiscalização da utilização dos recursos.

A técnica de otimização mais importante supõe limitar a quantidade de dados retornados, limitando o número de registros (cláusula WHERE) e os campos especificados na lista SELECT. Isto levará a uma utilização eficiente dos índices. Em princípio, uma cláusula WHERE deve ser seletiva, pois podemos usar os índices existentes nas colunas.