November 13, 2020 at 3:49 pm
we can use the folloiwng sql statment to monitor the statement executed before, but we can use the following to retrieve all executed sql statement since SQL Server starting up to now? or only retrieve the sql statement executed several ago?
and if there is a time limit , the information of the sys.dm_exec_sql_text can be overwritten after sever hours, thanks!
SELECT QS.creation_time, SUBSTRING(ST.text, (QS.statement_start_offset / 2) + 1,
((CASE QS.statement_end_offset WHEN - 1 THEN DATALENGTH(st.text) ELSE QS.statement_end_offset END - QS.statement_start_offset) / 2) + 1)
AS statement_text, ST.text, QS.total_worker_time, QS.last_worker_time, QS.max_worker_time, QS.min_worker_time
, QS.creation_time
FROM sys.dm_exec_query_stats QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) ST
ORDER BY QS.creation_time DESC
November 14, 2020 at 4:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 15, 2020 at 9:37 am
It will show you details only on plans that exist in the query cache. As soon as a query plan is being deleted from the query plan, you won't be able to see it's details with you query.
November 16, 2020 at 12:30 am
It will show you details only on plans that exist in the query cache. As soon as a query plan is being deleted from the query plan, you won't be able to see it's details with you query.
thanks !
How long the query cache will be deleted/removed? if we use dbcc dropcleanbuffers, the query cache will be removed ?
November 16, 2020 at 12:50 pm
There is no single number. It depends on many factors. For example - how often the code (e.g. stored procedure or function is being used), the code of the procedure, the code that is using the procedure (for example that it uses the with recompile option), the cache's size etc'. As far as I remember dbcc dropcleanbuffers only cleans the data cache and does not clean the data cache
Adi
November 16, 2020 at 1:16 pm
Thank you Adi Cohn for your kind help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply