Comments 15
ИМХО. Самое важное это не сиюминутный план запроса с самой низкой стоимостью, а стабильный и прогнозируемый. Конечно, приемлимый по цене. Но, для этого надо профайлить все. И иметь инструментарий корректировки итоговых запросов. Например, для указания использования nested loop вместо hash функций, которые на больших объёмах дают, порой, неконтролируемую просадку. и т. д.
Я специально брал большие обьемы данных чтобы оптимизатор не пытался перейти на nested loop в соединениях или другие пути если они ему покажутся эффективными. Хинты 1с не поддерживает и в трассировках я их не видел, но там и без хинтов можно перестройкой запросов под капотом платформы добится хороших результатов. С поддержкой по теме данной статьи общаюсь - думаю возьмут в работу
memory_optimized в sql имеют кучу ограничений, решают только проблемы чтения а не записи и еще коцепции разные в разных субд. 1с поддерживает 4 субд и им это важно.
Железом описанную в статье проблему не решишь. Там видно что поток для merge при соединении с двумя полями содержит меньше записей, чем с номером+ fld628=0. Там именно процессорное время уменьшается . В этом случае все будет ограничено одним ядром процессора
Железо помогает если конфигурация сделана с учетом горизонтального маштабирования как делали мы https://habr.com/ru/post/674282/
Добавить несколько SSD райдов и на каждый сделать отдельную файловую группу.
а вы часто утыкаетесь в производительность отдельного ssd?
"не очень понятно а в чём проблема создать неуникальный индекс?" Many to many более сложно делать чем однозначное по уникальному индексу
У вас не совсем корректное сравнение запросов. В первом случае (реальность) выполняется параметризированный запрос, во втором (ожидание) - с использованием локальных переменных, что ведёт к другому способу расчёта ожидаемого количества строк и, потенциально, может в итоге привести к другому плану. Прогоните второй вариант с OPTION (RECOMPILE), возможно вы увидите другие цифры в итоге.
Я перед каждым запросом делаю сброс всего instance sql. Так что каждый запрос идет с чистого листа
DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);
DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);
DBCC FREEPROCCACHE ;
DBCC DROPCLEANBUFFERS
В данном случае это ни на что не влияет, т.к. тут дело не кэшировании. В первом случае компилятор видит передаваемый в запрос параметр (при включенном по умолчанию parameter sniffing) и будет в расчётах использовать гистограмму из статистики, во втором случае - нет и будет использоваться общая плотность, что на неравномерно распределённых данных может дать сильно другое количество ожидаемых строк
Можете какой то пруф линк привести? Я вот впервые слышу что при разном способе передачи параметров будет разный способ использования статистики. При повторной передаче параметров как тут Почему Parameter Sniffing не всегда плохо (хотя обычно так и есть) | SQL-Ex blog да такое есть.
В любом случае запустил запрос еще раз с recompile разницы нет
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
---- -------- -------- ------ ------ ------ ---------- --------- -------- ------------- ------------ ---------- ----------- ---------- ---------------- ---------- -------- ---- -------- ------------------
18376071 1 Table Insert(OBJECT:([#tt_RESULT]), SET:([#tt_RESULT].[_Period] = [MIS_PROD2].[dbo].[_InfoRg18860].[_Period] as [T1].[_Period],[#tt_RESULT].[_Fld18861RRef] = [MIS_PROD2].[dbo].[_InfoRg18860].[_Fld18861RRef] as [T1].[_Fld18861RRef],[#tt_RESULT].[_Fld18865] = [MIS_PROD2].[dbo].[_InfoRg18860].[_Fld18865] as [T1].[_Fld18865],[#tt_RESULT].[_Fld18863RRef] = [MIS_PROD2].[dbo].[_InfoRg18860].[_Fld18863RRef] as [T1].[_Fld18863RRef],[#tt_RESULT].[_Fld19363RRef] = [MIS_PROD2].[dbo].[_InfoRg18860].[_Fld19363RRef] as [T1].[_Fld19363RRef])) 0 0 Table Insert Insert OBJECT:([#tt_RESULT]), SET:([#tt_RESULT].[_Period] = [MIS_PROD2].[dbo].[_InfoRg18860].[_Period] as [T1].[_Period],[#tt_RESULT].[_Fld18861RRef] = [MIS_PROD2].[dbo].[_InfoRg18860].[_Fld18861RRef] as [T1].[_Fld18861RRef],[#tt_RESULT].[_Fld18865] = [MIS_PROD2].[dbo].[_InfoRg18860].[_Fld18865] as [T1].[_Fld18865],[#tt_RESULT].[_Fld18863RRef] = [MIS_PROD2].[dbo].[_InfoRg18860].[_Fld18863RRef] as [T1].[_Fld18863RRef],[#tt_RESULT].[_Fld19363RRef] = [MIS_PROD2].[dbo].[_InfoRg18860].[_Fld19363RRef] as [T1].[_Fld19363RRef]) 2.87826E+006 201.792 2.87826 9 2752.21 PLAN_ROW 0 1
18376071 1 |--Top(ROWCOUNT est 0) 0 1 0 Top Top TOP EXPRESSION:((0)) 2.87826E+006 0 0.287826 85 2547.54 [T1].[_Period], [T1].[_Fld18861RRef], [T1].[_Fld18865], [T1].[_Fld18863RRef], [T1].[_Fld19363RRef] PLAN_ROW 0 1
18376071 1 |--Merge Join(Inner Join, MERGE:([T2].[tt_Fld628], [T2].[_Q_000_F_000])=([T1].[_Fld628], [T1].[_Fld18865]), RESIDUAL:([MIS_PROD2].[dbo].[_InfoRg18860].[_Fld628] as [T1].[_Fld628]=#tt_alternative.[tt_Fld628] as [T2].[tt_Fld628] AND [MIS_PROD2].[dbo].[_InfoRg18860].[_Fld18865] as [T1].[_Fld18865]=#tt_alternative.[_Q_000_F_000] as [T2].[_Q_000_F_000])) 0 2 1 Merge Join Inner Join MERGE:([T2].[tt_Fld628], [T2].[_Q_000_F_000])=([T1].[_Fld628], [T1].[_Fld18865]), RESIDUAL:([MIS_PROD2].[dbo].[_InfoRg18860].[_Fld628] as [T1].[_Fld628]=#tt_alternative.[tt_Fld628] as [T2].[tt_Fld628] AND [MIS_PROD2].[dbo].[_InfoRg18860].[_Fld18865] as [T1].[_Fld18865]=#tt_alternative.[_Q_000_F_000] as [T2].[_Q_000_F_000]) 2.87826E+006 0 90.3212 85 2547.25 [T1].[_Period], [T1].[_Fld18861RRef], [T1].[_Fld18865], [T1].[_Fld18863RRef], [T1].[_Fld19363RRef] PLAN_ROW 0 1
20079020 1 |--Index Scan(OBJECT:([tempdb].[dbo].[#tt_alternative] AS [T2]), ORDERED FORWARD) 0 3 2 Index Scan Index Scan OBJECT:([tempdb].[dbo].[#tt_alternative] AS [T2]), ORDERED FORWARD [T2].[tt_Fld628], [T2].[_Q_000_F_000] 2.0079E+007 75.5653 22.0871 34 97.6524 [T2].[tt_Fld628], [T2].[_Q_000_F_000] PLAN_ROW 0 1
22611008 1 |--Index Scan(OBJECT:([MIS_PROD2].[dbo].[_InfoRg18860].[_InfoR18860_ByDims18897_STRRRR] AS [T1]), WHERE:([MIS_PROD2].[dbo].[_InfoRg18860].[_Period] as [T1].[_Period]>='4022-02-01 00:00:00.000') ORDERED FORWARD) 0 4 2 Index Scan Index Scan OBJECT:([MIS_PROD2].[dbo].[_InfoRg18860].[_InfoR18860_ByDims18897_STRRRR] AS [T1]), WHERE:([MIS_PROD2].[dbo].[_InfoRg18860].[_Period] as [T1].[_Period]>='4022-02-01 00:00:00.000') ORDERED FORWARD [T1].[_Period], [T1].[_Fld18861RRef], [T1].[_Fld18865], [T1].[_Fld18863RRef], [T1].[_Fld19363RRef], [T1].[_Fld628] 2.27914E+007 2026.45 231.714 90 2258.16 [T1].[_Period], [T1].[_Fld18861RRef], [T1].[_Fld18865], [T1].[_Fld18863RRef], [T1].[_Fld19363RRef], [T1].[_Fld628] PLAN_ROW 0 1
Если посмотреть на estimated rows при выборке из _InfoRg18860, то видно, что разница всё же есть - 63194800 без recompile и 22791400 с recompile. В данном случае на план это не повлияло, но, потенциально, при других параметрах, вполне могло бы.
Точную ссылку где я про это читал сейчас по быстрому нагуглить не удалось, но например вот тут про это упоминается тоже - https://www.sqlservercentral.com/blogs/inside-the-statistics-histogram-density-vector
Начну более менее адекватно воспринимать автора, когда он ответит в чем сакральный смысл лепить индекс на временную таблицу вместе ее сортировки по полям связи.
Лучшее соединение враг хорошего?