SQL-Ex blog

Значит ли это, что содержимое все еще видимо?

Что-то вроде того. Если вы попробуете выполнить запрос к таблице, поскольку метаданные таблицы больше не знают о его существовании, вы не можете запросить этот столбец и, следовательно, вы не видите данные.

Однако, если проверить содержимое страницы данных, мы сможем все еще видеть некоторые метаданные столбца, который только что удалили.
Мы можем увидеть, что был столбец с конкретным Offset (сдвигом), который занимал Length (physical) X (где ‘X’ - число байтов), теперь удален (DROPPED).



"Метаданные - это не данные"

Это верно. Вот что я обнаружил: если вы проведете тест с типом данных (n)varchar/(n)char, то все же сможете увидеть текст, который был в столбце/записи, проверив страницу.

Возвращаясь к основной теме этой статьи, давайте посмотрим, как мы можем проверить, что пространство не изменилось.

Поверь глазам своим - подготовка среды

Чтобы поиграть с этим и увидеть интересные вещи, давайте создадим базу данных с именем TableInternals и в ней таблицу с именем Client.

CREATE DATABASE TableInternals

GO

USE TableInternals

GO

DROP TABLE IF EXISTS Client

GO

CREATE TABLE Client

(

	Id			int NOT NULL identity(1,1),

	FirstName	varchar(50),

	DoB			datetime

)

GO

Теперь вставим 50000 записей

Все данные, которые я собираюсь вставить, будут одинаковы, в данном случае это не имеет значения, поэтому я выбрал имя (Name) Alex и дату рождения (DoB) 1900-01-01.

Для этого я буду использовать запрос, который использует набор CTE для простой генерации большого числа записей.

;WITH

    L0 AS ( SELECT 1 AS c 

            FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),

                        (1),(1),(1),(1),(1),(1),(1),(1)) AS D(c) ),

    L1 AS ( SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B ),

    L2 AS ( SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B ),

    L3 AS ( SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B ),

    Nums AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum

              FROM L3 

			)

	INSERT INTO Client (FirstName, DoB)

	SELECT TOP (50000) 'Alex', '1900-01-01'

	  FROM Nums

GO

Размер таблицы

Чтобы иметь представление и чтобы мы могли сравнить его с последующими действиями, давайте проверим текущий размер данных в таблице сразу после создания. Для этого давайте воспользуемся системной хранимой процедурой sp_spaceused:

sp_spaceused Client

Мы имеем 1440Кб данных или 180 страниц (каждая страница имеет размер 8Кб).

Как найти страницу данных?

Чтобы иметь возможность проверить содержимое страницы, сначала нам необходимо узнать, какие страницы принадлежат конкретному файлу.

Для этого мы можем либо использовать команду DBCC IND - которой нет в официальной документации:

-- Синтаксис

DBCC IND (database_name, table_name, index_id);

или, начиная с SQL Server 2012, вы можете также использовать - тоже недокументированную - динамическую административную функцию (DMF) sys.dm_db_database_page_allocations.

-- Синтаксис

SELECT * FROM sys.dm_db_database_page_allocations(@DatabaseId, @TableId, @IndexId, @PartionId, @Mode)

Просто хочу напомнить, что недокументированные команды следует использовать с осторожностью, т.к. они официально не поддерживаются Microsoft и могут создавать проблемы в вашей базе данных.

Примеры

Используя наш пример, давайте выполним следующий код T-SQL:

-- Старый способ посмотреть содержимое страницы

DBCC IND ('TableInternals', 'Client', 1);

Или, как говорилось выше, вы можете также использовать

-- 2012 или новее, больше информации

SELECT *

FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.Client'), NULL, NULL, 'DETAILED')

Самые внимательные читатели заметят, что вывод DMF содержит 185 строк (а вывод DBCC IND возвращает только 181). Это означает, что таблице принадлежит 185 страниц. "Cláudio, но раньше вы говорили о 180 страницах, так ведь?" - правильно 180 страниц данных. В этом случае остальные 5 страниц распределяются следующим образом:

• 4 страницы принадлежат, но не используются - ссылка на неиспользуемое (unused) пространство: 4 страницы х 8Кб = 32Кб.


• 1 страница - IAM_PAGE, которая показана в index_size даже технически не является индексом.

Замечание. Как можно увидеть, число столбцов в результатах немного отличается, новый метод извлекает больше столбцов/данных по сравнению с DBCC IND.

Проверка содержимого страницы данных

Теперь мы можем просмотреть несколько страниц типа DATA_PAGE (PageType = 1 в выводе DBCC IND или page_type = 1 для DMF) и их ID (Отметим, что возвращаемые ID страницы могут отличаться для вашего сервера). Давайте выберем одну и используем ее в новой команде.

В этом случае нам необходимо использовать команду DBCC PAGE. Это еще одна недокументированная команда. Вот ее синтаксис:

DBCC PAGE ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

Если вы хотите больше узнать об этой команде, обратитесь к статье "Внутри Storage Engine: использование DBCC PAGE и DBCC IND для выяснения, происходит ли откат разделения страниц", а чтобы понять результаты - к статье "Внутри Storage Engine: анатомия записи" - обе от Paul Randal.

Продолжение примера

Выполнив следующий код T-SQL, мы сможем получить дамп страницы данных в консоли

DBCC TRACEON (3604);

GO

DBCC PAGE ('TableInternals', 1, 544, 3);

GO

Флаг трассировки 3604 используется для того, чтобы сделать вывод DBCC PAGE в консоль, а не в журнал ошибок. Что касается параметров DBCC PAGE, printopt=3 означает, что мы получим заголовок страницы плюс подробную построчную интерпретацию.

Если прокрутить результаты, мы найдем значения каждого столбца в записи.



Замечание. Имеется также DMF, которая может вернуть некоторую информацию о странице в базе данных. Если используется SQL Server 2019 или более поздняя версия DMF sys.dm_db_page_info даст вам информацию о заголовке страницы (но не содержимое/записи на странице). Она документирована и в данный момент поддерживается! Проверьте здесь.

Удаление столбца

Давайте теперь удалим столбец DoB:

ALTER TABLE Client

DROP COLUMN DoB

GO

Каков фактический размер данных?

Мы только что удалили столбец, который занимал 8 байт в каждой записи. Т.к. имеется 50000 записей в таблице, мы должны освободить пространство, верно? Ответ отрицательный!

Если вы снова выполните команду sp_spaceused, то обнаружите, что ничего не изменилось

sp_spaceused Client

Мы по-прежнему видим те же 1440 Кб данных.

Как поменялось содержимое страницы?

Давайте снова получим дамп той же страницы, чтобы проверить изменения.

DBCC TRACEON (3604);

GO

DBCC PAGE ('TableInternals', 1, 544, 3);

GO

Вот что мы получаем:



Как упоминалось ранее, мы можем теперь увидеть, что имеется столбец с определенным смещением 0х8, который теперь имеет длину (Length) 0, но физическую длину (Length (physical)) 8 и то, что раньше было имя столбца = дата/время, поменялось на DROPPED = NULL.

Как мы можем вернуть пространство?

Ответ прост, а вот возможность сделать это — уже совсем другая история.

Давайте начнем с ответа. Перестройка. Да, вам потребуется перестроит таблицу/индекс, чтобы освободить пространство, которое помечено для повторного использования.

-- Давайте вычистим удаленный столбец и наведем порядок.

ALTER TABLE Client REBUILD 

И теперь, если вы снова проверите используемое пространство, то сможете увидеть разницу.

Для этого нам нужно снова выполнить следующее:

sp_spaceused Client

Это показывает, что мы восстановили 400 Кб данных, что означает меньше на 50 страниц.

Почему это может быть непростым делом?

Есть много препятствий при работе с большими таблицами и/или небольшими окнами обслуживания.

Бесплатных обедов не бывает! В зависимости от версии/редакции используемого SQL Server эта операция может продолжаться дольше, чем ожидалось.

Если вы работает на Standard Edition, помните, что вы не можете создавать/перестраивать индексы с ONLINE=ON, и эта операция является однопоточной (нет параллелизма).

С другой стороны, если вы работаете с Enterprise Edition, вы можете использовать ONLINE=ON и параллелизм, но знайте, что для выполнения операции онлайн может потребоваться больше места в журнале транзакций. Если вы работаете с SQL Server 2017+, я рекомендую обратить внимание на возобновляемый индекс, поскольку он:

Позволяет выполнять усечение журналов транзакций в зависимости от операции создания или перестройки индекса.

Выводы

В этой статье мы увидели, как использовать некоторые недокументированные команды в T-SQL, чтобы иметь возможность увидеть содержимое страницы данных. Затем мы прошлись по ситуации с удалением столбца и выяснили, почему мы не видим освобождения пространства при этом. Наконец, мы увидели как можно вернуть это пространство.

Сказанное относится только к таблицам кучи и кластеризованным таблицам. Мы не охватили все сценарии, и вот что вы можете сами исследовать:

• Типы данных, которые при записи размещаются на ROW_OVERFLOW_DATA или LOB_DATA.


• Некластеризованные индексы.

Ссылки по теме
1. Анатомия записи
2. Кучи в SQL Server: часть 1 - основы
3. Об использовании индексов