Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite
2

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
share|improve this question
Good question, but I think this would be better answered by the dba gods on dbs.stackexchange.com – Preet Sangha Jan 29 at 4:16

migrated from stackoverflow.com Jan 29 at 8:53

2 Answers

up vote 2 down vote

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...

share|improve this answer
up vote 1 down vote accepted

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:

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

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

Good luck.

share|improve this answer
2  
You must NEVER EVER run this on a production system! This is very bad because you will clear the complete buffer cache. – Mark Kremers Jan 29 at 7:14
thank u very much. i was just about to clear the buffer cache – chamara Jan 29 at 7:27
i hope you don't do this on a production environment ;) – Mark Kremers Jan 29 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 at 13:33
You are so right! Mixed the two up (was way too early, no coffee, no good sleep, you catch the point) – Mark Kremers Jan 29 at 14:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.