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

Откуда:
Сообщений: 234
Есть хранимая процедура, выполнявшаяся ранее за 2 минуты. Затем время выполнения резко подскочило до получаса. Теперь вообще невозможно дождаться окончания, висит часами. Начинал анализировать план выполнения, когда ещё было возможно его получить - бросилась в глаза промашка в Est/Actual rows.
Картинка с другого сайта.
Сейчас могу воспроизвести план, только ограничив запрос top 100 (см во вложении).
После dbcc freeproccache, dropcleanbuffers ничего не меняется. Параметров у хранимки нет. Особенность - несколько раз используются создающиеся внутри хранимки временные таблицы. Подскажите, пожалуйста, как исправить ситуацию или хотя бы в каком направлении копать?

К сообщению приложен файл (sqlru_slowQueryPlan.rar - 10Kb) cкачать
11 июн 13, 14:15    [14420616]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
MyNiGoo
Member

Откуда:
Сообщений: 234
Промашка большая, вместо тысяч строк оптимизатор думает. что придёт всего лишь одна. Если бы там была табличная переменная, я бы это хоть как-то понимал, но нет.
11 июн 13, 14:17    [14420642]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
iap
Member

Откуда: Москва
Сообщений: 47047
Может, просто статистику обновить?
11 июн 13, 14:23    [14420709]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
MyNiGoo
Member

Откуда:
Сообщений: 234
iap, ежедневно
11 июн 13, 14:28    [14420739]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
вы ожидаете рекомендаций не видя текста процедуры ?
11 июн 13, 14:37    [14420824]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
MyNiGoo
Member

Откуда:
Сообщений: 234
там километровая ETL-простыня, в которой копируются из oltp справочники, а потом по ним строится таблица фактов. Профайлер показал, что тормозит только один финальный запрос.
11 июн 13, 14:41    [14420860]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
MyNiGoo
там километровая ETL-простыня, в которой копируются из oltp справочники, а потом по ним строится таблица фактов. Профайлер показал, что тормозит только один финальный запрос.


какой?
11 июн 13, 14:46    [14420882]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
iap
Member

Откуда: Москва
Сообщений: 47047
Winnipuh
MyNiGoo
там километровая ETL-простыня, в которой копируются из oltp справочники, а потом по ним строится таблица фактов. Профайлер показал, что тормозит только один финальный запрос.


какой?
финальный!
11 июн 13, 14:49    [14420903]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
MyNiGoo
Member

Откуда:
Сообщений: 234
Winnipuh,
+ текст запроса
select bir.ID [birID]
		,cast(bir.Quantity as decimal(24,12)) [birQty]
		,cast(lpn.Entity2Quantity / pn.TotalQuantity as decimal(24,12))
			[K_bir2pn]
		,lpn.Entity1ToEntity2Coef [Coef_bir2pn]
		,pn.ID [pnID]
		,pn.TotalQuantity [pnQty]
		,cast(rl.Entity2Quantity / req.Quantity as decimal(24,12))
			[K_pn2req]
		,rl.Entity1ToEntity2Coef  [Coef_pn2req]
		,req.ID [reqID]
		,req.Quantity [reqQty]
		,spl.Entity2Quantity --/ si.UnconvertedQuantity
			[K_req2si]
		,si.ID [siID]
		,si.UnconvertedQuantity [siQty]
		,notel.Entity2Quantity --/ ni.Quantity
			[K_req2ni]
		,spl.Entity1ToEntity2Coef [Coef_req2spi]
		,ni.ID [niID]
		,ni.Quantity [niQty]
		,dlink.Entity1Quantity --/ di.Quantity
			[K_req2di]
		,notel.Entity1ToEntity2Coef [Coef_req2note]
		,di.ID [diID]
		,di.Quantity [diQty]
		,dlink.Entity1ToEntity2Coef [Coef_req2deli]
		,isnull(rdl.rdid, -1) [reqDistrID]
		--,K_pn2reqdistrNormK = case when rdl.sumPnQty > pn.TotalQuantity then cast(cast(NormCoef * rdl.sumPnQty as decimal(24,12)) / pn.TotalQuantity as decimal(24,12)) else NormCoef end
		,new_K_pn2reqdistrNormK = case when rdl.sumPnQty > pn.TotalQuantity then cast(rdl.sumPnQty / pn.TotalQuantity as decimal(24,12)) else cast(1 as decimal(1,0)) end
		,K_pn2reqdistr = cast(rdl.pnQty / rdl.sumPnQty as decimal(24,12))
		,K_pn2reqdistrDI = cast(rdl.diQty / rdl.pnQty as decimal(24,12)) --isnull(cast(rdl.pnQty / rdl.sumPnQty as decimal(24,12)), cast(1 as decimal(1,0)))
		,K_pn2distr = cast(distri.Quantity / rdl.rdQty as decimal(24,12))
		,isnull(cast(rdl.pnQty / pn.TotalQuantity as decimal(24,12)), 1) [K_di2rdi_c]
		,rdl.coef [Coef_toFactPn]
		,rdl.new_Coef [Coef_Del_pn2deli]
		,pnIDd = rdl.projectNeedID
		,isnull(rdl.deliveryItemID, -1) as rdl_deliveryItemID
		,rdl_diCnt = cast(rdl.diCnt as decimal(3,0))
		,K_di2reqdistr = case
							when rdl.deliveryItemID = 0 
								then cast(1 as decimal(1,0)) 
							else isnull(cast(rdl.diQty / nullif(sumDiQty, 0.0) as decimal(24,12)), cast(1 as decimal(1,0)))
						 end
		,isnull(distri.ID, -1) [distri_ID]
		,distri.Quantity [distri_Quantity]
		,distri.sumQty [distri_sumQty]
		,K_rd2distri_c = isnull(cast(distri.Quantity / distri.sumQty as decimal(24,12)), cast(1 as decimal(1,0)))
		,K_rd2distri_r = isnull(cast(distri.Quantity / rdl.rdQty as decimal(24,12)), cast(1 as decimal(1,0)))
		,coalesce(pn.ProjectID, req.ProjectID, s.ProjectID, n.ProjectID, d.ProjectID, -1) as ProjectID
	from MTO_D_BudgetItemResources bir
	join #itemLink lpn
		on bir.ID = lpn.Entity1ID and lpn.EntityType = 1
	join MTO_D_ProjectNeeds pn
		on lpn.Entity2ID = pn.ID  and lpn.EntityType = 1
	join #itemLink rl
		on pn.ID = rl.Entity1ID and rl.EntityType = 9
	join MTO_D_Requests req
		on rl.Entity2ID = req.ID and rl.EntityType = 9
	join #itemLink spl
		on req.ID = spl.Entity1ID and spl.EntityType = 5
	join MTO_D_SpecificationItems si
		on si.ID = spl.Entity2ID and spl.EntityType = 5
	join #itemLink notel
		on req.ID = notel.Entity1ID and notel.EntityType = 12
	join MTO_D_NotificationItems ni
		on ni.SpecificationItemID = si.ID and notel.Entity2ID = ni.ID
	join #itemLink dlink
		on req.ID = dlink.Entity1ID and dlink.EntityType = 1012
	join MTO_D_DeliveryItems di
		on si.ID = di.SpecificationItemID and di.ID = dlink.Entity2ID
	join MTO_D_Notifications n
		on n.ID = ni.NotificationID
	join MTO_D_Deliveries d
		on d.ID = di.DeliveryID and n.ID = d.NotificationID
	left join MTO_D_Specifications s
		on si.SpecificationID = s.ID
	left join #requestDistributionLink rdl
		on pn.ID = rdl.projectNeedID
	left join (select ID, Quantity, sum(Quantity) over(partition by ID) sumQty, DistributionRequestItemID from MTO_D_DistributionItems) distri
		on distri.DistributionRequestItemID = rdl.rdid

плюс всё это засунуто в подзапрос, над которым голенький select, только формулы
11 июн 13, 14:51    [14420922]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
2 запроса
Guest
MyNiGoo,

у меня была похожая кака.
решилось разбиением на 2 запроса.
т.е. сперва делаем только половину соединений.
остальное досоединяем во 2-ом запросе.
11 июн 13, 14:55    [14420960]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
Crimean
Member

Откуда:
Сообщений: 13148
а времянки до чтения просто инсертятся или инсертятся а после делитятся / апдейтятся?
11 июн 13, 14:55    [14420963]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
iap
Member

Откуда: Москва
Сообщений: 47047
Индексы для временных таблиц есть?
11 июн 13, 15:03    [14421015]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
iap
Member

Откуда: Москва
Сообщений: 47047
Кстати, joinов много, сервер может и не подхватить наилучший план.
Попробовать поменять порядок joinов (маленькие таблицы раньше, большие позже).
11 июн 13, 15:08    [14421049]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
Александр52
Member

Откуда: Кокосовые острова ส็็็็็
Сообщений: 5134
Попробуйте проверить на индексы, я обычно в первую очередь на них смотрю, очистить в кэше планы выполнения 'DBCC FREEPROCCACHE' и запустить заного.
11 июн 13, 15:10    [14421061]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
MyNiGoo
Member

Откуда:
Сообщений: 234
2 запроса, было же хорошо! Вот бы добыть старый план и зафиксировать его как-нибудь :'(

Crimean, да - на самую используемую времянку до чтения есть апдейт. Но я на ней и индексы перед этим запросом строю, в т.ч. кластерный. Меня смущает, что справа от операции с неправильно посчитанными Esc/Actual Rows только обычные таблицы (кстати, тоже к этому моменту уже с ключами-индексами).
11 июн 13, 15:11    [14421071]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
MyNiGoo
Member

Откуда:
Сообщений: 234
iap, индексы есть.
Александр52 , про DBCC FREEPROCCACHE написал в самом начале - безрезультатно.
11 июн 13, 15:13    [14421087]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
iap
Member

Откуда: Москва
Сообщений: 47047
MyNiGoo
iap, индексы есть.
Александр52 , про DBCC FREEPROCCACHE написал в самом начале - безрезультатно.
Пошевелите joinы, пошевелите! Помогало много раз.

Ибо оптимизатору может не хватать времени до таймаута,
чтобы пересмотреть планы в поисках наилучшего.

Посмотрите также на хинт FORCE ORDER

Если, конечно, для всех постоянных таблиц запроса статистику точно обновили и не помогло.
11 июн 13, 15:19    [14421130]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
AnyKey45
Member

Откуда: Ekaterinburg-Moscow-EU
Сообщений: 219
iap,

а восстановить из бэкапов на тестовый и там проиграть, чтобы получить хороший план, дабы его проанализировать и зафиксировать?
мне повезло, у себя на рабочей ловил и вставлял костыль плангайда
думаю изменилось количество записей и оптимизатор начал думать по другому.
11 июн 13, 15:49    [14421375]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
MyNiGoo
Member

Откуда:
Сообщений: 234
получилось. Вот хронология
1 про joinы слышал раньше когда-то, поэтому уже чуть-чуть шевелил - безрезультатно
2 после совета iap сделал это смелее, загнал единственнную относительно длинную таблицу (~10^6) в конец - безрезультатно
3 дописал option(force order) - бинго, 30 секунд!

всем спасибо, iap особо признателен =)

как бы мне теперь зафиксировать этот план? USE PLAN, KEEP PLAN, KEEPFIXED PLAN ?
нюансы: все таблицы запроса и индексы для них создаются в этой же хранимке этапом выше и результат этого запроса идет в insert

KEEP PLAN - понял только, что запрос перекомпилируется исключительно при изменении структуры индексов на базовых таблицах
KEEPFIXED PLAN - смущает фраза "убедитесь, что запрос будет перекомпилирован только при изменении схемы базовых таблиц"
USE PLAN N'xml_plan' - смущает фраза "нельзя указывать в инструкциях INSERT ..."

что будет наиболее подходящим в моём варианте?
11 июн 13, 15:57    [14421438]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
MyNiGoo
Member

Откуда:
Сообщений: 234
AnyKey45, и это тоже пробовал - не получилось, хотя подобрал бэкап, когда хранимка пробежала быстрее всего.
Во вложении план быстрого запроса.

К сообщению приложен файл (sqlru_fastQueryPlan.rar - 25Kb) cкачать
11 июн 13, 16:01    [14421469]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
AnyKey45
Member

Откуда: Ekaterinburg-Moscow-EU
Сообщений: 219
gooli-te в сторону Plan Guides
11 июн 13, 16:12    [14421541]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
Mikebond
Member

Откуда: Киев
Сообщений: 32
"Кстати" :
раз :
select CAST(13/10 as numeric(24,12)) = 1.000000000000000000000000
select 13/CAST(10 as numeric(24,12)) = 1.300000000000000000000000
два:
автор
ISNULL ( check_expression , replacement_value )
...
Returns the same type as check_expression.

поэтому (имхо ) если уж очень нужно конвертить, я бы "cast(1 as decimal(1,0))" заменил на "cast(1 as decimal(24,12))", а можно и просто на "1" . Сервер типы поровняет, так зачем дважды кастить?

три:
"Force Order" стараюсь не использовать. Вместо этого пытаюсь сгруппировать несколько джойнов, возвращающих минимум данных, в подзапрос который джойнится к "большим" таблицам. Такой вот аналог "разбиения на 2 запроса", которое уже советовали. Но, если уже работает...
12 июн 13, 02:09    [14423695]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
MyNiGoo
Member

Откуда:
Сообщений: 234
Mikebond, спасибо, что обратил внимание на isnull - в cast действительно нет смысла в таком случае.
А подзапрос не может ещё больше сбить оптимизатор с толку? Понятно, что получится более универсальное, долговременное, переносимое решение, да и хинты не от хорошей жизни применяются, но я думаю, что планы-то разные могут выйти с подзапросами и "Force Order".
Впрочем, надо провести эксперимент, а не строить догадки.
13 июн 13, 01:06    [14425967]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
Ennor Tiegael
Member

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

И на временных таблицах тоже статистику обновляли?

Если там есть апдейты индексов, я бы их еще и ребилдить попробовал...
13 июн 13, 05:11    [14426074]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в хранимке стал выполняться очень долго  [new]
Exproment
Member

Откуда:
Сообщений: 416
Mikebond, кстати говоря явные неявные преобразования интересная тема - далеко не факт что sql server в вашем случае сделает дополнительное преобразование.

Когда тестировал приведение типов в предикатах заметил, что если даже int'у подать numeric или numeric'у int - то преобразования не будет пока их размерности покрывают друг друга(даже если явно указать cast).
А кот когда скажем numeric-литерал будет больше допустимого Int - то sql server попытается сделать cast но выпадет в исключения, ибо нельзя :)
Еще одно замечание - если бы sql server всегда выполнял преобразования, то index scan'ы вылезали бы намного чаще исходя из таблицы приоритетов преобразований(столбцы бы преобразовывались к литералам). И постоянно пришлось бы писать cast для каждого литерала.
Насколько я понимаю, решение о том кастовать или нет принимает оптимизатор и зашивает в план(что собственно можно увидеть из плана, по крайней мере в случае с предикатами).

То о чем вы говорите я бы скорее отнес не к лишним преобразованиям а к элегантности и правильности кода. :)
13 июн 13, 09:38    [14426376]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить