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

drop database db1;
go

create database db1;
go

use db1;
go

set nocount on;
go

-- в таблице будут перекошенные по id данные
-- 1 строка с id = 1
-- 1 строка с id = 2
-- 1000 000 строк с id = 1000
;with
l0(n) as (select 1 union all select 1),
l1(n) as (select 1 from l0 t1, l0 t2),
l2(n) as (select 1 from l1 t1, l1 t2),
l3(n) as (select 1 from l2 t1, l2 t2),
l4(n) as (select 1 from l3 t1, l3 t2),
l5(n) as (select 1 from l4 t1, l4 t2),
rt(n) as (select row_number() over(order by (select 0)) n from l5 t1, l5 t2)
select top(1000000)
	1000 as id,
	replicate('a', 100) as data
into
	t1
from
	rt;
go
insert into t1(id, data)
values
	(1, replicate('b', 100)),
	(2, replicate('c', 100));
go
create index idx_t1_id on t1(id);
go

-- чтобы первая же попавшаяся константа 
-- в запросе повлияла на план запроса

alter database db1 set parameterization forced;
go

exec sys.sp_create_plan_guide
	@name = N'planguide_for_id_1000',
	@stmt = N'select min(data) from t1 where t1.id = 1000',
	@type = N'SQL',
	@module_or_batch = null,
	@params = null,
	@hints = 'option(table hint(t1, index(0)))'
go

set statistics io on;
set statistics xml on;

-- подсуним оптимизатору константу для которой есть одна только строка
-- в плане ожидаемо index seek + rid lookup
select min(data) from t1 where t1.id = 1
go

-- подсуним оптимизатору запрос с константой id = 1000
-- для которой есть 1000 000 записей. так как parameterization forced,
-- то план должен быть как у предыдущего запроса, если не принять меры

-- так как создал planguide выше, то ожидал fullscan, а planguide не подхватывается
-- и вылазит indexseek + rid lookup
select min(data) from t1 where t1.id = 1000
go

-- такой план я ожидал
select min(data) from t1 with(index(0)) where t1.id = 1000
go

set statistics io off;
set statistics xml off;



почему не подхватывает planguide?
10 апр 14, 11:36    [15860498]     Ответить | Цитировать Сообщить модератору
 Re: почему не подцепляется planguide  [new]
почемучка000001
Guest
Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
	Feb 10 2012 19:39:15 
	Copyright (c) Microsoft Corporation
	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
10 апр 14, 11:45    [15860580]     Ответить | Цитировать Сообщить модератору
 Re: почему не подцепляется planguide  [new]
SomewhereSomehow
Member

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

+
use master;
go
drop database db1;
go

create database db1;
go

use db1;
go

set nocount on;
go

-- в таблице будут перекошенные по id данные
-- 1 строка с id = 1
-- 1 строка с id = 2
-- 1000 000 строк с id = 1000
;with
l0(n) as (select 1 union all select 1),
l1(n) as (select 1 from l0 t1, l0 t2),
l2(n) as (select 1 from l1 t1, l1 t2),
l3(n) as (select 1 from l2 t1, l2 t2),
l4(n) as (select 1 from l3 t1, l3 t2),
l5(n) as (select 1 from l4 t1, l4 t2),
rt(n) as (select row_number() over(order by (select 0)) n from l5 t1, l5 t2)
select top(1000000)
	1000 as id,
	replicate('a', 100) as data
into
	t1
from
	rt;
go
insert into t1(id, data)
values
	(1, replicate('b', 100)),
	(2, replicate('c', 100));
go
create index idx_t1_id on t1(id);
go

-- чтобы первая же попавшаяся константа 
-- в запросе повлияла на план запроса

alter database db1 set parameterization forced;
go

exec sys.sp_create_plan_guide
	@name = N'planguide_for_id_1000',
	@stmt = N'select min(data) from t1 where t1.id = 1000
',
	@type = N'SQL',
	@module_or_batch = null,
	@params = null,
	@hints = 'option(table hint(t1, index(0)))'
go

set statistics io on;
set statistics xml on;

-- подсуним оптимизатору константу для которой есть одна только строка
-- в плане ожидаемо index seek + rid lookup
select min(data) from t1 where t1.id = 1
go

-- подсуним оптимизатору запрос с константой id = 1000
-- для которой есть 1000 000 записей. так как parameterization forced,
-- то план должен быть как у предыдущего запроса, если не принять меры

-- так как создал planguide выше, то ожидал fullscan, а planguide не подхватывается
-- и вылазит indexseek + rid lookup
go
select min(data) from t1 where t1.id = 1000
go

-- такой план я ожидал
select min(data) from t1 with(index(0)) where t1.id = 1000
go

set statistics io off;
set statistics xml off;

--exec sp_control_plan_guide 'DROP', 'planguide_for_id_1000'

С разделителями GO осторожен будь, лишнее в пакете - к другому хэшу путь.
Всмысле нужно чтоб символы в гиде планов четко соответствовали тому, что передается в пакете, у вас в пакете есть: 1) лишний перенос строки 2) лишние комментарии сверху.
10 апр 14, 12:24    [15860922]     Ответить | Цитировать Сообщить модератору
 Re: почему не подцепляется planguide  [new]
почемучка000001
Guest
SomewhereSomehow,

Огромное спасибо. Никогда бы не додумался :)
10 апр 14, 12:29    [15860961]     Ответить | Цитировать Сообщить модератору
 Re: почему не подцепляется planguide  [new]
Crimean
Member

Откуда:
Сообщений: 13147
а вы подзапутались слегка. во-первых, если вы хотите заставить пользовать индекс, то писать надо не index(0) - это как раз форсит скан
то есть верный параметр вместо вашего это
@hints = N'option(table hint(t1, index(idx_t1_id)))'

во-вторых раз вы форсите параметризацию, то надо и план делать параметризованный
@stmt = N'select min ( data ) from t1 where id = @0',
@params = N'@0 int',

и в результате - все работает. но - можно и не форсить параметризацию для базы, а сделать +1 гайд с типом TEMPLATE и дать ему @hints = N'option(PARAMETERIZATION FORCED)'

собственно, у меня оба варианта с вашим репро заработали
10 апр 14, 13:29    [15861433]     Ответить | Цитировать Сообщить модератору
 Re: почему не подцепляется planguide  [new]
почемучка000001
Guest
Crimean,

пример искусственный.

все опции выбраны специально. а форсить я хотел именно скан для параметра id = 1000.
10 апр 14, 13:33    [15861465]     Ответить | Цитировать Сообщить модератору
 Re: почему не подцепляется planguide  [new]
Crimean
Member

Откуда:
Сообщений: 13147
ой. а на 2008 и без плана скан таблички и с планом скан таблички. однако. вот я в план индекс и всунул, чтобы разница была.

а с переносом строки - да! но чаще план надо на любой похожий запрос делать, чем на конкретное значение параметра, вот я по привычке с параметризацией сразу и сделал, а оно, по понятной причине, сразу и заработало.
10 апр 14, 13:38    [15861523]     Ответить | Цитировать Сообщить модератору
 Re: почему не подцепляется planguide  [new]
почемучка000001
Guest
Crimean
ой. а на 2008 и без плана скан таблички и с планом скан таблички. однако. вот я в план индекс и всунул, чтобы разница была.

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


На 2008 такое же поведение как и 2012 для этого примера.

+ коментарии отделены от запросов, иначе как выше заметил SomewhereSomehow, у батчей получаются разные хеши, и второй запрос компилится как новый под константу 1000, оптимизатор по статистике понимает что для id=1000 много строк
и решает full scan. пофиксено, чтобы все работало как задумано.

use master;
go
drop database db1;
go

create database db1;
go

use db1;
go

set nocount on;
go

-- в таблице будут перекошенные по id данные
-- 1 строка с id = 1
-- 1 строка с id = 2
-- 1000 000 строк с id = 1000
;with
l0(n) as (select 1 union all select 1),
l1(n) as (select 1 from l0 t1, l0 t2),
l2(n) as (select 1 from l1 t1, l1 t2),
l3(n) as (select 1 from l2 t1, l2 t2),
l4(n) as (select 1 from l3 t1, l3 t2),
l5(n) as (select 1 from l4 t1, l4 t2),
rt(n) as (select row_number() over(order by (select 0)) n from l5 t1, l5 t2)
select top(1000000)
	1000 as id,
	replicate('a', 100) as data
into
	t1
from
	rt;
go
insert into t1(id, data)
values
	(1, replicate('b', 100)),
	(2, replicate('c', 100));
go
create index idx_t1_id on t1(id);
go

-- чтобы первая же попавшаяся константа 
-- в запросе повлияла на план запроса

alter database db1 set parameterization forced;
go

exec sys.sp_create_plan_guide
	@name = N'planguide_for_id_1000',
	@stmt = N'select min(data) from t1 where t1.id = 1000
',
	@type = N'SQL',
	@module_or_batch = null,
	@params = null,
	@hints = 'option(table hint(t1, index(0)))'
go

set statistics io on;
set statistics xml on;

-- подсуним оптимизатору константу для которой есть одна только строка
-- в плане ожидаемо index seek + rid lookup
go
select min(data) from t1 where t1.id = 1
go

-- подсуним оптимизатору запрос с константой id = 1000
-- для которой есть 1000 000 записей. так как parameterization forced,
-- то план должен быть как у предыдущего запроса, если не принять меры

-- так как создал planguide выше, то ожидал fullscan, а planguide не подхватывается
-- и вылазит indexseek + rid lookup
go
select min(data) from t1 where t1.id = 1000
go

-- такой план я ожидал
go
select min(data) from t1 with(index(0)) where t1.id = 1000
go

set statistics io off;
set statistics xml off;

--exec sp_control_plan_guide 'DROP', 'planguide_for_id_1000'

10 апр 14, 14:49    [15862241]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить