How to Check SQL Server Query History

Keeping a history of SQL Server queries is essential for several reasons. It enables database developers and administrators to identify slow queries, detect performance bottlenecks, and take corrective actions. They can also test and analyze SQL code, debug applications, and ensure that their solutions run efficiently. In addition, query history provides valuable insight into who executed which queries, when, and in what context, supporting auditing and compliance efforts.

Although SQL Server retains detailed query execution data for troubleshooting or performance analysis, finding the exact information related to a specific issue can sometimes be challenging. Database specialists, therefore, benefit from convenient and powerful tools that simplify this process.

Methods to check SQL Server query history

When you need to review the data about executed SQL statements in SSMS for a particular period, you can use several options:


Below you can find a comparison table for all these methods.

Method Description Ease of Use Key Features
Dynamic Management Views (DMVs) Uses system views like sys.dm_exec_query_stats, sys.dm_exec_sql_text, and sys.dm_exec_query_plan to retrieve query execution statistics and history. Moderate
  • Displays cached query execution data
  • Allows detailed query performance analysis
  • Data cleared on server restart
SQL Server Profiler A tool that captures real-time query execution details. Moderate to Advanced
  • Real-time query tracking
  • Captures execution data like duration, reads, writes, and CPU usage
  • May impact server performance under load
Extended Events A lightweight event tracking system for monitoring SQL Server activity, including query execution history. Moderate to Advanced
  • Low-impact performance monitoring
  • Fine-grained control over which events to track
  • Suitable for long-term monitoring
Query Store (SQL Server 2016+) Stores historical execution data, including performance metrics, and allows for query performance tuning and analysis. Easy to Moderate
  • Retains query history across restarts
  • Provides performance insights
  • Allows query performance tuning
dbForge SQL Complete (SSMS Add-in) An SSMS add-in that logs executed SQL statements and allows for easy retrieval, searching, and analysis of queries. Easy
  • Stores query history for a specified period
  • Allows searching, sorting, and viewing past queries
  • Data saved to a file locally

Now, let us examine some practical use cases of checking for the query history with different methods.

Querying the cache via system representations

SQL Server saves information about executed queries in the cache, allowing database specialists to access it using SQL queries.

For instance, we need to identify and analyze the most resource-intensive SQL queries that have recently executed on a SQL Server instance. The query below retrieves the detailed runtime statistics for the top 100 queries (based on their average duration per execution) from the SQL Server sys.dm_exec_query_stats view. It also delivers with their text and execution plans.

WITH s
AS
(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]
  FROM sys.dm_exec_query_stats AS qs WITH (READUNCOMMITTED)
  ORDER BY CONVERT(MONEY, (qs.total_elapsed_time)) / (execution_count * 1000) DESC)
SELECT
  s.creation_time
 ,s.last_execution_time
 ,s.execution_count
 ,s.CPU
 ,s.[AvgCPUTime]
 ,s.TotDuration
 ,s.[AvgDur]
 ,s.[AvgIOLogicalReads]
 ,s.[AvgIOLogicalWrites]
 ,s.[AggIO]
 ,s.[AvgIO]
 ,s.[AvgIOPhysicalReads]
 ,s.plan_generation_num
 ,s.[AvgRows]
 ,s.[AvgDop]
 ,s.[AvgGrantKb]
 ,s.[AvgUsedGrantKb]
 ,s.[AvgIdealGrantKb]
 ,s.[AvgReservedThreads]
 ,s.[AvgUsedThreads]
 ,
  --st.text as query_text,
  CASE
    WHEN sql_handle IS NULL THEN ' '
    ELSE (SUBSTRING(st.text, (s.statement_start_offset + 2) / 2, (
      CASE
        WHEN s.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2
        ELSE s.statement_end_offset
      END - s.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]
 ,s.[sql_handle]
 ,s.plan_handle
 ,s.query_hash
 ,s.query_plan_hash
FROM s
CROSS APPLY sys.dm_exec_sql_text(s.[sql_handle]) AS st
CROSS APPLY sys.dm_exec_query_plan(s.[plan_handle]) AS sp

Query the system views to get the information

Locating executed SQL statements in SSMS

SQL Server Management Studio (SSMS) is the default IDE for SQL Server databases. It is a powerful and user-friendly solution with a neat GUI. Although it lacks some functionality available in more advanced alternatives, SSMS remains the SQL Server developers' favorite, a habitual environment for performing database tasks.

One of the beneficial features of SSMS is its ability to enhance default functionality with the help of add-ins. dbForge SQL Complete is an add-in for SSMS and Visual Studio, serving as a SQL coding assistant with numerous additional options, including storing and accessing query history data.

It stores every statement you run in a file on a disk or in a table in a database, as specified by a connection string. The storage period can be any; you set it yourself in the Options: SQL Complete > Options > Query History.

View the query history options in dbForge SQL Complete settings

Note
dbForge SQL Complete stores the history only for the queries executed via SSMS with this integrated add-in.

You can view, edit, and search T-SQL queries run in the database using a smart visual interface. Hence, if you're working on a script, you can access the whole history of every change you've made.

Particularly, the following options are available in dbForge SQL Complete:

  • Check the execution of SQL statements for the succeeded or failed status
  • View the data in the query history for a particular period
  • Sort the data for multiple criteria
  • Search for a specific statement
  • Monitor who and when executed any particular query

To view the query history information in SSMS, go to SQL Complete menu > Query History.

View the query history in SSMS using dbForge SQL Complete

Conclusion

Storing and accessing SQL query history is crucial for database developers and DBAs in their daily work. SQL Server provides several ways to manage this, from built-in tools and manual SQL queries to specialized GUI-based solutions.

SSMS, the default IDE for SQL Server, includes functionality for viewing query history. However, with dbForge SQL Complete for SQL Server, this process becomes much simpler and more efficient.

dbForge SQL Complete is a powerful SSMS add-in that significantly extends the capabilities of the IDE. It introduces advanced features, enhances existing ones, and transforms SSMS into a more versatile development environment. You can explore its full functionality with a free 14-day trial to see how it improves your workflow.

FAQ

How do I view SQL Server query history?

You can view SQL Server query history using Dynamic Management Views (DMVs) such as sys.dm_exec_query_stats, sys.dm_exec_sql_text, and sys.dm_exec_query_plan. These views provide information about cached query execution data. Alternatively, you can use SQL Server Profiler to capture real-time query execution details.

For a more persistent solution, use the Query Store (available in SQL Server 2016 and later), which keeps a history of executed queries and their performance statistics.

Another option is the dbForge SQL Complete, an add-in for SSMS which allows you to explore and manage executed SQL statements conveniently.

Where is SQL Server history log?

SQL Server does not have a built-in, permanent query history log. However, you can still access recent query execution data from the SQL Server cache, though this data is cleared when the server restarts.

If you need persistent tracking, enable the Query Store, which retains query execution history across restarts.

For advanced query history features (e.g., searching, viewing, and analyzing past queries), you can use dbForge SQL Complete, an add-in for SSMS which saves query history to a file or database for long-term storage.

How to see last executed query in SQL Server?

To retrieve the last executed query, you can use Dynamic Management Views (DMVs). Other methods include using SQL Server Profiler or Extended Events, both of which capture real-time query execution details.

You can also use dbForge SQL Complete, an add-in for SSMS which displays recently executed SQL statements along with timestamps for easy review.