Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
VicSO
Member

Откуда:
Сообщений: 189
Столкнулся с такой проблемой, везде пишется что ALTER INDEX REBUILD ONLINE = ON эта та-же операция что и ALTER INDEX REBUILD ONLINE = OFF только позволяет иметь доступность к индексу таблице ну и в дата файле будет на момент работы занимать двойной размер. Но не где не сказано что и журнал транзакций забьет в 5,5 раз больше чем размер индекса.
то есть REORGANIZE в этом случае даже выигрыше, так как на сильно фрагментированном индексе под 70% он журнал транзакций забил где-то на 2,2 от размера самого индекса.

Я это где-то в документации пропустил или это какой-то баг?
Тестировал на
Microsoft SQL Server Enterprise 13.0.5426.0
Microsoft SQL Server Enterprise 13.0.5026.0
25 окт 19, 11:56    [22002403]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
Владислав Колосов
Member

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

а что, на оффлайн операции прирост журнала меньше?
25 окт 19, 12:05    [22002415]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
VicSO
Member

Откуда:
Сообщений: 189
Владислав Колосов,

Да в офлайн режиме равный размеру индекса+- чучуть.
25 окт 19, 12:06    [22002417]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
VicSO,

я так подозреваю объект в момент ребилда под нагрузкой
25 окт 19, 12:06    [22002419]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
Владислав Колосов
Member

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

ну, не знаю, для меня допустим объем журналов в половину объёма базы. Фактически же меньше трети.
25 окт 19, 12:07    [22002421]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
VicSO
Но не где не сказано что и журнал транзакций забьет в 5,5 раз больше чем размер индекса.
...
Я это где-то в документации пропустил или это какой-то баг?

никакой это не баг, это плата за онлайновость.
оффлайновый ребилд минимально логируется в симпл и балк_логгед,
"эффективно" т.е. постранично логируется в полной модели.
а онлайновый ребилд пишет в лог построчно, конечно он лог закакает в любой модели
25 окт 19, 12:08    [22002423]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
msLex
Member

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

Никакого бага нет

Из-за онлайновости, ALTER INDEX REBUILD ONLINE = ON выполняется дольше (может сильно дольше), и т.к. это един транзакция, записи в логе (все, а не только этого процесса) с момента ее старта не могут быть удалены.

REORGANIZE же - это не единая транзакция, а следовательно не блокирует очистку лога.


В SQLServer 2017 для ALTER INDEX REBUILD ONLINE = ON появилась опция resumable. По-мимо возобновляемости, она делает REBUILD неединой транзакцией.
25 окт 19, 12:10    [22002428]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
VicSO
Member

Откуда:
Сообщений: 189
TaPaK,
Нет нагрузки нету.
Yasha123,
Ну тогда онлайн не выгодный в принципе
сильно фрагментированном индексе под 70% REORGANIZE отъел всего 2 раза больше.
раз онлайн отъедает 5 раза больше, тем более REORGANIZE можно отменить в любой момент без потери результата, чего не скажешь о ONLINE
25 окт 19, 12:12    [22002432]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
Владислав Колосов
Member

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

не выгоден, если у вас не 24/7 требования доступности.
25 окт 19, 12:13    [22002435]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
msLex
Member

Откуда:
Сообщений: 8065
VicSO
Ну тогда онлайн не выгодный в принципе
сильно фрагментированном индексе под 70% REORGANIZE отъел всего 2 раза больше.
раз онлайн отъедает 5 раза больше, тем более REORGANIZE можно отменить в любой момент без потери результата, чего не скажешь о ONLINE



1. REORGANIZE - очень (очень, очень) долгая операция.
2. Возможности REORGANIZE по уменьшению физической (внутренней) фрагментации сильно ограничены.
25 окт 19, 12:15    [22002437]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2394
не баг, а фича (как уже сказали для тех кто 24/7)

во время обслуживания индексов запускайте бэкап лога почаще (если у вас Фулл модель)
25 окт 19, 12:16    [22002438]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
VicSO
TaPaK,
Нет нагрузки нету.
Yasha123,
Ну тогда онлайн не выгодный в принципе
сильно фрагментированном индексе под 70% REORGANIZE отъел всего 2 раза больше.
раз онлайн отъедает 5 раза больше, тем более REORGANIZE можно отменить в любой момент без потери результата, чего не скажешь о ONLINE

да вам msLex уже объяснил,
никакие не 5 раз.
это из-за единой транзакции лог не может быть очищен,
и в ваш лог, пока ребилдилось, еще кто-то поднавалил.
сам же ребилд больше, чем 2 х размер индекса, не навалит
25 окт 19, 12:16    [22002440]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
VicSO
Member

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

В том то и дело, что отъедает в 5 раз больше, тогда смысл операции этой не понятен.
причем если делать DATA_COMPRESSION = PAGE , DATA_COMPRESSION = NONE то объём отъедаемый адекватен.
при сжатие получилось 0.8 при распаковки 1.3
25 окт 19, 12:16    [22002441]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
msLex
Member

Откуда:
Сообщений: 8065
Владислав Колосов
VicSO,

не выгоден, если у вас не 24/7 требования доступности.

при REORGANIZE данные также остаются доступны.
25 окт 19, 12:16    [22002442]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
VicSO
Member

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

В этот момент записей нет, база в тесте создана мной, и к ней кроме как данная операция не применяется.
25 окт 19, 12:18    [22002444]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
msLex
Member

Откуда:
Сообщений: 8065
VicSO
msLex,

В том то и дело, что отъедает в 5 раз больше, тогда смысл операции этой не понятен.
причем если делать DATA_COMPRESSION = PAGE , DATA_COMPRESSION = NONE то объём отъедаемый адекватен.
при сжатие получилось 0.8 при распаковки 1.3


Если вы все же поймете, что

Лог в 5 раз больше != rebuild записал в лог в 5 раз

то все остальное встанет на свои места само.
25 окт 19, 12:19    [22002446]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
msLex
Member

Откуда:
Сообщений: 8065
VicSO
Yasha123,

В этот момент записей нет, база в тесте создана мной, и к ней кроме как данная операция не применяется.


модель восстановления какая?
25 окт 19, 12:20    [22002448]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
VicSO
В этот момент записей нет, база в тесте создана мной, и к ней кроме как данная операция не применяется.

сейчас что-нибудь небольшое отребилдю,
где только я на сервере.
статистику вывешу, дайте 10 минут.
25 окт 19, 12:24    [22002456]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Yasha123
VicSO
В этот момент записей нет, база в тесте создана мной, и к ней кроме как данная операция не применяется.

сейчас что-нибудь небольшое отребилдю,
где только я на сервере.
статистику вывешу, дайте 10 минут.

вполне что-то мелкое может дать x5.
Но в жизни все индексы строятся онлайн и при таком варианте места б на лог не хватало бы никогда
25 окт 19, 12:36    [22002472]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
VicSO
Member

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

FULL а иначе вопрос не стоял, попались 2 индекса больших и в итоге база в тала. индексы были 50гб, и лога на 500гб просто не хватило. каждый час лог транзакции снимется и размер обычно от 300-до 1024 мегабайт.
25 окт 19, 12:37    [22002474]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
https://www.mssqltips.com/sqlservertip/2361/rebuilding-sql-server-indexes-using-the-online-option/
25 окт 19, 12:39    [22002476]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
VicSO
Member

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

Вот я тоже думал что требуется ток дополнительное пространство, но не в 5 раз больше чем сам индекс.
25 окт 19, 12:44    [22002478]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
VicSO
TaPaK,

Вот я тоже думал что требуется ток дополнительное пространство, но не в 5 раз больше чем сам индекс.

да никакие не 5 раз,
просто кроме вас в лог еще кто-то писал.

мое репро:
таблицу фрагментирую на 50%,
вставив сперва 10000 страниц с четным номером,
потом 10000 с нечетным,
1 запись = 1 страница.

фрагментированная таблица 300Мб, после ребилда 156Мб.
онлайновый ребилд записал в лог 214 Мб,
больше, чем объем перестроенного индекса,
но все же меньше, чем объем перестроенного х 2
+
select top 10000 n,
       cast('a' as char(8000)) as filler
into dbo.t
from dbo.nums
where n % 2 = 0;

alter table dbo.t
add constraint PK_t primary key clustered(n);

insert into dbo.t
select top 10000 n,
       cast('a' as char(8000)) as filler
from dbo.nums
where n % 2 > 0;

select *
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.t'), 1, null, null)


backup database [TestBulk]
to disk = 'C:\backups\sql_2016_dev\testbulk_full.bak'
with init;

alter database [TestBulk] set recovery full

backup log [TestBulk]
to disk = 'C:\backups\sql_2016_dev\testbulk_log.trn'

alter index PK_t on dbo.t rebuild
with (ONLINE = ON )

select sum([Log Record Fixed Length] + [Log Record Length])
from fn_dblog(null, null) -- 225.241.956 - 214Mb
25 окт 19, 13:07    [22002494]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
VicSO
Member

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

Ну значит либо баг какой-то либо настройки. А по по больше размер можете индекс создать на 10гб и попробовать тоже самое?
25 окт 19, 13:56    [22002595]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с ALTER INDEX REBUILD ONLINE = ON  [new]
VicSO
Member

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

Сделал по другому, сделал свою базу и восстановил туда бэкап
--несколько попыток.
BACKUP LOG [TEST] TO DISK = N'Nul:' WITH NOFORMAT, NOINIT, NAME = N'TEST', SKIP, NOREWIND, NOUNLOAD, STATS = 10
ALTER INDEX [PK_TanksVar] ON [dbo].[TanksVar] REBUILD WITH ( ONLINE = ON )
select sum([Log Record Fixed Length] + [Log Record Length]) from fn_dblog(null, null)
--505973580
BACKUP LOG [TEST] TO DISK = N'Nul:' WITH NOFORMAT, NOINIT, NAME = N'TEST', SKIP, NOREWIND, NOUNLOAD, STATS = 10
ALTER INDEX [PK_TanksVar] ON [dbo].[TanksVar] REBUILD WITH ( ONLINE = ON)
select sum([Log Record Fixed Length] + [Log Record Length]) from fn_dblog(null, null)
--512260764
BACKUP LOG [TEST] TO DISK = N'Nul:' WITH NOFORMAT, NOINIT, NAME = N'TEST', SKIP, NOREWIND, NOUNLOAD, STATS = 10
ALTER INDEX [PK_TanksVar] ON [dbo].[TanksVar] REBUILD WITH ( ONLINE = OFF )
select sum([Log Record Fixed Length] + [Log Record Length]) from fn_dblog(null, null)
--70999674
BACKUP LOG [TEST] TO DISK = N'Nul:' WITH NOFORMAT, NOINIT, NAME = N'TEST', SKIP, NOREWIND, NOUNLOAD, STATS = 10
ALTER INDEX [PK_TanksVar] ON [dbo].[TanksVar] REBUILD WITH ( ONLINE = OFF )
select sum([Log Record Fixed Length] + [Log Record Length]) from fn_dblog(null, null)
--70985806
Как видим разница 512260764/70985806=7,216 раз.
26 окт 19, 07:52    [22003106]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить