Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Fillfactor и кластерный индекс  [new]
kano
Member

Откуда:
Сообщений: 59
Доброго времени суток.
Исходные данные:
MS SQLServer 2000 - 8.00.2040 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Есть большая таблица "SDOC0002"
sp_spaceused 'SDOC0002':
namerowsreserveddataindex_sizeunused
SDOC00026987304768007952 KB29419824 KB6001784 KB32586344 KB

С индексами DBCC SHOWCONTIG('SDOC0002') WITH ALL_INDEXES, TABLERESULTS :
ObjectName ObjectId IndexName IndexId LevelPagesRowsMinimumRecordSizeMaximumRecordSizeAverageRecordSizeForvadedRecordsExtentsExtentSwitchesAverageFreeBytesAveragePageDensityScanDensityBestCountActualCountLogicalFragmentationExtentFragmentation
SDOC00022076586486SDOC0002_PRIM10367747869873044335370345.504609184609741493.401000000000181.54928341981715899.7201583599978394596854609750.26893430769674226.344512472934448
SDOC00022076586486SDOC0002_DOCUC3019272869873044172320.2989999999999990241882419811.27999.86064986409685799.55370056613908524091241990.3787721555767714113.858111460228212
SDOC00022076586486SDOC0002_UC4019511669873044172320.29899999999999902454829136110.22798.63816407215222283.70800013728249724390291373.064843477726070511.707674759654555
SDOC00022076586486SDOC0002_INVOICE43019112869873044202020.00239882621753.18500000000000299.34290832715592591.12441833854603623891262181.68264199907915114.85742871435718
SDOC00022076586486SDOC0002_SGID46015780669873044161616.001981023495125.9929999999999998.44337781072398783.9547156962887319726234962.96883515202210321.585058051489147

Primary Key:
CREATE UNIQUE CLUSTERED INDEX [SDOC0002_PRIM] ON [dbo].[SDOC0002]([DOCUMENTID], [ITEM])
Таблица не имеет автоинкрементных полей, так как, по-сути в ней хранятся строки документов, а шапка хранится в другой таблице.
Реиндекс происходит каждое воскресенье.

Собственно вопросы:
1) Имеет ли смысл fillfactor отличный от 0 или 100 для кластерного индекса, если за неделю вставляется не более 100 000 строк?

2) Почитал много интересного об индексах
https://www.sql.ru/articles/mssql/01082003Fill-FactorTruth.shtml
https://www.sql.ru/articles/mssql/2007/051403ExtentUsageAndBehavioursWhenUsingDBREINDEXAndSHRINKFILE.shtml
https://www.sql.ru/articles/mssql/03013101Indexes.shtml
Хотел уточнить. Есть ли смысл добавить автоинкрементное поле, создать кластерный индекс по нему с fillfactor=0 - это "сожмёт" таблицу и уменьшит размер некластерных индексов. А индекс по [DOCUMENTID], [ITEM] сделать не кластерным, и подобрать ему fillfactor в зависимости от средней фрагментации?

В чём смысл вопроса: Таблица содержит строки расходных накладных за последние 5-6 лет. Тоесть большая её часть - мертвый груз, нужна лишь для не частых отчётов. Размер табличка занимает серьёзный, и накладно её держать с филфактором по-умолчанию. Тем более с данными за текущий год идёт активная работа, и 2-3ГБ этой таблицы постоянно сидят в кеш-памяти.
15 сен 09, 11:56    [7659876]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36801
Если у вас нет проблем со вставкой, делайте 100%. И опять же, если нет проблем со вставкой, не трогайте кластерный индекс. А то, глядишь, начнутся проблемы с выборкой.
15 сен 09, 12:00    [7659903]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
kano
Member

Откуда:
Сообщений: 59
Гавриленко Сергей Алексеевич,

Что попадает в "проблемы со вставкой"? Высокие очереди записи?
Если не сложно, в трёх словах на какие счётчики обратить внимание.
15 сен 09, 12:08    [7659998]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36801
kano
Гавриленко Сергей Алексеевич,

Что попадает в "проблемы со вставкой"? Высокие очереди записи?
Если не сложно, в трёх словах на какие счётчики обратить внимание.
Я бы больше ориентировался на время вставки.
15 сен 09, 12:13    [7660045]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
kano
Member

Откуда:
Сообщений: 59
Гавриленко Сергей Алексеевич,

С временем вставки или обновления ощутимых проблем нет. Меня немного смущали в статьях ссылки на page split. Но судя по логам расщепление в основном идёт в некластерных индексах.

Насчёт изменения кластерного ключа. Если я не ошибаюсь, то в какой то из статей Вы говорили не торопится с выбором кластерного ключа. Как я понимаю, чем меньше кластерный ключ, тем быстрее поиск по некластреных, и наоборот?
В данном конкретном случае, к таблице выполняются два типа запросов по Primary Key:
1) ...WHERE DOCUMENTID = 1111 AND ITEM=1 то-есть не индекс скан, а индекс сиик.
2) ...WHERE DOCUMENTID = 1111 то-есть индекс скан по части кластерного индекса

У меня были две идеи: сделать автоинкрементный индекс или сделать кластерный только по DOCUMENTID. В обоих случаях - дополнительный некластерный DOCUMENTID, ITEM.

Где возможны падения производительности и засчёт чего?
Ожидаемое повышение времени поиска по некластерным индекам не перекроет падение производительности?
15 сен 09, 12:27    [7660190]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36801
Вы не от того пляшете. Не надо чинить того, что не сломалось. У вас есть какие-нибудь проблемы с производительностью? Давайте о них поговорим.
15 сен 09, 12:30    [7660217]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
kano
Member

Откуда:
Сообщений: 59
Гавриленко Сергей Алексеевич,

У меня высокие очереди чтения(до 40) и постоянно низкое значение PageLife Expectancy. Обычно это происходит когда делается переоценка, или любые другие операции, затрагивающие записи за большой период в таблицах прихода или расхода. В это время в кеш заливаются таблицы по 3-4 гектара, их немаленькие индексы - и транзакции начинают тянутся дольше, появляются 10-20 PAGEIOLATCH_SH и блокировки висят подольше.

Это не смертельно, но такие вещи наш маркетинг делает несколько раз в день.

Сейчас в базе 109 пользователей. В течении трёх месяцев - будет ещё примерно столько же.
Я не выдумываю себе работу, просто думаю лучше "перебдеть, чем недобдеть" :-)
15 сен 09, 12:54    [7660445]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
aleks2
Guest
1. Если вставка идет в хвост или гриву кластерного индекса - он не фрагментируется. И это рекомендуемая практика.

2.>>У меня высокие очереди чтения(до 40) и постоянно низкое значение PageLife Expectancy. Обычно это происходит когда делается переоценка, или любые другие операции, затрагивающие записи за большой период в таблицах прихода или расхода. В это время в кеш заливаются таблицы по 3-4 гектара, их немаленькие индексы - и транзакции начинают тянутся дольше, появляются 10-20 PAGEIOLATCH_SH и блокировки висят подольше.

Дык, ежели серверу надо прочитать "3-4 гектара" - чем тут поможет кластерный индекс?
Вам бизнес-логику "перебздеть" надо, ну или, на худой конец, конкретные запросы.
15 сен 09, 13:01    [7660512]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
kano
Member

Откуда:
Сообщений: 59
aleks2,

Ежли на эту табличку не сделать кластерный с филфактор=100, то в кеш полезет не 3-4 гектара, а 8-9.
А так как все некластреные построены на кластерном, то кластерный сидит в кеши безвылазно, и хотелось бы, шобы он там занимал места - поменьше. Вот для этого и вопрос: стоит или нет строить кластерный индекс по другим полям и если да, то по каким?
15 сен 09, 13:15    [7660634]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36801
kano
aleks2,

Ежли на эту табличку не сделать кластерный с филфактор=100, то в кеш полезет не 3-4 гектара, а 8-9.
А так как все некластреные построены на кластерном, то кластерный сидит в кеши безвылазно, и хотелось бы, шобы он там занимал места - поменьше. Вот для этого и вопрос: стоит или нет строить кластерный индекс по другим полям и если да, то по каким?
По каким полям не строй кластерный индекс, размер его практически не изменится, поотму что у кластерного индекса на листовом уровне все данные.
15 сен 09, 13:18    [7660655]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
kano
Member

Откуда:
Сообщений: 59
Гавриленко Сергей Алексеевич
По каким полям не строй кластерный индекс, размер его практически не изменится, поотму что у кластерного индекса на листовом уровне все данные.

Это я понял :-) Размер уменшится у таблицы, так как уйдёт unused-пространство.
А насчёт уменьшения индекса.
Может я не так понял - на уровне листьев не кластерного индекса лежит лежит уровень древа кластерного. Стало быть, чем короче кластерный, тем меньше места занимает не кластерный.
Отсюда следует, если у меня 69 000 000 строк в таблице и кластерный индекс станет "легче" на 4 байта на строку(выбросим ITEM), то каждый некластерный индекс похудеет на ((69000000*4)/1024)/1024=266,7 мегабайта. Я правильно понял идею?
15 сен 09, 13:47    [7660955]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
kano
Member

Откуда:
Сообщений: 59
Вот в этом и состоял второй вопрос:
стоит ли делать более короткий кластерный индекс? даст ли это прирост производительности, уменьшит ли размер не кластерных индексов или овчинка выделки не стоит?
15 сен 09, 14:37    [7661539]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36801
kano
Вот в этом и состоял второй вопрос:
стоит ли делать более короткий кластерный индекс? даст ли это прирост производительности, уменьшит ли размер не кластерных индексов или овчинка выделки не стоит?
При сканах - не стоит. Не в ту сторону смотрите. Либо структуру редизайнить надо, либо подобную отчетность выносить на отдельный сервер, чтобы другим не мешала.
15 сен 09, 14:39    [7661559]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
Glory
Member

Откуда:
Сообщений: 104760
kano
Вот в этом и состоял второй вопрос:
стоит ли делать более короткий кластерный индекс? даст ли это прирост производительности, уменьшит ли размер не кластерных индексов или овчинка выделки не стоит?

- Не бывает индексов оптимальных абсолютно для всех запросов. Особенно, если запросы различаются по своей сути, как select или insert. Придется выбирать, какие запросы для вас более важны

- В некластерные индексы пишется ссылка на кластерный, так что первым все равно, сколько занимает второй
15 сен 09, 14:40    [7661571]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
kano
Member

Откуда:
Сообщений: 59
Glory,

С оптимальностью Вы кругом правы. Для нас важнее выборка, по крайней мере - на данной таблице.

Glory
- В некластерные индексы пишется ссылка на кластерный, так что первым все равно, сколько занимает второй

Получается в статье https://www.sql.ru/articles/mssql/03013101Indexes.shtml#4] ошибка?
В ней сказано
Кластерный индекс использует row locator и он является частью не кластерного индекса на leaf level. Этот факт приводит к важному правилу SQL Server: создавайте кластерные ключи как можно более короткими. Каждый некластерный индекс будет использовать значения кластерного индекса. Следовательно увеличение размера кластерного индекса приводит к многократному увеличению требований по памяти для всех не кластерных индексов. Последнее приводит к увеличению времени на процессы чтения, сканирования данных и, как следствие, к снижению общей производительности системы. Еще одно наблюдение – увеличение длины ключа приводит к снижению количества записей индекса, способных уместиться в пределах одной страницы, как следствие – к увеличению операций чтения-записи.
15 сен 09, 15:32    [7662096]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
Glory
Member

Откуда:
Сообщений: 104760
kano

Получается в статье https://www.sql.ru/articles/mssql/03013101Indexes.shtml#4] ошибка?
В ней сказано
Кластерный индекс использует row locator и он является частью не кластерного индекса на leaf level. Этот факт приводит к важному правилу SQL Server: создавайте кластерные ключи как можно более короткими. Каждый некластерный индекс будет использовать значения кластерного индекса. Следовательно увеличение размера кластерного индекса приводит к многократному увеличению требований по памяти для всех не кластерных индексов. Последнее приводит к увеличению времени на процессы чтения, сканирования данных и, как следствие, к снижению общей производительности системы. Еще одно наблюдение – увеличение длины ключа приводит к снижению количества записей индекса, способных уместиться в пределах одной страницы, как следствие – к увеличению операций чтения-записи.

Где вы тут видите ошибку ? Если на странице кластерного индекса из-за филлфактора размещена 1 запись вместо 100 возможных, то размер кластерного ключа этой записи не будет равен размеру ста ключей
15 сен 09, 15:37    [7662142]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
kano
Member

Откуда:
Сообщений: 59
Glory,

Может я не так выразился.
Вы сказали что некластерный содержит ССЫЛКУ на кластерный, а в статье сказано что на уровне листьев некластреный индекс содержит верхний уровень кластерного индекса. То есть, не ссылку, а часть самого индекса.
Пример:
два индекса по одной таблице
1) кластерный по Documentid, Item
2) некластерный по DateDoc

Значит древо не кластерного индекса будет [DateDoc:строка:страница], а уровень листьев [DateDoc:Documentid, Item]

И если так, то на уровне листьев, длина строки индекса = 4+4+4 = 12

А если кластерный будет только по Documentid, то длина строки будет = 4+4 = 8

Просто уже столько всего прочитал, хочется окончательно всё расставить по полкам и уяснить, если поможете - будет здорово!
15 сен 09, 16:21    [7662594]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36801
И что? Если вы уберете Item из кластерного индекса, он сразу вдруг перестанет использоваться в запросах и его не надо будет ниоткуда читать?
15 сен 09, 16:23    [7662611]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
Glory
Member

Откуда:
Сообщений: 104760
kano
Glory,

Может я не так выразился.
Вы сказали что некластерный содержит ССЫЛКУ на кластерный, а в статье сказано что на уровне листьев некластреный индекс содержит верхний уровень кластерного индекса. То есть, не ссылку, а часть самого индекса.
Пример:
два индекса по одной таблице
1) кластерный по Documentid, Item
2) некластерный по DateDoc

Значит древо не кластерного индекса будет [DateDoc:строка:страница], а уровень листьев [DateDoc:Documentid, Item]

И если так, то на уровне листьев, длина строки индекса = 4+4+4 = 12

А если кластерный будет только по Documentid, то длина строки будет = 4+4 = 8

А каким боком тут fill factor то ?
15 сен 09, 16:27    [7662671]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
kano
Member

Откуда:
Сообщений: 59
Glory
А каким боком тут fill factor то ?

Если Вы имеете ввиду моё
Хотел уточнить. Есть ли смысл добавить автоинкрементное поле, создать кластерный индекс по нему с fillfactor=0 - это "сожмёт" таблицу и уменьшит размер некластерных индексов(ведь на уровне листьев станет на 4 байта короче запись). А индекс по [DOCUMENTID], [ITEM] сделать не кластерным, и подобрать ему fillfactor в зависимости от средней фрагментации?

Я хотел сказать что fillfactor=0 сожмёт таблицу, а перестройка кластерного индекса по одному полю - уменьшит размер некластерных индексов.
Сорри, пишу коряво.

Гавриленко Сергей Алексеевич
И что? Если вы уберете Item из кластерного индекса, он сразу вдруг перестанет использоваться в запросах и его не надо будет ниоткуда читать?

То есть идею построения некластерного индекса я правильно понял и передал?
Будет ипользоватся канешно же :) Это просто пример, хотелось убедится что я правильно понял архитектуру построения индексов.

Если я Вам ещё не очень надоел, то хотелось услышать ответ на один вопрос.:
На этой же таблице создаём автоинкремент ID и по нему делаем кластерный индекс.
Индекс [DOCUMENTID], [ITEM] соответственно уникальный не кластерный.
Кроме этого, уже есть 4-е других индекса.
Получается, при создании кластерного индекса по ID, все некластерные похудеют на 4байта на строку. Сумма сэкономленного места (69000000*4)/1024)/1024=266,7Мб * 4 = 1068 Мб
А накладные расходы на дополнительный индекс ([DOCUMENTID], [ITEM]) = (69000000*8)/1024)/1024 = 526 Мб
То есть, 1068 - 526 = 542 Мб освободится на диске, и немного меньше будет использоваться кеш-памяти при работе с этими индексами.
Так вот, если ли рациональное зерно в такой экономии или выигрыш в дисковом пространстве сведёт на нет потеря производительности ?
15 сен 09, 17:32    [7663220]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
Glory
Member

Откуда:
Сообщений: 104760
kano
Glory
А каким боком тут fill factor то ?

Если Вы имеете ввиду моё
Хотел уточнить. Есть ли смысл добавить автоинкрементное поле, создать кластерный индекс по нему с fillfactor=0 - это "сожмёт" таблицу и уменьшит размер некластерных индексов(ведь на уровне листьев станет на 4 байта короче запись). А индекс по [DOCUMENTID], [ITEM] сделать не кластерным, и подобрать ему fillfactor в зависимости от средней фрагментации?

Я хотел сказать что fillfactor=0 сожмёт таблицу, а перестройка кластерного индекса по одному полю - уменьшит размер некластерных индексов.
Сорри, пишу коряво.

Еще раз, fillfactor кластерного индекса не влияет на размер некластерных индексов, а размер ключа кластерного индекса - влияет
15 сен 09, 17:37    [7663259]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36801
Далось вам это пространство. Считать надо, сколько лишних чтений вы поимеете при создании кластерного индекса по полю, по которому даже ничего выбирать не собираетесь. Будет очень актуально для запросов, которые использовали кластерный индекс. Полезут букмарки, а кое-где и сканы. Но зато да, гигабайт места мы сэкономили ...
15 сен 09, 17:38    [7663264]     Ответить | Цитировать Сообщить модератору
 Re: Fillfactor и кластерный индекс  [new]
kano
Member

Откуда:
Сообщений: 59
Гавриленко Сергей Алексеевич,

А про слона то я и не подумал :) Вы абсолютно правы - строить индекс по не используемому полю, глупо.

Glory,

Совершенно верно, именно это я и хотел сказать. Длина ключа, это количество полей - если вместо двух использовать одно поле, для построения кластерного индекса - то длина ключа будет меньшая.

Спасибо всем.
Удержали от излишнего рвения. И помогли разобраться в вопросе.
15 сен 09, 17:52    [7663370]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить