Мне было поручено повысить производительность (и это мои первые реальные задачи по настройке производительности) хранимой процедуры создания отчетов, которая вызывается внешним интерфейсом SSRS, и в настоящее время хранимая процедура занимает около 30 секунд для работы с наибольшим объемом данных (на основе фильтров, установленных во внешнем интерфейсе отчета).
Эта хранимая процедура имеет разбивку на 19 выполняемых в ней запросов, большинство из которых преобразуют данные из исходного (устаревшего) формата из базовых таблиц в значимый набор данных для отображения на стороне бизнеса.
Я создал запрос на основе нескольких DMV, чтобы выяснить, какие из хранимых процедур являются наиболее ресурсоемкими запросами (небольшой фрагмент ниже), и я нашел один запрос, выполнение которого в среднем занимает около 10 секунд. .
select
object_name(st.objectid) [Procedure Name]
, dense_rank() over (partition by st.objectid order by qs.last_elapsed_time desc) [rank-execution time]
, dense_rank() over (partition by st.objectid order by qs.last_logical_reads desc) [rank-logical reads]
, dense_rank() over (partition by st.objectid order by qs.last_worker_time desc) [rank-worker (CPU) time]
, dense_rank() over (partition by st.objectid order by qs.last_logical_writes desc) [rank-logical write]
...
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text (qs.sql_handle) as st
cross apply sys.dm_exec_text_query_plan (qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) as qp
where st.objectid in ( object_id('SuperDooperReportingProcedure') )
, [rank-execution time]
, [rank-logical reads]
, [rank-worker (CPU) time]
, [rank-logical write] desc
Теперь этот запрос немного странный в том смысле, что план выполнения показывает, что основная часть работы (~80%) выполняется при вставке данных в локальную временную таблицу, а не при опросе других таблиц, из которых исходные данные берутся, а затем обрабатываются. (скриншот ниже из SQL Sentry Plan Explorer)
Кроме того, с точки зрения оценок строк, план выполнения имеет далеко идущие оценки для этого, в том смысле, что в локальную временную таблицу вставлено только 4218 строк, в отличие от ~248 тыс. строк, которые, по мнению плана выполнения, перемещаются в локальную временную таблицу. временная таблица. Итак, из-за этого я думаю о «статистике», но все же имеют ли они значение, если ~ 80% работы - это фактическая вставка в таблицу?
Одной из моих первых рекомендаций было переписать весь процесс и хранимую процедуру так, чтобы не включать перемещение и преобразование данных в хранимую процедуру создания отчетов, а выполнять преобразование данных каждую ночь в некоторые сохраняемые таблицы (данные в реальном времени). не требуется, только актуальные данные до конца предыдущего дня). Но бизнес-сторона не хочет вкладывать время и ресурсы в перепроектирование этого и вместо этого «предлагает» мне настроить производительность в смысле поиска, где и какие индексы я могу добавить, чтобы ускорить это.
Я не верю, что добавление индексов к базовым таблицам улучшит производительность отчета, поскольку большая часть времени, необходимого для выполнения запроса, уходит на сохранение данных во временную таблицу (которая, насколько мне известно, попадет в базу данных tempdb, что означает, что они будет записано на диск -> увеличенное время из-за задержки ввода-вывода).
Но даже в этом случае, как я уже упоминал, это моя первая задача по настройке производительности, и я пытался прочитать как можно больше, связанное с этим за последние пару дней, и пока это мои выводы, но я хотел бы попросить совета у более широкой аудитории и, надеюсь, получить больше информации и понимания того, что я могу сделать, чтобы улучшить эту процедуру.
В качестве нескольких четких вопросов, которые я был бы признателен, если бы можно было ответить:
- Есть ли что-то неправильное в том, что я сказал выше (в моем понимании БД или моих предположениях)?
- Верно ли, что добавление индекса во временную таблицу фактически увеличит время выполнения, поскольку таблица (и связанные с ней индексы перестраиваются/перестраиваются при каждом выполнении)?
- Можно ли сделать что-нибудь еще в этом сценарии без необходимости переписывать процедуру/запросы и делать это только с помощью индексов или других методов настройки? (Я читал несколько заголовков статей о том, что вы также можете «настроить tempdb», но пока не вдавался в подробности).
Любая помощь очень ценится, и если вам нужна дополнительная информация, я буду рад опубликовать.
Обновление (2 августа 2016 г.):
Рассматриваемый запрос (частично) ниже. Чего не хватает, так это еще нескольких агрегированных столбцов и соответствующих им строк в разделе GROUP BY:
select
b.ProgramName
,b.Region
,case when b.AM IS null and b.ProgramName IS not null
then 'Unassigned'
else b.AM
end as AM
,rtrim(ltrim(b.Store)) Store
,trd.Store_ID
,b.appliesToPeriod
,isnull(trd.countLeadActual,0) as Actual
,isnull(sum(case when b.budgetType = 0 and b.budgetMonth between @start_date and @end_date then b.budgetValue else 0 end),0) as Budget
,isnull(sum(case when b.budgetType = 0 and b.budgetMonth between @start_date and @end_date and (trd.considerMe = -1 or b.StoreID < 0) then b.budgetValue else 0 end),0) as CleanBudget
...
into #SalvesVsBudgets
from #StoresBudgets b
left join #temp_report_data trd on trd.store_ID = b.StoreID and trd.newSourceID = b.ProgramID
where (b.StoreDivision is not null or (b.StoreDivision is null and b.ProgramName = 'NewProgram'))
group by
b.ProgramName
,b.Region
,case when b.AM IS null and b.ProgramName IS not null
then 'Unassigned'
else b.AM
end
,rtrim(ltrim(b.Store))
,trd.Store_ID
,b.appliesToPeriod
,isnull(trd.countLeadActual,0)
Я не уверен, что это действительно полезно, но поскольку @kcung запросил это, я добавил информацию.
Кроме того, чтобы ответить на некоторые его вопросы:
- временные таблицы не имеют индексов на них
- Размер оперативной памяти: 32 ГБ
Обновление (3 августа 2016 г.):
Я попробовал предложения @kcung по перемещению операторов CASE из запроса, генерирующего совокупность, и, к сожалению, в целом время процедуры заметно не улучшилось, поскольку оно все еще колеблется в диапазоне от ± 0,25 до ± 1,0 секунды (да, оба ниже и больше времени, чем исходная версия хранимой процедуры, но я предполагаю, что это связано с переменной рабочей нагрузкой на моей машине).
План выполнения для того же запроса, но измененный для удаления условий CASE и оставления только агрегатов SUM, теперь выглядит следующим образом:
