Когда после TRUNCATE страницы станут доступны для повторного использования?

Автор: Paul Randal, Search Engine Q&A #10: When are pages from a truncated table reused?

Это вопрос, который мне когда-то прислали — если таблица усекается внутри транзакции, что защищает целостность страниц таблицы в случае отката транзакции? Давайте выясним.

Сначала я создам простую таблицу для экспериментов:

CREATE TABLE [TruncateTest] (
    [c1] INT IDENTITY,
    [c2] CHAR (8000) DEFAULT 'A');
GO
SET NOCOUNT ON;
GO
DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 20)
BEGIN
    INSERT INTO [TruncateTest] DEFAULT VALUES;
    SELECT @a = @a + 1;
END;
GO

Мы можем увидеть, какие страницы и экстенты выделены таблице, используя недокументированную команду DBCC IND:

DBCC IND ([test], [TruncateTest], 0);
GO

PageFID PagePID
------- -------
1       193
1       192
1       194
1       195
1       196
1       197
1       198
1       199
1       200
1       224
1       225
1       226
1       227
1       228
1       229
1       230
1       231
1       232
1       233
1       234
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Я отредактировал вывод, оставив только идентификаторы страниц, и мы можем видеть, что этой таблицей используется 4 экстента (начинающиеся на страницах (1:192), (1:200), (1:224) и (1:232)). Теперь, если мы усечём таблицу в транзакции, что покажет DBCC IND?

BEGIN TRAN;
GO
TRUNCATE TABLE [TruncateTest];
GO
DBCC IND ([test], [TruncateTest], 0);
GO

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Похоже, что таблице не выделено никаких страниц. Так где же они? Давайте проверим, какие есть блокировки. Вместо использования sp_lock, я буду использовать заменяющее его динамическое представление управления sys.dm_tran_locks:

SELECT
    [resource_type],
    [resource_description],
    [request_mode]
FROM
    sys.dm_tran_locks
WHERE
    [resource_type] IN (N'EXTENT', N'PAGE');
GO

resource_type  resource_description  request_mode
-------------- --------------------- -------------
EXTENT         1:200                 X
PAGE           1:198                 X
PAGE           1:199                 X
PAGE           1:196                 X
PAGE           1:197                 X
PAGE           1:194                 X
PAGE           1:195                 X
PAGE           1:192                 X
PAGE           1:193                 X
EXTENT         1:192                 X
PAGE           1:200                 X
EXTENT         1:232                 X
EXTENT         1:224                 X

Упс — все страницы и экстенты заблокированы. Таблица больше не показывает их как распределённые, но поскольку у них исключительные блокировки, подсистема распределения не может полностью освободить их до тех пор, пока эти блокировки не будут сняты (когда транзакция зафиксируется). Вот ответ — они не могут быть повторно использованы до тех пор, пока не будут действительно освобождены. Если происходит откат транзакции, страницы просто снова помечаются как распределённые.