Физическая структура данных в SQL Server 7.0

Журнал "Windows 2000 Magazine", #03/2000

Татьяна Крамарская

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

Я думаю, этот материал будет интересен специалистам, которые знакомы с документацией по SQL Server 7.0, уже имеют опыт работы и хотят глубже проникнуть в механизмы, используемые сервером.

Физическая структура данных SQL Server 7.0, в курсах Microsoft Official Curriculum (MOC), к сожалению, не рассматривается. В документации общая схема, конечно, изложена, но не описана динамика роста таблиц и индексов. Предлагаю рассмотреть динамику использования сервером физической структуры данных на простых примерах.

Не буду повторять сведения из документации, напомню лишь, что страница - это 8К, а экстентом называют последовательно расположенные 8 страниц (64К). SQL Server использует два типа экстентов: однородные и смешанные. Однородные экстенты всегда принадлежат только одному объекту. Смешанный экстент может использоваться восемью объектами.

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

Создадим несколько маленьких таблиц, которые не имеют кластеризованного индекса, т. е. расположены на диске вперемежку.

Итак, три таблицы TA1, TA2 и ТA3 в базе данных MYDB.

USE MYDB

CREATE TABLE TA1 (COL_1 CHAR (8000) NOT NULL)

CREATE TABLE TA2 (COL_1 CHAR (8000) NOT NULL)

CREATE TABLE TA3 (COL_1 CHAR (8000) NOT NULL)

Проверим, где расположена первая страница каждой таблицы и сколько места выделено и используется в таблице TA1. Вспомним, что поле FIRST - это первая страница, ROOT - последняя, FIRSTIAM - первая страница в списке IAM-страниц, индексных карт размещения.

В SQL Server 7.0 страница всегда идентифицируется парой параметров <fileid><pageno>, где <fileid> - идентификатор файла, а<pageno> - номер страницы в этом файле. Номера страниц уникальны только внутри одного файла. Поэтому, когда мы говорим о номере страницы, то подразумеваем именно два параметра. Именно в таком виде, в двух частях, эта информация и хранится в полях системной таблицы sysindexes. В таблице sysindexes все идентификаторы страниц хранятся в шестнадцатеричном виде с обратным порядком байтов. Для прочтения идентификаторов нужно сначала поменять порядок байтов, а потом перевести результат в десятичный вид.

Ниже приведена программа, которая выполняет эту работу, см. Листинг 1.

Результат имеет вид, показанный на Рисунке 1:

РИСУНОК 1.

Итак, мы видим, что первые страницы таблиц расположены не подряд, а через одну. Потому что сразу за первой страницей данных таблицы следует ее индексная карта размещения, IAM-страница.

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

INSERT INTO TA1(COL_1) VALUES ('1')

EXEC SP_SPACEUSED TA1

name rows reserved data index_size unused

-------------------------------------------

TA1        116 KB    8 KB    8 KB 0 KB

Теперь добавим еще одну запись и посмотрим, как изменилось значение поля ROOT, указывающего на последнюю страницу (Листинг 2).

Один из возможных результатов выглядит так:

Root converted root

-------------------------------

1:100        0x6400000000100

Повторим эту процедуру семь раз, увеличивая соответственно значение поля COL_1, пока в таблице не появится 9 записей.

Еще раз выберем информацию из таблицы sysindexes, как мы делали ранее, результат представлен на Рисунке 2:

РИСУНОК 2.

Выполним хранимую процедуру

EXEC SP_SPACEUSED TA1

name rows reserved data index_size unused

-------------------------------------------

TA1    9     136 Kв     72 Kв     8 Kв     56 Kв

Как видно из результата выполнения sp_spaceused, размер зарезервированного пространства 136 Кбайт, а размер данных 72 Кбайт.

Исследуем теперь заголовок страницы данных и индексной карты размещения.

Чтобы прочитать заголовок страницы, используем команду DBCC PAGE.

В качестве номера страницы будем подставлять значение полей FIRST, ROOT и FIRSTIAM из таблицы sysindexes, предварительно преобразовав их.

Например, для первой страницы таблицы TA1:

DBCC TRACEON (3604)

DBCC page ('MYDB', 1,86,0,0)

Результат на Рисунке 3.

РИСУНОК 3.

Заметьте, что идентификатор объекта (m_objId) в заголовке страницы соответствует результату выполнения оператора SELECT OBJECT_ID ('TAl'), и страница принадлежит таблице ТA1.

Тип экстента смешанный, страница заполнена на 100%.

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

DBCC TRACEON (3604)

DBCC page ('MYDB', 1,87,0,0)

Результат представлен на Рисунке 4.

РИСУНОК 4.

Заметим, что в заголовке страницы указан ее тип IAM_PG, тип экстента и заполнение.

Прочитаем заголовок последней страницы таблицы ТA1.

DBCC TRACEON (3604)

DBCC page ('MYDB', 1,112,0,0)

Результат на Рисунке 5.

РИСУНОК 5.

Тип экстента в данном случае не смешанный, а однородный.

Для более эффективного управления дисковым пространством SQL Server не выделяет маленьким таблицам сразу целый экстент. Для новой таблицы или индекса, как правило, выделяется место на смешанном экстенте. Когда объем таблицы или индекса увеличивается до восьми страниц, все последующие экстенты будут однородными. Место выделяется на доступных смешанных экстентах до тех пор, пока данные не займут по объему восемь страниц, тогда следующий выделенный экстент будет полностью принадлежать таблице. Если на смешанных экстентах места нет, а объем таблицы не достиг восьми страниц, выделяется новый экстент, но он будет объявлен смешанным. Например, таблица занимает две страницы на смешанном экстенте, и в нее еще добавляется сразу шесть записей; а если свободных страниц на смешанных экстентах нет, будет выделен новый смешанный экстент, и на нем разместится 6 записей. Потом добавляется еще одна запись. Будет выделен полный новый однородный экстент, и на нем размещена новая запись. Таким образом, начало таблицы в подобных случаях располагается на смешанном экстенте. Однако возможны и другие варианты. Например, используем оператор SELECT INTO. Выберем из нашей таблицы ТА1 7 записей и поместим их во вновь созданную таблицу ТА4.

SELECT * INTO TA4 FROM TT1 WHERE COL_1 NOT IN ('8','9')

Каким образом при этом будет выделено место? Результат чтения из sysindexes может выглядеть так, как показано на Рисунке 6.

РИСУНОК 6.

Об авторе

Крамарская Татьяна Алексеевна, к.т.н., MCSE, MCDBA, MCT. Специализируется на преподавании и консультировании по вопросам использования SQL Server и продуктов BackOffice. Старший консультант компании АЛЕСТА (MCSP,CTEC).
Адрес: kram@alesta.ru

ИНФОРМАЦИЯ

Компании

АЛЕСТА