asked on

finding recently run statements

i had run some recent statement from query window.. but i am trying to track exact parameters i sent.. but i could not see it from the below query..

there are a bunch of statements even from a week before- so i know memory is not the issue.. what am i missing.. isn't every select and execute statement cached?

thanks

SELECT  text,last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE last_execution_time between 
'2011-02-16 13:00:00.000' and '2011-02-16 14:45:00.000'
and text like '%sp_send_dbmail%'
ORDER BY qs.last_execution_time DESC

Open in new window

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lcohan

Or just run this without date limit:

SELECT top 100 text,last_execution_time
      FROM sys.dm_exec_query_stats qs
            CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
            CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
      WHERE text like '%sp_send_dbmail%'
      ORDER BY qs.last_execution_time DESC

anushahanna

ASKER

lcohan, still could not get it.. even though there is entry from before when these were written. do this come under your category you mentioned "I believe not all statements will stay in cache"- if so, what might they be?

SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anushahanna

ASKER

thanks lcohan- for your detailed query.