Array
(
)

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

Ignez
|
MVP
    12 mar 2015

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. 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.
No caso da execução com compatibilidade para SQL 2008, a query demora zero segundos. 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.
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!!!
Resumindo, o que tenho em relação a tempos é:
Compatibilidade SQL 2008: a rotina demora zero segundos usando variáveis TABLE ou tabelas temporárias.
Compatibilidade SQL 2014: a rotina demora SESSENTA segundos usando variáveis TABLE e ZERO segundos usando tabelas temporárias.
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.
Passei então ao plano de manutenção, que é composto de 17 segmentos. Eu validei cada um deles, alterando a rotina e 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 custo é mínima para cada comando, o máximo de diferença que apareceu entre os custo foi de 5%, o que não justificaria uma rotina no SQL 2014 demorar 60 vezes mais do que ela mesma no SQL 2008.
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.
Minha pergunta é: 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?