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

Откуда: МИНСК
Сообщений: 1202
Добрый день всем.

Recovery Model Simple
Надо было удалить 30 млн записей с одной достаточно широкой таблицы с кластерным индексом
Таблица стейжинговая - т.е используется только для ETL чтобы перелить в DWH
(разовая задача - но перидоически возможны повторения - фиксы данных )

посчитал это где то 1.5% от общего числа
и решил не замарачивать Delete FRom Date between
ибо на сервре я один - НЕ прошло - пепреполнение лога
Наверно надо было Select * into from в другую таблицу и переименовать
но все таки большой кусок - да и потом я понимаю надо опять
ALTER TABLE <t1> ADD CONSTRAINT <pk1> PRIMARY KEY CLUSTERED

Решил поудалять батчами по 10000 (5000)
WITH T1
AS
(
SELECT TOP 10000 id
FROM BIG
where id > 73465236123
ORDER BY 1 desc
)
DELETE FROM T1

проверил - вроде удаляется быстро
запустил по циклу - через 15 мни смотрю не то - зависло - снял
поглядел запрос - не пашет
UPDATE STATISTICS помогло - запустил опять - тоже самое

Вообщем дропнул я clustered PK (минут 25 - тоже не даром )
Создал NONclustered - запустил удаление по циклу и мин за 10-15 оно отработало (без UPDATE STATISTICS )

1) Понимаю что по хорошему надо делать партиции по месяцам и просто дропать
но пока не дошли руки к тому же в PK надо включать поле даты в этом случаей (а там пока ID достаточно )
к тому же не факт что надо именно месяц дропать - тут было 1.5

1?) После большой заливки (удаления ) данных я так
поинмаю UPDATE STATISTICS обязательная операция

2?) По ощущениям удаление больших кусков из табл. с кластерным индексом более затратно
киньте плз ссылки на мат. часть или кто сталкивался на практике -
(может и не так - частный случай )
зы гуглил сей момент - но не нашел пока толкового объяснения
21 мар 19, 13:47    [21839550]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4371
Гулин Федор,

А TOP 100000 TOP 500000 TOP 1000000 пробовали?

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

версия sql Какая?

и партиционирование должно помочь.
21 мар 19, 14:48    [21839656]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1202
a_voronin
Гулин Федор,

А TOP 100000 TOP 500000 TOP 1000000 пробовали?

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

версия sql Какая?

и партиционирование должно помочь.



Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 6.3 <X64> (Build 14393: ) (Hypervisor)

да про партицировние я в курсе - поможет - надо было быстро сделать

Из практичсекого опыта как раз размер батча делете д.б небольшой (обычно 5000 - здесь ставил 10000 ибо данных )

Про колмунстор вообще не понял - это ж вроде R/Only штука
а это стейжниговая таблица грузящаяся каждый день


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

ну и до кучи делает ли народ UPDATE STATISTICS - хотя здесь ясно что скорей всего надо
21 мар 19, 15:12    [21839712]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30702
Гулин Федор
есть ли особенности кластерного индекса при больших делетах ( особенно скажем в середине ) по ср. с некластерным.
Есть, с кластерным серверу удалять проще.
Только скрипт бы поудобнее для сервера надо. Скажем, если удаляется диапазок Id, и Id более менее равномерны, то лучше удалять по мелким вычисленным диапазонам id
Гулин Федор
Понимаю что по хорошему надо делать партиции по месяцам и просто дропать
Если данных много, удаление - постоянная задача, и поле секциоонирования хорошо лдожиться в ПК, то да, это самое лучшее решение.
21 мар 19, 15:35    [21839745]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4371
Гулин Федор
Про колмунстор вообще не понял - это ж вроде R/Only штука
а это стейжниговая таблица грузящаяся каждый день




Вы очень сильно отстали от жизни.

Колумстор с 2014 read write, а под 2016 он уже очень хорошо оптимизирован. И сжимает данные он очень серьезно.

Я бы попробовал. А партиционированный колумнстор -- это вообще круть.
21 мар 19, 16:29    [21839833]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7383
DELETE WITH (TABLOCK) должно снизить потребление журнала.
21 мар 19, 16:47    [21839859]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1202
alexeyvg
Гулин Федор
есть ли особенности кластерного индекса при больших делетах ( особенно скажем в середине ) по ср. с некластерным.
Есть, с кластерным серверу удалять проще.
Только скрипт бы поудобнее для сервера надо. Скажем, если удаляется диапазок Id, и Id более менее равномерны, то лучше удалять по мелким вычисленным диапазонам id


вот код - тут удалял начиная с какого то ИД ( по хорошему можно было с по ) - но чтобы упростить я удалял до конца
(т.е по факту с какой то даты )
WITH T1 AS  (
SELECT TOP 10000 id 
FROM BIG
where id > 73465236123
ORDER BY 1 desc 
)
DELETE FROM T1


Вот у меня в моем кейсе получилось ровно наооборот - почему до конца не пнял
21 мар 19, 17:04    [21839882]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1202
Владислав Колосов
DELETE WITH (TABLOCK) должно снизить потребление журнала.


https://blogs.msdn.microsoft.com/bartd/2010/06/01/purging-data/
СПАСИБО

возможно в моем случае разового удаления это бы решило проблему
хотя по ссылке (TABLOCK) используется в delete by chunks
т.е сложно сказать хватило бы лога на удаление 25 млн записей
21 мар 19, 17:11    [21839892]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
Владислав Колосов
DELETE WITH (TABLOCK) должно снизить потребление журнала.
Ух ты! А засчет чего и с какой версии?
Может все-таки попутали с INSERT?
21 мар 19, 17:13    [21839894]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
msLex
Member

Откуда:
Сообщений: 7726
invm
Ух ты! А засчет чего и с какой версии?
Может все-таки попутали с INSERT?

Всего навсего меньше инфы о блокировках в лог записывать будет.
21 мар 19, 17:22    [21839909]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
msLex
invm
Ух ты! А засчет чего и с какой версии?
Может все-таки попутали с INSERT?

Всего навсего меньше инфы о блокировках в лог записывать будет.

не будет никакого меньше, просто "другая" информация о блокировке каждой строки
21 мар 19, 17:29    [21839916]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
msLex
Member

Откуда:
Сообщений: 7726
TaPaK
msLex
пропущено...

Всего навсего меньше инфы о блокировках в лог записывать будет.

не будет никакого меньше, просто "другая" информация о блокировке каждой строки


будет, указатель на таблицу меньше чем указатель на строку
21 мар 19, 17:39    [21839928]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30702
Гулин Федор
Вот у меня в моем кейсе получилось ровно наооборот - почему до конца не пнял
Не знаю, надо планы смотреть, разбираться...
Нужно что то вроде такого цикла, тогда планы точно не собьются:
SET @id = 73465236123
WHILE (1=1)
BEGIN
    DELETE FROM BIG where id between @id and @id + 10000
    IF @@ROWCOUNT = 0 BREAK
    SET @id = @id + 10000
END

PS Получается, у вас удаляются последние записи, самые новые, начиная с некоего id?
21 мар 19, 17:41    [21839931]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
msLex
TaPaK
пропущено...

не будет никакого меньше, просто "другая" информация о блокировке каждой строки


будет, указатель на таблицу меньше чем указатель на строку

дейсвительно "уменьшит"
21 мар 19, 17:47    [21839938]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
msLex
Member

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


msLex
Всего навсего меньше инфы о блокировках в лог записывать будет.

TaPaK
не будет никакого меньше


Какое из утверждений верно?
21 мар 19, 18:02    [21839966]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1202
alexeyvg
Гулин Федор
Вот у меня в моем кейсе получилось ровно наооборот - почему до конца не пнял
Не знаю, надо планы смотреть, разбираться...
Нужно что то вроде такого цикла, тогда планы точно не собьются:
SET @id = 73465236123
WHILE (1=1)
BEGIN
    DELETE FROM BIG where id between @id and @id + 10000
    IF @@ROWCOUNT = 0 BREAK
    SET @id = @id + 10000
END

PS Получается, у вас удаляются последние записи, самые новые, начиная с некоего id?


да - я пошел по простому пути
по идее надо было betwwen - но проще было перегрузить с какого-то момента - за 1.5 мес
чем прогружать в реальности 2 гапа

зы насчет не собьется - хз - по факту сбивалась статистика
я на этой БД уже сталкивался когда загружались большие объемы инф-ции в таблицы
переставала рабоать даже Explain <Select Query>
и помогало UPDATE STATISTICS
21 мар 19, 18:07    [21839974]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
+ Всем отвечавшим посвящается
set nocount on;
go

use master;
create database Test001;
go

use Test001;

create table dbo.t1 (id int identity, s varchar(2000));
create table dbo.t2 (id int identity, s varchar(2000));
create table dbo.t3 (id int identity, s varchar(2000));

insert into dbo.t1
 (s)
select top (1000000)
 replicate(a.name + b.name, 20)
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

insert into dbo.t2 (s) select s from dbo.t1;
insert into dbo.t3 (s) select s from dbo.t1;

alter table dbo.t1 add primary key clustered (id);
create clustered columnstore index CIXCS_t2 on dbo.t2;
alter table dbo.t3 add primary key clustered (id);
create columnstore index IXCS_t3 on dbo.t3(s);
go

create function dbo.fnTransactionInfo
(
 @Table sysname
)
returns table
as
return (
 select
  ct.transaction_id, dt.database_transaction_log_record_count, dt.database_transaction_log_bytes_used, grc.ghost_record_count
 from
  sys.dm_tran_current_transaction ct join
  sys.dm_tran_database_transactions dt on dt.transaction_id = ct.transaction_id cross apply
  (select sum(ghost_record_count) as ghost_record_count from sys.dm_db_index_physical_stats(db_id(), object_id(@Table), 1, null, 'detailed')) grc
 where
  dt.database_id = db_id()
);
go

print '---------------------------------------';
print 'Clustered PK only';
print '---------------------------------------';
begin tran;
set statistics io, time on;
delete from dbo.t1 where id between 500000 and 700000;
set statistics io, time off;

select * from dbo.fnTransactionInfo('dbo.t1');
commit;

print '---------------------------------------';
print 'Clustered columnstore only';
print '---------------------------------------';
begin tran;
set statistics io, time on;
delete from dbo.t2 where id between 500000 and 700000;
set statistics io, time off;

select * from dbo.fnTransactionInfo('dbo.t2');
commit;

print '---------------------------------------';
print 'Clustered PK + nonclustered columnstore';
print '---------------------------------------';
begin tran;
set statistics io, time on;
delete from dbo.t3 where id between 500000 and 700000;
set statistics io, time off;

select * from dbo.fnTransactionInfo('dbo.t3');
commit;
go

drop index dbo.t3.IXCS_t3;
go

print '---------------------------------------';
print 'No tablock';
print '---------------------------------------';
begin tran;
delete top (100000) from dbo.t1;

select * from dbo.fnTransactionInfo('dbo.t1');
rollback;

print '---------------------------------------';
print 'Tablock';
print '---------------------------------------';
begin tran;
delete top (100000) from dbo.t1 with (tablock);

select * from dbo.fnTransactionInfo('dbo.t1');
rollback;
go

use master;
drop database Test001;
go

---------------------------------------
Clustered PK only
---------------------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 't1'. Scan count 1, logical reads 6662, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 297 ms.
transaction_id database_transaction_log_record_count database_transaction_log_bytes_used ghost_record_count
-------------------- ------------------------------------- ----------------------------------- --------------------
2206579 200715 22829504 5929

---------------------------------------
Clustered columnstore only
---------------------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 't2'. Scan count 2, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 327, lob physical reads 0, lob read-ahead reads 0.
Table 't2'. Segment reads 2, segment skipped 0.

SQL Server Execution Times:
CPU time = 704 ms, elapsed time = 840 ms.
transaction_id database_transaction_log_record_count database_transaction_log_bytes_used ghost_record_count
-------------------- ------------------------------------- ----------------------------------- --------------------
2207737 600007 40378820 0

---------------------------------------
Clustered PK + nonclustered columnstore
---------------------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 't3'. Scan count 1, logical reads 6313, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 437 ms, elapsed time = 495 ms.
transaction_id database_transaction_log_record_count database_transaction_log_bytes_used ghost_record_count
-------------------- ------------------------------------- ----------------------------------- --------------------
2208032 400712 44178872 6175

---------------------------------------
No tablock
---------------------------------------
transaction_id database_transaction_log_record_count database_transaction_log_bytes_used ghost_record_count
-------------------- ------------------------------------- ----------------------------------- --------------------
2209399 101570 23877972 11737

---------------------------------------
Tablock
---------------------------------------
transaction_id database_transaction_log_record_count database_transaction_log_bytes_used ghost_record_count
-------------------- ------------------------------------- ----------------------------------- --------------------
2213584 101646 23901624 5930
21 мар 19, 18:14    [21839983]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
msLex
TaPaK
пропущено...

не будет никакого меньше, просто "другая" информация о блокировке каждой строки


будет, указатель на таблицу меньше чем указатель на строку
А зачем вообще серверу записывать информацию о блокировках в лог?
21 мар 19, 19:14    [21840055]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36686
Mind
А зачем вообще серверу записывать информацию о блокировках в лог?
https://www.sqlskills.com/blogs/paul/lock-logging-and-fast-recovery/

Сообщение было отредактировано: 21 мар 19, 19:35
21 мар 19, 19:35    [21840090]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
Mind
Member

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

Спасибо. Не знал такой нюанс.
21 мар 19, 20:57    [21840158]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1202
@Invm
СПС за пример

есть пару вопросов

я попробровал
T4

alter table dbo.t4 add primary key nonclustered (id);
select * from dbo.fnTransactionInfo('dbo.t4');

EXEC sys.sp_helpindex @objname = 't4'
index_name	index_description	index_keys
PK__t4__3213E83ECF5CB769	nonclustered, unique, primary key located on PRIMARY	id

Cannot find a row in the system catalog with the index ID 1 for table "t4".


Основная задача была сравнить удаление в кластерном и некластерном индексе
( columnstore всплыли позднее )
1?) Как можно это сделать для некластерных

ghost_record_count - почитал что данные логически удаленные но физически оставшиеся - я так понимаю
это вспомгательная инфа
я так понимаю осн. польза от database_transaction_log_record_count ?
22 мар 19, 16:50    [21841084]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
Гулин Федор
Cannot find a row in the system catalog with the index ID 1 for table "t4".
Поправьте функцию:
(select sum(ghost_record_count) as ghost_record_count from sys.dm_db_index_physical_stats(db_id(), object_id(@Table), null, null, 'detailed')) grc

Гулин Федор
Основная задача была сравнить удаление в кластерном и некластерном индексе
Это бессмысленно.
Гулин Федор
я так понимаю осн. польза от database_transaction_log_record_count ?
Польза для чего? Для определения объема потребления ЖТ? Тогда database_transaction_log_bytes_used.

PS: Эффективно удалять фрагментами можно либо на простой модели восстановления, периодически делая checkpoint, либо периодически делать бекап ЖТ.
А тормоза, скорее всего, вызваны приращениями ЖТ.
Если у таблицы только кластерный ПК по id, для показанного запроса статистика роли не играет.
22 мар 19, 17:58    [21841213]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30702
Гулин Федор
Основная задача была сравнить удаление в кластерном и некластерном индексе
( columnstore всплыли позднее )
1?) Как можно это сделать для некластерных
Что нужно сделать? Провести эксперимент, или понять, что происходит на сервере?
Эксприменты проводите сами, само собой, а что происходит, понять очень просто:

- при удалении по диапазону ключей кластерного индекса сервер удалит диапазон расположенных рядом страниц БД (то есть цепочку расположенных рядом секторов с диска). Конечно, с учётом фрагментации, но на практике фрагментация не влияет.

- при удалении по диапазону ключей из некластерного индекса сервер пройдёт циклом по диапазону записей в индексе, найдёт ссылки на страницы с данными, и записи (строки данных) в них, считает каждую найденную страницу, и удалит записи изнутри этой страницы.

Вот, как то так.
23 мар 19, 11:38    [21841602]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
ScareCrow
Member

Откуда: Белый город
Сообщений: 16814
по идее надо было некластерные индексы дропать. ониж все перестраиваются и там write amplification недетский.
25 мар 19, 12:03    [21842591]     Ответить | Цитировать Сообщить модератору
 Re: Удаление большого куска записей в середине кластерного индекса  [new]
ScareCrow
Member

Откуда: Белый город
Сообщений: 16814
автор
при удалении по диапазону ключей кластерного индекса сервер удалит диапазон расположенных рядом страниц БД


он еще все вторичные индексы перестроит.
25 мар 19, 12:05    [21842596]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить