Non-SARG que nada! Enganei o SQL Server...

Bom dia pessoal.

Prefere ler em PDF?




Ontem eu citei em uma palestra do Teched 2010 que um index seek exibido no plano de execução pode esconder na verdade um range scan ou até um full scan, então vou pegar um gancho de uma pergunta que apareceu recentemente em um treinamento que estava ministrando.

Explicando sobre non-search arguments eu demonstrei a consulta 01, que busca as vendas de um determinado mês. Porém sendo um non-sarg, o SQL Server utiliza o índice OrderDate (NCL) mas não faz um seek, e sim um NonClustered Index Scan, varrendo 4 páginas (o índice é muito pequeno).

Para resolver o problema, vamos alterar a consulta removendo o non-sarg, conforme a consulta 02, fazendo com que o SQL Server faça um seek (lendo duas páginas - raiz e folha) ao invés de um scan.

-- Consulta 01
SELECT orderdate, OrderID FROM Orders WHERE MONTH(OrderDate) = 07 and YEAR(OrderDate) = 1996


-- Consulta 02
SELECT OrderDate, OrderID FROM Orders WHERE OrderDate between '19960701' and '19960731 23:59:59.997'

Nesse momento um DBA fez uma observação muito curiosa: “eu posso enganar o SQL Server!” E para isso basta executar a consulta 03.

-- Consulta 03
SELECT OrderDate, OrderId FROM Orders WHERE MONTH(OrderDate) = 07 and YEAR(OrderDate) = 1996 and OrderDate > 0

Prontinho, se você olhar o plano gerado pelo SQL Server (figura 01) verá que ele está fazendo um index seek, ao invés do index scan gerado originalmente pelo non-sarg. Resolvido? No no no meu caro, na verdade não estamos enganando o SQL Server, infelizmente estamos sendo enganados.

(Figura 01)

Se olharmos com calma, o seek predicate é somente o “orderdate > 1990-01-01”, isto é, um belo scan no nosso índice não-cluster e enquanto o SQL Server está fazendo esse “seek”, ele vai tentando aplicar o predicado com MONTH e YEAR, que é o nosso non-sarg.

Putz, como eu sei que o SQL Server está fazendo um index scan? Dê uma olhada no STATISTICS IO e você verá o seguinte: “Table 'Orders'. Scan count 1, logical reads 4”. Hhhuummm, scan count = 1 (o SQL Server está fazendo um scan!) e logical reads = 4 é o mesmo que vimos durante a execução da consulta 01. Houve então alguma diferença efetiva no plano de execução? NÃO! Se você executar lado a lado a consulta 01 e a 03, verá um custo relativo de 50% para cada.

O que me incentivou a finalmente escrever esse post? Hoje cedo em vi que o time de CSS postou um artigo bem legal, chamado “SCAN COUNT meaning in SET STATISTICS IO output”, que explica um pouco sobre o scan count e serve de base para esse post, em que o “falso index seek” que é um full scan ou um range scan.

Espero que seja útil, e não deixe esses detalhes do SQL Server te enganar, ok? :-)

Abraços e até um próximo artigo.

[]s

Luciano Caixeta Moreira - {Luti}

luciano.moreira@srnimbus.com.br

www.twitter.com/luticm

www.srnimbus.com.br

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

Por que este plano de execução?

Bom dia leitor.
Hoje eu vou trazer um ponto de interrogação para vocês, pois de vez em quando eu também esbarro em alguns detalhes que me deixam muito curioso para entender melhor como o SQL Server funciona. Quer ver?

Durante o treinamento de SQL Server 2008 Internals que eu estava ministrando, nós esbarramos em um plano de execução que se mostrou ligeiramente pior que o outro, em um momento onde eu esperava planos iguais. Teste aí...

USE AdventureWorks2008
go

select *
from sales.salesorderheader
where orderdate < '20010710'
go
select *
from sales.salesorderheader with(index(1))
where orderdate < '20010710'
go

Analisando os planos de execução eu obtive o seguinte resultado:

(Figura 01)

Notem que a primeira consulta é um pouco mais cara que a segunda (51% vs. 49%) e a diferença está no local onde o filtro (orderdate < ‘20071001’) é aplicado. Na primeira consulta o filtro é aplicado após a varredura as páginas do índice cluster (figura 02), gerando vTables maiores entres os passos do plano, enquanto na segunda consulta o SQL Server aplica o filtro no momento em que as páginas de dados (nível folha) são percorridas (figura 03).

(Figura 02)

(Figura 03)

A diferença entre os custos de execução já foi entendida, mas deixo aqui algumas perguntas...

1) Se com a hint eu forcei o SQL Server a usar o mesmo índice que ele escolheu para a primeira consulta, o plano de execução deveria ser o mesmo, não?
2) Porque no primeiro caso o SQL Server optou por fazer o filtro somente no fim? Sempre que penso no otimizador de consultas, imagino ele aplicando os filtros o quanto antes, para evitar vTables maiores e operações de maior custo no decorrer da execução.

Só para te poupar o trabalho, é claro que as estatísticas estão atualizadas e o custo de I/O é o mesmo para ambas as consultas, o tempo é obviamente diferente por conta do tamanho da vTables geradas e do filtro. Quando estiver fazendo os seus testes, recomendo usar o DBCC FREEPROCCACHE para não ficar caindo no mesmo plano de execução que está em cache (e foi parametrizado pelo SQL Server - note o “@1” na figura 01).

E aí, conhece o SQL Server? Então conte para todo mundo o que está acontecendo...
Em um próximo post eu detalho um pouco o comportamento do SQL Server para este caso e tento desvendar parte do mistério.

Nota: reproduzi o comportamento no SQL Server 2008 RTM e SP1. Quem testar em outras versões me conte.

Prefere ler o PDF? Baixe aqui.

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