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

Откуда:
Сообщений: 8711
invm
iljy
но гораздо больший выигрыш на таком запросе даст индекс (criteria_id, dt)
Само-собой. Только вот такой индекс бесполезен для запроса только по диапазону дат. Понятно, что можно сделать "секционирование вручную". Только вот зачем?
Итого нужно два индекса вместо одного секционированного.

Ну два индекса - не вот прям катастрофа.
invm
iljy
Или у вас в каждую секцию только одно значение критерия попадает?
Да.

Че-то это экзотика какая-то... Не, я допускаю, что может быть такая задача, но тут надо решать все очень конкретно по месту. Для такой конкретной ситуевины да, вы, по сути, эмулируете второй индекс через секции.

invm
iljy
Возвращаясь к теме, повторю: вопрос изначально не "почему не ускорило", а "почему замедлило".
А я не по теме выступаю - я алексу2 разъясняю почему секционирование таки может ускорить запрос :)


удачи
23 апр 16, 00:32    [19096619]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
aleks2
Guest
invm
aleks2
за счет чего выборка из секционированной таблицы может быть быстрее.
Это очевидно - за счет обращения только к тем секциям, которые затрагиваются запросом.

Это уже обсуждали.
Секции ничем не лучше кластерного индекса.
Обращаться придется стока же.

ЗЫ. Не надо парить неокрепший мозг чайников.
23 апр 16, 14:14    [19097431]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
invm
Member

Откуда: Москва
Сообщений: 9688
aleks2
Секции ничем не лучше кластерного индекса.
Обращаться придется стока же.
Был показан пример - 19096276
Где секционированный индекс позволяет уменьшить число чтений по сравнению с аналогичным несекционированным. Плюс избавляет от необходимости создавать еще один.
23 апр 16, 16:28    [19097640]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
aleks2
Guest
invm
aleks2
Секции ничем не лучше кластерного индекса.
Обращаться придется стока же.
Был показан пример - 19096276
Где секционированный индекс позволяет уменьшить число чтений по сравнению с аналогичным несекционированным. Плюс избавляет от необходимости создавать еще один.

Пример ради примера?
Не утруждай себя.

ЗЫ. Сеционирование ничем принципиально не отличается от кластерного индекса.
И как следствие, ничего не может дать, чего не в состоянии дать кластерный индекс.
23 апр 16, 17:44    [19097772]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37202
aleks2
invm
пропущено...
Был показан пример - 19096276
Где секционированный индекс позволяет уменьшить число чтений по сравнению с аналогичным несекционированным. Плюс избавляет от необходимости создавать еще один.

Пример ради примера?
Не утруждай себя.

ЗЫ. Сеционирование ничем принципиально не отличается от кластерного индекса.
И как следствие, ничего не может дать, чего не в состоянии дать кластерный индекс.
Может отличаться как минимум глубиной индекса.
23 апр 16, 18:10    [19097820]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
invm
Member

Откуда: Москва
Сообщений: 9688
aleks2
Сеционирование ничем принципиально не отличается от кластерного индекса.
И как следствие, ничего не может дать, чего не в состоянии дать кластерный индекс

Конечно не может. А если таки дает, то пример неверный
+ Подготовка
use tempdb;
go

create partition function pfn1(int) as range right for values
 (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
go

create partition scheme ps1 as partition pfn1 all to ([PRIMARY]);
go

create table dbo.t1 (dt datetime, g int, v int, dummy char(2000) default 'a', primary key (dt, g));
create table dbo.t2 (dt datetime, g int, v int, dummy char(2000) default 'a', primary key (dt, g) on ps1(g));
go

insert into dbo.t1
 (dt, g, v)
select top (500000)
 dateadd(minute, 15 * (row_number() over (order by rand()) - 1) / 10, '2015'),
 (row_number() over (order by rand()) - 1) % 10,
 1
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

insert into dbo.t2 select * from dbo.t1;
go
+ Тест
declare @v int, @ds datetime = '20150101', @de datetime = '20160101', @g int = 2;

print '';
print '/* dt + g ********************************************************************/';
set statistics io, time on;
select @v = sum(v) from dbo.t1 where dt > @ds and dt < @de and g = @g option (maxdop 1);
select @v = sum(v) from dbo.t2 where dt > @ds and dt < @de and g = @g option (maxdop 1);
set statistics io, time off;
print '/*****************************************************************************/';

print '';
print '/* dt ************************************************************************/';
set statistics io, time on;
select @v = sum(v) from dbo.t1 where dt > @ds and dt < @de option (maxdop 1);
select @v = sum(v) from dbo.t1 where dt > @ds and dt < @de option (maxdop 1);
set statistics io, time off;
print '/*****************************************************************************/';
go

Результат

/* dt + g ********************************************************************/
Table 't1'. Scan count 1, logical reads 117468, 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 = 110 ms, elapsed time = 120 ms.

Table 't2'. Scan count 1, logical reads 11750, 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 = 15 ms, elapsed time = 18 ms.
/*****************************************************************************/

/* dt ************************************************************************/
Table 't1'. Scan count 1, logical reads 117468, 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 = 187 ms, elapsed time = 173 ms.

Table 't1'. Scan count 1, logical reads 117468, 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 = 172 ms, elapsed time = 172 ms.
/*****************************************************************************/
23 апр 16, 18:37    [19097875]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
aleks2
Guest
Гавриленко Сергей Алексеевич
aleks2
пропущено...

Пример ради примера?
Не утруждай себя.

ЗЫ. Сеционирование ничем принципиально не отличается от кластерного индекса.
И как следствие, ничего не может дать, чего не в состоянии дать кластерный индекс.
Может отличаться как минимум глубиной индекса.

Ну да lim( log(x,2)/log(x, N) ) при x -> бесконечности сам вычислишь?
23 апр 16, 18:40    [19097879]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
aleks2
Guest
invm
aleks2
Сеционирование ничем принципиально не отличается от кластерного индекса.
И как следствие, ничего не может дать, чего не в состоянии дать кластерный индекс

Конечно не может. А если таки дает, то пример неверный


Направь свою энергию на содействие тредстартерше.
Глядишь, у нее и получится 16мс.
23 апр 16, 18:44    [19097885]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
iljy
Member

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

ну как, пообщались?
23 апр 16, 19:03    [19097930]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
invm
Member

Откуда: Москва
Сообщений: 9688
iljy
invm,

ну как, пообщались?
Ага. С предсказуемым результатом

ЗЫ: А про экзотику вы зря. Вариант вполне жизненный - таблица аудита, нужно выбирать события за период либо все, либо определенных категорий.
23 апр 16, 19:11    [19097945]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
iljy
Member

Откуда:
Сообщений: 8711
invm
ЗЫ: А про экзотику вы зря. Вариант вполне жизненный - таблица аудита, нужно выбирать события за период либо все, либо определенных категорий.


Я имел ввиду случай, когда в интервал секционирования одно значение попадает. Мне кажется, что все-таки экзотика. А для таблицы аудита, ИМХО, более полезным будет использовать секционирование по датам, и управлять секциями из серии более поздние - поближе (на быстрые носители), постарше - убираем подальше, а совсем старые - вообще отстегиваем и в архив. Но это так, от большого ума пальцем в небо.
23 апр 16, 19:24    [19097971]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
Привет!

Извините за пропажу - восстановила бэкап с копией с секционированием базы и еще раз посмотрела на план.
1) Да есть 2 MAX - но опять же в запросе есть фильтр по дате, которая является ключом секционирования.
2) и это самый важный пункт - такое ощущение что после секционирование поменялся порядок выборки из таблиц и это как раз все испортило. Дело в том, что этот запрос в 99% случаем ничего не вернет.
Там JOIN c 3мя таблицами (точнее одним представлением и 2мя таблицами), одна из которых прописана в WHERE EXISTS (представление). В несекционированной версии сервер сначала делает join с этой таблицей и потом вообще не делает JOIN с 2мя оставшимия. Как я понимаю потому что в результате JOIN получается 0 строк.
А в секционированном случае он JOIN делает со всеми 3мя. Так как таблицы немаленькие, то получается вот такой замедленный результат.
Кроме того если убрать JOIN с этими 2мя таблицами - ну ради эксперимента. Сервер все равно криво выбирает из самого вью - то есть в несекционированной версии он сразу выбирает из таблицы по PK, а в секционированном варианте - сначала делает JOIN таблиц для вью а потом еще раз довыбирает из одной из таблиц из вью по PK.
24 апр 16, 12:11    [19098920]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
Вот сам запрос

SELECT TOP ? Variable1 = Column1 FROM Schema1.Object1 WHERE Column2 = ?;

SELECT TOP ? Variable2 = Column3 FROM Schema1.Object2 WHERE Column4 = Variable3;

	
SELECT TOP ? Variable4 = Column1 FROM Schema1.Object1 WHERE Column2 = Variable5;

	
WITH Object3 AS
(
	SELECT
		MAX(Object4.Column5) as Column5
		,Variable2 as Column3
		,Variable3 as Column4
		,MAX(Object4.Column6) as Column6
		,Variable4 as Column1
		,Object4.Column7 as Column7
		,Object4.Column8 as Column8
		,Object4.Column9 as Column9
		,Object4.Column10
		,Object4.Column11
	FROM Schema1.Object5 Object4
		INNER JOIN Schema1.Object6 Object7 WITH (NOLOCK) ON
			Object7.Column5 = Object4.Column5
			AND Object7.Column4 = Variable3
			AND Object7.Column12 = Object4.Column6
	WHERE
		Object4.Column3 = Variable2
		AND Object4.Column1 = Variable4
		AND Object4.Column6 > Variable6
		AND Object4.Column6 < Variable7
	GROUP BY
		Object4.Column7, Object4.Column8, Object4.Column9, Object4.Column10, Object4.Column11
)
SELECT	Object3.Column5
		,Object3.Column4
		,Object3.Column6
		,Object3.Column1
		,Object3.Column8
		,Object3.Column9
		,Object3.Column10
		,Object3.Column11
		,Object3.Column7
		,Object8.Column13
		,Object9.Column14
		,Object9.Column15
FROM Object3
	INNER JOIN Schema1.Object10 Object8 WITH (NOLOCK) ON Object8.Column3 = Object3.Column3 AND Object8.Column8 = Object3.Column8
	INNER JOIN Schema1.Object11 Object9 WITH (NOLOCK) ON Object9.Column3 = Object3.Column3 AND Object9.Column8 = Object3.Column8 AND Object9.Column9 = Object3.Column9
WHERE
	EXISTS (
		SELECT 1 FROM Schema1.Object12 Object13 
		WHERE
			Object13.Column16 = Variable8
			AND Object13.Column3 = Object3.Column3 
			AND Object13.Column8 = Object3.Column8 
			AND Object13.Column7 = Object3.Column7
			AND Object13.Column9 = Object3.Column9
	)	          
	

Это секционированные таблицы
Schema1.Object5 Object4
Schema1.Object6

Column6 ключ секционирования - дата
Schema1.Object12 - это view из которого криво стала делаться выборка
24 апр 16, 12:18    [19098947]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
Как советовал AlanDenton я обновила статистики по всем таблицам, которые участвуют в запросе, в том числе по тем, которые входят во view, с опцией FULLSCAN.
24 апр 16, 12:19    [19098952]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
AlanDenton,

Вот что получилось если перенести EXISTS в CTE
и ниже приложу что получилось с опцией RECOMPLIE

При этом оптимизатор предлагает сделать индекс по 6 полям - ради эксперимента сделаю, но сомневаюсь что мне его дадут сделать на реальной таблице
24 апр 16, 12:34    [19098988]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
AlanDenton,

Вот EXISTS внутри CTE

К сообщению приложен файл (20160424 201603 Partitioning EXISTS IN CTE_Anonymized.sqlplan - 78Kb) cкачать
24 апр 16, 12:35    [19098990]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
AlanDenton,

тоже с опцией RECOMPILE

К сообщению приложен файл (20160424 201603 Partitioning EXISTS IN CTE RECOMPILE _Anonymized.sqlplan - 72Kb) cкачать
24 апр 16, 12:36    [19098994]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
Nimua,

Поправка - не по 6 полям, а по 3м по которым уже есть индекс и плюс включить еще 6 в INCLUDE
24 апр 16, 12:40    [19099000]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
Владислав Колосов,

Итак мой порядок действий:

1. Создала файловые группы (2х видов - для кластерного и для всех остальных), функцию секционирования и схему секционирования (соответственно тоже 2 для кластерного и для остальных)
2. Удалила все индексы на текущей таблице
3. Создала кластерный с ON схема секционирования для кластерного
4. Создала остальные индекс добавив туда ключ секционирования в конец с ON схема для остальных индексов
5. Добавила в JOIN поле секционирования между 2мя секционированными таблицами по этому полю
6. Добавила в WHERE Дата < текущей - для ключа секционирования
7. Обновила статистики с опцией FULLSCAN
24 апр 16, 12:45    [19099012]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
iljy
Member

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

поскольку индексы и хотя бы примерный DML объектов вы зажали, а выложенный ранее "план без секционирования" с этим запросом никак не бьется хотя бы по количеству задействованных объектов (генерал Иволгин все-таки искажает действительность. По привычке: противника дезинформирует), приступаем к гаданиям.
Я бы начал со сравнения ожидаемого и реального количества строк. Хрустальный шар подсказывает мне, что введение секционирования уменьшило ожидаемое количество строк при выборке из объектов 5 и 6. Соответственно, оптимизатор решил, что теперь ему выгоднее сначала выбрать из них, в потом присоединять все остальное. Если вы уверены в своей оценке, перепишите запрос с указанием принудительного порядка соединения (FORCE ORDER). Можно дальше поиграться с типами соединений, можно вообще переписать запрос на cross apply, в общем, начинается нормальная работа по оптимизации.
24 апр 16, 12:47    [19099015]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
aleks2
Guest
iljy
генерал Иволгин все-таки искажает действительность. По привычке: противника дезинформирует), приступаем к гаданиям.

Да уж. Псевдонимы доставляют.
Schema1.Object5 Object4
		INNER JOIN Schema1.Object6 Object7 


Список полей группировки запроса WITH Object3 AS
намекает, что зря там join под группировку сунут.

А облегчение серверу группировки - это гораздо эффективнее.
24 апр 16, 13:30    [19099129]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
iljy,

DML индексов на партиционированной таблице

кластерный

CREATE CLUSTERED INDEX [IX_TblMainPartition_Date] ON [dbo].[TblMainPartition]
(
	[ForeignId] ASC,
	[DateUtc] ASC
)


остальные:

ALTER TABLE [dbo].[TblMainPartition] ADD  CONSTRAINT [PK] PRIMARY KEY NONCLUSTERED 
(
	[Id] ASC,
	[DateUtc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF)
GO


CREATE NONCLUSTERED INDEX [IX_TblMainPartition_Type_Date] ON [dbo].[TblMainPartition]
(
	[ForeignId] ASC,
	[TypeId] ASC,
	[DateUtc] ASC
)
INCLUDE ( 	[Id],
	[Tbl2Id],
	[Tbl3Id])


CREATE NONCLUSTERED INDEX [IX_TblMainPartition_Id_Date] ON [dbo].[TblMainPartition]
(
	[Id] ASC,
	[DateUtc] ASC
)
INCLUDE ( 	[ForeignId],
	[TypeId],
	[Tbl3Id])
24 апр 16, 14:00    [19099196]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
iljy
Member

Откуда:
Сообщений: 8711
aleks2
Список полей группировки запроса WITH Object3 AS
намекает, что зря там join под группировку сунут.


Кстати да, зачем там вообще Object6? В качестве фильтра? Так перенесите его в WHERE, через EXISTS, вполне может серверу жизнь облегчить.

А, и еще. Очень режут глаза ваши NOLOCK. Зачем они? Основное правило использования NOLOCK состоит из 3 пунктов

1. Очень хорошо подумай, нужно ли тебе использовать NOLOCK.
2. Подумал? А теперь сядь и подумай еще раз.
3. Не используй NOLOCK.

Если у вас редко меняемая таблица и вы хотите сэкономить на блокировках, лучше поставьте TABLOCK. Если же таблица может меняться на ходу, то вы можете огрести огромное количество геморроя с грязными данными.
24 апр 16, 14:09    [19099222]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
iljy
Member

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

вот смешная вы ей богу как я должен понять, какие поля в ваших индексах соответствую каким в запросах? :)
24 апр 16, 14:11    [19099224]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование и грабли. После секционирования работает в 5 раз медленнее  [new]
Мирон1
Member

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

Вообще - то, объект6 похоже только проверка связующего.
Если так, то по идее ему место в "EXISTS".

Не знаю... нужен ли он вообще. Выборка и ключи, т. е. вся полезная нагрузка на объекте5.

И потом может для похожести стоит добавить ( NOLOCK ) на объект5 и объект12 ( в корреляционном запросе )

В принципе, если колонки на объектах 10 и 11 позволяют "GROUP BY", т. е. не БЛОБ, то можно убрать СТЕ и вывести объект6 в EXISTS.

Не забудьте добавить "ТОР 1" сразу после "SELECT" в EXISTS, чтобы проинформировать оптимайзер о весе выборки. В принципе он конечно быть достаточно умным, чтобы и самому догадаться... :)
24 апр 16, 14:14    [19099237]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 2 [3] 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить