Fabiano Neves's profileFabiano Neves Amorim - S...PhotosBlogListsMore  |  Tools Help |
|
|
December 17 Tipo de Joins no SQL ServerSemana passada participei de um treinamento de SQL Server na empresa em que falamos sobre execution plan, durante o treinamento surgiram alguns operadores de joins em nossas consultas de exemplo e percebi a necessidade de falar mais sobre alguns tipos de joins, ou seja, assunto para o Blog.
Estou lendo o livro SQL Server 2005 Query Tunning and Optimization e vou aproveitar os excelentes exemplos e explicações de como os operadores de join funcionam.
Vamos nos basear na seguinte consulta para os exemplos:
Select codigo from tabela1 Inner join tabela2 On tabela1.id = tabela2.id Where tabela1.id = 10
Nested Loops Joins – Compara cada linha de uma tabela(também conhecido por OUTER TABLE(FROM)) com cada linha de outra tabela(também conhecido por inner table) procurando pelas linhas que satisfaçam o relacionamento. Pseudo-Código:
for each row tabela1 in the outer table for each row tabela2 in the inner table if tabela1 joins with tabela2 return(tabela1, tabela2)
Merge Join - Trabalha em simultâneo lendo e comparando cada linha das duas tabelas ordenadas pelo relacionamento. Para cada etapa compara a linha atual para cada valor na tabela do join, se o valor for igual a entrada então ele retorna o join e continua a comparação. Se o valor não for igual ele descarta o resultado e continua a próxima leitura. Um Merge Join não necessariamente irá ler toda a tabela, quando a leitura chegar no fim de qualquer tabela envolvida no join o loop é parado. Pseudo-Código:
Get first row tabela1 from input 1 Get first row tabela2 from input 1 While not at the end of either input Begin If tabela1 joins with tabela2 Begin Output(tabela1, tabela2) Get next row tabela2 from input 2 end else if tabela1 < tabela2 get next row tabela1 from input 1 else get next row tabela2 form input 2 end
Hash Join – O hash join compartilha alguns conceitos do Merge Join, o algoritmo de Hash é executado em duas fases conhecidas por Build(Construção) e Probe(prova ou experimento). Durante a fase de Build o SQL irá ler todas as linhas do primeiro input(from) e criar uma tabela com os dados lidos mais o relacionamento gerando uma Hash Table em memória. Na fase do probe o SQL irá ler cada linha do segundo input(tabela do join) gerar o valor hash e verificar se o seu valor tem o mesmo valor da tabela Hash table recém criada retornando todas as linhas que tem o mesmo valor. É importante destacar que não necessariamente a Hash table sempre será gerada em memória pois o SQL irá reservar a quantidade de memória necessária para criação da tabela antes de cria-lá baseado nas statisticas geradas pelo otimizador. Caso durante e build o SQL necessite de mais memória para gerar a tabela Hash ele pode gravar os dados em um espaço temporário ou seja, TempDB. Pseudo-Código:
For each row tabela1 in the build table Begin Calculate hash value on tabela1 join key(s) Insert tabela1 into the appropriate hash bucket End For each row tabela2 in the probe table Begin Calculate hash value on tabela2 join key(s) For each row tabela1 in the corresponding hash bucket If tabela1 with tabela2 Ouput (tabela1, tabela2) end Cabe a dica que para os seus testes você pode forcar o SQL a usar um determinado tipo de join se os exemplos:
SELECT Tabela1.ID FROM Tabela1 INNER LOOP JOIN Tabela2 ON Tabela1.ID = Tabela2.ID WHERE Tabela1.ID = 10 GO SELECT Tabela1.ID FROM Tabela1 INNER MERGE JOIN Tabela2 ON Tabela1.ID = Tabela2.ID WHERE Tabela1.ID = 10 GO SELECT Tabela1.ID FROM Tabela1 INNER HASH JOIN Tabela2 ON Tabela1.ID = Tabela2.ID WHERE Tabela1.ID = 10
Fonte – Inside SQL Servr 2005: Query Tunning and Optimization. __________________________________________________________________ ( (55 - 14) 3404-3700 December 11 IndicesPrimeiramente quero agradecer ao Sanches pelas dicas sobre os Posts, Fiquem a vontade para me enviar criticas ou sugestões para posts.
Esta semana fiz uma alteração em uma tabela do no nosso banco de dados onde mudei a ordem das colunas do meu índice cluster, isso pode ser muito útil caso as consultas que são executadas nesta tabela tenham as colunas do índice cluster no where. Vamos a um exemplo prático.
--Vamos criar uma tabela para teste drop table teste create table teste (ID Int Identity(1,1) NOT NULL, Nome VarChar(200) NOT NULL, Data DateTime)
alter table teste add constraint xpk_Nome_ID primary key (Nome, ID) go
set nocount on declare @i Int set @i = 0
while @i < 1000 begin insert into teste (nome,data) values('A', GetDate()) insert into teste (nome,data) values('B', GetDate()) insert into teste (nome,data) values('C', GetDate()) insert into teste (nome,data) values('D', GetDate()) insert into teste (nome,data) values('E', GetDate()) set @i = @i + 1; end
-- Vamos imaginar a seguinte consulta -- Repare que o SQL não consegue fazer o seek porque a tabela está ordenada pela coluna Nome e não pela coluna ID select * from teste where id = 10
--Apaga a primary key alter table teste drop constraint xpk_Nome_ID GO --Recria a primary key ordenado por ID e Nome alter table teste add constraint xpk_ID_Nome primary key (ID, Nome)
-- Ao executar a consulta novamente conseguimos um seek. select * from teste where id = 10
Claro que isso depende de um estudo bem feito das consultas que são executadas na sua tabela, bem como a alteração dos valores das colunas(UPDATE) porque uma má alteração poderia causar fragmentação na tabela, mas fragmentação é assunto para um outro post.
Valeu __________________________________________________________________ ( (55 - 14) 3404-3700 December 04 Statisticas e Plano de ExecuçãoSemana passada me deparei com um problema de performance de uma procedure chamada pela minha aplicação Win32 exibindo uma mensagem de TimeOut para o usuário. Somente após as estatísticas das tabelas serem atualizadas é que o processo conseguiu rodar.
Vamos analisar um pouco o problema.
Primeira pergunta: O que são e para que servem as estatísticas das tabelas?
O SQL Server guarda informações em relação aos registros das tabelas para que o Otimizador possa decidir qual o melhor plano de execução para uma determinada consulta baseado nos dados que ele lê nas statistics das tabelas. Vamos a um exemplo:
insert into teste (nome) values('A')
insert into teste (nome) values('B')
insert into teste (nome) values('C')
insert into teste (nome) values('D')
insert into teste (nome) values('E')
set @i = @i + 1;
end
while @i < 10000
begin
insert into teste (nome) values('A')
set @i = @i + 1;
end
create index ix_teste on teste(nome)
--Vamos ver as Statistics criadas pelo indice
DBCC SHOW_Statistics(teste, ix_teste)
-- Faz um Table Scan pois ele lê nas Statistics que é o melhor meio de acesso aos dados, a consulta irá retornar 1000 registros
-- Repare que na propriedade Estimeted Number Rows do Execution Plan o SQL já sabe quantas linhas a consulta irá retornar.
select * from teste
where nome = 'A'
--Vamos incluir uma massa de dados para o valor X
declare @i Int
set @i = 0
while @i < 10000
begin
insert into teste (nome) values('X')
set @i = @i + 1;
end
-- Repare que o SQL criou um novo plano de execução para a consulta abaixo. Sendo que ele deveria gerar um table scan como no primeiro SQL.
-- ele não gerou porque as statistics estão desatualizadas repare que atualizando as statistics e executando a consulta novamente o SQL escolhe o melhor plano
-- Podemos concluir que o deixar as statistics desatualizadas pode levar o otimizador a um mal plano de execução.
-- Verificar a propriedade Estimeted Number Rows do Execution Plan.
select * from teste
where nome = 'X'
--Vamos atualizar as statistics
UPDATE STATISTICS teste
GO
--Faz um full Scan
select * from teste
where nome = 'X'
Segunda pergunta: Como identificar o problema?
Identifiquei que o problema era um mal plano de execução gerado pelo SQL de uma consulta feita dentro da procedure que retornava os dados do processo. Esta consulta fazia vários joins com tabelas com mais de 15 milhões de registros e o SQL Server estava fazendo um Index Scan em quase todas estas tabelas.
Rodei a procedure no SSMS com a opção para exibir o Plano de Execução (CTRL+M) ligada e tive o seguinte resultado.
Observando a imagem acima podemos identificar que as querys 5 e 6 são responsáveis pelo gargalo da procedure então me limitei em analisar estas consultas.
Após analisar as consultas constatei que o SQL Server não estava gerando um bom plano de execução para as consultas já que ele estava gerando vários Index Scan ao invés de Seeks.
Terceira pergunta: Como resolver o problema? Após identificar que o problema era o plano de execução temos várias maneiras de forçar o SQL Server a gera determinado plano por exemplo com o uso de Hints, no meu caso achei melhor criar um cursor e dentro de cada linha do cursor eu executo a instrução SQL mas passando uma condição bem restritiva no WHERE baseado no valor do cursor fazendo o SQL Server gerar um plano de execução com vários Seeks o que foi bem melhor que apenas um Index Scan.
Fiz mais outras alterações como trocar os DataTypes das tabelas temporárias usadas na procedure DateTime para SmallDateTime, Int para SmallInt ou TyniInt, SET NOCOUNT ON dentre outros, mas o ideal é sempre melhorar o gargalo.
A melhora foi muito grande, vamos analisar os resultados no profiler.
Conclusão de 2:20 minutos para 0. J
Verificamos então como identificar o problema, fica a dica sobre Statistics e a analise dos planos de execução utilizando o CTRL-M, é claro que isso não funciona sempre mas ajuda.
__________________________________________________________________ ( (55 - 14) 3404-3700 |
|
|
