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, OrderDateFROM dbo.OrdersWHERE OrderDate >= @odate
GOCriado 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'
GOO 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'
goQual 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'
goO 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'
goComo 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 OfficerSr. Nimbus Ltda
E-mail:
luciano.moreira@srnimbus.com.br
