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

Откуда:
Сообщений: 29
Не могу сообразить как можно отвадить SQL сканировать родительскую таблицу, при добавлении записей в дочернюю.
Особенно, учитывая тот факт, что значение в foreign key поле заносится из константы, т.е. там заведомо одно единственное значение. Вот в таком виде (при добавлении одной записи) в плане видим seek. Но стоит увеличить число записей (раскомментировать top и from), то на моём сервере уже при 39 записях SQL меняет seek на scan.

/*
create table dbo.Table_Master
(
	ID			int	identity(1,1)	not null	constraint PK_Table_Master primary key
	,Place_Holder	varchar(10)		not null
)

create table dbo.Table_Detail
(
	ID			int	identity(1,1)	not null	constraint PK_Table_Detail primary key
	,Master_ID		int				not null	constraint FK_Table_Detail__Table_Master foreign key references dbo.Table_Master (ID)
	,Place_Holder	varchar(10)		not null
)
*/

declare @ID int

insert into dbo.Table_Master
	(Place_Holder)
select
	'aaaa' as Place_Holder

set @ID = @@identity

insert into dbo.Table_Detail
	(Master_ID, Place_Holder)
select-- top 39
	@ID as Master_ID
	,'bbbbb' as Place_Holder
--from sys.tables


Forceseek для insert не разрешает Майкрософт.
1 мар 18, 15:01    [21228747]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
ДонАтелло
Member

Откуда:
Сообщений: 29
Имеется в виду второй запрос в плане (рис.)

К сообщению приложен файл. Размер - 12Kb
1 мар 18, 15:04    [21228764]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
ДонАтелло,

ну всавьте миллион записей в Table_Master :)
1 мар 18, 15:24    [21228855]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
ДонАтелло,

Вам доподлинно известно, что NL будет выгоднее MERGE или HASH?
1 мар 18, 15:59    [21229032]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
ДонАтелло
Member

Откуда:
Сообщений: 29
TaPaK
ДонАтелло,

ну всавьте миллион записей в Table_Master :)


:)
В данном случае таблица, как раз, маленькая. Автоочищаемая со временем... Но да, можно залить бетону для прочности... Как вариант...
1 мар 18, 16:45    [21229255]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
ДонАтелло
Member

Откуда:
Сообщений: 29
invm
ДонАтелло,

Вам доподлинно известно, что NL будет выгоднее MERGE или HASH?


А при чём тут это? Это join вкорячивает сам SQL на основании наличия foreign key. Вопрос, как раз, и состоит в том, знает ли кто-либо способ повлиять на этот join?
1 мар 18, 16:51    [21229284]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
ноу скан май френд ноу
Guest
ДонАтелло,

в каждой таблица строк по сколько? примерно по 39?
1 мар 18, 17:00    [21229329]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
ДонАтелло
А при чём тут это? Это join вкорячивает сам SQL на основании наличия foreign key. Вопрос, как раз, и состоит в том, знает ли кто-либо способ повлиять на этот join?
Если вас не устраивает "вкоряченное" - избавьтесь от FK.
Если же считаете, что лучше сервера знаете как соединить таблицы, то добавьте к запросу option (loop join) и будет вам счастье в виде Nested Loops + Index Seek.
1 мар 18, 17:04    [21229353]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
ДонАтелло
TaPaK
ДонАтелло,

ну всавьте миллион записей в Table_Master :)


:)
В данном случае таблица, как раз, маленькая. Автоочищаемая со временем... Но да, можно залить бетону для прочности... Как вариант...

мда... если вставити 1кк то получите seek. Я так понимаю вы боитесь слова "scan"
1 мар 18, 17:52    [21229625]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
ДонАтелло
invm
ДонАтелло,

Вам доподлинно известно, что NL будет выгоднее MERGE или HASH?


А при чём тут это? Это join вкорячивает сам SQL на основании наличия foreign key. Вопрос, как раз, и состоит в том, знает ли кто-либо способ повлиять на этот join?
Как же сервер может сделать проверку на существование записи, не вкорячивая join? Сказали проверять, он и проверяет.
1 мар 18, 18:01    [21229683]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
ДонАтелло
Member

Откуда:
Сообщений: 29
invm
ДонАтелло
А при чём тут это? Это join вкорячивает сам SQL на основании наличия foreign key. Вопрос, как раз, и состоит в том, знает ли кто-либо способ повлиять на этот join?
Если вас не устраивает "вкоряченное" - избавьтесь от FK.
Если же считаете, что лучше сервера знаете как соединить таблицы, то добавьте к запросу option (loop join) и будет вам счастье в виде Nested Loops + Index Seek.


Блин... Отчего-то был уверен что insert проигнорирует option. Виноват. В таком виде - да, пошёл seek на любых кол-вах...
От FK избавляться не очень хочется.
Но, т.к. вставляются записи ВСЕГДА строго по одному мастеру, то я считаю этот вариант тут вполне приемлемым.
Спасибо.
1 мар 18, 18:40    [21229854]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Основательный человек, сам старательно раскладывает грабли
1 мар 18, 18:42    [21229861]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
ДонАтелло
Member

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

Я не боюсь слова scan. Мне немного не нравятся дедлоки, вероятность которых, при наличии сканов резко возрастает. А так-то я более чем толерантен.
1 мар 18, 18:52    [21229899]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
ДонАтелло
Мне немного не нравятся дедлоки, вероятность которых, при наличии сканов резко возрастает.
При наличии сканов таблиц из одной записи? Вряд ли возрастает.
1 мар 18, 19:01    [21229915]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
ДонАтелло
Member

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

Кто сказал что одной? Это вставляю я N записей в Detail с гаранитрованно одним Master_ID. Но из этого никак не следует что в мастере нет к этому времени других записей.
И в профилировщике я чётко видел блокировки накладываемые этим сканом на посторонние (ну относительно текущего контекста)записи.
Как минимум на одну постороннюю запись. Ту, детали которой сейчас вставляет вторая, конкурирующая сессия.
1 мар 18, 19:07    [21229928]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
ДонАтелло
Мне немного не нравятся дедлоки, вероятность которых, при наличии сканов резко возрастает.
Возникновение дедлоков не зависит от метода доступа, а зависит от порядка наложения блокировок на ресурсы, к которым идет конкурентное обращение.
1 мар 18, 19:48    [21230025]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
ДонАтелло
Member

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

Да, но не только. Сканы в MSDN не указаны, как основная причина (тут не причины, а меры по предотвращению, но не суть, Ваша - первая):

•Access objects in the same order.
•Avoid user interaction in transactions.
•Keep transactions short and in one batch.
•Use a lower isolation level.
•Use a row versioning-based isolation level.
◦Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.
◦Use snapshot isolation.
•Use bound connections.

Но в массе литературы (у того же Короткевича) раздел посвящённый борьбе с взаимоблокировками начинается с фразы:
"...They can happen due to the scans introduced by non-optimized queries."
И трудно с ним не согласится... Если транзакция, которая собирается вставлять детали мастера 1, пусть, даже, кратковременно блокирует все записи в таблице мастер, то когда она поссорится с другой сессией, которая вставляет детали мастера 2 и тоже будет блокировать все записи в таблице мастер - это вопрос времени.
На приведённом тут примере это показать проще простого, если интересно.
1 мар 18, 20:05    [21230064]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
Сканы в MSDN
Guest
ДонАтелло,

автор
•Access objects in the same order.
•Keep transactions short and in one batch.

Вот оно и есть. "Скан" сам по себе еще ничего не означает.
Но он лочит очень много всего, в т.ч. лишнего (п3) и в слабопредсказуемом порядке (п1)
1 мар 18, 21:05    [21230239]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
ДонАтелло
Member

Откуда:
Сообщений: 29
Сканы в MSDN,

Да, ну и я о том же... Мораль то всё равно одна... Избегать их надо, по возможности.
1 мар 18, 21:21    [21230270]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
И что же будет лочить скан на read committed?
1 мар 18, 21:28    [21230280]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
ДонАтелло
Member

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

Я же не писал "всегда" или "везде"... Я написал что не надо лопатить больше чем надо, когда это не надо. Если Вас задело использование термина scan для обозначения "лишнего" - искренне прошу прощения.

Вопрос решён. Спасибо за помощь. Лично Вам. И лично Вам же "извините" за то что не сразу понял Ваше
invm
ДонАтелло,

Вам доподлинно известно, что NL будет выгоднее MERGE или HASH?


как направление мысли, а не как вопрос не по теме.

Но Вы же не хотите "прямо сейчас открыть дискуссию" © об уровнях блокировок?
+
[youtube=]
1 мар 18, 21:37    [21230292]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
ДонАтелло
Я написал что не надо лопатить больше чем надо, когда это не надо.
В вашем примере именно по этой причине оптимизатор ушел от NL к MERGE.
1 мар 18, 22:32    [21230382]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
ДонАтелло
Member

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

Ну, видимо, Вы имеете в виду, что MERGE, особенно по кластерному ключу, который уже отсортирован - это наискорейший вариант соединения двух наборов данных.
Однако, памятуя, что при read committed движок будет последовательно блокировать (и тут же отпускать) каждую запись из мастера, мы получаем, что даже в самом лучшем сценарии, когда все сканы производятся по порядку, мы наталкиваемся на блокировку второй сессии, которая будет ждать первую, несмотря на то, что они наполняют два разных Table_Master.ID деталями.
Т.е. мы выстроим запросы в очередь, чего, явно, не требуется.
В моём случае ситуация чуть сложнее. Таблиц с детализацией 5 штук. Т.е. запрос выглядит, условно, так:
begin tran
insert 1 row into Master
insert into it's Detail1
insert into it's Detail2
insert into it's Detail3
insert into it's Detail4
insert into it's Detail5
commit tran

Несложно увидеть, что тут будет 5 полных сканов мастера. Беря во внимание параллельные сессии, которые делают то же самое, +возможное нарушение порядка сканирования кластерного ключа мастера в следствии каких-нибудь merry-go-round scans, получаем вероятность взаимоблокировки. На ровном, в общем-то, месте.
На рисунке видно, что вторая сессия, в некий момент, напарывается на блокировку первой сессии. Хотя вторая сессия добавляет детали к совсем другой записи, нежели первая.

К сообщению приложен файл. Размер - 11Kb
2 мар 18, 01:45    [21230665]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
aleksrov
Member

Откуда:
Сообщений: 948
begin tran
insert 1 row into Master
insert into it's Detail1
insert into it's Detail2
insert into it's Detail3
insert into it's Detail4
insert into it's Detail5
commit tran
Шикарно, в одной транзакци.
А так да, блакировки на ровном месте.
2 мар 18, 06:43    [21230715]     Ответить | Цитировать Сообщить модератору
 Re: Clustered index scan родительской таблицы, при добавлении записей в дочернюю  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
ДонАтелло
На рисунке видно, что вторая сессия, в некий момент, напарывается на блокировку первой сессии. Хотя вторая сессия добавляет детали к совсем другой записи, нежели первая.
Это блокировка, а не взаимоблокировка.
Рассматривая только ваш случай, конкуренция может привести к взаимоблокировке, если будет allocation order scan.
2 мар 18, 09:44    [21231159]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить