O SQL Server armazena em cache as tabelas temporárias criadas nos procedimentos armazenados e apenas as renomeia quando o procedimento termina e é executado posteriormente. Minha pergunta tem a ver com quando o espaço tempdb é liberado. Eu li que a tabela é truncada no final do procedimento . Eu li nos comentários que isso é tratado por sessão e vi uma pergunta sobre se a limpeza é necessária ou não respondida no MSDN . Mas e se ele nunca for executado pela mesma sessão duas vezes?
Também ouvi dizer que existe um processo de coleta de lixo em segundo plano que libera esse espaço quando a tabela está fora do escopo.
Truncando uma tabela temporária no final do procedimento armazenado que a cria parece fazer com que o espaço que a tabela usa em tempdb para os dados sejam liberados mais rapidamente do que se nenhuma instrução truncate for usada, apesar das expectativas em contrário. Por quê?
Quais seriam as implicações de desempenho relativo de usar ou não usar uma instrução truncada? Ao usar o isolamento SNAPSHOT, o tempdb é frequentemente estressado e eu acho que liberar o espaço usado no tempdb de uma grande tabela temporária o mais rápido possível impediria o crescimento desnecessário do tempdb. Essa economia de espaço potencial teria o custo do desempenho?
Aqui está algum código para reproduzir o problema (principalmente de @TheGameiswar, com algumas alterações):
SET NOCOUNT ON;
GO
ALTER PROC usp_test
AS
BEGIN
IF object_id('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
SELECT *
INTO #temp
FROM [dbo].[Event_28] -- This is a table with 15313 rows, using 35648 KB according to sp_spaceused
--SELECT SUM(user_object_reserved_page_count) AS [user object pages used]
-- ,(SUM(user_object_reserved_page_count) * 1.0 / 128) AS [user object space in MB]
-- ,getdate() AS BeforeTruncate
--FROM tempdb.sys.dm_db_file_space_usage;
-- TRUNCATE TABLE #temp
--SELECT SUM(user_object_reserved_page_count) AS [user object pages used]
-- ,(SUM(user_object_reserved_page_count) * 1.0 / 128) AS [user object space in MB]
-- ,getdate() AS AfterTruncate
--FROM tempdb.sys.dm_db_file_space_usage;
END
GO
SELECT SUM(user_object_reserved_page_count) AS [user object pages used]
,(SUM(user_object_reserved_page_count) * 1.0 / 128) AS [user object space in MB]
,getdate() AS 'before'
FROM tempdb.sys.dm_db_file_space_usage;
EXEC usp_test
GO
SELECT SUM(user_object_reserved_page_count) AS [user object pages used]
,(SUM(user_object_reserved_page_count) * 1.0 / 128) AS [user object space in MB]
,getdate() AS 'final'
FROM tempdb.sys.dm_db_file_space_usage;
GO 40
As linhas comentadas foram deixadas comentadas para algumas execuções e não comentadas para outras. Quando o TRUNCATEfoi comentado, demorou entre 2,25 e 4,5 segundos antes que os resultados da tempdb.sys.dm_db_file_space_usageconsulta (4472 páginas a mais e 34,9375 MB a mais) correspondessem ao resultado antes que o procedimento fosse executado. Com as linhas (incluindo o TRUNCATE) não comentadas, levou apenas cerca de 0,11 - 0,9 segundos. Esses resultados são de um sistema ativo, com um pequeno crescimento de dados na tabela de origem durante este experimento.
Exemplo de saída com o código comentado (2,69 segundos da primeira à última entrada "final"):
user object pages used user object space in MB before
---------------------- --------------------------------------- -----------------------
1536 12.000000 2017-10-04 21:03:42.197
Beginning execution loop
user object pages used user object space in MB final
---------------------- --------------------------------------- -----------------------
6000 46.875000 2017-10-04 21:03:42.423
user object pages used user object space in MB final
---------------------- --------------------------------------- -----------------------
6000 46.875000 2017-10-04 21:03:42.533
user object pages used user object space in MB final
---------------------- --------------------------------------- -----------------------
6000 46.875000 2017-10-04 21:03:42.643
user object pages used user object space in MB final
---------------------- --------------------------------------- -----------------------
6000 46.875000 2017-10-04 21:03:42.883
user object pages used user object space in MB final
---------------------- --------------------------------------- -----------------------
6000 46.875000 2017-10-04 21:03:42.990
user object pages used user object space in MB final
---------------------- --------------------------------------- -----------------------
6000 46.875000 2017-10-04 21:03:43.100
user object pages used user object space in MB final
---------------------- --------------------------------------- -----------------------
6000 46.875000 2017-10-04 21:03:43.450
user object pages used user object space in MB final
---------------------- --------------------------------------- -----------------------
6000 46.875000 2017-10-04 21:03:43.650
user object pages used user object space in MB final
---------------------- --------------------------------------- -----------------------
6000 46.875000 2017-10-04 21:03:43.767
user object pages used user object space in MB final
---------------------- --------------------------------------- -----------------------
6000 46.875000 2017-10-04 21:03:43.993
user object pages used user object space in MB final
---------------------- --------------------------------------- -----------------------
6000 46.875000 2017-10-04 21:03:44.103
user object pages used user object space in MB final
---------------------- --------------------------------------- -----------------------
6000 46.875000 2017-10-04 21:03:44.213
user object pages used user object space in MB final
---------------------- --------------------------------------- -----------------------
6000 46.875000 2017-10-04 21:03:44.437
user object pages used user object space in MB final
---------------------- --------------------------------------- -----------------------
6000 46.875000 2017-10-04 21:03:44.553
user object pages used user object space in MB final
---------------------- --------------------------------------- -----------------------
6000 46.875000 2017-10-04 21:03:44.663
user object pages used user object space in MB final
---------------------- --------------------------------------- -----------------------
6000 46.875000 2017-10-04 21:03:44.887
user object pages used user object space in MB final
---------------------- --------------------------------------- -----------------------
6000 46.875000 2017-10-04 21:03:45.003
user object pages used user object space in MB final
---------------------- --------------------------------------- -----------------------
1536 12.000000 2017-10-04 21:03:45.113
Resultados de amostra com o código sem comentários (0,11 segundos da primeira à última entrada "final"):
user object pages used user object space in MB before
---------------------- --------------------------------------- -----------------------
1536 12.000000 2017-10-04 21:07:39.807
user object pages used user object space in MB BeforeTruncate
---------------------- --------------------------------------- -----------------------
6016 47.000000 2017-10-04 21:07:39.923
user object pages used user object space in MB AfterTruncate
---------------------- --------------------------------------- -----------------------
6016 47.000000 2017-10-04 21:07:39.923
Beginning execution loop
user object pages used user object space in MB final
---------------------- --------------------------------------- -----------------------
6016 47.000000 2017-10-04 21:07:40.160
user object pages used user object space in MB final
---------------------- --------------------------------------- -----------------------
1536 12.000000 2017-10-04 21:07:40.270
Se a tabela temporária for grande o suficiente ( mais de 128 extensões ), as desalocações de página física serão adiadas e executadas por uma tarefa do sistema em segundo plano. Isso é verdade se um explícito
TRUNCATE TABLEé usado ou não.A única diferença é um pequeno detalhe de implementação. Um explícito
TRUNCATE TABLEacontece para criar uma tarefa com um temporizador mais curto do que a tarefa de descarte adiada (de outra forma idêntica) criada pela limpeza temporária da tabela:Se isso é por acidente ou design é uma incógnita. É claro que pode mudar a qualquer momento, pois esse nível de detalhe vai muito além da área de superfície do produto suportada.
Se você desabilitar o descarte adiado globalmente com um sinalizador de rastreamento (principalmente) não documentado:
...as desalocações são executadas de forma síncrona em ambos os casos, e você não verá diferença no tempo.
Eu duvido seriamente que isso faria muita diferença de qualquer maneira. Se o tempdb for dimensionado adequadamente para as necessidades de pico de sua carga de trabalho, não importa se a queda adiada ocorre após um ou três segundos. O mesmo trabalho é feito; é apenas uma pequena diferença no tempo.
Por outro lado: Se você se sentir mais confortável usando
TRUNCATE TABLEtabelas temporárias no final de seus procedimentos armazenados, vá em frente. Eu não estou ciente de nenhuma desvantagem específica para fazer isso.