Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: Фрагментации  [new]
Alex_MA
Member

Откуда: Вологда
Сообщений: 190
Правильно ли я понимаю процесс выделения памяти под объекты :

1. Под объект выделяется сразу же экстент;
2. Данные записываются на эти экстенты; Если страницы экстента заполнены, то выделяется следующий экстент;
3. Если в процессе работы необходимо вставить данные не на пустую страницу, то происходит вставка в нужное место и часть данных переноситься - выделяется еще один экстент (или используется уже имеющийся и происходит вставка на конкретную страницу); На месте разбиения создается указатель на экстент с перенесенными данными.

В процессе реорганизации происходит перестроение на листовом уровне. Новые страницы не выделяются.
Теперь данные логически и физически упорядочены:

Картинка с другого сайта.

Однако, как видно остается внутренняя фрагментация. Которая реорганизацией не исправляется - необходим полный ребилд индекса. Происходит перераспределение данных по страницам, заполняется свободное место на страницах со значением FILLFACTOR.
Т.к. данные переносятся с одной страницы на другую, происходит каскадное перестроение B-дерева.

Картинка с другого сайта.

Правильно ?
24 мар 14, 08:19    [15775278]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
Glory
Member

Откуда:
Сообщений: 104760
Alex_MA
1. Под объект выделяется сразу же экстент;

Нет. Иначе бы не было смешанных экстентов
24 мар 14, 10:10    [15775641]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
Alex_MA
Member

Откуда: Вологда
Сообщений: 190
Glory
Alex_MA
1. Под объект выделяется сразу же экстент;

Нет. Иначе бы не было смешанных экстентов

Но ведь страница всегда в определенном экстенте, правильно ?
24 мар 14, 10:48    [15775870]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
Glory
Member

Откуда:
Сообщений: 104760
Alex_MA
3. Если в процессе работы необходимо вставить данные не на пустую страницу, то происходит вставка в нужное место и часть данных переноситься - выделяется еще один экстент (или используется уже имеющийся и происходит вставка на конкретную страницу); На месте разбиения создается указатель на экстент с перенесенными данными.

В конце странице есть ссылка на следующую страницу, а не на экстент.
24 мар 14, 11:42    [15776194]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
Alex_MA
Member

Откуда: Вологда
Сообщений: 190
Правильно ли я нарисовал ребилд индекса ?
24 мар 14, 17:03    [15778980]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
Glory
Member

Откуда:
Сообщений: 104760
Alex_MA
Правильно ли я нарисовал ребилд индекса ?

Rebuilding an index drops the index and creates a new one.

When indexes with 128 extents or more are rebuilt, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits.

BOL - ALTER INDEX, Dropping and Rebuilding Large Objects
24 мар 14, 17:22    [15779136]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
Alex_MA
Member

Откуда: Вологда
Сообщений: 190
Уважаемые коллеги, не могу понять еще один момент FILFACTOR. Объясните пожалуйста на пальцах.
Если он выставлен в 100% то страницы заполняются как можно плотнее, рекомендуется использовать в OLAP системах.
В OLTP советуют около 60-80%.

Как я понимаю, то если выставить 60-80%, то при вставке данных на страницу будет заполняться 60-80% места на странице.
Не могу понять, почему меньше вероятность вызова операций PageSplit ?
Вся суть FILFACTOR сводиться к тому, чтобы было меньше разбиений страниц ? Но почему их будет меньше, если мы заполнили страницу на 80%, а в эту страницу между данными нужно вставить еще данные -> page split.
24 мар 14, 17:52    [15779325]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
Glory
Member

Откуда:
Сообщений: 104760
Alex_MA
Но почему их будет меньше, если мы заполнили страницу на 80%, а в эту страницу между данными нужно вставить еще данные -> page split.

Потому что page split происходит только тогда, когда на странице НЕТ свободного места.
Зачем делать page split, если новая запись помещается на страницу ?
24 мар 14, 17:56    [15779357]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
Alex_MA
Member

Откуда: Вологда
Сообщений: 190
Glory
Alex_MA
Но почему их будет меньше, если мы заполнили страницу на 80%, а в эту страницу между данными нужно вставить еще данные -> page split.

Потому что page split происходит только тогда, когда на странице НЕТ свободного места.
Зачем делать page split, если новая запись помещается на страницу ?


Она не помещается, ведь 80% уже заполнены.
24 мар 14, 18:03    [15779400]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
Glory
Member

Откуда:
Сообщений: 104760
Alex_MA
Она не помещается, ведь 80% уже заполнены.

А сколько свободного места на странице нужно для вашей одной записи ? 100% что ли ?
24 мар 14, 18:05    [15779418]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
o-o
Guest
Glory
Alex_MA
Правильно ли я нарисовал ребилд индекса ?

Rebuilding an index drops the index and creates a new one.

When indexes with 128 extents or more are rebuilt, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits.

BOL - ALTER INDEX, Dropping and Rebuilding Large Objects


ТС писал про reorganize, когда картинку рисовал, а потом обозвал это rebuild :)
24 мар 14, 18:14    [15779485]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
Glory
Member

Откуда:
Сообщений: 104760
o-o
Glory
пропущено...

Rebuilding an index drops the index and creates a new one.

When indexes with 128 extents or more are rebuilt, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits.

BOL - ALTER INDEX, Dropping and Rebuilding Large Objects


ТС писал про reorganize, когда картинку рисовал, а потом обозвал это rebuild :)

А это предложение тогда про что "Однако, как видно остается внутренняя фрагментация. Которая реорганизацией не исправляется - необходим полный ребилд индекса."
24 мар 14, 18:15    [15779499]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
o-o
Guest
Alex_MA
Glory
пропущено...

Потому что page split происходит только тогда, когда на странице НЕТ свободного места.
Зачем делать page split, если новая запись помещается на страницу ?


Она не помещается, ведь 80% уже заполнены.


ок, на пальцах.
на страницу (для примера) можно уложить 10 записей
(пускай фиксированного размера записи). это называется 100%.
указали филфактор 80%, на странице 8 записей.
2 можно вписать.
на 3-ей произойдет page split.
указали 60%, еще 4 можно вставить, на 5-ой будет page split
24 мар 14, 18:17    [15779506]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
Alex_MA
Member

Откуда: Вологда
Сообщений: 190
o-o
Alex_MA
пропущено...


Она не помещается, ведь 80% уже заполнены.


ок, на пальцах.
на страницу (для примера) можно уложить 10 записей
(пускай фиксированного размера записи). это называется 100%.
указали филфактор 80%, на странице 8 записей.
2 можно вписать.
на 3-ей произойдет page split.
указали 60%, еще 4 можно вставить, на 5-ой будет page split


C таким же успехом можно ивыставить FILFAACTOR 100% и так же вписать эти 2 записи без page split.
Наверно я что то не догоняю :)
24 мар 14, 20:22    [15779985]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
Alex_MA
Member

Откуда: Вологда
Сообщений: 190
o-o
Glory
пропущено...

Rebuilding an index drops the index and creates a new one.

When indexes with 128 extents or more are rebuilt, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits.

BOL - ALTER INDEX, Dropping and Rebuilding Large Objects


ТС писал про reorganize, когда картинку рисовал, а потом обозвал это rebuild :)


o-o
Glory
пропущено...

Rebuilding an index drops the index and creates a new one.

When indexes with 128 extents or more are rebuilt, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits.

BOL - ALTER INDEX, Dropping and Rebuilding Large Objects


ТС писал про reorganize, когда картинку рисовал, а потом обозвал это rebuild :)


Реорганайз:
Картинка с другого сайта.

Ребилд:
Картинка с другого сайта.


Уж простите, просто хотелось бы разобраться.
24 мар 14, 20:30    [15780025]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Alex_MA
C таким же успехом можно ивыставить FILFAACTOR 100% и так же вписать эти 2 записи без page split.
Наверно я что то не догоняю :)
Куда вписать, если в этой странице уже нет места?!! А вставить то надо их в середину, согласно упорядоченности.

Другое дело когда не подразумевается вставка в середину, а только в конец (к примеру PK на автоинкрементное поле).
24 мар 14, 20:56    [15780148]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Alex_MA
Как я понимаю, то если выставить 60-80%, то при вставке данных на страницу будет заполняться 60-80% места на странице.
Не могу понять, почему меньше вероятность вызова операций PageSplit ?
Вся суть FILFACTOR сводиться к тому, чтобы было меньше разбиений страниц ? Но почему их будет меньше, если мы заполнили страницу на 80%, а в эту страницу между данными нужно вставить еще данные -> page split.
Во время вставки данных в индекс, fillfactor не учитывается. Страницы будут заполняться до 100%.
http://technet.microsoft.com/en-us/library/ms188783.aspx
The FILLFACTOR setting applies only when the index is created or rebuilt. The Database Engine does not dynamically keep the specified percentage of empty space in the pages.
24 мар 14, 21:20    [15780257]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Не думал что фраза «логически следующая по списку страница, не является следующей в физическом смысле», так трудно понимаема.

Тогда пример, лучше всего.

Создадим 2-е одинаковых таблицы t1, t2. В каждой таблице три столбца (a int, b int, c varchar(26)), a, b составляют уникальный кластерный индекс, c – заполнитель varchar(26).

Данные в таблицах, для удобства демо, подобраны так, что столбец a для каждой страницы один и тот же и одно значение приходится на одну страницу, т.е. первая страница таблицы содержит строки: (1,1,'bbb'), (1,2,'bbb'), (1,3,'bbb') и т.д… Вторая (2,1,'bbb'), (2,2,'bbb'), (2,3,'bbb') и т.д…

Для просмотра содержимого БД, я использую представление dm_db_database_page_allocations, которое появилось в 2012 версии (для версии 2008R2 я делал когда-то процедуру sp_EnumDbPages).

Кроме того, для определения, на какой странице лежит часть ключа, значение столбца a, я использую недокументированную функцию fn_PhysLocFormatter, которая отдает данные в виде (file_id:page_id:slot_id), нас интересует страница, так что я буду парсить ее номер. Все эти действия оформил в процедуру fragdb_pages_get, для удобства повторных вызовов.

+ Init
/*
use master;
go
drop database frag;
go
*/
--create db
create database frag;
go
use frag;
go

--create tables
create table t1(a int not null, b int not null, c varchar(26) not null)
create unique clustered index cix_ab on t1(a,b);
create table t2(a int not null, b int not null, c varchar(26) not null)
create unique clustered index cix_ab on t2(a,b);
go

--create function to generate page rows
create function dbo.page_rows(@a int)
returns table
as
return (
	select top(238) --average 238 rows per page
		a = @a,
		b = row_number() over(order by(select null)),
		c = replicate('c',13)
	from master..spt_values
)
go

--create function to watch db pages
create proc fragdb_pages_get
as
with 
	metadata_t1 as (select distinct a, pg = convert(int,parsename(replace(sys.fn_PhysLocFormatter (%%physloc%%),':','.'),2)) from t1 with(nolock)),
	metadata_t2 as (select distinct a, pg = convert(int,parsename(replace(sys.fn_PhysLocFormatter (%%physloc%%),':','.'),2)) from t2 with(nolock))
select 
	objname = object_name(a.[object_id]),
	page_id = a.allocated_page_page_id,
	is_mixed = a.is_mixed_page_allocation,
	pg_descr = a.page_type_desc,
	a_key = 
		case 
			when object_name(a.[object_id]) = 't1' then isnull(convert(varchar(10),mt1.a),'N/A')
			when object_name(a.[object_id]) = 't2' then isnull(convert(varchar(10),mt2.a),'N/A')
			else 'N/A'
		end
into #t
from 
	sys.dm_db_database_page_allocations(db_id(),null,null,0,'DETAILED') a 
	left join metadata_t1 mt1 on mt1.pg = a.allocated_page_page_id and a.page_type_desc = 'DATA_PAGE'
	left join metadata_t2 mt2 on mt2.pg = a.allocated_page_page_id and a.page_type_desc = 'DATA_PAGE'
where 
	object_id > 100 and
	a.page_type_desc <> 'IAM_PAGE'

create clustered index cix on #t (page_id,objname,pg_descr,is_mixed);

select distinct
	t1.page_id,
	t1.objname,
	t1.pg_descr,
	t1.is_mixed,
	a_key = stuff(t.x.value('.','nvarchar(max)'),1,2,'')
from 
	#t t1 
	cross apply (
		select ', ' + t.a_key as 'data()' from #t t
		where t1.page_id = t.page_id and t1.objname = t.objname and t1.pg_descr = t.pg_descr and t1.is_mixed = t.is_mixed
		for xml path(''),type
	) t(x)
order by
	t1.page_id	
go

--fill t1
insert t1(a, b, c)
select pr.a, pr.b, pr.c
from
	(
		select 1 
		union all select 2
	) extent_count(c) --2 extents
	cross apply
	(
		select 1 union all 
		select 2 union all 
		select 3 union all 
		select 4 union all 
		select 5 union all 
		select 6 union all 
		select 7 union all 
		select 8 where extent_count.c > 1 -- consider INDEX_PAGE for the first extent
	) page_count(c) -- 8 pages
	cross apply page_rows(page_count.c + 8*(extent_count.c-1)) pr
go
--fill t2
insert t2(a,b,c) select a,b, c from t1;
--fill t1 two more extents
insert t1(a, b, c)
select pr.a, pr.b, pr.c
from
	(
		select 3 
		union all select 4
	) extent_count(c) --2 extents
	cross apply
	(
		select 1 union all 
		select 2 union all 
		select 3 union all 
		select 4 union all 
		select 5 union all 
		select 6 union all 
		select 7 union all 
		select 8
	) page_count(c) -- 8 pages
	cross apply page_rows(page_count.c + 8*(extent_count.c-1)) pr
go
--fill t2 two more extents
insert t2(a,b,c) select a, b, c from t1 where a > 16;
go


В результате заполнения имеем такую картинку после заполнения.
-- 1. initial
exec fragdb_pages_get;
select * from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),1,0,'DETAILED');
go

+
Картинка с другого сайта.

Пояснения к картинке:
1. Границы по экстентам.
2. Бледно синим раскрашены страницы принадлежащие t1, с которой экспериментируем.
3. В заголовке действие, в данном случае «1. INITIAL» - начальное состояние
4. Уровень фрагментации из dm_db_index_physical_stats
5. Поля: PAGE – номер страницы в файле БД, OBJNAME – название объекта которому принадлежит страница, PG TYPE – тип страницы, IS MIXED – страница в смешанном экстенте (1 и 0), A_KEY значения первой колонки ключа (колонки a) на странице.
6. В финальной картинке, изменения каждого следующего шага выделены своим цветом

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

Фрагментация
Дальше начинаем делать манипуляции, увеличивая шаг за шагом фрагментацию, скрипт ниже. Что происходит в скрипте, написал в комментариях, да и понятно из названия шага.

+ Фрагментируем
-- 1. initial
exec fragdb_pages_get;
select * from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),1,0,'DETAILED');
go

-- 2. delete + insert
delete from t1 where a in (9,10,11,12); --delete
dbcc forceghostcleanup with no_infomsgs; 
go
insert t1(a,b,c) 
select  a, b, c from dbo.page_rows(9) union all select  a, b, c from dbo.page_rows(10) union all
select  a, b, c from dbo.page_rows(11) union all select  a, b, c from dbo.page_rows(12)
go
exec fragdb_pages_get;
select * from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),1,0,'DETAILED');
go

-- 3. truncate + insert
truncate table t2;
dbcc forceghostcleanup with no_infomsgs;
go
insert t1(a,b,c) 
select  a, b, c from dbo.page_rows(-1) union all select  a, b, c from dbo.page_rows(-2)
insert t1(a,b,c) 
select  a, b, c from dbo.page_rows(100) union all select  a, b, c from dbo.page_rows(101)
go
exec fragdb_pages_get;
select * from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),1,0,'DETAILED');
go

--4. insert + page split
insert t1 select top(20) a, b-20, c from dbo.page_rows(17);
insert t1 select top(20) a, b-20, c from dbo.page_rows(19);
insert t1 select top(20) a, b-20, c from dbo.page_rows(21);
insert t1 select top(20) a, b-20, c from dbo.page_rows(23);
insert t1 select top(20) a, b-20, c from dbo.page_rows(25);
insert t1 select top(20) a, b-20, c from dbo.page_rows(27);
insert t1 select top(20) a, b-20, c from dbo.page_rows(29);
insert t1 select top(20) a, b-20, c from dbo.page_rows(31);
go
exec fragdb_pages_get;
select * from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),1,0,'DETAILED');
go

--5. update - add more data
update t1 set c = replicate('c',26)
go
exec fragdb_pages_get;
select * from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),1,0,'DETAILED');
go


Результат - часть финальной картинки и файл с результатами, откуда она была сделана.
Картинка с другого сайта.
fragdb.xlsx (23 kB)

Видно как от шага к шагу данные в таблице перемешиваются.
Посмотрите на последнем шаге (если картинку плохо видно, можно открыть Excel, если он есть конечно), разве следуют там данные в порядке ключа, хоть в смешанных, хоть в однородных экстентах.

Чтобы сымитировать чтения в порядке ключа, можно поставить палец на первое значение в шаге INITIAL и перемешать его на следующее логическое значение ключа. Потом то же самое проделать с шагом пять - посмотрите, сколько раз будет бегать палец по экрану вверх-вниз по сравнению с шагом INITIAL. Это и есть логическая фрагментация.
24 мар 14, 21:38    [15780343]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
Alex_MA
Member

Откуда: Вологда
Сообщений: 190
SomewhereSomehow
Не думал что фраза «логически следующая по списку страница, не является следующей в физическом смысле», так трудно понимаема.

Тогда пример, лучше всего.

Создадим 2-е одинаковых таблицы t1, t2. В каждой таблице три столбца (a int, b int, c varchar(26)), a, b составляют уникальный кластерный индекс, c – заполнитель varchar(26).

Данные в таблицах, для удобства демо, подобраны так, что столбец a для каждой страницы один и тот же и одно значение приходится на одну страницу, т.е. первая страница таблицы содержит строки: (1,1,'bbb'), (1,2,'bbb'), (1,3,'bbb') и т.д… Вторая (2,1,'bbb'), (2,2,'bbb'), (2,3,'bbb') и т.д…

Для просмотра содержимого БД, я использую представление dm_db_database_page_allocations, которое появилось в 2012 версии (для версии 2008R2 я делал когда-то процедуру sp_EnumDbPages).

Кроме того, для определения, на какой странице лежит часть ключа, значение столбца a, я использую недокументированную функцию fn_PhysLocFormatter, которая отдает данные в виде (file_id:page_id:slot_id), нас интересует страница, так что я буду парсить ее номер. Все эти действия оформил в процедуру fragdb_pages_get, для удобства повторных вызовов.

+ Init
/*
use master;
go
drop database frag;
go
*/
--create db
create database frag;
go
use frag;
go

--create tables
create table t1(a int not null, b int not null, c varchar(26) not null)
create unique clustered index cix_ab on t1(a,b);
create table t2(a int not null, b int not null, c varchar(26) not null)
create unique clustered index cix_ab on t2(a,b);
go

--create function to generate page rows
create function dbo.page_rows(@a int)
returns table
as
return (
	select top(238) --average 238 rows per page
		a = @a,
		b = row_number() over(order by(select null)),
		c = replicate('c',13)
	from master..spt_values
)
go

--create function to watch db pages
create proc fragdb_pages_get
as
with 
	metadata_t1 as (select distinct a, pg = convert(int,parsename(replace(sys.fn_PhysLocFormatter (%%physloc%%),':','.'),2)) from t1 with(nolock)),
	metadata_t2 as (select distinct a, pg = convert(int,parsename(replace(sys.fn_PhysLocFormatter (%%physloc%%),':','.'),2)) from t2 with(nolock))
select 
	objname = object_name(a.[object_id]),
	page_id = a.allocated_page_page_id,
	is_mixed = a.is_mixed_page_allocation,
	pg_descr = a.page_type_desc,
	a_key = 
		case 
			when object_name(a.[object_id]) = 't1' then isnull(convert(varchar(10),mt1.a),'N/A')
			when object_name(a.[object_id]) = 't2' then isnull(convert(varchar(10),mt2.a),'N/A')
			else 'N/A'
		end
into #t
from 
	sys.dm_db_database_page_allocations(db_id(),null,null,0,'DETAILED') a 
	left join metadata_t1 mt1 on mt1.pg = a.allocated_page_page_id and a.page_type_desc = 'DATA_PAGE'
	left join metadata_t2 mt2 on mt2.pg = a.allocated_page_page_id and a.page_type_desc = 'DATA_PAGE'
where 
	object_id > 100 and
	a.page_type_desc <> 'IAM_PAGE'

create clustered index cix on #t (page_id,objname,pg_descr,is_mixed);

select distinct
	t1.page_id,
	t1.objname,
	t1.pg_descr,
	t1.is_mixed,
	a_key = stuff(t.x.value('.','nvarchar(max)'),1,2,'')
from 
	#t t1 
	cross apply (
		select ', ' + t.a_key as 'data()' from #t t
		where t1.page_id = t.page_id and t1.objname = t.objname and t1.pg_descr = t.pg_descr and t1.is_mixed = t.is_mixed
		for xml path(''),type
	) t(x)
order by
	t1.page_id	
go

--fill t1
insert t1(a, b, c)
select pr.a, pr.b, pr.c
from
	(
		select 1 
		union all select 2
	) extent_count(c) --2 extents
	cross apply
	(
		select 1 union all 
		select 2 union all 
		select 3 union all 
		select 4 union all 
		select 5 union all 
		select 6 union all 
		select 7 union all 
		select 8 where extent_count.c > 1 -- consider INDEX_PAGE for the first extent
	) page_count(c) -- 8 pages
	cross apply page_rows(page_count.c + 8*(extent_count.c-1)) pr
go
--fill t2
insert t2(a,b,c) select a,b, c from t1;
--fill t1 two more extents
insert t1(a, b, c)
select pr.a, pr.b, pr.c
from
	(
		select 3 
		union all select 4
	) extent_count(c) --2 extents
	cross apply
	(
		select 1 union all 
		select 2 union all 
		select 3 union all 
		select 4 union all 
		select 5 union all 
		select 6 union all 
		select 7 union all 
		select 8
	) page_count(c) -- 8 pages
	cross apply page_rows(page_count.c + 8*(extent_count.c-1)) pr
go
--fill t2 two more extents
insert t2(a,b,c) select a, b, c from t1 where a > 16;
go


В результате заполнения имеем такую картинку после заполнения.
-- 1. initial
exec fragdb_pages_get;
select * from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),1,0,'DETAILED');
go

+
Картинка с другого сайта.

Пояснения к картинке:
1. Границы по экстентам.
2. Бледно синим раскрашены страницы принадлежащие t1, с которой экспериментируем.
3. В заголовке действие, в данном случае «1. INITIAL» - начальное состояние
4. Уровень фрагментации из dm_db_index_physical_stats
5. Поля: PAGE – номер страницы в файле БД, OBJNAME – название объекта которому принадлежит страница, PG TYPE – тип страницы, IS MIXED – страница в смешанном экстенте (1 и 0), A_KEY значения первой колонки ключа (колонки a) на странице.
6. В финальной картинке, изменения каждого следующего шага выделены своим цветом

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

Фрагментация
Дальше начинаем делать манипуляции, увеличивая шаг за шагом фрагментацию, скрипт ниже. Что происходит в скрипте, написал в комментариях, да и понятно из названия шага.

+ Фрагментируем
-- 1. initial
exec fragdb_pages_get;
select * from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),1,0,'DETAILED');
go

-- 2. delete + insert
delete from t1 where a in (9,10,11,12); --delete
dbcc forceghostcleanup with no_infomsgs; 
go
insert t1(a,b,c) 
select  a, b, c from dbo.page_rows(9) union all select  a, b, c from dbo.page_rows(10) union all
select  a, b, c from dbo.page_rows(11) union all select  a, b, c from dbo.page_rows(12)
go
exec fragdb_pages_get;
select * from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),1,0,'DETAILED');
go

-- 3. truncate + insert
truncate table t2;
dbcc forceghostcleanup with no_infomsgs;
go
insert t1(a,b,c) 
select  a, b, c from dbo.page_rows(-1) union all select  a, b, c from dbo.page_rows(-2)
insert t1(a,b,c) 
select  a, b, c from dbo.page_rows(100) union all select  a, b, c from dbo.page_rows(101)
go
exec fragdb_pages_get;
select * from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),1,0,'DETAILED');
go

--4. insert + page split
insert t1 select top(20) a, b-20, c from dbo.page_rows(17);
insert t1 select top(20) a, b-20, c from dbo.page_rows(19);
insert t1 select top(20) a, b-20, c from dbo.page_rows(21);
insert t1 select top(20) a, b-20, c from dbo.page_rows(23);
insert t1 select top(20) a, b-20, c from dbo.page_rows(25);
insert t1 select top(20) a, b-20, c from dbo.page_rows(27);
insert t1 select top(20) a, b-20, c from dbo.page_rows(29);
insert t1 select top(20) a, b-20, c from dbo.page_rows(31);
go
exec fragdb_pages_get;
select * from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),1,0,'DETAILED');
go

--5. update - add more data
update t1 set c = replicate('c',26)
go
exec fragdb_pages_get;
select * from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),1,0,'DETAILED');
go


Результат - часть финальной картинки и файл с результатами, откуда она была сделана.
Картинка с другого сайта.
fragdb.xlsx (23 kB)

Видно как от шага к шагу данные в таблице перемешиваются.
Посмотрите на последнем шаге (если картинку плохо видно, можно открыть Excel, если он есть конечно), разве следуют там данные в порядке ключа, хоть в смешанных, хоть в однородных экстентах.

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


Огромное спасибо. Все понятно.
25 мар 14, 07:57    [15781195]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
Alex_MA
Member

Откуда: Вологда
Сообщений: 190
Давайте разберемся с FILFACTOR - по нему все таки остаются вопросы.

Если выставить FILFACTOR отличный от 100%, к примеру 60%, то данные на страницах будут заполнены на 60%.
Если на страницу, заполненную на 60% необходимо произвести вставку данных (данные, которые уместится на текущей странице), то эта вставка будет произведена, а данные на странице просто будут сдвинуты ?

Рассмотрим FILFACTOR=100%:
Допустим страница тоже заполнена на 60%. И тоже требуется вставить данные (данные, которые уместится на текущей странице), то эти данные будут вставлены, а данные на странице так же сдвинуты.

В чем разница понять не могу. Просветите безграмотного.
25 мар 14, 08:13    [15781222]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3274
Alex_MA
Если на страницу, заполненную на 60% необходимо произвести вставку данных (данные, которые уместится на текущей странице), то эта вставка будет произведена, а данные на странице просто будут сдвинуты ?
Вы, похоже, упускаете один момент - фрагментация внутри страницы не считается за фрагментацию. Поэтому вставка записи в страницу, заполненную за 60%, произойдет в некий свободный слот, независимо от порядка заполненных слотов в этой странице. И ничего никуда не "сдвинется".
25 мар 14, 08:26    [15781257]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
Alex_MA
Member

Откуда: Вологда
Сообщений: 190
Ennor Tiegael
Alex_MA
Если на страницу, заполненную на 60% необходимо произвести вставку данных (данные, которые уместится на текущей странице), то эта вставка будет произведена, а данные на странице просто будут сдвинуты ?
Вы, похоже, упускаете один момент - фрагментация внутри страницы не считается за фрагментацию. Поэтому вставка записи в страницу, заполненную за 60%, произойдет в некий свободный слот, независимо от порядка заполненных слотов в этой странице. И ничего никуда не "сдвинется".


Т.е. будет использовано уже больше 60% свободного места на странице ?
25 мар 14, 09:01    [15781372]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Alex_MA
Если выставить FILFACTOR отличный от 100%, к примеру 60%, то данные на страницах будут заполнены на 60%.
Если на страницу, заполненную на 60% необходимо произвести вставку данных (данные, которые уместится на текущей странице), то эта вставка будет произведена, а данные на странице просто будут сдвинуты ?

Рассмотрим FILFACTOR=100%:
Допустим страница тоже заполнена на 60%. И тоже требуется вставить данные (данные, которые уместится на текущей странице), то эти данные будут вставлены, а данные на странице так же сдвинуты.

В чем разница понять не могу. Просветите безграмотного.


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

Это не так - это опция времени построения индекса. Все равно как, например указать опцию SORT_IN_TEMPDB - да во время построения будет использована tempdb, после построения, индекс живет своей жизнью. Тоже и с FILFACTOR, если вы указали значение 100%, то это значение будет использоваться при построении индекса, после этого - индекс живет своей жизнью. Единственное что, в отличие от SORT_IN_TEMPDB, для FILFACTOR SQL Server сохраняет в метаданных информацию о том, с каким значением был построен индекс изначально. Возможно, это сбивает с толку, заставляя думать, что эта некоторая "опция" индекса, а не "опция" операции построения/перестроения. Но если присмотреться:
select OrigFillFactor from sys.sysindexes
select INDEXPROPERTY ( object_ID , index_or_statistics_name , 'IndexFillFactor' ) 

В первом случе OrigFillFactor - т.е. оригинальный/первоначальный, с которым был создан индекс.
Для свойства индекса IndexFillFactor в справке, читаем: "Значение коэффициента заполнения, использованное при создании индекса или при его последней перестройке."

По этому, описанная ситуация, когда вы во время перестроения указали FILFACTOR=100%, а после него страницы оказались заполнены на 60% невозможна, если только данных хватает и нет каких-то багов.
25 мар 14, 09:22    [15781444]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3274
Alex_MA
Ennor Tiegael
пропущено...
Вы, похоже, упускаете один момент - фрагментация внутри страницы не считается за фрагментацию. Поэтому вставка записи в страницу, заполненную за 60%, произойдет в некий свободный слот, независимо от порядка заполненных слотов в этой странице. И ничего никуда не "сдвинется".


Т.е. будет использовано уже больше 60% свободного места на странице ?
Ну разумеется. Вам Mind уже приводил цитату - филфактор влияет только на ребилд, при вставке страница заполняется до конца, насколько возможно.

Иначе смысла в этой опции бы не было - зачем резервировать место, которое никогда не будет использоваться?..
25 мар 14, 09:32    [15781488]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
Alex_MA
Member

Откуда: Вологда
Сообщений: 190
Правильно ли я представляю картину:
Картинка с другого сайта.
25 мар 14, 09:43    [15781544]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить