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

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

Если автор не указывает уровень изоляции, то я рассматриваю по умолчанию.
Изоляция по-умолчанию настраивается. ЕМНИП.

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

В курсе. Именно поэтому у нас на проекте сейчас и пересматривают весь этот код, что до нас наваяли.
Там много чиго, что мне не нравится. Но вопрос у меня сейчас больше теоретический. Чисто для моего знания и понимания.
13 ноя 14, 15:03    [16840664]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
SergeyPK
Member

Откуда:
Сообщений: 44
invm
SergeyPK
Планы одинаковые. По крайней мере сервер мне дает такой же самый актуальный план на оба запроса.
И как вы в этом убедились? Анализирую глазами?

Даже сравнил Винмержем. :)
13 ноя 14, 15:05    [16840677]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
SergeyPK
Member

Откуда:
Сообщений: 44
Glory
SergeyPK
. Планы одинаковые. По крайней мере сервер мне дает такой же самый актуальный план на оба запроса.

Вы их визуально сравнивали ? Или в текстовом виде вплоть до числа выбираемых записей и чмсла выполнений ?
Потому что Nested Loops везде выглядит одинаково, но 10 выполнений и 1000000 очень сильно влияеют на время выполнения и число чтений

Или в текстовом виде вплоть до числа выбираемых записей и чмсла выполнений.
13 ноя 14, 15:11    [16840730]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
SergeyPK
Member

Откуда:
Сообщений: 44
Glory
SergeyPK
Glory, уважаемый. Еще раз повторю. Мне не нужно, чтоб кто-то что-то у меня определил. Чисто теоретический вопрос

Какой вопрос то ?
Меня волнуют цифирки на экране ? Мне кажется, что они неправильные ?

Вопрос простой.
2 запроса. Отличаются только хинтом на одной таблице. Планы одинаковые. А время выполнение разное. Почему?
Отлично понимаю, что без реального примера врядли кто поможет. Но вдруг.
13 ноя 14, 15:18    [16840771]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Мне кажется, что чисто теоретически with (nolock), как и установка любого другого уровня изоляции транзакции на план влиять не должно. Но я тут могу ошибаться. Думаю, знающие люди могут поправить.
13 ноя 14, 15:25    [16840828]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
SergeyPK
Member

Откуда:
Сообщений: 44
Павел-П
Мне кажется, что чисто теоретически with (nolock), как и установка любого другого уровня изоляции транзакции на план влиять не должно. Но я тут могу ошибаться. Думаю, знающие люди могут поправить.

Может влиять. Сталкивался с таким.
13 ноя 14, 15:26    [16840840]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
Павел-П
Member

Откуда:
Сообщений: 234
SergeyPK
Павел-П
Мне кажется, что чисто теоретически with (nolock), как и установка любого другого уровня изоляции транзакции на план влиять не должно. Но я тут могу ошибаться. Думаю, знающие люди могут поправить.

Может влиять. Сталкивался с таким.


А пример привести можете. Ну или ссылочку, чтобы людям было полезно.
13 ноя 14, 15:28    [16840858]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
Павел-П
Member

Откуда:
Сообщений: 234
SergeyPK
Glory
пропущено...

Какой вопрос то ?
Меня волнуют цифирки на экране ? Мне кажется, что они неправильные ?

Вопрос простой.
2 запроса. Отличаются только хинтом на одной таблице. Планы одинаковые. А время выполнение разное. Почему?
Отлично понимаю, что без реального примера врядли кто поможет. Но вдруг.


А запросы на нагруженной системе выполняются. Изолируйте ваши 2 запроса от окружающего мира и выполните.
13 ноя 14, 15:29    [16840870]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
SergeyPK
Member

Откуда:
Сообщений: 44
Павел-П
SergeyPK
пропущено...

Вопрос простой.
2 запроса. Отличаются только хинтом на одной таблице. Планы одинаковые. А время выполнение разное. Почему?
Отлично понимаю, что без реального примера врядли кто поможет. Но вдруг.


А запросы на нагруженной системе выполняются. Изолируйте ваши 2 запроса от окружающего мира и выполните.

На изолированной.
P.S. Пример с разными планами попробую найти.
13 ноя 14, 15:47    [16841025]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
o-o
Guest
Павел-П
SergeyPK
...чисто теоретически with (nolock), как и установка любого другого уровня изоляции транзакции на план влиять не должно...
Может влиять. Сталкивался с таким.

А пример привести можете. Ну или ссылочку, чтобы людям было полезно.

вот не чтоб именно план, а порядок вычитывания менялся из-за использования with (nolock), на такое могу ссылку дать.
время-то поди разное будет у вычитки, но только в другую сторону...
When can allocation order scans be used?
13 ноя 14, 15:52    [16841080]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
invm
Member

Откуда: Москва
Сообщений: 9724
o-o,

У ТС (по его словам) физических чтений нет.


SergeyPK,

Ваш товарищ по несчастью - http://www.sqlservercentral.com/Forums/Topic1230597-149-1.aspx
13 ноя 14, 16:01    [16841163]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
SergeyPK
Member

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

У ТС (по его словам) физических чтений нет.


SergeyPK,

Ваш товарищ по несчастью - http://www.sqlservercentral.com/Forums/Topic1230597-149-1.aspx

Походу да. Ситуация один в один.
13 ноя 14, 16:21    [16841365]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37228
Давайте выясним версию вашего сервера.
13 ноя 14, 16:36    [16841508]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
SergeyPK
Member

Откуда:
Сообщений: 44
Гавриленко Сергей Алексеевич
Давайте выясним версию вашего сервера.

Сорри. Забыл сразу запостать.
Microsoft SQL Server 2008 (SP3) - 10.0.5846.0 (X64)
Aug 26 2013 09:39:36
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
13 ноя 14, 16:44    [16841593]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
SergeyPK
Member

Откуда:
Сообщений: 44
Павел-П
SergeyPK
пропущено...

Может влиять. Сталкивался с таким.


А пример привести можете. Ну или ссылочку, чтобы людям было полезно.


Черт, пытался вспомнить где у меня это было. Вспомнил, нашел, обрадовался. Потом расстроился.
Я добавлял это хинт в связке FTS (CONTAINSTABLE) + join на таблицу.
Только что проверил еще раз. План время от времени меняется. Ну, думаю, оно. Ан нет. Просто там сам по себе план нестабильный генерится. Даже если я поменяю алиас на таблице - может сгенерится другой.
Так что снимаю свое утверждение.
P.S. Коллега вот рядом утверждает, что видел такое с WITH NOLOCK. Но лениться вспомнить где и воспроизвести.
13 ноя 14, 18:30    [16842239]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8855
Паразитный параллелизм?, как вариант - с афигительной нагрузкой, например, на tempdb?...
14 ноя 14, 05:25    [16843731]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
SergeyPK
Member

Откуда:
Сообщений: 44
SIMPLicity_
Паразитный параллелизм?, как вариант - с афигительной нагрузкой, например, на tempdb?...

Что значит "паразитный"? В плане параллелизма нет.
14 ноя 14, 10:08    [16844288]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
уважаемый ТС,
я вот поражаюсь, что планы обоих запросов выложить не судьба или хотябы сравнить?
уже вроде как два раза просили.
с большой долей вероятности можно сказать что планы будут разные.
меняете запрос - может поменяться и план, это естественное поведение для sql server.
14 ноя 14, 14:06    [16846325]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
SergeyPK
Member

Откуда:
Сообщений: 44
Владимир Затуливетер
уважаемый ТС,
я вот поражаюсь, что планы обоих запросов выложить не судьба или хотябы сравнить?
уже вроде как два раза просили.
с большой долей вероятности можно сказать что планы будут разные.
меняете запрос - может поменяться и план, это естественное поведение для sql server.

Уважаемый Владимир Затуливетер, я вот поражаюсь, Вы вроде весь топик прочитали.
Выложить планы мне не позволяет соглашение с заказчиком. Могу Вас уверить, они совпадают от первой до последней буквы(цифры) во всем, кроме самого текста запроса. Лично сравнил 3 раза в WinMerge.
Если уж стопроцентно хотите убедится, что это так, то внизу я приведу ссылку, где описана точно такая же проблема и есть планы.

http://www.sqlservercentral.com/Forums/Topic1230597-149-1.aspx
14 ноя 14, 14:35    [16846612]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Спасибо за интересный вопрос.

Для желающих пощупать лично

Проверял на версиях:
- Microsoft SQL Server 2014 - 12.0.2342.0 (X64)
- Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64)
- Microsoft SQL Server 2008 (RTM) - 10.0.1779.0 (X64)

Проявляется везде, но могут быть разные условия.

2014 – требуется старая версия Cardinality Estimation Framework, которая включается документированным трейс флагом 9481 или уровнем совместимости БД с 2012 или более ранней версией. Иначе, план имеет другую форму и особенность не проявляется явно, а именно оба запроса работают одинаково плохо =)

2012 – В силу разницы внутренних эвристик, для той же стоимости применяется дополнительное правило и в плане появляется лишний Spool который избавляет от проблемы. В таком случае, для воспроизведения репро, требуется просто уменьшить число строк, чтобы дополнительная оптимизация со Spool не возникала (число подбирается эмпирически, глядя на план, у меня получились цифры где-то около t1 - 30 000 и t2 - 100 000).

2008 RTM – Дополнительно ничего делать не требуется

Репро (для 2014):
+
create database test;
go
use test;
go
if object_id('t1') is not null drop table t1;
if object_id('t2') is not null drop table t2;
go
create table t1(a int primary key identity, b int, c char(350) not null default(''));
create table t2(a int primary key identity, b int, c char(50) not null default(''));
go
with c as (
	select top(50000) rn=row_number() over(order by(select null)) from master..spt_values v1,master..spt_values v2,master..spt_values v3
)
insert into t1(b)
select convert(varchar(10), rn) from c;

with c as (
	select top(150000) rn=row_number() over(order by(select null)) from master..spt_values v1,master..spt_values v2,master..spt_values v3
)
insert into t2(b)
select rn from c
go
dbcc traceon(9481); -- Enable OLD CE
dbcc freeproccache; -- WARNING!!! clear cache.
go


set statistics time, xml, io on;
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 with(nolock)) option(maxdop 1);
set statistics time, xml, io off;
go


Результаты (время):
CPU time = 234 ms,  elapsed time = 394 ms.
CPU time = 5258 ms,  elapsed time = 5338 ms.

Более чем в 10 (!) раз медленнее, можно подобрать так, что разница будет в 100 и более раз.

Объяснение
Наверное, многим известно, что когда Query Processor (QP) не запрашивает сканирование индекса в определённом порядке, у Storage Engine (SE) есть две стратегии, как получить данные:
- Сканирование страниц в порядке ключа индекса (Index Order Scan)
- Сканирование страниц по картам размещения (Allocation/IAM Scan)

Для второго, помимо отсутствия требований к сортировке еще важно, чтобы данные не могли измениться в процессе сканирования – tablock, или их изменение было не важным – nolock (репро работает как с одним, так и с другим хинтом).

Теперь, посмотрим на план:
Картинка с другого сайта.

Два слова о плане в целом, а именно, почему такая сложная форма.

Дело в конструкции not in () и nullable колонке b. QP, должен обеспечить условие, если в подмножестве, которое проверятся на включение обнаружится хотя бы один null – запрос не должен ничего возвращать. Если попробовать переписать запрос через not exists, то план изменится, разница во времени исчезнет, а план станет гораздо проще.
set statistics time, xml, io on;
select * from t1 where not exists (select b from t2 where t1.b = t2.b) option(maxdop 1);
select * from t1 where not exists (select b from t2 with(nolock) where t1.b = t2.b) option(maxdop 1);
set statistics time, xml, io off;
go


Теперь, обратим внимание на выделенную часть TOP + Clustered Index Scan.

Оператор TOP, присутствует в плане, хотя мы не использовали TOP в запросе, т.к. это внутренняя оптимизация, (оправданная в данном случае и если ее отключить, оба запроса станут очень медленными), которая используется сервером для того, чтобы не сканировать весь индекс целиком, а взять только одну строку, ведь мы проверяем только вхождение – это то, о чем говорит верхний оператор Left Anti Semi Join.

Далее то, что в планах не показывается, а именно, то, как SE начинает сканирование в случае Index Order Scan и Allocation Scan.

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

Я попытался проиллюстрировать это на картинках (очень упрощенно):
Картинка с другого сайта.

Если попытаться сделать репро попроще, мы увидим тот же эффект (для форсирования нужной формы плана я использовал Force Order).
set statistics io, time on;
declare @a int;
select @a = t1.a from t1 cross apply (select top(1) b from t2) t2 option(force order, maxdop 1)
select @a = t1.a from t1 cross apply (select top(1) b from t2 with(nolock)) t2 option(force order,  maxdop 1)
set statistics io, time off;


Время:
CPU time = 93 ms,  elapsed time = 91 ms.
CPU time = 5133 ms,  elapsed time = 5138 ms.


При этом, интересным образом отображается число чтений =) Для Index Order Scan в статистику попадают все уровни, и получается 3 (глубина индекса) * 50 000 (запросов по одной странице) = 150 000 чтений. А для Allocation Order Scan страницы IAM не попадают в статистику, и получается 50 000. Выглядит парадоксально, чтений меньше, а времени больше!

Откуда время CPU? Дело в количестве инструкций, которые необходимо выполнить серверу, для того, чтобы найти либо одну страницу индекса, либо проинициализировать каждую страницу IAM.

Статистика по инструкциям из WinDbg, по получению одной (!) строки для Nested Loops (Left Anti Semi Join):
Index Order Scan: 22516 instructions
Allocation Scan: 641340 instructions
Теперь, помножить это на 50 000 и будет понятно, куда тратится процессорное время.

+ Для гиков

Если желаете пронаблюдать разницу в количестве действий, выкладываю два Call Tree для одного и другого случая в формате XML (я обычно открываю такое в XML Notepad).
Call Trees ZIP (12.5 KB)


Резюме
Чтобы не попадать в такие проблемы, нужно следовать хорошим практикам, а именно, объявлять колонки not null если там не может быть null, а если может быть null использовать not exists, ну и не использовать nolock без надобности, как «ускоритель».

Написал по мотивам Sql Server Central, т.к. там хотя бы есть планы, из которых можно все это раскрутить. ТС планы не предоставил, так что возможно у него что-то другое, но раз он сам сослался на SSC, будем считать, что тоже самое применимо и к случаю ТС.
16 ноя 14, 19:10    [16854682]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
Павел-П
Member

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

Большое спасибо за потраченное время и усилия на предыдущий пост.
16 ноя 14, 22:20    [16855320]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
SomewhereSomehow
Спасибо за интересный вопрос.


БРАВО!!!
17 ноя 14, 02:35    [16856017]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
SergeyPK
Member

Откуда:
Сообщений: 44
SomewhereSomehow
Спасибо за интересный вопрос.

Написал по мотивам Sql Server Central, т.к. там хотя бы есть планы, из которых можно все это раскрутить. ТС планы не предоставил, так что возможно у него что-то другое, но раз он сам сослался на SSC, будем считать, что тоже самое применимо и к случаю ТС.

SomewhereSomehow. Спасибо Вам большое!!! Теперь в голове все легло на свои полочки.
P.S. Планы действительно не могу предоставить по полиси. :(
17 ноя 14, 10:46    [16856918]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
SergeyPK
Member

Откуда:
Сообщений: 44
Отдельное спасибо invm за предоставленную ссылку.
17 ноя 14, 10:49    [16856935]     Ответить | Цитировать Сообщить модератору
 Re: WITH(NOLOCK) - без него значительно быстрее. Странно.  [new]
WarAnt
Member

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

Очень познавательно, спасибо!
17 ноя 14, 12:17    [16857587]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить