The data stored in the history of queries are among the most valuable for any DBA. Tracking back the SQL Server query history is a must in many cases. For instance, there might be 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 analysis task, the full SQL Server query history is present, and there are user-friendly solutions to work with these data.
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 system 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\Query History) in SSMS
The option #1, for instance, lets you output 100 queries with the highest execution time (including all delays) using the following script:
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
As for the other options, it is worth highlighting the possibility of using SQL Complete in SSMS.
SQL Complete stores the primary information about 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 job more manageable.
Particularly, note the following features provided by SQL Complete:
- Check the execution of SQL statements – you may define if they succeeded or failed
- View the data in the query history for a particular period
- Sort the data for multiple criteria
- Search for a particular statement – it is convenient for any analyst working with large amounts of data
The SQL Complete 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 set it yourself in the SQL Complete options. Hence, if you’re working on a script, you get the full history related to every change you’ve made. Yet keep in mind that SQL Complete 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, you can monitor who and when executed any particular query, or receive other valuable information for research and analysis.
You can use any resource for checking the MS SQL Server query history by user. A variety of tools to perform the task is simply and conveniently at your service.
You can find more information regarding SQL Query History in the corresponding topic of the Documentation.
