none
SQL 2014 - Tabela temporária mais rápida que variável table RRS feed

  • Pergunta

  • Pessoal, tudo bem?

    Estamos homologando um novo servidor com SQL 2014 em cluster. Durante os testes de performance, aconteceu agora bem estranho. Tenho uma rotina composta de diversos SELECT, variáveis TABLE e CTE. Quando eu executo essa rotina no SQL 2014, mas com o BD com compatibilidade 100 (SQL 2008), a query demora zero segundo para rodar.

    Porém, se eu mudo a compatibilidade para 2014, a mesma rotina demora quase 1 minuto para rodar. Pensei que pudesse ser alguma coisa na estrutura de índices ou estatísticas, por isso recriei todos os índices e estatísticas na compatibilidade 2014. Mesmo assim, a query continua demorando 1 minuto para rodar.

    Alguém já passou por algo parecido? Ou tem alguma ideia do que pode ser?

    Entre algumas modificações, na base da tentativa e erro, alterei todas as variáveis TABLE para tabelas temporárias. Com isso, o tempo da rotina caiu para zero!!!

    A pergunta agora é: por que? Por que o uso de tabelas temporárias deixou a rotina com resultado instantâneo, ao passo que o uso de variáveis TABLE fez a rotina demorar 1 minuto para concluir?

     Ideias? Dicas?

     Obrigada,

    quinta-feira, 12 de março de 2015 12:42

Respostas

  • Pessoal, bom dia.

    Descobri que o problema que estou enfrentando tem a ver com a nova versão do Cardinality Estimator no SQL 2014. O CE não está lidando corretamente com queries envolvendo join de tabelas com tabelas temporárias ou variáveis tabelas, e a execução das queries fica de 5 a 50 vezes mais lenta do que versões anteriores.

    Para resolver o problema, temos algumas opções:

    1) Se é numa única query, podemos usar OPTION(QUERYTRACEON 9481)

    2) Se é numa sessão específica, podemos usar DBCC TRACEON(9481)

    3) Se é num sistema inteiro, como é meu caso, podemos usar DBCC TRACEON(9481,-1)

    As três opções acima "desligam" a nova versão do CE no SQL 2014, utilizando a versão do SQL 2012.

    No meu caso, preferi a opção 4:

    4) Se é num sistema/instância, manter os BDs no SQL 2014 mas voltar os BDs para a compatibilidade 2008.

    Até agora, não encontrei nenhuma documentação da Microsoft falando sobre este caso, mas recebi um email do Fabricio Catae com o link de um artigo que explica exatamente o que está acontecendo aqui:

    https://milossql.wordpress.com/2014/01/03/new-cardinality-estimator-part-5-bug/

    Agora é aguardar para ver se a Microsoft vai comentar e/ou corrigir este caso.

    Aproveito para agradecer o Fabricio Catae pelo apoio nesta questão.


    quinta-feira, 19 de março de 2015 12:31

Todas as Respostas

  • Ignez já gerou um plano de execução da consulta?

    Aconselho gerar o plano de execução e analisar onde está o gargalo.

    Pergunta quando mudaram a compatibilidade para 2014 a database continuou na mesma filestream?

    Att.

    quinta-feira, 12 de março de 2015 12:52
  • Lucas, boa tarde.

    O plano de manutenção é o mesmo, independente da compatibilidade. Não houve mudança de filestream. Na verdade, não houve nenhuma mudança.

    Basicamente, o que estou executando é:

    USE MASTER
    GO

    ALTER DATABASE QVDSM3 SET COMPATIBILITY_LEVEL = 100 -- na segunda execução, eu troco para 120
    GO

    USE QVDSM3
    go

    DECLARE @nPeriodo = 201501
    ...


    E registro o tempo. Em seguida, mudo a compatibilidade e executo a query novamente.

    quinta-feira, 12 de março de 2015 14:45
  • Lucas, boa tarde.

    O plano de manutenção é o mesmo, independente da compatibilidade. Não houve mudança de filestream. Na verdade, não houve nenhuma mudança.

    Basicamente, o que estou executando é:

    USE MASTER
    GO

    ALTER DATABASE QVDSM3 SET COMPATIBILITY_LEVEL = 100 -- na segunda execução, eu troco para 120
    GO

    USE QVDSM3
    go

    DECLARE @nPeriodo = 201501
    ...


    E registro o tempo. Em seguida, mudo a compatibilidade e executo a query novamente.


    Ignez vc tem o plano de execução da consulta? A partir dele podemos identificar o gargalo da consulta.
    quinta-feira, 12 de março de 2015 16:11
  • Lucas, boa tarde.

    Não sei se é adequado considerar este caso como um gargalo de consulta. Como eu mencionei, a rotina roda em zero segundos no SQL 2008. O problema é quando executo a mesma rotina no SQL 2014, onde ela demora mais de um minuto para trazer o mesmo resultado.

    No SQL 2014, se eu alterar as variáveis TABLE para tabelas temporárias, a rotina roda em zero segundos. Meu problema é mais conceitual: porque o uso de variáveis TABLE numa rotina pode deixa-la tão demorada, quando o uso de tabelas temporárias deixa a rotina instantânea?

    A rotina é composta por:

    1. Vários SELECTS e CTE que carregam dados para dentro das variáveis TABLE ou tabelas temporárias.
    2. Cinco INSERT/SELECT usando tabelas do BD e objetos criados no item 1.
    3. Três UPDATE/SELECT usando tabelas do BD e objetos criados no item 1.

    O plano de manutenção é composto de 17 segmentos. Eu validei cada um deles, comparando a rotina usando:

    1) Variável table no SQL 2008 e SQL 2014.

    2) Tabela temporária no SQL 2008 e SQL 2014.

    3) Variável table em uma janela e tabela temporária em outra janela, ambas no SQL 2008.

    4) Variável table em uma janela e tabela temporária em outra janela, ambas no SQL 2014.

    Em todos os casos, o plano de manutenção é sempre o mesmo. A variação de carga é mínima para cada comando, o máximo de diferença que apareceu entre as cargas foi de 5%, o que não justificaria uma rotina no SQL 2014 demorar 60 vezes mais do que ela mesma no SQL 2008.

    quinta-feira, 12 de março de 2015 19:40
  • Pessoal, bom dia.

    Descobri que o problema que estou enfrentando tem a ver com a nova versão do Cardinality Estimator no SQL 2014. O CE não está lidando corretamente com queries envolvendo join de tabelas com tabelas temporárias ou variáveis tabelas, e a execução das queries fica de 5 a 50 vezes mais lenta do que versões anteriores.

    Para resolver o problema, temos algumas opções:

    1) Se é numa única query, podemos usar OPTION(QUERYTRACEON 9481)

    2) Se é numa sessão específica, podemos usar DBCC TRACEON(9481)

    3) Se é num sistema inteiro, como é meu caso, podemos usar DBCC TRACEON(9481,-1)

    As três opções acima "desligam" a nova versão do CE no SQL 2014, utilizando a versão do SQL 2012.

    No meu caso, preferi a opção 4:

    4) Se é num sistema/instância, manter os BDs no SQL 2014 mas voltar os BDs para a compatibilidade 2008.

    Até agora, não encontrei nenhuma documentação da Microsoft falando sobre este caso, mas recebi um email do Fabricio Catae com o link de um artigo que explica exatamente o que está acontecendo aqui:

    https://milossql.wordpress.com/2014/01/03/new-cardinality-estimator-part-5-bug/

    Agora é aguardar para ver se a Microsoft vai comentar e/ou corrigir este caso.

    Aproveito para agradecer o Fabricio Catae pelo apoio nesta questão.


    quinta-feira, 19 de março de 2015 12:31