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;
You can look to the Dynamic Management Views to get an aggregation of query metrics for queries that are currently in cache. However, it's only an aggregate, not specific query runs. Also, if the query is no longer in cache, then it's gone. You can look at the last execution time, possibly, to see if the query is there. But, let's say it was called three times in the last 48 hours, you won't know what that second call was, only the last one (last_execution_time) and the first one (creation_time).
You can only get this information if you already have a mechanism set up to get query results, such as Extended Events.
One other possibility though, if it's a query that caused blocking for longer than 30 seconds, it might be in the system_health Extended Event session. That does depend on how active your system is with errors & what not, those files do roll over, sometimes frequently.
You can use Extended Events to capture ALL queries running on a server, so later you can view any query that was run in a given period of time
Create an XE session that captures below events:
sql_batch_starting (and/or) sql_batch_completed
module_start (and/or) module_end
rpc_starting (and/or) rpc_completed
exec_prepared_sql
Please note that depending on your workload, running this kind of XE session might have BIG impact on server's performance , and event log files can grow huge
Above XE session, if no filters applied, will capture all queries running on server.
If your workload doesn't allow capturing ALL queries or you are interested in only top X (10,25,50) heaviest queries (sorted by CPU, memory, logical reads, etc.) - consider one of the monitoring tools like Redgate, Idera - these will show top queries for any period of time you specify
We need to know what was happening at a specific time in the past 48hours. made me think that he was in dire straits right now. It's not terribly important either way I suppose.
I have a similar question here:
is there a way to find out who was spid228 at a given time in the past?
that would point you to a server side trace.
and this one:
how to get History of queries executed with username in SQL
that would also point you to a server side trace, or a default trace, however there is this query that I use a lot(see below) however as Grant pointed out: "if the query is no longer in cache, then it's gone." :
/******************************************************
Script : Findout Who did what ?
Author : Kin Shah .. written for dba.stackexchange.com
*******************************************************/
USE master
go
SELECT sdest.DatabaseName
,sdes.session_id
,sdes.[host_name]
,sdes.[program_name]
,sdes.client_interface_name
,sdes.login_name
,sdes.login_time
,sdes.nt_domain
,sdes.nt_user_name
,sdec.client_net_address
,sdec.local_net_address
,sdest.ObjName
,sdest.Query
FROM sys.dm_exec_sessions AS sdes
INNER JOIN sys.dm_exec_connections AS sdec ON sdec.session_id = sdes.session_id
CROSS APPLY (
SELECT db_name(dbid) AS DatabaseName
,object_id(objectid) AS ObjName
,ISNULL((
SELECT TEXT AS [processing-instruction(definition)]
FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
FOR XML PATH('')
,TYPE
), '') AS Query
FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
) sdest
where sdes.session_id <> @@SPID
--and sdes.nt_user_name = '' -- Put the username here !
ORDER BY sdec.session_id
