Главная

SQL Server. Storage Engine. Страницы данных

В данной статье я хочу описать внутренние механизмы движка MS SQL Server по хранению данных и механику данного процесса. Это очень полезно для изучения особенностей работы SQL Server и для собственной проверки гипотез и утверждений о том, как работает SQL Server. Как говорится, доверяй, но проверяй.

Структура таблицы

Рассмотрим структуру таблицы в SQL Server.
Каждая таблица или индекс хранит свою информацию в партициях (partition, до 15.000 для sql server 2012). Каждая партиция может содержать один объект  B-дерева (b-tree, если быть точным в виде B+ дерева) или кучу(heap), отсюда, данные структуры называют общим именем hobt (heap or b-tree). Между партицией и hobt-структурой существует отношение один к одному. В зависимости от типа данных колонок таблицы или индекса, hobt может состоять из трех наборов страниц (allocation unit), каждая из которых образует цепочку страниц (iam chain):
  • IN_ROW_DATA (практически все типы данных, кроме описанных ниже)
  • ROW_OVERFLOW_DATA (типы переменной длины, вместе с которыми запись не помещается на страницу)
  • LOB_DATA ( nvarchar(max), filestream, xml, varbinary)


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

select  
    object_Name(i.object_Id) obj_Name,
    i.object_Id, i.name, i.index_Id, i.type_desc,
    p.partition_id, p.partition_number, p.hobt_id, p.rows AS row_count,
    au.allocation_unit_id, au.type, au.type_desc, au.data_pages, au.total_pages,
    siau.first_page, siau.first_iam_page
from sys.indexes i
inner join sys.partitions p
    ON p.index_Id = i.index_Id AND p.object_Id = i.object_Id
inner join sys.allocation_units au
    ON au.container_Id = p.partition_Id
inner join sys.system_internals_allocation_units siau
    on siau.allocation_unit_id = au.allocation_unit_id
where i.object_Id = object_Id('AllocationUnitDemo')
В качестве пример создадим тестовую таблицу и наполним ее содержимым и просмотрим ее метаданные. 
USE master
GO
IF DB_ID('StorageInternalsDemo') IS NOT NULL
BEGIN
    ALTER DATABASE StorageInternalsDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE StorageInternalsDemo;
END
CREATE DATABASE StorageInternalsDemo
GO
USE StorageInternalsDemo
GO

IF OBJECT_ID('dbo.AllocationUnitDemo') IS NOT NULL
    DROP TABLE dbo.AllocationUnitDemo

CREATE TABLE dbo.AllocationUnitDemo
(
    Id int IDENTITY,
    Name varchar(50) null,
    Descr varchar(8000) null,
    Comment varchar(max) null
)
GO
CREATE UNIQUE INDEX IX_AllocationUnitDemo_Id ON  dbo.AllocationUnitDemo(Id)
GO
INSERT INTO dbo.AllocationUnitDemo
VALUES (REPLICATE('a',50), REPLICATE('b',8000), REPLICATE(CAST('c' AS nvarchar(max)), 10000))



Созданная таблица храниться в куче (index_id = 0) и содержит все три типа блоков распределения. Также видно, что для данной таблицы существует некластерный индекс (index_id = 2). Каждый из блоков содержит цепочку страниц. Первая страница каждого такого блока - это битовая карта распределения страниц (IAM-page). Адрес этой страницы хранится в бинарном формате  в колонке first_iam_page. Итого, после вставки одной строки в таблицу мы имеем 8 страниц на диске. Разберемся чуть подробнее в содержимом данных страниц.

Типы страниц

SQL Server хранит все свои данные на страницах размером 8Кб. Существует несколько типов страниц, которые использует SQL Server. Непосредственно данные хранятся в следующих типах страниц:
  • Data page (PageType = 1)
  • Index page (PageType = 2)
  • Text page (PageType =3)
Страницы группируются в блоки по 8 штук -- экстенты. Различают смешанные (mixed) и однородные экстенты (uniform). При первичном создании объекта, страницы данных этого объекта размещаются в смешанном экстенте, который в свою очередь может содержать страницы данных других объектов. После того, как размер данных одного объекта выходит за рамки 8 страниц, все последующие данные аллоцируется в однородных экстентах.

В SQL Server выделяют отдельные типы страниц GAM-page и SGAM-page. Каждый бит такой страницы кодирует информацию о том выделено ли место под экстент (GAM) и если да, однородный ли он (SGAM). Каждая такая страница содержит информацию о 64000 экстентах, что примерно 4Гб.

Очень важным типом страниц являются PFS-страницы (Page Free Space). Данные страницы содержат один байт с данными на каждую страницу данных (в отличие от GAM и SGAM-страниц содержащих один бит для каждого экстента) и имеют приблизительный размер 64 МБ. Благодаря PFS-страницам, SQL Server определяет, хватит ли на странице с данными места для вновь добавляемой строки. Если места не хватит, то SQL Server алоцирует новую страницу.
Каждая запись в PFS-странице содержит следующие метаданные о странице:
  • Насколько заполнена страница с данными, возможные значения
    • 0_PCT_FULL     -  пустая
    • 50_PCT_FULL   - 51%-80%
    • 80_PCT_FULL   - 81%-95%
    • 100_PCT_FULL - 96%-100%
  • Является ли страница смешанной
  • Является ли страница IAM-страницей
  • Распределена ли страница

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

1. DBCC IND

DBCC TRACEON(3604);
GO
DBCC IND('tempdb', 'dbo.AllocationUnitDemo', -1)


2. Функция sys.dm_db_database_page_allocations

SELECT 
    partition_id, allocated_page_file_id as file_num, allocated_page_page_id as page_num, 
    allocated_page_iam_page_id iam_page, page_type,  page_type_desc, 
    page_level, page_free_space_percent, allocation_unit_type_desc
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.AllocationUnitDemo'), 
         NULL, NULL, 'DETAILED')

3. Функция sys.fn_PhysLocFormatter

Возвращает адреса слотов для всех строк данных таблицы в формате (FileNum:PageNum:SlotNum)
SELECT Id, sys.fn_PhysLocFormatter (%%physloc%%) AS RID FROM dbo.AllocationUnitDemo aud

Структура страницы

Все дисковые операции производятся непосредственно на уровне страниц. Страница содержит заголовок размером 96 Байт, который хранит её метаданные, раздел с данными и таблицу отступов (slot array, каждая ячейка использует 2 байта -- положение первого байта строки с данными). Все страницы нумеруются последовательно. Данные на страницу записываются сверху вниз, а таблица отступов формируется снизу вверх. При этом логический порядок строк поддерживается именно на уровне таблицы отступов, на самой странице данные могут храниться в неупорядоченном виде.


Одна строка с данными содержит следующую информацию:
  • 2 байта со статусами
  • 2 байта на общий размер полей с фиксированной длиной (отступ на позицию, где заканчиваются данные)
  • N байт данных фиксированной длины
  • 2 байта на количество колонок в строке
  • (Количество колонок / 8) байт -- карта полей, содержащих нулевые значения для текущей строки
  • 2 байта на количество полей с переменной длиной
  • (2 * Количество полей переменной длины) байт -- таблица отступов для полей переменной длины
  • M байт данных переменной длины
А также 14 байт для хранения временной метки, если используется версионирование на уровне строк (база данных переведена в режим SNAPSHOT)

Fixed size = 2 + 2 + 2 + Fdata
Variable size = 2 + 2*VarCountColumn + Vdata
Total size = Fixed size + Variable size + Ceiling(Ncol / 8)

Существуют следующие особенности. Если запись содержит строку переменной длины, размер которой не помещается на страницуSQL Server размещает эту строку в ROW_OVERFLOW хранилище и создает указатель на нее (forwarding pointer), который занимает 24 КБ. Тоже самое относится и к LOB-данным, только указатель на них может быть больше, чем 24 КБ в зависимости от количества LOB-страниц.

