Geralmente é mais rápido selecionar em uma tabela temporária do que selecionar em uma tabela real?

escrever em tempdb é mais rápido do que em uma tabela real fora de tempdb

É verdade. Existem dois aprimoramentos de IO em TempDb.

As gravações em uma tabela em um banco de dados do usuário devem ter seus registros de log liberados para o disco na confirmação ou, se uma inserção minimamente registrada (como SELECT … INTO), as páginas do banco de dados devem ser descarregadas no disco na confirmação. A maneira como o registro mínimo funciona em um banco de dados do usuário é que as páginas do banco de dados são gravadas diretamente no disco. No momento em que SELECT … INTO for concluído, as novas páginas serão todas gravadas nos arquivos físicos.

As gravações de TempDb não precisam ser descarregadas no disco na confirmação, pois o TempDb nunca é recuperado após uma falha. Portanto, eles simplesmente não são. Suas alterações geram registros de log, mas o buffer de log é liberado para o disco apenas quando está cheio, não para cada confirmação.

E desde o SQL Server 2014 , as inserções minimamente registradas no TempDb nem sempre são gravadas no disco. Se você carregar um pequeno, tabela temporária de curta duração, pode nunca ser gravado no disco. O log terá alguns registros sobre as alocações de páginas e entradas de metadados para a tabela, mas é isso.

EG execute o seguinte lote em tempdb, um banco de dados de recuperação completa e um banco de dados de recuperação simples para ver as diferenças.

drop table if exists foo go declare @data bigint declare @log bigint select @log = sum(case when type_desc = "LOG" then num_of_bytes_written end) ,@data = sum(case when type_desc = "ROWS" then num_of_bytes_written end) from sys.database_files f cross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fs select * into foo from sys.objects select -@log + sum(case when type_desc = "LOG" then num_of_bytes_written end) log_bytes ,-@data + sum(case when type_desc = "ROWS" then num_of_bytes_written end) data_bytes , (select recovery_model_desc from sys.databases where database_id = db_id()) recovery_model from sys.database_files f cross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fs 

e você verá algo como:

Para recuperação simples:

log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 24576 16384 SIMPLE 

para recuperação completa:

log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 36864 0 FULL 

e para tempdb:

log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 0 0 SIMPLE 

Às vezes, para tempdb, você verá o buffer de log liberado:

log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 61440 0 SIMPLE 

Comentários

• Há um caso em que a inserção inicial é mais rápida, mas ela volta para mordê-lo depois. Esta demonstração mostra uma consulta trazendo dados para o cache de buffer demorando muito mais, pois o gravador lento está ocupado gravando em páginas tempdb do disco que estão sujas para uma tabela temporária que não existe mais youtube .com / watch? v = X60ipwYv1Ms & feature = youtu.be
• Sim. Há ' s um potencial aprimoramento futuro para eliminar a liberação de páginas do Buffer Pool não alocadas a nenhum objeto. Mas carregar uma grande tabela temporária sempre impulsionará o IO, seja direta ou indiretamente, reduzindo a memória disponível para armazenamento em cache.