SQL-Ex blog

Записи данных

• Записи данных хранятся на страницах данных.


• Записи данных хранят строки кучи (heap) или листового уровня кластеризованного индекса.


• Запись данных всегда хранит все колонки строки таблицы - либо по значению, либо по ссылке.
  
  
  
  • Если какие-либо столбцы имеют типы данных LOB (text, ntext, image, или новые типы, появившиеся в SQL Server 2005, varchar(max), varbinary(max), XML), то в записи данных хранится указатель, который указывает на текстовую запись на другой странице (корень связанного дерева, хранящего значение LOB). Имеются исключения, когда схема настроена на хранение столбцов LOB в строке (in-row), если это возможно. Т.е. когда значение LOB достаточно мало, чтобы уложиться в ограничения на размер записи данных. Это дает выгоду с точки зрения производительности, поскольку выборка из столбца LOB не потребует дополнительной операции ввода-вывода для считывания текстовой записи.
  
  
  • В SQL Server 2005 не-LOB столбцы переменной длины (например, varchar, sqlvariant) также могут храниться вне строки (off-row), что позволяет строкам таблицы иметь размер, превышающий 8060 байт (размер дисковой страницы для хранения данных). В этом случае формат хранения аналогичен хранению значений LOB - указатель в записи данных указывает на текстовую запись.
  
  
  
  


• Есть разница в том, как извлекаются столбцы из кучи и кластеризованных индексов.
  
  
  
  • В куче столбцы извлекаются в порядке, заданном оператором CREATE TABLE (разумеется, разделяемых на столбцы постоянной и переменной длины).
  
  
  • В кластеризованном индексе кластерные ключи становятся первыми физическими столбцами в записи, за которыми в порядке, заданном оператором CREATE TABLE, следуют остальные столбцы.
  
  
  
  

Перемещаемые/перемещающие записи

• Технически в куче присутствуют только записи данных.


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


• Перемещающая запись остается на своем месте и указывает на новое положение записи. Её иногда называют перемещающим корешком (forwarding-stub), т.к. все, что она содержит, это местоположение реальной записи данных.


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


• Оптимизируя обслуживание обновления некластеризованных индексов, этот механизм может вызывать дополнительные операции ввода/вывода при выполнении оператора SELECT. Это происходит потому, что запись некластеризованного индекса указывает на старое положение записи, и может потребоваться дополнительная операция ввода-вывода, чтобы прочитать строку данных из её фактического местоположения. Это аргумент в пользу кластеризованных индексов в рамках дискуссии "куча против кластеризованного индекса".

Индексные записи

• Индексные записи сохраняются на индексных страницах.


• Имеется два типа индексных записей (которые отличаются только тем, какие столбцы они хранят):
  
  
  
  1. Те, которые хранят строки некластеризованных индексов на листовом уровне некластеризованного индекса.
  
  
  2. Те, которые включают b-tree, составляющие кластеризованные и некластеризованные индексы (т.е. на индексных страницах выше листового уровня кластеризованного или некластеризованного индекса).
  
  
  
  


• Разницу между ними я более подробно объясню в следующей статье.


• Индексные записи обычно не содержат значений всех столбцов таблицы (хотя некоторые содержат, и они называются покрывающими индексами).


• В SQL Server 2005 записи некластеризованных индексов могут включать значения LOB, как включенные столбцы (при этом детали хранения аналогичны хранению записей данных), и также могут иметь данные превышающие размер записи (опять же как для записей данных).

Текстовые записи

• Текстовые записи хранятся на текстовых страницах.


• Существуют различные типы текстовых записей, которые имеют структуру дерева для сохранения значений LOB на текстовых страницах двух типов. Я объясню как они работают и связаны друг с другом в последующей статье.


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

Фантомные записи

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


• Запись помечается битом, который указывает, что это фантомная запись и не может быть физически удалена до тех пор, пока транзакция, которая привела к появлению фантомной записи, не будет зафиксирована. После фиксации транзакции она удаляется асинхронным фоновым процессом (называемом задачей ghost-cleanup) или преобразуется обратно к реальной записи посредством вставки записи с тем же самым набором ключей.

Записи других типов

• Имеются также записи, которые используют для хранения различные битовые карты распределения, промежуточные результаты операций сортировки, метаданные файлов и баз данных (например, страница заголовков файлов и страница загрузки баз данных). Опять таки, я вернусь к ним в последующих статьях (однако большая очередь выстроилась ).

Структура записи

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

Структура для несжатых записей является следующей:

• Заголовок записи
  
  
  
  • Длина 4 байта
  
  
  • 2 байта метаданных записи (тип записи)
  
  
  • 2 байта прямого указателя записи на null bitmap (битовая карта NULL-значений).
  
  
  
  


• Часть записи фиксированной длины, содержащая столбцы, хранящие данные типов фиксированной длины (например, bigint, char(10), datetime)


• Null bitmap
  
  
  
  • Два байта для числа столбцов в записи
  
  
  • Переменное число байт по биту на столбец в записи, независимо от того, допускает этот столбец NULL-значения или нет (это проще по сравнению с SQL Server 2000, который имел по одному биту на только столбец, допускающий NULL).
  
  
  • Это допускает оптимизацию при чтении столбцов с NULL.
  
  
  
  


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


• Метка версии


• Только для SQL Server 2005, 14-байтная структура, содержащая временную метку (timestamp) плюс указатель на хранение версии в базе tempdb.

Оптимизация NULL bitmap

Итак, почему null bitmap является оптимизацией?

Во-первых, наличие null bitmap устраняет необходимость хранения специальных NULL-значений для типов данных фиксированной длины. Без null bitmap, как бы вы могли сказать, содержит ли столбец NULL? Для столбцов фиксированной длины вам потребовалось бы определить специальное NULL-значение, которое ограничивает эффективный диапазон сохраняемого типа данных. Для столбцов типа varchar значением может быть пустая строка нулевой длины, поэтому просто проверка длины не работает - вам опять таки потребовалось бы специальное значение. Для всех других типов данных переменной длины вы можете просто проверять длину. Итак, нам нужна битовая карта NULL-значений.

Во-вторых, она сокращает число циклов процессора. Если нет null bitmap, тогда требуются лишние команды, выполняемые для столбцов фиксированной и переменной длины.

Для фиксированной длины:

1. Чтение значения в сохраненном столбце (возможно неудачное обращение в кэш процессора).
2. Загрузка предопределенного для данного типа данных NULL-значения (возможно неудачное обращение в кэш процессора, но только для первого чтения в случае выборки множества строк).
3. Выполнение сравнения этих двух значений.

Для переменной длины:

1. Вычисление смещения массива переменных длин.
2. Чтение числа столбцов переменной длины (возможно неудачное обращение в кэш процессора).
3. Вычисление позиции в массиве смещений переменной длины для чтения.
4. Чтение смещения столбца оттуда (возможно неудачное обращение в кэш процессора).
5. Чтение следующего смещения (возможно неудачное обращение в кэш процессора, если смещение на 4 шаге попало на границу размера строки кэша).
6. Сравнение этих смещений для проверки на совпадение.

При использовании null bitmap все, что вам нужно, это:

1. Прочитать смещение null bitmap (возможно неудачное обращение в кэш процессора).
2. Вычислить дополнительное смещение бита NULL, который вы хотите прочитать.
3. Прочитать его (возможно неудачное обращение в кэш процессора).

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

Использование DBCC IND и DBCC PAGE для исследования строки

Давайте создадим тестовую таблицу:

USE [master];

GO



IF DATABASEPROPERTY (N'recordanatomy', 'Version') > 0 DROP DATABASE [RecordAnatomy];

GO

CREATE DATABASE [RecordAnatomy];

GO



USE [RecordAnatomy];

GO



CREATE TABLE [example] ([destination] VARCHAR(100), [activity] VARCHAR(100), [duration] INT);

GO



INSERT INTO [example] VALUES ('Banff', 'sightseeing', 5);

INSERT INTO [example] VALUES ('Chicago', 'sailing', 4);

GO

И опять мы можем использовать DBCC IND для для нахождения страницы:

DBCC IND ('recordanatomy', 'example', 1);

GO

Напомню, что нам потребуется установить trace-flag, чтобы вывод DBCC PAGE шел в консоль, а не в журнал ошибок. Вывод будет содержать что-то подобное следующему:

Slot 0 Offset 0x60 Length 33



Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS



Memory Dump @0x5C76C060



00000000: 30000800 05000000 0300f802 00160021 †0..............!

00000010: 0042616e 66667369 67687473 6565696e †.Banffsightseein

00000020: 67†††††††††††††††††††††††††††††††††††g



Slot 0 Column 0 Offset 0x11 Length 5



destination = Banff



Slot 0 Column 1 Offset 0x16 Length 11



activity = sightseeing



Slot 0 Column 2 Offset 0x4 Length 4



duration = 5

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

• Байт 0 представляет собой байт TagA метаданных записи.
  
  
  
  • Это 0х30, что соответствует 0х10 (бит 4) и 0х20 (бит 5). Бит 4 означает, что запись имеет null bitmap, а бит 5 - что запись имеет столбцы переменной длины. Если бы было также установлено 0х40 (бит 6), это означало бы, что запись имеет метку версии. Установка 0х80 (бит 7) означало бы, что байт 1 имеет в ней значение.
  
  
  • Биты 1-3 байта 0 указывают тип записи . Вот возможные варианты:
    
    
    
    • 0 = первичная запись. Запись данных в куче, которая не переносилась, или запись данных на листовом уровне кластерного индекса.
    
    
    • 1 = перенесенная запись
    
    
    • 2 = переносящая запись
    
    
    • 3 = Индексная запись
    
    
    • 4 = фрагмент blob
    
    
    • 5 = фантомная индексная запись
    
    
    • 6 = фантомная запись данных
    
    
    • 7 = фантомная запись версии. Специальная 15-байтовая запись, содержащая однобайтовый заголовок записи плюс 14-байтовую метку версии, которая используется при определенных обстоятельствах.
    
    
    
    
  
  
  
  


• В нашем примере ни один из этих битов не установлен, поэтому наша запись является первичной (primary) записью. Если запись является индексной, то байт 0 имел бы значение 0х36. Напомню, что тип записи начинается с бита 1, а не 0, поэтому для получения значения типа записи в байте нужно значение, указанное выше, сдвинуть влево на 1 бит (умножение на 2).


• Байт 1 - это байт TagB метаданных записи. Он может быть либо 0х00, либо 0х01. Если 0х01, это означает запись типа фантомная перенесенная. В этом случае 0х00 - это значение, которое мы ожидали получить в байте TagA.


• Байты 2 и 3 представляют собой смещение null bitmap в записи. Это 0х0008 (многобайтные значения сохраняются, начиная с младшего байта). Это означает, что имеется 4-х байтная часть записи фиксированной длины, начинающаяся с байта 4. Мы ожидали этого, поскольку нам известна схема таблицы.


• Байты 4 - 7 есть порции фиксированной длины. Опять таки, поскольку мы знаем схему таблицы, то интерпретируем эти байты как 4-байтное целое. Без этих знаний вам придется угадывать. Таким образом 0x00000005 - это ожидаемое значение столбца duration.


• Байты 8 и 9 - это количество столбцов в записи. Мы имеем 0х0003, что правильно. При условии, что имеется только 3 столбца, null bitmap при одном бите на столбец поместится в один байт.


• Байт 10 есть null bitmap. Его значение 0xF8. Нам потребуется преобразовать это значение в двоичную форму, чтобы понять его смысл: 0xF8 = 11111000. Смысл таков - биты 0-2 представляют столбцы 1-3, и они все равны 0, что означает отсутствие NULL. Биты 3-7 пресдтавляют несуществующие столбцы, и они установлены в 1 для ясности.


• Байты 11 и 12 есть количество столбцов переменной длины в записи. Данное значение равно 0х0002, что, как мы знаем, верно. Это означает, что будет два двухбайтовых входа в массиве смещений столбцов переменной длины. Это будут байты 13-14 и 15-16, содержащие значения 0х0016 и 0х0021 соответственно. Вспомним, что входы в массиве смещений столбцов переменной длины указывают на конец значения столбца - это делается для того, чтобы знать длину каждого столбца без необходимости дополнительного хранения их длин.


• Итак, последнее смещение есть байты 15 и 16, т.е. начинаться первый столбец переменной длины должен с байта 17 (или 0х11 в шестнадцатеричной кодировке), что согласуется с дампом DBCC PAGE. Смещением конца первого столбца переменной длины является 0х0016, поэтому первое значение занимает байты с 17 по 21 включительно. Само значение равно 0х42616E6666. Мы знаем из метаданных таблицы, что первым является столбец destination (типа varchar). Преобразование в ASCII дает нам значение столбца - 'Banff'. Используя аналогичную логику, выясняем, что второе значение занимает байты 22-32 включительно и равно 'sightseeing'. Оба эти значения совпадают с нашими ожиданиями.