Tabelas temporárias causam recompilações no SQL Server?

O documento Recompilar um Procedimento Armazenado diz:

A recompilação automática ocorre sempre que o SQL Server é reiniciado. Ela também ocorre se uma tabela subjacente referenciada pelo procedimento tiver passado por alterações físicas de design.

Além disso, o white paper Plan Caching and Recompilation in SQL Server 2012 afirma que:

Sempre que uma alteração de esquema ocorre para qualquer um dos objetos referenciados por um lote, o lote é recompilado. As alterações de esquema são definidas como um ou mais dos seguintes:

• Adicionar ou remover colunas de uma tabela ou exibição.
• Adicionar ou remover restrições, padrões ou regras de/para uma tabela.
• Adicionar um índice a uma tabela ou a uma exibição indexada.
• Descartando um índice definido em uma tabela ou uma visualização indexada. Isso só se aplica se o índice for usado pelo plano de consulta em questão.
• Descartando (observação: não criando ou atualizando) uma estatística definida em uma tabela. Isso só se aplica se o plano de consulta usar a tabela. Atualizar uma estatística (manual e atualização automática) pode causar uma recompilação relacionada à otimalidade (relacionada a dados) de quaisquer planos de consulta que usem essa estatística. Recompilações relacionadas à otimalidade são descritas posteriormente.

Portanto, o culpado não é a tabela temporária em si, mas a alteração feita em seu esquema.

Cenário 1 (sem cache de tabela #temp)

CREATE OR ALTER PROC SomeProc1
AS

CREATE TABLE #my_table(A INT, B INT)

--Recompiled every execution with reason "Deferred Compile"
INSERT #my_table VALUES(1,2);

BeforeSelect1: 

--Recompiled every execution with reason "Deferred Compile"
--and then again with reason "Temp Table Changed" when jumped up to by the GOTO and executed for a second time following index creation
SELECT COUNT(*), A FROM #my_table GROUP BY A;

IF NOT EXISTS(SELECT * FROM tempdb.sys.indexes where object_id = object_id('tempdb..#my_table') and index_id <> 0)
    BEGIN
    CREATE INDEX ix ON #my_table(A);
    GOTO BeforeSelect1
    END


--Recompiled every execution with reason "Deferred Compile"
SELECT COUNT(*), A FROM #my_table GROUP BY A;

O rastreamento dos eventos de compilação, conforme descrito no white paper Planejar cache e recompilação no SQL Server 2012, mostra exatamente o mesmo em cada execução.

No caso acima, a #temptabela não existe no início da execução em cada execução. Então, todas as instruções que fazem referência a ela estarão sujeitas à compilação adiada e serão compiladas imediatamente antes de serem executadas pela primeira vez. Se você tiver apenas um fluxo linear através das instruções, então este é o único motivo de recompilação que você verá devido ao #tempuso da tabela em si. Adicionei um GOTO acima para tornar as coisas um pouco mais interessantes e para mostrar a segunda recompilação da primeira instrução SELECT após a criação do índice.

Cenário 2 (com cache de tabela #temp)

CREATE OR ALTER PROC SomeProc2
AS

CREATE TABLE #my_table(A INT  INDEX ix , B INT)

--Recompiled on first execution with reason "Deferred Compile"
INSERT #my_table VALUES(1,2);

--Recompiled on first execution with reason "Deferred Compile"
SELECT COUNT(*), A FROM #my_table GROUP BY A;

--Recompiled on first execution with reason "Deferred Compile"
SELECT COUNT(*), A FROM #my_table GROUP BY A;

A mudança significativa do primeiro proc é que não há nenhuma CREATE INDEXinstrução separada. Isso significa que a #temptabela é elegível para o Temporary Table Caching (já que esse era o único problema de bloqueio para isso neste caso) se o SQL Server decidir fazê-lo (na minha máquina de desenvolvimento, isso levou algumas tentativas para ver, pois eu estava um pouco deixando meu SQL Server local sem memória). Quando nenhuma tabela temporária em cache é reutilizada, ela mostra o padrão usual de compilações adiadas. A área destacada em rosa no rastreamento mostra uma execução onde reutilizou uma tabela temporária em cache e não houve eventos de recompilação. Isso não significa que sempre haverá zero eventos de recompilação associados à #temptabela, no entanto. Você ainda pode acabar vendo outros motivos usuais de recompilação, como "Estatísticas alteradas" - embora como isso funciona no caso de tabelas temporárias em cache possa ser problemático .