August 18, 2010 at 9:58 am
I ran this query but is gives me today list,
----------------------------------------------------------------------
SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC
------------------------------------------------------------------------
Is there any way to get compelte query list which from particular date to date
every time sql server restart all information which stored in dynamic management view get fired?
Please help..
August 18, 2010 at 11:10 am
Correct, because dynamic management views are not real objects. They are exposing the underline information with in SQL Server. In this particular DMV, the statements actually get pulled out without SQL restarting. This DMV I think keeps last 500 entries only, so if your statement in question has been more then 500 executions away it is gone. Also note that even though there might be a line in this DMV you might not be able to retrieve the SQL because it has been phased out from lack of usage.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
August 18, 2010 at 11:19 am
Mohit K. Gupta (8/18/2010)
This DMV I think keeps last 500 entries only
That's the missing index DMV.
Query stats returns data on the actual, current contents of the plan cache. It is not a list of recently run queries, it is a list of queries that are currently in the plan cache. There could be queries that have just run but will not be reflected in that.
http://sqlinthewild.co.za/index.php/2009/07/10/why-the-dmvs-are-not-a-replacement-for-sql-trace/
If you need to track recently run queries, rather consider a server-side trace.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2010 at 11:26 am
Thank You ,, 🙂
There is any way or query to find the execution of queries,
I need a last 10 days queries which run on my sql server..
August 18, 2010 at 11:28 am
Once we execute a query, in which location it is stored, and how many days it will be in there.
Can we set an option to stay a long or any option to stored in our tables ????
August 18, 2010 at 1:04 pm
Sharon Kumar (8/18/2010)
Once we execute a query, in which location it is stored, and how many days it will be in there
Executed queries are not stored anywhere.
What you were querying earlier is the plan cache, a portion of memory, but that's a cache of plans that SQL thinks it will reuse, not a log of executed queries. Plans will get thrown out of cache for various reasons (or not cached at all) and if/when that happens, evidence of those queries will disappear from the plan cache.
There is any way or query to find the execution of queries,
I need a last 10 days queries which run on my sql server..
SQL Trace. You need to have been running it to get the results.
If I may ask, why? 10 days of trace data (all queries executed on the server) is going to be an immense amount of data. I work with servers that produce 1 GB of trace data in 2 hours. That's just the queries executed, nothing more. What are you planning to do with it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2010 at 1:08 pm
thank for your help.:)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply