I have used below query to get the history queries on ran SQL Server but it's not giving me the whole history it just returns queries ran on nearly about last 1/2 an hour. I want to get the queries ran on week ago. Is there any solution?

SELECT  dest.text, deqs.last_execution_time
FROM    sys.dm_exec_query_stats AS deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE    dest.text NOT LIKE '%dest.text%' 
         order by deqs.last_execution_time desc
  • Good question, but I think this would be better answered by the dba gods on dbs.stackexchange.com Jan 29 '13 at 4:16

I believe this is handled internally in SQL Server's query cache using memory available on the system. I know you can empty all of the cache by running the following:


I just don't think you can alter how many queries it's saving.

Good luck.

  • 4
    You must NEVER EVER run this on a production system! This is very bad because you will clear the complete buffer cache. Jan 29 '13 at 7:14
  • thank u very much. i was just about to clear the buffer cache
    – chamara
    Jan 29 '13 at 7:27
  • i hope you don't do this on a production environment ;) Jan 29 '13 at 7:30
  • @MarkKremers: this doesn't clear the complete buffer cache. It only clears the procedures cache and forces recompilation of all subsequent plans. Not too good on a production db, but still, not as bad as a complete cold cache. To force a cold cache you'd need a CHECKPOINT and DBCC DROPCLEANBUFFERS. Chamara: never ever run anything in production without checking on a dev/test environment.
    – Marian
    Jan 29 '13 at 13:33
  • You are so right! Mixed the two up (was way too early, no coffee, no good sleep, you catch the point) Jan 29 '13 at 14:32

Well, the reason you don't see everything is quite easy, the buffer cache gets cleared when new resources are needed. You don't have unlimited memory available, so no unlimited cache to hold the plans. That's why you probably are seeing only a top level of queries...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy