Usuário com melhor resposta
SQL 2014 - Tabela temporária mais rápida que variável table

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,
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.
- Editado Ignez Mello quinta-feira, 19 de março de 2015 12:32
- Marcado como Resposta Ignez Mello quinta-feira, 19 de março de 2015 12:32
Todas as Respostas
-
-
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
GOALTER DATABASE QVDSM3 SET COMPATIBILITY_LEVEL = 100 -- na segunda execução, eu troco para 120
GOUSE QVDSM3
goDECLARE @nPeriodo = 201501
...
E registro o tempo. Em seguida, mudo a compatibilidade e executo a query novamente. -
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
GOALTER DATABASE QVDSM3 SET COMPATIBILITY_LEVEL = 100 -- na segunda execução, eu troco para 120
GOUSE QVDSM3
goDECLARE @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. -
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:
- Vários SELECTS e CTE que carregam dados para dentro das variáveis TABLE ou tabelas temporárias.
- Cinco INSERT/SELECT usando tabelas do BD e objetos criados no item 1.
- 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.
-
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.
- Editado Ignez Mello quinta-feira, 19 de março de 2015 12:32
- Marcado como Resposta Ignez Mello quinta-feira, 19 de março de 2015 12:32