PDF
Недавно, на одном из форумов был озвучен интересный вопрос. Есть сильно фрагментированная таблица (фрагментация более 80%), без кластерного индекса. Вопрос заключается в том, что нужно ли пытаться бороться с фрагментацией, например, создавая и удаляя для этого кластерный индекс. Влияет ли фрагментация на то, как используется стратегия чтения read-ahead.
В этой заметке я постарался исследовать этот вопрос. Заметка состоит из следующих частей:
— Немного теории о структурах данных – основные понятия
— Немного практики в изучении структур данных
— Что такое фрагментация и какая она бывает
— Фрагментация небольших таблиц
— Что такое read-ahead
— Фрагментация экстентов – эксперимент
— Полезные ссылки
Немного теории о структурах данных – основные понятия
Для дальнейшего удобства я буду именовать SQL Server как сиквел сервер или просто сервер*.
* Кстати, почему одни произносят «сиквел», а другие «эскуэль». Есть мнение, что произношение «сиквел» возникло в лохматых 1970-х. Когда в компании IBM впервые зародился SQL, то его аббревиатура была Structured English QUEry Language — SEQUEL, которая, впоследствии, при принятии языка как стандарта, выродилась в SQL, но т.к. англоговорящим людям проще произносить сиквел, нежели эскуэль , то произношение видимо прижилось. Этот интересный факт взят из книги Ицика Бен-Гана Inside Microsoft SQL Server 2008: T-SQL Querying
Итак, что представляет собой база данных (БД) на физическом уровне? Прежде всего, это файлы в файловой системе. Сейчас мы опустим способы организации файлов, файловые группы, журнал транзакций и прочее, т.к. это не предмет данной заметки. Нас интересует только файл данных, для простоты, пусть он будет один.
Каким образом организованы данные в файле? Конечно, можно открыть файл БД в каком-нибудь hex редакторе, но структура файла от этого понятнее не станет. Обычно, когда говорят о том, как сервер хранит данные, оперируют понятиями — страница и экстент. Чтобы не пересказывать документацию, я дам только краткое описание и ссылку, на более подробное описание.
Страница — структура данных, размером 8192 байт (8 кб), являющаяся основной единицей хранения. Данные таблиц хранятся именно в страницах. Так же страница — это минимальная порция данных, которая может быть прочитана сервером (это значит, если требуется прочитать только одну строку, сервер все равно считает с диска страницу, на которой располагается строка, целиком). Каждая страница в файле имеет свой номер. Это величина от 0 до ((Max File Size/8 KB)-1). Когда страница читается с диска, ее номер немедленно проверяется (сравнивается номер из заголовка страницы с номером, вычисленным по смещению адреса страницы в файле, если они не совпадают, генерируется ошибка). Почему я заостряю на этом внимание, потому, что важно запомнить, что номер страницы (или pageId) — напрямую соответствует ее смещению в файле. Более подробно тут.
Экстент — объединение 8-и физически непрерывных страниц в файле. Каждая страница принадлежит какому либо экстенту. Если в экстенте все 8 страниц принадлежат одному и тому же объекту (например, таблице), то экстент называется однородным. Если в экстенте хранятся страницы, принадлежащие разным объектам, то смешанным.
Более подробно можно посмотреть тут страницы и экстенты
Важно понимать, что страницы, относящиеся к одной таблице, могут располагаться как в начале файла, так и в конце, в зависимости от того, когда эти страницы были созданы, и в каком месте файла нашлось пустое место.
Сразу возникает вопрос, как сервер, может определить, какие страницы принадлежат таблице, а какие нет. Неужели для этого нужно считывать весь файл БД последовательно и проверять каждую страницу на принадлежность к интересующей таблице?
Конечно же нет. Для этой цели, сервер может использовать карту размещения страниц таблицы (строго говоря, такие карты создаются не для таблицы или индекса в целом, а для любых типов так называемых «единиц распределения» allocation units, из которых состоит таблица или индекс, но для простоты и т.к. заметка посвящена куче, я буду просто писать «таблица», подразумевая таблицу-кучу с типом единицы распределения IN_ROW_DATA). Каждая такая карта, физически, представляет собой тоже страницу в файле и значит, ограничена 8192 байтами, на которых могут поместиться сведения о примерно 4ГБ данных. Если таблица превышает указанный порог, то страницы карт организуются в цепочку. Такие страницы карт — имеют специальное название — Index Allocation Map или страницы IAM. Страница IAM содержит заголовок страницы, массив указателей на одиночные страницы таблицы и битовую карту указателей на экстенты таблицы. По этому массиву и указателям, сервер может определить, какие из страниц или экстентов из 4ГБ адресного пространства принадлежат таблице, а какие нет. Сами страницы IAM всегда размещаются в смешанных экстентах.
Когда создается таблица, для нее создается две страницы: 1) IAM и 2) первая страница данных (интересно, что, строго говоря, страницы создаются, но не во время операции create table, а во время первого добавления данных, чуть позже мы это увидим в примере).
Доступ к страницам таблицы через страницы карт IAM называют allocation order scan, т.е. просмотр в порядке размещения. Страницы считываются из файла одна за другой в том порядке, в котором они размещены в файле, последовательным перемещением по диску.
Сервер так же умеет организовывать страницы в двусвязный список. Для этого в таблице должен быть создан кластерный индекс. Связность списка (т.е. что за чем следует) организуется по ключу индекса. В таком случае, каждая страница ссылается на предыдущую страницу и на последующую страницу. Конечно же, т.к. кластерный индекс является полноправным индексом, также создаются и страницы для верхних уровней индекса и вся структура в целом представляет собой B-дерево (для более подробного объяснения рекомендую ознакомиться с отличной статьей Индексы. Теоретические основы.)
* Вообще есть еще одна разновидность, «куча, организованная в связный список». Такую структуру используют некоторые системные таблицы. Некоторые сведения об этом можно найти тут
Когда страницы организованы в двусвязный список, то у сервера появляется возможность определить, какие страницы принадлежат таблице, просто перемещаясь от одной странице в списке к другой по указателям списка. Этот способ называется index order scan, т.е. просмотр в порядке индекса. В таком случае, упрощенно говоря, если первая страница лежит в середине файла, вторая в конце, а третья в начале, то серверу придется сначала переместить головку диска в середину файла, далее в конец файла, и потом в начало файла. Что медленнее, чем в случае allocation order scan, когда чтение идет последовательно в порядке размещения в файле. Поэтому при просмотре в порядке индекса, подобная «размазанность» или фрагментация будет влиять на скорость. Это известный факт и по этому, влияние фрагментации кластерного индекса при index order scan — в данной заметке не рассматривается.
Следует сказать об еще одном типе страниц — PFS страницы. PFS — Page Free Space — эти страницы тоже являются своего рода страницами битовых (точнее байтовых, по байту на страницу) карт, только имеют другое назначение. При помощи них, сервер отслеживает информацию о размещении и свободном месте на страницах, чтобы свободные страницы таблиц можно было бы легко и быстро найти для добавления в них данных. В отличие от IAM — которая имеют отношение к конкретному индексу или таблице, страница PFS — содержит сведения о файле в целом. Я специально упомянул этот тип страниц, т.к. при помощи него мы будем смотреть, как размещаются страницы в БД.
Немного практики в изучении структур данных
Теперь, когда основные понятия озвучены, посмотрим на все описанные структуры в реальности, а не на картинках.
Для исследования того, как размещаются данные, и что происходит в результате манипуляций, нам потребуется несколько инструментов. К сожалению, многие инструменты, которые позволяют заглянуть внутрь сервера, являются недокументированными. По этому, если вы хотите следить за ходом эксперимента, то необходимо обеспечить соответствующую тестовую среду.
1) Не проводите опыты на рабочем сервере, для экспериментов необходим тестовый сервер. Например, я, использую сервер, установленный на моей локальной машине.
2) Используемая версия сервера Microsoft SQL Server 2008 R2 (RTM) — 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation Express Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
Используемые инструменты:
dbcc ind — выводит информацию о страницах индекса таблицы или кучи
dbcc page — выводит информацию об определенной странице
dbcc extentinfo — выводит информацию об используемых экстентах
sp_EnumDbPages — написанная мной демо процедура, выводит информацию о типе страниц и статусе их размещения в БД (из первой страницы PFS). Процедура написана только для демонстрации, не должна использоваться на реальных системах, не будет работать на SQL Server 2005 (без предварительной обработки напильником).
dbcc traceon(3604) — специальный флаг трассировки, по-умолчанию некоторые недокументированные команды dbcc направляют вывод результатов своей работы в errorlog, для того чтобы перенаправить их вывод на консоль, требуется включить этот флаг.
Приступим:
1) Создаем БД PagesAllocationDB.
2) Переключаемся в контекст созданой бд.
3) Создаем вспомогательную процедуру sp_EnumDbPages.
upd.18.05.2012:
Для 2012 сервера нет необходимости создавать вспомогательную процедуру. Вместо этого, можно воспользоваться новой недокументированной DMV sys.dm_db_database_page_allocations, в которой содержится похожая информация.
Вот вариант использования:
select * from sys.dm_db_database_page_allocations(db_id(),null,0,null,'DETAILED');
От туда можно узнать об ид страницы, типе страницы, типе размещения, принадлежности к объекту и множество другой полезной информации.
Создаем БД для тестов:
create database PagesAllocationDB; go
Создаем процедуру для просмотра страниц БД
Откроем отдельное окно и посмотрим, какие страницы у нас присутствуют в созданной БД.
use PagesAllocationDB; go exec sp_EnumDbPages; go
PageID — ИД страницы (состоит из номера файла, в данном случае всегда 1, и номера страницы, при этом помним, что номер страницы отражает ее расположение в файле).
AllocationStatus — статус страницы, размещена ли она под какой-либо объект, или нет.
Type — тип страницы.
IndexID — ИД индекса.
ObjectID — ИД таблцы.
ObjectName — Имя таблицы.
RecordsOnPage — кол-во записей на странице.
AllocationExtentType — тип экстента в котором размещена страница (mixed — смешанный, uniform — однородный).
Мы видим, что нулевой идет страница File Header Page, за ней PFS Page, GAM Page и т.д.
Теперь открываем еще одно окно, и создаем таблицу — таблица имеет такой размер полей, чтобы одна запись занимала 8004 байта, примерно одну страницу, для удобства, чтобы не вставлять много записей.
Создадим таблицу и посмотрим, какие структуры данных были созданы.
create table MyTable(id int identity, a char(8000) default 'a'); go dbcc ind (PagesAllocationDB,MyTable,0); exec sp_EnumDbPages; go
Если внимательно посмотреть на результаты, какие страницы выделены в БД под таблицу, то ни команда dbcc ind (вообще ничего не вывела), ни информация sp_EnumDbPages (можно поискать записи, у которых в колонке ObjectName было бы MyTable) — не покажет никаких следов созданной таблицы. Я специально просил открыть другое окно, чтобы была возможность сравнить с предыдущими результатами, когда таблицы еще не было в БД.
Таким образом, можно сделать вывод о том, что в момент создания таблицы, создаются только метаданные, а физическая структура данных не создается.
Теперь, добавим одну строчку (которая, напомню, занимает почти целую страницу). И посмотрим, что изменилось.
insert into MyTable default values;
go
dbcc ind (PagesAllocationDB,MyTable,0);
exec sp_EnumDbPages;
go
Во первых, команда dbcc ind — вывела следующее:
Было создано две страницы, у меня они получили номера 90 и 93. Одна из них, страница IAM (PageType=10), другая страница данных (PageType=1). Страница данных ссылается на страницу IAM (IAMPID = 93).
Процедура sp_EnumDbPages:
Мы видим, что там, где были пустые страницы, теперь располагаются страницы нашей таблицы. Обратите внимание, что страницы были выделены в смешанном (MIXED) экстенте.
Теперь добавим еще 7 строк, чтобы всего таблица стала занимать 8 строк. Посмотрим, как они разместились, а так же в каких экстентах.
insert into MyTable default values; go 7 exec sp_EnumDbPages; go dbcc extentinfo(PagesAllocationDB,MyTable,-1) go
page_id — номер страницы
ext_size — кол-во выделенных (зарезервированных) страниц
pg_alloc — кол-во используемых (размещенных) страниц
Мы видим, что страницы таблицы по-прежнему размещены в смешанных экстентах.
Давайте попробуем добавить еще одну строку.
insert into MyTable default values; go exec sp_EnumDbPages; go dbcc extentinfo(PagesAllocationDB,MyTable,-1) go
Теперь мы видим, что был выделен экстент в 8 страниц, их которых, пока заполнена одна.
Если вы добавите еще одну строку, вывод dbcc extentinfo, покажет:
если вы продолжите добавлять записи, вы увидите, как будет увеличиваться значения колонки pg_alloc, пока оно не дойдет до 8, потом выделится следующие 8 страниц и т.д.
Дело в том, что как только таблица становится больше определенного размера (64 кб или 8 страниц), сервер решает, что раз таблица большая, то неплохо бы под нее выделять не по одной странице в смешанном экстенте, а сразу по 8 (т.е. по целому однородному экстенту, в котором будут храниться данные только этой таблицы).
Попробуем удалить все записи из нашей таблицы. И посмотреть что изменилось.
delete from MyTable; go exec sp_EnumDbPages; go dbcc ind(PagesAllocationDB,MyTable,0) go
И как мы увидим — ничего! У таблицы по-прежнему присутствует страница IAM, для нее по-прежнему выделены все структуры данных, и все страницы.
Хитрость в данном случае в том, что таблица является кучей, при удалении из кучи, сиквел сервер накладывает блокировки на строки или страницы и как результат, после удаления всех данных со страницы, сама страница остается «занятой» для этой таблицы и не может быть переиспользована другими.
Поведение похоже на баг, но ребята из МС задокументировали это поведение в BOL — DELETE, Замечания : Удаление строк из кучи — так что это фича. =) Бороться можно тем, что при удалении использовать подсказку tablock.
Проверим, что останется после удаления таким способом. Воссоздадим ситуацию до удаления, пересоздав таблицу и заново добавив туда строки, после чего удалив их с подсказкой tablock.
drop table MyTable; go create table MyTable(id int identity, a char(8000) default 'a'); go insert MyTable default values; go 9 exec sp_EnumDbPages; go dbcc ind(PagesAllocationDB,MyTable,0) go delete from MyTable with(tablock); go exec sp_EnumDbPages; go dbcc ind(PagesAllocationDB,MyTable,0) go
Мы видим, что действительно, страницы с данными удалились, но страница IAM осталась.
Теперь, в довершение ко всему, выполним команду truncate.
truncate table MyTable; go dbcc ind(PagesAllocationDB,MyTable,0) go exec sp_EnumDbPages; go
Как видно из результатов, мы вернулись к тому, с чего начинали, у таблицы теперь нету ни страниц данных, ни IAM, т.е. truncate убивает все, включая страницу IAM.
Смешанные экстенты глобально помечены как «занятые», так что смешанный экстент не сможет быть выделен под какой-либо объект. Однородный экстент может быть освобожден, если освобождаются все его страницы, тогда информация о нем убирается из GAM и из страницы IAM, которой он принадлежал. Важно отметить, что если экстент помечен как выделенный для какой-либо таблицы, это не значит, что все страницы в нем инициализированы. Может ли однородный экстент стать смешанным? Может, если он будет сначала полностью освобожден как однородный и впоследствии будет захвачен как смешенный.
Теперь, можно закрыть все окна, создать новое и удалить БД, т.к. она нам больше не понадобится
use master; go drop database PagesAllocationDB; go
Что такое фрагментация и какая она бывает
Фрагментация, это процесс разделения какой-либо целостной сущности, на множество разрозненных частей — фрагментов. Чем больше фрагментов, тем выше фрагментация.
Говоря о БД, я бы условно разделил виды фрагментации таким образом:
1. Фрагментация файлов БД в файловой системе
2. Фрагментация внутри БД
2.1 Фрагментация внешняя
2.1.1 Логическая фрагментация страниц
2.1.2 Фрагментация экстентов
2.2 Фрагментация внутренняя (плотность страниц)
Теперь вкратце по паре слов по каждой из них.
1. Фрагментация файлов БД в файловой системе
Вспомним, что БД, физически представляет из себя обычные файлы. А как мы знаем, файлы в файловой системе могут подвергаться фрагментации. И многие знают, что в Windows даже есть встроенная утилита «Дефрагментация диска» для борьбы с этим. Этот вид фрагментации в данной заметке нас не интересует.
2. Фрагментация внутри базы данных.
Вспомним, что мы оперируем такими понятиями как таблицы, индексы, страницы, экстенты и т.д. Данные относящиеся к разным объектам не обязательно располагаются в файле упорядоченно и непрерывно. Это и вызывает такого рода фрагментацию. Теперь конкретнее.
2.1.Внешняя фрагментация.
Внешняя фрагментация представляет собой фрагментацию страниц листового уровня индекса или фрагментацию экстентов, если таблица является кучей. Подробнее написано в документации, поэтому чтобы ее не пересказывать, скажу вкратце своими словами. Логическая фрагментация страниц, это когда в двусвязном списке индекса, логически следующая по списку страница, не является следующей в физическом смысле. Фрагментация экстентов — когда физически за экстентом А следующим является экстент В не принадлежащий к тому же объекту что экстент А (т.е. грубо говоря экстенты разных таблиц в файле перемешаны между собой). Проводимый ниже эксперимент касается только этого вида фрагментации.
2.2 Внутренняя фрагментация.
Внутренняя фрагментация, это фрагментация данных на уровне страниц. Здесь не имеется в виду то, что строки идут неупорядочено. Они запросто могут идти неупорядочено, порядок поддерживается на уровне массива указателей на строки. Т.е. при добавлении новой строки, проще изменить порядок указателей в массиве и добавить строку в любое свободное место на странице, чем сдвигать все строки по странице.
Подробнее об этом можно почитать, например, тут в разделе slot array. Это не будет фрагментацией. Здесь под фрагментацией понимается насколько плотно заполнены страницы. Страницы могут быть заполнены не полностью, например, из-за обновлений приводящих к переносу строк или расщеплений страницы в случае индексов и т.д. Понятно, что чем менее плотно лежат данные на странице, тем больше страниц потребуется для представления одной и той же информации. Допустим, на странице помещается 10 записей, всего в таблице 100 записей, если бы страницы были заполнены целиком, то нам потребовалось бы 100/10 = 10 страниц. Теперь допустим, страницы заполнены всего на 50%, тогда нам потребовалось бы 100/(0.5*10) = 20 страниц. Т.е. пришлось бы сделать в два раза больше чтений. Это все довольно прозрачно, поэтому влияние внутренней фрагментации тут не рассматривается.
Кстати, прежде чем бежать и добиваться 100% плотности на страницах, обязательно учтите то, что есть и обратная сторона медали. Если на таблице есть кластерный индекс, то возможен обратный эффект. Если потребуется добавить строку со значением ключа кластерного индекса на страницу, которая заполнена уже на 100% (это не касается последней страницы индекса), то сервер будет вынужден сделать расщепление страницы, что приведет к накладным расходам и еще большей фрагментации. Ведь не зря же придумали такую вещь как fillfactor. Необходимо все это учитывать.
Фрагментация небольших таблиц
В некоторых алгоритмах связанных с фрагментацией, страницы в смешанных экстентах (а как мы помним первые 8 страниц размещаются именно в смешанных экстентах), просто игнорируются, т.к. занимают чрезвычайно малый размер (и, как правило, процент от общих данных), однако сильно усложняют алгоритмы. Об этом написано в статье Пола Рэндала.
Проведем маленький эксперимент: попробуем дефрагментировать таблицу с кластерным индексом размером менее 8КБ.
Пусть, например, это будет таблица сотрудников среднего предприятия, около 600 человек. И вот, воспользовавшись sys.dm_db_index_physical_stats, мы увидели, что у нее фрагментация составляет, более 70% в связи с чем принимаем решение срочно ее дефрагментировать всеми различными способами.
create database smallfragemnt;
go
use smallfragemnt;
go
create table dbo.Employee(
EmplID smallint identity,
LastName char(20) default('a'),
FirstName char(20) default('a'),
MidName char(15) default('a'),
Phone char(15) default('a')
);
go
create clustered index ixc_Employee on dbo.Employee(EmplID);
go
declare @i int=0;
while @i < 600 begin
insert into dbo.Employee default values;
set @i+=1;
end;
select convert(varchar(100),'default') as method, avg_fragmentation_in_percent
into defragresults
from sys.dm_db_index_physical_stats(db_id('smallfragemnt'),object_id('Employee'),null,null,null);
go
dbcc dbreindex(Employee,ixc_Employee);
insert into defragresults
select '1) dbcc dbreindex',avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(db_id('smallfragemnt'),object_id('Employee'),null,null,null);
go 3
dbcc indexdefrag(smallfragemnt,Employee,ixc_Employee) with no_infomsgs;
insert into defragresults
select '2) dbcc indexdefrag',avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(db_id('smallfragemnt'),object_id('Employee'),null,null,null);
go 3
alter index ixc_Employee on dbo.Employee rebuild;
insert into defragresults
select '3) rebuild',avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(db_id('smallfragemnt'),object_id('Employee'),null,null,null);
go 3
alter index ixc_Employee on dbo.Employee reorganize;
insert into defragresults
select '3) reorganize',avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(db_id('smallfragemnt'),object_id('Employee'),null,null,null);
go 3
drop index ixc_Employee on dbo.Employee;
create clustered index ixc_Employee on dbo.Employee(EmplID);
insert into defragresults
select '4) drop create',avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(db_id('smallfragemnt'),object_id('Employee'),null,null,null);
go 3
select * from defragresults;
select page_count from sys.dm_db_index_physical_stats(db_id('smallfragemnt'),object_id('Employee'),null,null,null);
--dbcc extentinfo(smallfragemnt,Employee,-1)
go
use master;
go
drop database smallfragemnt;
Как мы видим, никакие наши ухищрения не помогают, фрагментация все равно присутствует, хотя вроде бы реально работающая таблица, в реальной системе, с полезными данными, аж по 600 сотрудникам. Все дело не в полезности данных и не в количестве строк, а в количестве страниц, если вся информация умещается в 8 страниц — то фрагментация не исчезнет как ни старайся. Она будет оставаться за счет того, что первые 8 страниц все равно выделяются в смешанных экстентах (касается версии сервера > 2000). Что будет если увеличить число сотрудников втрое? Давайте выполним скрипт еще раз, увеличив число записей в три раза «while @i < 600» —> «while @i < 1800»
Как и можно было ожидать, новые страницы выделялись в однородных экстентах, но первые 8 все равно внесли свой вклад во фрагментацию, по этому добиться нулевого значения нам не удалось! Но минутку, неужели нельзя добиться 0%, и фрагментация может снижаться только по мере того как будет снижаться процент страниц размещенных в смешанных экстентах по отношению к остальным? На самом деле нет. Есть очередная магическая цифра, это 24 страницы, как только размер таблицы начинает превышать 24 страницы, начинает использовать другой алгоритм и при перестроении индекса, первые 8 страниц будут выделены в одном экстенте.
Проверьте сами, увеличьте число записей так, чтобы таблица стала занимать более 24 страниц (в данном случае, это примерно 2400 записей), раскомментируйте команду dbcc extentinfo и выполните скрипт еще раз. Вот, что получилось у меня:
Вывод из всего этого можно сделать такой, что при выявлении фрагментации, посмотрите насколько большая у вас таблица, причем не на количество строк или «значимость» данных, а на количество страниц, если их менее 24, то добиться абсолютно нулевой фрагментации, скорее всего, будет практически невозможно.
Что такое read-ahead
read-ahead — это стратегия доступа к данным известная как упреждающее чтение. Сервер пытается предугадать, какие страницы данных потребуются запросу еще до того, как они ему реально понадобятся.
Для выполнения read-ahead сервер использует функцию ReadFileScatter и набор сложных алгоритмов для выявления страниц, которые могут потребоваться в ближайшем будущем. Например, если в запросе используется индекс и искомые строки уже определены, то страницы данных самой таблицы могут быть получены при помощи read-ahead. Важно понимать, что этот вариант использования упреждающего чтения возможный, но далеко не единственный, это лишь один из многих возможных.
Для реализации read-ahead сервер предпринимает следующие шаги:
1. Получить требуемое количество буферов из списка свободных буферов.
2. Для каждой страницы:
2.1. Поиском по хэшу определить не находится ли уже требуемая страница в памяти
2.2. Если находится, то немедленно вернуть захваченный буфер в список свободных.
2.3. Установить верные параметры для вызова функции ReadFileScatter.
2.4. Запросить кратковременную I/O блокировку (latch), чтобы исключить обращения к буферу в момент его заполнения данными страницы из файла.
2.5. Если страница не найдена в хэш таблице, то добавить хэш в таблицу.
3. Вызвать функцию ReadFileScatter для чтения данных.
4. По завершению операции I/O, страница проверяется на валидность, проверяется номер страницы, проверяется не является ли страница неполной (torn)
5. Освобождается краткосрочная блокировка (latch).
Функция ReadFileScatter считывает данные из файла, начиная с позиции, указанной в структуре OVERLAPPED и распределяет данные в несколько буферов пользователя. Вот сигнатура функции:
BOOL ReadFileScatter(
HANDLE hFile, // handle of file to read
FILE_SEGMENT_ELEMENT aSegmentArray[], // array of buffer segments that receives data
DWORD nNumberOfBytesToRead, // number of bytes to read
LPDWORD lpReserved, // reserved must be NULL
LPOVERLAPPED lpOverlapped // address of structure for data
);
Обратите внимание, функция принимает параметр nNumberOfBytesToRead. Количество байт, которые необходимо прочитать. Согласно документации это значение может варьироваться от 8кб до 512 кб (т.е. от одной страницы, до восьми экстентов). Цель эксперимента как раз определить, как фрагментация будет влиять на размер кусков читаемых за одну физическую операцию ввода вывода.
Еще может возникнуть вопрос, что будет если, например, серверу требуется прочитать страницы 1,2,3,4, но страница 3 уже есть в кэше? В таком случае, сервер считает более эффективным не разбивать запрос на два запроса (страницы 1,2 и страница 4) чтобы получить недостающие страницы, а вместо этого считывает все 4 страницы единым блоком, а страница 3 остается в кэше неперезаписанной.
Фрагментация экстентов — эксперимент
Теперь, когда понятны все механизмы, становится понятным и как обеспечить должные условия эксперимента.
Итак, наша задача, создать две базы данных, в обеих базах создать таблицы, намного превышающие размер в 8 КБ, чтобы данные под них выделались экстентами. При этом выделение экстентов таблицы в одной базе данных, чередовать с выделением экстентов во вспомогательной таблице этой же базы, чтобы экстенты перемешивались, и создавалась внешняя фрагментация экстентов (при этом не будет происходить внутренняя фрагментация, и условия эксперимента будут более менее честными, т.к. таблицы в итоге будут занимать в базе одинаковое число страниц). После чего очистить кэш данных, включить сбор статистики и выполнить запрос инициирующий неупорядоченный просмотр таблиц.
Инструменты
Мы будем использовать три инструмента.
1. утилиту FileMon от Марка Руссиновича.
2. set statistics io, time
3. механизм сбора данных при помощи Extended Events
Подготовим данные для эксперимента, создадим две базы данных: heapdb и heapdb_fragmentation. В каждой из них по таблице куче — dbo.heap. Для удобства, размер одной записи в таблице сделаем равным примерно одной странице, чтобы не вставлять много записей.
Кроме того, в БД heapdb_fragmentation создадим вторую таблицу — dbo.mixer. Будем использовать ее для создания фрагментации экстентов.
Далее давайте добавим в каждую таблицу, для начала, по 800 страниц, т.е. 800 записей (т.к. в нашей таблице запись = странице), причем добавляя по 8 записей во вторую таблицу, мы будем также добавлять случайное число записей в таблицу dbo.mixer, для моделирования фрагментации.
------------------------------------------------------------
-- создаем БД без фрагментации
create database heapdb;
go
use heapdb;
go
if object_id('dbo.heap') is not null drop table dbo.heap;
create table dbo.heap(a char(8000) default 'a');
go
-- выполняем 100 итераций
declare @i int = 0;
while @i < 100 begin
-- в каждой итерации добавляем по 8 записей (которые будут занимать 8 страниц, т.е. экстент)
insert into dbo.heap(a) select top 8 'a'from master..spt_values
set @i +=1;
end
go
------------------------------------------------------------
-- создаем БД с фрагментацией
create database heapdb_fragmentation;
go
use heapdb_fragmentation;
go
if object_id('dbo.heap') is not null drop table dbo.heap;
if object_id('dbo.mixer') is not null drop table dbo.mixer;
create table dbo.heap(a char(8000) default 'a');
create table dbo.mixer(a char(8000) default 'a');
go
-- выполняем так же 100 итераций
declare @i int = 0;
while @i < 100 begin
-- в каждой итерации добавлем по 8 записей в исследуемую таблицу и некоторое кол-во записей (кратное 8) в таблицу mixer
insert into dbo.heap(a) select top 8 'a'from master..spt_values
insert into dbo.mixer(a) select top ((abs(checksum(newid()))%3+1)*8) 'a'from master..spt_values
set @i +=1;
end
go
------------------------------------------------------------
-- посмотрим на получившиеся результаты
select * from sys.dm_db_index_physical_stats(db_id('heapdb'),object_id('heap'),null,null,'DETAILED')
select * from sys.dm_db_index_physical_stats(db_id('heapdb_fragmentation'),object_id('heap'),null,null,'DETAILED')
Итак, мы видим, что фрагментация кучи в первой БД менее 2%, во второй более 97%, при этом, таблицы занимают одинаковое число страниц (что исключает влияние внутренней фрагментации страниц).
Создадим в каждой базе по процедуре sp_EnumDbPages и вызовем их, чтобы убедиться в том, как распределились данные.
Вот частичный вывод результата работы процедуры
Итак, ок, мы видим, что в одном случае страницы таблицы располагаются непрерывно, в другом, страницы таблиц перемешаны блоками по 8 штук.
Теперь откроем два окна, и скопируем в каждое из них следующий код.
Окно 1:
-- без фрагментации use heapdb; go checkpoint 10; dbcc dropcleanbuffers; go /* select count(*) from dbo.heap; */
Окно 2:
-- фрагментация use heapdb_fragmentation; go checkpoint 10; dbcc dropcleanbuffers; go /* select count(*) from dbo.heap; */
Далее, возьмем замечательную утилиту от Марка Руссиновича FileMon, которая не только показывает все физические обращения к файлу, но так же указывает и кол-во прочитанных байт в случае операции чтения.
Запустим ее и сразу приостановим сбор событий, т.к. сразу посыпется куча лишней информации. Чтобы получать информацию только интересующем нас процессе, необходимо установить фильтр на процесс sqlservr.exe.
После установки фильтра очистим все уже зарегистрированные события и запустим их сбор снова.
Вернемся в Sql Server Management Studio. Теперь выделим в окне 1 последнюю закомментированную строчку. Вы нажмем F5, т.е. выполним ее. То же самое проделаем в окне 2, выделим строчку и выполним ее. Вернемся в FileMon и остановим трассировку.
Вот какие результаты получились у меня.
Обратите внимание, на выделенные красным части. Понять к какой БД относится событие можно по имени файла. Каждая строчка соответствует одному реальному обращению к файлу. Ясно видно, что в случае с нефрагментированной кучей, сервер использовал намного меньше физических обращений к файлу, при этом читая файл кусками вплоть до length 524288 (512 КБ, как раз та самая цифра, что заявлена в документации). Тогда как в случае фрагментации серверу понадобилось гораздо больше физических обращений, но с меньшим размером читаемой порции данных (в среднем по 65536, т .е. по одному экстенту).
Можно сделать еще любопытнее! FileMon умеет сохранять результаты работы в лог, а так же указывает смещение, по которому проводится чтение в файле. Давайте сохраним и подгрузим результаты в БД.
Я привожу скрипт загрузки файла, но нужно понимать, что я не ставил себе целью сделать парсер логов работы FileMon, и хотя на моем файле лога скрипт отработал без проблем, но я не могу поручиться за то, как он отработает у вас. Хотя если вы делали, как написано выше, то, скорее всего, получили примерно такой же файл, с такой же структурой, и все будет ок.
Выполним и посмотрим, а какие собственно страницы были прочитаны.
Вот, что получилось у меня:
Теперь наглядно можно увидеть, как сервер сначала читает данные из неких служебных таблиц, потом страницы PFS и IAM, и далее начинает читать данные самой таблицы.
Интересно посмотреть, что покажет статистика:
set nocount on; -- без фрагментации use heapdb; go checkpoint; -- сбросим все страницы на физический носитель dbcc dropcleanbuffers with no_infomsgs; -- очистим кэш данных waitfor delay '00:00:01'; -- magic go set statistics io on -- включаем статистику go print 'heapdb:'; select count(*) from dbo.heap; -- инициируем просмотр go set statistics io off go -- фрагментация, выполняеем все те же действия use heapdb_fragmentation; go checkpoint; dbcc dropcleanbuffers with no_infomsgs; waitfor delay '00:00:01'; go set statistics io on go print 'heapdb_fragmentation:'; select count(*) from dbo.heap; go set statistics io off go
Мои результаты:
heapdb:
Table ‘heap’. Scan count 1, logical reads 800, physical reads 18, read-ahead reads 800, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
heapdb_fragmentation:
Table ‘heap’. Scan count 1, logical reads 800, physical reads 64, read-ahead reads 800, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Результаты похожи на то, что мы увидели при помощи FileMon. Число страниц, с том числе и прочитанных упреждающим чтением, совпадает. Но вот число физических обращений к самому файлу увеличилось почти в 4 раза, в случае фрагментированных данных.
Что интересно, пока я писал эту статью, я выполнял этот скрипт много раз и порой получал весьма обескураживающие результаты в поле physical reads. Иногда, например, что-то вроде этого.
Поэтому, мне захотелось, конечно же, проверить, что же все-таки происходит альтернативным способом. И к счастью такой способ отыскался. Это способ сбора информации при помощи Extended Events. Этот механизм в некотором роде позволяет получать результаты напоминающие SQL Trace, однако набор событий, степень детализации и механизм работы — разные. Я не буду описывать механизм extended events, он хорошо подробно и с примерами описан тут. Если вы с ним не знакомы, можете пока просто выполнить скрипт, чтобы увидеть результаты, а про механизм, которым они были получены почитать позже.
Мы будем собирать информацию по следующим событиям: sql_statement_starting, sql_statement_completed, file_read, file_read_completed, physical_page_read, async_io_requested, async_io_completed.
К сожалению, есть один неприятный нюанс, связанный с событием file_read_completed. Для этого события в сиквел сервер 2008, 2008R2 не собирается информация о размере прочитанных из файла данных. Размер прочитанных данных фиксируется только в SQL Server 2012 (aka Denali) А так как все предыдущие испытания проводились на версии 2008R2, то и трассировку мы будем выполнять для нее.
Скрипт делится на три части.
1) В первой, мы создаем трассировку, включаем ее, выполняем запрос, при этом результаты трассировки сохраняются в xml файл. Проделываем эти операции поочередно на двух разных БД.
2) Во второй части, парсим результаты трассировок из разных файлов в таблицу.
3) В третьей анализируем результаты.
1.
use heapdb;
-- создаем трассировку
if exists(select * from sys.server_event_sessions where name='heapdb_trace')
drop event session heapdb_trace on server;
declare @traceddl nvarchar(4000) = '
create event session heapdb_trace
on server
add event sqlserver.sql_statement_starting ( action (sqlserver.sql_text)),
add event sqlserver.sql_statement_completed ( action (sqlserver.sql_text)),
add event sqlserver.file_read ( where (sqlserver.database_id = '+ cast(db_id() as varchar(3))+')),
add event sqlserver.file_read_completed ( where (sqlserver.database_id = '+ cast(db_id() as varchar(3))+')),
add event sqlserver.physical_page_read ( where (sqlserver.database_id = '+ cast(db_id() as varchar(3))+')),
add event sqlos.async_io_requested ( where (sqlserver.database_id = '+ cast(db_id() as varchar(3))+')),
add event sqlos.async_io_completed ( where (sqlserver.database_id = '+ cast(db_id() as varchar(3))+'))
add target package0.asynchronous_file_target( set filename=''c:\heapdb_trace.xel'',metadatafile=''c:\heapdb_trace.xem'')
with (max_memory = 8mb, event_retention_mode = allow_single_event_loss, track_causality = on, max_dispatch_latency=10seconds)'
exec (@traceddl);
go
--очищаем буферный кэш
checkpoint
go
dbcc dropcleanbuffers
go
--запускаем трассировку, выполняем запрос, ждем пока события запишутся в файл и останавливаем трассировку
alter event session heapdb_trace on server state=start;
go
select count(*) from heap;
go
waitfor delay '00:00:15';
go
alter event session heapdb_trace on server state=stop;
go
--- все то же самое для другой БД, только результаты запишем в другой файл
use heapdb_fragmentation;
go
if exists(select * from sys.server_event_sessions where name='heapdb_fragmentation_trace')
drop event session heapdb_fragmentation_trace on server;
declare @traceddl nvarchar(4000) = '
create event session heapdb_fragmentation_trace
on server
add event sqlserver.sql_statement_starting ( action (sqlserver.sql_text)),
add event sqlserver.sql_statement_completed ( action (sqlserver.sql_text)),
add event sqlserver.file_read ( where (sqlserver.database_id = '+ cast(db_id() as varchar(3))+')),
add event sqlserver.file_read_completed ( where (sqlserver.database_id = '+ cast(db_id() as varchar(3))+')),
add event sqlserver.physical_page_read ( where (sqlserver.database_id = '+ cast(db_id() as varchar(3))+')),
add event sqlos.async_io_requested ( where (sqlserver.database_id = '+ cast(db_id() as varchar(3))+')),
add event sqlos.async_io_completed ( where (sqlserver.database_id = '+ cast(db_id() as varchar(3))+'))
add target package0.asynchronous_file_target( set filename=''c:\heapdb_fragmentation_trace.xel'',metadatafile=''c:\heapdb_fragmentation_trace.xem'')
with (max_memory = 8mb, event_retention_mode = allow_single_event_loss, track_causality = on, max_dispatch_latency=10seconds)'
exec (@traceddl);
go
checkpoint
go
dbcc dropcleanbuffers
go
alter event session heapdb_fragmentation_trace on server state=start;
go
select count(*) from heap;
go
waitfor delay '00:00:15';
go
alter event session heapdb_fragmentation_trace on server state=stop;
go
drop event session heapdb_trace on server;
drop event session heapdb_fragmentation_trace on server;
Теперь, загрузим данные из файлов в таблицы
<strong>2.</strong>
use tempdb;
go
if object_id('trace_res') is not null drop table trace_res;
if object_id('trace_res_parsed') is not null drop table trace_res_parsed;
go
create table trace_res (id int identity primary key, event_data xml, dbname varchar(20));
go
insert into trace_res(event_data, dbname)
select cast(event_data as xml), 'heapdb' from sys.fn_xe_file_target_read_file('c:\heapdb_trace*.xel','c:\heapdb_trace*.xem', null, null);
insert into trace_res(event_data, dbname)
select cast(event_data as xml), 'heapdb_fragmentation' from sys.fn_xe_file_target_read_file('c:\heapdb_fragmentation_trace*.xel','c:\heapdb_fragmentation_trace*.xem', null, null);
go
select
id,
event_data.value('(event/@name)[1]', 'varchar(50)') as event_name,
dateadd(hh,
datediff(hh, getutcdate(), current_timestamp),
event_data.value('(event/@timestamp)[1]', 'datetime2')) as [timestamp],
coalesce(event_data.value('(event/data[@name="database_id"]/value)[1]', 'int'),
event_data.value('(event/action[@name="database_id"]/value)[1]', 'int')) as database_id,
event_data.value('(event/data[@name="mode"]/text)[1]', 'nvarchar(4000)') as [mode],
event_data.value('(event/data[@name="file_handle"]/value)[1]', 'nvarchar(4000)') as [file_handle],
event_data.value('(event/data[@name="offset"]/value)[1]', 'bigint') as [offset],
event_data.value('(event/data[@name="page_id"]/value)[1]', 'int') as [page_id],
event_data.value('(event/data[@name="file_id"]/value)[1]', 'int') as [file_id],
event_data.value('(event/data[@name="file_group_id"]/value)[1]', 'int') as [file_group_id],
event_data.value('(event/data[@name="size"]/value)[1]', 'bigint') as ,
event_data.value('(event/data[@name="wait_type"]/text)[1]', 'nvarchar(100)') as [wait_type],
event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') as [duration],
event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(4000)') as [sql_text],
event_data.value('(event/data[@name="cpu"]/value)[1]', 'int') as [cpu],
event_data.value('(event/data[@name="reads"]/value)[1]', 'bigint') as [reads],
event_data.value('(event/data[@name="writes"]/value)[1]', 'bigint') as [writes],
cast(substring(event_data.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)'), 1, 36) as uniqueidentifier) as activity_id,
cast(substring(event_data.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)'), 38, 10) as int) as event_sequence
into
trace_res_parsed
from
trace_res
order by id;
Посмотрим кол-во обращений к файлу
<strong>3.</strong>
use tempdb;
go
declare @heapdb_activity varchar(50),@heapdb_fragmentation_activity varchar(50)
select @heapdb_activity = activity_id
from
trace_res_parsed p
where
event_name = 'sql_statement_starting' and
sql_text like '%select count(*) from heap%' and
dbname = 'heapdb';
select @heapdb_fragmentation_activity = activity_id
from
trace_res_parsed p
where
event_name = 'sql_statement_starting' and
sql_text like '%select count(*) from heap%' and
p.dbname = 'heapdb_fragmentation'
select
event_name,
dbname,
occurences = count(*)
from
trace_res_parsed
where
activity_id in (@heapdb_activity, @heapdb_fragmentation_activity) and
event_name in ('async_io_completed', 'async_io_requested', 'file_read', 'file_read_completed', 'physical_page_read')
group by
event_name,
dbname,
activity_id
order by
activity_id,
event_name
Вот что получилось у меня:
Из этого эксперимента, так же явно видно, что в случае фрагментации, число обращений к файлу довольно сильно возросло. Если бы в 2008R2 собиралась статистика по размеру прочитанных данных, то картина получилась бы еще более ясной, и мы бы увидели разницу в размере порций данных, которыми читает сервер. Впрочем, ничего не мешает повторить эксперимент на сервере 2012, и убедиться в этом.
Имхо
В итоге, про фрагментацию экстентов, хотелось бы написать что-то однозначное типа «да это плохо» или «нет, можно об этом не думать». Но! Как обычно и бывает, серебряной пули нет, и все зависит от ситуации.
Если у вас, таблица куча довольно большая, используется в стиле append-only, т.е. только для добавления данных (что снижает риск внутренней фрагментации на страницах), а это довольно частый сценарий, например для записи каких-нибудь логов или показаний датчиков/приборов. Если при этом на таблице есть много некластерных индексов (что опять же высоко вероятно, т.к., как правило, такие таблицы используются для последующего анализа или построения каких-нибудь отчетов). То особого смысла бороться с фрагментацией экстентов нету. Да, действительно может потребоваться меньше физических обращений к диску, но на сколько меньше? в 8 раз при самой высокой фрагментации, когда каждый экстент фрагментирован, и то при условии, что страниц данных еще нет в кэше! И какую цену за это придется платить, перестроение всех некластерных индексов дважды!
С другой стороны, если таблица небольшая, как мы уже видели раньше, то зачем вообще думать о ее фрагментации?
Таким образом, единственной ситуацией, остается часто таблица, в которой нет большого числа некластерных индексов, средняя по размеру данные которой не только добавляются и удаляются, но очень интенсивно обновляются. Но в таком случае, не проще ли создать кластерный индекс, руководствуясь, например, для выбора его полей замечаниями отсюда Выбор полей для кластерного индекса.?
Как говорится, думайте сами, решайте сами, бороться ли с ветряными мельницами самостоятельно или предоставить эту заботу серверу.
Полезные ссылки
Я старался перечислять полезные ссылки по мере написания, но, на всякий случай, для удобства еще раз перечислю здесь основные:
How It Works: SQL Server Page Allocations
Storage Internal–Page Structure(1) (расшифровка числовых кодов типов страниц)
Storage Internal–Page Structure(2)
Geek City: Removing Single Page Allocations (Частично описывается вывод extentinfo)
Using DBCC PAGE
Table and Index Organization
Heap Structures
SQL Server Storage Engine: on-disk structures
Считывание страниц
Whitepapaer: SQL Server 2000 I/O Basics
Read Ahead BOL
ReadFileScatter function
FileMon for Windows v7.04
Знакомство с расширенными событиями SQL Server
An XEvent a Day
Does Index Fragmentation Matter with SSD’s?
Дмитрий, добрый день
Очень интересный и познавательный блог
Эксперементировал у себя на тесте, при уровне изоляции RCSI удаление из таблицы даже с tablock не приводит к «освобождению» страниц и они остаются «занятыми» этой таблицей