locked
Sql Server 2008 e 2005 usa estratégia diferente do SQL Server 2000 ? RRS feed

  • Pergunta

  • Olá pessoal, gostaria da ajuda de vocês para entender e se possível resolver o problema descrito a seguir:

    Executo no meu sistema a query abaixo:

    SELECT TOP 1 IdEstacao FROM DSINV with (nolock) WHERE NomeEstacao = '-' order by NomeEstacao desc

    Quando utilizo o SQL Server 2000 o plano de execução que obtenho é o mostrado abaixo:

    StmtText
    ----------------------------------------------------------------------------------------------------------------------------
      |--Top(1)
           |--Index Seek(OBJECT:([WELLFOTOTICA].[dbo].[DSINV].[NomeEstacao]), SEEK:([DSINV].[NomeEstacao]='-') ORDERED FORWARD)

    Quando utilizo o SQL Server 2005 o plano de execução que obtenho é o mostrado abaixo:

    StmtText
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------
      |--Top(TOP EXPRESSION:((1)))
           |--Parallelism(Gather Streams, ORDER BY:([Expr1003] DESC))
                |--Sort(TOP 1, ORDER BY:([Expr1003] DESC))
                     |--Compute Scalar(DEFINE:([Expr1003]=tertiary_weights([WellFototica].[dbo].[DSINV].[NomeEstacao])))
                          |--Index Seek(OBJECT:([WellFototica].[dbo].[DSINV].[ndx_NomeEstacao]), SEEK:([WellFototica].[dbo].[DSINV].[NomeEstacao]='-') ORDERED FORWARD)

    Quando utilizo o SQL Server 2008 o plano de execução que obtenho é o mostrado abaixo:

    StmtText
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------
      |--Top(TOP EXPRESSION:((1)))
           |--Parallelism(Gather Streams, ORDER BY:([Expr1003] DESC))
                |--Sort(TOP 1, ORDER BY:([Expr1003] DESC))
                     |--Compute Scalar(DEFINE:([Expr1003]=tertiary_weights([WellFototica].[dbo].[DSINV].[NomeEstacao])))
                          |--Index Seek(OBJECT:([WellFototica].[dbo].[DSINV].[ndx_NomeEstacao]), SEEK:([WellFototica].[dbo].[DSINV].[NomeEstacao]='-') ORDERED FORWARD)

    Ou seja, o 2000 resolve a query de forma tremendamente mais "performática" do que o 2005 e o 2008, pois ele resolve tudo no "Index Seek", e o 2005 e 2008 mesmo fazendo o "Index Seek" no mesmo índice, depois ele executa um "SORT" que destroi a performance. No profile o tempo de execução do 2000 aparece com 0 (zero) tanto em "Duração" como em "CPU", no 2005 e 2008 os tempos vão para em torno de 2 segundos.

    Este comportamento é apresentado mesmo alterando o modo de compatibilidade do banco para a versão correspondente ao SGBD (2005 -> 90, 2008 -> 100) e reindexando todos os índices.

    Alguém tem uma explicação para este comportamento diferente ? E se isso pode ser resolvido com alguma alteração configuração ? (Sei que alterando a estrutura da query posso mudar este cenário, mas isso é somente um caso do sistema, podem existir outros).

    Obrigado,

    Roberto

    quinta-feira, 19 de agosto de 2010 15:10

Respostas

  •  

    Boa dia Rvgadelha, tudo bem?

    O sql server 2005/2008 possui mais otimizações do que o 2000. E cada otimização desta possui um custo. E por menor que seja este custo, ele pode fazer diferença em determinados cenários onde houver grandes compilações;

    O tamanho do plano de execução do 2k5 e 2k8 é maior do que do 2k, devido estes recursos a mais. O esforço para executar todos estes recursos, também é maior.

    Um dos maiores motivos de termos um plano de execução "super simples" no sql 2k, é devido ao fato de processadores x86 "antigos" possuírem pouco cache L2 (implicações de processamento).

    Os atuais processadores possuem uma performance incrível (ex.: processadores em personal computers que possuem quase 7giga flops per second de 12 núcleos).

    E manter o mesmo plano de execução de uma versão para outra é praticamente impossível, ainda mais falando de queries adhoc...

    Mas resumindo, não que o sql 2k5 ou o 2k8 seja mais lento que o 2k. O motivo são os novos recursos.

    Se você trabalhar com uma query complexa por exemplo (seja em joins), você notará que a árvore criada para retornar os dados terá maior performance do que no 2k.

    E em caso de compilação paralela, é ralizado um scan e uma ordenação de partições em paralelo, e a quantidade de tabelas de ordenação existente ao mesmo tempo, dependerá da quantidade atual de trabalhos concorrentes. As partições desejadas, são selecionadas pelo "trabalhador" uma a uma. Quando ele seleciona uma partição, ele parte para a outra partição. Cada "trabalhador" compila de 0 a N partições e eles NÃO compartilham entre outros "trabalhadores". Agora, como podemos ter uma partição igual a 0???? Bom, se o DOP for maior que o número de partições, então não teremos partições suficientes para distribuir a todos os "trabalhadores". Por isso se você setar o parâmetro maxdop para um valor maior que zero, você pode conseguir eliminar o paralelismo. Mas devemos tomar muito cuidado para setar o maxdop..

    Outra coisa para deixar o plano de execução mais parecido o possível com o sql 2k, é  alterar o collation da coluna pesquisada.
    Se você observar o sql está convertendo sua pesquisa em um valor binário (função: tertiary_weights).
    Exemplo:

    Criei um banco com o collate: Latin1_General_CI_AS, meu tempdb possui o collate: Latin1_General_CI_AS

    Criando a tabela:
    create table teste1 (id int, nome varchar(200) COLLATE SQL_Latin1_General_Pref_CP437_CI_AS)

    Executando o comando:
    select top 1 id from teste1 with(nolock) where nome collate  = '-' order by nome desc

    Eu terei o seguinte plano:

    StmtText
    --------------------------------------------------------------------------------------------------
      |--Sort(TOP 1, ORDER BY:([Expr1004] DESC))
           |--Compute Scalar(DEFINE:([Expr1004]=tertiary_weights([tes].[dbo].[teste1].[nome])))
                |--Table Scan(OBJECT:([tes].[dbo].[teste1]), WHERE:([tes].[dbo].[teste1].[nome]='-'))

    Note que é bem parecido com o plano reportado por sua query.

    Porém se eu criar a tabela com o mesmo collate da minha base de dados:
    create table teste2 (id int, nome varchar(200) COLLATE Latin1_General_CI_AS) -- Observe que só coloco o nome do collate para explicar, porém se ocultar o nome, por default ele criará com o collate da base.

    Executando o comando:
    select top 1 id from teste2 with(nolock) where nome  = '-' order by nome desc

    Teríamos o plano bem parecido com o do sql2k:

    StmtText
    ---------------------------------------------------------------------------------------------
      |--Top(TOP EXPRESSION:((1)))
           |--Table Scan(OBJECT:([tes].[dbo].[teste2]), WHERE:([tes].[dbo].[teste2].[nome]='-'))



    As divergências são devido a estrutura de sua tabela com a minha.

    Espero que lhe ajude esta explicação, qualquer dúvida, favor postar.

    Atenciosamente

    Dobereiner Miller Silva Rodrigues

    sqlinternal.blogspot.com


    Aquilo que sou é aquilo que me foi outorgado
    sexta-feira, 20 de agosto de 2010 14:36
  • rvgadelha

    Apartir do SQL Server 2005 foram adicionado alguns novos operadores, como também outros tiveram a sua nomeclatura modificada.

    È verdade. o Plano SQL2K é bem mais simples que os planos do SQL2K5 e SQL2K8. porém temos que notar que temos uma operação de paralelismo "|--Parallelism(Gather Streams, ORDER BY:([Expr1003] DESC))" isso em determinados caso podem prejudicar o desempenho da sua consulta. Principalmente me ambientes com altos volumes transacional, Cache saturado, sub-sistema de I/O sobrecarregado. são variaveis que podem prejudicar operações em paralelo.

    Adicione o MAXDOP = 1 na sua consulta no SQL Server 2005/2008 e coloque o resultado aqui para podemos analisar.

     

    []´s

     

    Leivio Fontenele - www.dbaninja.com.br


    MCP | MCTS | MCITP - DBA SQL Server Sênior www.dbaninja.com | www.cleverdata.com.br
    quinta-feira, 19 de agosto de 2010 19:24

Todas as Respostas

  • rvgadelha

    Apartir do SQL Server 2005 foram adicionado alguns novos operadores, como também outros tiveram a sua nomeclatura modificada.

    È verdade. o Plano SQL2K é bem mais simples que os planos do SQL2K5 e SQL2K8. porém temos que notar que temos uma operação de paralelismo "|--Parallelism(Gather Streams, ORDER BY:([Expr1003] DESC))" isso em determinados caso podem prejudicar o desempenho da sua consulta. Principalmente me ambientes com altos volumes transacional, Cache saturado, sub-sistema de I/O sobrecarregado. são variaveis que podem prejudicar operações em paralelo.

    Adicione o MAXDOP = 1 na sua consulta no SQL Server 2005/2008 e coloque o resultado aqui para podemos analisar.

     

    []´s

     

    Leivio Fontenele - www.dbaninja.com.br


    MCP | MCTS | MCITP - DBA SQL Server Sênior www.dbaninja.com | www.cleverdata.com.br
    quinta-feira, 19 de agosto de 2010 19:24
  • Leivio,

    Segue o resultado da consulta com a alteração sugerida:

    SELECT TOP 1 IdEstacao FROM DSINV with (nolock) WHERE NomeEstacao = '-' order by NomeEstacao desc
    option (maxdop 0)

    (1 linha(s) afetadas)

    StmtText
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------
      |--Top(TOP EXPRESSION:((1)))
           |--Parallelism(Gather Streams, ORDER BY:([Expr1003] DESC))
                |--Sort(TOP 1, ORDER BY:([Expr1003] DESC))
                     |--Compute Scalar(DEFINE:([Expr1003]=tertiary_weights([WellFototica].[dbo].[DSINV].[NomeEstacao])))
                          |--Index Seek(OBJECT:([WellFototica].[dbo].[DSINV].[ndx_NomeEstacao]), SEEK:([WellFototica].[dbo].[DSINV].[NomeEstacao]='-') ORDERED FORWARD)

    Atc,

    Roberto

    quinta-feira, 19 de agosto de 2010 22:30
  • Desculpem,


    Fiz o teste sugerido, mas na hora de postar coloquei o resultado errado, segue o correto:
    SELECT TOP 1 IdEstacao FROM DSINV with (nolock) WHERE NomeEstacao = '-' order by NomeEstacao desc
    option (maxdop 1)

    StmtText
    ----------------------------------------------------------------------------------------------------------------------------------------------------------
      |--Sort(TOP 1, ORDER BY:([Expr1003] DESC))
           |--Compute Scalar(DEFINE:([Expr1003]=tertiary_weights([WellFototica].[dbo].[DSINV].[NomeEstacao])))
                |--Index Seek(OBJECT:([WellFototica].[dbo].[DSINV].[ndx_NomeEstacao]), SEEK:([WellFototica].[dbo].[DSINV].[NomeEstacao]='-') ORDERED FORWARD)

    Com o uso da opção o tempo de execução reduziu drasticamente.

    Obrigado Leivio !

    Sds,

    Roberto

    sexta-feira, 20 de agosto de 2010 13:25
  •  

    Boa dia Rvgadelha, tudo bem?

    O sql server 2005/2008 possui mais otimizações do que o 2000. E cada otimização desta possui um custo. E por menor que seja este custo, ele pode fazer diferença em determinados cenários onde houver grandes compilações;

    O tamanho do plano de execução do 2k5 e 2k8 é maior do que do 2k, devido estes recursos a mais. O esforço para executar todos estes recursos, também é maior.

    Um dos maiores motivos de termos um plano de execução "super simples" no sql 2k, é devido ao fato de processadores x86 "antigos" possuírem pouco cache L2 (implicações de processamento).

    Os atuais processadores possuem uma performance incrível (ex.: processadores em personal computers que possuem quase 7giga flops per second de 12 núcleos).

    E manter o mesmo plano de execução de uma versão para outra é praticamente impossível, ainda mais falando de queries adhoc...

    Mas resumindo, não que o sql 2k5 ou o 2k8 seja mais lento que o 2k. O motivo são os novos recursos.

    Se você trabalhar com uma query complexa por exemplo (seja em joins), você notará que a árvore criada para retornar os dados terá maior performance do que no 2k.

    E em caso de compilação paralela, é ralizado um scan e uma ordenação de partições em paralelo, e a quantidade de tabelas de ordenação existente ao mesmo tempo, dependerá da quantidade atual de trabalhos concorrentes. As partições desejadas, são selecionadas pelo "trabalhador" uma a uma. Quando ele seleciona uma partição, ele parte para a outra partição. Cada "trabalhador" compila de 0 a N partições e eles NÃO compartilham entre outros "trabalhadores". Agora, como podemos ter uma partição igual a 0???? Bom, se o DOP for maior que o número de partições, então não teremos partições suficientes para distribuir a todos os "trabalhadores". Por isso se você setar o parâmetro maxdop para um valor maior que zero, você pode conseguir eliminar o paralelismo. Mas devemos tomar muito cuidado para setar o maxdop..

    Outra coisa para deixar o plano de execução mais parecido o possível com o sql 2k, é  alterar o collation da coluna pesquisada.
    Se você observar o sql está convertendo sua pesquisa em um valor binário (função: tertiary_weights).
    Exemplo:

    Criei um banco com o collate: Latin1_General_CI_AS, meu tempdb possui o collate: Latin1_General_CI_AS

    Criando a tabela:
    create table teste1 (id int, nome varchar(200) COLLATE SQL_Latin1_General_Pref_CP437_CI_AS)

    Executando o comando:
    select top 1 id from teste1 with(nolock) where nome collate  = '-' order by nome desc

    Eu terei o seguinte plano:

    StmtText
    --------------------------------------------------------------------------------------------------
      |--Sort(TOP 1, ORDER BY:([Expr1004] DESC))
           |--Compute Scalar(DEFINE:([Expr1004]=tertiary_weights([tes].[dbo].[teste1].[nome])))
                |--Table Scan(OBJECT:([tes].[dbo].[teste1]), WHERE:([tes].[dbo].[teste1].[nome]='-'))

    Note que é bem parecido com o plano reportado por sua query.

    Porém se eu criar a tabela com o mesmo collate da minha base de dados:
    create table teste2 (id int, nome varchar(200) COLLATE Latin1_General_CI_AS) -- Observe que só coloco o nome do collate para explicar, porém se ocultar o nome, por default ele criará com o collate da base.

    Executando o comando:
    select top 1 id from teste2 with(nolock) where nome  = '-' order by nome desc

    Teríamos o plano bem parecido com o do sql2k:

    StmtText
    ---------------------------------------------------------------------------------------------
      |--Top(TOP EXPRESSION:((1)))
           |--Table Scan(OBJECT:([tes].[dbo].[teste2]), WHERE:([tes].[dbo].[teste2].[nome]='-'))



    As divergências são devido a estrutura de sua tabela com a minha.

    Espero que lhe ajude esta explicação, qualquer dúvida, favor postar.

    Atenciosamente

    Dobereiner Miller Silva Rodrigues

    sqlinternal.blogspot.com


    Aquilo que sou é aquilo que me foi outorgado
    sexta-feira, 20 de agosto de 2010 14:36
  • Obs.: Esqueci de informar... meu exemplo foi realizado sobre o sql server 2005 e 2008 e 2008 r2. Infelizmente não tenho nem uma mídia se quer do 2000, para criar outros cenários e explicar aqui.

    Atenciosamente

    Dobereiner Miller Silva Rodrigues

    sqlinternal.blogspot.com


    Aquilo que sou é aquilo que me foi outorgado
    sexta-feira, 20 de agosto de 2010 14:38
  • Post antigo, por isso o mesmo foi encerrado.

    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 5 de junho de 2018 13:08
    Moderador