October 15, 2018 at 10:11 pm
Hello - Can anyone please advise how to run the below piece of script against all user databases? Basically want to find the regressed queries all user dbs which have QueryStore option enabled.
create Proc regressed_Queries (
--recent workload
@recent_start_time datetimeoffset = '2018-10-01 14:50:51 -04:00',
@recent_end_time datetimeoffset = '2018-10-10 14:50:51.5594718 -04:00',
--- "History" workload
@history_start_time datetimeoffset = '2018-08-03 14:53:50.9886517 -04:00',
@history_end_time datetimeoffset = '2018-10-15 14:53:50.9886517 -04:00',
@results_row_count Int = 25,
@min_exec_count Int = 1
)
as
begin
WITH
hist AS
(
SELECT
p.query_id query_id,
ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration,
SUM(rs.count_executions) count_executions,
COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
WHERE NOT (rs.first_execution_time > @history_end_time OR rs.last_execution_time < @history_start_time)
GROUP BY p.query_id
),
recent AS
(
SELECT
p.query_id query_id,
ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration,
SUM(rs.count_executions) count_executions,
COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
WHERE NOT (rs.first_execution_time > @recent_end_time
OR rs.last_execution_time < @recent_start_time)
GROUP BY p.query_id
)
SELECT TOP (@results_row_count)
results.query_id query_id,
results.object_id object_id,
ISNULL(OBJECT_NAME(results.object_id),'''') object_name,
results.query_text query_text,
results.additional_duration_workload additional_duration_workload,
results.total_duration_recent total_duration_recent,
results.total_duration_hist total_duration_hist,
ISNULL(results.count_executions_recent, 0) count_executions_recent,
ISNULL(results.count_executions_hist, 0) count_executions_hist,
queries.num_plans num_plans
FROM
(
SELECT
hist.query_id query_id,
q.object_id object_id,
qt.query_sql_text query_text,
ROUND(ROUND(CONVERT(float, recent.total_duration/recent.count_executions-hist.total_duration/hist.count_executions)
*(recent.count_executions), 2)*0.001,2) additional_duration_workload,
ROUND(recent.total_duration, 2) total_duration_recent,
ROUND(hist.total_duration, 2) total_duration_hist,
recent.count_executions count_executions_recent,
hist.count_executions count_executions_hist
FROM hist
JOIN recent ON hist.query_id = recent.query_id
JOIN sys.query_store_query q ON q.query_id = hist.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE
recent.count_executions >= @min_exec_count
) AS results
JOIN
(
SELECT
p.query_id query_id,
COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_plan p
GROUP BY p.query_id
HAVING COUNT(distinct p.plan_id) >= 1
) AS queries
ON queries.query_id = results.query_id
WHERE additional_duration_workload > 0
ORDER BY additional_duration_workload DESC
OPTION (MERGE JOIN)
END
Thanks.
October 16, 2018 at 1:14 am
You'll either need to use a CURSOR/WHILE and change the connected database and run the query dynamically, or use the (undocumented) SP sp_msforeachdb; which still requires dynamic SQL.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 16, 2018 at 9:11 am
Thom A - Tuesday, October 16, 2018 1:14 AMYou'll either need to use a CURSOR/WHILE and change the connected database and run the query dynamically, or use the (undocumented) SP sp_msforeachdb; which still requires dynamic SQL.
thnx for the response. I tried to do that but again having trouble to implement. Can you give one small exam to see how to manipulate this in the actual code?
Thanks.
October 16, 2018 at 10:44 am
It worked, thanks. 🙂
Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply