Dlaczego obcinanie tabeli tymczasowej na końcu procedury składowanej, która powoduje, że zwalnia ona miejsce w tempdb szybciej?

SQL Server buforuje tabele tymczasowe utworzone w ramach procedur przechowywanych i po prostu zmienia ich nazwy po zakończeniu procedury, a następnie jej wykonaniu. Moje pytanie dotyczy momentu zwolnienia przestrzeni tempdb. Czytałem, że tabela jest obcinana na końcu procedury . Czytałem w komentarzach, że jest to obsługiwane na podstawie sesji i widziałem pytanie o to, czy konieczne jest czyszczenie w odpowiedzi na MSDN . Ale co jeśli nigdy nie zostanie wykonane dwukrotnie w tej samej sesji?

Słyszałem również, że istnieje proces usuwania śmieci w tle, który zwalnia to miejsce, gdy stół jest poza zakresem.

Obcinanie tabeli temp na końcu tworzonej procedury składowanej wydaje się powodować, że przestrzeń używana przez tabelę w tempdb do zwolnienia danych jest szybsza niż w przypadku braku instrukcji obcięcia, pomimo przeciwnych oczekiwań. Dlaczego?

Jakie byłyby implikacje dotyczące względnej wydajności używania lub nie używania takiej skróconej instrukcji? Podczas korzystania z izolacji SNAPSHOT, tempdb jest często obciążony i wydaje mi się, że zwolnienie miejsca używanego w tempdb z dużej tabeli temp, tak szybko jak to możliwe, zapobiegnie niepotrzebnemu wzrostowi tempdb. Czy te potencjalne oszczędności przestrzeni kosztowałyby wydajność?

Oto kod do odtworzenia problemu (głównie z @TheGameiswar, z pewnymi zmianami):

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

Skomentowane linie pozostawiono w komentarzach dla niektórych przebiegów, a dla innych w komentarzach. Kiedy TRUNCATEkomentarz został skomentowany, tempdb.sys.dm_db_file_space_usageminęło od 2,25 do 4,5 sekundy, zanim wyniki zapytania (4472 więcej stron i 34,9375 MB więcej) pasowały do wyniku przed wykonaniem procedury. Gdy linie (łącznie z TRUNCATE) nie były komentowane, zajęło to tylko około 0,11 - 0,9 sekundy. Te wyniki pochodzą z systemu na żywo, z niewielkim wzrostem danych w tabeli źródłowej podczas tego eksperymentu.

Przykładowe dane wyjściowe z zakomentowanym kodem (2,69 sekundy od pierwszego do ostatniego „końcowego” wpisu):

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

Przykładowe wyniki z kodem bez komentarza (0,11 sekundy od pierwszego do ostatniego „końcowego” wpisu):

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

— Mark Freeman
źródło

Obcinanie tabeli temp na końcu tworzonej procedury składowanej wydaje się powodować, że przestrzeń używana przez tabelę w tempdb do zwolnienia danych jest szybsza niż w przypadku braku instrukcji obcięcia, pomimo przeciwnych oczekiwań. Dlaczego?

Jeśli tabela tymczasowa jest wystarczająco duża ( ponad 128 zakresów ), fizyczne zwolnienia stron są odraczane i wykonywane przez zadanie systemowe w tle. Dzieje się tak niezależnie od tego, czy TRUNCATE TABLEużyto wyrażenia jawnego, czy nie.

Jedyną różnicą jest drobny szczegół implementacji. Wyraźnie TRUNCATE TABLEzdarza się, aby utworzyć zadanie z krótszym zegarem niż (inaczej identyczne) zadanie odroczonego upuszczania utworzone przez tymczasowe czyszczenie tabeli:

Czy to przypadek, czy projekt, można się domyślić. Może oczywiście ulec zmianie w dowolnym momencie, ponieważ ten poziom szczegółowości wykracza daleko poza obszar obsługiwanego produktu.

Jeśli globalnie wyłączysz odroczone upuszczanie za pomocą (głównie) nieudokumentowanej flagi śledzenia:

DBCC TRACEON (671, -1);

... zwolnienia są wykonywane synchronicznie w obu przypadkach i nie widać różnicy w czasie.

Jakie byłyby implikacje dotyczące względnej wydajności używania lub nie używania takiej skróconej instrukcji? Podczas korzystania z izolacji SNAPSHOT, tempdb jest często obciążony i wydaje mi się, że zwolnienie miejsca używanego w tempdb z dużej tabeli temp, tak szybko jak to możliwe, zapobiegnie niepotrzebnemu wzrostowi tempdb. Czy te potencjalne oszczędności przestrzeni kosztowałyby wydajność?

Poważnie wątpię, czy i tak przydałoby się to wiele. Jeśli tempdb jest odpowiednio dostosowany do szczytowych potrzeb obciążenia, to czy odroczony spadek nastąpi po jednej czy trzech sekundach nie powinno mieć znaczenia. Ta sama praca jest wykonywana; to tylko niewielka różnica w czasie.

Z drugiej strony: jeśli czujesz się bardziej komfortowo przy TRUNCATE TABLEtymczasowych stołach pod koniec procedur przechowywanych, idź z tym. Nie jestem świadomy żadnego konkretnego minusu robienia tego.

— Paul White 9
źródło