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

Откуда: Москва
Сообщений: 895
Hello world!
Есть две таблицы. У обеих есть кластерный индекс. Первая содержит 242575 , а вторая 97142 записей.
CREATE TABLE [tmp].[T_abc_branch_](
	[DTST] [int] NOT NULL, [DTED] [int] NOT NULL, [Product_Key] [int] NOT NULL, [Branch_Key] [int] NOT NULL,
	[ABC_Количество] [nvarchar](5) NOT NULL,	[Rating_Количество] [bigint] NOT NULL,
	[ABC_Доход] [nvarchar](5) NOT NULL,		[Rating_Доход] [bigint] NOT NULL,
	[ABC_Оборот] [nvarchar](5) NOT NULL,		[Rating_Оборот] [bigint] NOT NULL,
	[ABC_Клиенты] [nvarchar](5) NOT NULL,		[Rating_Клиенты] [bigint] NOT NULL,
	[ABC_Частота] [nvarchar](5) NOT NULL,		[Rating_Частота] [bigint] NOT NULL,
	[ABC_ОстаткиШт] [nvarchar](5) NOT NULL,		[Rating_ОстаткиШт] [bigint] NOT NULL,
	[ABC_ОстаткиRUB] [nvarchar](5) NOT NULL,	[Rating_ОстаткиRUB] [bigint] NOT NULL,
	[ABC_ВЕС] [nvarchar](5) NOT NULL,		[Rating_ВЕС] [bigint] NOT NULL,
	[ABC_ОБЪЕМ] [nvarchar](5) NOT NULL,		[Rating_ОБЪЕМ] [bigint] NOT NULL
) 
CREATE UNIQUE CLUSTERED INDEX [IC_U_T_abc_branch_:Год:Product_Key:Branch_Key:] ON [tmp].[T_abc_branch_]
(
	[DTST] ASC,
	[DTED] ASC,
	[Product_Key] ASC,
	[Branch_Key] ASC
)
CREATE TABLE [dim].[ABC_Branch_SKU](
	[ABC_Key] [int] IDENTITY(1,1) NOT NULL,
	[ABC_Количество] [nvarchar](50) NOT NULL,	[ABC_Количество_ORD] [smallint] NOT NULL,
	[ABC_Доход] [nvarchar](50) NOT NULL,		[ABC_Доход_ORD] [smallint] NOT NULL,
	[ABC_Оборот] [nvarchar](50) NOT NULL,		[ABC_Оборот_ORD] [smallint] NOT NULL,
	[ABC_Клиенты] [nvarchar](50) NOT NULL,		[ABC_Клиенты_ORD] [smallint] NOT NULL,
	[ABC_Частота] [nvarchar](50) NOT NULL,		[ABC_Частота_ORD] [smallint] NOT NULL,
	[ABC_ОстаткиШт] [nvarchar](50) NOT NULL,	[ABC_ОстаткиШт_ORD] [smallint] NOT NULL,
	[ABC_ОстаткиRUB] [nvarchar](50) NOT NULL,	[ABC_ОстаткиRUB_ORD] [smallint] NOT NULL,
	[ABC_ВЕС] [nvarchar](50) NOT NULL,		[ABC_ВЕС_ORD] [smallint] NOT NULL,
	[ABC_ОБЪЕМ] [nvarchar](50) NOT NULL,		[ABC_ОБЪЕМ_ORD] [smallint] NOT NULL
)
CREATE UNIQUE CLUSTERED INDEX [IC_U_ABC_Branch_SKU:ABC_Key:] ON [dim].[ABC_Branch_SKU]
(
	[ABC_Key] ASC
)
Перед выполнением основного запроса обновляю статистики.
UPDATE STATISTICS [DWH_Staging_Area].[tmp].[T_abc_branch_] [IC_U_T_abc_branch_:Год:Product_Key:Branch_Key:] WITH FULLSCAN
UPDATE STATISTICS [DWH].[dim].[ABC_Branch_SKU] [IC_U_ABC_Branch_SKU:ABC_Key:] WITH FULLSCAN
Скрипт основного запроса
		SELECT	 ABC_.ABC_Key, ABC.*
		FROM	[DWH_Staging_Area].[tmp].[T_abc_branch_] AS ABC
		JOIN	[DWH].[dim].[ABC_Branch_SKU] AS ABC_
			ON	ABC.[ABC_Количество] = ABC_.[ABC_Количество] AND
				ABC.[ABC_Доход] = ABC_.[ABC_Доход]  AND
				ABC.[ABC_Оборот] = ABC_.[ABC_Оборот]  AND
				ABC.[ABC_Клиенты] = ABC_.[ABC_Клиенты] AND
				ABC.[ABC_Частота] = ABC_.[ABC_Частота] AND
				ABC.[ABC_ОстаткиШт] = ABC_.[ABC_ОстаткиШт] AND
				ABC.[ABC_ОстаткиRUB] = ABC_.[ABC_ОстаткиRUB] AND
				ABC.[ABC_ВЕС] = ABC_.[ABC_ВЕС] AND
				ABC.[ABC_ОБЪЕМ] = ABC_.[ABC_ОБЪЕМ] 
		OPTION (RECOMPILE)	
При его выполнении используется план запроса, в котором оператор Sort сливает данные на диск, хотя кардинальность вычислена правильно. Подскажите с чем это связано?
17 дек 18, 16:56    [21766177]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
Yagrus2
Member

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


К сообщению приложен файл. Размер - 132Kb
17 дек 18, 16:56    [21766180]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
А откуда сиквелу знать сколько будет в итоге строк? Если у вас статистика не по тем полям по которым идет соединение, ну и если по правде не вижу проблемы. Может банально не хватать памяти. Как вариант можно убрать RECOMPILE и посмотреть что с планом.
17 дек 18, 17:16    [21766205]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
aleks222
Member

Откуда:
Сообщений: 952
Так нэ лезет в память!
17 дек 18, 17:19    [21766213]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
когда серверу особо хочется посортировать,
можно ему явно влепить hash join в запрос
17 дек 18, 17:25    [21766223]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 895
AlanDenton
А откуда сиквелу знать сколько будет в итоге строк? Если у вас статистика не по тем полям по которым идет соединение
Данные спилятся в операторе Sort до выполнения соединения.
AlanDenton
Может банально не хватать памяти.
Как это проверять?
17 дек 18, 17:28    [21766227]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
msLex
Member

Откуда:
Сообщений: 8073
Yasha123
когда серверу особо хочется посортировать,
можно ему явно влепить hash join в запрос

что вы, что вы, сейчас понабегут апологеты "безхинтового" программирования.
17 дек 18, 17:28    [21766228]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Расшарьте план выполнения в формате sqlplan пжл
17 дек 18, 17:36    [21766238]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
felix_ff
Member

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

версия сиквела у вас какая?

можете попробовать поэкспериментировать с вариациями
option (maxdop 1)
option (querytraceon 7470)
17 дек 18, 17:39    [21766244]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 895
Yasha123
когда серверу особо хочется посортировать,
можно ему явно влепить hash join в запрос

Попробовал, но у такого запроса(hash join) стоимость получилась больше, чем у исходного(merge join + sort).
17 дек 18, 17:40    [21766245]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 895
felix_ff
Yagrus2,

версия сиквела у вас какая?


Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
Oct 20 2015 15:36:27
Copyright (c) Microsoft Corporation
Business Intelligence Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
17 дек 18, 17:42    [21766248]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
msLex
Yasha123
когда серверу особо хочется посортировать,
можно ему явно влепить hash join в запрос

что вы, что вы, сейчас понабегут апологеты "безхинтового" программирования.

на прежнем месте у 2008 R2 временами сносило крышу,
массово мерджил там, где еще накануне красовался hash, им же самим выбранный.
натурально, с сортировкой обеих таблиц (огромных таблиц).
прибили хинтами, благо данные в таблицы только добавлялись.
17 дек 18, 17:43    [21766250]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Yagrus2
Yasha123
когда серверу особо хочется посортировать,
можно ему явно влепить hash join в запрос

Попробовал, но у такого запроса(hash join) стоимость получилась больше, чем у исходного(merge join + sort).

да наплевать на стоимость.
запустите и убедитесь.
вы видите, какие объемы он решил посортировать?
17 дек 18, 17:45    [21766254]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
вот кстати и ответ, с чего вдруг полез merge.
потому что hash видите ли вдруг подорожал.
еще бы он вменяемо объяснил, в честь чего подорожание
17 дек 18, 17:46    [21766255]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 895
AlanDenton
Расшарьте план выполнения в формате sqlplan пжл


К сообщению приложен файл (111.sqlplan - 119Kb) cкачать
17 дек 18, 17:48    [21766261]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 895
Yasha123
да наплевать на стоимость.
запустите и убедитесь.
вы видите, какие объемы он решил посортировать?

Не пойму вас.
Я параллельно запустил два запроса. Первый в хинтом на HASH JOIN. Через просмотр Execution plan вижу такую картинку

К сообщению приложен файл. Размер - 36Kb
17 дек 18, 17:55    [21766270]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
да, кстати, и не 2008 это был,
а 2012.
у меня все хранится.
могу подарить желающим поломать голову,
какого черта у него хэш подорожал.
реально же хэш был быстрее в 3,5 раз
и в темпдб не лазил

+

Warning: The join order has been enforced because a local join hint is used.
Table 'TAB_PROCEDURE_T'. Scan count 9, logical reads 3534693, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TAB_TABELLONE_DATI_AGGIUNTIVI_T_day'. Scan count 9, logical reads 137616, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 155434, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1903553 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 39484 ms, elapsed time = 47835 ms.

Table 'TAB_TABELLONE_DATI_AGGIUNTIVI_T_day'. Scan count 9, logical reads 137792, physical reads 0, read-ahead reads 10, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TAB_PROCEDURE_T'. Scan count 9, logical reads 3534693, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1903553 row(s) affected)

(1 row(s) affected)


К сообщению приложен файл (merge_hash_carla.zip - 31Kb) cкачать
17 дек 18, 17:56    [21766271]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Yagrus2,
что непонятного?
вы смотрите оценку в попугаях.
а я вам говорю запустить оба запроса и сравнить время выполнения.
реальное время выполнения
17 дек 18, 17:57    [21766274]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 895
Yasha123,
Размер сортируемой таблицы 36,133 MB
17 дек 18, 17:58    [21766275]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Yagrus2
Размер сортируемой таблицы 36,133 MB

вообще да, ваши таблицы хиловаты.
в моем случае 27Гб было в основной таблице.
тем не менее, ему приспичило сортировать
17 дек 18, 18:05    [21766282]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
felix_ff
Member

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

у вас предполагаемые планы приложены :(
заинтересовали, а конкретики не дали.
17 дек 18, 18:10    [21766286]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
Yasha123
Member

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

у вас предполагаемые планы приложены :(
заинтересовали, а конкретики не дали.


конктерика в статистике выполнения под спойлером.
прилагаю то, что сохранилось.
я уже у другого клиента,
тем не менее, я точно помню, что планы полетели массово.
причем, скорее всего, это было после введения page compression
на ряде таблиц.
компрессию убирать не пожелали, зато потребовали срочно вернуться к прежнему времени выполнения.
так что хинтами пришлось прибить.
конфигурация сервера 100% не менялась,
данные в таблицах с одного дня на другой по сотне-тысяче строк лишь прибавляли.
17 дек 18, 18:30    [21766300]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
+ были сохранены предполагаемые,
чтобы было видно оценку при выборе плана.
это кстати был как раз нетипичный случай, где хэш стоил меньше,
но он его все равно не выбирал.
в других случаях он еще и считал, что хэш дороже.
сейчас еще покопаюсь, если найду вывалю сюда.
--
этот случай странен вдвойне, потому что должен выбирать с меньшей стоимостью,
а на деле было не так
17 дек 18, 18:37    [21766310]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Так там merge промахивается в оценке в 50раз
17 дек 18, 18:40    [21766312]     Ответить | Цитировать Сообщить модератору
 Re: Оператор Sort и Spill Level  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 895
Yasha123
Yagrus2,
а я вам говорю запустить оба запроса и сравнить время выполнения.
реальное время выполнения
Через HASH JOIN время выполнения получилось меньше.
Скрипт Время
	
SELECT	ABC_.ABC_Key, 
	ABC.DTST,	
	ABC.DTED,	
	Product_Key,	
	Branch_Key
into #q2
FROM	[DWH_Staging_Area].[tmp].[T_abc_branch_] AS ABC
JOIN	[DWH].[dim].[ABC_Branch_SKU] AS ABC_
  ON	ABC.[ABC_Количество] = ABC_.[ABC_Количество] AND
	ABC.[ABC_Доход] = ABC_.[ABC_Доход]  AND
	ABC.[ABC_Оборот] = ABC_.[ABC_Оборот]  AND
	ABC.[ABC_Клиенты] = ABC_.[ABC_Клиенты] AND
	ABC.[ABC_Частота] = ABC_.[ABC_Частота] AND
	ABC.[ABC_ОстаткиШт] = ABC_.[ABC_ОстаткиШт] AND
	ABC.[ABC_ОстаткиRUB] = ABC_.[ABC_ОстаткиRUB] AND
	ABC.[ABC_ВЕС] = ABC_.[ABC_ВЕС] AND
	ABC.[ABC_ОБЪЕМ] = ABC_.[ABC_ОБЪЕМ] 
OPTION (RECOMPILE , HASH JOIN)	
(242575 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 1435 ms,  elapsed time = 405 ms.
SQL Server parse and compile time: 
   CPU time = 5 ms, elapsed time = 5 ms.
SELECT	ABC_.ABC_Key, 
	ABC.DTST,	
	ABC.DTED,	
	Product_Key,	
	Branch_Key
into #q3
FROM	[DWH_Staging_Area].[tmp].[T_abc_branch_] AS ABC
JOIN	[DWH].[dim].[ABC_Branch_SKU] AS ABC_
  ON	ABC.[ABC_Количество] = ABC_.[ABC_Количество] AND
	ABC.[ABC_Доход] = ABC_.[ABC_Доход]  AND
	ABC.[ABC_Оборот] = ABC_.[ABC_Оборот]  AND
	ABC.[ABC_Клиенты] = ABC_.[ABC_Клиенты] AND
	ABC.[ABC_Частота] = ABC_.[ABC_Частота] AND
	ABC.[ABC_ОстаткиШт] = ABC_.[ABC_ОстаткиШт] AND
	ABC.[ABC_ОстаткиRUB] = ABC_.[ABC_ОстаткиRUB] AND
	ABC.[ABC_ВЕС] = ABC_.[ABC_ВЕС] AND
	ABC.[ABC_ОБЪЕМ] = ABC_.[ABC_ОБЪЕМ] 
OPTION (RECOMPILE)
(242575 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 4946 ms,  elapsed time = 1723 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
18 дек 18, 10:43    [21766686]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить