Почему в моей таблице-куче много пустых страниц?

Автор: Paul Randal, SQLskills SQL101: Why does my heap have a bunch of empty pages?

У SQLskills есть инициатива вести блог на базовые темы, которую мы называем SQL101. Мы все пишем о вещах, которые часто делаются неправильно, технологиях, используемых неверно, или случаях, когда существует множество заблуждений, ведущих к серьёзным проблемам.

Вот вопрос, который мне часто задают (перефразирую):

«У меня есть большая таблица-куча, в которой пространство не освобождается, когда я удаляю большое количество записей, но когда я сжимаю базу данных, размер кучи уменьшается. Можете объяснить?»

Такое поведение соответствует тому, как работает SQL Server, но оно довольно неинтуитивно. Когда страница в индексе становится пустой, она всегда освобождается, поскольку пустые страницы не допускаются в структуре индекса SQL Server начиная с SQL Server 2005. Однако структура кучи отличается, и, как следствие, отличается и поведение.

Всякий раз, когда строка удаляется в куче, обычно страница, содержащая эту строку, не становится пустой. Однако если страница кучи, на которой хранилась строка, становится пустой в результате удаления, эта страница не может быть освобождена из таблицы, если не удерживается монопольная (X) блокировка таблицы (чтобы удалить страницу из внутреннего механизма «отслеживания» свободного пространства, который существует для куч). Нечасто встречается блокировка таблицы уровня X, если только не произошла эскалация уровня блокировок (потому что вы удаляете достаточно строк, чтобы вызвать повышение с отдельных блокировок X на строки до единой блокировки X на таблицу) или если вы, например, явно используете указание TABLOCK в операторе удаления. Но, поскольку оба эти обстоятельства маловероятны, пустая страница кучи обычно не может быть освобождена.

Существует статья базы знаний, описывающая этот феномен: KB 913399. Однако статья базы знаний ссылалась только на версии вплоть до SQL Server 2005 включительно, но такое поведение существует во всех версиях, и его очень легко воспроизвести, если вы хотите убедиться в этом сами. Кроме того, если вы используете один из вариантов изоляции моментальных снимков, то даже использование TABLOCK не позволит страницам быть освобождёнными.

Приведённый ниже скрипт создаст базу данных, создаст таблицу с одной строкой на страницу кучи, покажет вам, сколько страниц и строк, и насколько заполнена каждая страница. Затем он удалит все строки и покажет, что страницы всё ещё там, без строк и пустые. Попробуйте!

CREATE DATABASE [EmptyHeapTest];
GO
USE [EmptyHeapTest];
GO

CREATE TABLE [test] ([c1] INT IDENTITY, [c2] CHAR (8000) DEFAULT 'a');
GO

SET NOCOUNT ON;
GO
-- Вставляем 1000 строк, что выделит 1000 страниц
INSERT INTO [test] DEFAULT VALUES;
GO 1000

-- Сколько страниц и насколько они заполнены?
SELECT [page_count], [record_count], [avg_page_space_used_in_percent]
FROM sys.dm_db_index_physical_stats (DB_ID (N'EmptyHeapTest'), OBJECT_ID (N'test'), 0,  DEFAULT, 'DETAILED');
GO

page_count           record_count         avg_page_space_used_in_percent
-------------------- -------------------- ------------------------------
1000                 1000                 98.974549048678

-- Удаляем все строки
DELETE FROM [test];
GO

-- Были ли все страницы удалены?
SELECT [page_count], [record_count], [avg_page_space_used_in_percent]
FROM sys.dm_db_index_physical_stats (DB_ID (N'EmptyHeapTest'), OBJECT_ID (N'test'), 0,  DEFAULT, 'DETAILED');
GO

page_count           record_count         avg_page_space_used_in_percent
-------------------- -------------------- ------------------------------
1000                 0                    0

Пустые страницы будут повторно использоваться последующими вставками (в ту же таблицу, конечно), но если пространство не будет повторно использоваться после массового удаления в куче, вы можете рассмотреть возможность использования указания TABLOCK, чтобы позволить пустым страницам быть освобождёнными и сделать пространство доступным для использования другими объектами в базе данных.

Другой вариант — просто использовать кластерный индекс вместо кучи или, если куча необходима, вы можете перестроить кучу с помощью ALTER TABLE ... REBUILD (эта возможность была добавлена в SQL Server 2008 для поддержки включения сжатия на куче), с оговоркой, что это приведёт к перестроению всех некластерных индексов таблицы.

В крайнем случае (на мой взгляд) вы можете вернуть пустое пространство кучи с помощью операции сжатия. Сжатие не освобождает пространство внутри страниц при их перемещении (за исключением уплотнения LOB-страниц по ходу выполнения — с переменным успехом в зависимости от версии и сборки, см. KB 2967240), но оно удаляет пустые страницы вместо их перемещения. Это эффективно уменьшит размер кучи после массового удаления, но с обычными оговорками о том, что сжатие вызывает фрагментацию индексов и обычно является дорогой, медленной операцией.

Теперь вы знаете, почему у вас может быть много пустых страниц в ваших кучах!