How to get historical queries in SQL Server, based on time

Please how do I get the query that was running at a specific time in SQL Server 2016? Something similar to AWR report in Oracle? We need to know what was happening at a specific time in the past 48hours.

I'd appreciate some guidance plus documentation please.

thank you.

The query store tracks historical queries with hourly granularity, eg:

If the issue occurred in the past and you want to do root cause
analysis, use Query Store. Users with database access can use T-SQL to
query Query Store data. Query Store default configurations use a
granularity of 1 hour. Use the following query to look at activity for
high CPU consuming queries.

WITH AggregatedCPU AS (SELECT q.query_hash, SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_millisec, SUM(count_executions * avg_cpu_time / 1000.0)/ SUM(count_executions) AS avg_cpu_millisec, MAX(rs.max_cpu_time / 1000.00) AS max_cpu_millisec, MAX(max_logical_io_reads) max_logical_reads, COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans, COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids, SUM(CASE WHEN rs.execution_type_desc='Aborted' THEN count_executions ELSE 0 END) AS Aborted_Execution_Count, SUM(CASE WHEN rs.execution_type_desc='Regular' THEN count_executions ELSE 0 END) AS Regular_Execution_Count, SUM(CASE WHEN rs.execution_type_desc='Exception' THEN count_executions ELSE 0 END) AS Exception_Execution_Count, SUM(count_executions) AS total_executions, MIN(qt.query_sql_text) AS sampled_query_text
                           FROM sys.query_store_query_text AS qt
                                JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id
                                JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
                                JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id=p.plan_id
                                JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id=rs.runtime_stats_interval_id
                           WHERE rs.execution_type_desc IN ('Regular', 'Aborted', 'Exception')AND rsi.start_time>=DATEADD(HOUR, -2, GETUTCDATE())
                           GROUP BY q.query_hash), OrderedCPU AS (SELECT query_hash, total_cpu_millisec, avg_cpu_millisec, max_cpu_millisec, max_logical_reads, number_of_distinct_plans, number_of_distinct_query_ids, total_executions, Aborted_Execution_Count, Regular_Execution_Count, Exception_Execution_Count, sampled_query_text, ROW_NUMBER() OVER (ORDER BY total_cpu_millisec DESC, query_hash ASC) AS RN
                                                                  FROM AggregatedCPU)
    SELECT OD.query_hash, OD.total_cpu_millisec, OD.avg_cpu_millisec, OD.max_cpu_millisec, OD.max_logical_reads, OD.number_of_distinct_plans, OD.number_of_distinct_query_ids, OD.total_executions, OD.Aborted_Execution_Count, OD.Regular_Execution_Count, OD.Exception_Execution_Count, OD.sampled_query_text, OD.RN
    FROM OrderedCPU AS OD
    WHERE OD.RN<=15
    ORDER BY total_cpu_millisec DESC;

https://learn.microsoft.com/en-us/azure/azure-sql/database/monitoring-with-dmvs#the-cpu-issue-occurred-in-the-past

StackExchange Database Administrators Q#301630, answer score: 4