Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 SQL2016 & ALTER TABLE ADD  [new]
Mr. X
Guest
Всем, доброго дня.
Похоже есть проблема.
На SQL2016Dev (13.0.4411.0) запуск команды добавления столбца/ов (ALTER TABLE ... ADD ... NULL) на таблице в 100GB (как на обычной, так и на секционированной) проходит в оффлайне: команда накладывает необходимые блокировки на все время своего выполнения (на такой таблице это ~30min). На др версиях (2008R2, 2012 & 2014) выполняется ожидаемо, быстро. На коннекте по этому поводу пусто - не нашел. Комментарии...
15 фев 17, 12:47    [20213629]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37224
Все дело в ваших "...".
15 фев 17, 12:54    [20213672]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
Mr. X
Guest
Гавриленко Сергей Алексеевич,

Мои ... просты ([dbo].[Table] естественно существует)
ALTER TABLE [dbo].[Table]
ADD Data1 int NULL, Data2 bigint NULL, Data3 datetime NULL, Data4 date NULL, Data5 time NULL, Data6 bit NULL, Data7 smallint NULL, Data8 real NULL, Data9 float NULL, Data10 varchar(100) NULL
GO

PS: кол-во столбцов роли не играет - хоть 1, хоть 10; На др версиях (2008R2, 2012 & 2014) этот скрипт выполняется ожидаемо, быстро.
15 фев 17, 13:04    [20213750]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Mr. X,

уберите NULL будет быстро.
15 фев 17, 13:14    [20213804]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
iap
Member

Откуда: Москва
Сообщений: 47083
TaPaK
Mr. X,

уберите NULL будет быстро.
Если по-умолчанию поля NOT NULL, то без DEFAULTа вообще будет ошибка,
а с DEFAULTом - заполнение новых полей значениями по умолчанию как раз и займёт уйму времени.
Ибо ALTER затронет уже не только метаданные, как можно было надеяться в случае с полями NULL.
15 фев 17, 13:20    [20213847]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
invm
Member

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

Не смотрели на каком ресурсе остальные ждут?
15 фев 17, 13:22    [20213858]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
TaPaK
Member

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

а я о чём? для его случая убрать NULL и будет быстро :) или там где-то мелко not написано???
15 фев 17, 13:22    [20213862]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
Mr. X
Guest
invm,

Не понял вашего вопроса. Какие ресурсы блокируются при выполнении команды?
15 фев 17, 13:25    [20213887]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
iljy
Member

Откуда:
Сообщений: 8711
Mr. X,

DDL таблицы покажите. Любая модификация структуры может затрагивать страницы данных (а может и не затрагивать, зависит от), и на 100Г быстро это точно не будет.
15 фев 17, 13:26    [20213891]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
invm
Member

Откуда: Москва
Сообщений: 9719
Mr. X
Не понял вашего вопроса. Какие ресурсы блокируются при выполнении команды?
Да много их может быть.
Смотрите в sys.dm_waiting_tasks на чем ожидают блокируемые.
15 фев 17, 13:32    [20213933]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
invm
Member

Откуда: Москва
Сообщений: 9719
sys.dm_os_waiting_tasks
15 фев 17, 13:35    [20213945]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
Дед-Папыхтет
Member [заблокирован]

Откуда:
Сообщений: 2808
iap
TaPaK
Mr. X,

уберите NULL будет быстро.
Если по-умолчанию поля NOT NULL, то без DEFAULTа вообще будет ошибка,
а с DEFAULTом - заполнение новых полей значениями по умолчанию как раз и займёт уйму времени.
Ибо ALTER затронет уже не только метаданные, как можно было надеяться в случае с полями NULL.

Это было в далеком 2008м SQL Server. Не знаю как на 2012, но на 2014 уже моментально
15 фев 17, 14:01    [20214085]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
Mr. X
Guest
invm,

Результаты:
первый вывод для sys.dm_os_waiting_tasks (не все сессии (у остальных id < 20), параллелизм включен)
второй вывод для
SELECT
[Resource].resource_type, [Resource].request_mode, [Resource].request_type,
[Resource].request_status, [Resource].request_session_id,
[Objects].[Table], [Objects].[Index], [Objects].[Partition], [Objects].[Rows]
FROM
(
SELECT
resource_type, resource_associated_entity_id, request_mode, request_status, request_session_id, request_type
FROM sys.dm_tran_locks
GROUP BY resource_type, resource_associated_entity_id, request_mode, request_status, request_session_id, request_type
) [Resource]
INNER JOIN
(
SELECT
object_name(Parts.object_id) [Table], Indexs.name [Index], Parts.partition_number [Partition], Parts.rows [Rows], Parts.hobt_id
FROM sys.partitions Parts
INNER JOIN sys.indexes Indexs
ON Parts.object_id = Indexs.object_id AND Parts.index_id = Indexs.index_id
) [Objects]
ON [Objects].hobt_id = [Resource].resource_associated_entity_id
ORDER BY [Resource].request_session_id
GO
15 фев 17, 15:26    [20214459]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
Mr. X
Guest
Файлик

К сообщению приложен файл. Размер - 88Kb
15 фев 17, 15:43    [20214540]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
Mr. X
Guest
Mr. X,

Это если в один поток для sys.dm_os_waiting_tasks (не все сессии (у остальных id < 20), параллелизм выключен)

ЗЫ: на другом сервере на секционированной таблице в 300GB на SQL2014Dev добавление 3-х столбцов прошло за 5 сек

К сообщению приложен файл. Размер - 41Kb
15 фев 17, 15:58    [20214599]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
Mr. X
Guest
Mr. X,

Скрины выше для секционированной таблицы.
Есть явный запрет только на ALTER COLUMN для секционированной таблицы:
"The data type of a column of a partitioned table cannot be changed."
https://msdn.microsoft.com/en-us/library/ms190273.aspx
Про ALTER TABLE ... ADD ... NULL каких-то ограничений не нашел.
15 фев 17, 16:06    [20214627]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
invm
Member

Откуда: Москва
Сообщений: 9719
Картинки свидетельствуют, что
а) параллелизм таки включен.
б) нет сессий, блокированных другими.
в) есть ожидание на физическое чтение с диска
15 фев 17, 16:17    [20214673]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
Mr. X
Guest
invm,

Тут 20214540 параллелизм действительно включен и есть взаимоблокировки

а тут выключен 20214599

в обоих случаях не блокированный воркер висит в PAGEIOLATCH_SH

Дело не в параллелизме, хотя ранее полагал что, если при распараллеливании есть взаимоблокировки, то делу поможет его отключение. Привел оба варианта для информации.

"есть ожидание на физическое чтение с диска" - в том то и дело, что вместо того, что бы изменить только метаданные, команда меняет данные в строках (предположение).

Скрипты...
15 фев 17, 17:37    [20215055]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8570
Mr. X,

у Вас точно по умолчанию добавление столбца настроено с NULL для всех баз одинаково?
Уберите NULL в запросе и попробуйте там и там.
15 фев 17, 18:08    [20215134]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
Mr. X
Guest
Владислав Колосов,

"Уберите NULL в запросе" - убрал на SQL2016Dev (13.0.4411.0):
ALTER TABLE [dbo].[mrrec]
ADD data1 datetime, data2 bigint, data3 varchar(150)
GO
Поведение не изменилось, те такое как в 20214540.
За одним исключением: теперь ждем WRITELOG
15 фев 17, 18:33    [20215199]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
Mr. X
Guest
Владислав Колосов,

"у Вас точно по умолчанию добавление столбца настроено с NULL для всех баз одинаково?" - есть какая-то настройка перекрывающая указание в скрипте?
15 фев 17, 18:35    [20215206]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
invm
Member

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

В принципе, все что происходило, может выяснить проанализировав результат из fn_dblog.
15 фев 17, 18:36    [20215210]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
Mr. X
Guest
invm,

Поднял рядом SQL2014Dev (12.0.5538.0).
Пролил на нем аналогичные метаданные - широкая табл, секционирование, статистика с STATISTICS_INCREMENTAL (на 2016 также).
Данные не заливал, табл пуста и ее секции соответственно тоже.
И... добавление столбцов проходит на ара!!!

Завтра залью такой же объем данных. Попробую добавление столбцов на заполненной таблице.

ЗЫ: интересная деталь
ALTER TABLE [dbo].[mrrec]
ADD data1 datetime, data2 bigint, data3 varchar(150)
GO
при выполнении этого скрипта в метаданных табл эти столбцы позволяют NULL.
15 фев 17, 19:10    [20215280]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
Mr. X
Guest
Mr. X,

И... добавление столбцов проходит на ара!!! - те на ура!!! :)
15 фев 17, 19:11    [20215287]     Ответить | Цитировать Сообщить модератору
 Re: SQL2016 & ALTER TABLE ADD  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Mr. X
Mr. X,

И... добавление столбцов проходит на ара!!! - те на ура!!! :)


автор
уберите NULL будет быстро.
вроде давно написал
15 фев 17, 20:08    [20215400]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить