Problemas de Performance com Tabelas variáveis?
Tabelas variavéis apareceram no SQL Server com a intenção de reduzir compilações tornando-se bem popular com sua frequente utilização e em alguns casos mais do que as tabelas temporárias.
A sua famosa utilização deu-se devido ao mito que: “Tabela variável fica na memória e tabela temporária fica no tempdb”. Execute o código abaixo e verifique que mesmo para a tabela variável o SQL Server aloca espaço no tempdb fisicamente.
USE tempdb
go
declare @Tabela table (cod int not null primary key clustered (cod))
insert into @Tabela values(1),(2)
select sys.fn_PhysLocFormatter(%%physloc%%) FROM @Tabela
No entanto, ambos tipos de tabela possuem suas particularidades, pós e contras. Existem diferença entre tabela variável e tabela temporária? Sim várias.
O objetivo desse post não é falar sobre a diferença de ambas, mas, no final do post tem algumas referências sobre o assunto. Recomendo a leitura
O principal problema que já tive com tabelas variáveis foi devido a criação de planos de execução ineficiente, pois, quando uma query é compilada no SQL Server o número de linhas da tabela variável é “desconhecido”. Esse comportamento pode fazer com que o otimizador de consulta realize uma estimativa que pode não ser a melhor trazendo problemas no momento da geração do plano de execução já que a cardinalidade para tabelas variáveis são sempre 1. Eu escrevi sobre isso neste artigo.
A partir do SQL Server 2012 SP2 uma nova trace flag foi adicionada para ajudar nesse ponto.A trace flag 2453 faz com que o SQL Server detecta “linhas suficientes” sendo inseridas em uma tabela variável e faz com que seja disparado uma recompilação da instrução T-SQL para produzir um plano de execução mais eficiente.
Em que cenário a utilização dessa trace flag se aplicaria? Imagine em um ambiente que existem diversos objetos de programação criados e muitos deles apresentam problemas de desempenho devido a utilização da tabela variável. Para ter que alterar todos, isso pode demandar uma boa quantidade de tempo, nesse caso, seria interessante avaliar a utilização do Trace Flag 2453. Ou até mesmo em um ambiente onde o código fonte é disponibilizados por fábricas de softwares terceira e você não possui permissão para alteração do código fonte para mudar as tabelas variáveis para tabelas temporárias
Referências:
http://epmxperts.wordpress.com/2010/06/23/myth-sql-server-table-variables-vs-temp-tables/
http://blogs.msdn.com/b/psssql/archive/2012/08/22/10053781.aspx
Até mais.
