SQL-Ex blog

Основы: Типы единиц размещения, ограничения на размеры страниц и строк

Физической единицей хранения в SQL Server является страница. Каждая страница имеет фиксированный размер 8К байтов. Где это возможно, данные сохраняются построчно в пределах этих "страниц". Строка не может выходить за границы страницы. Если пространство, выделенное типу данных, фиксировано, или если данные переменной длины достаточно малы для того, чтобы строка поместилась, используется базовый тип единицы размещения "IN_ROW_DATA". Однако есть два способа хранить данные, которые иначе вышли бы за границы страницы.

1. Когда единственная строка становится слишком большой, чтобы храниться in-row, данные могут быть перегружены на страницы "ROW_OVERFLOW_DATA".
2. Когда единственный столбец хранит более 8000 байтов, или если разработчик решил применить такое поведение, данные сохраняются на страницах "LOB_DATA".

Поясним на примерах.

Исследование пределов размера строки и страницы

Единственный символьный столбец с VARCHAR(n) и VARCHAR(MAX)

8000 символов

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

CREATE TABLE demo.OneColVarcharN   (Col1 VARCHAR(8000));

CREATE TABLE demo.OneColVarcharMax (Col1 VARCHAR(MAX));

 

INSERT INTO  demo.OneColVarcharN   (Col1)

SELECT REPLICATE('x', 8000);

 

INSERT INTO  demo.OneColVarcharMax (Col1)

SELECT REPLICATE('x', 8000); 

После каждой вставки мы будем использовать варианты следующего запроса, который покажет тип страницы, куда были загружены наши строки:

SELECT

    OBJECT_NAME([object_id])        AS TableName,

    alloc_unit_type_desc            AS AllocUnitTp,

    page_count                      AS PgCt,

    avg_page_space_used_in_percent  AS AvgPgSpcUsed,

    record_count                    AS RcdCt,

    min_record_size_in_bytes        AS TableName,,

    max_record_size_in_bytes        AS MaxRcdSz,

    forwarded_record_count          AS FwdRcdCt

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL , 'DETAILED'); 

Результата запроса показывают, что только страницы "IN_ROW_DATA" были записаны для обеих таблиц, и эти страницы заполнены на 98,97%.

TableName	AllocUnitTp	PgCt	AvgPgSpcUsed	RcdCt	MinRcdSz	MaxRcdSz	FwdRcdCt
OneColVarcharMax	IN_ROW_DATA	1	98.97	1	8011	8011	0
OneColVarcharN	IN_ROW_DATA	1	98.97	1	8011	8011	0

8001 символов

Ясно, что мы не можем вставить 8001 символов в 8000-символьный столбец, но мы можем это сделать для столбца VARCHAR(MAX).

INSERT INTO demo.OneColVarcharMax (Col1)

SELECT REPLICATE(CONVERT(VARCHAR(MAX), 'x'), 8001); 

Результаты этого запроса показывают, что была сделана запись как на страницы "IN_ROW_DATA", так и "LOB_DATA" страницы.

TableName	AllocUnitTp	PgCt	AvgPgSpcUsed	RcdCt	MinRcdSz	MaxRcdSz	FwdRcdCt
OneColVarcharMax	IN_ROW_DATA	1	0.43	1	35	35	0
OneColVarcharN	LOB_DATA	1	99.02	1	8015	8015	NULL

Обратите внимание, что запись "in row" очень мала. В данном случае там хранится только 24-байтовый указатель. Вся символьная строка перемещена на LOB-страницу.

Итак, SQL Server сохраняет обычные столбцы VARCHAR(n) и VARCHAR(MAX) в "in row" по умолчанию. Когда VARCHAR(MAX) превышает 8000 символов, указатель записывается в "in row", а строка сохраняется на страницах "LOB".

Два символьный столбца с VARCHAR(n) и VARCHAR(MAX)

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

8000 символов

Как и ожидалось, две строки по 4000 символов отлично умещаются в обеих таблицах.

CREATE TABLE demo.TwoColVarcharN   (Col1 VARCHAR(8000), Col2 VARCHAR(8000));

CREATE TABLE demo.TwoColVarcharMax (Col1 VARCHAR(MAX),  Col2 VARCHAR(MAX));

 

INSERT INTO demo.TwoColVarcharN   (Col1, Col2)

SELECT REPLICATE('x', 4000), REPLICATE('x', 4000);

 

INSERT INTO demo.TwoColVarcharMax (Col1, Col2)

SELECT REPLICATE('x', 4000), REPLICATE('x', 4000); 

Результаты запроса показывают, что только страницы "IN_ROW_DATA" были использованы для записи обеих таблиц, при этом страницы заполнены на 99,00%.

8060 символов

Максимальное число байтов на страницу составляет 8060. Давайте посмотрим, как это работает.

Фактически не требуется всех 8060 символов, чтобы заполнить эту страницу. Только 8047 символов записываются на страницы "in row", а, начиная с 8048 символов, они записываются как на "in row" страницы, так и на “row overflow” страницы (для VARCHAR(n)) или на “LOB” страницы (для VARCHAR(MAX)).

Результаты для 8047 символов (4023 и 4024 символов)

TableName	AllocUnitTp	PgCt	AvgPgSpcUsed	RcdCt	MinRcdSz	MaxRcdSz	FwdRcdCt
TwoColVarcharMax	IN_ROW_DATA	1	99.58	1	8060	8060	0
TwoColVarcharN	IN_ROW_DATA	1	99.52	1	8060	8060	0

Результаты для 8048 символов (4024 и 4024 символов)

TableName	AllocUnitTp	PgCt	AvgPgSpcUsed	RcdCt	MinRcdSz	MaxRcdSz	FwdRcdCt
TwoColVarcharMax	IN_ROW_DATA	1	50.17	1	4061	4061	0
TwoColVarcharMax	LOB_DATA	1	49.89	1	4038	4038	NULL
TwoColVarcharN	IN_ROW_DATA	1	50.17	1	4061	4061	0
TwoColVarcharN	LOB_DATA	1	49.98	1	4038	4038	NULL

Заметим, что при 8047 символах размер записи составляет в точности 8060 байтов. Что находится в этих 23 байтах? Каждая страница данных имеет заголовок, в котором содержится метаинформация, такая как идентификатор файла базы данных, номер текущей страницы, номера предыдущей и следующей страницы, число свободных байтов на странице и т.д.

200000 символов

Для таблицы с VARCHAR(MAX) результаты в целом те же, даже если ширина столбца далеко превышает 8000-символьный предел. Здесь мы записываем две 100000-символьных строки.

INSERT INTO demo.TwoColVarcharMax (Col1, Col2)

SELECT

    REPLICATE(CONVERT(VARCHAR(MAX), 'x'), 100000),

    REPLICATE(CONVERT(VARCHAR(MAX), 'x'), 100000);

Результаты

TableName	AllocUnitTp	PgCt	AvgPgSpcUsed	RcdCt	MinRcdSz	MaxRcdSz	FwdRcdCt
TwoColVarcharMax	IN_ROW_DATA	1	0.75	1	61	61	0
TwoColVarcharMax	LOB_DATA	27	91.89	28	228	8054	NULL

Теперь мы имеем единственную страницу, на которой находятся два указателя и 27 страниц, хранящих большие символьные строки.

Сравнение хранения LOB в строке (In-Row) и вне строки (Out-of-Row)

Для типов больших данных, подобных VARCHAR(MAX), SQL Server позволяет хранить данные in-row (до 8000 байтов) или out-of-row.

Мы будем использовать две новые таблицы, одну in-row, а другую out-of-row:

CREATE TABLE demo.OneColVarcharMaxIn  (Col1 VARCHAR(MAX));

CREATE TABLE demo.OneColVarcharMaxOut (Col1 VARCHAR(MAX)); 

Чтобы изменить для столбца VARCHAR(MAX) способ хранения со значения по умолчанию (in row) на out-of-row, выполните оператор:

EXEC sp_tableoption 'demo.OneColVarcharMaxOut', 'large value types out of row', 1; 

1 символ

Запись единственного символа в каждый столбец в этих таблицах показывает интересный результат:

TableName	AllocUnitTp	PgCt	AvgPgSpcUsed	RcdCt	MinRcdSz	MaxRcdSz	FwdRcdCt
OneColVarcharMaxIn	IN_ROW_DATA	1	0.15	1	12	12	0
OneColVarcharMaxOut	IN_ROW_DATA	1	0.33	1	27	27	0
OneColVarcharMaxOut	LOB_DATA	1	1.04	1	84	84	NULL

Таблица in-row создала только одну страницу. Таблица out-of-row создала одну страницу для указателя и одну страницу для строки out-of-row. Обратите внимание на разницу в количестве байтов: запись in-row занимает 12 байтов, а запись out-of-row - 111 байтов.

Это поведение демонстрирует две отрицательные стороны хранения out-of-row, когда записываются маленькие записи:

1. Влияние на множество страниц вне зависимости от размера записываемых данных.
2. Требуется избыточное пространство хранения.

8000 символов

TableName	AllocUnitTp	PgCt	AvgPgSpcUsed	RcdCt	MinRcdSz	MaxRcdSz	FwdRcdCt
OneColVarcharMaxIn	IN_ROW_DATA	1	98.97	1	8011	8011	0
OneColVarcharMaxOut	IN_ROW_DATA	1	0.33	1	27	27	0
OneColVarcharMaxOut	LOB_DATA	2	50.02	2	84	8014	NULL

Заметим, что таблица out-of-row записывает две страницы вместо одной (столбец "PgCt"). Фактически требуется только 65 символов в таблице out-of-table, чтобы появилась вторая страница.

8001 символов

TableName	AllocUnitTp	PgCt	AvgPgSpcUsed	RcdCt	MinRcdSz	MaxRcdSz	FwdRcdCt
OneColVarcharMaxIn	IN_ROW_DATA	1	0.43	1	35	35	0
OneColVarcharMaxIn	LOB_DATA	1	99.02	126	8015	8015	NULL
OneColVarcharMaxOut	IN_ROW_DATA	1	0.33	1	27	27	0
OneColVarcharMaxOut	LOB_DATA	2	98.43	127	84	8015	NULL

Как только количество байтов превысит 8000, таблица in-row запишет на страницу LOB.

1000000 символов

TableName	AllocUnitTp	PgCt	AvgPgSpcUsed	RcdCt	MinRcdSz	MaxRcdSz	FwdRcdCt
OneColVarcharMaxIn	IN_ROW_DATA	1	0.43	1	35	35	0
OneColVarcharMaxIn	LOB_DATA	126	98.42	126	2020	8054	NULL
OneColVarcharMaxOut	IN_ROW_DATA	1	0.33	1	27	27	0
OneColVarcharMaxOut	LOB_DATA	126	98.43	127	84	8054	NULL

С такими большими строками результаты поведения по умолчанию (in-row) и out-of-row очень похожи.

Производительность

Теперь посмотрим, как эти различные варианты влияют на производительность. Напишем несколько операторов insert и запишем результаты с различными установками и значениями.

INSERT от 0 до 8000 символов

Протестируем сначала производительность процесса, который вставляет строки, которые укладываются в VARCHAR(n).

Основная идея заключается во вставке 10000 строк данных и замера времени. Я запускал тесты на своем ПК. Это машина не серверного класса, но весьма мощная. Все тесты выполнялись на SQL Server 2008 R2 и Windows 7 Enterprise.

Результаты представлены в микросекундах на вставку для записи длиной от 0 до 8000 символов. Ссылку на набор скриптов тестирования можно найти в конце статьи (см. оригинальную статью).



Данные, на основании которых строились графики, приводятся в приложении к статье.

Представленные результаты показывают, что VARCHAR(n) немного быстрей, чем VARCHAR(MAX) (in-row), только для строк от 0 до 1000 строк символов. При 2000 символах и выше VARCHAR(MAX) (in-row) наиболее быстрый. Это неожиданный результат.

Интуитивно ожидалось, что VARCHAR(n) должен быть быстрей, чем VARCHAR(MAX), и в некоторых случаях это так и есть. Я думаю, что при малом расхождении мы не можем полагаться на точность измерений.

INSERT - больше 8000 символов

Эти тесты ограничиваются VARCHAR(MAX), но сравниваются in-row с out-of-row.

Результаты в микросекундах на вставку представлены для записей длиной от 9000 до 30000 символов.



Данные для графика приведены в приложении к статье.

UPDATE - предел столбца

Когда столбец VARCHAR(MAX) in-row превышает предел 8000 символов, данные перемещаются на страницу LAB_DATA. Скрипт Allocation Units – UPDATE.sql тестирует 8000-, затем 8001-, затем 8000-символьный перенос данных. Этот тест демонстрирует пемещение данных со страницы IN_ROW_DATA на страницу LOB_DATA, а затем обратно для in-row таблицы.

Эти операции могут быть дорогими, но насколько? Сделайте быстрое предсказание прежде, чем читать дальше.

Следующий скрипт PerfTest – UPDATE.sql тестирует как 8000-8001 сценарий, так и 8001-8000 сценарий. Результаты реально удивительны. Я неоднократно проверял код, чтобы убедиться в отсутствии ошибок, поскольку результаты никак не согласовывались с моими ожиданиями.

Результаты теста UPDATE

In/Out	From	To	Elapsed Mcs	Penalty vs Out-of-Row
In-Row	8000	8001	34238839	82%
Out-of-row	8000	8001	18804768
In-Row	8001	8000	9969989	-47%
Out-of-row	8001	8000	18804768

Как ожидалось, обновление 8000-8001 занимало больше времени, для in-row, чем для out-of-row. Удивительно, что обновление 8001-8000 оказалось быстрей для in-row. Опираясь на эти результаты, внутри SQL Server должен быть некий ускоритель, оптимизирующий перемещение данных из страницы LOB_DATA на страницу IN_ROW_DATA.

UNICODE

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

Рекомендации

Не используйте повсеместно VARCHAR(MAX)

Есть упрощенная техника проектирования, предполагающая использование VARCHAR(MAX) для каждого символьного столбца. Тесты показали, что использование VARCHAR(MAX) (in-row) вместо VARCHAR(n) вызывает проблемы производительности для строк, содержащих менее 8000 символов. Кроме того, имеется масса причин ограничить длину строки в базе данных.

Проблемы UI

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

Производительность - запись и обновление длинных строк

По результатам тестов производительности вы можете увидеть, что на запись длинных строк определенно влияет задержка записи. Хотя запись строки любого размера (даже нулевого) занимает время, запись длинных строк занимает больше времени. Например, при использовании VARCHAR(n) запись 1000 символов занимает в среднем 217 микросекунд, в то время как запись 8000 символов занимает в среднем 448 микросекунд.

Сжатие

Я полагаю, что наиболее побудительным мотивом использовать сжатие, является стремление избежать строк свыше 8000 символов. LOB-данные никогда не сжимаются.
Если LOB-данные сохраняются в строке (in row), они будут сжиматься, но только тогда, когда вы используете для таблицы сжатие уровня PAGE. Если вы используете сжатие уровня ROW, то LOB-данные никогда не будут сжиматься, вне зависимости от того, сохраняются они в строке или вне строки.

Выводы

• Где это подходит, используете VARCHAR(n), а не VARCHAR(MAX)




• даже если нет прироста производительности, в целях хорошего проектирования и


• поскольку данные VARCHAR(MAX) не сжимаются.




• Сохранение больших строк занимает больше времени, чем небольших.


• Обновление значения VARCHAR(MAX) (in row) от значения ниже 8000 к значению свыше 8000 будет относительно медленным, хотя разница для отдельной транзакции не будет заметной.


• Обновление in-row значения VARCHAR(MAX) от значения свыше 8000 к значению ниже 8000 будет быстрей, чем если бы таблица была установлена на сохранение данных out-of-row.


• Использование опции out-of-row для VARCHAR(MAX) будет замедлять запись, пока строки не очень длинны.