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

Откуда:
Сообщений: 95
Добрый день.
Прошу помощи у более опытных коллег, т.к. сам познать тайный замысел оптимизатора запроса не в силах.
Есть запрос, который находится в прикрепленном файле.
При его выполнении возникают следующие вопросы:
1. В результате первого выполнения запроса, оператор сканирования некластерного индекса _Documen556_ByDocDate_TRL возвращает более 40 строк, хотя у нас в запросе стоит условие TOP 40. Почему так происходит?
2. При втором и последующий выполнениях, этот оператор возвращает 41 строку, т.е. на одну больше, чем указано в TOP.
Почему оператор возвращает на 1 строку больше?
3. Если поставить условие TOP 43 и выше, то будет скан кластерного индекса и вернется уже вся таблица, а при сортировке снова отберется 41 строка.
Почем если поставить TOP 43 идет скан кластерного индекса, а если TOP 42 идет скан некластерного?

Запрос формируется платформой 1С, поэтому прошу не задавать вопросы, зачем все это нужно и т.д.
Меня интересует, почему оптимизатор выбирает именно такой план для этого запроса.

Запрос, планы запросов и базу можно скачать здесь: https://yadi.sk/d/V8aEzDS5eQJUw
База весит всего 13 Мб.

Заранее большое спасибо за ответы.
2 фев 15, 11:55    [17205495]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы по плану запроса  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3755
не смотрел план, но телепатически
1-2. индекс неуникальный
3. статистика
2 фев 15, 12:10    [17205658]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы по плану запроса  [new]
SomewhereSomehow
Member

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

Причин может быть несколько. То, что вы выложили на ядиск - Temp.bak - 2.58 мб, Запрос - 0 Б, Plan - 2.3 КБ - явно что-то пошло не так. Да и не все будут качать оттуда. Выложите сюда просто как прикрепленный файл актуальный план в виде .sqlplan в zip
2 фев 15, 13:19    [17206239]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы по плану запроса  [new]
Андрей_Батькович
Member

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

Индекс уникальный, статистика обновлена.
2 фев 15, 14:09    [17206629]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы по плану запроса  [new]
Андрей_Батькович
Member

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

Архив перезалил: https://yadi.sk/d/qVYVILGqeQZUJ

Файл с планами и запросом во вложении

К сообщению приложен файл (Запрос и планы.zip - 10Kb) cкачать
2 фев 15, 14:13    [17206670]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы по плану запроса  [new]
хмхмхм
Guest
Андрей_Батькович
3. Если поставить условие TOP 43 и выше, то будет скан кластерного индекса и вернется уже вся таблица, а при сортировке снова отберется 41 строка.
Почем если поставить TOP 43 идет скан кластерного индекса, а если TOP 42 идет скан некластерного?


Это из-за того, что оптимизатор считает, что скан кластерного индекса обойдется дешевле, чем скан некластерного + подтягивание большого кол-ва данных (key lookup).
Если вы включите поля, которые указаны в key lookup в иднекс в качестве included, то оптимизатор выберет скан некластерного индекса всегда.
2 фев 15, 14:46    [17206919]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы по плану запроса  [new]
SomewhereSomehow
Member

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

План запроса с ТОП 40: Обратите внимание в правом верхнем Nested Loops, есть свойство - With Ordered Prefetch = true.
Это означает, что во время выполнения используется оптимизация, которая заранее запрашивает строки, которые понадобятся в будущем, т.е. это некоторый "запрос на read ahead" от оптимизатора.
Вот тут подробнее:
Random Prefetching
OPTIMIZED Nested Loops Joins

Достигается это тем, что в executable план, добавляется некоторый "скрытый" итератор, его в xml плане нет, можно увидеть только в дебаггере, этот итератор и запрашивает "лишних" строк. Чтобы не пересказывать, вот ссылка на оригинал статьи, где это хорошо описано (английский), Пол (автор) даже нарисовал картинку как это могло бы выглядеть:
Картинка с другого сайта.
Paul White - Nested Loops Prefetching

Касательно Top(43) - это вопрос стоимости, как только оптимизатор решает что сканировать всю таблицу становится дешевле, чем использовать поиск по индексу + поиск по закладкам (key lookup) он переключается на сканирование таблицы. Тот же самый механизм что и при выборе между "поиск по не покрывающему индексу + поиск по закладкам" vs "сканирование таблицы".
2 фев 15, 14:48    [17206937]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы по плану запроса  [new]
Андрей_Батькович
Member

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

Спасибо, теперь понятно почему с холодным кэшем план отличается.

Но все равно это не дает ответа на 2 вопрос.
Почему оператор возвращает всегда на 1 строку больше чем указано в параметре TOP?
Это происходит даже если кэш заполнен.
2 фев 15, 18:06    [17208248]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы по плану запроса  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Андрей_Батькович
SomewhereSomehow,
Но все равно это не дает ответа на 2 вопрос.
Почему оператор возвращает всегда на 1 строку больше чем указано в параметре TOP?
Это происходит даже если кэш заполнен.

О каком операторе вы говорите? Верхний, тот самый ТОП, возвращает 40.
Подозреваю, что речь про другие операторы, которые ниже в плане. В таком случае, можете сами себя спросить, почему вы решили, что чтобы в результате соединения получилось 40 строк, то нужно, чтобы из одной таблицы было именно 40 строк, а не 1000 строк например. Ведь 40 строк, это если каждая строка поданная на вход соединению обязательно будет соединена со строкой другого входа.
Вот, например (нет строки а=9), требуется прочитать на одну строку больше из #t2, чтобы получить 40 строк в итоге:
use tempdb;
go
create table #t1 (a int primary key);
create table #t2 (a int primary key);
insert #t1 select top(1000) row_number() over(order by(select null)) from master..spt_values v1,master..spt_values v2;
insert #t2 select top(1000) row_number() over(order by(select null)) from master..spt_values v1,master..spt_values v2;
go
-- delete row a = 9
delete from #t1 where a = 9
go
-- 41
set statistics xml on
select top(40) * from #t1 t1 join #t2 t2 on t1.a = t2.a
set statistics xml off
go
drop table #t1,#t2;

Тут как раз вроде никаких странностей нет, все довольно очевидно.
2 фев 15, 20:03    [17208819]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы по плану запроса  [new]
Андрей_Батькович
Member

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

Теперь все понятно.
Еще раз большое спасибо.
2 фев 15, 20:10    [17208859]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить