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

Откуда:
Сообщений: 2
Я дилетант в sql и вопрос общий. кому не жалко минутки прошу помочь. поиском пользовался не помогло. как работает Delete с условием? на сколько я понял каждая удаляемая запись блокируется и логируется. или нет? или операции сразу работают с подмножеством которое в условии?
просто если представим что есть 2 связанных таблицы по нескольку десятков лямов в каждой и в малонагруженое время мы чистим их удаляя процентов 20. вот как это сделать наиболее быстро. знаю что если в цикле сделать с ограниченным количеством то объем лога будет небольшой.
и еще интересно про секционирование. оно предпочтительно для хранилищ или активное изменение не противопоказаны этой структуре??? и вот в ситуации с удалением 20% от нескольких десятков лямов(если секционированы) какой грубо округляя выигрыш будет если сравнить с дропом секций и просто delete с условием. извиняюсь за сумбурность. и есть ли какие то особенно важные критерии пропущеные в вопросе? заранее спасибо.
24 дек 12, 12:51    [13676339]     Ответить | Цитировать Сообщить модератору
 Re: Объяснить механизм Delete  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
renzo
на сколько я понял каждая удаляемая запись блокируется и логируется. или нет? или операции сразу работают с подмножеством которое в условии?
операции сразу работают с подмножеством которое в условии, все изменения данных логируются (в логи пишутся значения каждой записи до и после изменения), оптимальная блокировка выбирается сервером (для удаления 20% из большой таблицы будет заблокирована вся таблица).
renzo
и еще интересно про секционирование. оно предпочтительно для хранилищ или активное изменение не противопоказаны этой структуре??? и вот в ситуации с удалением 20% от нескольких десятков лямов(если секционированы) какой грубо округляя выигрыш будет если сравнить с дропом секций и просто delete с условием.
Активное изменение противопоказано, если данные меняются во всех секциях
Удаление секции конечно проще обычного удаления delete с условием.
24 дек 12, 13:48    [13676857]     Ответить | Цитировать Сообщить модератору
 Re: Объяснить механизм Delete  [new]
Гость333
Member

Откуда:
Сообщений: 3683
renzo
на сколько я понял каждая удаляемая запись блокируется и логируется

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

renzo
вот как это сделать наиболее быстро.

Ставил когда-то подобный эксперимент, выяснилось, что наиболее быстро удалять в цикле порциями по N записей. N определял экспериментально. С увеличением значения N скорость сначала росла, а затем падала. То есть существует некий оптимум для N, а можно ли его как-то определить теоретически — не знаю. Ещё помню, что удалось ускорить запрос на удаление, указав хинт на использование кластерного индекса (получился clustered index scan вместо nonclustered index seek). Но, возможно, это была индивидуальная особенность — очищаемая таблица в том эксперименте никогда не растёт больше определённого объёма (несколько десятков миллионов записей).

renzo
и вот в ситуации с удалением 20% от нескольких десятков лямов(если секционированы) какой грубо округляя выигрыш будет если сравнить с дропом секций и просто delete с условием

Если у вас удаётся секционировать таблицу так, что удаляемые записи помещаются в одну секцию, то выигрыш шикарный — ибо рокировка метаданных займёт несколько миллисекунд (если на таблице в момент выполнения "alter table switch partition" не висит никаких блокировок).

Если вы секционируете таблицу, весьма желательно провести ревизию всех запросов, обращающихся к этой таблице. Иначе возможны неприятные сюрпризы с изменившимися планами запросов. Пример:
+
set nocount on;
use tempdb;
go
-- Создаём таблицу и индекс на файловой группе
create table dbo.test_partition(id int identity,
x int default 1,
constraint pk_test_partition primary key clustered(id));

create index i_x on dbo.test_partition(x);
go
insert dbo.test_partition default values;
go 10000
set showplan_text on;
go
-- Видим в плане запроса Index Scan, Top 1
select max(x) from dbo.test_partition;
go
set showplan_text off;
go
create partition function test_pf(int)
as range left
for values (2000, 4000, 6000, 8000);

create partition scheme test_ps
as partition test_pf
all to ([primary]);
go
-- Пересоздаём таблицу и индекс на файловой группе
alter table dbo.test_partition drop constraint pk_test_partition;

alter table dbo.test_partition add constraint pk_test_partition primary key clustered (id) on test_ps(id);

drop index i_x on dbo.test_partition;

create index i_x on dbo.test_partition(x) on test_ps(id);
go
set showplan_text on;
go
-- Запрос тот же, план другой - полный Index Scan
select max(x) from dbo.test_partition;
go
set showplan_text off;
go
drop table dbo.test_partition;
drop partition scheme test_ps;
drop partition function test_pf;
go
24 дек 12, 14:08    [13677030]     Ответить | Цитировать Сообщить модератору
 Re: Объяснить механизм Delete  [new]
renzo
Member

Откуда:
Сообщений: 2
премного благодарен за потраченное время! можно закрываться.
24 дек 12, 14:25    [13677172]     Ответить | Цитировать Сообщить модератору
 Re: Объяснить механизм Delete  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Гость333,

хм. достаточно странное поведение, как-то не сравнивал ранее. спасибо!
получается - индекс практически перестает работать для типовых запросов, а чтобы работал - его нельзя "выравнивать", нужно явно создавать на ФГ, а не на схеме..
скажем, элементраная вычитка top 100 x order by x начинает сканить и после сортировать, а не тупо сканить индекс
24 дек 12, 14:58    [13677445]     Ответить | Цитировать Сообщить модератору
 Re: Объяснить механизм Delete  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Гость333,

это, оказалось, старая бага
https://connect.microsoft.com/SQLServer/feedback/details/240968/partition-table-using-min-max-functions-and-top-n-index-selection-and-performance
только фиксить ее не собираются уже 2 года как
24 дек 12, 16:54    [13678494]     Ответить | Цитировать Сообщить модератору
 Re: Объяснить механизм Delete  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Crimean
только фиксить ее не собираются уже 2 года как

Печально, но ничего, продолжаем плясать вокруг секционированных таблиц с курсорами/циклами по партициям :-)
24 дек 12, 17:27    [13678675]     Ответить | Цитировать Сообщить модератору
 Re: Объяснить механизм Delete  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Гость333,

апну старую тему - такое поведение с секциями осталось в 12 сервере?
(чтобы самому не разворачивать стенд?)
6 фев 13, 11:55    [13882984]     Ответить | Цитировать Сообщить модератору
 Re: Объяснить механизм Delete  [new]
invm
Member

Откуда: Москва
Сообщений: 9823
Crimean,

11.0.3339.0 -- ничего не изменилось .
6 фев 13, 12:03    [13883072]     Ответить | Цитировать Сообщить модератору
 Re: Объяснить механизм Delete  [new]
Crimean
Member

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

спасибо!
6 фев 13, 12:19    [13883209]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить