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

Откуда: Moscow
Сообщений: 2480
Блог
Кому не лень, и у кого версия меньше или равна Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0, проверьте пожалуйста следующий запрос:
print @@version;
go
select * from sys.objects where object_id in (3) option(merge join); --hash also
go
select * from sys.objects where object_id in (3,4) option(merge join); --hash also

У меня в первом случае ошибка о невозможности построить план, во втором, ок.
Также, если это поведение джойнов уже где-то описано - буду признателен за ссылку. На коннекте и в гугле искал - не нашел.
Всем спасибо.
п.с.
В 2012 не проявляется.
15 июл 13, 16:10    [14568624]     Ответить | Цитировать Сообщить модератору
 Re: Баг оптимизатора  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Microsoft SQL Server 2008 (SP2) - 10.0.4064.0 (X64)

Та же картина
сообщение
Query processor could not produce a query plan because of the hints defined in this query.
Resubmit the query without specifying any hints and without using SET FORCEPLAN.
15 июл 13, 16:16    [14568677]     Ответить | Цитировать Сообщить модератору
 Re: Баг оптимизатора  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2861.0 (X64)
Aug 20 2012 19:28:58
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

(0 row(s) affected)
15 июл 13, 16:16    [14568684]     Ответить | Цитировать Сообщить модератору
 Re: Баг оптимизатора  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
Microsoft SQL Server 2008 (SP3) - 10.0.5828.0 (X64)

то же самое
15 июл 13, 16:25    [14568759]     Ответить | Цитировать Сообщить модератору
 Re: Баг оптимизатора  [new]
super-code
Member

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

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

(1 row(s) affected)
15 июл 13, 16:27    [14568776]     Ответить | Цитировать Сообщить модератору
 Re: Баг оптимизатора  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Не вызывает ошибку только
select * from sys.objects where object_id in (3) option(loop join); --hash also


Скорее всего связанно с тем как работают механизмы джойнов внутри "мотора"
15 июл 13, 16:28    [14568788]     Ответить | Цитировать Сообщить модератору
 Re: Баг оптимизатора  [new]
Гость333
Member

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

Воспроизвелось на версиях:
Microsoft SQL Server 2005 - 9.00.4053.00 (Intel IA-64)
Microsoft SQL Server 2005 - 9.00.5057.00 (X64)
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)
15 июл 13, 16:44    [14568933]     Ответить | Цитировать Сообщить модератору
 Re: Баг оптимизатора  [new]
baracs
Member

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

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2811.0 (X64)
Apr 6 2012 01:59:29
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Msg 8622, Level 16, State 1, Line 1
Обработчик запросов не может предоставить план запроса из-за подсказок, определенных в запросе. Заново запустите запрос без указания подсказок и без использования SET FORCEPLAN.

(1 row(s) affected)
15 июл 13, 16:49    [14568991]     Ответить | Цитировать Сообщить модератору
 Re: Баг оптимизатора  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Всем спасибо! Значит не мерещится.

Maxx,
Если в смысле "мотора" имеется ввиду execution engine, то до туда не доходит, падает еще на этапе построения плана. Пока, затрудняюсь сказать с чем связано, пойду как раз сейчас домой и по-изучаю.
15 июл 13, 16:50    [14569012]     Ответить | Цитировать Сообщить модератору
 Re: Баг оптимизатора  [new]
StarikNavy
Member

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

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)
Apr 22 2011 19:23:43
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Сообщение 8622, уровень 16, состояние 1, строка 1
Обработчик запросов не может предоставить план запроса из-за подсказок, определенных в запросе. Заново запустите запрос без указания подсказок и без использования SET FORCEPLAN.
15 июл 13, 16:54    [14569060]     Ответить | Цитировать Сообщить модератору
 Re: Баг оптимизатора  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
SomewhereSomehow
немного не точно выразился..loop будет делать "перебор" и если чето не найдет ну и фиг с ним по факту,а вот для двух других (для hash , конечно с натяжкой) будет все таки пытаться "сделать" 2 набора ,видимо так сделано , что построитель планов для всех кроме loop при таком варианте пытаться сразу "просчитать" наборы ,а передан только 1 набор и он сразу дает от ворот поворот. (Я так себе могу пояснить,почему такое поведение ,косвенно здесь подтверждание,хотя уверен сие вы читали)
Если чет раскопаете - делитесь .
15 июл 13, 17:21    [14569319]     Ответить | Цитировать Сообщить модератору
 Re: Баг оптимизатора  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
SomewhereSomehow
Кому не лень, и у кого версия меньше или равна Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0, проверьте пожалуйста следующий запрос:
print @@version;
go
select * from sys.objects where object_id in (3) option(merge join); --hash also
go
select * from sys.objects where object_id in (3,4) option(merge join); --hash also

У меня в первом случае ошибка о невозможности построить план, во втором, ок.
Также, если это поведение джойнов уже где-то описано - буду признателен за ссылку. На коннекте и в гугле искал - не нашел.
Всем спасибо.
п.с.
В 2012 не проявляется.

пришлось поправить название таблиц и колонок
print @@version;
go
select * from sysobjects where id in (3) option(merge join); --hash also
go
select * from sysobjects where id in (3,4) option(merge join); --hash also

Microsoft SQL Server  2000 - 8.00.2040 (Intel X86) 
	May 13 2005 18:33:17 
	Copyright (c) 1988-2003 Microsoft Corporation
	Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)


(1 row(s) affected)

(2 row(s) affected)
16 июл 13, 08:12    [14570923]     Ответить | Цитировать Сообщить модератору
 Re: Баг оптимизатора  [new]
Maxx
Member [скрыт]

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

sysobjects - содержит в себе UNION
sys.objects - нет
16 июл 13, 10:46    [14571648]     Ответить | Цитировать Сообщить модератору
 Re: Баг оптимизатора  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Maxx
HandKot,

sysobjects - содержит в себе UNION
sys.objects - нет
В 2000-м? А разве там sysobjects не таблицей была?
16 июл 13, 10:48    [14571668]     Ответить | Цитировать Сообщить модератору
 Re: Баг оптимизатора  [new]
Maxx
Member [скрыт]

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

ой я не помню , помоему в 2000 ето все таки была таблица.... сорри версию не досмотрел в 2000 вообще в схеме sys помоему ничего не существовало
16 июл 13, 10:51    [14571700]     Ответить | Цитировать Сообщить модератору
 Re: Баг оптимизатора  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
iap
Maxx
HandKot,

sysobjects - содержит в себе UNION
sys.objects - нет
В 2000-м? А разве там sysobjects не таблицей была?


ага таблицей, тоже не сразу посмотрел
так что мой пост не в счет :)
16 июл 13, 11:34    [14572026]     Ответить | Цитировать Сообщить модератору
 Re: Баг оптимизатора  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
В общем, примерно понятно, в чем дело. Оптимизатор сам себя обхитряет. Большой практической ценности бага не имеет, имхо, по крайней мере, в моем случае, т.к. существенного влияния на производительность не оказало. Но обратил внимание потому, что присутствие NL в плане было не очень логичным.

Однако эффекты недоработка производит забавные. Желающим понаблюдать ниже примеры.
+ БД opt

create database opt;
go
use opt;
go
create table t1(a int primary key, b int not null, c int check (c between 1 and 50));
create table t2(b int primary key, c int, d char(10));
create table t3(c int primary key);
go
insert into t1(a,b,c) select number, number%100+1, number%50+1 from master..spt_values where type = 'p' and number between 1 and 1000;
insert into t2(b,c) select number, number%100+1 from master..spt_values where type = 'p' and number between 1 and 1000;
insert into t3(c) select number from master..spt_values where type = 'p' and number between 1 and 1000;
go
alter table t1 add constraint fk_t2_b foreign key (b) references t2(b);


+ Забавные эффекты

/* 1. */
use opt;
go
--Error
select *
from
	t1
	join t2 on t1.a = t2.b
where
	t1.a = 1
option(recompile,merge join,hash join);
go
--Ok
select *
from
	t1
	join t2 on t1.a+1 = t2.b
where
	t1.a = 1
option(recompile,merge join,hash join);
go

/* 2. */
--Error
select *
from
	t1
	join t2 on t1.a = t2.b
where
	t1.a in (1)
option(recompile,merge join,hash join);
go
--Ok
select *
from
	t1
	join t2 on t1.a+1 = t2.b
where
	t1.a in (1,2)
option(recompile,merge join,hash join);
go

/* 3. */
--Error
select *
from
	t1
	join t2 on t1.a = t2.b and t1.b = t2.c
where
	t1.a = 1 and
	t1.b = 1
option(recompile,merge join,hash join);
go
--Ok
select *
from
	t1
	join t2 on t1.a = t2.b and t1.b = t2.c
where
	t1.a = 1
option(recompile,merge join,hash join);
go

/* 4. */
--Error
declare @a int = null;
select 
	*
from 
	t1 
	join t2 on t1.a = t2.b
where
	t1.a = @a or @a is null	
option(recompile, merge join);
go
--Ok
declare @a int = 1;
select 
	*
from 
	t1 
	join t2 on t1.a = t2.b
where
	t1.a = @a or @a is null	
option(recompile, merge join);



Причина оказалась в том, как оптимизатор выполняет упрощение дерева операторов.

До того, как он доходит до самой оптимизации, он всячески пытается упростить дерево операторов. В частности он проталкивает предикаты вниз на уровень доступа к данным, при этом он умеет учитывать транзитивные зависимости. Т.е. если t1.a = t2.b, и t1.a = 1, то значит что и t2.b = 1. И вот что дальше делает оптимизатор - он исключает из дерева само условие соединения t1.a = t2.b, превращая его в некое подобие кросс-джойна. И хотя, на этом этапе все ок, но когда начинается сама оптимизация и поиск альтернатив, то если исключен NL, остаются только SM, HS, которые требуют как минимум один equi предикат в условии соединения.

И вот тут отличие 2005-2008R2 от 2012, в 2012 в методе построения SM джойна есть расширение функционала в виде дополнительных методов, типа PexprConstructColumnEqualsColumnPredicate, которое строит предикат необходимый джойну и в итоге в плане получается такое условие соединения ([opt].[dbo].[t2].b) = ([opt].[dbo].[t1].a) для мерджа.

Насчет 2000, нужно посмотреть на примерах с джойнами, у меня нет инстанса под рукой. Оптимизатор вроде сильно изменился от версии 2000 к 2005, так что не знаю.

В общем, получается, что если оптимизатор может исключить условие соединения, то он сам себя ограничивает только NL. Постараюсь найти время написать про это подробнее в блоге.
16 июл 13, 11:49    [14572147]     Ответить | Цитировать Сообщить модератору
 Re: Баг оптимизатора  [new]
SomewhereSomehow
Member

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

Во втором примере только t1.a+1 уже можно убрать оставив просто t1.a, иначе непонятно что иллюстрирует пример. Опечатался я.
+ * 2. *
/* 2. */
--Error
select *
from
	t1
	join t2 on t1.a = t2.b
where
	t1.a in (1)
option(recompile,merge join,hash join);
go
--Ok
select *
from
	t1
	join t2 on t1.a = t2.b
where
	t1.a in (1,2)
option(recompile,merge join,hash join);
go
16 июл 13, 11:52    [14572182]     Ответить | Цитировать Сообщить модератору
 Re: Баг оптимизатора  [new]
Гость333
Member

Откуда:
Сообщений: 3683
SomewhereSomehow
Забавные эффекты

Насчет 2000, нужно посмотреть на примерах с джойнами, у меня нет инстанса под рукой.

У меня есть "Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)".
В нём нет опции recompile для запросов, поэтому я её убрал.
Первые 3 эффекта повторились, как для версии 2008R2.

В четвёртом эффекте вы, наверное, перепутали местами "Error" и "Ok"? Ибо запрос, помеченный "Error", выполняется успешно, а запрос "Ok" — выдаёт ошибку (на версии 2008R2).

На версии 2000 оба запроса из "четвёртого эффекта" отработали нормально, что меня немного озадачило. Потом я понял, что это из-за опции recompile. MSSQL, выполняя запрос 4.1, перекомпилирует его, подставляя актуальное значение переменной, и получает такое:
select 
	*
from 
	t1 
	join t2 on t1.a = t2.b
where
	t1.a = null or null is null	
option(merge join);

что эквивалентно такому запросу:
select 
	*
from 
	t1 
	join t2 on t1.a = t2.b
option(merge join);

Здесь merge не смущает оптимизатор, и он строит требуемый план запроса.

В примере 4.2 после подстановки @a = 1 получается такой запрос:
select 
	*
from 
	t1 
	join t2 on t1.a = t2.b
where
	t1.a = 1
option(merge join)

Что эквивалентно запросу 1.1 (который завершается с ошибкой).

MSSQL 2000 не умеет делать подобные подстановки в план запроса в рантайме, поэтому успешно выполняет оба запроса из примера 4.

Если же убрать recompile, то и MSSQL 2005/2008 успешно выполнят пример 4.
16 июл 13, 14:54    [14573484]     Ответить | Цитировать Сообщить модератору
 Re: Баг оптимизатора  [new]
SomewhereSomehow
Member

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

Да, совершенно верно, в 4м тож опечатался =).
Все остальное тоже понятно, спасибо что потестили на 2000.
16 июл 13, 15:18    [14573722]     Ответить | Цитировать Сообщить модератору
 Re: Баг оптимизатора  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
А про 2014 будет в вашем блоге?
Конечно хорошо что они фиксят такие откровенные баги, но 2014 они же хвалят как что-то особенное.
Да и багом это нельзя назвать, ибо баги только в допиленных идеальных вещах. А тут зачатки системы оптимизации, им ещё пилить и пилить. :)

OffTop
И ещё, как знатока оптимизатора планов:
Вы более познаёте со стороны или имеете "доступ к тайным знаниям"? Имеете ли связи с группой разрабов?
Интересуют вопросы по развитию оптимизатора в целом. К примеру (с потолка) использование GPU для параллельному поиску оптимального плана, к примеру.

Ссори за ламерность вопроса. И возможно уже спрашивал(и).

PS: Ну да, закатал я губень, что хочу аналога Феймана, но по теории БД. Популяризаторы всегда в спросе.
16 июл 13, 18:08    [14574777]     Ответить | Цитировать Сообщить модератору
 Re: Баг оптимизатора  [new]
SomewhereSomehow
Member

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

До 2014 очень хотелось бы добраться, самому интересно, но пока не получается со временем.
Тайных знаний нет, при личном знакомстве они тоже ничего секретного не выдадут =)
Касательно общего развития, лучше спросить местных MVP, знаю, что по крайней мере раньше они вели какие-то дискуссии с группой разработчиков на тему, как сейчас не знаю.
16 июл 13, 20:04    [14575152]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить