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




Forceseek для insert не разрешает Майкрософт.

Имеется в виду второй запрос в плане (рис.)

TaPaKДонАтелло,

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

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

invmДонАтелло,

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

А при чём тут это? Это join вкорячивает сам SQL на основании наличия foreign key. Вопрос, как раз, и состоит в том, знает ли кто-либо способ повлиять на этот join?

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

Блин... Отчего-то был уверен что insert проигнорирует option. Виноват. В таком виде - да, пошёл seek на любых кол-вах...
От FK избавляться не очень хочется.
Но, т.к. вставляются записи ВСЕГДА строго по одному мастеру, то я считаю этот вариант тут вполне приемлемым.
Спасибо.

TaPaK,

Я не боюсь слова scan. Мне немного не нравятся дедлоки, вероятность которых, при наличии сканов резко возрастает. А так-то я более чем толерантен.

alexeyvg,

Кто сказал что одной? Это вставляю я N записей в Detail с гаранитрованно одним Master_ID. Но из этого никак не следует что в мастере нет к этому времени других записей.
И в профилировщике я чётко видел блокировки накладываемые этим сканом на посторонние (ну относительно текущего контекста)записи.
Как минимум на одну постороннюю запись. Ту, детали которой сейчас вставляет вторая, конкурирующая сессия.

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 и тоже будет блокировать все записи в таблице мастер - это вопрос времени.
На приведённом тут примере это показать проще простого, если интересно.

Сканы в MSDN,

Да, ну и я о том же... Мораль то всё равно одна... Избегать их надо, по возможности.

invm,

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

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

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

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

Но Вы же не хотите "прямо сейчас открыть дискуссию" © об уровнях блокировок?
[spoiler][youtube=

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, получаем вероятность взаимоблокировки. На ровном, в общем-то, месте.
На рисунке видно, что вторая сессия, в некий момент, напарывается на блокировку первой сессии. Хотя вторая сессия добавляет детали к совсем другой записи, нежели первая.