[SQL Server] Анализ всех запросов к таблице
От: Somescout  
Дата: 06.07.15 05:04
Оценка:
Здравствуйте.

Вероятно я спрашиваю что-то тривиальное, но можно ли профайлером получить все запросы в которых участвовала заданная таблица, а в идеале все индексы которые в них использовались при обращении к ней (ну и совсем хорошо статистику — число строк, логических чтений и т.п. для операции по этой таблице в запросе). Интересует именно сводная информация, т.к. само собой можно пройтись по всем запросам вручную, но это довольно долго и таблиц много.
ARI ARI ARI... Arrivederci!
Re: [SQL Server] Анализ всех запросов к таблице
От: Olaf Россия  
Дата: 06.07.15 07:42
Оценка:
Здравствуйте, Somescout, Вы писали:

S>Здравствуйте.


S>Вероятно я спрашиваю что-то тривиальное, но можно ли профайлером получить все запросы в которых участвовала заданная таблица, а в идеале все индексы которые в них использовались при обращении к ней (ну и совсем хорошо статистику — число строк, логических чтений и т.п. для операции по этой таблице в запросе). Интересует именно сводная информация, т.к. само собой можно пройтись по всем запросам вручную, но это довольно долго и таблиц много.


У вас задача совсем нетривиальная, достаточно разнородная информация, которую нужно объединить как-то между собой. Сходу не могу сообразить, а как такое вообще можно получить.

Информацию по использованию индексов, включая кучу, с момента последней перезагрузки сервера можно получить так
select db_name(s.database_id) as database_name
       ,object_name(s.[object_id]) as table_name
       ,i.name as index_name
       ,i.index_id
       ,user_updates as total_writes
       ,user_seeks + user_scans + user_lookups as total_reads
       ,user_updates - (user_seeks + user_scans + user_lookups) as diff
from sys.dm_db_index_usage_stats as s
join sys.indexes as i on s.[object_id] = i.[object_id] and i.index_id = s.index_id

Информацию по использованию указанной таблицы в запросах можно получить из кэшированного плана запроса. Время жизни информации ограничено временем жизни плана в кэше.
;with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select db_name(qp.dbid) as [database]
       ,object_name(qp.objectid, qp.dbid) as [object]
       ,qsa.last_execution_time
       ,qsa.avg_elapsed_time
       ,qsa.total_elapsed_time
       ,qp.query_plan
from
(
    select qs.plan_handle
           ,max(last_execution_time) as last_execution_time
           ,sum(total_elapsed_time / execution_count) as [avg_elapsed_time]
           ,sum(total_elapsed_time) as total_elapsed_time
    from sys.dm_exec_query_stats as qs
    group by qs.plan_handle 
) qsa 
cross apply sys.dm_exec_query_plan(qsa.plan_handle) as qp
where qp.query_plan.exist('//ColumnReference[@Table="[Photo]"]') = 1
order by qsa.total_elapsed_time desc
Re[2]: [SQL Server] Анализ всех запросов к таблице
От: Somescout  
Дата: 06.07.15 08:17
Оценка:
Здравствуйте, Olaf, Вы писали:

O>У вас задача совсем нетривиальная, достаточно разнородная информация, которую нужно объединить как-то между собой. Сходу не могу сообразить, а как такое вообще можно получить.


Наверное мне стоило описать цель: есть база с большим количеством таблиц, база создавалась методом "Индексов, индексов каждой таблице и побольше!", насоздавали кучу одноколоночных индексов, в некоторых случаях неоптимально выбран кластерный индекс. Хочется: запустить профайлер, собрать лог запросов с их разбором, скормить какой-нибудь программе которая на основании этого лога выдаст какие индексы нужны для таблиц (создаём нужные, выкидываем лишние). В принципе можно и самому что-то такое состряпать, но не хотелось бы делать лишнюю работу если это уже реализовано.
ARI ARI ARI... Arrivederci!
Отредактировано 06.07.2015 8:18 Somescout . Предыдущая версия .
Re[3]: [SQL Server] Анализ всех запросов к таблице
От: Olaf Россия  
Дата: 06.07.15 09:12
Оценка: 3 (1) +1
Здравствуйте, Somescout, Вы писали:

S>Наверное мне стоило описать цель: есть база с большим количеством таблиц, база создавалась методом "Индексов, индексов каждой таблице и побольше!", насоздавали кучу одноколоночных индексов, в некоторых случаях неоптимально выбран кластерный индекс. Хочется: запустить профайлер, собрать лог запросов с их разбором, скормить какой-нибудь программе которая на основании этого лога выдаст какие индексы нужны для таблиц (создаём нужные, выкидываем лишние). В принципе можно и самому что-то такое состряпать, но не хотелось бы делать лишнюю работу если это уже реализовано.


Теперь понятно. Для этих целей можно воспользоваться родным инструментом от Microsoft DTA (Tutorial: Database Engine Tuning Advisor). Приложение на основе рабочей нагрузки выдает рекомендации по улучшению производительности. В качестве источника данных может выступать таблица или файл трассировки. Оставить отзывов не могу, т.к. использовал только в ознакомительных целях.
Re[4]: [SQL Server] Анализ всех запросов к таблице
От: Somescout  
Дата: 06.07.15 13:00
Оценка:
Здравствуйте, Olaf, Вы писали:

O>Теперь понятно. Для этих целей можно воспользоваться родным инструментом от Microsoft DTA (Tutorial: Database Engine Tuning Advisor). Приложение на основе рабочей нагрузки выдает рекомендации по улучшению производительности. В качестве источника данных может выступать таблица или файл трассировки. Оставить отзывов не могу, т.к. использовал только в ознакомительных целях.


Спасибо, попробую.
ARI ARI ARI... Arrivederci!
Re[4]: [SQL Server] Анализ всех запросов к таблице
От: Somescout  
Дата: 08.07.15 15:58
Оценка:
Здравствуйте, Olaf, Вы писали:

O>Теперь понятно. Для этих целей можно воспользоваться родным инструментом от Microsoft DTA (Tutorial: Database Engine Tuning Advisor). Приложение на основе рабочей нагрузки выдает рекомендации по улучшению производительности. В качестве источника данных может выступать таблица или файл трассировки. Оставить отзывов не могу, т.к. использовал только в ознакомительных целях.


Интересный инструмент: строит оптимальные индексы для запросов, чистит неиспользуемые индексы, создаёт статистику для нужных колонок (как-то даже не задумывался что её тоже создавать надо). Вроде как должен ещё давать рекомендации по partitioning, но протестировать это пока не удалось. Может работать по данным профайлера, либо по закэшированным планам запросов.

Из минусов — очень часто падает, пока на трэйс-файле из-за этого использовать не получилось.
ARI ARI ARI... Arrivederci!
Re[5]: [SQL Server] Анализ всех запросов к таблице
От: Olaf Россия  
Дата: 09.07.15 05:01
Оценка: 9 (2) +1
Здравствуйте, Somescout, Вы писали:

S>Интересный инструмент: строит оптимальные индексы для запросов, чистит неиспользуемые индексы, создаёт статистику для нужных колонок (как-то даже не задумывался что её тоже создавать надо). Вроде как должен ещё давать рекомендации по partitioning, но протестировать это пока не удалось. Может работать по данным профайлера, либо по закэшированным планам запросов.


Инструменты подобного рода хороши тем, что возможно избавляют специалиста от рутинной работы, но с другой стороны они могут предоставлять ложную картину происходящего на сервере БД. Точность рекомендаций инструмента определяется в данном случае длительностью наблюдения, т.е. чем меньше интервал наблюдения, тем расплывчатей картина получается. Простой пример из жизни – отчет или процесс, который запускается раз в квартал или в полгода. Сомневаюсь, что DTA предоставит правильную информацию по объектам, которые используются в этих запросах. Поэтому без знания бизнес-процессов и схемы БД очень опасно следовать рекомендациям инструмента.

Альтернативой является ручной тюнинг, когда имея в арсенале набор DMV, вы можете извлечь всю необходимую информацию для принятия решения. Актуальность данных будет определяться временем функционирования сервера и/или БД. Это конечно не призыв к действию, только информация к размышлению. В качестве примера привожу набор «домашних заготовок», которые можно адаптировать под себя и тюнить необходимые процессы. Я думаю на просторах сети их неограниченное количество.

  DMV запросы
