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

Откуда:
Сообщений: 75
Коллеги , добрый день !
прошу совет или/и раьяснение.
Есть база ms sql 2012 с совместимостью 2005
размер = 1,5 Тб
есть желание растащить данные и индексы по файловым группам (физически по разным шпинделям)
данные оставляю где и были , индексы в новую ребилдю
так вот возник вопрос , опишу кратко порядок действий :
1. создаю файловую группу FGIDX01
2. собственно запускаю скриптик который ребилдит в новую файловую группу (alter index ... on FGIDX01)
3. далее дефрагментирую и ужимаю данные
.....

так вот вопрос как ограничить FGIDX01 только индексами
мне не надо чтобы новые обьекты создавались в новой группе , если явно не указали , и данные от старых обьектов тоже не надо туда писать ю только индексы с явным указанием .
??
4 мар 15, 07:06    [17340517]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
egor_s
мне не надо чтобы новые обьекты создавались в новой группе , если явно не указали??

если не указали конкретную ФГ, то объект создаётся в дефолтной ФГ...по умолчанию - это праймори...

кроме того, вы в курсе, что кластерный индекс - это и есть сами данные? или у вас новая ФГ только под некластерные индексы?
4 мар 15, 08:18    [17340608]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
egor_s
Member

Откуда:
Сообщений: 75
да мне нужны только NONCLUSTERED
а откуда видно праймогри ФГ
каким запросом можно посмотреть
4 мар 15, 10:37    [17341083]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4424
egor_s
да мне нужны только NONCLUSTERED
а откуда видно праймогри ФГ
каким запросом можно посмотреть

select * from sys.data_spaces
4 мар 15, 11:00    [17341187]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
сомнение
Guest
автор
есть желание растащить данные и индексы по файловым группам (физически по разным шпинделям)

А зачем это делается? Ведь, если я правильно понимаю, при использовании некластерного индекса будут задействованы индексы на одном шпинделе и данные на другом шпинделе.
4 мар 15, 11:06    [17341221]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4424
сомнение
автор
есть желание растащить данные и индексы по файловым группам (физически по разным шпинделям)

А зачем это делается? Ведь, если я правильно понимаю, при использовании некластерного индекса будут задействованы индексы на одном шпинделе и данные на другом шпинделе.

Правильно понимаете. Операции чтения происходят одновременно... точнее с малым временным промежутком между ними.
4 мар 15, 11:12    [17341260]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
сомнение
Guest
SQL2008
сомнение
пропущено...

А зачем это делается? Ведь, если я правильно понимаю, при использовании некластерного индекса будут задействованы индексы на одном шпинделе и данные на другом шпинделе.

Правильно понимаете. Операции чтения происходят одновременно... точнее с малым временным промежутком между ними.


В случае, если индексы и данные будут лежать на одном шпинделе, то серверу надо получить индекс, выявить нужный сегмент (все аналогично) - подождать пока диск прокрутится максимум один полный раз и считать нужный сегмент.
В чем выигрыш подхода, когда данные и индексы на разных шпинделях? Я не понимаю, ведь время полной прокрутки диска не так велико.
4 мар 15, 11:34    [17341399]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4424
сомнение
SQL2008
пропущено...

Правильно понимаете. Операции чтения происходят одновременно... точнее с малым временным промежутком между ними.


В случае, если индексы и данные будут лежать на одном шпинделе, то серверу надо получить индекс, выявить нужный сегмент (все аналогично) - подождать пока диск прокрутится максимум один полный раз и считать нужный сегмент.
В чем выигрыш подхода, когда данные и индексы на разных шпинделях? Я не понимаю, ведь время полной прокрутки диска не так велико.

Контроллеру (не серверу!) нужно считать индекс, перепозиционировать считывающую головку на нужный кластер (участок записи на диске), начитать данные в кэш контроллера, выдать команду процессору, что данные готовы, дождаться когда процессор их считает, перепозиционироваться на чтение следующего индекса, сбросить память в кэше, начитать новое значения из нового кластера в кэш и далее по кругу.

В случае с одновременными чтениями - с одного диска читаются индексы.
Если они реорганизованы, то последовательно друг за другом, следовательно в один кэш. Больше к диску обрашения не будет пока в кэше загружены все наши индексы.
Со второго диска начитываются данные. Опять таки в кэш.

Разницу чувствуете?
4 мар 15, 12:11    [17341562]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8570
Если у вас кеш хит менее 80%, то никакие шпиндели не помогут.
Данные же практически никогда не будут находиться на диске физически последовательно, учитывая, что диск показывает наружу логические, а не физические поверхности, дорожки и т.д. Баловство по-моему.
Впрочем, автор может поупражняться и потом всем рассказать об эффективности инновации.
4 мар 15, 15:06    [17342599]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
egor_s
Member

Откуда:
Сообщений: 75
такое баловство проводил не раз , но на Oracle/Solaris и принял это за норму
эффективность на высоте.
5 мар 15, 07:27    [17344875]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
egor_s
Member

Откуда:
Сообщений: 75
млин все замуты с переносом усложняются просто alter index .... reduild .... on NewFG
не прокатит сначала надо дропнуть индекс а потом пересоздать в новом месте
это не гуд я хотел online .

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

как просто все в oracle
ALTER INDEX ... REBUILD [ TABLESPACE ... ]
5 мар 15, 08:03    [17344913]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
invm
Member

Откуда: Москва
Сообщений: 9719
egor_s
как пересоздать индекс онлайн в новом месте
create index ... on ... with (drop_existing = on, online = on) on [filegroup];
Но таблица все равно буден недоступна до завершения инструкции.
5 мар 15, 08:36    [17344970]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37224
invm
Но таблица все равно буден недоступна до завершения инструкции.
Почему?
5 мар 15, 10:12    [17345285]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
invm
Member

Откуда: Москва
Сообщений: 9719
Гавриленко Сергей Алексеевич
Почему?
Sch-M накладывается на таблицу.
5 мар 15, 10:46    [17345424]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37224
invm
Гавриленко Сергей Алексеевич
Почему?
Sch-M накладывается на таблицу.
online = on не смущает?
5 мар 15, 11:59    [17345911]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
сомнение
Guest
egor_s
такое баловство проводил не раз , но на Oracle/Solaris и принял это за норму
эффективность на высоте.


Сколько в попугаях?
5 мар 15, 12:08    [17345963]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
o-o
Guest
Гавриленко Сергей Алексеевич
invm
пропущено...
Sch-M накладывается на таблицу.
online = on не смущает?


Search Engine Q&A #19: Misconceptions around index rebuilds (allocation, BULK_LOGGED mode, locking)
Myth 5: online index rebuild doesn't take any locks

This myth is untrue. The 'online' in 'online index operations' is a bit of a misnomer. Online index operations need to take two very short-term table locks. An S (Shared) table lock at the start of the operation to force all write plans that could touch the index to recompile, and a SCH-M (Schema-Modification – think of it as an Exclusive) table lock at the end of operation to force all read and write plans that could touch the index to recompile.

The most recent time this came up on the forums was someone noticing insert queries timing out after an online index rebuild operation had just started. The problem is that the table lock that online index rebuild needs has to be entered into the grant queue in the lock manager until it can be acquired – and it will stay there until existing transactions that are holding conflicting locks either commit or roll-back. Any transaction that requires a conflicting lock AFTER the index rebuild lock has been queued but not acquired (and then released) will wait behind it in the lock grant queue. If the query timeout is reached before the transaction can get it's lock, it will timeout.

This is still much better than the table lock being held for the entire duration of the index rebuild operation.
5 мар 15, 12:15    [17346001]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
invm
Member

Откуда: Москва
Сообщений: 9719
Гавриленко Сергей Алексеевич
online = on не смущает?
Не смущает. Такое поведение документировано:
https://msdn.microsoft.com/ru-ru/library/ms188783.aspx?f=255&MSPPError=-2147217396
ON

Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This enables queries or updates to the underlying table and indexes to proceed. At the start of the operation, a Shared (S) lock is held on the source object for a very short period of time. At the end of the operation, for a short period of time, an S (Shared) lock is acquired on the source if a nonclustered index is being created; or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online and when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table.
+ В профайлере это отчетливо видно
create table dbo.t (id int primary key, v int);
create index IX_t__v on dbo.t(v);
insert into dbo.t values (1, 1);
go

create index IX_t__v on dbo.t(v) with (drop_existing = on, online = on);
go

drop table dbo.t;
go

EventSequenceEventClassTextDataModeTypeObjectIDObjectID2TransactionIDDurationSPID
157902SQL:StmtStartingcreate index IX_t__v on dbo.t(v) with (drop_existing = on, online = on);55
157903Lock:Acquired$hash = 0x2b0a656d:0x02 - Sch-M11 - METADATA0022615106055
157904Lock:Released$hash = 0x2b0a656d:0x02 - Sch-M11 - METADATA002261510655
157905Lock:Acquired[INDEX_OPERATION]2 - Sch-M5 - OBJECT72210161372210161322615105055
157906Lock:Acquired$hash = 0x38:0x40000002 - Sch-M11 - METADATA0022615108055
157907Lock:Released$hash = 0x38:0x40000002 - Sch-M11 - METADATA002261510855
157908Lock:Acquired2 - Sch-M12 - HOBT0367494570756295884822615109055
157909Lock:Released2 - Sch-M12 - HOBT036749457075629588482261510955
157910Lock:Acquired2 - Sch-M5 - OBJECT72210161372210161322615105055
157911Lock:Acquiredobject_id = 722101613, index_id or stats_id = 22 - Sch-M11 - METADATA0022615105055
157912Lock:Acquiredobject_id = 722101613, index_id or stats_id = 22 - Sch-M11 - METADATA0022615105055
157913Lock:Acquired2 - Sch-M12 - HOBT0367494570756295884822615105055
SQL:StmtCompletedcreate index IX_t__v on dbo.t(v) with (drop_existing = on, online = on);2261510555
157916Lock:Released2 - Sch-M12 - HOBT036749457075629588482261510555
157917Lock:Released[INDEX_OPERATION]2 - Sch-M5 - OBJECT7221016137221016132261510555
157918Lock:Releasedobject_id = 722101613, index_id or stats_id = 22 - Sch-M11 - METADATA002261510555
157919Lock:Released2 - Sch-M5 - OBJECT7221016137221016132261510555
5 мар 15, 13:14    [17346439]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37224
Как сочетается short term lock в документациях c утверждением "Но таблица все равно буден недоступна до завершения инструкции"?

Сообщение было отредактировано: 5 мар 15, 13:44
5 мар 15, 13:43    [17346666]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
invm
Member

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

В документации не сказано, что Sch-M удержвается на короткое время.

ЗЫ: Документация может содержать неточности, которые каждая из дискутирующих сторон может трактовать по-разному.
5 мар 15, 15:08    [17347356]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
Гавриленко Сергей Алексеевич
Member

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

В документации не сказано, что Sch-M удержвается на короткое время.

ЗЫ: Документация может содержать неточности, которые каждая из дискутирующих сторон может трактовать по-разному.
По факту: не происходит никакой недоступности таблицы на все время операции. Сделайте себе для теста таблицу 10 Гб и убедитесь сами. Мне убеждаться в этом никакой необходимости нет, потому что я последние пол года очень активно и именно так объекты между файловыми группами таскаю, в т.ч. и такие, которые ребилдятся по два дня. Никакой недоступности таблицы в течение двух этих дней я не наблюдал.
5 мар 15, 15:19    [17347453]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
o-o
Guest
цитата из видяшки Рэндала для интересующихся:

There are two locks that are held by online index operations that can actually cause long-term blocking.
Now, if you think about it, the phrase "almost online index operations" isn't a very good marketing term,
so the phrase " online index operations" was coined even though there is the potential for an online index operation
to actually NOT be online
.
So the first lock held is a share table lock and that's held at the start of the operation so that new in-build index can be created.
Before the share lock can be held there has to be no other operations that are modifying the table, so it's possible that the share lock has to wait,
While the share lock is waiting, anybody else that wants to make modifications to the table is going to be blocked, and so there is a potential for blocking.
At the end of the operation a special kind of lock called a Schema-Modification lock needs to be held.
You can think of a Schema-Modification lock as a kind of super Exclusive lock, nobody else can have any locks whatsoever on the table.
Now, because of this, this Schema-Modification lock can't be granted until there are no readers or writers of the table.
So there is the possibility that it's gonna block for a while.
While the Schema-Modification lock is waiting to be granted, nobody else can start doing anything else with the table,
no new transaction can start, so again, potential for long-term blocking.
From both of these locks there is then a potential for long-term blocking.
So the beginning and end points of the online index operations may not be very online at all.
For the long-term duration of the online index operation, after the share lock has been momentarily grabbed and then released,
there is a lock that's held for the duration of the operation, and it's called an Intent-Share lock.
You can think of it as a s kind of light-weight lock and that shouldn't cause any blocking unless somebody does an operation that require an exclusive table lock

К сообщению приложен файл. Размер - 78Kb
5 мар 15, 15:42    [17347680]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8570
egor_s
такое баловство проводил не раз , но на Oracle/Solaris и принял это за норму
эффективность на высоте.

Это с секундомером или силой самовнушения и верой в Истины? Насчет Oracle ничего не скажу, не знаю, как он работает с индексами.
Замечу, что это далеко не первое, на что стоит обращать внимание при неудовлетворительной производительность системы.
5 мар 15, 18:43    [17348939]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
o-o
Guest
invm
+ В профайлере это отчетливо видно
create table dbo.t (id int primary key, v int);
create index IX_t__v on dbo.t(v);
insert into dbo.t values (1, 1);
go

create index IX_t__v on dbo.t(v) with (drop_existing = on, online = on);
go

drop table dbo.t;
go

EventSequencetEventClasstTextDatatModetTypetObjectIDtObjectID2tTransactionIDtDurationtSPID
157902tSQL:StmtStartingtcreate index IX_t__v on dbo.t(v) with (drop_existing = on, online = on);ttttttt55t
157903tLock:Acquiredt$hash = 0x2b0a656d:0x0t2 - Sch-Mt11 - METADATAt0t0t22615106t0t55t
157904tLock:Releasedt$hash = 0x2b0a656d:0x0t2 - Sch-Mt11 - METADATAt0t0t22615106tt55t
157905tLock:Acquiredt[INDEX_OPERATION]t2 - Sch-Mt5 - OBJECTt722101613t722101613t22615105t0t55t
157906tLock:Acquiredt$hash = 0x38:0x4000000t2 - Sch-Mt11 - METADATAt0t0t22615108t0t55t
157907tLock:Releasedt$hash = 0x38:0x4000000t2 - Sch-Mt11 - METADATAt0t0t22615108tt55t
157908tLock:Acquiredtt2 - Sch-Mt12 - HOBTt0t3674945707562958848t22615109t0t55t
157909tLock:Releasedtt2 - Sch-Mt12 - HOBTt0t3674945707562958848t22615109tt55t
157910tLock:Acquiredtt2 - Sch-Mt5 - OBJECTt722101613t722101613t22615105t0t55t
157911tLock:Acquiredtobject_id = 722101613, index_id or stats_id = 2t2 - Sch-Mt11 - METADATAt0t0t22615105t0t55t
157912tLock:Acquiredtobject_id = 722101613, index_id or stats_id = 2t2 - Sch-Mt11 - METADATAt0t0t22615105t0t55t
157913tLock:Acquiredtt2 - Sch-Mt12 - HOBTt0t3674945707562958848t22615105t0t55t
tSQL:StmtCompletedtcreate index IX_t__v on dbo.t(v) with (drop_existing = on, online = on);ttttt22615105tt55t
157916tLock:Releasedtt2 - Sch-Mt12 - HOBTt0t3674945707562958848t22615105tt55t
157917tLock:Releasedt[INDEX_OPERATION]t2 - Sch-Mt5 - OBJECTt722101613t722101613t22615105tt55t
157918tLock:Releasedtobject_id = 722101613, index_id or stats_id = 2t2 - Sch-Mt11 - METADATAt0t0t22615105tt55t
157919tLock:Releasedtt2 - Sch-Mt5 - OBJECTt722101613t722101613t22615105tt55t

это не тот Sch-M, к-ый в конце накладывается,
этот висит весь ребилд и он нужен для предотвращения одновременного другого онлайнового ребилда,
у этого resource_subtype = INDEX_OPERATION:
"it's holding a special lock, it's holding a Schema-Modification lock on what's called an index sub-resource lock,
and an index sub-resource lock this is a special kind of lock
that that prevents more than one online index operation from happening at the same type
" -- все из того же Рэндаловского видео
5 мар 15, 22:59    [17349810]     Ответить | Цитировать Сообщить модератору
 Re: прошу совета по реорганизации данных в частности по переносу индексов  [new]
invm
Member

Откуда: Москва
Сообщений: 9719
o-o,

Спасибо.
6 мар 15, 00:04    [17349970]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить