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

Откуда: Сидней
Сообщений: 1166
 		Добрый день,

Унаследовал warehouse сервер. Там эта таблица загружается простым:
select * into MyDB2.MyTable from MyDB1.dbo.MyTable

Старый ДБА сказал, что это лучший вариант, ибо это минимально логируемая операция. Я читал, что это верно только до версии 2008, у нас 2014:
http://stackoverflow.com/questions/8560619/sql-server-select-into-versus-insert-into-select

Проблема в том, что иногда время загрузки таблицы возрастает раз в 10 по сравнению с нормальным. Это случается раз в одну-две недели.

Не совсем понятно, если дело в размере, то почему это случается не каждый раз, а раз в одну-две недели?

Я думаю, что такая загрузка убивает tempdb и раздувает лог файл, даже при минимальном логировании.

Устанавливать SSIS на сервер не очень хочется.

Я думаю, разбить загрузку на батчи рамером в 10% от таблицы:

автор
DECLARE @StartID bigint, @BatchSize bigint, @EndID bigint
select @StartID = MIN([MyTableID]) from MyDB1.dbo.MyTable
select @BatchSize = 200000000 -- start with the batch size approx. equal to 10% of the table row count
select @EndID = @StartID + @BatchSize

select top 1 * from MyDB1.dbo.MyTable
while @@rowcount > 0
begin
insert into MyDB2.MyTable select * from MyDB1.dbo.MyTable where MyTableID between @StartID and @EndID

set @StartID = @EndID + 1
select @EndID = @StartID + @BatchSize
end

Чтобы снизить нагрузку на tempdb и уменьшить размер транзакции.

Хочу посоветоваться с форумом на тему того, насколько разумны мои предположения.

Спасибо.
24 апр 17, 04:03    [20427606]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
Massa52
Member

Откуда:
Сообщений: 382
 		Roust_m,
Цикл вроде бесконечный:
Значениe 
@@rowcount
завист от 
select @EndID = @StartID + @BatchSize
24 апр 17, 05:36    [20427613]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
aleksrov
Member

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

Старый ДБА сказал, что это лучший вариант, ибо это минимально логируемая операция. Я читал, что это верно только до версии 2008, у нас 2014:
http://stackoverflow.com/questions/8560619/sql-server-select-into-versus-insert-into-select

И где вы там такое вычитали? Читайте внимательнее.

The first one (SELECT INTO) will create and populate a new table the second (INSERT... SELECT) inserts to an existing table.
In versions of SQL Server prior to 2008 the first one could be minimally logged and the second one not but this is no longer true.
Т.е. после 2008 обе могут быть мин. лог.
https://msdn.microsoft.com/en-us/library/ms191244.aspx
The amount of logging for SELECT...INTO depends on the recovery model in effect for the database. Under the simple recovery model or bulk-logged recovery model, bulk operations are minimally logged. With minimal logging, using the SELECT… INTO statement can be more efficient than creating a table and then populating the table with an INSERT statement

Загрузка не может просто так возрасти в 10 раз. Смотрите что происходит на сервере, какие блокировки, чего запрос ждет.
24 апр 17, 06:05    [20427616]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1166
Massa52
Roust_m,
Цикл вроде бесконечный:
Значениe 
@@rowcount
завист от 
select @EndID = @StartID + @BatchSize


Почему же бесконечный? Как только "insert into MyDB2.MyTable select * from MyDB1.dbo.MyTable where MyTableID between @StartID and @EndID" не найдет новый записей, то @@rowcount будет нулем.

Например:
select * from MyDB1.dbo.MyTable where 1=2
select @@rowcount
------------------------------------------
0
24 апр 17, 07:29    [20427638]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
Roust_m
Member

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

Старый ДБА сказал, что это лучший вариант, ибо это минимально логируемая операция. Я читал, что это верно только до версии 2008, у нас 2014:
http://stackoverflow.com/questions/8560619/sql-server-select-into-versus-insert-into-select

И где вы там такое вычитали? Читайте внимательнее.

The first one (SELECT INTO) will create and populate a new table the second (INSERT... SELECT) inserts to an existing table.
In versions of SQL Server prior to 2008 the first one could be minimally logged and the second one not but this is no longer true.
Т.е. после 2008 обе могут быть мин. лог.
https://msdn.microsoft.com/en-us/library/ms191244.aspx
The amount of logging for SELECT...INTO depends on the recovery model in effect for the database. Under the simple recovery model or bulk-logged recovery model, bulk operations are minimally logged. With minimal logging, using the SELECT… INTO statement can be more efficient than creating a table and then populating the table with an INSERT statement

Загрузка не может просто так возрасти в 10 раз. Смотрите что происходит на сервере, какие блокировки, чего запрос ждет.


Блокировок в момент проблемы не наблюдается (со слов старого ДБА). Чего он ждет посмотрю в следующий раз когда проблема возникнет.

Мне все-таки кажется, что транзакция слишком большая, табличка все-таки 1.8TB, вот и думаю разбить ее немного. Наверное придется ставить SSIS. Они под загрузку больших таблиц заточены хорошо.
24 апр 17, 07:33    [20427642]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34723
Блог
 		чтобы быстро решить проблему - ставьте SSIS и читайте https://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx
но каждый раз копировать почти 2Тб - это не нормально, что-то в архитектуре системы не так
24 апр 17, 07:41    [20427644]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1166
 		Опять же со слов старого ДБА, 90% ожиданий на сервере в это время LATCH_EX
24 апр 17, 07:52    [20427650]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1166
Критик
чтобы быстро решить проблему - ставьте SSIS и читайте https://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx
но каждый раз копировать почти 2Тб - это не нормально, что-то в архитектуре системы не так


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

Возможно придется ставить SSIS, хотя текущее решение, тоже вроде как минимально логируемое, значит SSIS делает что-то дополнительное, по сравнению с тупым "select * into...".
24 апр 17, 08:02    [20427660]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
aleks2
Guest
Roust_m
Мне все-таки кажется, что транзакция слишком большая, табличка все-таки 1.8TB, вот и думаю разбить ее немного. Наверное придется ставить SSIS. Они под загрузку больших таблиц заточены хорошо.


Наивный чукотский вьюноша.

Roust_m
Я думаю, что такая загрузка убивает tempdb и раздувает лог файл, даже при минимальном логировании.


Учись, студент.
24 апр 17, 08:14    [20427667]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34723
Блог
Roust_m
значит SSIS делает что-то дополнительное, по сравнению с тупым "select * into...".


там можно сделать много поточную загрузку
24 апр 17, 08:15    [20427669]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
o-o
Guest 		если заливать частями через insert..select,
то надо с tablock, тогда будет минимально логироваться
-----
про "возрастание времени загрузки непонятно отчего":
бэкапа нет ли в это время?
во время бэкапа все операции логируются полностью
24 апр 17, 08:17    [20427673]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
Massa52
Member

Откуда:
Сообщений: 382
Roust_m
Почему же бесконечный? Как только "insert into MyDB2.MyTable select * from MyDB1.dbo.MyTable where MyTableID between @StartID and @EndID" не найдет новый записей, то @@rowcount будет нулем.
0

А разве 
select @EndID = @StartID + @BatchSize
не выставит @@rowcount = 1
24 апр 17, 08:44    [20427696]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
o-o
Guest 		цикл конечно бесконечный.
условие ненахождения записей это @StartID > 2mlrd,
а не @@rowcount = 0,
@@rowcount от каждого присваивания (set/select) меняется
24 апр 17, 09:05    [20427718]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1166
aleks2
Roust_m
Мне все-таки кажется, что транзакция слишком большая, табличка все-таки 1.8TB, вот и думаю разбить ее немного. Наверное придется ставить SSIS. Они под загрузку больших таблиц заточены хорошо.


Наивный чукотский вьюноша.

Roust_m
Я думаю, что такая загрузка убивает tempdb и раздувает лог файл, даже при минимальном логировании.


Учись, студент.


Я не спорю, учиться никогда не поздно.

А по теме можешь что-то сказать?
24 апр 17, 09:27    [20427749]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
o-o
Guest
Roust_m
Я думаю, что такая загрузка убивает tempdb и раздувает лог файл, даже при минимальном логировании.
...
А по теме можешь что-то сказать?

а по теме: ну какое отношение к вашей загрузке имеет темпдб?
лог разумеется используется, но это лог самой базы.
на все время транзакции лог не может быть усечен,
а значит, если в него валят еще и другие транзакции, размер лога должен быть таким,
чтобы там уместилось все наваленное и другими тоже за все время загрузки.
и если размер недостаточный (например, кто-то периодически шринкает лог),
лог будет расти и зануляться -- а все писатели будут ждать
24 апр 17, 09:41    [20427765]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
aleks2
Guest
Roust_m
aleks2
пропущено...


Наивный чукотский вьюноша.

пропущено...


Учись, студент.


Я не спорю, учиться никогда не поздно.

А по теме можешь что-то сказать?


Канешно. Оба тезиса неверны.
Глупы и не соответствуют действительности.
24 апр 17, 10:13    [20427904]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31779
Roust_m
Я думаю, что такая загрузка убивает tempdb и раздувает лог файл, даже при минимальном логировании.
Нет.
Roust_m
Я думаю, разбить загрузку на батчи рамером в 10% от таблицы:
А вот так потребуется писать через лог.
Roust_m
Проблема в том, что иногда время загрузки таблицы возрастает раз в 10 по сравнению с нормальным. Это случается раз в одну-две недели.
Либо кто то лочит источник, либо выполняются какие то нагружающие сервер действия, типа бакапа.
Roust_m
Почему же бесконечный? Как только "insert into MyDB2.MyTable select * from MyDB1.dbo.MyTable where MyTableID between @StartID and @EndID" не найдет новый записей, то @@rowcount будет нулем.
@@rowcount всегда будет равен 1, потому что select @EndID = @StartID + @BatchSize всегда обрабатывает одну запись.
24 апр 17, 10:31    [20427982]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1166
o-o
Roust_m
Я думаю, что такая загрузка убивает tempdb и раздувает лог файл, даже при минимальном логировании.
...
А по теме можешь что-то сказать?

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


Ну если в транзакции есть select, то tempdb используется, не?

Кстати, если таблица почти 2ТБ и используется минимально логируемая транзакция, то примерно на сколько лог раздуется?
24 апр 17, 10:45    [20428042]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1166
alexeyvg
@@rowcount всегда будет равен 1, потому что select @EndID = @StartID + @BatchSize всегда обрабатывает одну запись.


Пардон, в самом деле, не заметил. Пока я это не тестировал, но это можно переписать чтобы цикл был не бесконечным. Вопрос только в том, поможет ли.
24 апр 17, 10:48    [20428058]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1166
alexeyvg
Либо кто то лочит источник, либо выполняются какие то нагружающие сервер действия, типа бакапа.

Про бекап версию проверю. Я так понимаю, нужно проверить был ли в это время бекап базы, куда вставляются эти данные?

Если бы кто-то лочил источник, то были бы видны блокировки. А их не видно.
24 апр 17, 10:54    [20428086]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1166
alexeyvg
Roust_m
Я думаю, разбить загрузку на батчи рамером в 10% от таблицы:
А вот так потребуется писать через лог.


А согласно ссылке выше в версии после 2008-го (у нас 2014-й) insert into тоже может быть минимально логированной.

Товарищ о-о, тоже это утверждает. А можно ли это протестировать?
24 апр 17, 10:58    [20428099]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31779
Roust_m
Если бы кто-то лочил источник, то были бы видны блокировки. А их не видно.
А у вас отслеживаются блокировки???
Ок, это просто предположение.
Roust_m
Я так понимаю, нужно проверить был ли в это время бекап базы, куда вставляются эти данные?
Вообще нужно проверить любые действия, нагружающие сервер, в то время, когда делался перенос таблицы.
Roust_m
Вопрос только в том, поможет ли.
Операция select into всегда с минимальным логированием, операция insert select может быть тоже с минимальным логированием, но конкретно у вас - нет.
24 апр 17, 11:00    [20428106]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1166
alexeyvg
Вообще нужно проверить любые действия, нагружающие сервер, в то время, когда делался перенос таблицы.

Ну одно дело нагружающие, другое дело превращающие операцию из минимально логируемой в полностью логируемую.

ИМХО, в первом случае увеличение длительности тразакции в 10 раз маловероятно, а во втором вполне.
24 апр 17, 11:11    [20428151]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
aleks2
Guest
Roust_m
Кстати, если таблица почти 2ТБ и используется минимально логируемая транзакция, то примерно на сколько лог раздуется?


Примерно на 0 (ноль).

ЗЫ. Откуда тока вылазят "спецуалисты с отсутствием мозга"?

Минимально логгируемые операции НЕ используют журнал.
От слова "совсем". Ну т.е. какая-то хрень, чисто символическая, туды пишется.
НО! Все остальное пишется прямо в базу на НОВЫЕ (вновь выделяемые целиком и заполняемые данными в рамках только этой операции) страницы.
Осознай это и те полегчает.

Когда данные пишутся на НОВЫЕ страницы - нема никакой необходимости дублировать их в журнал.
Достаточно записать список этих страниц.
Вот так, незатейливо, устроены "минимально логгируемые операции".
24 апр 17, 11:52    [20428347]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31779
Roust_m
alexeyvg
Вообще нужно проверить любые действия, нагружающие сервер, в то время, когда делался перенос таблицы.

Ну одно дело нагружающие, другое дело превращающие операцию из минимально логируемой в полностью логируемую.
Да, это 2 разных непересекающихся варианта.

Roust_m
ИМХО, в первом случае увеличение длительности тразакции в 10 раз маловероятно, а во втором вполне.
Да по всякому может быть. Хотя да, многовато в 10 раз.

Но это же просто проверяется, чего гадать то???
24 апр 17, 12:36    [20428563]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl→      все
Все форумы / Microsoft SQL Server Ответить