Arquivo
Novo Desafio SQL Server – Performance 2
|
E ai meu povo, estão prontos para mais um desafio? Espero que sim! Aqui vai mais um desafio relacionado a performance. Conforme os outros desafios, quem quiser participar é só me enviar sua solução por e-mail no “fabiano underline amorim arroba bol ponto com ponto br”. Como de praxe o ganhador leva uma cópia do meu livro impressa. (Alberto e Evandro não me xinguem, essa semana eu mando o livro de vocês :-)) |
 |
O desafio de hoje consiste em melhorar a performance da seguinte consulta:
CHECKPOINT
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
SELECT Pedidos.NumeroPedido,
Pedidos.DT_Pedido,
Pedidos.Valor,
SUM(ItensPed.Qtde) AS TotalItens
FROM Pedidos
INNER JOIN ItensPed
ON Pedidos.NumeroPedido = ItensPed.NumeroPedido
INNER JOIN Produtos
ON ItensPed.ID_Produto = Produtos.ID_Produto
WHERE Pedidos.DT_Pedido BETWEEN ‘20121010’ AND ‘20121020’
AND Produtos.Descricao like ‘%cicle%’
GROUP BY Pedidos.NumeroPedido,
Pedidos.DT_Pedido,
Pedidos.Valor
OPTION (RECOMPILE, MAXDOP 1)
SET STATISTICS IO OFF
Originalmente a consulta faz as seguintes leituras.
STATISTICS IO:
Table ‘ItensPed’. Scan count 0, logical reads 19840, physical reads 1, read-ahead reads 11900
Table ‘Pedidos’. Scan count 1, logical reads 230, physical reads 1, read-ahead reads 221
Table ‘Worktable’. Scan count 1, logical reads 3818, physical reads 0, read-ahead reads 0
Table ‘Produtos’. Scan count 1, logical reads 2283, physical reads 3, read-ahead reads 2270
Profiler:
![clip_image002[4]](https://mcflyamorim.files.wordpress.com/2012/10/clip_image0024.jpg "clip_image002[4]")
Vamos focar em melhorar o tempo, mas principalmente o número de leituras de páginas que são executadas nas tabelas.
Eu consegui fechar com os seguintes números:
STATISTICS IO:
Table ‘…’. Scan count 3, logical reads 9, physical reads 4, read-ahead reads 0
Table ‘…’. Scan count 1, logical reads 9, physical reads 1, read-ahead reads 7
Table ‘…’. Scan count 1, logical reads 3, physical reads 3, read-ahead reads 0
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0
Table ‘ItensPed’. Scan count 1, logical reads 652, physical reads 2, read-ahead reads 654
Table ‘Produtos’. Scan count 0, logical reads 6, physical reads 3, read-ahead reads 0
Table ‘Pedidos’. Scan count 1, logical reads 8, physical reads 1, read-ahead reads 6
Profiler:
![clip_image004[4]](https://mcflyamorim.files.wordpress.com/2012/10/clip_image0044.jpg "clip_image004[4]")
/*
Regras
* Não pode mudar índice cluster
* Não pode mudar schema das tabelas existentes (fks, datatype, nullable…)
* Pode criar quantos índices forem necessários (exceto cluster)
* Vale usar view indexadas
* Vale criar novos objetos (procedures, views, triggers, functions…)
* Vale reescrever a consulta
* Pelo menos 5 caracteres obrigatoriamente são utilizados para fazer o filtro pela descrição do produto
* Valores utilizados como filtro não são fixos… ou seja, tem que funcionar para qualquer valor que for solicitado
*/
Segue script para criar e popular as tabelas:
USE tempdb
GO
IF OBJECT_ID(‘Produtos’) IS NOT NULL
DROP TABLE Produtos
GO
CREATE TABLE Produtos (ID_Produto Int IDENTITY(1,1) PRIMARY KEY,
Descricao VarChar(400),
Col1 VarChar(400) DEFAULT NEWID())
GO
INSERT INTO Produtos (Descricao)
VALUES (‘Bicicleta’), (‘Carro’), (‘Motocicleta’), (‘Trator’)
GO
;WITH CTE_1
AS
(
SELECT TOP 200000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
FROM sysobjects a, sysobjects b, sysobjects c, sysobjects d
)
INSERT INTO Produtos (Descricao)
SELECT REPLACE(NEWID(), ‘-‘, ‘ ‘)
FROM CTE_1
GO
–SELECT * FROM Produtos
–GO
IF OBJECT_ID(‘ItensPed’) IS NOT NULL
DROP TABLE ItensPed
GO
IF OBJECT_ID(‘Pedidos’) IS NOT NULL
DROP TABLE Pedidos
GO
CREATE TABLE Pedidos (NumeroPedido VarChar(80) PRIMARY KEY,
DT_Pedido Date,
ID_Cliente Int,
Valor Float)
GO
;WITH CTE_1
AS
(
SELECT TOP 50000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
FROM sysobjects a, sysobjects b, sysobjects c, sysobjects d
)
INSERT INTO Pedidos(NumeroPedido, DT_Pedido, ID_Cliente, Valor)
SELECT ‘Ped-‘ + CONVERT(VarChar, rn), — Composto por "Ped + NumeroSequencial"
GetDate() – ABS(CheckSum(NEWID()) / 10000000),
ABS(CHECKSUM(NEWID())) / 1000000 AS ID_Cliente,
ABS(CHECKSUM(NEWID())) / 100000. AS Valor
FROM CTE_1
GO
–SELECT * FROM Pedidos
–GO
IF OBJECT_ID(‘ItensPed’) IS NOT NULL
DROP TABLE ItensPed
GO
CREATE TABLE ItensPed (NumeroPedido VarChar(80) FOREIGN KEY REFERENCES Pedidos(NumeroPedido),
DT_Entrega Date,
ID_Produto Int,
Qtde SmallInt,
PRIMARY KEY(NumeroPedido, ID_Produto) WITH(IGNORE_DUP_KEY=ON))
GO
INSERT INTO ItensPed(NumeroPedido, DT_Entrega, ID_Produto, Qtde)
SELECT NumeroPedido,
DATEADD(d, ABS(CheckSum(NEWID()) / 100000000), DT_Pedido),
ABS(CHECKSUM(NEWID())) / 1000000 + 1 AS ID_Produto,
ABS(CHECKSUM(NEWID())) / 1000000. AS Qtde
FROM Pedidos
GO 100
–SELECT * FROM ItensPed
–GO
Olhos atentos já viram que dei uma dica… Divirta-se e boa sorte!
Abs.
Reposta–Desafio Performance
Galera vamos lá, resposta para o desafio de performance que postei a alguns dias atrás.
Primeiramente eu quero agradecer a todos que participaram e me enviaram sugestões de códigos com melhorias para a consulta.
· Alberto Lima – Microsoft Brasil
· Rodrigo Souza (twitter) – Microsoft Brasil
· Omid Afzalalghom – Itaú BBA
· Thiago Alencar (twitter|blog) – DBA Saraiva
· Alex Rosa (blog) – IBM
· Alan Victor – CNP
· Evandro Camara – Sight Business Intelligence
Se eu me esqueci de você é só me xingar aqui com um comentário que atualizo o post J… Valeu mesmo por terem participado. Se você tentou, mas não me mandou sua solução… mande, mesmo que só para dizer, ei, eu fiz assim… o que acha?
Como divulgado no post original, o Evandro conseguiu uma solução sensacional usando uma view indexada para evitar o acesso à tabela com milhões de linhas. O código da view é o seguinte:
USE tempdb
GO
IF OBJECT_ID(‘vw_OrdersBig_TestPerf’) IS NOT NULL
DROP VIEW vw_OrdersBig_TestPerf
GO
CREATE VIEW vw_OrdersBig_TestPerf
WITH SCHEMABINDING
AS
SELECT CustomerID,
CountCol,
OrderDate,
SUM_Value = SUM(Value),
COUNT_BIG = COUNT_BIG(*)
FROM dbo.OrdersBig
GROUP BY CustomerID,
CountCol,
OrderDate
GO
CREATE UNIQUE CLUSTERED INDEX PK_vw_OrdersBig_TestPerf ON dbo.vw_OrdersBig_TestPerf (CustomerID, CountCol, OrderDate)
GO
Existem duas cosias bem interessantes na solução do Evandro, primeiro, eu já falei várias vezes que views indexadas são espetaculares para melhoria de performance de consultas, principalmente para consultas com agregações, quem já fez treinamento comigo sabe disso. Sim ela tem um custo, e muitas vezes, alto, mas dependendo do cenário, ela pode sim ser utilizada. Outro fato interessante é que o otimizador de consultas trabalha muito bem para identificar que existe uma view indexada que contém os dados desejados pela consulta… como assim?… Olha só, mesmo rodando a query original, o SQL Server gera o seguinte plano de execução:
Como podemos observar no plano acima, mesmo não fazendo select na view o SQL Server utiliza o índice da view indexada para evitar acessar a tabela com 5 milhões de linhas, com isso ele lê bem menos dados, sendo mais preciso ele lê uma “tabela” (view indexada) com apenas 975 páginas contra 35921 páginas da tabela OrdersBig. Só com isso já temos um ganho muito grande.
Novamente, Parabéns Evandro.
O ganhador da solução sem utilizar view indexada, foi o Alberto Lima, mas antes de falar sobre a solução dele, vamos analisar a minha solução e ver alguns pontos importantes sobre a consulta original:
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)
Existem 3 problemas na consulta acima.
1. A clausula COUNT(DISTINCT 1) não faz nenhum sentido
2. O CASE com a subquery faz com que o SQL execute a subquery para cada valor analisado no case.
3. A clausula COUNT(DISTINCT a.OrderDate) é o grande problema de performance da consulta
O plano pode ser dividido em duas partes, primeiro para calcular o COUNT + COUNT(DISTINCT)
E depois a parte de CASE +SubQuery:
Vamos resolver os problemas por partes, primeiro eliminando um passo do plano trocando o “COUNT(DISTINCT 1)” por “1”. Concordam que “COUNT(DISTINCT 1)” é sempre igual a “1”? O mais irritante é que o otimizador de consultas não identifica isso sozinho.
Outra alteração que podemos fazer é em relação ao CASE + SubQuery, uma forma muito simples de resolver este problema é não usar a subquery como expressão para o CASE, ou seja, trocamos isso:
…
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
…
Por isso:
…
END AS Cnt,
(SELECT CASE AVG(b.Value)
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 b
WHERE b.CustomerID = a.CustomerID) AS Sts
FROM OrdersBig AS a
…
Utilizando o AVG(b.Value) como expressão para o CASE evitamos o problema de execução da subquery para cada valor na lista do CASE.
Após efetuar estas duas alterações temos o seguinte plano de execução:
SELECT a.CustomerID,
a.CountCol,
CASE a.CountCol
WHEN ‘Count’ THEN COUNT(1)
WHEN ‘CountDistinct’ THEN COUNT(DISTINCT a.OrderDate)
WHEN ‘CountDistinct_1’ THEN 1
ELSE NULL
END AS Cnt,
(SELECT CASE AVG(b.Value)
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 b
WHERE b.CustomerID = a.CustomerID) AS Sts
FROM OrdersBig a
GROUP BY a.CustomerID, a.CountCol
ORDER BY a.CustomerID
OPTION (MAXDOP 1)
Uau, já ficou MUITO mais simples não é? O problema agora é que estou acessando fazendo um scan na tabela OrdersBig 3 vezes.
Outro problema que nos resta, é o “COUNT (DISTINCT OrderDate)”, para resolver este problema eu mudei um pouco a forma de solicitar esta informação, ou invés de usar o COUNT DISTINCT eu usei a ROW_NUMBER particionando a janela por CustomerID e CountCol e depois contei a quantidade de valores igual a 1.
Vamos criar um cenário mais simples para entender o conceito:
IF OBJECT_ID(‘Tab1’) IS NOT NULL
DROP TABLE Tab1
GO
CREATE TABLE Tab1 (Col1 Int, Col2 Int)
GO
INSERT INTO Tab1 VALUES(1, 1), (1, 1), (1, 1), (2, 1), (2, 1), (3, 1), (3, 1)
GO
SELECT Col1,
COUNT(Col2) AS "Count", — Palavra reservada
COUNT(DISTINCT Col2) AS CountDistict
FROM Tab1
GROUP BY Col1
GO
Vamos analisar o conceito que mencionei acima passo a passo, primeiro vamos gerar o ROW_NUMBER particionando por Col1 com base na ordem de Col2.
SELECT *,
ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2) AS rn
FROM Tab1
Agora se eu contar a quantidade de ocorrências de “1” agrupando por Col1 terei o resultado esperado concordam? … Para fazer isso vamos colocar a consulta em uma CTE e fazer um CASE para retornar apenas os valores igual a 1.
WITH CTE_1
AS
(
SELECT *,
CASE
WHEN ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2) = 1 THEN 1
ELSE NULL
END AS rn
FROM Tab1
)
SELECT *
FROM CTE_1
Agora podemos simplesmente fazer um COUNT na coluna RN que os valores NULL serão ignorados e teremos o mesmo resultado que o COUNT DISTINCT. Vejamos:
WITH CTE_1
AS
(
SELECT *,
CASE
WHEN ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2) = 1 THEN 1
ELSE NULL
END AS rn
FROM Tab1
)
SELECT Col1,
COUNT(Col2) AS "Count", — Palavra reservada
COUNT(rn) AS CountDistict
FROM CTE_1
GROUP BY Col1
Agora conseguimos resolver a consulta com apenas um scan na tabela, porém agora temos um novo problema, o SORT por Col1 (Clausula PARTITION BY) + Col2 (Clausula ORDER BY), mas esse é fácil de resolver certo? … Basta criar um índice por Col1 e Col2. Bora fazer isso.
CREATE INDEX ix1 ON Tab1 (Col1, Col2)
Agora temos o seguinte plano:
Nice and clean!
Voltando para nosso cenário, a consulta ficaria assim:
— Criando o índice para evitar o Sort e para cobrir a SubQuery
CREATE INDEX ix1 ON OrdersBig (CustomerID, CountCol, OrderDate) INCLUDE(Value) WITH(DATA_COMPRESSION=PAGE)
GO
;WITH CTE_1
AS
(
SELECT CustomerID,
CountCol,
OrderDate,
CASE
WHEN ROW_NUMBER() OVER(PARTITION BY CustomerID, CountCol, OrderDate ORDER BY OrderDate) = 1 THEN 1
ELSE NULL
END AS DistinctCnt
FROM OrdersBig
)
SELECT CustomerID,
CountCol,
CASE CountCol
WHEN ‘Count’ THEN COUNT(1)
WHEN ‘CountDistinct’ THEN COUNT(DistinctCnt)
WHEN ‘CountDistinct_1’ THEN 1
ELSE NULL
END AS Cnt,
(SELECT CASE AVG(b.Value)
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 b
WHERE b.CustomerID = CTE_1.CustomerID) AS Sts
FROM CTE_1
GROUP BY CustomerID, CountCol
ORDER BY CustomerID
OPTION (MAXDOP 1)
Temos o seguinte plano:
Olhos atentos devem ter reparado que eu criei o índice utilizando a clausula “DATA_COMPRESSION = PAGE”, isso faz muito diferença na leitura do índice, já que terei que varrer a tabela ;-).
Outro ponto importantíssimo em relação a performance desta consulta é que o mesmo índice esta sendo utilizado duas vezes, primeiro um Index Scan é realizado já que esta é a primeira vez que os dados estão sendo lidos essa será uma leitura física, quando o Index Seek for realizado os dados já estarão em Cache gerando leituras lógicas. Isso significa que ainda que eu crie outro índice menor (por CustomerID com INCLUDE de Value), a performance da consulta será pior, pois o seek neste novo índice geraria leituras físicas.
Na minha máquina a consulta acima faz o seguinte uso de recursos:
Pra deixar o desafio mais interessante, lanço uma pergunta. Será que da pra fazer isso tudo, com apenas UMA leitura na tabela OrdersBig?
Dá, mas o SQL Server infelizmente ainda não é bem esperto na criação deste plano… Eu poderia evitar a SubQuery do AVG e escrever a consulta assim:
;WITH CTE_1
AS
(
SELECT CustomerID,
CountCol,
OrderDate,
AVG(Value) OVER(PARTITION BY CustomerID) AS Media,
CASE
WHEN ROW_NUMBER() OVER(PARTITION BY CustomerID, CountCol, OrderDate ORDER BY OrderDate) = 1 THEN 1
ELSE NULL
END AS DistinctCnt
FROM OrdersBig
)
SELECT CustomerID,
CountCol,
CASE CountCol
WHEN ‘Count’ THEN COUNT(1)
WHEN ‘CountDistinct’ THEN COUNT(DistinctCnt)
WHEN ‘CountDistinct_1’ THEN 1
ELSE NULL
END AS Cnt,
CASE Media
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 CTE_1
GROUP BY CustomerID, CountCol, Media
ORDER BY CustomerID
OPTION (MAXDOP 1)
Infelizmente a operação de SORT é totalmente desnecessária, mas o SQL Server continua gerando o SORT… isso é um BUG que eu já reclamei, e que foi fechado pela Micosoft como “By Design”… anyway, não vou entrar no mérito aqui se isso é bug ou não é… o que espero é que em novas versões do produto a MS de mais atenção para esse tipo de funcionalidade.
A solução ganhadora (do Alberto Lima), é bem interessante porque usa índices filtrados, segue o script completo:
CREATE TABLE dbo.Tmp_OrdersBig
(
[OrderID] [int] IDENTITY(1,1) PRIMARY KEY NONCLUSTERED NOT NULL,
[CustomerID] [int] NULL,
[OrderDate] [date] NULL,
[Value] [numeric](18, 2) NOT NULL,
[CountCol] [varchar](20) NULL,
) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_OrdersBig SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT TMP_ORDERSBIG ON
go
IF EXISTS(SELECT * FROM dbo.OrdersBig)
EXEC(‘INSERT INTO dbo.Tmp_OrdersBig (OrderID, CustomerID, OrderDate, Value, CountCol)
SELECT OrderID, CustomerID, OrderDate, Value, CONVERT(char(15), CountCol) FROM dbo.OrdersBig WITH (HOLDLOCK TABLOCKX)’)
GO
SET IDENTITY_INSERT TMP_ORDERSBIG OFF
GO
DROP TABLE dbo.OrdersBig
GO
EXECUTE sp_rename N’dbo.Tmp_OrdersBig’, N’OrdersBig’, ‘OBJECT’
GO
CREATE CLUSTERED INDEX IX_OrdersBigClustered ON dbo.OrdersBig
(
CustomerID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix_orders_big_CountCol_CountCol_customerid_CountDistinct] ON [dbo].[OrdersBig]
(
[CountCol] ASC,
[CustomerID] ASC,
[OrderDate] ASC
)
WHERE ([CountCol]=‘CountDistinct’)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_orders_big_Countcol_Customerid_Value] ON [dbo].[OrdersBig]
(
[CountCol] ASC,
[CustomerID] ASC,
[Value] ASC
)WITH (DATA_COMPRESSION=PAGE, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
USE [tempdb]
GO
CREATE NONCLUSTERED INDEX [IX_orders_big_CountCol_Customerid_Count] ON [dbo].[OrdersBig]
(
[CountCol] ASC,
[CustomerID] ASC
)
WHERE ([CountCol]=‘Count’)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Script da consulta:
WITH CTE_Customers ( CountCust, CustomerID, CountCol, Value )
AS ( SELECT CountCust = ( CASE WHEN CountCol = ‘Count’
THEN ( SELECT COUNT(1)
FROM OrdersBig e
WHERE CountCol = ‘Count’ and
e.CustomerID = a.CustomerID
)
WHEN CountCol = ‘CountDistinct’
THEN ( SELECT COUNT(DISTINCT d.OrderDate)
FROM OrdersBig d
WHERE CountCol = ‘CountDistinct’ and
d.CustomerID = a.CustomerID
)
WHEN CountCol = ‘CountDistinct_1’
THEN ( SELECT COUNT(DISTINCT 1)
FROM OrdersBig C
WHERE CountCol = ‘CountDistinct_1’ and
c.CustomerID = a.CustomerID
)
ELSE NULL
END ),
CustomerID,
CountCol,
AVG(VALUE) AS Value
FROM OrdersBig AS A
GROUP BY Customerid,
CountCol
)
SELECT a.CustomerID,
a.CountCol,
CountCust as Cnt,
( CASE VALUE
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 )
FROM CTE_Customers a
ORDER BY a.CustomerID
OPTION ( MAXDOP 1 );
O plano é o seguinte:
A consulta roda em 4 segundos, e utiliza os seguintes recursos:
Apesar da consulta do Alberto fazer mais leituras de páginas que a minha solução, o tempo foi menor e isso é o que importa.
Eu gostaria muito de saber qual o tempo da minha consulta e da consulta do Alberto na sua máquina, pode testar e postar o resultado em um comentário aqui no blog?
É isso ai galera, espero que tenham gostado… e fiquem de olho que já tenho outro desafio de performance pronto para ser publicado 🙂
Abs.
Aviso – Resposta desafio de performance
Galera só um lembrete,
Semana que vem vou postar o resultado ok?… Quem está trabalhando na solução corre que ta acabando o tempo. 
Se você não sabe do que estou falando clique aqui.
Abs.
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.
Solução desafio de segurança no SQL Server
Primeiramente quero agradecer a todos que se interessaram pelo desafio e perderam algum tempo tentando chegar na solução 
. Eu gostei tanto que já tenho outro desafio preparado para publicação, mas vamos por partes…
Se você não sabe do que estou falando veja o desafio aqui.
A única pessoa que conseguiu responder o desafio mandou uma solução MUITO MELHOR que a minha hahuahuuh… não era por menos, quem respondeu o desafio foi o grande (modo de dizer) Fabricio Catae (blog|twitter) o ninja!
Parabéns Catae você é o cara! No próximo desafio vou colocar um aviso do tipo “Aviso: Ninjas estão automaticamente desclassificados! huahuahua”
Vamos analisar primeiro a minha solução, e depois veremos a do Catae.
Solução 1:
Minha solução consiste em trabalhar com as mensagens de erro do SQL Server para ver as informações que supostamente não deveríamos saber que existe. Isso é muito utilizado em SQL Injection, ou seja, se você não trata as mensagens de erro que envia para sua aplicação, tá muito errado, cuidado.
Primeiramente vejamos o plano de execução do select na view: (pra quem tá chorando nessa hora dizendo, aaaa mas não vale ver o plano e tals… não Sr. eu disse que vale tudo, ou seja, você conhece as tabelas, pode ver o plano logado com um usuário que te permissão para isso, mas para “ver” os dados que não poderíamos ver você precisa estar logado com o usuarioquenaopodenada)
![clip_image001[4]](https://mcflyamorim.files.wordpress.com/2012/09/clip_image00142.png?w=578&h=239 "clip_image001[4]")
Vamos executar este plano passo a passo:
-
Executa o Clustered Index Scan na tabela TabFuncionarios, ou seja, vai fazer um SCAN na tabela, pra quem ainda não entendeu, isso significa que o SQL vai passar por TODAS as linhas da tabela. É importante que você entenda isso.
-
Nested Loops trabalhando como Anti Semi Join (left anti semi = not exists, right anti semi = exists). Um Anti Semi Join é um Join que retorna dados de apenas um dos lados do join, ou seja, neste caso apenas colunas da tabela TabFuncionarios serão retornadas.
-
Para cada linha recebida pelo Scan na TabFuncionarios, o Nested Loops chama o Clustered Index Scan na tabela TabEsconderFuncionarios.
-
Clustered Index Scan na tabela TabEsconderFuncionarios retorna as linhas da tabela TabEsconderFuncionarios para o Nested Loops para ver quem faz o join, e consequentemente eliminar as linhas que não queremos exibir (validação do not exisits).
Bom, agora que sabemos que a PRIMEIRA COISA que o plano faz é varrer a tabela TabFuncionarios, podemos tentar aplicar um filtro nesta tabela para tentar gerar um erro de conversão… Por exemplo:
O que acontece se eu rodar o seguinte comando?
SELECT *
FROM vw_TabFuncionarios
WHERE CONVERT(Int, Nome) = 0
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘Fabiano’ to data type int.
Plano da consulta:
![clip_image002[4]](https://mcflyamorim.files.wordpress.com/2012/09/clip_image00241.png?w=797&h=258 "clip_image002[4]")
O que acontece é que o SQL tenta converter a coluna Nome para Int e gera um erro de conversão. Ótimo, estamos chegando perto…
Repare que no plano de execução da consulta acima o SQL Server executa (Predicate) o CONVERT(Int, Nome) = 0 antes de fazer o join, ou seja, o SQL vai tentar fazer a conversão para TODAS as linhas da tabela, no caso acima na primeira linha lida o erro já é exibido, porém podemos incluir mais um filtro no valor que já conhecemos para ignorar o “Fabiano” por exemplo:
SELECT *
FROM vw_TabFuncionarios
WHERE CONVERT(Int, Nome) = 0
AND Nome NOT IN (‘Fabiano’)
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘Gilberto’ to data type int.
Plano da consulta:
![clip_image003[4]](https://mcflyamorim.files.wordpress.com/2012/09/clip_image00341.png?w=799&h=249 "clip_image003[4]")
Repare que agora a conversão deu erro no Gilberto, vamos inclui-lo na lista do IN:
SELECT *
FROM vw_TabFuncionarios
WHERE CONVERT(Int, Nome) = 0
AND Nome NOT IN (‘Fabiano’, ‘Gilberto’)
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘Luciano’ to data type int.
Já conseguimos visualizar que existe um funcionário chamado Luciano 
, e pra saber qual é o salario dele? … Basta concatenar a coluna salario com o Nome e tentar converter tudo para Int…
SELECT *
FROM vw_TabFuncionarios
WHERE CONVERT(Int, Nome + CONVERT(VarChar(30), Salario)) = 0
AND Nome NOT IN (‘Fabiano’, ‘Gilberto’)
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘Luciano25999.00’ to data type int.
Feito, conseguimos ver que o Luciano ganha R$25.999,00 por mês, tai o motivo dele estar na tabela EsconderFuncionarios J. Para ver quanto o Ivan ganha, basta incluir o Luciano na lista do IN…
Tem uma coisa importante que talvez você não tenha notado… Repare que para que isso tudo funcione o SQL Server primeiro tem que fazer a validação do NOT IN, e depois fazer a validação da conversão do nome para int = 0. Ou seja,
No WHERE, ele tem que executar primeiro isso “NomeNOTIN(‘Fabiano’,‘Gilberto’)” e depois “CONVERT(Int,Nome)= 0”, veja que ainda que nós tenhamos escrito o código especificando primeiro o CONVERT e depois o NOT IN, o SQL Server executou o NOT IN antes, vejamos isso no plano:
![clip_image004[4]](https://mcflyamorim.files.wordpress.com/2012/09/clip_image00441.png?w=597&h=167 "clip_image004[4]")
O otimizador de consultas que decide quem ele deve executar primeiro, leia mais sobre isso neste post do Itzik. Ok, mas dai vem a pergunta, como forçar a ordem de execução das expressões e ter certeza de que nossa solução vai funcionar independente da versão do SQL Server que estou usando? (No SQL Server 2000 a consulta acima não funciona).
Para forçar a ordem de execução das expressões e evitar (ou forçar) um erro devemos usar o CASE, portanto a consulta final ficaria assim:
— Rodar a consulta no escopo do UsuarioQueNaoPodeNada
EXEC AS USER = ‘UsuarioQueNaoPodeNada’
GO
— Quanto o Luciano ganha?
SELECT *
FROM vw_TabFuncionarios
WHERE (CASE
WHEN (Nome NOT IN (‘Fabiano’, ‘Gilberto’)) AND (CONVERT(Int, Nome + CONVERT(VarChar, Salario) ) = 0) THEN 0
ELSE 1
END) = 0
Solução 2 (by Fabrício Catae a.k.a. Ninja):
A solução do Catae é sensacional porque ela não retorna os dados utilizando as mensagens de erro do SQL. A ideia é simples (depois eu você vê é claro), ela consiste em gerar um lock exclusivo nas linhas da tabela TabEsconderFuncionarios e depois ler a view utilizando o hint ReadPast.
Pra quem não conhecia, o hint READPAST é similar ao famoso NOLOCK, a ideia é não ficar bloqueado por uma sessão que está com lock exclusivo nas linhas que você esta tentando ler. Porém diferentemente do NOLOCK (quer permite leitura suja para não ficar bloqueado) o READPAST “pula” as linhas que estão com lock por linha (KEY LOCK). Voltando ao nosso cenário, se as linhas da tabela TabEsconderFuncionarios estiverem bloqueadas com xLock (lock exclusivo) e RowLock (lock por linha) ao ler os dados da view, o SQL Server “entende” que não existe nada na tabela e consequentemente retorna tudo.
Vejamos na prática:
— Sessão 1
BEGIN TRAN
— Gerar lock exclusivo em tudo o que lê, ou seja,
— nas linhas Fabiano e Gilberto da TabFuncionarios
— e nas linhas ID 3 e 4 da TabEsconderFuncionarios
SELECT * FROM vw_TabFuncionarios WITH (xlock, rowlock)
WHERE Nome IN (‘Fabiano’, ‘Gilberto’)
GO
Em uma sessão eu inicio uma transação, e leio a view utilizando dois hints, o xlock para forçar a o lock exclusivo, e o rowlock para forçar o lock por linha (lembre-se o READPAST só “ignora” locks de linha).
Podemos consultar os locks gerado pela consulta na view utilizando o seguinte código:
— Sessão 1
— Consultar locks
— Gerou os locks esperados
SELECT OBJECT_NAME(partitions.OBJECT_ID) AS ObjectName,
dm_tran_locks.resource_type,
dm_tran_locks.resource_description,
dm_tran_locks.request_mode
FROM sys.dm_tran_locks
LEFT OUTER JOIN sys.partitions
ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
WHERE request_session_id = @@spid
AND resource_type = ‘KEY’
ORDER BY 1
![clip_image005[4]](https://mcflyamorim.files.wordpress.com/2012/09/clip_image00541.png?w=467&h=103 "clip_image005[4]")
Conforme podemos observar o select na view gerou 2 locks na tabela TabFuncionarios (para os IDs 1 e 2, repare no where por nome) e 2 locks na tabela TabEsconderFuncionarios (nos IDs 3 e 4)
Se eu abrir outra sessão e tentar rodar um select na vw_TabFuncionarios eu vou ficar bloqueado pro causa dos locks exclusivos gerados na sessão 1, porém se eu rodar o código com o hint READPAST, o SQL vai ler as linhas de IDs 3 e 4 da tabela TabFuncionarios e ao tentar fazer o join com a TabEsconderFuncionarios ele não vai encontrar nada lá (porque ele “pula” as linhas bloqueadas), e consequentemente retorna os dados… Vejamos:
— Sessão 2
— Abrir outra sessão e ler os dados da view com READPAST hint
— Como os IDs da TabEsconderFuncionarios estão com lock na linha (importante que lock de linha)
— ao fazer o join para ver quem NOT EXISTS o SQL não ve nada, ou seja, posso ver todas as linhas
SELECT * FROM vw_TabFuncionarios WITH(readpast)
![clip_image006[4]](https://mcflyamorim.files.wordpress.com/2012/09/clip_image00641.png?w=185&h=92 "clip_image006[4]")
Conforme podemos observar, conseguimos visualizar todos os dados da tabela.
É importante que o SQL Server gere os locks POR LINHA, se na sessão 1 o SQL Server gerar os locks por página ou tabela, esquece que não vai funcionar…
Se você esta se perguntando, como evitar esse comportamento… a resposta é… você poderia criar view com with(nolock) nas tabelas e dai ao tentar ler a view utilizando o READPAST o SQL Server ira gerar a seguinte mensagem de erro:
Msg 650, Level 16, State 1, Line 5
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.
Bom, é isso, espero que tenham gostado, quer mais? … J
Fique de olho que na semana que vem vou publicar outro desafio, desta vez mais simples, porém relacionado a performance.
Abs.
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 