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

Question

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,

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.