Arquivo

Archive for the ‘Tuning’ Category

Desafio SQL Server – Performance

17 de setembro de 2012 29 comentários

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.