Plano de execução + cache = DBA enganado

Bom dia pessoal, vamos de SQL Server mais uma vez?

Se você preferir ler o PDF deste artigo, pode baixá-lo aqui.

É comum termos que analisar o plano de execução de uma consulta ou procedimento em nosso banco de dados, aí utilizamos como ponto de partida os famosos STATISTICS IO, STATISTICS TIME e EXECUTION PLAN, para nos ajudar na análise, mas cuidado! Existe algo que pode atrapalhar e levá-lo a tirar conclusões erradas, quer ver?
Em primeiro lugar vamos criar o procedimento ListaPedidosData, que lista todos os pedidos a partir de determinada data:

USE Northwind
GO

IF OBJECT_ID('dbo.ListaPedidosData') IS NOT NULL
DROP PROC dbo.ListaPedidosData
GO

CREATE PROC dbo.ListaPedidosData
@odate AS DATETIME
AS
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= @odate
GO

Criado o procedimento, vamos executá-lo com um parâmetro de alta seletividade (isto é, que retorna poucos registros). Analisando o plano de execução podemos notar que o SQL Server sabiamente optou por fazer um index seek seguindo de quatro key lookups, um para cada registro retornado pelo seek (figura 01), com o seguinte custo de IO: Table 'Orders'. Scan count 1, logical reads 10.

EXEC dbo.ListaPedidosData '19980506'
GO

(Figura 01)

Agora vamos tentar chamar o procedimento com um parâmetro de baixa seletividade, que vai retornar todos os pedidos existentes na tabela (830). O que você espera desse plano de execução? Um clustered index scan, certo? Errado...

EXEC dbo.ListaPedidosData '19960101'
GO

O SQL Server também vai fazer um index seek seguindo de um key lookup (figura 01), pois ele está reutilizando um plano que está em cache e este foi definido dessa forma, então é a maneira que a consulta deve ser executada. Analisando o custo de IO temos: Table 'Orders'. Scan count 1, logical reads 1665. Um pouquinho diferente que o primeiro, certo? Esse é um dos preços que pagamos por estar reutilizando um plano da cache, ele pode não ser bom para o parâmetro atual.
Com o intuito de provar que o plano não é bom para essa seletividade, você acaba por tirar a consulta de dentro do procedimento e executá-la externamente, informando o parâmetro com baixa seletividade. O que você verá? Dessa vez acertou, um clustered index scan (figura 02), com custo de IO: Table 'Orders'. Scan count 1, logical reads 22.

(figura 02)

Pareceu bem melhor que o plano utilizado pela SP, não foi? Aí para termos certeza disso, vamos executar o procedimento e a consulta no mesmo batch e analisar o custo relativo de cada um.

EXEC dbo.ListaPedidosData '19960101'
go

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= '19960101'
go

Qual o resultado? O SQL Server mostra para você que a execução do procedimento tem um custo de 41% do batch, enquanto o scan custa os restantes 59%. Isto é, pelo que o SQL Server nos informa, é mais barato executar o primeiro do que o segundo (figura 03).

(Figura 03)

É nessa hora que você, leitor, grita: ooooohhhhhhhhhh! Eu podia apostar um pedaço do dedo que o segundo plano é o melhor e o SQL Server me diz que o custo dele é maior que o primeiro. Macacos me mordam!
Aí você sai olhando informação de IO, tempo de execução (CPU e Elapsed), custo de tudo que pode imaginar e ainda assim tudo aponta para o segundo como o melhor plano, mas o SQL Server insiste em mostrar um custo menor para o primeiro.
O que está acontecendo?
Faça o seguinte teste. Execute a dupla procedimento/consulta trocando o parâmetro do procedimento por uma série de valores aleatórios, conforme trecho de código abaixo:

-- comparando os tempos...
EXEC dbo.ListaPedidosData '20100101'
go

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= '19960101'
go

-- comparando os tempos...
EXEC dbo.ListaPedidosData '19970101'
go

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= '19960101'
go

O que você vai ver? Que TODOS os resultados relativos de custo estão na proporção 41% e 59%! Opa, então têm alguma coisa “errada”, que é o.... PLAN CACHE!

Você concorda que o otimizador de consultas do SQL Server utilizou uma série de estatísticas para gerar o plano que foi colocado em cache? Não iria fazer muito sentido se a cada execução o SQL Server ficasse estimando novos custos de acordo com os parâmetros, afinal o plano será reutilizado de qualquer forma, então porque perder tempo gerando essas informações? O que temos nos exemplos acima é o SQL Serve reutilizando o plano e também os custos estimados para a consulta original, que retornou somente 4 registros!
Faça o teste executando o script abaixo:

-- Execute os dois juntos SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM dbo.Orders WHERE OrderDate >= '19980506' go SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM dbo.Orders WHERE OrderDate >= '19960101' go

O que você viu? Novamente a proporção 41% e 59%. Aqui faz todo o sentido do mundo, pois um o custo do primeiro é realmente menor que o do segundo. Então para verificar a real proporção entre os custos de execução vamos usar uma hint para simular o plano que está em cache (com valores atualizados) e ver o resultado...

-- Simulando o plano em cache, mas agora com valores atualizados...
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders WITH(INDEX(OrderDate))
WHERE OrderDate >= '19960101'
go

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= '19960101'
go

Como resultado, temos a figura 04.

(Figura 04)

Ah, agora sim! O primeiro tem um custo relativo de 92% enquanto o segundo têm um custo relativo de 8% no batch.
Eu escrevi tudo isso para te dizer o que?

CUIDADO COM OS PLANOS EM CACHE QUANDO ESTIVER FAZENDO A ANÁLISE COMPARATIVA DE SUAS CONSULTAS!

Os valores armazenados em cache para um parâmetro diferente do que você está utilizando serão reaproveitados na hora de mostrar o custo relativo, podendo levá-lo a tirar conclusões erradas em seu ambiente. Então durante sua análise é interessante sempre usar um DBCC FREEPROCCACHE para limpar a cache de planos e usar hints para simular planos, facilitando a comparação.

Espero que você tenha gostado...

[]s
Luciano Caixeta Moreira - {Luti}
Chief Innovation Officer
Sr. Nimbus Ltda
E-mail: luciano.moreira@srnimbus.com.br