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

Откуда: Подмосковье
Сообщений: 292
Посмотрел, в процессе создания кластерного индекса (после его удаления) инструкцией

ALTER TABLE [dbo.TableName] ADD CONSTRAINT [PK] PRIMARY KEY CLUSTERED (...) ... 


выполняется такая команда

insert [dbo].[TableName] select * from [dbo].[TableName] option (maxdop 1)


Можно ли как-то вмешаться в её maxdop, увеличив его с 1 до ... , если обстановка на сервере позволяет?
30 ноя 18, 13:47    [21749940]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 35825
ALTER TABLE [dbo.TableName] ADD CONSTRAINT [PK] PRIMARY KEY CLUSTERED (...) ... with ( maxdop = N )
30 ноя 18, 13:50    [21749945]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
AR®
Member

Откуда: Подмосковье
Сообщений: 292
Не помогло.
Полный список опция таков:
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, maxdop = 70)


Странно: подчёркивает "maxdop = 70" красным как ошибочное, но выполняет и... не распараллеливает.
30 ноя 18, 14:02    [21749965]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
AR®
Member

Откуда: Подмосковье
Сообщений: 292
То есть, при выполнении осталось как и было:
insert [dbo].[TableName] select * from [dbo].[TableName] option (maxdop 1)
30 ноя 18, 14:04    [21749969]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 5951
AR®,

как maxdop даст улучшение скорости при вставке?
30 ноя 18, 14:05    [21749973]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
Владислав Колосов
Member

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

разве там нужен знак равенства?
30 ноя 18, 14:06    [21749975]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
Гавриленко Сергей Алексеевич
Member

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

разве там нужен знак равенства?
Может и не нужен.

Владислав Колосов
как maxdop даст улучшение скорости при вставке?
Создание индекса -- это не только вставка, но еще и чтение с сортировкой.
30 ноя 18, 14:08    [21749982]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
Yasha123
Member

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

как maxdop даст улучшение скорости при вставке?

хинт на всю инструкцию, а селект параллелится.
и никакой знак равенства в хинте, разумеется, не нужен
30 ноя 18, 14:10    [21749985]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
AR®
Member

Откуда: Подмосковье
Сообщений: 292
В select в option (maxdop 1) знак "=" действительно не используется.
А во with без него ошибка, а с ним работает, но - без распараллеливания.
30 ноя 18, 14:14    [21750000]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
Владислав Колосов
Member

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

да, распараллелится, но потом застрянет в бутылочном горлышке. Профита ноль целых, ноль десятых.
30 ноя 18, 14:16    [21750003]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
AR®
Member

Откуда: Подмосковье
Сообщений: 292
Владислав Колосов
Профита ноль целых, ноль десятых.


Известны ли Вам другие средства?
30 ноя 18, 14:19    [21750012]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
Гавриленко Сергей Алексеевич
Member

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

да, распараллелится, но потом застрянет в бутылочном горлышке. Профита ноль целых, ноль десятых.
Откуда вы знаете? Может он на стопятьст SSD-дисков индекс льет.
30 ноя 18, 14:19    [21750014]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
архивариус
Member

Откуда:
Сообщений: 121
AR®
Не помогло.
Странно: подчёркивает "maxdop = 70" красным как ошибочное, но выполняет и... не распараллеливает.

а цифра 70 для maxdop только меня удивила?
30 ноя 18, 15:08    [21750115]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
msLex
Member

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

как maxdop даст улучшение скорости при вставке?

Так, что дисковая подсистема вполне может работать быстрее чем все прочие операции в один поток.
И для этого совсем не обязательно иметь огромные полки c SSD.
30 ноя 18, 15:16    [21750128]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
Massa52
Member

Откуда:
Сообщений: 319
архивариус,
Нет не только, сервер тоже обалдел и отказался параллелить :)
30 ноя 18, 15:17    [21750132]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
AR®
Member

Откуда: Подмосковье
Сообщений: 292
архивариус
а цифра 70 для maxdop только меня удивила?


А что удивительного? Написал же сразу, что обстановка позволяет.
Это пока сервер в монопольном распоряжении для проведения работ.
При обычном повседневном использовании maxdop 70 - это, конечно, некоторый избыток оптимизма.
30 ноя 18, 15:27    [21750158]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5671
AR®
архивариус
а цифра 70 для maxdop только меня удивила?


А что удивительного? Написал же сразу, что обстановка позволяет.
Это пока сервер в монопольном распоряжении для проведения работ.
При обычном повседневном использовании maxdop 70 - это, конечно, некоторый избыток оптимизма.

богатые люди...
30 ноя 18, 15:29    [21750166]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 27408
AR®
архивариус
а цифра 70 для maxdop только меня удивила?


А что удивительного? Написал же сразу, что обстановка позволяет.
Это пока сервер в монопольном распоряжении для проведения работ.
При обычном повседневном использовании maxdop 70 - это, конечно, некоторый избыток оптимизма.
70 ядер для перестроения индекса - это сильно. Сколько же там всего? Хотя сейчас у одного сокета бывает по 32 ядра, 64 потока...
И система хранения должна соответствовать, а то не имеет смысла.
30 ноя 18, 15:31    [21750173]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 27408
TaPaK
AR®
пропущено...


А что удивительного? Написал же сразу, что обстановка позволяет.
Это пока сервер в монопольном распоряжении для проведения работ.
При обычном повседневном использовании maxdop 70 - это, конечно, некоторый избыток оптимизма.

богатые люди...
Угу, 70 ядер да по $20 000 на ядро лицензия на сиквел... Так можно и DBA 5 тыр накинуть... :-)
30 ноя 18, 15:33    [21750179]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 27408
alexeyvg
Угу, 70 ядер да по $20 000 на ядро лицензия на сиквел...
Ой, нет, по 5К, перепутал с сокетом
30 ноя 18, 15:33    [21750182]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
AR®
Member

Откуда: Подмосковье
Сообщений: 292
alexeyvg
70 ядер для перестроения индекса - это сильно


Жаль только, что задействовать их не получается.
А без распараллеливания - не менее 10 часов работы, поэтому и задумался...
30 ноя 18, 15:36    [21750187]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 35825
В документации написано, что для параллельного создания индекса надо указывать значение от 2 до 64.

И еще написано, что "Parallel index operations are not available in every SQL Server edition. For more information, see Features Supported by the Editions of SQL Server 2016"
30 ноя 18, 15:43    [21750202]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
архивариус
Member

Откуда:
Сообщений: 121
AR®
архивариус
а цифра 70 для maxdop только меня удивила?

А что удивительного? Написал же сразу, что обстановка позволяет.
Это пока сервер в монопольном распоряжении для проведения работ.
При обычном повседневном использовании maxdop 70 - это, конечно, некоторый избыток оптимизма.

Меня терзают смутные сомнения что лучше все же начинать с числа равного количеству физических ядер в одном сокете (или numa узле), а таких интел еще не завозил?
30 ноя 18, 15:47    [21750211]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
архивариус
Member

Откуда:
Сообщений: 121
maxdop 0 еще попробуйте раз тестите, узнаем сколько SQL сам решит выделить ядер.
30 ноя 18, 15:54    [21750239]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5671
архивариус
maxdop 0 еще попробуйте раз тестите, узнаем сколько SQL сам решит выделить ядер.

Сервер сам(по усолчанию) не хочит параллелить индексацию, при этом сразу получаете фрагментацию индекса.
30 ноя 18, 15:56    [21750242]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
AR®
Member

Откуда: Подмосковье
Сообщений: 292
Любое значение maxdop=... игнорируется, включая = 0.
У нас 2008R2 - он ещё не знает этого?
30 ноя 18, 16:05    [21750274]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5671
AR®
Любое значение maxdop=... игнорируется, включая = 0.
У нас 2008R2 - он ещё не знает этого?

смотря какой
https://msdn.microsoft.com/it-it/library/cc645993(v=sql.105).aspx
30 ноя 18, 16:09    [21750283]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
AR®
Member

Откуда: Подмосковье
Сообщений: 292
Похоже, у нас Standard, а у него Parallel index operations = "".
Жаль.
30 ноя 18, 16:25    [21750323]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5671
AR®
Похоже, у нас Standard

вы не уверны?
30 ноя 18, 16:26    [21750326]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
AR®
Member

Откуда: Подмосковье
Сообщений: 292
Не уверен.
30 ноя 18, 16:28    [21750336]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5671
AR®
Не уверен.

как вы к серверу вообще пробраись?

SELECT @@VERSION
30 ноя 18, 16:29    [21750339]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
AR®
Member

Откуда: Подмосковье
Сообщений: 292
Standard Edition (64-bit), я почему-то был уверен, что Enterprise (возможно он у нас на другой машине).
30 ноя 18, 16:32    [21750348]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5671
AR®
Standard Edition (64-bit), я почему-то был уверен, что Enterprise (возможно он у нас на другой машине).

и ядер сразу не 70 становится :)
30 ноя 18, 16:34    [21750352]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
AR®
Member

Откуда: Подмосковье
Сообщений: 292
Вообще-то регламентируется Number of CPUs, а про ядра ничего не сказано.
Процессоров как раз 4, по 20 Logical на каждого...
30 ноя 18, 16:39    [21750375]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
AR®
Member

Откуда: Подмосковье
Сообщений: 292
Ещё, раз уж заговорили, интересует, что в

insert [dbo].[TableName] select *, %%bmk%% from [dbo].[TableName] option (maxdop 1)


означает %%bmk%% ?
30 ноя 18, 16:42    [21750383]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5671
AR®
Вообще-то регламентируется Number of CPUs, а про ядра ничего не сказано.
Процессоров как раз 4, по 20 Logical на каждого...

Поодозреваю, что устаревшее описание Limited to lesser of 4 sockets or 24 cores.
30 ноя 18, 16:43    [21750388]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
Yasha123
Member

Откуда:
Сообщений: 1114
AR®
Вообще-то регламентируется Number of CPUs, а про ядра ничего не сказано.

really?

К сообщению приложен файл. Размер - 11Kb
30 ноя 18, 16:44    [21750392]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
Yasha123
Member

Откуда:
Сообщений: 1114
в еррорлоге посмотрите, сколько он реально видит
30 ноя 18, 16:49    [21750407]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
AR®
Member

Откуда: Подмосковье
Сообщений: 292
В логе windows или SQL-server?
30 ноя 18, 16:53    [21750418]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
Yasha123
Member

Откуда:
Сообщений: 1114
сервера.
exec xp_readerrorlog 0,1,N'cores';


К сообщению приложен файл. Размер - 9Kb
30 ноя 18, 17:00    [21750431]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 5951
Все равно не верю, что писать в один поток сервер будет не хуже, чем читать в 10 в одну и ту же файловую группу, например. Хоть SSD хоть RAM Disk. Запись ну никак не может быть быстрее чтения даже один поток к одному.
30 ноя 18, 17:41    [21750512]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 35825
Владислав Колосов
Все равно не верю, что писать в один поток сервер будет не хуже, чем читать в 10 в одну и ту же файловую группу, например. Хоть SSD хоть RAM Disk. Запись ну никак не может быть быстрее чтения даже один поток к одному.
Тот факт, что при создании индекса надо не только читать и писать данные, но еще и сортировать, вы сознательно игнорируете?
30 ноя 18, 18:04    [21750568]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
msLex
Member

Откуда:
Сообщений: 5676
Гавриленко Сергей Алексеевич
Владислав Колосов
Все равно не верю, что писать в один поток сервер будет не хуже, чем читать в 10 в одну и ту же файловую группу, например. Хоть SSD хоть RAM Disk. Запись ну никак не может быть быстрее чтения даже один поток к одному.
Тот факт, что при создании индекса надо не только читать и писать данные, но еще и сортировать, вы сознательно игнорируете?



А еще игнорируется тот основополагающий факт, что сразу на диск пишется только лог, а данные меняются в памяти, и только потом lazywrite-ом сбрасываются на диск
30 ноя 18, 19:27    [21750696]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 886
msLex
Гавриленко Сергей Алексеевич
пропущено...
Тот факт, что при создании индекса надо не только читать и писать данные, но еще и сортировать, вы сознательно игнорируете?



А еще игнорируется тот основополагающий факт, что сразу на диск пишется только лог, а данные меняются в памяти, и только потом lazywrite-ом сбрасываются на диск


не совсем так, лог тоже на диск сразу не пишется.

это при том если не рассматривать еще delayed durablity
30 ноя 18, 21:46    [21750794]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 35825
felix_ff
не совсем так, лог тоже на диск сразу не пишется.
Ну давайте еще там про кеш на контроллере вспомним, ага.

Максимальная очередь на запись лога - не более 112 реквестов на базу (начиная с 2012го), так что можно считать, что сразу. (Сколько по факту, каждый может помониторить сам через sys.dm_io_pending_io_requests)
30 ноя 18, 22:59    [21750819]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
AR®
Member

Откуда: Подмосковье
Сообщений: 292
Yasha123
сервера.
exec xp_readerrorlog 0,1,N'cores';



(0 row(s) affected)

Т.е. работает 2008R2 Standard на 4 процах = 80 логических процах.
1 дек 18, 15:26    [21751028]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
AR®
Member

Откуда: Подмосковье
Сообщений: 292
Другой вопрос возник неожиданно.

ALTER TABLE [dbo].[TableName] ADD  CONSTRAINT [I_IDX] PRIMARY KEY CLUSTERED (...)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
ON [NewFileGroup]


работало минут 10 и упало с:
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.
Msg 4902, Level 16, State 1, Line 1
Cannot find the object "dbo.TableName" because it does not exist or you do not have permissions.

И в SSMS текущая база стала master (в которой действительно нет и не было таблицы TableName).

Если создавать заново кластерный индекс на группе PRIMARY, то он создаётся.
Другие таблицы благополучно перенеслись на [NewFileGroup].

Чем может отличаться проблемная таблица?
1 дек 18, 15:36    [21751031]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 35825
Ну, dbcc checktable сделайте, если явно не понятно, в чем проблема.
1 дек 18, 16:06    [21751041]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить пересоздание кластерного индекса  [new]
AR®
Member

Откуда: Подмосковье
Сообщений: 292
Так делал, никаких патологий.
Как и при обычном повседневном использовании этой таблицы.
Не получается перенести её в другую файловую группу.
Повторюсь, если снести кластерный индекс и пересоздать его на PRIMARY, всё получается за разумное время.
1 дек 18, 16:12    [21751044]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Microsoft SQL Server Ответить