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

Откуда: Moscow Square
Сообщений: 624
Добрый день!
Дано: Синхронная реплика AlwaysOn, таблица 763 млн. строк, 77 Гб весом. На ней кластерный индекс с таким же весом в 77 Гб и фрагментацией 42%.
Задача: перестроить его для уменьшения фрагментации.
Проблема: При ребилде для других процессов начинаются множественные ожидания типа HADR_SYNC_COMMIT (ожидание коммита на синхронной реплике), из-за чего всё встает колом. Узкое место - сеть, но между серверами стоит 10 Гбит, расширять некуда.
Ребилд выполняется командой
ALTER INDEX [CI_Index] ON Table
REBUILD WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = ON, ONLINE = ON)

Рекомендуют ограничить ребилд одним потоком:
ALTER INDEX [CI_Index] ON Table
REBUILD WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = ON, ONLINE = ON, MAXDOP=1)

Но картина не меняется, что неудивительно, поскольку MAXDOP=1 стоит на уровне всего сервера.

Дело именно в размере индекса, поскольку после проявления проблемы я ограничил размер обслуживаемых индексов 5 Гб, после чего план отработал без подобных проблем. Осталось считанное число по факту необслуживаемых индексов.

Один из вариантов решения, которые предлагают - на время реиндексации переводить реплику в асинхронный режим, после окончания - возвращать обратно. Но хотелось бы решить проблему как-то более гуманно.
8 апр 18, 22:59    [21322143]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 886
делайте реорганизацию, от фрагметации не избавит,я так делаю, но уменьшит ее, либо стройте по партициям, можно вообще тогда не делать обслуживание индексов, если получится.
А вообще я бы понаблюдал , есть ли отставание реплики, возможно и те индексы негатив создают.
И вообще так необходимо перестраиваить, реально смотрели ? или по феншую типа тоого?
9 апр 18, 01:22    [21322189]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
aleksrov
Member

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

Как сказали выше а вам точно это надо.
Смотрите. У вас стоит maxdop 1, значит или вы не понимаете зачем он или у вас прям чуть ли не 100% OLTP система. Если последнее, или если у вас SSD, фрагментация (external) не будет влиять на производительность никак. В таком случае будет влиять internal, т.е. если страницы у вас полупустые, тогда у вас будет больше индекс и он будет больше занимать места в памяти.
Плюс вы делаете Online ребилд, он пишет в лог намного больше чем offline, попробывал сейчас на свой системе при ребилде индекса 5.4 MB при offline лог вырос примерно на эту цифру, при online на 12-13 MB.
http://sqlblog.com/blogs/kalen_delaney/archive/2011/03/08/what-gets-logged-for-index-rebuilds.aspx
Поэтому у вас вариант или делать offline (если можно конечно) или делать reorginize частями.
9 апр 18, 06:32    [21322229]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
aleksrov
Member

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

Реарганизация также генерит множество записей, в особенности для сильно фрагментированных индексов
https://blogs.msdn.microsoft.com/timchapman/2012/09/28/index-rebuild-vs-reorganize-the-transaction-log-edition/
Но зато вы можете ее остановить без потери результата.
9 апр 18, 06:36    [21322233]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Alexander Titkin
Member

Откуда: Москва
Сообщений: 90
При построении индекса эффект тот же самый, это чтобы отмести гениальные вариации на тему "оно вам надо?". SQL Server 12, 14.
9 апр 18, 08:49    [21322305]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
aleksrov
Oblom,

Как сказали выше а вам точно это надо.
Смотрите. У вас стоит maxdop 1, значит или вы не понимаете зачем он или у вас прям чуть ли не 100% OLTP система. Если последнее, или если у вас SSD, фрагментация (external) не будет влиять на производительность никак. В таком случае будет влиять internal, т.е. если страницы у вас полупустые, тогда у вас будет больше индекс и он будет больше занимать места в памяти.
Плюс вы делаете Online ребилд, он пишет в лог намного больше чем offline, попробывал сейчас на свой системе при ребилде индекса 5.4 MB при offline лог вырос примерно на эту цифру, при online на 12-13 MB.
http://sqlblog.com/blogs/kalen_delaney/archive/2011/03/08/what-gets-logged-for-index-rebuilds.aspx
Поэтому у вас вариант или делать offline (если можно конечно) или делать reorginize частями.


1. У меня прям 100% OLTP-система и да, конкретно эта таблица на SSD.
2. ONLINE жизненно необходим. Таблица высоконагруженная, по факту ядро системы. Любой простой - минус в премии и в карме. По факту online-rebuild единственная причина, почему куплен Enterprise, а не Standart. Вторая причина - этот самый AlwaysOn )))

За ссылку и совет по REORGINIZE огромное спасибо, буду курить.
9 апр 18, 08:58    [21322312]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
да, забыл SELECT @@VERSION
Microsoft SQL Server 2016 (SP1-CU6) (KB4037354) - 13.0.4457.0 (X64)
Nov 8 2017 17:32:23
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
9 апр 18, 09:00    [21322314]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
ОперацияПингвин
Member

Откуда:
Сообщений: 643
Блог
Oblom,

обновляйтесь до 2017-ого сиквела
9 апр 18, 09:28    [21322340]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
ОперацияПингвин
Member

Откуда:
Сообщений: 643
Блог
В 2017 -ом такая фитча есть
resumable index rebuild
9 апр 18, 09:40    [21322374]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
ОперацияПингвин
В 2017 -ом такая фитча есть
resumable index rebuild


Круть.
Спасибо за совет. Только мы все новогодние праздники убили, чтоб обновиться с 2005 на 2016. Боюсь теперь ещё лет 10 на такую авантюру никто не подпишется )))
9 апр 18, 09:47    [21322407]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
ОперацияПингвин
Member

Откуда:
Сообщений: 643
Блог
Oblom
ОперацияПингвин
В 2017 -ом такая фитча есть
resumable index rebuild


Круть.
Спасибо за совет. Только мы все новогодние праздники убили, чтоб обновиться с 2005 на 2016. Боюсь теперь ещё лет 10 на такую авантюру никто не подпишется )))


C 2016 до 2017 должно быть более безболезненно, чем с 2005 до 2016
9 апр 18, 09:58    [21322439]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
ОперацияПингвин
Oblom
пропущено...


Круть.
Спасибо за совет. Только мы все новогодние праздники убили, чтоб обновиться с 2005 на 2016. Боюсь теперь ещё лет 10 на такую авантюру никто не подпишется )))


C 2016 до 2017 должно быть более безболезненно, чем с 2005 до 2016


Говорят, у нас даже лицензионные права это позволяют. Но точно не раньше нового года, а сейчас ещё только апрель ))) Так что проблему как-то надо решать.

Но судя по всему проблема известная и красивых решений не имеет. Либо сваливаться с синхронной на асинхронную реплику, либо партицировать таблицу вместе с индексом и жрать его по кусочкам.
9 апр 18, 10:04    [21322454]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
aleksrov
Member

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

Ребилд больших индексов в принципе гемор как таковой, даже без AG.
У нас стандарт, ребилд делать не могу, т.к. приложение валится в ошибки если таблица недоступна, даже ночью, когда никто не работает, идут потоком автоматические задания, которые перенести нельзя, поэтому тоже делаю только реорг. и иногда ребилд, когда система отключается для проведения каких либо работ.
9 апр 18, 10:14    [21322475]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить