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

Откуда:
Сообщений: 95
Коллеги, приветствую!

Помогите не сойти с ума -)
Выполняю запрос, в плане вижу, что чтение идет сначала из таблицы AccumRgT106, потом из AccumRg102.
Читаю план как обычно, справа на лево, сверху вниз.

Но блокировки по какой-то причине накладываются сначала на таблицу AccumRg102, и только потом на AccumRgT106.
Проверял с помощью расширенных событий, скрины трассировки и сам план запроса прикладываю.

Почему так происходит? Что я делаю не так?

К сообщению приложен файл (1.zip - 142Kb) cкачать
17 фев 21, 00:09    [22281994]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1697
Андрей_Батькович,

у вас оператор соединения merge join, для него потоки входа что правый что левый запускаются почти одновременно. (это типо неблокирующий оператор)

поэтому тут нет ничего удивительного что может блокировка сначало наложиться на ресурсы правого входа оператора соединения.

add:точнее не совсем так как я написал, точка входа для первого сканирования выбирается исходя из кол-ва строк (вроде) для оператора при merge join (но это не точно :) )

Сообщение было отредактировано: 17 фев 21, 02:50
17 фев 21, 02:49    [22282019]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
Marat2020
Member

Откуда: Белоходоковск
Сообщений: 23
felix_ff,
И даже то что у него MAXDOP=1, это не влияет?
Просто уточняющий вопрос, я не очень хорошо в этом понимаю.
17 фев 21, 03:16    [22282020]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1697
Marat2020,

не должно, я понимаю если бы оператор был бы HJ или NL там первым обрабатывается левый вход.

конкретного описания технологии реалализации в sql server оператора к сожалению нет.
можно только догадываться с какого входа начинается сканирование. те материалы которые встречались лично мне описывали начало обхода левого входа, но судя по приложенным ТС данным и общей концепции работы самого оператора предполагаю что сканирование может начинаться и с правого входа.

другое дело что в плане присутствует блокирующий оператор сортировки для правого входа, и получается что обработка правого входа получается несколько "оперативней" чем начало сканирования для левого входа.
этот факт выглядит весьма занятным

Сообщение было отредактировано: 17 фев 21, 03:38
17 фев 21, 03:43    [22282023]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
Андрей_Батькович
Member

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

Спасибо за пояснение!
Но я не понимаю, почему наличие сортировки на правом входе, делает этот вход более "оперативным"?
Разве за счет сортировки он не должен наоборот немного отставать от левого входа?
17 фев 21, 09:10    [22282046]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
Андрей_Батькович
Member

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

Стоимость плана слишком мала что бы включилось распараллеливание.
17 фев 21, 09:19    [22282052]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9634
Андрей_Батькович,

У вас 2019 RTM. После обновления до последнего CU чудеса могут исчезнуть.
17 фев 21, 10:36    [22282076]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8322
Какая разница - в каком порядке подаются строки?
17 фев 21, 10:58    [22282081]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
Андрей_Батькович
Member

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

Обновил, чудеса продолжаются.
17 фев 21, 11:05    [22282086]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
Андрей_Батькович
Member

Откуда:
Сообщений: 95
Владислав Колосов,

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

2. Как минимум то что план не соответствует фактическому выполнению кажется мне весьма странным, и очень похоже на ошибку. В следствии этого расследование проблем с дедлоками может занять больше времени чем следовало бы. А так то разницы никакой )))
17 фев 21, 11:09    [22282088]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
msLex
Member

Откуда:
Сообщений: 8718
Андрей_Батькович,

В "нижней" ветки у вас sort. Это блокирующий оператор, и до его окончания нет смысле получать "верхний" поток.
17 фев 21, 11:12    [22282091]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
Андрей_Батькович
Member

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

Ок, допустим, но почему тогда не сделать это ветку левой, что план можно было читать как обычно? Разрабы не досмотрели?
17 фев 21, 11:16    [22282096]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
msLex
Member

Откуда:
Сообщений: 8718
Андрей_Батькович
msLex,

Ок, допустим, но почему тогда не сделать это ветку левой, что план можно было читать как обычно? Разрабы не досмотрели?

Зачем?

В плане все верно.

В мерж потоки поступают именно в том порядке, который указан в плане. Просто для получения одного из потоков требуется дополнительные действия. Сортировка. До ее окончания, потоки для мерж вообще не читаются. Т.к. в этом просто нет смысла.

Сообщение было отредактировано: 17 фев 21, 11:17
17 фев 21, 11:24    [22282106]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
Андрей_Батькович
Member

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

Если читать план справа на лево, сверху вниз, то первым в плане выполняется чтение AccumRgT106, но блокируется сначала AccumRg102.
Что же тут верного?

Или вы предлагаете читать план другим способом?
17 фев 21, 11:27    [22282109]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
Андрей_Батькович
Member

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

> Просто для получения одного из потоков требуется дополнительные действия. Сортировка. До ее окончания, потоки для мерж вообще не читаются. Т.к. в этом просто нет смысла.

Это я понимаю, я не понимаю почему вход с сортировкой отображается на правом входе, а не на левом.
17 фев 21, 11:30    [22282111]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
msLex
Member

Откуда:
Сообщений: 8718
Андрей_Батькович
msLex,

Если читать план справа на лево, сверху вниз, то первым в плане выполняется чтение AccumRgT106, но блокируется сначала AccumRg102.
Что же тут верного?

Или вы предлагаете читать план другим способом?


Я предлагаю читать план именно так, как он работает.
Причину, по которой именно так поступает SQL Server вам уже объяснили.
Дальнейшие "пререкания" на эту тему, не более чем демагогия, только от которой = 0

Просто чтобы вы понимали.
В merge join порядок входящих потоков влияет не только на то из какой из них первой будет вычитана 1-я запись, но и направление "локальных cross join nested loop-ов" в случае одинаковых неуникальных значений в обоих потоках данных, а может еще куча других скрытых от нас действий.
17 фев 21, 11:39    [22282118]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
msLex
Member

Откуда:
Сообщений: 8718
Андрей_Батькович
Это я понимаю, я не понимаю почему вход с сортировкой отображается на правом входе, а не на левом.

Потому что в рамках merge join, поток данных после сортировки действительно "правый".
И когда данные отсортированы, сначала будет прочитана 1-я запись из левого потока, а только потом 1-я запись из правого потока.
17 фев 21, 11:41    [22282120]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
Андрей_Батькович
Member

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

Правильно ли я понимаю, что оптимизатор по какой-то (неведомой нам причине) решил на левый вход для MJ подать результат чтения первой таблицы, а на правый, результат чтения второй таблицы. Хотя там идет операция Concatenation и по идее порядок вообще не важен.

При выполнении, СУБД увидела что в правом входе есть Sort, и выполнять чтение в левом бессмысленно, пока не выполнен Sort.
Идет чтение второй таблицы, потом идет Sort и данные подаются в MJ.
Потом идет чтение первой таблицы из левого входа и только после этого идет объединение.

Все верно?

Просто действительно хочу разобраться в нюансах на будущее, что бы правильно читать планы. Вчера убил весь день, что бы понять почему происходит дедлок пока не возник этот когнитивный диссонанс.
17 фев 21, 12:45    [22282172]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
msLex
Member

Откуда:
Сообщений: 8718
Андрей_Батькович
msLex,

Правильно ли я понимаю, что оптимизатор по какой-то (неведомой нам причине) решил на левый вход для MJ подать результат чтения первой таблицы, а на правый, результат чтения второй таблицы. Хотя там идет операция Concatenation и по идее порядок вообще не важен.

При выполнении, СУБД увидела что в правом входе есть Sort, и выполнять чтение в левом бессмысленно, пока не выполнен Sort.
Идет чтение второй таблицы, потом идет Sort и данные подаются в MJ.
Потом идет чтение первой таблицы из левого входа и только после этого идет объединение.

Все верно?

Просто действительно хочу разобраться в нюансах на будущее, что бы правильно читать планы. Вчера убил весь день, что бы понять почему происходит дедлок пока не возник этот когнитивный диссонанс.


Судя по плану, порядку наложения блокировок и логике почти так.

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

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

Сообщение было отредактировано: 17 фев 21, 12:46
17 фев 21, 12:53    [22282186]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
Андрей_Батькович
Member

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

Теперь понял, спасибо!

Очень конечно меня удивляет этот факт, всегда думал что порядок отображения в плане, совпадает с порядком выполнения операторов.

Ушел думать о несовершенстве бытия.
17 фев 21, 13:00    [22282197]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9634
Андрей_Батькович,

Алгоритм merge join - https://sqlserverfast.com/epr/merge-join/
Там же, в разделе "Concatenation", описан ваш случай.
17 фев 21, 13:11    [22282220]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
Андрей_Батькович
Member

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

Прочитал, но свой случай, к сожалению, там не увидел.
17 фев 21, 13:37    [22282246]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34664
Андрей_Батькович
Коллеги, приветствую!

Помогите не сойти с ума -)
Выполняю запрос, в плане вижу, что чтение идет сначала из таблицы AccumRgT106, потом из AccumRg102.
Читаю план как обычно, справа на лево, сверху вниз.

Но блокировки по какой-то причине накладываются сначала на таблицу AccumRg102, и только потом на AccumRgT106.
Проверял с помощью расширенных событий, скрины трассировки и сам план запроса прикладываю.

Почему так происходит? Что я делаю не так?


А кто тебе вообще сказал, что порядок блокировки таблиц как-то вообще определён, на ещё и определяется планом?
17 фев 21, 15:36    [22282337]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
Андрей_Батькович
Member

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

Давайте все таки на Вы.
А чем же он по вашему определяется?
17 фев 21, 15:43    [22282340]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
Владислав Колосов
Member

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

разумеется планом, чем же еще? Существует поставщик строк, который не отображается на плане. Этот поставщик действует согласно плана, до прохождения блокирующего оператора он может не накладывать блокировки для чтения данных таблиц, которые используются позже, согласно плана запроса.
17 фев 21, 16:10    [22282357]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9634
msLex
В "нижней" ветки у вас sort. Это блокирующий оператор, и до его окончания нет смысле получать "верхний" поток.
Тогда почему это не так, когда на обоих входах есть блокирующие итераторы?
17 фев 21, 16:55    [22282370]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
msLex
Member

Откуда:
Сообщений: 8718
invm
Тогда почему это не так, когда на обоих входах есть блокирующие итераторы?

а что происходит в случае двух блокирующих операторов?
каков порядок наложения блокировок?
17 фев 21, 17:16    [22282378]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
invm
Member

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

+
use tempdb;
set ansi_nulls, quoted_identifier, xact_abort, nocount on;
go

create table dbo.t1 (id int constraint PK_t1 primary key);
create table dbo.t2 (id int);

insert into dbo.t1 (id) values (1);
insert into dbo.t2 (id) values (2);

update statistics dbo.t1 with rowcount = 10000;
update statistics dbo.t2 with rowcount = 10000;

select 't1', %%lockres%%, id from dbo.t1;
select 't2', %%lockres%%, id from dbo.t2;
go

set statistics xml on;
dbcc traceon(1200, 3604, -1);
select
 t.id
from
 (
  select id from dbo.t1 with (repeatableread)
  union all
  select id from dbo.t2 with (repeatableread)
 ) t (id)
order by
 t.id
option
 (merge join);
dbcc traceoff(1200, 3604, -1);
set statistics xml off;

alter table dbo.t1 drop constraint PK_t1;
select 't1', %%lockres%%, id from dbo.t1;

print '';
print '----------------------------------------------------------------------';
print '';

set statistics xml on;
dbcc traceon(1200, 3604, -1);
select
 t.id
from
 (
  select id from dbo.t1 with (repeatableread)
  union all
  select id from dbo.t2 with (repeatableread)
 ) t (id)
order by
 t.id
option
 (merge join);
dbcc traceoff(1200, 3604, -1);
set statistics xml off;
go

drop table dbo.t1, dbo.t2;
go

Process 70 acquiring IS lock on OBJECT: 2:498816839:0  (class bit2000000 ref1) result: OK
Process 70 acquiring IS lock on OBJECT: 2:466816725:0 (class bit2000000 ref1) result: OK
Process 70 acquiring IS lock on PAGE: 2:4:224 (class bit2000000 ref0) result: OK
Process 70 acquiring S lock on RID: 2:4:224:0 (class bit2000000 ref0) result: OK
Process 70 acquiring IS lock on PAGE: 2:5:168 (class bit2000000 ref0) result: OK
Process 70 acquiring S lock on KEY: 2:4035225289354772480 (8194443284a0) (class bit2000000 ref1) result: OK
Process 70 releasing lock reference on KEY: 2:4035225289354772480 (8194443284a0)

Process 70 acquiring IS lock on OBJECT: 2:498816839:0 (class bit2000000 ref1) result: OK
Process 70 acquiring IS lock on OBJECT: 2:466816725:0 (class bit2000000 ref1) result: OK
Process 70 acquiring IS lock on PAGE: 2:5:168 (class bit2000000 ref0) result: OK
Process 70 acquiring S lock on RID: 2:5:168:0 (class bit2000000 ref0) result: OK
Process 70 acquiring IS lock on PAGE: 2:4:224 (class bit2000000 ref0) result: OK
Process 70 acquiring S lock on RID: 2:4:224:0 (class bit2000000 ref0) result: OK

17 фев 21, 19:08    [22282437]     Ответить | Цитировать Сообщить модератору
 Re: Порядок блокировки таблиц не соответствует плану запроса  [new]
Андрей_Батькович
Member

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

Спасибо вам большое за пример!

Еще раз убеждаюсь что поведение не очевидное, и больше похоже на недоработку.
Не вижу причин по которым нельзя было бы правый поток сделать первым на обработку (для конкатенации вроде порядок роли не играет), тогда и путаницы с чтением плана не было бы.
17 фев 21, 20:18    [22282488]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Microsoft SQL Server Ответить