How To Find Last Executed Query In Sql Server

The data stored in the history of queries are amongst the most valuable for any DBA. Tracking back the SQL Server query history is a must in many cases. For instance, in that location might exist a need to investigate a particular work case, check the backup history, or recover a specific query if your SQL Server suffers a crash.

Fortunately, for any failure or routine assay task, the total SQL Server query history is nowadays, and there are user-friendly solutions to work with these information.

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

Queries are saved in the cache via organisation representations (sys.dm_exec_query_stats, sys.dm_exec_sql_text, and sys.dm_exec_query_plan)
Using SQL Server Profiler
Using Extended Events
Using the Query Store, starting from the 2016 version
Using SQL Complete (SQL Complete\Execution History) in SSMS

The pick #i, for case, lets you output 100 queries with the highest execution time (including all delays) using the following script:

          with due south every bit ( select top(100) creation_time, last_execution_time, execution_count, total_worker_time/1000 as CPU, catechumen(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime], qs.total_elapsed_time/chiliad as TotDuration, convert(money, (qs.total_elapsed_time))/(execution_count*thousand)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)) equally [AvgIO], [sql_handle], plan_handle, statement_start_offset, statement_end_offset, plan_generation_num, total_physical_reads, convert(coin, 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)) every bit [AvgIOLogicalWrites], query_hash, query_plan_hash, total_rows, convert(coin, total_rows/(execution_count + 0.0)) as [AvgRows], total_dop, convert(money, total_dop/(execution_count + 0.0)) equally [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)) every bit [AvgUsedThreads] from sys.dm_exec_query_stats equally qs with(readuncommitted) order by convert(money, (qs.total_elapsed_time))/(execution_count*thou) desc ) select southward.creation_time, s.last_execution_time, s.execution_count, s.CPU, s.[AvgCPUTime], s.TotDuration, s.[AvgDur], s.[AvgIOLogicalReads], s.[AvgIOLogicalWrites], s.[AggIO], south.[AvgIO], s.[AvgIOPhysicalReads], s.plan_generation_num, south.[AvgRows], due south.[AvgDop], s.[AvgGrantKb], south.[AvgUsedGrantKb], s.[AvgIdealGrantKb], s.[AvgReservedThreads], s.[AvgUsedThreads], --st.text every bit query_text, case when sql_handle IS Cipher so ' ' else(substring(st.text,(south.statement_start_offset+ii)/2,( example when southward.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*ii       else due south.statement_end_offset     cease - due south.statement_start_offset)/two  )) 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], due south.plan_handle, due south.query_hash, s.query_plan_hash from s cross apply sys.dm_exec_sql_text(due south.[sql_handle]) equally st cross use sys.dm_exec_query_plan(s.[plan_handle]) as sp

As for the other options, it is worth highlighting the possibility of using SQL Complete in SSMS.

SQL Consummate stores the primary information most the executed SQL statements in SSMS for a specified period. With this SSMS add-in, you can view, edit, and search T-SQL queries run in the database. A convenient interface makes this task more manageable.

Specially, notation the following features provided past SQL Complete:

Bank check the execution of SQL statements – you may define if they succeeded or failed
View the data in the query history for a particular catamenia
Sort the data for multiple criteria
Search for a detail argument – it is convenient for any analyst working with big amounts of data

The SQL Consummate solution stores every statement that you run in a file on a disk or in a table in a database specified with a connection string. The storage period can be any – you lot set it yourself in the SQL Complete options. Hence, if y'all're working on a script, you get the full history related to every change you've made. Yet continue in mind that SQL Consummate stores the history only for the queries executed via SSMS that SQL Complete is integrated with.

SQL Statements are saved in a list written to a file and/or a database. The solution can delete the older SQL query history logs automatically. Additionally, y'all can monitor who and when executed any particular query, or receive other valuable information for inquiry and analysis.

Fig.ane Execution History in SQL Complete

You can employ any resources for checking the MS SQL Server query history by user. A multifariousness of tools to perform the task is just and conveniently at your service.

Author
Recent Posts

Evgeniy is an MS SQL Server database annotator, developer, and administrator. He is involved in the development and testing of the SQL Server database direction tools. Evgeniy too writes SQL Server-related manufactures.

dbforge, sql complete, sql query history, sql server