T-SQL | Blog - Fabiano Neves Amorim

Arquivo

Archive for the ‘T-SQL’ Category

Video com 6 horas de treinamento tuning gratuito…

27 de fevereiro de 2014 Fabiano Amorim 18 comentários

Fala galera, depois de um tempo sumido, cá estou…

No ano passado eu ministrei um treinamento in-company e o cliente fez a gentileza de gravar e disponibilizar os vídeos do treinamento, inclusive para o público J.

Acabei de subir pro youtube, são 16 vídeos com aproximadamente 6 horas de treinamento onde falei sobre os seguinte assuntos:

Porque pensar set-based é tão difícil?
— Query tuning
Overview plano de execução
Profiler, STATISTICS IO/CPU
Triggers
Dicas sobre scalar functions
Common table expressions
Cursores e windows functions
TOP e CROSS APPLY

Espero que vocês gostem e que seja útil. Seguem os links para acessar os vídeos:

Abs.

Fabiano Amorim

Categorias:T-SQL, Treinamento, Tuning, Video, Virtual PASS BR

TSQL Expert– SP, RJ e BSB

2 de julho de 2013 Fabiano Amorim 4 comentários

Galera, acabei de colocar no site da Sr.Nimbus o calendário para nosso treinamento de T-SQL expert.

Seguem mais detalhes:

· Rio de Janeiro

o SQL11 T-SQL Expert

o Carga horária: 24 horas

o Treinamento no período integral aos sábados das 09:00 as 18:00 nos dias 12, 19 e 26 de Outubro de 2013.

o Local: A definir

o Valor: R$1.550,00 reais parcelado em até 12x via pagseguro, ou R$1.450,00 a vista via depósito bancário.

o Link para inscrição: http://www.srnimbus.com.br/calendario/sql11-rj-outubro-2013/

· São Paulo

o SQL11 T-SQL Expert

o Carga horária: 24 horas

o Treinamento no período integral aos sábados das 09:00 as 18:00 nos dias 3, 10 e 17 de Agosto de 2013.

o Local: Proximidades da avenida paulista

o Valor: R$1.450,00 reais parcelado em até 12x via pagseguro, ou R$1.350,00 a vista via depósito bancário.

o Link para inscrição: http://www.srnimbus.com.br/calendario/sql11-sp-agosto-2013/

· Brasília

o SQL11 T-SQL Expert

o Carga horária: 24 horas

o Treinamento no período integral aos sábados das 09:00 as 18:00 nos dias 7, 14 e 21 de Setembro de 2013.

o Local: Proximidades da avenida paulista

o Valor: R$1.650,00 reais parcelado em até 12x via pagseguro, ou R$1.550,00 a vista via depósito bancário.

o Link para inscrição: http://www.srnimbus.com.br/calendario/sql11-bsb-setembro-2013/

Categorias:Sr.Nimbus, T-SQL, Treinamento, Virtual PASS BR

T-SQL Expert em SP aos sábados(11,18 e 25 de Maio)

15 de abril de 2013 Fabiano Amorim 5 comentários

Fala meu povo.

Para nossa alegria, estou fechando um treinamento de T-SQL Expert aos sábados nos dias 11, 18, 25 de Maio (exato, são apenas 3 sábados). Você pode ler mais detalhes em relação a este treinamento aqui no meu blog, ou pela ementa no site da Sr.Nimbus.

Mais detalhes:

Resumo:

http://www.srnimbus.com.br/calendario/sql11-sp-maio-2013/

Ementa: T-SQL Expert, SQL11 (sem o módulo sobre unit tests)
Carga horária: 24 horas
Horário das aulas: 09:00 a 18:00
Datas: 11, 18, 25 de Maio
Valor: R$ 1.500,00 em até 12 vezes via PagSeguro e R$1.400,00 a vista
Aluno precisa levar notebook
Local: Região da Paulista, local definitivo a confirmar.

Caso tenha interesse, basta utilizar o botão de reserva de vaga no link acima.

Abs.

Categorias:Sr.Nimbus, T-SQL, Treinamento

T-SQL Expert online em Março-2013

18 de fevereiro de 2013 Fabiano Amorim 1 comentário

T-SQL Expert online,

Pessoal acabei de abrir uma turma para nosso treinamento de T-SQL Expert. Você pode ler mais detalhes em relação a este treinamento aqui no meu blog, ou pela ementa no site da Sr.Nimbus.

Para se inscrever no treinamento utilize o seguinte link: http://www.srnimbus.com.br/calendario/online-sql11-t-sql-expert/

Resumo:

Ementa: T-SQL Expert, SQL11 (módulo sobre unit tests será removido do treinamento online)
Carga horária: 24 horas
Horário das aulas entre 18:30 e 21:30 (3 horas por dia) seguindo o seguinte cronograma
18, 19, 20 e 21 de março (segunda a quinta-feira)
25, 26, 27 e 28 de março (segunda a quinta-feira)
Valor: R$ 720,00 em até 12 vezes via PagSeguro e R$680,00 a vista

Abs.

Categorias:T-SQL, Treinamento

Video PASS24 hours – Escrevento códigos “like a boss”

11 de janeiro de 2013 Fabiano Amorim 3 comentários

Update: A animação do PPT não ficou legal no vídeo… baixe o PPT para ver melhor!

Galera, o vídeo da minha sessão sobre T-SQL no PASS24 horas está disponível.

http://www.sqlpass.org/LearningCenter/SessionRecordings/24HoursPortuguese2012.aspx

Você pode baixar o material aqui:

http://www.srnimbus.com.br/wp-content/uploads/2012/11/T-SQL-Expert-Escrevendo-c%C3%B3digos-like-a-boss.zip

Abaixo alguns prints da sessão:

Espero que gostem!

Abs.

Categorias:Evento, T-SQL, Video, Virtual PASS BR, WebCast

Novo Desafio SQL Server – Performance 2

17 de outubro de 2012 Fabiano Amorim 1 comentário

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

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:

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:

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

IF OBJECT_ID(‘Produtos’) IS NOT NULL

DROP TABLE Produtos

CREATE TABLE Produtos (ID_Produto Int IDENTITY(1,1) PRIMARY KEY,

Descricao VarChar(400),

Col1 VarChar(400) DEFAULT NEWID())

INSERT INTO Produtos (Descricao)

VALUES (‘Bicicleta’), (‘Carro’), (‘Motocicleta’), (‘Trator’)

;WITH CTE_1

(

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

–SELECT * FROM Produtos

–GO

IF OBJECT_ID(‘ItensPed’) IS NOT NULL

DROP TABLE ItensPed

IF OBJECT_ID(‘Pedidos’) IS NOT NULL

DROP TABLE Pedidos

CREATE TABLE Pedidos (NumeroPedido VarChar(80) PRIMARY KEY,

DT_Pedido Date,

ID_Cliente Int,

Valor Float)

;WITH CTE_1

(

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

–SELECT * FROM Pedidos

–GO

IF OBJECT_ID(‘ItensPed’) IS NOT NULL

DROP TABLE ItensPed

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

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.

Categorias:Desafio, T-SQL, Tuning, Virtual PASS BR

Reposta–Desafio Performance

1 de outubro de 2012 Fabiano Amorim 11 comentários

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

IF OBJECT_ID(‘vw_OrdersBig_TestPerf’) IS NOT NULL

DROP VIEW vw_OrdersBig_TestPerf

CREATE VIEW vw_OrdersBig_TestPerf

WITH SCHEMABINDING

SELECT CustomerID,

CountCol,

OrderDate,

SUM_Value = SUM(Value),

COUNT_BIG = COUNT_BIG(*)

FROM dbo.OrdersBig

GROUP BY CustomerID,

CountCol,

OrderDate

CREATE UNIQUE CLUSTERED INDEX PK_vw_OrdersBig_TestPerf ON dbo.vw_OrdersBig_TestPerf (CustomerID, CountCol, OrderDate)

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

CREATE TABLE Tab1 (Col1 Int, Col2 Int)

INSERT INTO Tab1 VALUES(1, 1), (1, 1), (1, 1), (2, 1), (2, 1), (3, 1), (3, 1)

SELECT Col1,

COUNT(Col2) AS "Count", — Palavra reservada

COUNT(DISTINCT Col2) AS CountDistict

FROM Tab1

GROUP BY Col1

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

(

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

(

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)

;WITH CTE_1

(

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

(

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]

ALTER TABLE dbo.Tmp_OrdersBig SET (LOCK_ESCALATION = TABLE)

SET IDENTITY_INSERT TMP_ORDERSBIG ON

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

SET IDENTITY_INSERT TMP_ORDERSBIG OFF

DROP TABLE dbo.OrdersBig

EXECUTE sp_rename N’dbo.Tmp_OrdersBig’, N’OrdersBig’, ‘OBJECT’

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]

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]

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]

USE [tempdb]

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]

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.

Categorias:Desafio, T-SQL, Tuning, Virtual PASS BR

Pensar set-based, porque é tão difícil?

20 de setembro de 2012 Fabiano Amorim 11 comentários

Cá estou, no avião voltando pra casa depois de mais um trabalho de Tuning no SQL Server, processo que demorava 10 horas agora demora 24 minutos, cliente feliz e eu voltando pra casa pra curtir filho lindo e esposa maravilhosa. #SrNimbusRocks

Estou lendo um livro do Joe Celko chamado “Thinking in Sets” (pensando em sets) e eu gostaria de falar um pouco sobre isso.

Uma das dificuldades mais comuns para desenvolvedores que querem escrever códigos SQL que tenham boa performance, é abandonar o pensamento de linguagens procedurais (onde você diz tudo o que quer fazer) e começar a pensar em códigos declarativos (onde você apenas diz o que quer).

Desde pequenos (no meu caso com 16 anos) aprendemos que em programação devemos fazer tudo passo a passo. Quem lembra de quando seu professor de lógica de programação (se é que existe lógica nessa bagaça) disse: – “Boa noite turma, hoje vamos escrever nosso primeiro código!” e ele continuou dizendo: “- Quero que você pegue uma folha e escreva todos os passos necessários para trocar um pneu, ou fritar um ovo (no meu caso foi a do fritar o ovo)”. Eu tive essa aulinha no curso técnico de processamento de dados, lembro que escrevi algo parecido com:

1. Pegar o ovo

2. Abrir o ovo

3. Colocar na frigideira

4. Fritar o ovo

5. Pegar o ovo da frigideira

6. Colocar no prato

Escrevemos estes passos e achamos que estamos abafando… Dai o professor pega o papel e diz… ué, mas vai pegar o ovo da onde? Tá na geladeira? O ovo tá onde? Dai você diz… aaa … é verdade, vou corrigir me da ai… dai você adiciona:

1. Pegar o ovo da geladeira

2. …

Dai o professor pega a folha e diz… ué, na sua casa você deixa a geladeira aberta?… Dai você diz… caramba (já começando a ficar irritado) é verdade.

1. Abrir a geladeira

2. Pegar o ovo da geladeira

3. …

Dai o professor pega a folha e diz… ué, e se não tiver mais ovo na geladeira? E se a geladeira estiver quebrada e o ovo estragado, e se a geladeira tiver um cadeado? E se você estiver com a mão ocupada com alguma coisa? Como vai pegar o ovo? … Dai você diz… Professor, na boa, vai a merda! Smile with tongue out E aprende a lição! Tem que prever TUDO, e dizer passo a passo o que deve ser feito.

É assim que aprendemos a programar, linha a linha, passo a passo! Porém, quando estamos falando de banco de dados, precisamos pensar que vamos trabalhar com um set, ou seja, um conjunto de linhas (melhor dizendo, um conjunto de elementos). É de responsabilidade do banco de dados decidir qual será a melhor maneira para ler os dados. Ainda que eles serão lidos linha a linha, quem decide a melhor maneira de fazer isso é o banco, não você. Por exemplo, o SQL Server pode optar por fazer uma soma, utilizando um algoritmo de hash, ou um join utilizando um merge e etc…

Lembre-se de que cada instrução (comando SQL) enviada para o banco de dados passa por uma série de validações (parse, binding, optimize…) e isso tem um custo. Por isso cursores e loops são tão ruims em relação a performance. Uma frase sensacional do Celko sobre cursores é a seguinte:

“A melhor técnica que você pode utilizar para melhorar a performance de um cursor é não usar cursor.”

Sarcastic smile Sensacional.

Quando você vê uma tabela chamada População você acha que nela existem populações, mas na verdade existem pessoas. Uma tabela de Floresta não contem florestas contem árvores… Temos que pensar em um todo, não no individuo. Ao invés de criar uma tabela chamada Abelhas para armazenar dados sobre abelhas, crie uma tabela chamada Colmeia! (é sério esse foi o único coletivo que eu conhecia quando estava escrevendo este artigo kkk Smile with tongue out )

Quer exemplos de como nós programadores pensamos em código procedural e não em sets? Vejamos…

Se eu disser, nesta mesa eu tenho 8 caixas de ovos (de galinha e codorna) fechadas e de tamanhos variados, quero que você olhe cada caixa e me entregue todas as caixas que contêm a mesma quantidade de ovos.

O que pensamos em fazer?

· Contar quantos ovos tem em cada caixa, separar as caixas que tem a mesma quantidade e me entregar.

O que seria mais eficiente?

· Olhar a quantidade exibida na embalagem, separar as caixas que tem a mesma quantidade e me entregar.

Reparou na diferença? … Uma solução conta todos os ovos, e a outra usa uma agregação… Uma solução vê os ovos e outra vê as caixas (um todo).

Vejamos outro exemplo que usa o caso clássico das tabelas de Pedidos e Itens de Pedidos.

Se eu disser, quero uma consulta que retorne todos os pedidos em que a quantidade de itens comprados é igual para todos os itens.

Uma alternativa seria escrever uma consulta que varre a tabela de “Itens de Pedidos” e executa uma subquery para verificar se existe algum item de pedido diferente da quantidade do item lido… Algo mais ou menos assim:

select a.CodigoPedido from itens_pedidos a

where not exists (select * from itens_pedidos b

where a.CodigoPedido = b.CodigoPedido

and a.quantidade <> b.quantidade)

Ou seja, se existir algum pedido com quantidade diferente, ele será desconsiderado da consulta. Eu poderia escrever um cursor para responder a consulta, mas não vou fazer isso Smile .

Outra forma mais interessante seria o seguinte:

select CodigoPedido from itens_pedidos

group by CodigoPedido

having min(quantidade) = max(quantidade)

Repararam na diferença?… Eu sei que é mais difícil pensar assim, encontrar soluções deste tipo não é fácil, mas acreditem, é possível.

Estudem e entendam as “window functions” no SQL Server, entendam como usar funções de agregação e evitem cursores, sempre.

Outra coisa interessante que sempre falo, é em relação a formatação de dados via SQL e ordenação.

Não faça formatação no SQL, please, deixe para formatar alguma coisa, na aplicação, banco de dados sofre para incluir uma “mascara” no CNPJ, faça isso na aplicação e não no banco.

Para ordenação eu digo o mesmo. Ordenação é na aplicação e não no banco! Você sabia que a clausula ORDER BY não faz parte do SELECT ? Pois é, no padrão ANSI/ISO da linguagem SQL (structured query language) não existe order by no select, no ISO o order by faz parte da sintaxe de cursores. Os fabricantes de bancos de dados criam uma extensão dessa opção para que você a utilize nos selects. Fica a dica, quase sempre podemos ordenar do lado da aplicação com pouquíssimo trabalho.

Fecho o artigo com um ditado Turco bem interessante:

“Não importa o quão longe você foi por uma estrada errada, volte!”

Abraços e bom código!

Categorias:SQL Server - Performance, T-SQL, Virtual PASS BR

Desafio SQL Server – Performance

17 de setembro de 2012 Fabiano Amorim 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

— Prepara ambiente

— Aproximadamente 2 minutos para rodar

— Tab com 287368 KB

IF OBJECT_ID(‘OrdersBig’) IS NOT NULL

DROP TABLE OrdersBig

CREATE TABLE OrdersBig (OrderID int NOT NULL IDENTITY(1, 1),

CustomerID int NULL,

OrderDate date NULL,

Value numeric (18, 2) NOT NULL)

ALTER TABLE OrdersBig ADD CONSTRAINT xpk_OrdersBig PRIMARY KEY CLUSTERED (OrderID)

— 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

ALTER TABLE OrdersBig ADD CountCol VarChar(20)

UPDATE TOP (50) PERCENT OrdersBig SET CountCol = ‘Count’

WHERE CountCol IS NULL

UPDATE TOP (50) PERCENT OrdersBig SET CountCol = ‘CountDistinct’

WHERE CountCol IS NULL

UPDATE OrdersBig SET CountCol = ‘CountDistinct_1’

WHERE CountCol IS NULL

CHECKPOINT

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

Writes: 100350

Duration (ms): 101522

CHECKPOINT

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

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

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

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.

Categorias:Desafio, T-SQL, Tuning, Virtual PASS BR

Hypothetical indexes, making it easy to use…

31 de maio de 2012 Fabiano Amorim Deixe um comentário

Hi there, yes, I know, it’s being a while since I wrote something here… So, sorry about that.

Yesterday the guy I’m a big fan (if you already join one of my SQL trainings you know that Smile ) Query Optimizer Guru Paul White (blog|twitter) answered a question on twitter (#sqlhelp hash tag) and he mentioned a post I wrote some time ago about hypothetical indexes on SQL Server. That is a very nice feature but unfortunately is not pretty to use because it requires you to use DBCC AUTOPILOT and set AUTOPILOT to ON…

The parameters you have to use are not straightforward to find and may discourage you to use it… I’ve created a procedure to make it a little easier to use.

Originally I created this procedure after a student question about how to make it easier to use hypothetical indexes on SQL Server. So I thought you may like it.

Unfortunately it relies on a CLR stored procedure to SET the AUTOPILOT, but if you don’t mind to use it in a develop environment (which is something normal to do) then you can use it, following is the CLR code, and if you are interested you can download the project code here:

-- CLR Proc
/*
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void CLR_GetAutoPilotShowPlan
    (
         SqlString SQL,
         out SqlXml PlanXML
    )
    {
        //Prep connection
        SqlConnection cn = new SqlConnection("Context Connection = True");

        //Set command texts
        SqlCommand cmd_SetAutoPilotOn = new SqlCommand("SET AUTOPILOT ON", cn);
        SqlCommand cmd_SetAutoPilotOff = new SqlCommand("SET AUTOPILOT OFF", cn);
        SqlCommand cmd_input = new SqlCommand(SQL.ToString(), cn);

        if (cn.State != ConnectionState.Open)
        {
            cn.Open();
        }

        //Run AutoPilot On
        cmd_SetAutoPilotOn.ExecuteNonQuery();

        //Run input SQL
        SqlDataAdapter da = new SqlDataAdapter();
        DataSet ds = new DataSet();

        da.SelectCommand = cmd_input;
        ds.Tables.Add(new DataTable("Results"));

        ds.Tables[0].BeginLoadData();
        da.Fill(ds, "Results");
        ds.Tables[0].EndLoadData();

        //Run AutoPilot Off
        cmd_SetAutoPilotOff.ExecuteNonQuery();

        if (cn.State != ConnectionState.Closed)
        {
            cn.Close();
        }

        //Package XML as output
        System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
        //XML is in 1st Col of 1st Row of 1st Table
        xmlDoc.InnerXml = ds.Tables[0].Rows[0][0].ToString();
        System.Xml.XmlNodeReader xnr = new System.Xml.XmlNodeReader(xmlDoc);
        PlanXML = new SqlXml(xnr);
    }
};
*/

Following is the code to compile it on SQL Server and to create another procedure to simulate the hypothetical indexes:

-- Publishing Assembly
IF EXISTS(SELECT * FROM sys.assemblies WHERE name = 'CLR_ProjectAutoPilot')
BEGIN
  IF OBJECT_ID('st_CLR_GetAutoPilotShowPlan') IS NOT NULL
    DROP PROC st_CLR_GetAutoPilotShowPlan

  DROP ASSEMBLY CLR_ProjectAutoPilot
END
GO
CREATE ASSEMBLY CLR_ProjectAutoPilot FROM 'C:\ProjectAutoPilot\ProjectAutoPilot\bin\Release\ProjectAutoPilot.dll' WITH PERMISSION_SET = SAFE
GO

CREATE PROCEDURE st_CLR_GetAutoPilotShowPlan (@Query NVarChar(MAX), @ShowPlan XML OUTPUT)
AS
  EXTERNAL NAME CLR_ProjectAutoPilot.StoredProcedures.CLR_GetAutoPilotShowPlan
GO

IF OBJECT_ID('st_TestHipotheticalIndexes', 'p') IS NOT NULL
  DROP PROC dbo.st_TestHipotheticalIndexes
GO
CREATE PROCEDURE dbo.st_TestHipotheticalIndexes (@SQLIndex NVarChar(MAX), @Query NVarChar(MAX))
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRY
    BEGIN TRAN
    DECLARE @CreateIndexCommand NVarChar(MAX),
            @IndexName NVarChar(MAX),
            @TableName NVarChar(MAX),
            @SQLIndexTMP NVarChar(MAX),
            @SQLDropIndex NVarChar(MAX),
            @SQLDbccAutoPilot NVarChar(MAX),
            @i Int,
            @QuantityIndex Int,
            @Xml XML

    IF SubString(@SQLIndex, LEN(@SQLIndex), 1) <> ';'
    BEGIN
      RAISERROR ('Last caracter in the index should be ;', -- Message text.
                 16, -- Severity.
                 1 -- State.
                 );
    END

    SET @SQLDropIndex = '';
    SET @QuantityIndex = LEN(@SQLIndex) - LEN(REPLACE(@SQLIndex, ';', ''))
    SELECT @SQLIndexTMP = SUBSTRING(@SQLIndex, 0, CharIndex(';', @SQLIndex))

    SET @i = 0
    WHILE @i < @QuantityIndex
    BEGIN
      SET @SQLIndexTMP = SUBSTRING(@SQLIndex, 0, CharIndex(';', @SQLIndex))
      SET @CreateIndexCommand = SUBSTRING(@SQLIndexTMP, 0, CharIndex(' ON ',@SQLIndexTMP))
      SET @IndexName = REVERSE(SubString(REVERSE(@CreateIndexCommand), 0, CharIndex(' ', REVERSE(@CreateIndexCommand))))
      SET @TableName = SUBSTRING(REPLACE(@SQLIndexTMP, @CreateIndexCommand + ' ON ', ''), 0, CharIndex(' ', REPLACE(@SQLIndexTMP, @CreateIndexCommand + ' ON ', '')))
      IF ISNULL(@TableName,'') = ''
        SET @TableName = SUBSTRING(REPLACE(@SQLIndexTMP, @CreateIndexCommand + ' ON ', ''), 0, CharIndex('(', REPLACE(@SQLIndexTMP, @CreateIndexCommand + ' ON ', '')))
      SET @SQLIndex = REPLACE(@SQLIndex, @SQLIndexTMP + ';', '')
      --SELECT @SQLIndex, @SQLIndexTMP, @CreateIndexCommand, @TableName, @IndexName

      -- Creating hypotetical index
      IF CharIndex('WITH STATISTICS_ONLY =', @SQLIndexTMP) = 0
      BEGIN
        SET @SQLIndexTMP = @SQLIndexTMP + ' WITH STATISTICS_ONLY = -1'
      END
      -- PRINT @SQLIndexTMP
      EXEC (@SQLIndexTMP)

      -- Creating query to drop the hypotetical index
      SELECT @SQLDropIndex = @SQLDropIndex + 'DROP INDEX ' + @TableName + '.' + @IndexName + '; '
      -- PRINT @SQLDropIndex

      -- Executing DBCC AUTOPILOT
      SET @SQLDbccAutoPilot = 'DBCC AUTOPILOT (0, ' + 
                                               CONVERT(VarChar, DB_ID()) + ', '+ 
                                               CONVERT(VarChar, OBJECT_ID(@TableName),0) + ', ' +
                                               CONVERT(VarChar, INDEXPROPERTY(OBJECT_ID(@TableName), @IndexName, 'IndexID')) + ')'

      EXEC (@SQLDbccAutoPilot)
      --PRINT @SQLDbccAutoPilot

      SET @i = @i + 1
    END

    -- Executing Query
    DECLARE @PlanXML xml

    EXEC st_CLR_GetAutoPilotShowPlan @Query = @Query, 
                                     @ShowPlan = @PlanXML OUT
    SELECT @PlanXML

    -- Droping the indexes
    EXEC (@SQLDropIndex)

    COMMIT TRAN
  END TRY
  BEGIN CATCH
    ROLLBACK TRAN
    -- Execute error retrieval routine.
    SELECT ERROR_NUMBER()    AS ErrorNumber,
           ERROR_SEVERITY()  AS ErrorSeverity,
           ERROR_STATE()     AS ErrorState,
           ERROR_PROCEDURE() AS ErrorProcedure,
           ERROR_LINE()      AS ErrorLine,
           ERROR_MESSAGE()   AS ErrorMessage;
  END CATCH;
END
GO

The proc st_TestHipotheticalIndexes expect two input parameters:

@SQLIndex: Here you should specify the command to create the index you and to try (the hypothetical indexes), if you want to try more than one index, just call it separating many “create index” commands by coma. For instance:

@SQLIndex = 'CREATE INDEX ix_12 ON Products (Unitprice, CategoryID, SupplierID) INCLUDE(ProductName);CREATE INDEX ix_Quantity ON Order_Details (Quantity);',

@Query: Here you should write the query you want to try.

Here is a sample of how to call it on :

-- Sample 1
EXEC dbo.st_TestHipotheticalIndexes @SQLIndex = 'CREATE INDEX ix ON Order_Details(Quantity);', 
@Query = 'SELECT * FROM Order_Details WHERE Quantity < 1'

The results of the query above is an XML with the query plan considering the suggested index:

Another sample:

— Sample 2

-- Sample 2
EXEC dbo.st_TestHipotheticalIndexes @SQLIndex = 'CREATE INDEX ix_12 ON Products (Unitprice, CategoryID, SupplierID) INCLUDE(ProductName);CREATE INDEX ix_Quantity ON Order_Details (Quantity);', 
                                    @Query = 'SELECT p.ProductName, p.UnitPrice, s.CompanyName, s.Country, od.quantity FROM Products as P INNER JOIN Suppliers as S ON P.SupplierID = S.SupplierID INNER JOIN order_details as od ON p.productID = od.productid WHERE P.CategoryID in (1,2,3) AND P.Unitprice < 20 AND S.Country = ''uk'' AND od.Quantity < 90'