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

Откуда: Екатеринбург
Сообщений: 205
Добрый день. Столкнулся с ситуацией, в которой мне непонятно поведение оптимизатора.

С помощью тестового примера попробую воссоздать реальную ситуацию:

+Пример

use testdb
go

if object_id( 'dbo.test_table', 'U' ) is not null
drop table dbo.test_table;
go

create table dbo.test_table(
id bigint identity ( 0, 1) not null
, text_data nvarchar(1000) null
, pdt datetime not null constraint def1 default getdate()
, status_id tinyint not null constraint def2 default 100
) on [primary]
go

insert into dbo.test_table ( text_data )
select top 100000 spt1.name
from master..spt_values spt1, master..spt_values spt2

;with cte as
(
select * from dbo.test_table tablesample ( 10 percent )
)
update cte set status_id = 10

create clustered index cidx_test_table__process_datetime on dbo.test_table( pdt )
go

create nonclustered index ix_test_table__process_status_id on dbo.test_table( status_id )
go

create nonclustered index ncix_test_table_id on dbo.test_table( id )
got_id( 'dbo.test_table', 'U' ) is not null
drop table dbo.test_table;
go

create table dbo.test_table(
id bigint identity ( 0, 1) not null
, text_data nvarchar(1000) null
, pdt datetime not null constraint def1 default getdate()
, status_id tinyint not null constraint def2 default 100
) on [primary]
go

insert into dbo.test_table ( text_data )
select top 100000 spt1.name
from master..spt_values spt1, master..spt_values spt2


;with cte as
(
select * from dbo.test_table tablesample ( 10 percent )
)
update cte set status_id = 10


create clustered index cidx_test_table__process_datetime on dbo.test_table( pdt )
go

create nonclustered index ix_test_table__process_status_id on dbo.test_table( status_id )
go

create nonclustered index ncix_test_table_id on dbo.test_table( id )
go

Выполняю запросы:
+

-- Index Seek ( NonClustered )
select min( pdt )
from dbo.test_table with (nolock)
where status_id in ( 0 )

-- Index Seek ( NonClustered )
select min( pdt )
from dbo.test_table with (nolock)
where status_id in ( 10 )

-- Clustered Index Scan
select min( pdt )
from dbo.test_table with (nolock)
where status_id in ( 0, 10 )


Почему, когда в предложении where указаны два критерия поиска, оптимизатор выбирает сканирование кластерного индекса?
Насколько мне известно, оптимизатор может сделать индексное объединение

Данный пример, быть может, не до конца иллюстрирует реальную ситуацию.
Приведу результаты выполнения боевых запросов на секционированной таблице объемом примерно 50 млн. строк.
+


select min( process_datetime )
from ewunicalls_.callbase_prepaid_cross with (nolock)
where process_status_id in ( 0, 10 )

/* Результат:
Table 'callbase_prepaid_cross'. Scan count 973, logical reads 1842827, physical reads 10719, read-ahead reads 1634549, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 15522 ms, elapsed time = 262698 ms.
*/

-- Руками сделаю аналог индексного объединения
select min(pdt )
from (
select min( process_datetime ) as pdt
from ewunicalls_.callbase_prepaid_cross with (nolock)
where process_status_id in ( 0 )
union
select min( process_datetime )
from ewunicalls_.callbase_prepaid_cross with (nolock)
where process_status_id in ( 10 ) ) z

/* Результат:
Table 'callbase_prepaid_cross'. Scan count 1959, logical reads 195, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 27 ms.
*/

select min( process_datetime )
from ewunicalls_.callbase_prepaid_cross with (nolock)
where process_status_id in ( 0 )

select process_datetime
from ewunicalls_.callbase_prepaid_cross with (nolock)
where process_status_id in ( 10 )

/* Результат:
Table 'callbase_prepaid_cross'. Scan count 986, logical reads 108, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 15 ms.

Table 'callbase_prepaid_cross'. Scan count 973, logical reads 87, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 12 ms.
*/



Второй запрос, возвращает тот же результат, что и первый.
Вопрос: Почему сиквел сам не пришел к такому, вроде как, более оптимальному плану ?

Планы запросов в аттаче.
+select @@version

Microsoft SQL Server 2008 (SP3) - 10.0.5848.0 (X64)
Oct 18 2013 19:36:57
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)


К сообщению приложен файл (4 плана.sqlplan - 71Kb) cкачать
18 дек 15, 10:33    [18576582]     Ответить | Цитировать Сообщить модератору
 Re: Непонятный выбор плана  [new]
WarAnt
Member

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

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.
Msg 2714, Level 16, State 6, Line 2
There is already an object named 'test_table' in the database.
18 дек 15, 11:29    [18577036]     Ответить | Цитировать Сообщить модератору
 Re: Непонятный выбор плана  [new]
Pavel1211
Member

Откуда: Екатеринбург
Сообщений: 205
WarAnt,

да, нечаянно дважды структуру таблицы привел.
18 дек 15, 11:35    [18577097]     Ответить | Цитировать Сообщить модератору
 Re: Непонятный выбор плана  [new]
WarAnt
Member

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

используйте
либо
select min( pdt )
from dbo.test_table t join (values (0),(10)) a(a) on a.a = status_id

либо
select min( pdt )
from (select pdt 
from dbo.test_table with (nolock)
where status_id in ( 0 )
union
select pdt
from dbo.test_table with (nolock)
where status_id in ( 10 )) a


а почему, щас придет SomewhereSomehow и популярно объяснит:)
18 дек 15, 11:38    [18577114]     Ответить | Цитировать Сообщить модератору
 Re: Непонятный выбор плана  [new]
Pavel1211
Member

Откуда: Екатеринбург
Сообщений: 205
WarAnt,

Да, спасибо, я примерно так и сделал. Больше интесно, почему сиквел сам до этого не дошел )))
Тоже жду "популярного" ответа :)
18 дек 15, 11:45    [18577162]     Ответить | Цитировать Сообщить модератору
 Re: Непонятный выбор плана  [new]
SomewhereSomehow
Member

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

Добрый день.
Сегодня много дел, так что не так популярно, без картинок =)

Про index union

У вас тут нет объединения индексов даже когда вы переписали запрос. Индекс у вас один (и в репро, и в реальном плане, используется один).

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

Про выбор плана

+ дополненное репро
-- Index Seek ( NonClustered )
select min( pdt )
from dbo.test_table with (nolock)
where status_id in ( 0 )

-- Index Seek ( NonClustered )
select min( pdt )
from dbo.test_table with (nolock)
where status_id in ( 10 )

-- 1. Clustered Index Scan
select min( pdt )
from dbo.test_table with (nolock)
where status_id in ( 0, 10 )

-- 2. Index seek: much more expensive
select min( pdt )
from dbo.test_table with (forceseek)
where status_id in ( 0, 10 )

-- 3. Index Seek: more selective predicate results in an Index Seek without any hints
select min( pdt )
from dbo.test_table with (nolock)
where status_id in ( 0, /*10*/ 1 ) 

-- 4. Index Seek: without Top optimization
select min( pdt )
from dbo.test_table with (nolock)
where status_id in ( 0, 10 )
option(queryruleoff BuildTop)

-- 5. Index Seek: Alternative way to remove Top optimization - introduce vercor aggregation
select min( pdt )
from dbo.test_table with (nolock)
where status_id in ( 0, 10 )
group by ()


Здесь выбор плана такого план основан на стоимости. Сравните по стоимости запрос 1 и 2. Второй, с форсированным при помощи подсказки поиском – гораздо дороже.

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

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

Первое, предикат 10 не очень селективен и предполагается что будет выбрано довольно много строк (сравните с примером 3, где поиск не по 0,10, а по 0,1 – там поиск выбран без всяких хинтов).

И второе, он может применить оптимизацию, для поиска минимального значения при помощи ТОП, т.к. агрегат скалярный, который всегда возвращает одно значение, т.е. это эквивалентно замене: select min(a) from t, на select top(1) a from t order by a, с тем нюансом, что запрос с min вернет одну строку даже если в таблице пусто, а запрос с top не вернет ничего, поэтому, чтобы замена была эквивалентной, над запросом поиска по top, нужно еще сделать скалярный агрегат. Т.е. переписать запрос: select min(a) from (select top(1) a from t order by a) x.

Поэтому в плане stream aggregate <- top <- clustered scan.

Т.е. сервер исходя из предположения о равномерном распределении считает, что он начнет сканировать индекс в порядке кластерного ключа pdt, возьмет самое первое значение и все. Это будет выгоднее, чем перебирать все и искать минимальный или выполнять случайный доступ. Но натыкается на суровую реальность, т.к. есть предикат 0 и 10, и нужное значение никак не в первых строках, так что так и продолжат сканировать таблицу (в ссылке выше ситуация разобрана подробнее).

Если же как в случаях 4, отключить правило построения ТОП (недокументировано) или добавить пустое условие группировки (сделать агрегат векторным), то вычислять агрегат при помощи поиска первого минимального значения больше нельзя, эта оптимизация не применима и дешевле становится поиск по индексу.
18 дек 15, 14:56    [18578647]     Ответить | Цитировать Сообщить модератору
 Re: Непонятный выбор плана  [new]
Pavel1211
Member

Откуда: Екатеринбург
Сообщений: 205
Супер! Спасибо! Ознакомлюсь с вашей статьей о RowGoal.

Почему то я думал что вот такая ситуация также попадает под индексное объединение:
select min(pdt), max(pdt) from dbo.test_table


Попутно хотелось бы задать еще вопрос:
Можно ли, используя только план запроса, понять, сколько оптимизатор просканировал строк на самом деле?
18 дек 15, 17:54    [18579763]     Ответить | Цитировать Сообщить модератору
 Re: Непонятный выбор плана  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Приведенный вами запрос:
select min( pdt )
from dbo.test_table with (nolock)
where status_id in ( 0, 10 )

Не попадает под объединение индексов, это просто два поиска по индексу.

Что касается min, maх - мы не говорили об этом.
Но, по-моему, я рассказывал про эту форму плана, то ли на 24HOP Соединения то ли Агрегация, точно не помню. Где-то там можно посмотреть.

И смотрите сами, считать ли это объединением индексов или нет. По-мне - вполне можно, хотя в статье про index union, приведенной вами же, такого примера нет. (Проблема в том, что когда говоришь про оптимизатор, очень многое не документировано и нет официальных определений, на которые можно сослаться. Просто исходите из здравого смысла.)

Pavel1211
Попутно хотелось бы задать еще вопрос:
Можно ли, используя только план запроса, понять, сколько оптимизатор просканировал строк на самом деле?


Можно, но для определенных версий. Смотрите новое свойство оператора плана (но не для 2008) - Actual Rows Read.

Если есть среда, где выполнить запрос, до этого выручал флаг 9130, который упомянут в статье в моем первом ответе. Он предотвращает объединение фильтра и сканирования и позволяет посмотреть, сколько было просканировано. Правда при этом фильтр выступает как отдельный оператор, что хуже в плане производительности. Кроме того не документировано. Так что подходит только для диагностики.
18 дек 15, 19:07    [18580009]     Ответить | Цитировать Сообщить модератору
 Re: Непонятный выбор плана  [new]
Pavel1211
Member

Откуда: Екатеринбург
Сообщений: 205
Спасибо!
18 дек 15, 21:49    [18580550]     Ответить | Цитировать Сообщить модератору
 Re: Непонятный выбор плана  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2396
а не подскажут заодно уважаемые доны?
есть поле
[isFlag] [int] NULL,

как правильно создать индекс (или изменить запись условия), для ускорение выборки
...where ISNULL(NC.isFlag,0)!=0
19 дек 15, 16:33    [18582234]     Ответить | Цитировать Сообщить модератору
 Re: Непонятный выбор плана  [new]
Mike_za
Member

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

Зачем вам вообще проверка не нулл? Нулл через равно не пройдет.
Если условие не является выполняющимся крайне редко, то индекс использоваться для быстрого поиска не будет. Если же вам нужны поля кроме входящего в индекс, то он и сканироваться не будет. Ибо куча лукапов станет дороже скана
19 дек 15, 21:52    [18583219]     Ответить | Цитировать Сообщить модератору
 Re: Непонятный выбор плана  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
StarikNavy
как правильно создать индекс (или изменить запись условия), для ускорение выборки
...where ISNULL(NC.isFlag,0)!=0



where NC.isFlag !=0 or NC.isFlag is not null
20 дек 15, 12:48    [18584361]     Ответить | Цитировать Сообщить модератору
 Re: Непонятный выбор плана  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2396
Mike_za,
спасибо!

Knyazev Alexey
тоже спасибо! что-то не сообразил сразу, только наверное AND а не OR
21 дек 15, 13:20    [18588189]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить