How do you find recent queries in SQL Server?

 To find recent queries executed in SQL Server, you can use a few different methods depending on the level of detail you need and the configuration of your SQL Server environment. Here are some common ways to capture recent queries:

1. Using Dynamic Management Views (DMVs)

SQL Server provides dynamic management views (DMVs) that can help you track currently executing queries or recently executed queries.

a. Tracking Currently Executing Queries

You can query the sys.dm_exec_requests DMV to see currently running queries:

SELECT 
    r.session_id,
    r.status,
    r.start_time,
    r.command,
    r.sql_handle,
    t.text AS query_text
FROM 
    sys.dm_exec_requests r
CROSS APPLY 
    sys.dm_exec_sql_text(r.sql_handle) t;

This query will return the session ID, status, start time, the type of command being executed, and the actual SQL query text.

b. Tracking Recently Executed Queries (Cache)

If you want to find queries that have been recently executed and are still in the plan cache (i.e., those that haven't been evicted), you can query the sys.dm_exec_query_stats and sys.dm_exec_sql_text DMVs:

SELECT 
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    t.text AS query_text
FROM 
    sys.dm_exec_query_stats qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.plan_handle) t
ORDER BY 
    qs.execution_count DESC;

This will return queries that have been executed, the number of times they were executed, their average elapsed time, and logical reads.

c. Recent Queries from Extended Events or Profiler

You can use Extended Events (XEvents) or SQL Server Profiler to capture recent queries. Extended Events are preferred over Profiler for production environments as they have less overhead.

To set up an Extended Event session to capture recent queries:

CREATE EVENT SESSION [CaptureRecentQueries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION(sqlserver.sql_text)
    WHERE (sqlserver.database_id = DB_ID('YourDatabase'))
)
ADD TARGET package0.ring_buffer;
GO

-- Start the session
ALTER EVENT SESSION [CaptureRecentQueries] ON SERVER STATE = START;

You can later query the captured events using:

SELECT 
    event_data.value('(event/data[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text
FROM 
    sys.fn_xe_file_target_read_file('CaptureRecentQueries*', NULL, NULL, NULL);

This query reads the events from the ring buffer where recent query text is stored.

2. Using SQL Server Profiler

SQL Server Profiler can also be used to monitor recent queries. You can create a trace that captures events such as SQL:BatchStarting, SQL:BatchCompleted, or RPC:Completed to monitor queries in real-time.

To use SQL Profiler:

1. Open SQL Server Profiler.
2. Start a new trace, select the appropriate events (e.g., SQL:BatchStarting, SQL:BatchCompleted).
3. Filter by application name, database, or other parameters to limit the scope.
4. Run the trace to capture recent queries.

3. Using Default Trace (for recent activity)

SQL Server's default trace records recent server activity, including queries. You can query the default trace to get information about recent queries:

SELECT 
    t.DatabaseName, 
    t.ObjectName, 
    t.TextData, 
    t.StartTime, 
    t.LoginName, 
    t.ApplicationName
FROM 
    fn_trace_gettable(CONVERT(VARCHAR(150), (SELECT value 
                                             FROM sys.fn_trace_getinfo(NULL) 
                                             WHERE property = 2)), DEFAULT) t
WHERE 
    t.EventClass IN (11, 12)  -- EventClass 11 and 12 correspond to SQL:BatchCompleted and RPC:Completed
ORDER BY 
    t.StartTime DESC;

This method uses the default trace and filters for completed SQL batches and remote procedure calls.

4. Using SQL Server Audit (for compliance or monitoring)

If you have SQL Server Audit set up, you can use it to capture query execution events. SQL Server Audit allows you to track queries executed in the server, and you can configure it to log these events to a file or application.

Example of an audit specification:

CREATE SERVER AUDIT [AuditQueries]
TO FILE (FILEPATH = 'C:\AuditLogs\')
WITH (ON_FAILURE = CONTINUE);

CREATE SERVER AUDIT SPECIFICATION [AuditQuerySpec]
FOR SERVER AUDIT [AuditQueries]
ADD (SCHEMA_OBJECT_ACCESS_GROUP)
WITH (STATE = ON);

5. Using sys.dm_exec_sessions for Session Info

If you want to see queries for specific sessions (e.g., for a particular user or application), you can query the sys.dm_exec_sessions DMV:

SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    r.start_time,
    t.text AS query_text
FROM 
    sys.dm_exec_sessions s
JOIN 
    sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY 
    sys.dm_exec_sql_text(r.sql_handle) t;

Conclusion

• For real-time monitoring, sys.dm_exec_requests will give you the currently executing queries.
• For recently executed queries in the plan cache, sys.dm_exec_query_stats provides information about queries that are still in memory.
• For historical query data, Extended Events, Profiler, or Default Trace can be useful.