Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 2 [3]      все
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
SomewhereSomehow,

Великолепно! Спасибо за проделанную работу!
17 ноя 14, 20:59    [16861827]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
SomewhereSomehow, спсибо!!!))
25 фев 15, 00:49    [17307979]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
IAM Scan
Guest
А как определить, какой из сканов Index Order Scan или Allocation/IAM Scan использовался в запросе?
16 июл 15, 08:49    [17897911]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
Arm79
Member

Откуда: МО, Раменское
Сообщений: 3695
SomewhereSomehow, супер. Спасибо.
16 июл 15, 19:58    [17901581]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
o-o
Guest
извиняюсь за поднятие старой темы по прошествии чуть ли не года,
просто на нее сослался invm из свежей темы.
и хочу я спросить, что означает в данном случае IAM chain,
словами и на картинке.
в моем понимании IAM-страницы объединяются в цепочки,
когда одному объекту соответствует белее одной IAM-страницы.
ну там таблица размазана по нескольким файлам, или размером не вышла: вылезла за 4Гб,
или присутствует секционирование, или там IN_ROW_DATA, LOB_DATA, ROW_OVERFLOW_DATA.
но я беру репро прямо из этого топика, у меня сервер 2008 R2,
поэтому никаких изменений не вношу, запускаю как есть, наблюдаю эффект.
в то же время не наблюдаю никаких IAM-цепочек.
2 таблицы -- 2 IAM-страницы, по одной на каждую таблицу.
откуда бы взяться цепочкам, если база имеет один файл данных, таблицы не переваливают за 4Гб,
секционирования нет, аllocation units тоже только IN_ROW_DATA?
поясните пожалуйста.
заранее спасибо

SomewhereSomehow
Для Index Order Scan – север находит только первую страницу индекса и сразу начинает читать записи.
Для Allocation Order Scan – сервер проходит по всей цепочке IAM и только потом начинает чтение, хотя нужна ему по сути только одна страница.

Я попытался проиллюстрировать это на картинках (очень упрощенно):
Картинка с другого сайта.
8 авг 16, 13:35    [19515537]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
http://www.queryprocessor.com/nolock-and-top-optimization/
8 авг 16, 14:34    [19515874]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
o-o
Guest
так что-то там картинка поменялась и никаких IAM-цепочек и в помине нет
(а тут эти цепочки указаны как ПРИЧИНА)
тогда как же всем все было ясно?

К сообщению приложен файл. Размер - 45Kb
8 авг 16, 14:42    [19515917]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
o-o
Guest
тогда уточнение терминологии.
правильно ли я на этот раз понимаю,
что дело не в зеленых стрелках к страницам листового уровня,
а в *интерпретации* пусть даже и одной IAM-страницы?
т.е. вот эту самую битмапину надо *каждый раз полностью* расшифровывать,
ну т.е. там бит выколот или нет, а надо всего 1 страницу,
так он не в состоянии нужный бит проверить, высчитывает для каждого экстента,
allocated он или нет?
8 авг 16, 14:51    [19515969]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
SomewhereSomehow
http://www.queryprocessor.com/nolock-and-top-optimization/

Да, если кто-то сохранил себе куда-то эту тему, на всякий случай, поясню. В первый раз, я немного не туда посмотрел, перепутал. В блоге, разумеется, я это учел, но поднимать из-за этого старую тему счел бессмысленным. Суть проблемы остается одной и той же, но детали в блоге описаны более правильно, так что вместо ссылки на тему, лучше использовать ссылку на блог.
8 авг 16, 15:57    [19516460]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
o-o
Guest
спасибо
---
по-моему, стОило старую тему поднять,
потому что
1) это интересная тема и ее помнят и будут продолжать цитировать
в отличие от тем "как получить 1 января в аксессе",
поэтому актуальная информация прямо в теме не помешала бы
2) кто ж кроме вас мог знать, что надо в блоге пошариться,
ведь в теме нет прямой ссылки на нужную статью
(уже есть :)
8 авг 16, 16:04    [19516510]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
iljy
Member

Откуда:
Сообщений: 8711
o-o,

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


SomewhereSomehow

В блоге объясняется, что проблемная ветка Nested Loop предназначена для решения ситуации, когда в t2 есть NULL (тогда ничего не должно быть возвращено). Но сдается мне, что эту проблему решает следующий цикл, который с Row Count Spool. Именно в ней у операции Clustered Index Scan присутствует предикат [NoLockDB].[dbo].[t2].[b] IS NULL. А ветка, про которую речь, отрабатывает другую ситуацию - null выводится, только если запрос внутри not in не возвращает ничего:

select *
from (values (null),(0),(2)) t(x)
where x not in (select * from (values(0),(1)) tt(xx) where xx > 100)


На эту мысль меня навел предикат в Nested Loop ([NoLockDB].[dbo].[t1].[b] IS NULL), т.е. строка с t1.b is NULL проходит дальше, только если в t2 пусто.


И в итоге по плану запроса у меня возникли такие вопросы:

1. Почему в проблемной ветке TOP, а не Row Count Spool? Тут нам надо просто знать, есть ли такие строки, зачем тыкать в таблицу каждый раз вместо одной проверки?
2. Проблемная ветка вообще не должна выполняться для [t1].[b] IS NOT NULL, почему ее не отсечь фильтром запуска? Ибо - зачем тыкать 35 тысяч раз в таблицу, если это вообще не нужно?
8 авг 16, 22:27    [19517994]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
iljy,

Добрый день.

В блоге не объясняется, какая ветка плана за какое условие отвечает, это не цель статьи. Просто говорится, чем обусловлен такой сложный план, который содержит всего две таблицы, в частности, той проблемой, что запрос ничего не должен возвращать, если в подзапросе встретится NULL. Эффект с NULL в not in() ярко демонстрирует ситуацию, когда запрос перестает работать так, как задумал программист и почему не надо писать такие запросы.

Если интересно, какая ветка, за какое именно условие отвечает, это легко можно увидеть таким способом, прямо в этом же примере:

select * from t1 where b not in (select b from t2) option(maxdop 1);
select * from t1 where isnull(b,1) not in (select b from t2) option(maxdop 1);
select * from t1 where b not in (select isnull(b,1) from t2) option(maxdop 1);
select * from t1 where isnull(b,1) not in (select isnull(b,1) from t2) option(maxdop 1);

+
Картинка с другого сайта.

Ветка с TOP действительно обрабатывает условие, если на внешней стороне NL строка с NULL, то проверить, есть ли что-то на внутренней стороне.

По вопросам. Предикат применяется ко внешнему входу, не ко внутреннему, если делать Row Count Spool, то придется каждый раз читать всю таблицу и считать строки, это очень дорого. В противоположность, когда Row Count Spool используется в следующей ветке, где ко внутреннему входу можно применить предикат b IS NULL и далее уже просто проверять наличие строк в Spool. План с Row Count Spool тем не менее возможен, попробуйте посмотреть план:
select * from t1 where b not in (select b from t2) option(maxdop 1, queryruleoff BuildTop);

Картинка с другого сайта.

Но во избежание изнасилования сервера, этот запрос лучше не запускать.

Что касается Start Up фильтра, то не вижу теоретической невозможности его добавить, возможно это упущение, а возможно причина стоимостная.
9 авг 16, 12:39    [19519680]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
SomewhereSomehow,

В дополнение по форме плана, раз это оказалось интересно, скажу что такая форма порождена оптимизацией, а не необходимостью.
В самом простейшем случае, когда строк мало:
declare @t1 table (b int);
declare @t2 table (b int);
select * from @t1 where b not in (select b from @t2);

План получается также простой:
Картинка с другого сайта.
С простым и понятным предикатом.
Когда строк становится много, такой план получается слишком дорогим, поэтому начинаются ухищрения.
Можно отключить правила оптимизации, которые делают эти ухищрения, чтобы получить простую форму и сравнить стоимости:
select * from t1 where b not in (select b from t2) option(maxdop 1);
select * from t1 where b not in (select b from t2) option(maxdop 1, queryruleoff SplitLASJN, queryruleoff BuildSpool);

Первый весит 12.7057 юнитов, второй 34563.3. Что отражается в SSMS в стоимости запросов относительно друг друга даже как 0% к 100% =)
Картинка с другого сайта.
9 авг 16, 12:59    [19519799]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
iljy
Member

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

по поводу не писать такие запросы - этому учишься очень быстро :) Просто заинтересовали некоторые особенности плана, решил копнуть.
Понятно, что просто RowCount будет дорого, но ведь он может сочетаться с TOP. Это сочетание легко получить, если добавить в t2 несколько нулов, тогда во второй ветке TOP появится. Просто удивило, что в одном случае сервер додумывается до такой оптимизации, а в другом - увы.
Про StartUp Filter тоже странно, если так срабатывает ограничение по стоимости... Вообще есть ощущение, что для NL AntiSemi Join оценка количества строк работает некорректно, он офигенно промахивается в количестве строк на внутренней стороне: ожидается одна, реально - 35000. Почему - непонятно, на внешней стороне он честно ожидает 35т. Думаю, из-за этой оценки он и не делает никаких попыток что-либо с этой веткой сделать полезного.
9 авг 16, 15:23    [19520619]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
автор
Вообще есть ощущение, что для NL AntiSemi Join оценка количества строк работает некорректно, он офигенно промахивается в количестве строк на внутренней стороне: ожидается одна, реально - 35000.

Вы уверены, что правильно посмотрели? Для NL SSMS отображает так, что для достоверной картины нужно умножать Estimated Number of Rows на Estimated Number Of Executions. Plan Explorer например отображает иначе, и на картинке из предыдущего сообщения (там оценочные планы), видно, что оценивается правильно 35000 строк.

Вообще, можно не удивляться, что какие-то оптимизации, которые можно было бы сделать, сервер не делает, это вполне может быть и пробел в функциональности, особенно, если это не является распространенной болезненной точкой.
9 авг 16, 17:03    [19521108]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
iljy
Member

Откуда:
Сообщений: 8711
SomewhereSomehow
автор
Вообще есть ощущение, что для NL AntiSemi Join оценка количества строк работает некорректно, он офигенно промахивается в количестве строк на внутренней стороне: ожидается одна, реально - 35000.

Вы уверены, что правильно посмотрели? Для NL SSMS отображает так, что для достоверной картины нужно умножать Estimated Number of Rows на Estimated Number Of Executions. Plan Explorer например отображает иначе, и на картинке из предыдущего сообщения (там оценочные планы), видно, что оценивается правильно 35000 строк.

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


Да, действительно, не посмотрел на Number of Executions внутреннего элемента. Но тогда тем более странно: сервер предпочитает 35 тысяч раз обратиться к таблице, даже не попытавшись закешировать результат. not in может и не является болезненной точкой (ну по крайней мере не в этом :) ), но вложенные циклы-то сплошь и рядом используются. А для конкретно этого случая вариантов оптимизации можно кучу придумать.
10 авг 16, 08:24    [19523047]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
iljy,

Можете попробовать собрать ваши идеи в кучу, сделать какой-нибудь хороший пример и оформить MS в качестве предложения: https://connect.microsoft.com/SQLServer если предложение будет популярным, глядишь, что-нибудь действительно доделают в этой области.
10 авг 16, 14:07    [19524924]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
iljy
Member

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

надо попробовать :) спасибо за разъяснение!
10 авг 16, 18:06    [19526146]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
архивариус
Member

Откуда:
Сообщений: 163
просто к теме почему бывает медленнее...

6. “WITH (NOLOCK)” doesn’t actually mean no locking.

At some point in your career, you’re going to start using WITH (NOLOCK) on everything because it gets your query results faster. That’s not necessarily a bad idea, but it can come with some surprising side effects that Kendra discusses in her “There’s Something About Nolock” video. I’m going to focus on one of them here, though.

When you query a table – even WITH (NOLOCK) – you take out a schema stability lock. No one else can change that table or indexes until your query is finished. That doesn’t sound like a big deal until you need to drop an index, but you can’t because people are constantly querying a table, and they think there’s no overhead as long as they use WITH (NOLOCK).

There’s no silver bullet here, but start by reading about SQL Server’s isolation levels – I bet READ COMMITTED SNAPSHOT ISOLATION is an even better choice for your app. It gets you consistent data with less blocking hassles.
11 авг 16, 11:30    [19528419]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
архивариус
Member

Откуда:
Сообщений: 163
ссылка потерялась
https://www.brentozar.com/archive/2013/02/7-things-developers-should-know-about-sql-server/
11 авг 16, 11:31    [19528429]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
o-o
Guest
архивариус
просто к теме почему бывает медленнее...
6. “WITH (NOLOCK)” doesn’t actually mean no locking.
...
When you query a table – even WITH (NOLOCK) – you take out a schema stability lock. No one else can change that table or indexes until your query is finished. That doesn’t sound like a big deal until you need to drop an index, but you can’t because people are constantly querying a table, and they think there’s no overhead as long as they use WITH (NOLOCK).

так вы как бы пишете одно, а пример приводите от другого.
у вас же читатели с NOLOCK совсем не тормозятся, они счастливы.
это кто-то другой, желающий дропнуть индекс, висит и ждет.
---
в этой теме обсуждали как доступ в соответствии с IAM может оказаться менее выгодным,
чем в порядке индекса (обычно как раз наоборот)
но если вам нужен пример, когда зависнет читатель с NOLOCK
и именно на *неполученной блокировке Sch-S*, то мы такое тоже обсуждали, вот ссылка на пример:
select with (nolock) отваливается по timeout'у
11 авг 16, 12:05    [19528646]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34697
SergeyPK
iap
пропущено...
Изоляция по-умолчанию настраивается. ЕМНИП.

SergeyPK, задавайте хинт явно, а не просто убирайте его.
И никогда не пишите NOT IN(SELECT ...).
Попадётся в подселекте NULL, - и попадёте.

В курсе. Именно поэтому у нас на проекте сейчас и пересматривают весь этот код, что до нас наваяли.
Там много чиго, что мне не нравится. Но вопрос у меня сейчас больше теоретический. Чисто для моего знания и понимания.



всё просто, ты думаешь, что планы одинаковы, а они разные, поэтому ты не можешь понять, почему разница во времени и io.

они не могут быть одинаковыми, потому что разные уровни изоляции.

также важно, есть у тебя mvcc или нет, включено ли оно.

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

это тебе все знать не нужно, нужно брать планы и статистику io и сравнивать, и все.

главное то не это, а что ты будешь делать после того, как поймешь, почему это происходит.
а делать то в общем и нечего, кроме как убрать NOLOCK, так и убирай сразу...
12 авг 16, 06:50    [19532271]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 2 [3]      все
Все форумы / Microsoft SQL Server Ответить