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

Откуда: Сидней
Сообщений: 1197
alexeyvg
Roust_m
пропущено...

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

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

Но это же просто проверяется, чего гадать то???


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

Но я проверю и то и другое.
24 апр 17, 12:41    [20428589]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31959
Roust_m
Если бекап был на базе в которую идет запись, то вероятно запись стала полностью логируемой
Разве? С чего это запись будет полностью логироваться?
24 апр 17, 12:54    [20428702]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
o-o
Guest
alexeyvg
Roust_m
Если бекап был на базе в которую идет запись, то вероятно запись стала полностью логируемой
Разве? С чего это запись будет полностью логироваться?

это черным по белому написано в справке.
https://msdn.microsoft.com/en-us/library/ms191244(v=sql.90).aspx
BOL
Unless a backup is running, minimal logging is used under the simple recovery mode
24 апр 17, 14:21    [20429301]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
o-o
Guest
Roust_m
Я думаю, разбить загрузку на батчи рамером в 10% от таблицы:

alexeyvg
А вот так потребуется писать через лог.

что значит "а вот так"?
что такое "через лог"?
любое изменение данных будет отражено в логе,
хоть вот так, хоть не вот так.
если же речь о минимальном логировании,
то и select..insert может логироваться минимально,
если его выполнять с tablock.
что вообще-то уже было сказано
24 апр 17, 14:25    [20429332]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
o-o
Guest
Roust_m
Ну если в транзакции есть select, то tempdb используется, не?

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

Откуда: Moscow
Сообщений: 31959
o-o
это черным по белому написано в справке.
https://msdn.microsoft.com/en-us/library/ms191244(v=sql.90).aspx
Да, что то подзабыл, спасибо.
24 апр 17, 14:31    [20429365]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31959
o-o
что значит "а вот так"?
что такое "через лог"?
любое изменение данных будет отражено в логе,
"а вот так" - это значит выполнить тот код, который написан, "как есть".
"через лог" - значит, что все данные будут записаны в лог, и потом записаны в таблицу.
o-o
если же речь о минимальном логировании
Да, речь о нём.
o-o
то и select..insert может логироваться минимально,
если его выполнять с tablock.
А т.к. он не выполняет insert..select с tablock, то будет полное логирование.
Да, ещё трейс-флаг может быть, но он о нём тоже не знает :-)
24 апр 17, 14:36    [20429395]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
o-o
Guest
Roust_m
alexeyvg
пропущено...
А вот так потребуется писать через лог.


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

Товарищ о-о, тоже это утверждает. А можно ли это протестировать?

чего ж нельзя-то.

создаете тестовую базу с минилогом, дефолтовым, 1Мб.
создаете таблицу с интовым полем, куда инсертите с таблоком 1000000 значений, например так:
create table dbo.t(n int);

insert into dbo.t with (tablock)
select top 1000000 row_number() over (order by getdate()) as n
from sys.all_columns c1 cross join sys.all_columns c2 cross join sys.all_columns c3;

убеждаетесь, что лог не вырос,
а ведь таблица в 1000000 интов это 12,5 Мб,
было бы логирование полным, *как минимум* лог до этой цифры вырос бы.

ну и чтобы и это проверить, своей таблице и делаете все ту же вставку, уже без таблока:
truncate taable dbo.t;

insert into dbo.t 
select top 1000000 row_number() over (order by getdate()) as n
from sys.all_columns c1 cross join sys.all_columns c2 cross join sys.all_columns c3;

радуетесь выросшему логу и времени выполнения
24 апр 17, 14:49    [20429465]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
o-o
Guest
alexeyvg
Да, ещё трейс-флаг может быть, но он о нём тоже не знает :-)

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

Откуда: Москва / Калуга
Сообщений: 35382
Блог
tempdb может использоваться при вставке, если у вас приемник данных секционирован
24 апр 17, 21:53    [20431011]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
o-o
Guest
Критик
tempdb может использоваться при вставке, если у вас приемник данных секционирован

это замещение метаданных что ли в темпдб происходит при включении секции?
поделитесь ссылкой, плиз
24 апр 17, 22:01    [20431035]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
o-o
Guest
кстати, у него там перелив таблицы из одной базы в другую.
как бы это 2 разных файла.
не подскажете, как переключать секции, расположенные в разных файлах?
24 апр 17, 22:07    [20431058]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
o-o,

похоже, он имеет ввиду лить напрямую

insert into partitioned_table from source_table


там появится sort
24 апр 17, 22:42    [20431141]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
o-o
Guest
тогда вопрос, при чем тут этот топик.
в котором ТС из одной базы в другую льет (через select into), а тормоза списывает на темпдб!
объяснение у него простое: есть селект в транзакции -> используется темпдб.
не понимаю, зачем еще пришельцев(секционирование) за уши притягивать,
лишь бы все-таки свалить все на темпдб
24 апр 17, 22:46    [20431148]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
И что, за весь день никто так план запроса и не попросил?...
25 апр 17, 01:30    [20431292]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35382
Блог
o-o
Критик
tempdb может использоваться при вставке, если у вас приемник данных секционирован

это замещение метаданных что ли в темпдб происходит при включении секции?
поделитесь ссылкой, плиз


пересортировка происходит в tempdb, ссылки нет, но проверить очень просто
25 апр 17, 08:19    [20431480]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
o-o
Guest
Критик
o-o
пропущено...

это замещение метаданных что ли в темпдб происходит при включении секции?
поделитесь ссылкой, плиз


пересортировка происходит в tempdb, ссылки нет, но проверить очень просто

Секционирования тоже нет, как и участия темпдб.
Код для проверки приведен выше
25 апр 17, 08:26    [20431490]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
На целевой БД выставлено автоматическое обновлениеи статистики? Вдруг приходит пора ее пересчитать для целевой таблицы-ведь размер не детский.
25 апр 17, 12:59    [20432412]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
o-o
Guest
Для читающих со второй страницы:
ТС льет вот так:
select * into MyDB2.dbo.MyTable from MyDB1.dbo.MyTable
Поэтому секционированный приемник, темпдб, сортировка чего-либо, статистика приемника, - это не его случай
25 апр 17, 14:59    [20432957]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Все не читал... Помнится очень один процесс загрузки ускорили за счет параллельной заливки данных.
25 апр 17, 20:29    [20433805]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
Roust_m
Member

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

DECLARE @StartID bigint, @BatchSize bigint, @EndID bigint, @LastID bigint
select @StartID = MIN([ID]) from MyDB1.dbo.MyTable
select @LastID = max(ID) from MyDB1.dbo.MyTable
select @BatchSize = 100000 -- start with the batch size approx. equal to 10% of the table row count
select @EndID = @StartID + @BatchSize


while @StartID < @LastID
begin
	insert into MyDB2..MyTable  with (tablock) 
	select * from MyDB1.dbo.MyTable where ID between @StartID and @EndID
	set @StartID = @EndID + 1
	select @EndID = @StartID + @BatchSize
end
27 апр 17, 03:21    [20438257]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1197
Обнаружил, что в SQL Server 2014 "select * into" работает паралельно, поэтому если это заменить на "insert into ... with (tablock) select * ...", то несмотря на то, что транзация остается минимально логируемой, скорость выполнения падает в два раза (на моем примере).

Грабли, короче, не думал, что так сложно найти что-то лучше, чем "select * into".

Вот пример который я построил (мой вариант с батчами занял 1 минуту 7 секунд, "select * into" всего 33 секунды):
-- create source database
create database MyDB1
go
ALTER DATABASE MyDB1 SET RECOVERY BULK_LOGGED 
GO
USE [MyDB1]
GO

-- create source table
create table dbo.MyTable(id int not null, [name] varchar(30) default 'TestName');
GO
-- truncate table MyDB1..MyTable -- for testing
-- populate source table with 1Mln rows
insert into MyDB1.dbo.MyTable with (tablock)
select top 100000000 row_number() over (order by getdate()) as n, 'TestName'
from sys.all_columns c1 cross join sys.all_columns c2 cross join sys.all_columns c3;
GO
select COUNT(1) from MyDB1.dbo.MyTable with (nolock)

ALTER TABLE dbo.MyTable ADD PRIMARY KEY CLUSTERED 
(
	Id ASC
)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 [PRIMARY]
GO
-- just to make sure the log file is shrunk to the minimum
DBCC SHRINKFILE (N'MyDB1_log' , 1)
GO


-- create destination database
create database MyDB2
go
ALTER DATABASE MyDB2 SET RECOVERY BULK_LOGGED 
GO
USE [MyDB2]
GO

DBCC SHRINKFILE (N'MyDB2_log' , 1)
GO
-- just to make sure the log file is shrunk to the minimum
select * into MyDB2..MyTable from MyDB1..MyTable where 1=2
go

-- truncate table MyDB2..MyTable -- for testing
-- populate destination database
DECLARE @StartID bigint, @BatchSize bigint, @EndID bigint, @LastID bigint
select @StartID = MIN([ID]) from MyDB1.dbo.MyTable
select @LastID = max(ID) from MyDB1.dbo.MyTable
select @BatchSize = 10000000 -- start with the batch size approx. equal to 10% of the table row count
select @EndID = @StartID + @BatchSize


while @StartID < @LastID
begin
	insert into MyDB2..MyTable  with (tablock) 
	select * from MyDB1.dbo.MyTable where ID between @StartID and @EndID
	set @StartID = @EndID + 1
	select @EndID = @StartID + @BatchSize
end

GO
-- can comment out "with (tablock)" and observe the log increased, meaning the inserts no longer minimally logged
select COUNT(1) from MyDB2.dbo.MyTable with (nolock)

drop table MyDB2..MyTable
GO
select * into MyDB2..MyTable from MyDB1..MyTable 
go



USE [master]
GO

/****** Object:  Database [MyDB1]    Script Date: 4/27/2017 1:38:37 AM ******/
DROP DATABASE [MyDB1]
GO


/****** Object:  Database [MyDB2]    Script Date: 4/27/2017 1:38:50 AM ******/
DROP DATABASE [MyDB2]
GO
27 апр 17, 04:51    [20438275]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
Massa52
Member

Откуда:
Сообщений: 388
Roust_m
В общем я попробовал по совету о-о поставить (tablock) и транзакция разбитая на батчи стала минимально логируемой. Возможно это решит проблему:

DECLARE @StartID bigint, @BatchSize bigint, @EndID bigint, @LastID bigint
select @StartID = MIN([ID]) from MyDB1.dbo.MyTable
select @LastID = max(ID) from MyDB1.dbo.MyTable
select @BatchSize = 100000 -- start with the batch size approx. equal to 10% of the table row count
select @EndID = @StartID + @BatchSize


while @StartID < @LastID
begin
	insert into MyDB2..MyTable  with (tablock) 
	select * from MyDB1.dbo.MyTable where ID between @StartID and @EndID
	set @StartID = @EndID + 1
	select @EndID = @StartID + @BatchSize
end


Скрипт не полностью переносит данные.
для иллюстрации - см. упрощенный вариант скрипта.
DECLARE @StartID bigint, @BatchSize bigint, @EndID bigint, @LastID bigint
select @StartID = 1 --MIN([ID]) from MyDB1.dbo.MyTable
select @LastID = 11  --max(ID) from MyDB1.dbo.MyTable
select @BatchSize = 4 --100000 -- start with the batch size approx. equal to 10% of the table row count
select @EndID = @StartID + @BatchSize

while @StartID < @LastID
begin
	--insert into MyDB2..MyTable  with (tablock) 
	--select * from MyDB1.dbo.MyTable where ID between @StartID and @EndID
	PRINT @StartID
	PRINT @EndID
	set @StartID = @EndID + 1
	select @EndID = @StartID + @BatchSize
end

11 запись не запишется.
27 апр 17, 06:48    [20438304]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
uaggster
Member

Откуда:
Сообщений: 1062
Roust_m
Обнаружил, что в SQL Server 2014 "select * into" работает паралельно, поэтому если это заменить на "insert into ... with (tablock) select * ...", то несмотря на то, что транзация остается минимально логируемой, скорость выполнения падает в два раза (на моем примере).

Грабли, короче, не думал, что так сложно найти что-то лучше, чем "select * into".

Вот пример который я построил (мой вариант с батчами занял 1 минуту 7 секунд, "select * into" всего 33 секунды):


1. Побейте таблицу-приёмник на секции (хотя бы порезав диапазон кластерного ключа на N частей).
2. Делайте Select * into ... Where id between [диапазон ключей в секции] в таблицу в той же файловой группе, что и приемник.
3. Потом сделайте свитч партишн.

К сожалению, п.2 не удастся запустить параллельно для нескольких секций в нескольких джобах (а хотелось бы), иначе операция не будет минимально логгируемой.
Или я тут не прав?
27 апр 17, 10:03    [20438692]     Ответить | Цитировать Сообщить модератору
 Re: Лучший способ скопировать таблицу размером 1.8ТБ с числом записей почти 2 миллиарда  [new]
o-o
Guest
все еще продолжается праздник секционирования?
ТС переносит из одной базы в другую, какой switch?
у него *в разных базах* таблицы
27 апр 17, 10:10    [20438730]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить