блокировки при удалении покрывающего индекса / Microsoft SQL Server

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / блокировки при удалении покрывающего индекса

25 сообщений из 32, страница 1 из 2

все

блокировки при удалении покрывающего индекса

#40009129

Sandist

Участник

Сообщения: 460

Рейтинг: 0 / 0

привет, подскажите пожалуйста что можно сделать в такой ситуации:
есть таблица А которая имеет примерно 3 млн записей, и которая часто используется пользователями в запросах select (высоко нагруженная)
Есть отдельная база данных с такой же таблицей/ В этой базе собираются данные и записываются в аналогичную таблицу. Затем джоб копирует данные из этой таблицы в рабочую и тут возникают проблемы с блокировками.

На текущий момент я реализовал так

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

WHILE 1 = 1
	BEGIN
		delete TOP (100000) 
		from AutoSupply.dbo.t_МагазинПоТоварам

		IF @@ROWCOUNT < 100000 BREAK;
	END

	insert into AutoSupply.dbo.t_МагазинПоТоварам(Магазин, Товар, и куча полей)
	select Магазин, Товар, и куча полей
	from dbo.t_МагазинПоТоварам with(nolock)

Ранее на AutoSupply.dbo.t_МагазинПоТоварам был ключ по Магазин и Товар, но возникали блокировки когда я удалял ключ перед копированием данных. То есть в момент

Код: sql

alter table AutoSupply.dbo.t_МагазинПоТоварам drop constraint PK_t_МагазинПоТоварам

возникали блокировки

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

Код: sql

DROP INDEX ix_магазинпотоварам ON AutoSupply.dbo.t_МагазинПоТоварам

в общем сейчас решил создать уникальный некласт индекс на поля Магазин и Товар и не удалять его перед и создавать после

Таблица обновляет каждые 15 минут, при этом в момент обновления запрещается создание документов, поэтому само обновление (копирование данных) должно производиться максимально быстро.

Может кто сталкивался с этим и может помочь советом как лучше поступить

спасибо

...

Рейтинг:

0 / 0

16.10.2020, 09:54

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40009142

Yasha123

Участник

Сообщения: 1 968

Рейтинг: 0 / 0

1. почему не truncate, если все равно удаляются все записи?
2. любое изменение схемы объекта, как например удаление индекса, всегда даст Sch-M.
3. если дропать кластерный (PK видимо был кластерный),
то таблица из кластерного состояния перестраивается в кучу,
это будет дольше, чем просто дропнуть некластерный.
4. верните уже свой кластерный на место, и в соседней базе, раз там *аналогичная* таблица,
сделайте такой же кластерный.
а при инсерте делайте не просто селект, а упорядоченный с order by по ключу кластерного (order by Магазин, Товар).
все ваше время убивалось не на инсерт, а на сортировку для вставки в любой индекс
5. если простая модель, то еще лучше вставить с таблоком. моментально выйдет, 3 млн это не объем

...

Рейтинг:

0 / 0

16.10.2020, 10:40

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40009164

Sandist

Участник

Сообщения: 460

Рейтинг: 0 / 0

Yasha123,

спасибо большое за ответ
1. truncate не использую потому что он тоже блокирует данные (насколько я понял)
2. реализовал вставку по твоему совету

Код: sql

1.
2.
3.
4.

insert into AutoSupply.dbo.t_МагазинПоТоварам with(tablock) (ExtID_Ресурс,ExtID_Важность, и много полей)
	select ExtID_Ресурс,ExtID_Важность, и много полей
	from dbo.t_МагазинПоТоварам with(nolock)
	order by ExtID_Ресурс, ExtID_Важность

на таблице источнике добавил аналогичный ключ.
Процесс копирования занимает 18 секунд - это нормально? то есть примерно на 8 секунд медленнее чем скопировать и затем восстановить покрывающий некластерный индекс. Может я что-то упустил?

...

Рейтинг:

0 / 0

16.10.2020, 11:15

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40009165

alexeyvg

Участник

Откуда: Moscow

Сообщения: 26 926

Рейтинг: 0 / 0

Sandist

1. truncate не использую потому что он тоже блокирует данные (насколько я понял)

Да, но он намного быстрее.

Sandist

Процесс копирования занимает 18 секунд - это нормально? то есть примерно на 8 секунд медленнее чем скопировать и затем восстановить покрывающий некластерный индекс.

Такое может быть.

...

Рейтинг:

0 / 0

16.10.2020, 11:23

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40009171

Sandist

Участник

Сообщения: 460

Рейтинг: 0 / 0

alexeyvg,

ребята, подскажите пожалуйста из своего опыта
как скопировать данные без создания блокировок (обязательно и truncate не подходит) и при этом максимально быстро
зная, что таблица высоко нагружена и там нужен индекс на Магазин+Товар

...

Рейтинг:

0 / 0

16.10.2020, 11:32

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40009185

Yasha123

Участник

Сообщения: 1 968

Рейтинг: 0 / 0

Sandist

примерно на 8 секунд медленнее чем скопировать и затем восстановить покрывающий некластерный индекс. Может я что-то упустил?

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

...

Рейтинг:

0 / 0

16.10.2020, 12:41

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40009188

Yasha123

Участник

Сообщения: 1 968

Рейтинг: 0 / 0

Sandist

как скопировать данные без создания блокировок (обязательно и truncate не подходит) и при этом максимально быстро
зная, что таблица высоко нагружена и там нужен индекс на Магазин+Товар

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

...

Рейтинг:

0 / 0

16.10.2020, 12:48

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40009195

aleks222

Участник

Сообщения: 2 564

Рейтинг: 0 / 0

Sandist

Может кто сталкивался с этим и может помочь советом как лучше поступить

Ну коль пошла такая пЪянка - режЪ последний огурец.

1. Добавлям в AutoSupply.dbo.t_МагазинПоТоварам поле version, записываем туды 1. Делаем кластерный индекс (version, Магазин, Товар).
2. Переименовываем таблицу AutoSupply.dbo.t_МагазинПоТоварам_Hidden.
3. Делаем вьюху AutoSupply.dbo.t_МагазинПоТоварам as select * from AutoSupply.dbo.t_МагазинПоТоварам_Hidden where version = 1.
4. Все радостно работают.
5. Копируем новые записи
insert into AutoSupply.dbo.t_МагазинПоТоварам(version, Магазин, Товар, и куча полей)
select 2, Магазин, Товар, и куча полей
from dbo.t_МагазинПоТоварам with(nolock)
6. Alter View AutoSupply.dbo.t_МагазинПоТоварам as select * from AutoSupply.dbo.t_МагазинПоТоварам_Hidden where version = 2
7. Это дело 1 сек. Никто и не заметит. Все радостно работают.
8. И у тя 15 мин на удаление delete from AutoSupply.dbo.t_МагазинПоТоварам_Hidden where version = 1. Если удалять пачками по 5000 - таблица будет НЕзаблокирована.
9. Повторяем с п5. Пока не надоест.

ЗЫ. Канешно, swith круче.

ЗЗЫ. Кстати Alter View необязателен. Можно вспомогательной табличкой откосить.

...

Рейтинг:

0 / 0

16.10.2020, 13:09

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40009222

SIMPLicity_

Участник

Откуда: (((@)))

Сообщения: 8 932

Рейтинг: 0 / 0

Yasha123

Sandist

и что?
truncate это 0 секунд.
truncate + switch тоже 0 секунд.
но лучше конечно делитить и писать построчно в лог,
delete-то у нас ничего не блокирует, правда?

Извините, вопрос : что подразумевается под " switch " ?

PS Это что-то новое? Или способ реализации чего-либо? Где почитать?
Заранее спасибо!

...

Рейтинг:

0 / 0

16.10.2020, 13:54

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40009228

felix_ff

Участник

Откуда: Moscow

Сообщения: 1 982

Рейтинг: 0 / 0

SIMPLicity_

Yasha123

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

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

и что?
truncate это 0 секунд.
truncate + switch тоже 0 секунд.
но лучше конечно делитить и писать построчно в лог,
delete-то у нас ничего не блокирует, правда?

Извините, вопрос : что подразумевается под " switch " ?

PS Это что-то новое? Или способ реализации чего-либо? Где почитать?
Заранее спасибо!

https://www.cathrinewilhelmsen.net/2015/04/19/table-partitioning-in-sql-server-partition-switching/
https://docs.microsoft.com/ru-ru/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver15

...

Рейтинг:

0 / 0

16.10.2020, 14:02

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40009319

SIMPLicity_

Участник

Откуда: (((@)))

Сообщения: 8 932

Рейтинг: 0 / 0

felix_ff

SIMPLicity_

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

Извините, вопрос : что подразумевается под " switch " ?

PS Это что-то новое? Или способ реализации чего-либо? Где почитать?
Заранее спасибо!

Спасибо! Уже читаю...

...

Рейтинг:

0 / 0

16.10.2020, 18:11

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40009991

fkthat

Участник

Сообщения: 3 601

Рейтинг: 0 / 0

alexeyvg

Да, но он намного быстрее.

К слову сказать, truncate имеет очень неприятное ограничение - его вообще нельзя делать на таблице на кторую есть FK, даже если данные это позволяют.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.

create table a (
  id int primary key
)

create table b (
  id int references a(id)
)

truncate table a

Msg 4712, Level 16, State 1, Line 9
Cannot truncate table 'a' because it is being referenced by a FOREIGN KEY constraint.

И это при том, что обе таблицы вообще пустые.

...

Рейтинг:

0 / 0

19.10.2020, 20:02

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40011925

Sandist

Участник

Сообщения: 460

Рейтинг: 0 / 0

привет, сори что пропал
Ребята, спасибо большое суть я уловил, или делаем добавление данных в таблице меняем переключатель и затем потихому удаляем старые данные
либо стараемся сделать все оч быстро и эти операции будут ждать освобождения ресурсов

На тек момент реализовал truncate + switch (спасибо Yasha123). Далее буду стараться придумать что-то с секционированием, то есть новая секция и switch в нее...

...

Рейтинг:

0 / 0

26.10.2020, 15:43

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40011953

SERG1257

Участник

Сообщения: 2 932

Рейтинг: 0 / 0

Способ обновления таблицы А как очистить/загрузить это просто дешево и сердито. Замечательно работает если данные сильно меняются.
А вот если не очень то удалять и перезаписывать всё смысла имеет мало. Попробуйте вместо truncate/insert

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.

delete from target where not exists (select * from source where source.id=target.id)

update target set target.data=source.data
from target 
join source on target.id=source.id and target.data<>source.data

insert data into target 
select data from source where not exists (select * from source where source.id=target.id)

...

Рейтинг:

0 / 0

26.10.2020, 17:13

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40034641

Sandist

Участник

Сообщения: 460

Рейтинг: 0 / 0

привет
резюмирую тут свои выводы и вдруг кому понадобится или же меня поправят

Я вижу 2 выхода из этой ситуации
1 - когда мы все же блокируем объект (таблицу) на оч короткое время, очищаем ее и заливаем данные
у меня сейчас так и реализовано:

Код: sql

1.
2.

truncate table AutoSupply.dbo.t_МагазинПоТоварам
alter table AutoSupply.dbo.t_МагазинПоТоварам_refresh switch to AutoSupply.dbo.t_МагазинПоТоварам

при таком подходе плохо, если есть долгоживущие запросы к таблице, к примеру select update delete которые долго выполняются... truncate и alter table (обеим нужен монопольный доступ) будут ждать завершения запросов и только после этого выполнятся (но выполнятся менее чем за 1 секунду)

2 - когда мы переводим базу в режим с уровнем изоляции READ_COMMITTED_SNAPSHOT (ну или SNAPSHOT). При этом мы не будем блокировать select'ами данные, данные будут считываться из снимка на момент ДО внесения в них незакомиченных транзакций за счет версионирования данных (но тут нужно быть осторожным, так как копии данных для select будут попадать в tempdb и она может вырасти, ну и про скорость чтения с tempdb не стоит забывать я думаю).
Тогда в таблицу мы добавляем поле Version(ну или любое др название) и присваиваем различные версии для "текущих" и "новых" данных.
Текущие данные у нас будут иметь признак Version = 1 (к примеру)
Добавляем новые данные с признаком Version = 0 (к примеру)
Далее делаем update и заменяем 0 на 1, а 1 на 0
Соответственно во вьюшке мы выбираем только данные where Version = 1
После - удаляем данные из таблицы where Version = 0 (пакетами через top)
При этом методе мы с меньшей долей вероятности получим блокировки данных и чтение не будет влиять на этот процесс. То есть процесс обновления будет работать параллельно с работой select'ов и мы не получим остановку работы отчетов или остановку процесса обновления данных

Может в чем ошибаюсь, поправьте пожалуйста

...

Рейтинг:

0 / 0

10.01.2021, 14:43

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40034646

aleks222

Участник

Сообщения: 2 564

Рейтинг: 0 / 0

Sandist

Далее делаем update и заменяем 0 на 1, а 1 на 0
Может в чем ошибаюсь, поправьте пожалуйста

Дурацкая идея.
Update и сопровождающие его блокировки, и запись в журнал.
Нафига?

Быстрее alter view сделать.
А еще проще и также быстро - update 1(одной) строки вспомогательной таблички.

...

Рейтинг:

0 / 0

10.01.2021, 15:14

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40034650

Sandist

Участник

Сообщения: 460

Рейтинг: 0 / 0

aleks222,

если пользователи читают из представления, разве alter view не будет ждать пока все обращения к ней не завершаться перед тем как обновиться? по идее мы просто будем ждать так же как если бы сделали truncate и затем switch

...

Рейтинг:

0 / 0

10.01.2021, 15:20

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40034653

Sandist

Участник

Сообщения: 460

Рейтинг: 0 / 0

Sandist,
авторА еще проще и также быстро - update 1(одной) строки вспомогательной таблички.

то есть в отдельной таблице хранить ту версию которая актуальна и менять одно значение в этой таблице. А во вьюшке уже отбирать версию по этой таблице. Далее удалять все версии которые не соответствуют той что в таблице?
правильно понял идею?
вроде да, она лучше выходит чем делать update

...

Рейтинг:

0 / 0

10.01.2021, 15:25

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40034655

ptr128

Участник

Откуда: Moscow

Сообщения: 680

Рейтинг: 0 / 0

Sandist,

я что-то не понял проблемы. А зачем удалять строки в таблице? Не проще ли только помечать их на удаление при MERGE, а уже периодическим заданием зачищать во время низкой нагрузки системы?

...

Рейтинг:

0 / 0

10.01.2021, 15:28

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40034660

Sandist

Участник

Сообщения: 460

Рейтинг: 0 / 0

ptr128,
суть в том, что мне не нужны старые данные, я меняю их на новые
есть 2 таблицы, в одной лежат текущие данные которые читают пользователи и есть вторая таблица с новыми данные которыми нужно заменит старые

если ты про то, что через MERGE менять данные, то их нужно сравнивать, а там сравнивать кучу полей, подозреваю, что будет гораздо медленнее

если ты про то, что удалять старые не нужно, то данных довольно немало (4 млн записей) и обновляются они каждые 15 минут, таблица сильно вырастит

...

Рейтинг:

0 / 0

10.01.2021, 15:36

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40034663

ptr128

Участник

Откуда: Moscow

Сообщения: 680

Рейтинг: 0 / 0

Sandist,

Вы уверены, что меняются каждые 15 минут все четыре миллиона записей?
Время затрачиваемое на сравнение двух записей при MERGE точно меньше, чем время затрачиваемое на вставку одной записи.
Поэтому, если меняется меньше половины записей, то прирост производительсти Вы точно получите. А накопление даже 200 миллионов записей на удаление за сутки вполне приемлемо для MS SQL.

...

Рейтинг:

0 / 0

10.01.2021, 15:47

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40034665

Sandist

Участник

Сообщения: 460

Рейтинг: 0 / 0

ptr128,
да, изменяются около 90% всех записей (но не все поля конечно меняются). Я проверю вариант с merge?может действительно оно того стоит

да, можно и 200 млн, но зачем? в этом ведь нет необходимости, мы тратим место + все равно это будет влиять на скорость считывания (секций нет)

...

Рейтинг:

0 / 0

10.01.2021, 15:50

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40034668

ptr128

Участник

Откуда: Moscow

Сообщения: 680

Рейтинг: 0 / 0

Sandist,

Важно не столько количество записей, в которых меняются поля, сколько количество записей, в которых меняются индексированные поля. Неиндексированные поля можно смело обновлять. А вот при модификации индекса есть шансы заблокироваться.

А падение производительности выборок из этой таблицы будет по логарифмическому закону и очень далеко от линейного. Естественно, если выборки не уходят в table scan или clustered index scan.

...

Рейтинг:

0 / 0

10.01.2021, 15:59

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40034669

Sandist

Участник

Сообщения: 460

Рейтинг: 0 / 0

ptr128,

на самом деле ключевые поля практически не обновляются, они статичны (в 99.99% случаев) обновляются характеристики которые не имеют отношения к ключу. Я проверю вариант с MERGE и отпишусь

...

Рейтинг:

0 / 0

10.01.2021, 16:03

| Ответить | Цитировать | Написать

блокировки при удалении покрывающего индекса

#40034699

invm

Участник

Откуда: Москва

Сообщения: 9 956

Рейтинг: 0 / 0

Sandist

есть таблица А которая имеет примерно 3 млн записей, и которая часто используется пользователями в запросах select (высоко нагруженная)

Как только начнете мержить эту таблицу с другой, практически все ваши читатели повиснут на ожиданиях. И не важно обновляются ключевые столбцы индексов или нет. Ибо:

Sandist

да, изменяются около 90% всех записей

И спасет только RCSI или Snapshot.

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

...

Рейтинг:

0 / 0

10.01.2021, 18:06

| Ответить | Цитировать | Написать

25 сообщений из 32, страница 1 из 2

все

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / блокировки при удалении покрывающего индекса

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=46&msg=40009228&tid=1685201]:	0ms
get settings:	10ms
get forum list:	13ms
check forum access:	4ms
check topic access:	4ms
track hit:	159ms
get topic data:	11ms
get forum data:	3ms
get page messages:	62ms
get tp. blocked users:	1ms
others:	305ms

total:	572ms