Desafio SQL Server – Performance
UPDATE 18-09-2012: Este desafio é baseado em um cenário real que trabalhei, porém eu não me atendei a um fato importantíssimo… No cenário original o group by era efetuado em 23 colunas. Isso significa que se você tentasse criar um índice com base em todas as colunas do group by, o SQL batia o limite de 16 colunas como chave de um índice. O que quero dizer com isso é que não vale criar uma view indexada para ajudar nesta solução 
…
Quando criei este cenário de exemplo eu não me atentei a este ponto extremamente importante.
De qualquer forma, meus parabéns para o Evandro Camara que me mandou a solução que roda em menos de 1 segundo utilizando a view indexada. Vou considerar a resposta dele como válida, e estou mudando o desafio para… Não vale usar a view indexada… (ou seja, igual ao cenário original que trabalhei)… Eu poderia mudar o cenário para usar mais de 16 colunas no group by… daria na mesma…
Na solução do desafio falo melhor em relação ao beneficio da view indexada neste caso.
————————————————————————————————
Galera, depois do desafio de segurança da semana passada, vamos para um desafio sobre performance!
Este vai ser um pouco mais simples e vai ser no mesmo esquema do último desafio, ou seja, ganhador vai levar uma cópia do meu livro impressa.
A ideia do desafio é simples, temos uma consulta, e precisamos efetuar as modificações necessárias para melhorar a performance, você pode usar qualquer recurso disponível no SQL Server 2008 R2, a ideia é ter performance na LEITURA, ou seja, se precisar criar 50 índices (por favor, não crie!) pode criar, reescrever a consulta também vale, contanto é claro que os resultados sejam os mesmos.
É obrigatório usar MAXDOP 1 para evitar paralelismo.
Vejamos o cenário:
USE tempdb
GO
— Prepara ambiente
— Aproximadamente 2 minutos para rodar
— Tab com 287368 KB
IF OBJECT_ID(‘OrdersBig’) IS NOT NULL
DROP TABLE OrdersBig
GO
CREATE TABLE OrdersBig (OrderID int NOT NULL IDENTITY(1, 1),
CustomerID int NULL,
OrderDate date NULL,
Value numeric (18, 2) NOT NULL)
GO
ALTER TABLE OrdersBig ADD CONSTRAINT xpk_OrdersBig PRIMARY KEY CLUSTERED (OrderID)
GO
— Tab com 5 milhões de linhas
INSERT INTO OrdersBig(CustomerID, OrderDate, Value)
SELECT TOP 5000000
ABS(CONVERT(Int, (CheckSUM(NEWID()) / 10000000))),
CONVERT(Date, GetDate() – ABS(CONVERT(Int, (CheckSUM(NEWID()) / 10000000)))),
ABS(CONVERT(Numeric(18,2), (CheckSUM(NEWID()) / 1000000.5)))
FROM sysobjects a, sysobjects b, sysobjects c, sysobjects d
GO
ALTER TABLE OrdersBig ADD CountCol VarChar(20)
GO
UPDATE TOP (50) PERCENT OrdersBig SET CountCol = ‘Count’
WHERE CountCol IS NULL
GO
UPDATE TOP (50) PERCENT OrdersBig SET CountCol = ‘CountDistinct’
WHERE CountCol IS NULL
GO
UPDATE OrdersBig SET CountCol = ‘CountDistinct_1’
WHERE CountCol IS NULL
GO
CHECKPOINT
GO
O comando acima criamos uma tabela chamada OrdersBig com 5 milhões de linhas, a consulta é a seguinte:
— Problema
/*
Consulta demora 1 minutos e 41 segundos para rodar e o uso dos recursos são:
CPU: 96503
Reads: 75902646
Writes: 100350
Duration (ms): 101522
*/
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
SET STATISTICS IO ON
SELECT a.CustomerID,
a.CountCol,
CASE a.CountCol
WHEN ‘Count’ THEN COUNT(1)
WHEN ‘CountDistinct’ THEN COUNT(DISTINCT a.OrderDate)
WHEN ‘CountDistinct_1’ THEN COUNT(DISTINCT 1)
ELSE NULL
END AS Cnt,
CASE (SELECT AVG(b.Value)
FROM OrdersBig b
WHERE b.CustomerID = a.CustomerID)
WHEN 1000 THEN ‘Média = 1 mil’
WHEN 2000 THEN ‘Média = 2 mil’
WHEN 3000 THEN ‘Média = 3 mil’
WHEN 4000 THEN ‘Média = 4 mil’
WHEN 5000 THEN ‘Média = 5 mil’
ELSE ‘Não é número exato’
END AS Sts
FROM OrdersBig AS a
GROUP BY a.CustomerID, a.CountCol
ORDER BY a.CustomerID
OPTION (MAXDOP 1)
SET STATISTICS IO OFF
GO
Antes de rodar a consulta estou rodando um FREEPROCCACHE, DROPCLEANBUFFERS e CHECKPOINT para forçar a compilação do plano e gerar leitura física.
A consulta cima roda em aproximadamente 1 minuto e 41 segundos e utiliza os recursos descritos acima (nos comentários).
O desafio consiste em melhorar a consulta para que ela rode no menor tempo possível e utilize menos recursos. A medida mais importante é, TEMPO, ou seja, quanto menos tempo para rodar a consulta melhor.
Eu consegui baixar o tempo da consulta acima para 7 segundos utilizando os seguintes recursos:
/*
Consulta demora 7 segundos para rodar e o uso dos recursos são:
CPU: 6552
Reads: 22523
Writes: 2
Duration (ms): 7249
*/
Se você conseguir chegar próximo aos números acima me manda sua solução que vou testar e medir os resultados, os seus números com certeza serão um pouco diferente do meu já que crio os dados dinamicamente, os valores exibidos pra você podem ser diferentes dos exibidos pra mim.
Vou dar uma dica… que dica o que rapaz… se vira! kkk
Boa sorte e divirta-se.
Deixe um comentário
Fabiano Amorim
SQL Server MVP
Meu livro, Complete ShowPlan Operators
Livro, SQL Server além do conceito
Livro, Diálogos ao vento
Blog - Fabiano Neves Amorim 
Desafio aceito…
Dica é para os fracos, aqui é CAVERA mano…kakakakaka
Hahaha, boa… foi isso mesmo que eu pensei qdo escrevi sobre a dica kkk
opaa…um detalhe importante, cade a foto do Catae recebendo o livro???
vc lança o segundo desafio e não mostra que entregou o livro do primeiro…rrrrsss…malandro…
pronto, agora vc terá dor de cabeça com isso…hehehehe
hahha, Catae abriu mão do livro :-)… segue a resposta dele sobre isso:
“Do livro, deixa o pessoal enviar as sugestões e manda o premio para eles. A gente se entende depois! :-)”
“Antes de rodar a consulta estou rodando um FREEPROCCACHE, DROPCLEANBUFFERS e CHECKPOINT para forçar a compilação do plano e gerar leitura física.”
vc está limpando o cache todas as vezes antes de fazer a consulta ou não? isso muda bastante o cenário
Sim sim, todas as vezes. Não considere o tempo destes comandos é claro… para o desafio considere apenas o tempo do SELECT ok?
eu novamente, rrrsss
como tem variáveis de ambiente que serão diferentes, maquina por exemplo.
que tal compararmos a porcentagem de ganho entre a Execução Original e a Execução Otimizada?
comparar entre: CPU, Reads, Writes e Duration…
Assim, saberemos que no seu ambiente vc teve X % de ganho e os participantes tiveram Y % de ganho…se Y for menor que X…vc perdeu maluco…hehehehe
opsss…se Y for maior que X…agora vou voltar pro trabalho….
Ummm, pode ser, mas lembre-se o tempo é o fator predominante… você por exemplo pode chegar em uma solução que usa mais CPU porem tem um tempo melhor… (usa um hash aggregate por exemplo…)
Desafio aceito²!
Tenho duas duvidas: o tempo usado pra criar os indices (e quaolquer outra coisa que queiramos criar) será contado?ou apenas o tempo do select em si?
E até quando tempos pra enviar a soluções?
Grande Alan, quanto tempo eim :-)…
Vamos contar apenas o tempo do select em si, e não o tempo de criação dos índices.
Vou deixar pelo menos 1 semana, talvez 2, dependendo das respostas que eu receber ;-).
Abs.
Para conhecimento, em que hardware os testes foram realizados? Na minha “incrível” máquina a primeira consulta está a mais de 7min rodando e nada… rsrs
kkk ta vendo só como precisa otimizar?
rodei no meu note i5 hd 7500 liberando 1gb de memoria pro sql…
abs
Erickson,
a mesma coisa aconteceu comigo hehehehhe, achei que tinha bugado…
Acho que ta resolvido ^^ ….do jeito que eu fiz roda em bem menos que 1s
pra que mail eu mando??
manda no fabiano underline amorim arrouba bol .com ponto br
menos que 1s….bom heim…estou em 5s aqui…mas tenho a leve impressão de que usei técnicas que serão reprovadas…rrsss….vou mandar amanhã
Te respondi no e-mai… e vou atualizar o blog post com o seguinte comentário…. (to tentando fazer isso com esse lixo de internet)
UPDATE 18-09-2012: Este desafio é baseado em um cenário real que trabalhei, porém eu não me atendei a um fato importantíssimo… No cenário original o group by era efetuado em 23 colunas. Isso significa que se você tentasse criar um índice com base em todas as colunas do group by, o SQL batia o limite de 16 colunas como chave de um índice. O que quero dizer com isso é que não vale criar uma view indexada para ajudar nesta solução …
Quando criei este cenário de exemplo eu não me atentei a este ponto extremamente importante.
De qualquer forma, meus parabéns para o Evandro Camara que me mandou a solução que roda em menos de 1 segundo utilizando a view indexada. Vou considerar a resposta dele como válida, e estou mudando o desafio para… Não vale usar a view indexada… (ou seja, igual ao cenário original que trabalhei)… Eu poderia mudar o cenário para usar mais de 16 colunas no group by… daria na mesma…
Na solução do desafio falo melhor em relação ao beneficio da view indexada neste caso.
Obrigado pelos parabéns Fabiano….hehehe…mas meu aniversário foi ontem xD
Te mandei uma sugestão para resolver o seu problema no mundo real (pena que não posso simulá-lo.)
Grande abraço!
então os meus 5s, estão valendo muito agora…rrrss
Estou com 11 segundos… estava tão feliz… mas pelo visto já sou na melhor das hipoteses o segundo colocado
Lembrem-se… 11 segundos ou 5 segundos… o que vai valer é o tempo na minha maquina :-)… me manda pra eu testar por aqui.
Abs
Boa tarde Fabiano, criei alguns indexes e o script da consulta, te mandei agora no email, não sei se o resultado é o mesmo que o seu, mas ao menos está bem próximo no que se refere ao número de Reads da sua consulta e no tempo também, mas como estamos testando em maquinas diferentes, veja se atinge o resultado esperado os valores que rodaram na minha maquina, mandei via Email.
Um abraço
Boa tarde Fabiano, criei alguns indexes e alterei a query procurando manter o resultado, enviei a pouco tempo no seu email, os resultados foram próximos dos seus, tanto em leitura como em tempo, mas como estamos em maquinas diferentes esse tempo da minha maquina é irrelevante e só serve como parâmetro mesmo….rsrs
Mas o plano e as instruções estão em anexo no email.
Um abraço
Alberto Lima
Fabiano, bom dia.
Cara, muito legal este desafio, estava justamente procurando um cenário para simular melhorias de performance e escrita de querys no SQL Server.
Vou fazer uso deste Desafio na aula de hoje para meus alunos.
Valeu.
Fabiano, boa tarde.
Gostei muito deste Desafio, sensacional, muito bem elaborado e pensado.
Se você me permitir vou fazer uso deste conteúdo em minhas aulas de Programação para Banco de Dados.
Um grande abraço.
Boa tarde Junior, obrigado pelo comentário.
Pode ficar a vontade para usar o desafio na sua aula. Alias, fico feliz em poder ajudar. Fico também feliz de saber que você se deu o trabalho de comentar aqui no post me avisando que iria fazer isso… Fico pe da vida quando alguém usa conteúdo alheio sem dar os devidos créditos e sem ao menos avisar o autor original… Abs.