-- {Статистика использования индексов}
select object_name(s.[object_id]) as objectname
       ,i.name as indexname
       ,i.index_id
       ,user_seeks + user_scans + user_lookups as reads
       ,user_updates as writes
       ,i.type_desc
       ,i.fill_factor
       ,user_seeks
       ,user_scans
       ,user_lookups
from sys.dm_db_index_usage_stats as s
join sys.indexes as i on s.[object_id] = i.[object_id]
where objectproperty(s.[object_id], 'IsUserTable') = 1 and i.index_id = s.index_id and s.database_id = db_id()
order by object_name(s.[object_id])
         ,writes desc
         ,reads desc

-- {Неиспользуемые индексы}
select object_name(i.[object_id]) as tablename
       ,i.name
from sys.indexes i
join sys.objects o on i.[object_id] = o.[object_id]
where i.index_id not in (
                            select s.index_id
                            from sys.dm_db_index_usage_stats as s
                            where s.[object_id] = i.[object_id]
                                  and i.index_id = s.index_id
                                  and database_id = db_id()
                        )
      and o.[type] = 'U'
order by object_name(i.[object_id]) asc

-- {Пропущенные индексы}
select user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) as [index_advantage]
       ,migs.last_user_seek
       ,mid.[statement] as [Database.Schema.Table]
       ,mid.equality_columns
       ,mid.inequality_columns
       ,mid.included_columns
       ,migs.unique_compiles
       ,migs.user_seeks
       ,migs.avg_total_user_cost
       ,migs.avg_user_impact
from sys.dm_db_missing_index_group_stats as migs with(nolock)
join sys.dm_db_missing_index_groups as mig with(nolock) on migs.group_handle = mig.index_group_handle
join sys.dm_db_missing_index_details as mid with(nolock) on mig.index_handle = mid.index_handle
where mid.database_id = db_id()
order by index_advantage desc

-- {Возможно "плохие" индексы writes > reads}
select db_name(s.database_id) as database_name
       ,object_name(s.[object_id]) as table_name
       ,i.name as index_name
       ,i.index_id
       ,user_updates as total_writes
       ,user_seeks + user_scans + user_lookups as total_reads
       ,user_updates - (user_seeks + user_scans + user_lookups) as diff
from sys.dm_db_index_usage_stats as s with(nolock)
join sys.indexes as i with(nolock) on s.[object_id] = i.[object_id] and i.index_id = s.index_id
where objectproperty(s.[object_id], 'IsUserTable') = 1
      and user_updates > (user_seeks + user_scans + user_lookups)
      and i.index_id > 1
      and s.database_id = db_id()
order by diff desc, total_writes desc, total_reads asc

S>создаёт статистику для нужных колонок (как-то даже не задумывался что её тоже создавать надо)
Речь идет о мульти колоночных статистиках, которые не создаются автоматически!? Как раз DTA позволяет это сделать. А вот если, рекомендации по одно колоночным статистикам, которые могут создаваться автоматически, то это означает, что у вас отключено auto create statistics.
Re[6]: [SQL Server] Анализ всех запросов к таблице
От: Somescout  
Дата: 09.07.15 17:27
Оценка:
Про DVM-таблицы интересно, не знал про них. Возможно придётся пользоваться именно ими вместо DTA (по причине падучести оного, надо будет лог поискать).
ARI ARI ARI... Arrivederci!
Re[6]: [SQL Server] Анализ всех запросов к таблице
От: Somescout  
Дата: 10.07.15 02:54
Оценка:
Здравствуйте, Olaf, Вы писали:

O> {Возможно "плохие" индексы writes > reads}


Можете рассказать как с ними поступать? Есть несколько таких индексов, они вроде как слегка используются, но число записей на 2 порядка выше числа чтений.
ARI ARI ARI... Arrivederci!
Re[7]: [SQL Server] Анализ всех запросов к таблице
От: Olaf Россия  
Дата: 10.07.15 04:39
Оценка: 19 (1)
Здравствуйте, Somescout, Вы писали:

S>Здравствуйте, Olaf, Вы писали:


O>> {Возможно "плохие" индексы writes > reads}


S>Можете рассказать как с ними поступать? Есть несколько таких индексов, они вроде как слегка используются, но число записей на 2 порядка выше числа чтений.


Необходимо обратить особое внимание на такие индексы и при условии, что количество записей значительно больше нуля, а количество чтений равно нулю, данные индексы являются отличными кандидатами на удаление, но только после изучения, а где и как они могли бы применяться. Причины, по которым индекс не используется оптимизатором запросов для чтения, я бы обозначил три:
1. Индекс не так хорош, что бы решить проблему поиска данных в таблице и оптимизатор его игнорирует. Возможно, рядом есть более хороший кандидат для этих целей.
2. Бизнес-процесс построен таким образом, что данные пишутся, но никто их не читает (пережитки прошлого, рудименты и прочее)
3. Вы попали в интервал наблюдения, когда пользователи не читают данные или читают достаточно мало. А вот в квартальный отчет или годовой индекс выстреливает на все 100 и покрывает задачи бизнеса для подготовки данных.
На мой взгляд, разница в два порядка не такая большая, чтобы применять решение об удалении, даже если чтений нет совсем.

Возвращаясь к вопросу DTA – а вы пробовали результат трассировки (или сам файл) сохранить в таблицу БД и уже ее использовать как источник для анализа?
Re[8]: [SQL Server] Анализ всех запросов к таблице
От: Somescout  
Дата: 14.07.15 16:49
Оценка:
Здравствуйте, Olaf, Вы писали:

O>Здравствуйте, Somescout, Вы писали:


S>>Здравствуйте, Olaf, Вы писали:


O>>> {Возможно "плохие" индексы writes > reads}


S>>Можете рассказать как с ними поступать? Есть несколько таких индексов, они вроде как слегка используются, но число записей на 2 порядка выше числа чтений.


O>Необходимо обратить особое внимание на такие индексы и при условии, что количество записей значительно больше нуля, а количество чтений равно нулю, данные индексы являются отличными кандидатами на удаление, но только после изучения, а где и как они могли бы применяться. Причины, по которым индекс не используется оптимизатором запросов для чтения, я бы обозначил три:

O>1. Индекс не так хорош, что бы решить проблему поиска данных в таблице и оптимизатор его игнорирует. Возможно, рядом есть более хороший кандидат для этих целей.
O>2. Бизнес-процесс построен таким образом, что данные пишутся, но никто их не читает (пережитки прошлого, рудименты и прочее)
O>3. Вы попали в интервал наблюдения, когда пользователи не читают данные или читают достаточно мало. А вот в квартальный отчет или годовой индекс выстреливает на все 100 и покрывает задачи бизнеса для подготовки данных.
O>На мой взгляд, разница в два порядка не такая большая, чтобы применять решение об удалении, даже если чтений нет совсем.

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

Развивая тему тюнинга базы:

Можно ли на основе статистики использования индексов в таблице объективно выделить оптимального кандидата для кластерного индекса?
Т.е. сейчас для многих таблиц (считай для всех) сделан кластерный индекс по первичному ключу, который является GUID'ом. Как я понимаю, это может быть проблемой для больших и часто обновляемых таблиц — вставка строки происходит в произвольную часть таблицы и приводит к её пересортировке (точнее разбивке страницы), а выполнение запроса по более упорядоченному полю приводит к загрузке избыточного числа страниц в память. Субъективно я могу оценить, что в некоторых случаях лучше сделать кластерный индекс, например по дате (если записи добавляются в основном с возрастающей датой и тяжёлые запросы также делаются для определённого периода). Но есть ли объективные признаки что индекс нужно сделать кластерным?

Кроме того, для таблиц с кластерным индексом по GUID возможно имеет смысл изменить fillfactor. Будет ли он влиять на физическое размещение данных? Каким его стоит выставлять (пока мысль о 0.95-0.9)? Можно ли узнать (средний) размер строки таблицы в байтах если я захочу зарезервировать 3 средних строки на страницу?

O>Возвращаясь к вопросу DTA – а вы пробовали результат трассировки (или сам файл) сохранить в таблицу БД и уже ее использовать как источник для анализа?


Сегодня попробовал, всё равно падает. Есть подозрение что это из-за того что установлено несколько версий Management Tools.
ARI ARI ARI... Arrivederci!
Отредактировано 14.07.2015 16:53 Somescout . Предыдущая версия . Еще …
Отредактировано 14.07.2015 16:52 Somescout . Предыдущая версия .
Re[9]: [SQL Server] Анализ всех запросов к таблице
От: Olaf Россия  
Дата: 14.07.15 19:38
Оценка:
Здравствуйте, Somescout, Вы писали:

S>Развивая тему тюнинга базы:


S>Можно ли на основе статистики использования индексов в таблице объективно выделить оптимального кандидата для кластерного индекса?

S>Т.е. сейчас для многих таблиц (считай для всех) сделан кластерный индекс по первичному ключу, который является GUID'ом. Как я понимаю, это может быть проблемой для больших и часто обновляемых таблиц — вставка строки происходит в произвольную часть таблицы и приводит к её пересортировке (точнее разбивке страницы), а выполнение запроса по более упорядоченному полю приводит к загрузке избыточного числа страниц в память.

Для того чтобы уменьшить фрагментированность индекса и снизить количество операций деления страниц при вставке, крайне рекомендуется для кластерного индекса построенного на типе данных uniqueidentifier в качестве ограничения по умолчанию использовать функцию NEWSEQUENTIALID(), генерирующую псевдопоследовательные уникальные значения.

S>Субъективно я могу оценить, что в некоторых случаях лучше сделать кластерный индекс, например по дате (если записи добавляются в основном с возрастающей датой и тяжёлые запросы также делаются для определённого периода). Но есть ли объективные признаки что индекс нужно сделать кластерным?


  Статистика использования индексов
-- {Статистика использования индексов}
select object_name(s.[object_id]) as objectname
       ,i.name as indexname
       ,i.index_id
       ,user_seeks + user_scans + user_lookups as reads
       ,user_updates as writes
       ,i.type_desc
       ,i.fill_factor
       ,user_seeks
       ,user_scans
       ,user_lookups
from sys.dm_db_index_usage_stats as s
join sys.indexes as i on s.[object_id] = i.[object_id]
where objectproperty(s.[object_id], 'IsUserTable') = 1 and i.index_id = s.index_id and s.database_id = db_id()
order by object_name(s.[object_id])
         ,writes desc
         ,reads desc

Объективные признаки, используя например статистику накопленную сервером, для поиска лучшего кандидата на кластерный индекс будет найти не просто, но можно рассмотреть варианты. Кластерный индекс сам по себе покрывающий индекс, поэтому идеальным вариантом будет картина, на которой запрос указанный выше выдаёт максимум операций поиска (seeks), минимум операций сканирования (scans) и уточняющих запросов (lookups). Если кластерный индекс выбран неудачно, значит количество операций поиска будет сведено к минимуму, а преобладать будут операции сканирования и уточняющих запросов. Тогда необходимо обратить внимание на некластерный индекс с максимальным значением операций поиска, возможно именно он, не являясь покрывающим индексом, дает уточняющие запросы и является более подходящим кандидатом на роль кластерного индекса. Но это идеальная картина мира, некластерных индексов может быть много и все вместе они могут вносить свой вклад в количество дополнительных обращений к некластерному индексу. Придется выбирать лучший уже основываясь на целесообразности, общих рекомендациях ну и самом бизнес процессе.

Update:
Автор статьи Choosing the Best Clustered Index математически определяет лучших кандидатов на кластерный индекс. Основывается он на соотношении seek и lookup операций между кластерным и некластерным индексом. В целом можно рассмотреть в качестве подспорья.
Отредактировано 15.07.2015 6:52 Olaf . Предыдущая версия .
Re[10]: [SQL Server] Анализ всех запросов к таблице
От: Somescout  
Дата: 25.07.15 18:47
Оценка:
Здравствуйте, Olaf, Вы писали:

O>Для того чтобы уменьшить фрагментированность индекса и снизить количество операций деления страниц при вставке, крайне рекомендуется для кластерного индекса построенного на типе данных uniqueidentifier в качестве ограничения по умолчанию использовать функцию NEWSEQUENTIALID(), генерирующую псевдопоследовательные уникальные значения.

Увы, над этим я не властен. Структура базы уже есть и определяется не мной.

O>Объективные признаки, используя например статистику накопленную сервером, для поиска лучшего кандидата на кластерный индекс будет найти не просто, но можно рассмотреть варианты. Кластерный индекс сам по себе покрывающий индекс, поэтому идеальным вариантом будет картина, на которой запрос указанный выше выдаёт максимум операций поиска (seeks), минимум операций сканирования (scans) и уточняющих запросов (lookups). Если кластерный индекс выбран неудачно, значит количество операций поиска будет сведено к минимуму, а преобладать будут операции сканирования и уточняющих запросов. Тогда необходимо обратить внимание на некластерный индекс с максимальным значением операций поиска, возможно именно он, не являясь покрывающим индексом, дает уточняющие запросы и является более подходящим кандидатом на роль кластерного индекса. Но это идеальная картина мира, некластерных индексов может быть много и все вместе они могут вносить свой вклад в количество дополнительных обращений к некластерному индексу. Придется выбирать лучший уже основываясь на целесообразности, общих рекомендациях ну и самом бизнес процессе.


O>Update:

O>Автор статьи Choosing the Best Clustered Index математически определяет лучших кандидатов на кластерный индекс. Основывается он на соотношении seek и lookup операций между кластерным и некластерным индексом. В целом можно рассмотреть в качестве подспорья.

Есть ещё один момент, который эта теория не учитывает — кластерный индекс в идеале должен монотонно возрастать. Так как если вставка строк в таблицу и обновление индекса проводятся очень часто, фрагментация и деление страниц съест всю пользу от наиболее оптимального с точки зрения запросов ключа (а дефрагментировать многогигабайтный индекс долго). По хорошему надо как-то отследить какие именно данные вставлялись в таблицу и из требуемых индексов выбрать тот, который будет монотонно изменяться. Альтернатива только задать fillfactor, но на кластерном индексе это автоматом снижает эффективность Index Lookups. Либо можно попробовать partition table, но я пока этого не пробовал делать, надо изучать.

Есть ещё filtered index, но я не совсем понимаю: если есть два таких индекса (key>x и key<=x — покрывающие вместе всю таблицу), оптимизатор задействует их если в запросе будет условие key>y, где y<x — т.е. попадающее в оба индекса?
ARI ARI ARI... Arrivederci!
Отредактировано 27.07.2015 8:24 Somescout . Предыдущая версия .
Re[11]: [SQL Server] Анализ всех запросов к таблице
От: Sinclair Россия https://github.com/evilguest/
Дата: 29.07.15 11:35
Оценка:
Здравствуйте, Somescout, Вы писали:

S>Есть ещё один момент, который эта теория не учитывает — кластерный индекс в идеале должен монотонно возрастать.

А разве это не приводит к проблемам излишнего ожидания на page lock при интенсивной вставке из нескольких потоков? Мне всегда казалось, что вставка в B-дерево уже O(logN), независимо от вставки в конец или середину.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Re[12]: [SQL Server] Анализ всех запросов к таблице
От: Somescout  
Дата: 29.07.15 17:22
Оценка:
Здравствуйте, Sinclair, Вы писали:

S>Здравствуйте, Somescout, Вы писали:


S>>Есть ещё один момент, который эта теория не учитывает — кластерный индекс в идеале должен монотонно возрастать.

S>А разве это не приводит к проблемам излишнего ожидания на page lock при интенсивной вставке из нескольких потоков? Мне всегда казалось, что вставка в B-дерево уже O(logN), независимо от вставки в конец или середину.
Вставка в середину таблицы приводит к разбивке страницы, в которую происходит вставка и к обновлению как минимум одной non-leaf страницы индекса (в худшем случае до 2*(глубина индекса)). С учётом того что всё это должно быть записано атомарно, скорости это не прибавляет. Кроме того возникает фрагментация таблицы, особенно при интенсивной вставке, что опять же не ускоряет выборку.

Насколько это будет проблемой зависит от дисковой подсистемы, объёма памяти и размера таблицы.
ARI ARI ARI... Arrivederci!
Re[13]: [SQL Server] Анализ всех запросов к таблице
От: Sinclair Россия https://github.com/evilguest/
Дата: 30.07.15 06:14
Оценка:
Здравствуйте, Somescout, Вы писали:
S>Вставка в середину таблицы приводит к разбивке страницы, в которую происходит вставка и к обновлению как минимум одной non-leaf страницы индекса (в худшем случае до 2*(глубина индекса)).
Это если у вас страница, куда происходит вставка, заполнена на 100%. Это же бывает достаточно редко. Точнее — мы имеем o(log(n)) операций в худшем случае.
S>С учётом того что всё это должно быть записано атомарно, скорости это не прибавляет. Кроме того возникает фрагментация таблицы, особенно при интенсивной вставке, что опять же не ускоряет выборку.
Что такое "фрагментация таблицы"?
Да, и почему вы думаете, что вставка "в конец" не будет првиводить к разбивке страницы? Ведь в b-tree все leaf-страницы одинаковые — что "в середине", что "с краю".
S>Насколько это будет проблемой зависит от дисковой подсистемы, объёма памяти и размера таблицы.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Re[14]: [SQL Server] Анализ всех запросов к таблице
От: wildwind Россия  
Дата: 30.07.15 07:22
Оценка:
Здравствуйте, Sinclair, Вы писали:

S> Да, и почему вы думаете, что вставка "в конец" не будет првиводить к разбивке страницы? Ведь в b-tree все leaf-страницы одинаковые — что "в середине", что "с краю".


Я, например, тоже ожидаю, что Скуль достаточно умный для того, чтобы по-человечески обработать заполнение таблицы в порядке identity pk (типичный случай). А именно, при вставке в самый конец учесть fill factor и просто перейти к новой странице. К примеру, Оракл поступает именно так.
Hardware eventually fails. Software eventually works. ::: avalon/1.0.442
Re[15]: [SQL Server] Анализ всех запросов к таблице
От: _ABC_  
Дата: 30.07.15 08:02
Оценка:
Здравствуйте, wildwind, Вы писали:

W>Я, например, тоже ожидаю, что Скуль достаточно умный для того, чтобы по-человечески обработать заполнение таблицы в порядке identity pk (типичный случай). А именно, при вставке в самый конец учесть fill factor и просто перейти к новой странице. К примеру, Оракл поступает именно так.

Fill factor он учитывать не будет. Fill factor учитывается только при создании или перестроении индекса. Дальше SQL Server пытается каждую страницу
заполнить на 100%.

Но просто перейдет к новой странице, как только старая заполнится — это да. А при разделении страницы посередине индекса он поделит
страницу примерно пополам. Помимо всего прочего, это обойдется существенно дороже в плане количества информации, записанной в журнал
транзакций.
Re[16]: [SQL Server] Анализ всех запросов к таблице
От: Sinclair Россия https://github.com/evilguest/
Дата: 30.07.15 09:01
Оценка:
Здравствуйте, _ABC_, Вы писали:
_AB>Fill factor он учитывать не будет. Fill factor учитывается только при создании или перестроении индекса. Дальше SQL Server пытается каждую страницу
_AB>заполнить на 100%.
+1.
_AB>Но просто перейдет к новой странице, как только старая заполнится — это да.
Хм. А разве он не сделает page split крайней страницы в момент её заполнения?
_AB>А при разделении страницы посередине индекса он поделит страницу примерно пополам.

Помимо всего прочего, это обойдется существенно дороже в плане количества информации, записанной в журнал
_AB>транзакций.
А зачем писать page split в журнал транзакций? Разве эта информация нужна для undo/redo?
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Re[16]: [SQL Server] Анализ всех запросов к таблице
От: wildwind Россия  
Дата: 30.07.15 09:31
Оценка:
Здравствуйте, _ABC_, Вы писали:

ABC> Fill factor он учитывать не будет. Fill factor учитывается только при создании или перестроении индекса. Дальше SQL Server пытается каждую страницу

ABC> заполнить на 100%.

Понятно, спасибо. Я и сам уже нагуглил подтверждение.
Hardware eventually fails. Software eventually works. ::: avalon/1.0.442
Подождите ...
Wait...
Пока на собственное сообщение не было ответов, его можно удалить.