Windows LiveWindows Live™
 
 
Fabiano Neves's profileFabiano Neves Amorim - S...PhotosBlogListsMore Tools Help

Blog
    March 26

    Produto Cartesiano e Query Optimizer

    Galera, existem algumas situações onde o QO(Query Optimizer) simplesmente, decide remover uma instrução do join de uma consulta o que acaba gerando um inesperado Produto Cartesiano, ou seja, a mesma coisa que um join sem relacionamento, um cross join.

     

    Por exemplo, imagine o seguinte código:

     

    use tempdb

    GO

     

    declare @tab1 Table(a Int)

     

    insert into @tab1

    select TOP 1000 1

      from sysobjects b, sysobjects a

     

    set statistics io on

    set statistics time on

     

    select * from @tab1 a

     inner join @tab1 b

        on a.a = b.a

     

    select * from @tab1 a

     inner join @tab1 b

        on a.a = b.a

     where a.a = 1

     

    set statistics io off

    set statistics time off

     

    A duas consultas acima retornam todos os registros ta tabela @tab1, mas existe uma grande diferença entre a primeira e a segunda consulta.

     

    A primeira diferença que podemos notar é que a segunda consulta contem uma condição no where onde a.a tem que ser igual a 1. Ai é que ta, neste caso o QO sabe que existe uma redundância de comparação, pois se “a.a” é igual 1(where), e “a.a” é igual a “b.a” (join), logo ele sabe que b.a também será igual a 1. Portanto neste caso ele remove a condição do join (a.a = b.a) e aplica o filtro de “a = 1” nas tabelas “a” e “b”.

     

    Este comportamento pode ser evidenciado ao analisarmos os planos de execução gerados. O plano da segunda consulta ficou assim:

     

    StmtText Argument
    select * from @tab1 a   inner join @tab1 b      on a.a = b.a   where a.a = 1 NULL
      |--Nested Loops(Inner Join) NULL
           |--Table Scan(OBJECT:(@tab1 AS [b]), WHERE:(@tab1.[a] as [b].[a]=(1))) OBJECT:(@tab1 AS [b]), WHERE:(@tab1.[a] as [b].[a]=(1))
           |--Table Scan(OBJECT:(@tab1 AS [a]), WHERE:(@tab1.[a] as [a].[a]=(1))) OBJECT:(@tab1 AS [a]), WHERE:(@tab1.[a] as [a].[a]=(1))

     

    Repare que na coluna argument o QO gerou apenas um argumento para a leitura das tabelas, e no Loop ele não gerou a condição de join. Isso caracteriza um Produto Cartesiano, onde para cada linha da tabela definida como “a”, o SQL vai ligar com todas as linhas da tabela “b”.

     

    Ok, mas isso é ruim?... Sim neste caso sim... Pois para esta consulta utilizar um algoritmo de Hash para fazer o join seria muito mais eficaz. Para confirmar isso, basta verificar o plano gerado pela primeira consulta. Lembrando que ambas as consultas são relativamente iguais. Repare que o SQL gerou um Hash Join para fazer a ligação entre as tabelas.

     

    StmtText

    Argument

    select * from @tab1 a   inner join @tab1 b      on a.a = b.a

    NULL

      |--Hash Match(Inner Join, HASH:([b].[a])=([a].[a]), RESIDUAL:(@tab1.[a] as [b].[a]=@tab1.[a] as [a].[a]))

    HASH:([b].[a])=([a].[a]), RESIDUAL:(@tab1.[a] as [b].[a]=@tab1.[a] as [a].[a])

           |--Table Scan(OBJECT:(@tab1 AS [b]))

    OBJECT:(@tab1 AS [b])

           |--Table Scan(OBJECT:(@tab1 AS [a]))

    OBJECT:(@tab1 AS [a])

     

    Se analisarmos os resultados do statistics io e statistics time, veremos que a primeira consulta, utilizou menos tempo, IO e CPU para retornar os dados.

     

    Primeira consulta (Hash Join):

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table '#2E75B1C0'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

     

    SQL Server Execution Times:

       CPU time = 328 ms,  elapsed time = 19976 ms.

     

     

    Segunda consulta (Loop Join):

    Table '#2E75B1C0'. Scan count 2, logical reads 2002, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

     

    SQL Server Execution Times:

       CPU time = 407 ms,  elapsed time = 20377 ms.

     

    Bom, mas você pode estar pensando, bem, neste caso é só utilizar um hint para forçar o uso do Hash Join certo? Errado, se você tentar fazer isso vai receber a seguinte mensagem:

     

    select * from @tab1 a

     inner join @tab1 b

        on a.a = b.a

     where a.a = 1

    option(hash join)

     

    Msg 8622, Level 16, State 1, Line 20

    Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

     

    Como o SQL remove a condição do Join, ele terá que fazer um Produto Cartesiano e o SQL só consegue fazer isso utilizando o Loop Join L. Neste caso, o Hint colide com a Lógica do QO e gera o erro.

     

    Como resolver o problema?....

    Basta esperar a próxima versão do SQL, e torcer para que eles copiem o Oracle, que neste caso criou um parâmetro para tratar com este problema.

    http://jonathanlewis.wordpress.com/2006/12/13/cartesian-merge-join/

     

    Enviei este exemplo para o Boris Baryshnikov (SQL Server Engine), e ele me respondeu dizendo que estão trabalhando em uma correção para uma futura release.

     

    Já existe um chamado no Connect falando sobre isso, portanto, só nos resta aguardar, e ficarmos atentos a este problema...

    9:52:30 AM | Add a comment | Permalink | Blog it | SQL Server
    March 19

    Fabiano VS Telefônica

    Depois de 4 meses utilizando E PAGANDO meu speedy de 2 megas(isso mesmo no plural, conforme a Senhora Marcia escreveu abaixo), cansei de pagar o valor indevido e pedi o cancelamento.

     

    Quero deixar aqui meu desprazer com a péssima qualidade de serviços prestados pela Telefônica. E nem ao menos português eles sabem, pelos menos usa um corretor automático e pronto.

     

    Marcia Augusto: Bem Vindo(a) Fabiano, em que posso ajudar ?

    Fabiano:              Olá Marcia

    Fabiano:              Quero trocar meu Speedy de 2 mb para 1 mb

    Fabiano:              você pode fazer isso?

    Fabiano:              ?

    Fabiano:              ?

    Fabiano:              Tem alguém ai?

    Marcia Augusto: Senhor Fabiano por gentileza entre em contato no chat relacionado a vendas, esse se refere-se ao cancelamento.

    Fabiano:              Ok, então cancele o meu Speedy

    Marcia Augusto: Senhor. ! Para melhor atendê-lo por gentileza informe nome completo e telefone com ddd do assinante da linha telefônica.

    Fabiano:              Fabiano Neves Amorim, 014-xxxxxxxxx

    Fabiano:              Cancelou?

    Fabiano:              ?

    Marcia Augusto: Para realizar o cancelamento do serviço soeedy é necessário a confirmação do número de CPF e RG do assinante da linha.

    Fabiano:              CPF: xxxxxxx RG: xxxxxxx

    Marcia Augusto: Aguarde um momento para verificação de seu cadastro.

    Fabiano:              ok

    Fabiano:              ok

    Marcia Augusto: Aguarde um momento para verificação em nosso sistema.

    Fabiano:              ok

    Marcia Augusto: Por gentileza aguarde mais um momento.

    Fabiano:              ok

    Marcia Augusto: Qual motivo da solicitação do cancelamento?

    Fabiano:              Já fazem 4 meses que você estão me enviando a conta com o valor errado. Cansei de tentar reclamar, portanto cancele.

    Marcia Augusto: Senhor Fabiano o senhor está no período de fidelidade se cancelar vai gerar quebra de fidelidade no valor de 259,20.

    Fabiano:              Mas vocês não estão me cobrando o valor correto, portanto não existe “fidelidade” nenhuma entre as partes.

    Marcia Augusto: Senhor conforme verificado no sistema o senhor está no período de fidelidade.

    Marcia Augusto: Em relação as contas peço por gentileza que peça uma análise das contas.

    Fabiano:              Ja pedi

    Fabiano:              Veio dizendo que não havia nenhum erro.

    Marcia Augusto: Senhor Fabiano o seu speedy é de 1 mega .

    Fabiano:              E qual é o valor correto?

    Marcia Augusto: Senhor Fabiano seu speedy é de 2 megas. desconsidere essa primeira informação.

    Fabiano:              Da mensalidade?

    Marcia Augusto: Senhor peço por gentileza que aguarde mais um momento para que eu possa verificar no sistema.

    Fabiano:              ok

    Marcia Augusto: Por gentileza aguarde só mais um momento, pois nosso sistema se encontra lento.

    Fabiano:              ok

    Marcia Augusto: Senhor Fabiano obrigado por esperar , o valor do speedy é de 99;87.

    Fabiano:              pois é, não foi este valor o acordado

    Fabiano:              durante a compra, ...

    Fabiano:              Faz o seguinte... cancela

    Fabiano:              Eu pago a Multa e nunca mais falo com a Telefonica e todos ficamos felizes

    Marcia Augusto: Senhor se cancelar vai gerar quebra de fidelidade no valor de 259,20,mesmo assim o senhor quer cancelar?

    Fabiano:              CANCELA

    Marcia Augusto: Aguarde um momento enquanto efetuo seu pedido e emito seu protocolo.

    Marcia Augusto: Senhor Fabiano por gentileza anote o número do protocolo N1090319975.

    Marcia Augusto: O prazo para ser retirado o serviço é de sete dias úteis para sair do sistema.

    Fabiano:              E quando chega a conta com a "quebra" de fidelidade?

    Marcia Augusto: O senhor deseja mais alguma informação?

    Fabiano:              E quando chega a conta com a "quebra" de fidelidade?

    Marcia Augusto: Provavelmente será na próxima fatura.

    Marcia Augusto: Algo mais que posso ajuda-lo?

    Fabiano:              Consegue cancelar a linha telefonica?

    Marcia Augusto: Senhor Fabiano nese momento não da porque fica pedente a ordem de cancelamento do serviço speedy só depois de sete dias.

    Marcia Augusto: Para melhor atendê-lo por gentileza informe nome completo e telefone com ddd do assinante da linha telefônica.

    Marcia Augusto: Desconsidere essa informação senhor Fabiano.

    Fabiano:              Ok, consegue cancelar ou não?

    Marcia Augusto: Senhor Felipe agora o sistema não permite com já disse, a ordem fica pendente no sistema só depois de sete dias..

    Fabiano:                Ok então só daqui a 7 dias úteis correto? só para constar meu nome é Fabiano e não Felipe.

    Marcia Augusto: Sim senhor Fabiano.

    Marcia Augusto: Foi um prazer atende-lo. A telefônica agradece ,tenha uma boa tarde.

    Fabiano:                 Muito obrigado, sou eu quem agradeço a gentileza da senhora.

    Marcia Augusto: Aguarde um momento para verificação em nosso sistema.

    Marcia Augusto: Por gentileza desconsidere a mensagem acima pois as informações estão divergentes de acordo com o que foi solicitado.

     


    Pois é pessoal, espero do fundo do coração e alma, que um dia esse nosso Brasil e nossos governantes tenham a coragem de pensar pelo menos um pouco, só um pouco, no povo.

    6:00:53 PM | Add a comment | Read comments (3) | Permalink | Blog it | Pessoal
    March 15

    Entrevista - Eu mesmo :-)

    Galera, como devem ter percebido nestas ultimas semanas estou um pouco distante dos fóruns e do Blog.

    Estou trabalhando em um projeto de integração tecnológica entre 2 grandes instituições financeiras. Ainda não sei se posso dar mais detalhes portanto, por enquanto fica assim...

    Como o projeto é bem grande, com certeza durante o andamento das fases do projeto, surgirão vários assuntos interessantes para compartilhar com vocês.

    Prometo que assim que eu puder posto mais detalhes...

     

    Por enquanto fiquem com a entrevista que a um tempo atrás, a pedido do Diego Nogare, eu mesmo respondi. As perguntas são as mesmas que fiz com alguns profissionais SQL Server.

     

    Eu acho que não coloquei aqui no blog minhas respostas portanto... segue para quem não viu na revista.

     

    1.       Vamos começar falando da novidade do momento, quais as novas features do SQL Server 2008 que você acha mais importante, e porque? Nomeie pelo menos 3.

    a.       A que me fez parar e dizer UAU foram as implementações de Compression, Page Compression, Row Compression e Backup Compression, fiz alguns testes e me surpreendi com a qualidade de compressão de dados. Usei uma frase na WebCast de “Caminhos de Upgrade para SQL Server 2008”, - Imagina converter um livro de 1000 páginas para apenas 200, pois é mais ou menos isso que o algoritmo de compressão por página LZ78 criado pelos geeks seniors Lempel e Ziv faz.

    1.       Nos meus testes cheguei a seguinte conclusão.

    Tabela

    CPU

    Writes

    Duration

    Tamanho

    Pages

    Tmp_Row_Compression

    10656

    1617

    45809

    12 MB

    1613

    Tmp_Page_Compression

    11359

    1142

    44118

    8 MB

    1133

    Tmp_Sem_Compression

    10032

    5914

    46873

    46 MB

    5883

     

    b.      Filtered Index – Essa veio bem a calhar, poder escrever um comando desses vai ajudar e muito para quem tem tabelas muito grandes e com acesso a dados mais recentes, caso partition não for uma possibilidade, imagine o seguinte poderíamos criar um índice para cada período e gravar cada índice em um filegroup diferente que por sua vez estará em discos diferentes. Interesting... vale a pena estudar a fundo essa feature.

    c.       Resource Governor – Esse pode ser utilizado tanto em ambientes de produção como ambientes de teste, imagine o seguinte cenário(bem comum em desenvolvedoras de Software), Um servidor SQL Server distribuído para todos os desenvolvedores utilizarem para testes, desenvolvimento etc... Em um banco desses rola de tudo, select *, operações bulk insert, restore de backup, tudo ao mesmo tempo e sempre tem aqueles “comilões” de recurso que rodam tudo isso ao mesmo tempo J(eu não) , com o Resoruce Governor poderíamos limitar a quantidade de CPU e memória para que ele não pare o servidor enquanto trabalha.

    2.       Em relação a performance no SQL Server 2005, quais as features você acha mais importante?

    a.       O SQL 2005 comparado com o 2000, tem muitas, mas posso destacar a partition como uma excelente escolha para balanceamento de carga entre servidores diferentes, procure por Distributed Partitioned Views / Federatad Databases.

    3.       Quais suas dicas para um profissional que está iniciando em SQL Server?

    a.       Estudar muito e manter-se atualizado, se puder comece com um bom curso oficial Microsoft, caso não seja possível fazer o curso, existem MUITOS blogs e sites especializados em SQL Server que na minha opinião tem um conteúdo até melhor e mais aprofundado em cada tecnologia do SQL.

    4.       O que é necessário para se tornar um usuário Master em SQL Server?

    a.       Eu acho que somente com experiência e uma vivência diária de trabalho com o SQL Server o usuário poderá um dia dizer que é um Master em SQL Server. Mesmo assim tenho certeza que haverão situações em que até mesmo um super usuário terá que recorrer ao velho e bom senhor do conhecimento, “Pai-Google”.

    5.       O que é necessário para se tornar um usuário Master em Transact SQL Server?

    a.       Encontre uma empresa que tem um banco de dados com mais de 2 mil procedures e 3 mil functions e tabelas com mais de 200 milhões de registros e comece a tentar otimizar os códigos TSQL. Acredite ou não isso é MUITO comum de se encontrar, já trabalhei em algumas implementações de DW em diversos banco de dados e já vi alguns desses bancos. Caso não seja possível faça o seguinte, inicie um trace no profiler e abra a aplicação que roda no banco, comece dar os cliques e fazer as operações comuns do dia a dia dos usuários do software e depois veja os selects executados por sua aplicação e tente otimizá-los.

    6.       Qual sua opinião em relação ao uso excessivo de Triggers no banco de dados?

    a.       Eu não sou contra o uso de triggers mas tento evitá-las o máximo possível, na minha opinião elas só deixam o código mais complexo e mais pesado. Eu conversei com o Conor Cunningham(Super Plus Master Boss do time do Query Processor) sobre triggers. Vou deixar 2 links para meus posts falando Sobre Triggers.

    1.       Why triggers are Bad - Part I

    1.       http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!486.entry

    2.       Why triggers are Bad - Part II

    1.       http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!488.entry

    7.       Qual o maior erro que uma pessoa pode cometer ao analisar um plano de execução?

    a.       Sem dúvida é achar que um Index Seek é SEMPRE melhor que um Clustered Index Sacan. Ou então ver uma flecha indicando o uso de paralelismo e logo voltar no código para incluir um OPTION(MAXDOP 1).

    8.       Quando usar um Clustered Index e quando usar um Non Clustered Index?

    a.       Isso depende bastante de cada cenário, mas eu particularmente uso Int IDENTITY(1,1) para pks e deixo ela como meu índice cluster e os nonclustered para consultas onde poderei fazer um covered índex, onde o SQL terá que acessar apenas o nonclustered índex para ler toda as informações necessárias para retornar os dados da consulta.

    9.       Porque estatísticas são importantes?

    a.       Estatísticas estão a toda hora sendo utilizadas pelo query processor para conseguir gerar o melhor plano de execução para sua consulta. Por exemplo nas estatísticas ele poderá saber se o valor que você está procurando irá retornar 10 linhas ou então 10 milhões de linhas, neste caso ele poderia optar por usar paralelismo por ex.

    10.   O quanto fragmentação no banco de dados pode afetar performance? Existe algum beneficio em ter fragmentação no banco?

    a.       Physical fragmentation é quando você tem espaço livre nas páginas da tabela e isso em alguns casos pode ser uma coisa boa, já que irá evitar que o SQL efetue splits em futuros inserts e updates na tabela.

    b.      Logical fragmentation é quando a próxima página lógica não é contínua... Ou seja, o SQL não irá conseguir fazer um read-ahead o que sempre será ruim. Confesso que fiquei curioso em relação a algum cenário onde esse tipo de fragmentação seria uma coisa boa, entrei em contato com nada mais nada menos que a Kimberly Trip e para minha surpresa após 1 hora que eu havia enviado o e-mail ela me respondeu dizendo que ela não via nenhum cenário onde esse tipo de fragmentação poderia ser benéfica, e mais, ela respondeu com cópia para o Paul Randal perguntando a opinião dele e logo depois ele também respondeu dizendo a mesma coisa. Portanto me fiz por satisfeito com as respostas J.

    11.   Afinal paralelismo é bom ou ruim?

    a.       Na minha opinião Paralelismo é bom. O que acontece é que em alguns casos onde temos uma pressão de CPU no servidor e o SQL optou por por fazer uma determinada consulta utilizando paralelismo, depois disso, o servidor recebeu outras requisições que demandavam muito mais CPU, neste caso a coisa pode ficar feia. L

    12.   Você utiliza alguma ferramenta para auxiliar na resolução de problemas de performance? Quais?

    a.       Profiler e SSMS são minha prediletas, as ferramentas de gerenciamento da Idera e da Quest são muito boas, mas é difícil convencer alguém de que o alto custo delas vale a pena.

    13.   Em um banco de dados em produção você utiliza algum TraceFlag habilitado?

    a.       Sim o 1118 para que o SQL não aloque mais extends mistos e sempre aloque extends uniformes, evitando uma possível contenção na TempDB que na teoria é a maior utilizada pela controladora SGAM já que as tabelas temporárias casualmente são pequenas.

    14.   Cite 3 livros que não podem faltar na coleção de um especialista SQL Server.

    a.       Inside SQL Server 2000 e todos da Série Inside Microsoft SQL Server 2005.

     

    15.   Nestes anos de experiência, Qual foi o problema mais difícil de resolver que encontrou?

    a.       Com certeza foi na otimização de uma proc com 2500 linhas que como vocês podem imaginar efetuava vários processos super complexos, depois de muito brigar com ela, mudei bastante coisa e no fim deu tudo certo J.

     

    16.   Já passou por algum daqueles problemas que resolveu mas até hoje não sabe o que era?

    a.       Direto, e quanto acontece isso faço o seguinte, para tudo, fecha, apaga e começa novamente, sempre deu certo, mas não me pergunte qual era o problema.

     

    17.   Qual o maior banco de dados que já trabalhou e quantas linhas tinha a maior tabela que já viu?

    a.       O banco de dados de um de nossos clientes, 200 gb e tabelas com mais de 250 milhões de registros, punk. Tem noção do tempo que demora pra reindexar ela?

    18.   Como diria o Tobby(charges.com.br) - Bate bola jogo rápido:

    a.       Um concorrente digno do SQL Server: Oracle para Grandes BDs, Firebird contra a Versão Express.

    b.      Uma feature: Compression.

    19.   Um comando SQL(o meu predileto é o SHUTDOWN WITH NOWAIT J) : SHUTDOWN WITH NOWAIT

    20.   Você tem algum blog? Site? Msn?... Como os usuários da comunidade podem entrar em contato com você?

    a.       E-Mail: fabiano_amorim@bol.com.br

    b.      Blog: http://fabianosqlserver.spaces.live.com/blog/

    c.       Msn: fabianonevesamorim@hotmail.com

    21.   Deseja deixar alguma consideração final?

    a.       “É melhor ser criticado pelos sábios do que ser elogiado pelos insensatos. Elogios vazios são como gravetos atirados em uma fogueira.” Eclesiastes.

     
    12:26:12 AM | Add a comment | Permalink | Blog it | SQL Server