Transcript Внутреннее устройство страниц и экстентов SQL Server Князев Алексей ( [email protected] ) Екатеринбург EastWind MVP, MCP, MCTS, MCITP.

Внутреннее устройство страниц
и экстентов SQL Server
Князев Алексей ( [email protected] )
Екатеринбург
EastWind
MVP, MCP, MCTS, MCITP
Обо мне
Закончил Радиотехнический факультет УрФУ (бывший УГТУ-УПИ)
по специальности Автоматизированные системы обработки
информации и управления.
C 2002 года работаю с SQL Server.
Более 4х лет занимался администрированием SQL Server 6.5, 7.0,
2000, 2005, 2008 на крупном машиностроительном предприятии Урала.
Приходилось обслуживать более 100 баз данных единовременно.
С 2010-ого года занимаюсь разработкой на T-SQL в телекоммуникационной
сфере. А это высоконагруженные системы, объём баз исчисляется в десятках
терабайт.
Последние два года специализируюсь на построении DWH-решений.
С 2012-ого года читаю курс лекций по базам данных в Уральском федеральном
университете
(УрФУ
http://urfu.ru/).
C 2007-ого являюсь лидером MCP-клуба в г. Екатеринбург (www.EkbIT.pro).
C 2014-ого лидер SQL User Group в г. Екатеринбург (http://yekt.sqlpass.org/).
2
2
Описание доклада
В докладе будет подробно рассмотрена архитектура страниц и экстентов
SQL Server. Поговорим о том, какие они бывают и как правильно их читать.
Посмотрим интересные примеры, как с помощью недокументированных
процедур можно понять, в каком виде хранятся ваши данные и получить
ответ на многие вопросы по физическому устройству SQL Server.
3
3
Содержание








Зачем разработчику знать архитектуру страниц и экстентов?
PAGE (Страница)
EXTENT (Экстент)
Недокументированные процедуры DBCC PAGE и DBCC EXTENTINFO
Internals Viewer for SQL Server
Несколько примеров из жизни…
Заключение
Вопросы?
4
4
Зачем это разработчику?
 Необходимо, чтобы лучше понять устройство Database Engine
 Позволит лучше понять архитектуру файлов и файловых групп
 Даст возможность наглядно увидеть внутренне устройство и физическое
хранение ваших данных и индексов
 Позволит в дальнейшем погрузиться в архитектуру обработчика
запросов, управления памятью, задачами и потоками
 Позволит грамотно организовать систему резервного копирования и
аварийного восстановления в случаи сбоя
5
5
PAGE (Страница)
 Все файлы данных (*.mdf и *.ndf) логически деляться на страницы от 0
до N
 Размер страницы – 8192 байта (8КB).
 Страница состоит из:
 Заголовок (первые 96 байт)
 Строки данных (8060 байт)
 Таблица смещения (36 байт)
 Порядок записей определяется по таблице смещения, а не по
физическому размещению на странице
6
6
Header (Заголовок)




















m_pageId - идентификатор страницы
m_headerVersion - версия заголовка страницы, для SQL Server 7.0 и выше всегда 1
m_type – тип страницы
m_typeFlagBits - Для страниц данных и индексов всегда 4. Для остальных страниц 0, кроме PFS (для них может быть значение 1, указывающее на ghost-записи).
m_level - уровень страницы в B-дереве (0 – листовой уровень) , для всех страницы, кроме индексных значение всегда 0.
m_flagBits – флаг с описанием страниц, например наличие контрольной суммы (0х200) или torn-page (0х100), а так же может указывать «грязная» или «чистая» страница
m_objId (AllocUnitId.idObj)*
m_indexId (AllocUnitId.idInd)*
m_prevPage – указатель на предыдущую страницу B-дерева
m_nextPage - указатель на следующую страницу B-дерева
pminlen – размер записей с фиксированной длинной
m_slotCnt – количество записей на странице
m_freeCnt – количество свободного пространства на странице в байтах
m_freeData – смещение от начала страницы до первого свободного байта
m_reservedCnt – количество свободного пространства в байтах, зарезервированное под транзакции (для корректного отката транзакции)
m_lsn – LSN(Log Sequence Number) записи в журнале транзакций, которая изменила страницу
m_xactReserved – последнее значение, которое было добавлено в поле m_reservedCnt
m_xdesId – внутренний идентификатор последней транзакции, которая изменила поле m_reservedCnt
m_ghostRecCnt – количество ghost-записей на странице
m_tornBits – либо контрольная сумма, либо по 2 последних бита из секторов 1-15 (заголовок расположен в 0-ом секторе) + 2 бита контроля обрыва страницы (актуально
для torn-page)
*m_objId и m_indexId – параметры для определения Allocation Unit ID. Формула следующая: (m_indexId << 48) | (m_objId << 16)
На t-sql можно вычислить по формуле:
Параметр AllocUnitId даёт возможность получить значения ParttionId, ObjectId и IndexId
из системных представлений sys.system_internals_allocation_units и sys.partitions
7
7
Records (Записи)
 Заголовок записи (4 байта)
Типа записи (2 байта)







primary record
forwarded record
overflow data record
BLOB record
ghost record
Указатель на NULL bitmap/общий размер данных с фиксированной длинной (2 байта)
 Столбцы с фиксированной длинной (bigint, char(10), datetime)
 NULL bitmap (битовая маска NULL-значений)


Количество столбцов в записи (2 байта)
Переменное количество байт, содержащее по биту на каждый столбец независимо от того допускается ли
NULL-значение или нет (в SQL Server 2000 содержались битовые указатели только на поля NULL)
 Указатель на смещение столбцов переменной длинны


Количество столбцов переменной длинны (2 байта)
2 байта на каждый столбец переменной длинны с указанием положения/смещения с учётом самих данных
в столбце
 Указатель версионности

14 байт: timestamp + указатель на версию в tempdb
8
8
Row Offset or Slot Array (Таблица смещения)






2 байта на каждую запись
Записи на странице физически не упорядочены
Между записями на страницы могут быть разрывы
При удалении записи не уплотняются
Данные на страницу пишутся сверху вниз
Таблица смещения заполняется снизу вверх
DELETE 3 rows
9
INSERT 2 rows
9
Демонстрация
Разбираем структуру страницы
Типы страниц (m_type)















1: Data page
2: Index page
3: Text Mixed Page
4: Text Page
7: Sort Page
8: GAM Page
9: SGAM Page
10: IAM Page
11: PFS Page
13: Boot Page
14: Server Configuration Page
15: File Header Page
16: Differential Changed map
17: Bulk Change Map
18,19,20: служебные страницы
11
11
Data page
 m_type = 1
 Любые данными, кроме данных типа text, ntext, image, nvarchar(max),
varchar(max) и varbinary(max), а также данные типа xml, когда
параметр text in row установлен в значение ON.
 Содержит данные в таблицах-кучах или кластерный индекс на листовом
уровне
 Строка данных может быть в 3-х форматах:
 Обычный
 Сжатый (при сжатии PAGE или ROW)
 Разреженный (Sparse-колонки)
 Просмотр: DBCC PAGE
12
12
Index page
 m_type = 2
 Страницы индексов:
 Нелистовой уровень кластерного индекса
 Любые страницы некластерного индекса
 Просмотр: DBCC PAGE
13
13
Text Mixed Page
 m_type = 3
 Смешанные страницы, могут содержать небольшие LOB-данные
нескольких типов строк
 Просмотр: DBCC PAGE
14
14
Text Page
 m_type = 4
 Страницы с LOB (Large object data types)-данными одной колонки
 text, ntext, image, nvarchar(max), varchar(max), varbinary(max) и xml.
 Столбцы переменной длинны, когда строки данных превышают 8 КБ
 varchar, nvarchar, varbinary и sql_variant
 Просмотр: DBCC PAGE
15
15
Sort Page
 m_type = 7
 Страницы с промежуточными результатами сортировки
 Просмотр: DBCC PAGE
16
16
GAM Page
 m_type = 8
 GAM (Global Allocation Map) - глобальная карта распределения
 На GAM-страницах записано, какие экстенты были размещены. В каждой
карте GAM содержится 64 000 экстентов или почти 4 ГБ данных. В карте
GAM приходится по одному биту на каждый экстент в покрываемом им
интервале. Если бит равен 1, то экстент свободен; если бит равен 0, то
экстент занят
 В каждом файле данных первая страница GAM имеет №2
 Просмотр: DBCC PAGE
17
17
SGAM Page
 m_type = 9
 SGAM (Shared Global Allocation Map) – общая глобальная карта
распределения
 На SGAM-страницах записано, какие экстенты в текущий момент
используются в качестве смешанных экстентов и имеют как минимум одну
неиспользуемую страницу. В каждой карте SGAM содержится 64 000
экстентов или почти 4 ГБ данных. В карте SGAM приходится по одному биту
на каждый экстент в покрываемом им интервале. Если бит равен 1, то
экстент используется как смешанный экстент и имеет свободную страницу.
Если бит равен 0, то экстент не используется как смешанный экстент, или он
является смешанным экстентом, но все его страницы используются
 В каждом файле данных первая страница SGAM имеет №3
 Просмотр: DBCC PAGE
18
18
IAM Page
 m_type = 10
 IAM (Index Allocation Map) – карта распределения индекса
 IAM - страница имеет заголовок, отражающий первый экстент из диапазона,
сопоставленного с данной IAM-страницей. IAM-страница также имеет большую
битовую карту, в которой каждый бит представляет экстент. Первый бит схемы
представляет первый экстент диапазона, второй бит — второй экстент и т. д.
Если бит равен 0, то соответствующий ему экстент не привязан к единице
распределения, которой принадлежит IAM-страница. Если он равен 1, то
соответствующий ему экстент привязан к единице распределения, которой
принадлежит IAM-страница.
 Единица распределения может иметь один из трех типов
 IN_ROW_DATA - Содержит секцию кучи или индекса
 LOB_DATA - Содержит типы данных больших объектов (LOB), например: xml,
varbinary(max) или varchar(max)
 ROW_OVERFLOW_DATA - Содержит данные переменной длины, которые хранятся в
столбцах varchar, nvarchar, varbinary или sql_variant и превышают допустимый размер
строки 8060 байт
 Просмотр: DBCC PAGE
19
19
PFS Page
 m_type = 11
 PFS (Page Free Space) – распределение страниц и степень их заполнения
В PFS на каждую страницу приходится по одному байту, хранящему информацию о том, была ли
страница размещена или нет, а если была — то пустая она, или ее заполнение находится в
промежутке от 1 до 50 процентов, от 51 до 80 процентов, от 81 до 95 процентов или от 96 до 100
процентов
В каждом файле данных первая страница PFS имеет №1


 Общая структура PFS
Бит 7: не используется
Бит 6: указывает, является ли страница выделенной (allocated) или нет
Бит 5: указывает, что страница расположена в смешенном экстенте
Бит 4: указывает, что это IAM - страница
Бит 3: указывает, что страница содержит ghost-записи (страница содержит логически удалённые
записи, но ещё не очищенные)
Бит 2-0: указывает степень заполнения страницы











0 – пустая страница (000)
1 – заполнена на 1 - 50 процентов (001)
2 – заполнена на 51 - 80 процентов (010)
3 – заполнена на 81 - 95 процентов (011)
4 – заполнена на 96 – 100 процентов (100)
 Просмотр: DBCC PAGE
20
20
Boot Page
 m_type = 13
 Содержит информацию о БД
(одна страница на одну БД)
 Это всегда страница №9 в файле №1
 Просмотр: DBCC PAGE либо
DBCC DBINFO
21
21
Server Configuration Page
 m_type = 14
 Содержит системную информацию, часть данных из sp_configure
 Страница расположена только в БД master в первом файле страница
№10
 Просмотр: DBCC PAGE
22
22
File Header Page
 m_type = 15
 Страница с заголовком файла, содержит информацию о каждом файле
БД
 Для каждого файла – одна страница с №0
 Просмотр: DBCC PAGE, DBCC FILEHEADER
23
23
Differential Changed map
 m_type = 16
 DCM - Схема разностных изменений
 Она отслеживает экстенты, которые были изменены со времени последнего
выполнения инструкции BACKUP DATABASE. Если бит равен 1, значит, экстент был
изменен со времени последнего выполнения инструкции BACKUP DATABASE. Если
бит равен 0, то экстент не был изменен
 Чтобы определить, какие экстенты были изменены, разностные резервные копии
считывают только страницы DCM. Это существенно сокращает количество страниц,
которые должна просмотреть разностная резервная копия. Количество времени,
которое затрачивает разностная резервная копия, пропорционально количеству
экстентов, измененных со времени последнего выполнения инструкции BACKUP
DATABASE, а не размеру всей базы данных
 В каждом файле данных первая страница DCM имеет №6
 Просмотр: DBCC PAGE
24
24
Bulk Change Map
 m_type = 17
 BCM - Схема массовых изменений
 Она отслеживает экстенты, измененные операциями с неполным протоколированием
со времени последнего выполнения инструкции BACKUP LOG. Если бит равен 1,
значит, экстент был изменен операцией неполного протоколирования после
последнего выполнения инструкции BACKUP LOG. Если бит равен 0, то экстент не
был изменен операциями с неполным протоколированием.
 Актуально только для модели восстановления с неполным протоколированием (bulklogged recovery model)
 В каждом файле данных первая страница DCM имеет №7
 Просмотр: DBCC PAGE
25
25
Несколько служебных страниц
 m_type = 18
 Страницы, которые будут удалены, при выполнении операции DBCC
CHECKDB с аргументами REPAIR_*
 m_type = 19
 Временные страницы, которые используются при работе с индексами
(ALTER INDEX … REORGANIZE или DBCC INDEXDEFRAG)
 m_type = 20
 Страницы, предварительно выделенные в рамках операции массовой
загрузки, которые, в конечном счете, будут переведены в обычные
фиксированные страницы БД
 Просмотр: DBCC PAGE
26
26
Демонстрация
Типы страниц
EXTENT (Экстент)
 Основной единицей хранилища данных
в SQL Server является страница.
Место на диске, предоставляемое для размещения
файла данных (MDF- или NDF-файл) в базе данных, логически разделяется на
страницы с непрерывным перечислением от 0 до n. Дисковые операции ввода-вывода
выполняются на уровне страницы. А именно, SQL Server считывает или записывает
целые страницы данных.
 Экстент — это коллекция, состоящая из восьми физически непрерывных
страниц; они используются для эффективного управления страницами.
Все страницы хранятся в экстентах. Один экстент – 64 КБ.
28
28
Типы экстентов
 SQL Server имеет два типа экстентов
 Однородные экстенты принадлежат одному объекту; все восемь страниц в
кластере могут быть использованы только этим владеющим объектом
 Смешанные экстенты могут находиться в общем пользовании у не более
восьми объектов. Каждая из восьми страниц в экстенте может находиться во
владении разных объектов
 Новая таблица или индекс — это обычно страницы, выделенные из
смешанных экстентов. При увеличении размера таблицы или индекса до
восьми страниц эти таблица или индекс переходят на использование
однородных экстентов для последовательных единиц распределения.
При создании индекса для существующей таблицы, в которой
содержится достаточно строк, чтобы сформировать восемь страниц в
индексе, все единицы распределения для индекса находятся в
однородных экстентах.
29
29
Демонстрация
Экстенты
DBCC PAGE, DBCC EXTENTINFO и DBCC IND
 DBCC PAGE ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ]);
 Printopt:




0 – возвращает
1 – возвращает
2 – возвращает
3 – возвращает
только заголовок страницы
заголовок, записи и таблицу смещения
заголовок и полный дамп страницы
заголовок и расширенную информацию о каждой записи
 DBCC EXTENTINFO [({'database_name'| dbid | 0} [,{'table_name' | table_id} [,
{'index_name' | index_id | -1}]])];
 DBCC IND ( { 'dbname' | dbid }, { 'objname' | objid }, { nonclustered indid | 1 | 0 | -1 |
-2 } );
nonclustered indid = идентификатор некластерного индекса
1 = кластерный индекс
0 = Displays information in-row data pages and in-row IAM pages (from Heap)
-1 = Displays information for all pages of all indexes including LOB (Large object binary)
pages and row-overflow pages
 -2 = Displays information for all IAM pages




31
31
Internals Viewer for SQL Server
 Internals Viewer for SQL Server – это ADD-IN для SQL Server Management
Studio, который в графическом виде показывает распределение страниц в
файлах БД
 Проект с открытым кодом
 На текущий момент в общем доступе есть решения для SSMS 2005, 2008,
2008R2
 Реализовано на базе DBCC PAGE
32
32
ADD-IN for SSMS 2005, 2008, 2008R2
Internals Viewer for SQL Server
Когда полезно обратиться к чтению страницы
 Если у вас возникают вопросы по поводу того, как на самом деле
расположены данные на страницах
 Для проверки какой-либо информации из различных источников
 Для того, чтобы проверить свои гипотезы и предположения
относительно физического расположения ваших данных
34
34
Несколько примеров из жизни
Полезные скрипты
Заключение
Ресурсы
 Paul S. Randal
 http://www.sqlskills.com/blogs/paul/
 Internals Viewer for SQL Server
 http://internalsviewer.codeplex.com/ (SSMS 2005,2008)
 http://intview2.codeplex.com/ (SSMS 2008R2)
 Мой блог
 http://www.t-sql.ru
37
37
Вопросы?
Спасибо за участие!