如果需要，可以通过SQL Profiler监视SQL查询

我使用下面的查询来跟踪未启用跟踪分析器的SQL服务器上的应用程序活动。 该方法使用查询存储(SQL Server 2016+)而不是DMV的。这提供了更好的查看历史数据的能力，以及更快的查找。 捕获sp_who/sp_whoisactive无法捕获的短时间运行的查询是非常有效的。

/* Adjust script to your needs.
    Run full script (F5) -> Interact with UI -> Run full script again (F5)
    Output will contain the queries completed in that timeframe.
*/

/* Requires Query Store to be enabled:
    ALTER DATABASE <db> SET QUERY_STORE = ON
    ALTER DATABASE <db> SET QUERY_STORE (OPERATION_MODE = READ_WRITE, MAX_STORAGE_SIZE_MB = 100000)
*/

USE <db> /* Select your DB */

IF OBJECT_ID('tempdb..#lastendtime') IS NULL
    SELECT GETUTCDATE() AS dt INTO #lastendtime
ELSE IF NOT EXISTS (SELECT * FROM #lastendtime)
    INSERT INTO #lastendtime VALUES (GETUTCDATE()) 

;WITH T AS (
SELECT 
    DB_NAME() AS DBName
    , s.name + '.' + o.name AS ObjectName
    , qt.query_sql_text
    , rs.runtime_stats_id
    , p.query_id
    , p.plan_id
    , CAST(p.last_execution_time AS DATETIME) AS last_execution_time
    , CASE WHEN p.last_execution_time > #lastendtime.dt THEN 'X' ELSE '' END AS New
    , CAST(rs.last_duration / 1.0e6 AS DECIMAL(9,3)) last_duration_s
    , rs.count_executions
    , rs.last_rowcount
    , rs.last_logical_io_reads
    , rs.last_physical_io_reads
    , q.query_parameterization_type_desc
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY plan_id, runtime_stats_id ORDER BY runtime_stats_id DESC) AS recent_stats_in_current_priod
    FROM sys.query_store_runtime_stats 
    ) AS rs
INNER JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
INNER JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
INNER JOIN sys.query_store_query AS q ON q.query_id = p.query_id
INNER JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
LEFT OUTER JOIN sys.objects AS o ON o.object_id = q.object_id
LEFT OUTER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
CROSS APPLY #lastendtime
WHERE rsi.start_time <= GETUTCDATE() AND GETUTCDATE() < rsi.end_time
    AND recent_stats_in_current_priod = 1
    /* Adjust your filters: */
    -- AND (s.name IN ('<myschema>') OR s.name IS NULL)
UNION
SELECT NULL,NULL,NULL,NULL,NULL,NULL,dt,NULL,NULL,NULL,NULL,NULL,NULL, NULL
FROM #lastendtime
)
SELECT * FROM T
WHERE T.query_sql_text IS NULL OR T.query_sql_text NOT LIKE '%#lastendtime%' -- do not show myself
ORDER BY last_execution_time DESC

TRUNCATE TABLE #lastendtime
INSERT INTO #lastendtime VALUES (GETUTCDATE()) 

系统不会以这种方式记录查询。如果你知道你想提前这么做，你可以使用SQL Profiler来记录将要输入的内容，并在Profiler运行期间跟踪查询。

如果您需要通过SMSS执行的查询的历史记录。

你可能想试试SSMSPlus。

https://github.com/akarzazi/SSMSPlus

这个特性在SSMS中并不是现成的。

您需要SSMS 18或更新版本。

声明:我是作者。

查询历史可以通过系统视图查看:

sys.dm_exec_query_stats sys.dm_exec_sql_text sys.dm_exec_query_plan

例如，使用以下查询:

select  top(100)
        creation_time,
        last_execution_time,
        execution_count,
        total_worker_time/1000 as CPU,
        convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
        qs.total_elapsed_time/1000 as TotDuration,
        convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
        total_logical_reads as [Reads],
        total_logical_writes as [Writes],
        total_logical_reads+total_logical_writes as [AggIO],
        convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0)) as [AvgIO],
        [sql_handle],
        plan_handle,
        statement_start_offset,
        statement_end_offset,
        plan_generation_num,
        total_physical_reads,
        convert(money, total_physical_reads/(execution_count + 0.0)) as [AvgIOPhysicalReads],
        convert(money, total_logical_reads/(execution_count + 0.0)) as [AvgIOLogicalReads],
        convert(money, total_logical_writes/(execution_count + 0.0)) as [AvgIOLogicalWrites],
        query_hash,
        query_plan_hash,
        total_rows,
        convert(money, total_rows/(execution_count + 0.0)) as [AvgRows],
        total_dop,
        convert(money, total_dop/(execution_count + 0.0)) as [AvgDop],
        total_grant_kb,
        convert(money, total_grant_kb/(execution_count + 0.0)) as [AvgGrantKb],
        total_used_grant_kb,
        convert(money, total_used_grant_kb/(execution_count + 0.0)) as [AvgUsedGrantKb],
        total_ideal_grant_kb,
        convert(money, total_ideal_grant_kb/(execution_count + 0.0)) as [AvgIdealGrantKb],
        total_reserved_threads,
        convert(money, total_reserved_threads/(execution_count + 0.0)) as [AvgReservedThreads],
        total_used_threads,
        convert(money, total_used_threads/(execution_count + 0.0)) as [AvgUsedThreads],
        case 
            when sql_handle IS NULL then ' '
            else(substring(st.text,(qs.statement_start_offset+2)/2,(
                case
                    when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2      
                    else qs.statement_end_offset    
                end - qs.statement_start_offset)/2  ))
        end as query_text,
        db_name(st.dbid) as database_name,
        object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as [object_name],
        sp.[query_plan]
from sys.dm_exec_query_stats as qs with(readuncommitted)
cross apply sys.dm_exec_sql_text(qs.[sql_handle]) as st
cross apply sys.dm_exec_query_plan(qs.[plan_handle]) as sp
WHERE st.[text] LIKE '%query%'

使用下面的脚本可以看到当前运行的查询:

select ES.[session_id]
      ,ER.[blocking_session_id]
      ,ER.[request_id]
      ,ER.[start_time]
      ,DateDiff(second, ER.[start_time], GetDate()) as [date_diffSec]
      , COALESCE(
                    CAST(NULLIF(ER.[total_elapsed_time] / 1000, 0) as BIGINT)
                   ,CASE WHEN (ES.[status] <> 'running' and isnull(ER.[status], '')  <> 'running') 
                            THEN  DATEDIFF(ss,0,getdate() - nullif(ES.[last_request_end_time], '1900-01-01T00:00:00.000'))
                    END
                ) as [total_time, sec]
      , CAST(NULLIF((CAST(ER.[total_elapsed_time] as BIGINT) - CAST(ER.[wait_time] AS BIGINT)) / 1000, 0 ) as bigint) as [work_time, sec]
      , CASE WHEN (ER.[status] <> 'running' AND ISNULL(ER.[status],'') <> 'running') 
                THEN  DATEDIFF(ss,0,getdate() - nullif(ES.[last_request_end_time], '1900-01-01T00:00:00.000'))
        END as [sleep_time, sec] --Время сна в сек
      , NULLIF( CAST((ER.[logical_reads] + ER.[writes]) * 8 / 1024 as numeric(38,2)), 0) as [IO, MB]
      , CASE  ER.transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'ReadUncommited'
        WHEN 2 THEN 'ReadCommited'
        WHEN 3 THEN 'Repetable'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
        END as [transaction_isolation_level_desc]
      ,ER.[status]
      ,ES.[status] as [status_session]
      ,ER.[command]
      ,ER.[percent_complete]
      ,DB_Name(coalesce(ER.[database_id], ES.[database_id])) as [DBName]
      , SUBSTRING(
                    (select top(1) [text] from sys.dm_exec_sql_text(ER.[sql_handle]))
                  , ER.[statement_start_offset]/2+1
                  , (
                        CASE WHEN ((ER.[statement_start_offset]<0) OR (ER.[statement_end_offset]<0))
                                THEN DATALENGTH ((select top(1) [text] from sys.dm_exec_sql_text(ER.[sql_handle])))
                             ELSE ER.[statement_end_offset]
                        END
                        - ER.[statement_start_offset]
                    )/2 +1
                 ) as [CURRENT_REQUEST]
      ,(select top(1) [text] from sys.dm_exec_sql_text(ER.[sql_handle])) as [TSQL]
      ,(select top(1) [objectid] from sys.dm_exec_sql_text(ER.[sql_handle])) as [objectid]
      ,(select top(1) [query_plan] from sys.dm_exec_query_plan(ER.[plan_handle])) as [QueryPlan]
      ,NULL as [event_info]--(select top(1) [event_info] from sys.dm_exec_input_buffer(ES.[session_id], ER.[request_id])) as [event_info]
      ,ER.[wait_type]
      ,ES.[login_time]
      ,ES.[host_name]
      ,ES.[program_name]
      ,cast(ER.[wait_time]/1000 as decimal(18,3)) as [wait_timeSec]
      ,ER.[wait_time]
      ,ER.[last_wait_type]
      ,ER.[wait_resource]
      ,ER.[open_transaction_count]
      ,ER.[open_resultset_count]
      ,ER.[transaction_id]
      ,ER.[context_info]
      ,ER.[estimated_completion_time]
      ,ER.[cpu_time]
      ,ER.[total_elapsed_time]
      ,ER.[scheduler_id]
      ,ER.[task_address]
      ,ER.[reads]
      ,ER.[writes]
      ,ER.[logical_reads]
      ,ER.[text_size]
      ,ER.[language]
      ,ER.[date_format]
      ,ER.[date_first]
      ,ER.[quoted_identifier]
      ,ER.[arithabort]
      ,ER.[ansi_null_dflt_on]
      ,ER.[ansi_defaults]
      ,ER.[ansi_warnings]
      ,ER.[ansi_padding]
      ,ER.[ansi_nulls]
      ,ER.[concat_null_yields_null]
      ,ER.[transaction_isolation_level]
      ,ER.[lock_timeout]
      ,ER.[deadlock_priority]
      ,ER.[row_count]
      ,ER.[prev_error]
      ,ER.[nest_level]
      ,ER.[granted_query_memory]
      ,ER.[executing_managed_code]
      ,ER.[group_id]
      ,ER.[query_hash]
      ,ER.[query_plan_hash]
      ,EC.[most_recent_session_id]
      ,EC.[connect_time]
      ,EC.[net_transport]
      ,EC.[protocol_type]
      ,EC.[protocol_version]
      ,EC.[endpoint_id]
      ,EC.[encrypt_option]
      ,EC.[auth_scheme]
      ,EC.[node_affinity]
      ,EC.[num_reads]
      ,EC.[num_writes]
      ,EC.[last_read]
      ,EC.[last_write]
      ,EC.[net_packet_size]
      ,EC.[client_net_address]
      ,EC.[client_tcp_port]
      ,EC.[local_net_address]
      ,EC.[local_tcp_port]
      ,EC.[parent_connection_id]
      ,EC.[most_recent_sql_handle]
      ,ES.[host_process_id]
      ,ES.[client_version]
      ,ES.[client_interface_name]
      ,ES.[security_id]
      ,ES.[login_name]
      ,ES.[nt_domain]
      ,ES.[nt_user_name]
      ,ES.[memory_usage]
      ,ES.[total_scheduled_time]
      ,ES.[last_request_start_time]
      ,ES.[last_request_end_time]
      ,ES.[is_user_process]
      ,ES.[original_security_id]
      ,ES.[original_login_name]
      ,ES.[last_successful_logon]
      ,ES.[last_unsuccessful_logon]
      ,ES.[unsuccessful_logons]
      ,ES.[authenticating_database_id]
      ,ER.[sql_handle]
      ,ER.[statement_start_offset]
      ,ER.[statement_end_offset]
      ,ER.[plan_handle]
      ,NULL as [dop]--ER.[dop]
      ,coalesce(ER.[database_id], ES.[database_id]) as [database_id]
      ,ER.[user_id]
      ,ER.[connection_id]
from sys.dm_exec_requests ER with(readuncommitted)
right join sys.dm_exec_sessions ES with(readuncommitted)
on ES.session_id = ER.session_id 
left join sys.dm_exec_connections EC  with(readuncommitted)
on EC.session_id = ES.session_id
where ER.[status] in ('suspended', 'running', 'runnable')
or exists (select top(1) 1 from sys.dm_exec_requests as ER0 where ER0.[blocking_session_id]=ES.[session_id])

该请求显示所有活动请求和显式阻止活动请求的所有请求。

所有这些和其他有用的脚本都作为SRV数据库中的表示形式实现，该数据库是免费分发的。 例如，第一个脚本来自视图[inf]。[vBigQuery]，第二个来自视图[inf].[vRequests]。

对于查询历史，也有各种第三方解决方案。 我使用Dbeaver的查询管理器: 和SSMS内嵌的SQL Tools中的查询执行历史:

下面的SQL查询可以显示简单的查询日志:

SELECT last_execution_time, text
FROM sys.dm_exec_query_stats stats
CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) 
ORDER BY last_execution_time

下图是它的样子:

并且，下面的SQL查询可以显示简单的事务查询日志:

SELECT Operation, [Begin Time], [End Time] 
FROM fn_dblog(NULL,NULL)

下图是它的样子:

此外，我还不知道SQL查询将简单查询日志和简单事务查询日志显示在一起。