Здравствуйте, Sinclair, Вы писали:
_AB>>Но просто перейдет к новой странице, как только старая заполнится — это да. S>Хм. А разве он не сделает page split крайней страницы в момент её заполнения?
И нет и да. Протестировал как раз перед тем, как написать пост. Тестировал на 2012-м,
но уверен в аналогичном поведении как минимум с 2008-го.
Да — логически операция одна — page split, но она оптимизированна для вставки в конец.
Нет — при выделении страницы в конце индекса он выделяет новую страницу и пишет туда только новую запись.
При разделении в середине он делит страницу пополам и добавляет запись на одну из поделенных страниц.
S>А зачем писать page split в журнал транзакций? S>Разве эта информация нужна для undo/redo?
Many types of operations are recorded in the transaction log. These operations include:
* Every extent and page allocation or deallocation
Все операции со страницами пишутся в лог.
Но на самом деле в журнал пишется информация не просто о выделении новой страницы, а именно о том,
что произошел page split. И там есть несколько логических операций, одна из которых применяется
только для разделения страниц в середине индекса. Это удаление записей на старой странице.
В любом случае, эта информация в том или оном виде нужна для undo/redo.
Каждая запись в логе о модификации строки содержит в себе физический адрес записи (page id, slot id).
Поэтому без логгирования операций со страницами никак не обойтись.
[Добавлено]. Собственно, журналирование изменения адреса перенесенных на новую страницу данных и есть основной
фактор в данном случае. Чем больше записей было на разделенной странице, тем больше места потребуется в журнале.
Здравствуйте, _ABC_, Вы писали:
ABC> Да — логически операция одна — page split, но она оптимизированна для вставки в конец. ABC> Нет — при выделении страницы в конце индекса он выделяет новую страницу и пишет туда только новую запись. ABC> При разделении в середине он делит страницу пополам и добавляет запись на одну из поделенных страниц.
Вот об этом я и говорил. Спасибо, моя уверенность в SQL Server team восстановлена.
Hardware eventually fails. Software eventually works. ::: avalon/1.0.442
Re[14]: [SQL Server] Анализ всех запросов к таблице
Здравствуйте, Sinclair, Вы писали:
S>Здравствуйте, Somescout, Вы писали:
S>Это если у вас страница, куда происходит вставка, заполнена на 100%. Это же бывает достаточно редко. Точнее — мы имеем o(log(n)) операций в худшем случае.
По умолчанию fillfactor=100% и все leaf страницы полностью заполнены, а non-leaf (так и не знаю как правильно назвать это — branch, trunk?) резервируют место под одну запись.
S>Что такое "фрагментация таблицы"?
Это физическая (гм...) непоследовательность страниц таблицы(индекса) и заполненность страниц. А поскольку при вставке в середину таблицы страница делится пополам, то это повышает фрагментацию. Насколько я понимаю, SQL Server использует read-ahead поэтому порядок страниц может влиять на скорость их загрузки, кроме того если индекс подошёл для index seek, там будет именно последовательное чтение страниц и любая фрагментация его замедлит.
S>Да, и почему вы думаете, что вставка "в конец" не будет првиводить к разбивке страницы? Ведь в b-tree все leaf-страницы одинаковые — что "в середине", что "с краю".
На этот вопрос уже ответили.
Здравствуйте, Somescout, Вы писали:
S>>Что такое "фрагментация таблицы"? S>Это физическая (гм...) непоследовательность страниц таблицы(индекса) и заполненность страниц. А поскольку при вставке в середину таблицы страница делится пополам, то это повышает фрагментацию. Насколько я понимаю, SQL Server использует read-ahead поэтому порядок страниц может влиять на скорость их загрузки, кроме того если индекс подошёл для index seek, там будет именно последовательное чтение страниц и любая фрагментация его замедлит.
В случае index seek как раз не будет последовательного чтения страниц. Там будет случайное чтение от root page до leaf page со всеми
необходимыми остановками. На эту операцию фрагментация фактически не влияет.
Re[16]: [SQL Server] Анализ всех запросов к таблице
Здравствуйте, _ABC_, Вы писали:
_AB>В случае index seek как раз не будет последовательного чтения страниц. Там будет случайное чтение от root page до leaf page со всеми _AB>необходимыми остановками. На эту операцию фрагментация фактически не влияет.
Разве? Index seek выполняется когда предикат индекса совпадает с предикатом запроса, т.е. index (a) и (where a between x and y) в этом случае находится первая страница диапазона и читается до последней. Хотя в более сложных случаях доступ будет более произвольным (index(a,b), where a>x and b<y), но в любом случае чтение идёт диапазонами строк: находим первую и последнюю строку соответствую условию и читаем всё между ними.
Кстати, index scan будет работать точно также, правда при этом обрабатываются и не соответствующие условию строки входящие в диапазон.
И во всех случаях влияние фрагментации будет зависеть от длинны диапазона и размера одной строки.
Здравствуйте, Somescout, Вы писали:
S>Здравствуйте, _ABC_, Вы писали:
S>Разве? Index seek выполняется когда предикат индекса совпадает с предикатом запроса, т.е. index (a) и (where a between x and y) в этом случае находится первая страница диапазона и читается до последней.
По сути это уже этакий range scan.
Я думал, ты говоришь о поиске по равенству.
Re[18]: [SQL Server] Анализ всех запросов к таблице
Здравствуйте, _ABC_, Вы писали: _AB>Здравствуйте, Sinclair, Вы писали: _AB>>>Но просто перейдет к новой странице, как только старая заполнится — это да. S>>Хм. А разве он не сделает page split крайней страницы в момент её заполнения? _AB>И нет и да. Протестировал как раз перед тем, как написать пост. Тестировал на 2012-м, _AB>но уверен в аналогичном поведении как минимум с 2008-го. _AB>Да — логически операция одна — page split, но она оптимизированна для вставки в конец. _AB>Нет — при выделении страницы в конце индекса он выделяет новую страницу и пишет туда только новую запись. _AB>При разделении в середине он делит страницу пополам и добавляет запись на одну из поделенных страниц.
Может показаться, что ситуация складывается идеальная, использование значений с монотонным возрастанием позволяет снизить фрагментированность индекса за счет отсутствия разбиения страниц, что в конечном итоге снижает стоимость обслуживания, а так же положительно сказывается на производительности всей системы. Это справедливо, но не во всех случаях. Дело в том, что СУБД гарантирует монотонную генерацию значений, но не дает никаких гарантий относительно последовательности вставки этих данных в таблицу. Т.е. фактически возможны ситуации, когда запись с меньшим значением ключа будет вставлена позже, и наоборот, а это в свою очередь может привести к разбиению страниц, вместо создания и заполнения новой в конце. Количество таких ситуаций будет определяться нагрузкой на систему. Для анализа решил провести эксперимент. В цикле из 1000 итераций в 200 потоках (соединениях) вставляется запись в пять разных таблиц:
1. Таблица с identity (dbo.TIdentity)
2. Таблица с newid() (dbo.TNewid)
3. Таблица с newsequentialid() (dbo.TNewsequentialid)
4. Таблица c sequence (dbo.TSequence)
5. Простая таблица c identity (dbo.TSimple), в которую последовательно вставляется 200 000 записей в одном потоке, для сравнения фрагментированности индексов.
Поле, на котором генерируются значения, является первичным ключом с кластерным индексом.
Структура
create table dbo.TIdentity
(
id int identity(1,1) not null primary key,
dt datetime2 default(sysdatetime()),
st varchar(7000)
)
go
create table dbo.TNewid
(
id uniqueidentifier not null default (newid()) primary key,
dt datetime2 default(sysdatetime()),
st varchar(7000)
)
go
create table dbo.TNewsequentialid
(
id uniqueidentifier not null default (newsequentialid()) primary key,
dt datetime2 default(sysdatetime()),
st varchar(7000)
)
go
create sequence dbo.CountBy as int start with 1 increment by 1;
go
create table dbo.TSequence
(
id int not null default (next value for dbo.CountBy) primary key,
dt datetime2 default(sysdatetime()),
st varchar(7000)
)
go
create table dbo.TSimple
(
id int identity(1,1) not null primary key,
dt datetime2 default(sysdatetime()),
st varchar(7000)
)
go
Второй момент, на котором хотелось бы остановиться в эксперименте – это суммарное время выполнения операции. Выше Sinclair высказал интересную мысль, использование рандомных значений, например newid() для типа данных uniquidentifier позволяет повысить конкуренцию за счет того, что в таком индексе блокируются разные ресурсы, в отличие от вставки последовательных значений, где процессы борются за доступ к последней странице.
Проверял на SQL Server 2012 Standard Edition с помощью приложения SqlQueryStress. Сам запрос выглядит так
Запрос на вставку
declare @i int = 0
while (@i < 1000)
begin
insert into dbo.TSimple(st)
select replicate('_', 7000)
set @i += 1
end
Результаты
1. Суммарное время выполнения операции
dbo.TIdentity — 00:08:02.3893
dbo.TNewid — 00:09:08.0350
dbo.TNewsequentialid — 00:10:18:3139
dbo.TSequence — 00:11:11.2454
Из результатов видно, что вставка произвольных значений не дала выигрыша по времени. При этом фрагменитрованность индекса для такого случая самая высокая. Возможно, преимущества по конкуренции и присутствуют, но много времени занимает разбиение страниц и протоколирование в лог. В целом время выполнения операции достаточно близкое для всех таблиц, разброс +- 3 минуты. Однако стоит обратить особое внимание на фрагментированность индекса, где используются монотонно возрастающие значения (таблицы TIdentity, TNewsequentialid, TSequence) – показатель колеблется от 74% до 81%. Т.е. для нагруженной системы, несмотря на использование последовательных значений высокая фргаменитрованность индекса так же имеет место быть.
Re[11]: [SQL Server] Анализ всех запросов к таблице
Здравствуйте, Somescout, Вы писали:
O>>Update: O>>Автор статьи Choosing the Best Clustered Index математически определяет лучших кандидатов на кластерный индекс. Основывается он на соотношении seek и lookup операций между кластерным и некластерным индексом. В целом можно рассмотреть в качестве подспорья.
S>Есть ещё один момент, который эта теория не учитывает — кластерный индекс в идеале должен монотонно возрастать. Так как если вставка строк в таблицу и обновление индекса проводятся очень часто, фрагментация и деление страниц съест всю пользу от наиболее оптимального с точки зрения запросов ключа (а дефрагментировать многогигабайтный индекс долго). По хорошему надо как-то отследить какие именно данные вставлялись в таблицу и из требуемых индексов выбрать тот, который будет монотонно изменяться. Альтернатива только задать fillfactor, но на кластерном индексе это автоматом снижает эффективность Index Lookups. Либо можно попробовать partition table, но я пока этого не пробовал делать, надо изучать.
На самом деле теория не учитывает не только монотонность, но и ширину, уникальность и изменчивость данных. Подход ориентируется на статистику использования индексов, которая возникает при эксплуатации БД. Это не призыв к действию, а информация для боле детального анализа и изучения, на что стоит обратить внимание.
Re[12]: [SQL Server] Анализ всех запросов к таблице
Здравствуйте, Olaf, Вы писали:
O>На самом деле теория не учитывает не только монотонность, но и ширину, уникальность и изменчивость данных. Подход ориентируется на статистику использования индексов, которая возникает при эксплуатации БД. Это не призыв к действию, а информация для боле детального анализа и изучения, на что стоит обратить внимание.
Я уже начинаю думать, что оптимальным вариантом будет партиционирование таблицы на актуальную и архивную части.
ARI ARI ARI... Arrivederci!
Re[19]: [SQL Server] Анализ всех запросов к таблице
Здравствуйте, Olaf, Вы писали:
>Т.е. фактически возможны ситуации, когда запись с меньшим значением ключа будет вставлена позже, и наоборот, а это в свою очередь может привести к разбиению страниц, вместо создания и заполнения новой в конце
В примере всё-таки крайний случай взят. Для строк, у которых размер значительно меньше размера страницы, такого беспредела с фрагментацией быть не должно. Ну, по крайней мере, в теории
К сожалению, ms sql под рукой нет, будет классно если проверишь у себя. Например, с st varchar(200)
Re[20]: [SQL Server] Анализ всех запросов к таблице
Здравствуйте, Sinix, Вы писали:
S>В примере всё-таки крайний случай взят. Для строк, у которых размер значительно меньше размера страницы, такого беспредела с фрагментацией быть не должно. Ну, по крайней мере, в теории
S>К сожалению, ms sql под рукой нет, будет классно если проверишь у себя. Например, с st varchar(200)
Результат для varchar(200):
Фрагментация для TNewsequentialid и TIdentity уменьшилась в два раза. А вот для последовательности показатель улучшился, но не намного. Подозреваю нужно указать конкретное значение для кэша, например 1000, чтобы снизить количество обращений к системным таблицам.
Вроде как напрашивается ответ – уменьшилась длина записи, сократилось количество страниц необходимое для хранения данных (а фактически уменьшилось время для вставки записи), что соответственно снизило количество ситуаций, в которых записи вставляются не по порядку. Так?
Re[21]: [SQL Server] Анализ всех запросов к таблице
Здравствуйте, Olaf, Вы писали:
O>Вроде как напрашивается ответ – уменьшилась длина записи, сократилось количество страниц необходимое для хранения данных (а фактически уменьшилось время для вставки записи), что соответственно снизило количество ситуаций, в которых записи вставляются не по порядку. Так?
Ну... оно конечно помогло, но не сильно. В общем чую (но обосновать не могу — матчасть надо освежать), что вставки по-прежнему утыкаются в конец страницы и приводят к куче лишних page split.
