Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Оператор Sort и Spill Level / 25 сообщений из 31, страница 1 из 2
  1  все
17.12.2018, 16:56
    #39749057
Yagrus2
Yagrus2
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
Hello world!
Есть две таблицы. У обеих есть кластерный индекс. Первая содержит 242575 , а вторая 97142 записей.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
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
)

Перед выполнением основного запроса обновляю статистики.
Код: sql
1.
2.
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

Скрипт основного запроса
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
		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 сливает данные на диск, хотя кардинальность вычислена правильно. Подскажите с чем это связано?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 16:56
    #39749058
Yagrus2
Yagrus2
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 17:16
    #39749069
AlanDenton
AlanDenton
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
А откуда сиквелу знать сколько будет в итоге строк? Если у вас статистика не по тем полям по которым идет соединение, ну и если по правде не вижу проблемы. Может банально не хватать памяти. Как вариант можно убрать RECOMPILE и посмотреть что с планом.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 17:19
    #39749075
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
Так нэ лезет в память!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 17:25
    #39749078
Yasha123
Yasha123
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
когда серверу особо хочется посортировать,
можно ему явно влепить hash join в запрос
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 17:28
    #39749080
Yagrus2
Yagrus2
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
AlanDentonА откуда сиквелу знать сколько будет в итоге строк? Если у вас статистика не по тем полям по которым идет соединение Данные спилятся в операторе Sort до выполнения соединения.
AlanDentonМожет банально не хватать памяти. Как это проверять?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 17:28
    #39749081
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
Yasha123когда серверу особо хочется посортировать,
можно ему явно влепить hash join в запрос
что вы, что вы, сейчас понабегут апологеты "безхинтового" программирования.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 17:36
    #39749088
AlanDenton
AlanDenton
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
Расшарьте план выполнения в формате sqlplan пжл
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 17:39
    #39749092
felix_ff
felix_ff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
Yagrus2,

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

можете попробовать поэкспериментировать с вариациями
Код: sql
1.
2.
option (maxdop 1)
option (querytraceon 7470)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 17:40
    #39749093
Yagrus2
Yagrus2
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
Yasha123когда серверу особо хочется посортировать,
можно ему явно влепить hash join в запрос
Попробовал, но у такого запроса(hash join) стоимость получилась больше, чем у исходного(merge join + sort).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 17:42
    #39749095
Yagrus2
Yagrus2
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
felix_ffYagrus2,

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


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)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 17:43
    #39749096
Yasha123
Yasha123
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
msLexYasha123когда серверу особо хочется посортировать,
можно ему явно влепить hash join в запрос
что вы, что вы, сейчас понабегут апологеты "безхинтового" программирования.
на прежнем месте у 2008 R2 временами сносило крышу,
массово мерджил там, где еще накануне красовался hash, им же самим выбранный.
натурально, с сортировкой обеих таблиц (огромных таблиц).
прибили хинтами, благо данные в таблицы только добавлялись.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 17:45
    #39749098
Yasha123
Yasha123
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
Yagrus2Yasha123когда серверу особо хочется посортировать,
можно ему явно влепить hash join в запрос
Попробовал, но у такого запроса(hash join) стоимость получилась больше, чем у исходного(merge join + sort).
да наплевать на стоимость.
запустите и убедитесь.
вы видите, какие объемы он решил посортировать?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 17:46
    #39749099
Yasha123
Yasha123
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
вот кстати и ответ, с чего вдруг полез merge.
потому что hash видите ли вдруг подорожал.
еще бы он вменяемо объяснил, в честь чего подорожание
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 17:48
    #39749103
Yagrus2
Yagrus2
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
AlanDentonРасшарьте план выполнения в формате sqlplan пжл
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 17:55
    #39749106
Yagrus2
Yagrus2
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
Yasha123да наплевать на стоимость.
запустите и убедитесь.
вы видите, какие объемы он решил посортировать?
Не пойму вас.
Я параллельно запустил два запроса. Первый в хинтом на HASH JOIN. Через просмотр Execution plan вижу такую картинку
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 17:56
    #39749107
Yasha123
Yasha123
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
да, кстати, и не 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)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 17:57
    #39749109
Yasha123
Yasha123
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
Yagrus2,
что непонятного?
вы смотрите оценку в попугаях.
а я вам говорю запустить оба запроса и сравнить время выполнения.
реальное время выполнения
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 17:58
    #39749110
Yagrus2
Yagrus2
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
Yasha123,
Размер сортируемой таблицы 36,133 MB
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 18:05
    #39749112
Yasha123
Yasha123
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
Yagrus2Размер сортируемой таблицы 36,133 MB
вообще да, ваши таблицы хиловаты.
в моем случае 27Гб было в основной таблице.
тем не менее, ему приспичило сортировать
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 18:10
    #39749115
felix_ff
felix_ff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
Yasha123,

у вас предполагаемые планы приложены :(
заинтересовали, а конкретики не дали.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 18:30
    #39749121
Yasha123
Yasha123
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
felix_ffYasha123,

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

конктерика в статистике выполнения под спойлером.
прилагаю то, что сохранилось.
я уже у другого клиента,
тем не менее, я точно помню, что планы полетели массово.
причем, скорее всего, это было после введения page compression
на ряде таблиц.
компрессию убирать не пожелали, зато потребовали срочно вернуться к прежнему времени выполнения.
так что хинтами пришлось прибить.
конфигурация сервера 100% не менялась,
данные в таблицах с одного дня на другой по сотне-тысяче строк лишь прибавляли.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 18:37
    #39749126
Yasha123
Yasha123
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
+ были сохранены предполагаемые,
чтобы было видно оценку при выборе плана.
это кстати был как раз нетипичный случай, где хэш стоил меньше,
но он его все равно не выбирал.
в других случаях он еще и считал, что хэш дороже.
сейчас еще покопаюсь, если найду вывалю сюда.
--
этот случай странен вдвойне, потому что должен выбирать с меньшей стоимостью,
а на деле было не так
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2018, 18:40
    #39749127
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
Так там merge промахивается в оценке в 50раз
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.12.2018, 10:43
    #39749294
Yagrus2
Yagrus2
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оператор Sort и Spill Level
Yasha123Yagrus2,
а я вам говорю запустить оба запроса и сравнить время выполнения.
реальное время выполненияЧерез HASH JOIN время выполнения получилось меньше.
Скрипт Время
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
	
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)

Код: javascript
1.
2.
3.
4.
5.
6.
7.
8.
(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.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
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)

Код: javascript
1.
2.
3.
4.
5.
6.
7.
8.
(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.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Оператор Sort и Spill Level / 25 сообщений из 31, страница 1 из 2
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]