Início > Desafio, T-SQL, Tuning, Virtual PASS BR > Desafio SQL Server – Performance

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 Smile

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.

About these ads
  1. Alex Rosa
    17 de setembro de 2012 às 12:56

    Desafio aceito…

    Dica é para os fracos, aqui é CAVERA mano…kakakakaka

    • 17 de setembro de 2012 às 13:19

      Hahaha, boa… foi isso mesmo que eu pensei qdo escrevi sobre a dica kkk

  2. Alex Rosa
    17 de setembro de 2012 às 13:03

    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

    • 17 de setembro de 2012 às 13:21

      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! :-)”

  3. Alex Rosa
    17 de setembro de 2012 às 13:33

    “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

    • 17 de setembro de 2012 às 13:46

      Sim sim, todas as vezes. Não considere o tempo destes comandos é claro… para o desafio considere apenas o tempo do SELECT ok?

  4. Alex Rosa
    17 de setembro de 2012 às 13:38

    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

    • Alex Rosa
      17 de setembro de 2012 às 13:40

      opsss…se Y for maior que X…agora vou voltar pro trabalho….

    • 17 de setembro de 2012 às 13:49

      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…)

  5. Alan Victor
    17 de setembro de 2012 às 22:59

    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?

    • 17 de setembro de 2012 às 23:57

      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.

  6. 18 de setembro de 2012 às 13:40

    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

    • 18 de setembro de 2012 às 14:58

      kkk ta vendo só como precisa otimizar?

      rodei no meu note i5 hd 7500 liberando 1gb de memoria pro sql…
      abs

      • 27 de setembro de 2012 às 1:14

        Erickson,
        a mesma coisa aconteceu comigo hehehehhe, achei que tinha bugado…

  7. Evandro Camara
    18 de setembro de 2012 às 16:36

    Acho que ta resolvido ^^ ….do jeito que eu fiz roda em bem menos que 1s

    pra que mail eu mando??

    • 18 de setembro de 2012 às 17:18

      manda no fabiano underline amorim arrouba bol .com ponto br

    • Alex Rosa
      18 de setembro de 2012 às 19:31

      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ã

    • 18 de setembro de 2012 às 19:52

      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.

      • Evandro Camara
        18 de setembro de 2012 às 19:56

        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!

      • Alex Rosa
        18 de setembro de 2012 às 20:17

        então os meus 5s, estão valendo muito agora…rrrss

  8. 19 de setembro de 2012 às 22:04

    Estou com 11 segundos… estava tão feliz… mas pelo visto já sou na melhor das hipoteses o segundo colocado

    • 20 de setembro de 2012 às 1:03

      Lembrem-se… 11 segundos ou 5 segundos… o que vai valer é o tempo na minha maquina :-)… me manda pra eu testar por aqui.

      Abs

  9. 26 de setembro de 2012 às 15:53

    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

  10. albertolimasp
    26 de setembro de 2012 às 16:01

    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

  1. 19 de setembro de 2012 às 20:57
  2. 27 de setembro de 2012 às 12:36

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

Seguir

Obtenha todo post novo entregue na sua caixa de entrada.

Junte-se a 60 outros seguidores

%d blogueiros gostam disto: