3

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.

3

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.

1
  • thank you so much for your time. I shall try these out and look at the doc and references and feed back. Thanks again.
    – PTL_SQL
    Oct 25 '21 at 15:56
3

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://docs.microsoft.com/en-us/azure/azure-sql/database/monitoring-with-dmvs#the-cpu-issue-occurred-in-the-past

1
  • My apologies @DavidBrowne, I somehow missed this as being a separate response. Thank you so much for your time. I shall try the query out and look at the doc and feed back. Thanks again
    – PTL_SQL
    Oct 25 '21 at 16:00
1

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

4
  • This is a good answer for the topic; but I note that the OP's statement of the question strongly implies he wanted data from the last 48 hours from NOW. That is not going to be available, because no XE was in operation already. Oct 26 '21 at 0:56
  • 1
    @RossPresser features from other answers like Query store are not turned on by default either, so you have to "put them into operation" manually. And DMV (sys.dm_exec_query_stats) doesn't hold all information either as plans/queries can be pushed out of plan cache pretty quickly. P.S. Honestly I don't see any strong implications that OP needs 48 h data backwards from "NOW" Oct 26 '21 at 8:31
  • I'm not criticizing your answer -- it gives the best way to make sure you can track every query over a 48 hour period. The OP's phrasing 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. Oct 26 '21 at 13:29
  • 2
    thank you @RossPresser Oct 26 '21 at 15:53
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.