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

Откуда: Moscow Square
Сообщений: 624
Добрый день
Пытаюсь впервые разобраться с партицированием. Задача: убедиться по плану запроса, что выбираются данные только из той партиции, в которой лежат нужные данные.

Нашел статью коллеги Гладченко тынц
где написано следующее:
если выполнить
create partition function pf(int) as range for values (0, 10, 100)
create partition scheme ps as partition pf all to ([PRIMARY])
create table t (a int, b int) on ps(a)

select * from t where a < 0

то в плане выполнения будет
|–Table Scan(OBJECT:([t]), WHERE:([t].[a]<(0)) PARTITION ID:((1)))

Однако, когда указанное выполняю я, вижу:
|--Table Scan(OBJECT:([master].[dbo].[t]), SEEK:([PtnId1001] >= (1) AND [PtnId1001] <= RangePartitionNew(CONVERT_IMPLICIT(int,[@1],0),(0),(0),(10),(100))), WHERE:([master].[dbo].[t].[a]<CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)

Что я делаю не так?
5 мар 19, 15:46    [21825495]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
Oblom,

У вас запрос параметризировался и вы столкнулись с так называемым dynamic partition elimination.

если добавить option (recompile) получите что хотите
5 мар 19, 16:10    [21825521]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
felix_ff
Oblom,
У вас запрос параметризировался и вы столкнулись с так называемым dynamic partition elimination.
если добавить option (recompile) получите что хотите

Запустил с recompile, получил:
|--Table Scan(OBJECT:([master].[dbo].[t]), SEEK:([PtnId1001]=(1)), WHERE:([master].[dbo].[t].[a]<(0)) ORDERED FORWARD)

Вроде непохоже на пример из статьи, или это одно и то же, записанное разными словами?
И как уйти от dynamic partition elimination не используя RECOMPILE? а то для промышленных решений как-то жестковато.
5 мар 19, 16:40    [21825580]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
Oblom
И как уйти от dynamic partition elimination
Зачем? Неэстетично выглядит?

Вам вообще для чего секционирование? Веяние моды?
5 мар 19, 16:58    [21825595]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
invm
Oblom
И как уйти от dynamic partition elimination
Зачем? Неэстетично выглядит?

Вам вообще для чего секционирование? Веяние моды?


Общая задача:
Есть DWH с несколькими таблицами фактов более 100 млн. каждая
Есть кубы на этих таблицах секционированных по годам, код партиций в кубе вида "WHERE Date >='20180101' AND Date < '20190101' "
Ежедневно пересчитываются партиции последнего года или последних двух лет.
Показалось разумным секционировать таблицы фактов синхронно кубам, чтобы ускорить ежедневный пересчет кубов за счет того, что выборка будет не по всей таблице, а по 1-2 последним партициям.

Мне теперь надо понять по плану запроса, что моё секционирование решило поставленную задачу.
5 мар 19, 17:08    [21825602]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
Oblom
чтобы ускорить ежедневный пересчет кубов
Секционирование - средство администрирования, а не ускорения. Кластерный индекс справится не хуже.
А если на ваших таблицах уже есть индексы, то секционирование еще и добавит головной боли.
Oblom
Мне теперь надо понять по плану запроса, что моё секционирование решило поставленную задачу.
Не пробовали почитать что такое "dynamic partition elimination"?
5 мар 19, 17:46    [21825645]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
Oblom,

у Фридмана хорошо описан сам процесс:
почитайте https://blogs.msdn.microsoft.com/craigfr/2006/11/27/introduction-to-partitioned-tables/
5 мар 19, 17:50    [21825649]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30766
Oblom
invm
Вам вообще для чего секционирование? Веяние моды?
Показалось разумным секционировать таблицы фактов синхронно кубам, чтобы ускорить ежедневный пересчет кубов за счет того, что выборка будет не по всей таблице, а по 1-2 последним партициям.
Понятно, веяние моды.
5 мар 19, 19:49    [21825728]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
invm
Не пробовали почитать что такое "dynamic partition elimination"?


Попробовал, почитал и понял. Более того, в моем случае, его таки нет, а если и будет, то не смертельно.

Хоть бы раз здесь в моей теме не написали "вам это нафиг не надо", вместо того, чтобы хотя бы кинуть ссыль на документацию, которую надо почитать. Если уж своими словами лениво объяснить, имея опыт.
Весь опыт по любой теме всегда сводится к "вам это нафиг не надо".

Спасибо всем, вопрос закрыт.
6 мар 19, 09:34    [21825924]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
Владислав Колосов
Member

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

кубы обновляют при помощи SSIS и change tracking, если не ошибаюсь.
6 мар 19, 11:39    [21826049]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
Владислав Колосов
Member

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

Типы должны быть приведены явно к типу аргумента функции секционирования:

select * from t where a < cast(0 as int)


Если не соблюдать это правило, можете получить просмотр всех секций. В вашем случае просмотрена была только одна. Если "шашечки" важнее, чем "ехать", то проблема, наверное, есть.
6 мар 19, 11:49    [21826065]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
Владислав Колосов
Типы должны быть приведены явно к типу аргумента функции секционирования
Сможете объяснить как же тогда работает исключение секций в вышеприведенном примере?
6 мар 19, 12:15    [21826102]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2375
Oblom

Хоть бы раз здесь в моей теме не написали "вам это нафиг не надо", вместо того, чтобы хотя бы кинуть ссыль на документацию, которую надо почитать. Если уж своими словами лениво объяснить, имея опыт.

именно это вам своими словами и написали:
"Секционирование - средство администрирования, а не ускорения. Кластерный индекс справится не хуже."
6 мар 19, 12:16    [21826106]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
Владислав Колосов
Member

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

конечно же это неправда. Секции эффективны при просмотре данных в плане запроса.
6 мар 19, 12:22    [21826111]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
Владислав Колосов
Member

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

увы, нет, я не настолько детально знаю особенности движка.
6 мар 19, 12:22    [21826113]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
Владислав Колосов
Секции эффективны при просмотре данных в плане запроса.
Расшифруйте.
Владислав Колосов
увы, нет, я не настолько детально знаю особенности движка.
Тогда на основании чего вы даете такие рекомендации?
И почему ваше предложение обязательно для предикатов со столбцом секционирования, а не вообще для всех?
6 мар 19, 12:47    [21826145]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
Владислав Колосов
Member

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

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

Обязательно потому, что, как показывает практика, неявное преобразование может привести к просмотру всех секций вместо тех, которые подходят в заданные условия.
6 мар 19, 14:03    [21826289]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
Проблему легко увидеть, слегка изменив пример:

create partition function pf(int) as range for values (0, 10, 100)
create partition scheme ps as partition pf all to ([PRIMARY])
create table t (a int, b int) on ps(a)

select * from t where a < cast(0 as bigint)
6 мар 19, 14:15    [21826310]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
Владислав Колосов
насколько я видел в планах, использование секций приводит к просмотру меньшего количества данных и, вместо просмотра всех строк таблицы, запрос читает лишь строки секции, попадающую в заданный предикатом диапазон.
А соответствующий индекс будет работать как-то иначе?
Владислав Колосов
Обязательно потому, что, как показывает практика, неявное преобразование может привести к просмотру всех секций вместо тех, которые подходят в заданные условия.
Ну так почему же ваше правило не обязательно для поиска по индексу?
6 мар 19, 14:27    [21826330]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
Владислав Колосов
Member

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

хорошие вопросы, надо подумать. Вы имеете в виду поиск диапазона в кластерном индексе?
6 мар 19, 16:12    [21826484]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
felix_ff
Member

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

как по мне плюшки исключения секций в плане сайдэффекта увеличения производительности можно сравнивать только в случае сканирования индекса.

create partition function pf(int) as range for values (0, 10, 100)
create partition scheme ps as partition pf all to ([PRIMARY])
create table t (a int, b int) on ps(a)

insert into t (a, b)
select top (1000) 10, row_number() over (order by 1/0) from master.dbo.spt_values
insert into t (a, b)
select top (2000) 200, row_number() over (order by 1/0) from master.dbo.spt_values

create index ix on t(a) on ps(a)
create index ix2 on t(a) on [primary]
update statistics t with fullscan

select index_id, rows, partition_number, total_pages, used_pages 
from sys.partitions p
   join sys.allocation_units au on au.container_id = p.partition_id
where p.object_id = object_id('t')


set statistics io, time on
select a from t with(index(ix), forcescan) where a>100 --прочитает 2000 строк
go
select a from t with(index(ix2), forcescan) where a>100 --прочитает 3000 строк
go
set statistics io, time off
6 мар 19, 16:33    [21826511]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
Владислав Колосов
Member

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

у меня как раз ситуация с большим количеством ad hoc запросов и достаточно широкой таблицей, по которой все наборы индексов строить не оптимально. Разделение на секции дало многократный рост производительности, поскольку запросы обращаются к относительно небольшим интервалам временнЫх меток данных. Теперь мне интересно проверить - как изменится план и производительность, если я уберу секции и оставлю тот же кластерный индекс.
6 мар 19, 16:43    [21826525]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
Владислав Колосов
Вы имеете в виду поиск диапазона в кластерном индексе?
В любом, не требующем лукапов.

Возможность позиционирования по секции или по индексу определяется саргабельностью предиката.
В случае допустимости неявного приведения типов, саргабельность предиката зависит от приоритетов типов левой и правой части предиката.
Если тип справа приоритетнее - потребуется явное приведение.
6 мар 19, 17:38    [21826611]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
felix_ff
как по мне плюшки исключения секций в плане сайдэффекта увеличения производительности можно сравнивать только в случае сканирования индекса.
Ваш пример очень искусственный. И без форсирования сканирования не работает.

Но в реальности увеличение производительности при секционировании вполне возможно. Например, когда столбец секционирования не первый в индексе.
use tempdb;
go

create partition function pf(int) as range for values (0, 10, 50, 100);
create partition scheme ps as partition pf all to ([PRIMARY]);
create table t (a int, b int, c int, dummy char(8000) null) on ps(b);

insert into t (a, b, c)
select top (1000) 1, 0, row_number() over (order by 1/0) from master.dbo.spt_values
union all
select top (1000) 1, 10, row_number() over (order by 1/0) from master.dbo.spt_values
union all
select top (1000) 1, 50, row_number() over (order by 1/0) from master.dbo.spt_values
union all
select top (1000) 1, 100, row_number() over (order by 1/0) from master.dbo.spt_values
union all
select top (1000) 2, 0, row_number() over (order by 1/0) from master.dbo.spt_values
union all
select top (1000) 2, 10, row_number() over (order by 1/0) from master.dbo.spt_values
union all
select top (1000) 2, 50, row_number() over (order by 1/0) from master.dbo.spt_values
union all
select top (1000) 2, 100, row_number() over (order by 1/0) from master.dbo.spt_values;

create index ix on t(a, b) include (c, dummy) on ps(b)
create index ix2 on t(a, b) include (c, dummy) on [primary]
go

set statistics io, time on

declare @c int;

select @c = count(*) from t with (index = ix) where a > 0 and b in (0, 100) and c = 50;
select @c = count(*) from t with (index = ix2) where a > 0 and b in (0, 100) and c = 50;

set statistics io, time off
go

drop table t;
drop partition scheme ps;
drop partition function pf;
go
6 мар 19, 18:30    [21826654]     Ответить | Цитировать Сообщить модератору
 Re: Вступление в партицирование  [new]
Yasha123
Member

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

у меня как раз ситуация с большим количеством ad hoc запросов и достаточно широкой таблицей, по которой все наборы индексов строить не оптимально. Разделение на секции дало многократный рост производительности, поскольку запросы обращаются к относительно небольшим интервалам временнЫх меток данных. Теперь мне интересно проверить - как изменится план и производительность, если я уберу секции и оставлю тот же кластерный индекс.

так если вам "помогло" секционирование, то ключ секционирования присутствовал во всех where. значит, дата есть во всех этих таблицах и кластерный по дате вам обеспечил бы ровно такой же выборочный просмотр диапазона
6 мар 19, 19:23    [21826689]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить