SQL Server Execution History Query Guide

In SQL Server, you can retrieve historical SQL execution records by querying system tables. Here are some commonly used system tables and views:

1. sys.dm_exec_query_stats: This view provides statistical information about recently executed queries, including the query’s execution plan, CPU usage, memory usage, and more.

SELECT
    *
FROM
    sys.dm_exec_query_stats

1. sys.dm_exec_sql_text: This view provides detailed information about SQL statements, including the SQL text and the statement’s execution plan.

SELECT
    *
FROM
    sys.dm_exec_sql_text(PLAN_HANDLE)

PLAN_HANDLE is obtained from the execution plan handle in the sys.dm_exec_query_stats view.

1. sys.dm_exec_requests: This view provides information about the currently executing requests, including the executing SQL statement, progress, and wait type.

SELECT
    *
FROM
    sys.dm_exec_requests

1. sys.dm_exec_sessions: This view provides information about the sessions currently connected to SQL Server, including session ID, login name, application name, etc.

SELECT
    *
FROM
    sys.dm_exec_sessions

1. The sys.dm_exec_connections view provides information about connections currently connected to SQL Server, including connection ID, client IP address, and connection status.

SELECT
    *
FROM
    sys.dm_exec_connections

1. The function sys.dm_exec_query_plan returns the execution plan for a given SQL statement.

SELECT
    *
FROM
    sys.dm_exec_query_plan(PLAN_HANDLE)

The PLAN_HANDLE is obtained from the sys.dm_exec_query_stats view in the execution plan.

Please note that the data in these system tables and views will only be retained in memory for a period of time in SQL Server. Once SQL Server is restarted or the cache is cleared, this historical data will be lost. If you need to preserve historical SQL execution records long-term, you may consider using SQL Server’s tracing feature or a third-party tool.