Get a query to execute a SQL server
Can anyone tell me how we can get the entire server SQL query that has been executed on the database between two dates?
I find the following request on the net, but it is quite complicated:
SELECT QS.creation_time Creation,
QS.last_execution_time LastExec,
QS.plan_generation_num NbComp,
QS.execution_count NbExec,
OBJECT_NAME(SQL.objectid) Procedures,
SUBSTRING
(
SQL.text,
QS.statement_start_offset / 2 + 1,
(
CASE
WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), SQL.text)) * 2
ELSE QS.statement_end_offset
END - QS.statement_start_offset
) / 2 + 1
) AS Instruction
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) SQL
I also repeat that this query only gives me a query that was executed after this date: 2014-12-19 14: 06: 12.250, which is the restart date of my server where I have the databases.
Is there a query that gives us the entire completed query of the day?
thank
source to share
This is because you are looking in the procedure cache and the plans you are using can no longer live there (rebooting the server / instance, manually clearing the proc cache, etc.)
The original sys.dm_exec_query_stats may produce inaccurate results if the server is currently running a workload. More accurate results can be determined by re-querying the query. Source: MSDN
To get queries executed against an instance would create SQL Trace
.
In SQL Trace that capture the SQL: StmtCompleted event .
source to share
This query recently helped me "remember" some SQL that I recently used and saved me a ton of time to re-do the whole thing. Detailed but not specific dates (and possibly with the same restrictions described by pdrb), but I was able to find what I was looking for:
SELECT t.[text]
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
source to share