CODEWITHSUNDEEP
sql-server
user3569267
user3569267

Reputation: 1135

Retrieve SQL server executed query

Can anyone tell me please how we can retrieve all sql server query that were executed, on a database, between 2 dates?

I find the following query 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 remarque also that this query give me only query that were executed after this date: 2014-12-19 14:06:12.250 , which is the date when I restart my server on which I have the databases.

Is there a query that give us all executed query of the day?

Thanks

Upvotes: 3

Views: 531

Answers (2)

Marty
Marty

Reputation: 1202

This query recently helped me "remember" some SQL I had recently used, and saved me a ton of time having to redo it all. Verbose, not date specific (and probably subject to same limitations 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

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93754

That's because you are looking in the procedure cache, and the plans that were used might no longer live there (server/instance restart, manually clearing the proc cache, etc..)

An initial query of sys.dm_exec_query_stats might produce inaccurate results if there is a workload currently executing on the server. More accurate results may be determined by rerunning the query Source : MSDN

To get the queries executed against an instance would be to create a SQL Trace.

In SQL Trace that capture the SQL:StmtCompleted event.

Upvotes: 1

Related Questions