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

Нужны примеры, статьи, чтобы не наступить на грабли.

Могу привести свой пример, когда производительность скорее всего упадет из-за spill в tempdb.

Например, если у нас есть секционированный некластерный ключ по полю datetime, то в запросе вида:
  declare @d datetime = getdate() 

  SELECT *
  FROM [MY].[dbo].[TBL]
  where datetime > @d
  order by datetime desc

Будет давать sort после index seek по каждой из секций:
  |--Sort(ORDER BY:([Database3].[dbo].[TBL].[datetime] DESC))
       |--Index Seek(OBJECT:([Database3].[dbo].[TBL].[idx_datetime]), SEEK:([PtnId1000] >= (1) AND [PtnId1000] <= (3) AND [Database3].[dbo].[TBL].[datetime] > [@d]) ORDERED FORWARD)


При этом аналогичный не секционированный индекс будет работать без сортировки.

Вот пример создания и заполнения самой таблицы и схемы:
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PARTITION FUNCTION [Fn_part](smallint) AS RANGE LEFT FOR VALUES (0, 100)
GO
CREATE PARTITION SCHEME [Schem_part] AS PARTITION [Fn_part] TO ([PRIMARY], [PRIMARY], [PRIMARY])
GO



CREATE TABLE [dbo].[TBL](
	[id] [smallint] NOT NULL,
	[datetime] [datetime] NOT NULL,
 CONSTRAINT [PK_b_old] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) on Schem_part(id)

CREATE NONCLUSTERED INDEX [idx_datetime] ON [dbo].[TBL]
(
	[datetime] ASC
)
INCLUDE ( 	[id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
on  Schem_part(id)
GO

GO


insert into dbo.[TBL]
select top 50 row_number() over (order by name) - 50, dateadd(day, row_number() over (order by name) - 50, getdate()) from master.sys.objects as o
union all
select top 50 row_number() over (order by name), dateadd(day, row_number() over (order by name), getdate()) from master.sys.objects as o
union all
select top 50 row_number() over (order by name) + 50, dateadd(day, row_number() over (order by name) + 50, getdate()) from master.sys.objects as o
union all
select top 50 row_number() over (order by name) + 100, dateadd(day, row_number() over (order by name) + 100, getdate()) from master.sys.objects as o
9 мар 16, 14:56    [18911443]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и падение производительности  [new]
Владислав Колосов
Member

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

а где в примере "секционированный некластерный ключ по полю datetime"?
9 мар 16, 15:24    [18911564]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и падение производительности  [new]
partitioning
Guest
Владислав Колосов,

Может быть не совсем так выразился: секционируем таблицу по id, ключ некластерный строится для каждой секции (для каждого диапазона значений id).

Вот код:
CREATE NONCLUSTERED INDEX [idx_datetime] ON [dbo].[TBL]
(
	[datetime] ASC
)
INCLUDE ( 	[id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
on  Schem_part(id)
9 мар 16, 15:26    [18911581]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и падение производительности  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
а почему тогда вы делаете выборку по дате, а не по ID?

  SELECT *
  FROM [MY].[dbo].[TBL]
  where datetime > @d
  order by datetime desc
9 мар 16, 15:31    [18911612]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и падение производительности  [new]
Владислав Колосов
Member

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

т.е. просто индекс... Я повторил Ваш пример у себя и в плане не увидел никаких дополнительных сортировок по каждой из секций.
9 мар 16, 15:32    [18911619]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и падение производительности  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
partitioning,

Option (recompile) на запросы и уйдёт поиск по всем
9 мар 16, 15:34    [18911626]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и падение производительности  [new]
Владислав Колосов
Member

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

куда он уйдёт, если столбец секционирования не участвует в фильтре запроса?
9 мар 16, 15:38    [18911650]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и падение производительности  [new]
TaPaK
Member

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

куда он уйдёт, если столбец секционирования не участвует в фильтре запроса?

вроде ж присутствует

INCLUDE ( 	[id])
9 мар 16, 15:40    [18911657]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и падение производительности  [new]
Konst_One
Member

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

куда он уйдёт, если столбец секционирования не участвует в фильтре запроса?

вроде ж присутствует

INCLUDE ( 	[id])


это не те дроиды, что вы ищите
9 мар 16, 15:41    [18911673]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и падение производительности  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Konst_One,

точно :)
9 мар 16, 15:45    [18911695]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и падение производительности  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4897
partitioning,
INCLUDE ([id]) здесь не имеет никакого смысла. Вы уже поделили индекс используя Schem_part(id).


CREATE NONCLUSTERED INDEX [idx_datetime] ON [dbo].[TBL]
(
	[datetime] ASC
)
INCLUDE ( 	[id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
on  Schem_part(id)


Может иметь смысл


CREATE NONCLUSTERED INDEX [idx_datetime] ON [dbo].[TBL]
(
	ID, [datetime] 
)
9 мар 16, 15:50    [18911729]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и падение производительности  [new]
Crimean
Member

Откуда:
Сообщений: 13147
"классика жанра" - таблица оборотов / остатков. конечно же, с датой. секционирование, безусловно, по дате. запросы типа "найти дату последнего движения" (любого типа, хоть "min/max", хоть "top 1 order by") становятся "медленными и печальными", если индекс по дате "ровнять". если индекс оставлять невыровненным - все ок, но не о том же пост, правда?
9 мар 16, 17:17    [18912254]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и падение производительности  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Еще одна жертва мифа о том что секционирование это решение для производительности?
partitioning
Например, если у нас есть секционированный некластерный ключ по полю datetime
Неа. Он у вас не по datetime больше, он у вас по (id, datetime). Вы физически поделили таблицу(индекс) на несколько частей, и теперь у вас нет больше сквозной сортировки по datetime, у вас только сортировка внутри каждой секции.
9 мар 16, 23:20    [18913225]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и падение производительности  [new]
Crimean
Member

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

> Еще одна жертва мифа о том что секционирование это решение для производительности?

ну ИНОГДА же - улучшает :D очень иногда, но все же
а так - согласен, конечно же. секции - хорошо для админов. и, часто, боль для разрабов
10 мар 16, 01:44    [18913420]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить