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

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
Ну или кластерный по WEBPROJECT_COMPONENT_ID, CHECK_SUM
12 фев 13, 02:01    [13911621]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

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

С CPU боюсь соврать, думаю что да. Стандартно была загрузка на 50-60% сейчас 10-20%. Но это не показатель так как ночь, количество юзеров меньше в разы.
12 фев 13, 02:01    [13911622]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

Откуда:
Сообщений: 93
Ray D,

Вполне возможно. Еще немного деталей. Данная таблица разрастается по мере формирования кешей страниц. Затем она достигает своего "рабочего" значения примерно в 1 млн записей и живет своей жизнью. Периодически обновляются и добавляются новые записи. В день "новых" записей не больше 2000. "Обновленных" записей не больше 10 тыс.-20 тыс. Данные в ней по-большому временные и ее можно без потерь пересоздать, и она в течение 2-3 дней заполнится сама. пару раз делал truncate table и обнулял все.

Пока база растет потерь в производительности особо не наблюдалось. Когда она разбухла стало все ощутимо. Начал ковырять и увидел фрагментацию индексов. Сделал ребилд. Вроде все зафурычило опять, потом через 2 часа опять та же хрень.

В базе везде установлено automatically recompute statistics по индексам.
12 фев 13, 02:10    [13911627]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Стопудово, план. Прибейте его хинтами и сделайте кластерный индекс.

Сообщение было отредактировано: 12 фев 13, 02:17
12 фев 13, 02:15    [13911631]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

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

В принципе можно сейчас все вернуть на место и для чистоты эксперимента посмотреть что будет.

Второй вопрос - вы считаете можно было хинтами ситуацию вылечить?
12 фев 13, 02:19    [13911632]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Forceseek, если версия позволяет.

З.Ы. Ваш несчастный миллион записей тупить может только если неправильный план. Ну или по одной записи на страницу раскидать, что чуть менее, чем невероятно при озвученных цифрах и схеме.
12 фев 13, 02:23    [13911635]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

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

А кластерный индекс делать все же как отдельное big int или можно по PK.
Я вот этот момент пока не уловил, почему по PK не сработало.
12 фев 13, 02:30    [13911640]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3422
khourshed
Я вот этот момент пока не уловил, почему по PK не сработало.
У вас первичный ключ на гуиде, это 16 байт. Если вы сделаете его кластерным, то эти 16 байт будут входить во все некластерные индексы как указатель. Т.е. одна запись в некластерном индексе по инту будет весить 20 байт, из которых полезных будет только 4.

Если же сделать PK по инту (при ваших объемах вставки этого вполне достаточно), то ширина такого индекса будет уже 8 байт вместо 20, и КПД поднимется в разы.

Именно поэтому всегда рекомендуют делать кластерный индекс как можно более узким. Гуид в этом плане - один из худших кандидатов.
12 фев 13, 05:26    [13911684]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
khourshed,

и дать дельный практический совет то я вам буду премного благодарен. ...

Даю дельный практический совет:
Бросить бороться с фрагментацией, и заняться решением конкретных проблем производительности: кривыми планами запросов, созданием индексов...

Фрагментация лечится очень просто — пере созданием индекса или таблицы. Есть также более мягкие, современные варианты, но про сути это то же самое. Ты будешь это делать? Нет, ты делать это не будешь, потому что это долго, дорого, и возможно потребует отключение бд от пользователей.
12 фев 13, 09:49    [13912069]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
Гость333
Member

Откуда:
Сообщений: 3683
khourshed
вот 10 последних записей, например:
3A8F5B5F-7074-E211-86F5-000F20D0F73F
288F5B5F-7074-E211-86F5-000F20D0F73F
474C6359-7074-E211-86F5-000F20D0F73F
3C4C6359-7074-E211-86F5-000F20D0F73F
3B4C6359-7074-E211-86F5-000F20D0F73F
2F4C6359-7074-E211-86F5-000F20D0F73F
204C6359-7074-E211-86F5-000F20D0F73F
144C6359-7074-E211-86F5-000F20D0F73F
ED4B6359-7074-E211-86F5-000F20D0F73F
CEBA5C53-7074-E211-86F5-000F20D0F73F

Как видите значения последовательны.

Последовательны, но есть один нюанс...
declare @t table (guid uniqueidentifier);

insert @t
select '3A8F5B5F-7074-E211-86F5-000F20D0F73F' union all
select '288F5B5F-7074-E211-86F5-000F20D0F73F' union all
select '474C6359-7074-E211-86F5-000F20D0F73F' union all
select '3C4C6359-7074-E211-86F5-000F20D0F73F' union all
select '3B4C6359-7074-E211-86F5-000F20D0F73F' union all
select '2F4C6359-7074-E211-86F5-000F20D0F73F' union all
select '204C6359-7074-E211-86F5-000F20D0F73F' union all
select '144C6359-7074-E211-86F5-000F20D0F73F' union all
select 'ED4B6359-7074-E211-86F5-000F20D0F73F' union all
select 'CEBA5C53-7074-E211-86F5-000F20D0F73F';

select * from @t order by guid;

(10 row(s) affected)
guid
------------------------------------
CEBA5C53-7074-E211-86F5-000F20D0F73F
ED4B6359-7074-E211-86F5-000F20D0F73F
144C6359-7074-E211-86F5-000F20D0F73F
204C6359-7074-E211-86F5-000F20D0F73F
2F4C6359-7074-E211-86F5-000F20D0F73F
3B4C6359-7074-E211-86F5-000F20D0F73F
3C4C6359-7074-E211-86F5-000F20D0F73F
474C6359-7074-E211-86F5-000F20D0F73F
288F5B5F-7074-E211-86F5-000F20D0F73F
3A8F5B5F-7074-E211-86F5-000F20D0F73F

(10 row(s) affected)

Как видно, это последовательно убывающие значения. А индекс у вас (был) возрастающий. То есть вставка велась не в конец индекса, а в начало. Всё сходится — при таком раскладе фрагментация моментально становится over 99%.
12 фев 13, 09:52    [13912082]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
Crimean
Member

Откуда:
Сообщений: 13147
а запрос идет как батч или внутри хранимки?
12 фев 13, 11:45    [13912774]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
khourshed
Ray D,

Вполне возможно. Еще немного деталей. Данная таблица разрастается по мере формирования кешей страниц. Затем она достигает своего "рабочего" значения примерно в 1 млн записей и живет своей жизнью. Периодически обновляются и добавляются новые записи. В день "новых" записей не больше 2000. "Обновленных" записей не больше 10 тыс.-20 тыс. Данные в ней по-большому временные и ее можно без потерь пересоздать, и она в течение 2-3 дней заполнится сама. пару раз делал truncate table и обнулял все.

Пока база растет потерь в производительности особо не наблюдалось. Когда она разбухла стало все ощутимо. Начал ковырять и увидел фрагментацию индексов. Сделал ребилд. Вроде все зафурычило опять, потом через 2 часа опять та же хрень.

В базе везде установлено automatically recompute statistics по индексам.


Так вообще в таком случае какой смысл с фрагмент ацией бороться
12 фев 13, 11:54    [13912859]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
А еще вопрос к автору топика:
Запросы на выборку какие?
Точечные или есть выборка диапазона про индексу?

Просто если точечные запросы, то им фрагментация и не важна.
12 фев 13, 12:17    [13913024]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

Откуда:
Сообщений: 93
Гость333,

Я последовательность привел в убывающем порядке. Значения все возрастающие. Иначе там в принципе быть не может
12 фев 13, 12:23    [13913082]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

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

Запрос идет как хп
12 фев 13, 12:23    [13913085]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

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

автор
Так вообще в таком случае какой смысл с фрагмент ацией бороться


Вы мне предлагаете каждые 3 часа индекс ребилдить?
12 фев 13, 12:24    [13913091]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
khourshed,

Кластерный индекс сначала решил создать на Primary Key (все значения этого поля получены функцией NewSequentialId()). Ситуацию не поменяло. Основной рабочий индекс IX_CheckSum через 2 часа опять 98% фрагментации. Попробую с полем типа int как советовали. может изменит ситуацию.


Как бы кластерный индекс или нет, на фрагментацию никак не влияет.
Влияет только на фрагментацию этого самого индекса, один раз, после пересодания. Потом он расползается.
12 фев 13, 12:26    [13913106]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

Откуда:
Сообщений: 93
Ennor Tiegael,

автор
У вас первичный ключ на гуиде, это 16 байт. Если вы сделаете его кластерным, то эти 16 байт будут входить во все некластерные индексы как указатель. Т.е. одна запись в некластерном индексе по инту будет весить 20 байт, из которых полезных будет только 4.

Если же сделать PK по инту (при ваших объемах вставки этого вполне достаточно), то ширина такого индекса будет уже 8 байт вместо 20, и КПД поднимется в разы.

Именно поэтому всегда рекомендуют делать кластерный индекс как можно более узким. Гуид в этом плане - один из худших кандидатов.


Спасибо за объяснение.
Вопрос еще вот в чем. Почему кластерный индекс по PK не вылечил фрагментацию по IX_CheckSum как с int-ом. У меня подозрения что все таки план запроса был не оптимальный, соглашусь с предыдущими ораторами. Руки дойдут поставлю эксперимент для подтверждения.

Но все равно, имея кластерный индекс даже по PK с NewSequentialId() мне кажется такой хрени не должно было быть с планом.
12 фев 13, 12:28    [13913137]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

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

автор
А еще вопрос к автору топика:
Запросы на выборку какие?
Точечные или есть выборка диапазона про индексу?

Просто если точечные запросы, то им фрагментация и не важна.


Запросы в 99% случаев точечные. Я бы даже сказал в 99,9%
12 фев 13, 12:30    [13913152]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

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

автор
Даю дельный практический совет:
Бросить бороться с фрагментацией, и заняться решением конкретных проблем производительности: кривыми планами запросов, созданием индексов...

Фрагментация лечится очень просто — пере созданием индекса или таблицы. Есть также более мягкие, современные варианты, но про сути это то же самое. Ты будешь это делать? Нет, ты делать это не будешь, потому что это долго, дорого, и возможно потребует отключение бд от пользователей.


Дык я за этим и пришел сюда - разобраться в ситуации. Фрагментация - это повод для анализа проблемы.
12 фев 13, 12:32    [13913164]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
Crimean
Member

Откуда:
Сообщений: 13147
khourshed
Запрос идет как хп


а проблема "в целом" выражается как "проседания" выполнения конкретно этой хп?
или, скорее, связана вообще с вычитками из этой таблицы "по всему коду"?
и, если вариант 1 - рекомпиляция через sp_recompile "случайно" не решит ли проблему, хотя бы "на время"?
12 фев 13, 12:36    [13913200]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

Откуда:
Сообщений: 93
Ну вот, все -таки меня сомнения терзали не напрасно. После нормальной утренней нагрузки в 50 тыс запросов фрагментация индекса IX_CHECKSUM 98% при кластерном int-е

План запроса:
  |--Compute Scalar(DEFINE:([Expr1004]=isnull([ViewCacheDb].[dbo].[cms_tblViewCache].[INSTANCE_STATE_ID],{guid'B01ADD9F-F2DA-4ED8-AAE5-373A8B3EB4B7'})))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [ViewCacheDb].[dbo].[cms_tblViewCache].[ID]))
|--Index Seek(OBJECT:([ViewCacheDb].[dbo].[cms_tblViewCache].[IX_CheckSum]), SEEK:([ViewCacheDb].[dbo].[cms_tblViewCache].[WEBPROJECT_COMPONENT_ID]=[@pWebProjectId] AND [ViewCacheDb].[dbo].[cms_tblViewCache].[CHECK_SUM]=[@pCheckSum]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([ViewCacheDb].[dbo].[cms_tblViewCache].[IX_cms_tblViewCache_Id]), SEEK:([ViewCacheDb].[dbo].[cms_tblViewCache].[ID]=[ViewCacheDb].[dbo].[cms_tblViewCache].[ID] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)

Состояние индекса:
fill_factornametablenameleaf_insert_countleaf_delete_countleaf_update_countleaf_allocation_countnonleaf_allocation_countrange_scan_countsingleton_lookup_countrow_lock_countrow_lock_wait_countrow_lock_wait_in_ms
80IX_CheckSumcms_tblViewCache546050069830270228110610115


Кластерный инт проблему не решил. Причем по другим индексам фрагментация осталась маленькой. Только IX_CheckSum затронуло. Производительность стала заметно хуже.
12 фев 13, 12:59    [13913369]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

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

Я уже писал - проседают selectы с NOLOCK
12 фев 13, 13:00    [13913374]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
Ennor Tiegael
Member

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

А WEBPROJECT_COMPONENT_ID у вас тоже через NewSequentialId() генерится? Ибо если нет, то такой индекс всегда будет размываться очень быстро, при любом кластернике.
12 фев 13, 13:02    [13913396]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

Откуда:
Сообщений: 93
Ennor Tiegael,

WEBPROJECT_COMPONENT_ID через newid() генерился.
12 фев 13, 13:11    [13913478]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить