Здравствуйте, 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). Если кластерный индекс выбран неудачно, значит количество операций поиска будет сведено к минимуму, а преобладать будут операции сканирования и уточняющих запросов. Тогда необходимо обратить внимание на некластерный индекс с максимальным значением операций поиска, возможно именно он, не являясь покрывающим индексом, дает уточняющие запросы и является более подходящим кандидатом на роль кластерного индекса. Но это идеальная картина мира, некластерных индексов может быть много и все вместе они могут вносить свой вклад в количество дополнительных обращений к некластерному индексу. Придется выбирать лучший уже основываясь на целесообразности, общих рекомендациях ну и самом бизнес процессе.

Здравствуйте, 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 операций между кластерным и некластерным индексом. В целом можно рассмотреть в качестве подспорья.