Prior to SQL Server 2005 if we want to find out the list of recently run queries we need to depend on SQL Profiler.
Now in SQL Server 2005 the life has become more easier(!). With the help of of an Dynamic Management Views (DMV) and a table valued function we can list the required result.
Points to note:
1. sys.dm_exec_query_stats -- This DMV returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.
2. sys.dm_exec_sql_text -- This table valued function returns the text of the SQL batch that is identified by the specified sql_handle
Solution:
Select
dmStats.last_execution_time as 'Last Executed Time',
dmText.text as 'Executed Query'
from
sys.dm_exec_query_stats as dmStats
Cross apply
sys.dm_exec_sql_text(dmStats.sql_handle) as dmText
Order By
dmStats.last_execution_time desc
Hope this helps!
Now in SQL Server 2005 the life has become more easier(!). With the help of of an Dynamic Management Views (DMV) and a table valued function we can list the required result.
Points to note:
1. sys.dm_exec_query_stats -- This DMV returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.
2. sys.dm_exec_sql_text -- This table valued function returns the text of the SQL batch that is identified by the specified sql_handle
Solution:
Select
dmStats.last_execution_time as 'Last Executed Time',
dmText.text as 'Executed Query'
from
sys.dm_exec_query_stats as dmStats
Cross apply
sys.dm_exec_sql_text(dmStats.sql_handle) as dmText
Order By
dmStats.last_execution_time desc
Hope this helps!
Comments
I have studied your aricles.very intresting and more usefull.
I am facing one problem in sql server 2000 hosting server.
some one created one guest user for database.I know guest user having limited access only.How can I restric this user.help me.
I was guessing wether could you hel me, i have SQL server2000 and something did occur a field of stock became equal to Cero, like if somebody had ran a query to make this, the question is may i know who made this query?
i would appreciate to much your help,
thanks in advance!!