Посмотрим данные страницы ранее созданного объекта. Сделать это можно при помощи команды DBCC PAGE:
dbcc traceon(3604)
go
dbcc page('StorageInternalsDemo', 1, 268, 3)
в качестве параметров которой передается имя БД, номер файла, номер страницы и опция для отображения данных страницы, которая может принимать следующие значения:
  • 0 - заголовок страницы
  • 1 - заголовок, дамп каждой записи и таблица смещения
  • 2 - заголовок и дамп всей страницы
  • 3 - заголовок и расширенная информация по каждой записи
Вставим еще несколько строк в нашу тестовую таблицу

INSERT INTO dbo.AllocationUnitDemo
VALUES (REPLICATE('a',50), REPLICATE('b',8000), REPLICATE(CAST('c' AS nvarchar(max)), 10000))
GO 2

Результата работы команды DBCC PAGE для страницы 268 с параметром 3

PAGE: (1:268)


BUFFER:


BUF @0x00000004812B45C0

bpage = 0x000000046A2DA000          bhash = 0x0000000000000000          bpageno = (1:268)
bdbid = 21                          breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 12056                       bstat = 0xb
blog = 0x212121cc                   bnext = 0x0000000000000000          

PAGE HEADER:


Page @0x000000046A2DA000

m_pageId = (1:268)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 84    m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594043432960                                
Metadata: PartitionId = 72057594039042048                                Metadata: IndexId = 0
Metadata: ObjectId = 245575913      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 8                         m_slotCnt = 3                       m_freeCnt = 7703
m_freeData = 483                    m_reservedCnt = 0                   m_lsn = (35:189:19)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x61 MIXED_EXT ALLOCATED  50_PCT_FULL                        DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED           

Slot 0 Offset 0x60 Length 129

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 129                   
Memory Dump @0x0000000013E5A060

0000000000000000:   30000800 01000000 04006003 0045005d 80818061  0.........`..E.]...a
0000000000000014:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
0000000000000028:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
000000000000003C:   61616161 61616161 61020000 00010000 005a7a00  aaaaaaaaa........Zz.
0000000000000050:   00401f00 000a0100 00010000 00040000 16010000  .@..................
0000000000000064:   00035d00 00681f00 00090100 00010000 00102700  ..]..h... ........'.
0000000000000078:   00e70000 00010000 00                          .ç.......

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

Id = 1                              

Slot 0 Column 2 Offset 0x13 Length 50 Length (physical) 50

Name = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa                

Descr = [BLOB Inline Root] Slot 0 Column 3 Offset 0x45 Length 24 Length (physical) 24

Level = 0                           Unused = 0                          UpdateSeq = 1
TimeStamp = 2052718592              Type = 2                            
Link 0

Size = 8000                         RowId = (1:266:0)                   

Comment = [BLOB Inline Root] Slot 0 Column 4 Offset 0x5d Length 36 Length (physical) 36

Level = 0                           Unused = 22                         UpdateSeq = 1
TimeStamp = 1560477696              Type = 4                            
Link 0

Size = 8040                         RowId = (1:265:0)                   
Link 1

Size = 10000                        RowId = (1:231:0)

Также выполним команду с параметром 1, чтобы просмотреть данные таблицы смещения

Row - Offset                        
2 (0x2) - 354 (0x162)               
1 (0x1) - 225 (0xe1)                
0 (0x0) - 96 (0x60)

Проанализируем полученные данные.
Для начала вычислим размер одной записи (ориентируясь на данные, которые мы вставили в таблицу) внутри IN_ROW страницы по формуле описанной выше.

Row size = 6 + 4 + 2 + 6 + 1 + 50 + 24 + 36 = 129

Где 4 байта на колонку типа int
50 байт на строку varchar(50). заполненную полностью;
24 байта на указатель на данные колонки varchar(8000), размещенной в ROW_OVERFLOW странице
36 байтов на указатель на LOB данные, для строки типа varchar(max). 12 байт на метаданные и по 12 байт на каждую LOB страницу. В нашем случае их 2

 Итого, получаем 129 байт на хранение одной строки на IN_ROW странице.
Наши вычисления подтверждаются при просмотре данных страницы, так как она содержит поле Record Size = 129.  

Материалы

10 февраля 2015 г.