none
sql2008 management studio...query RRS feed

  • Question

  • We are using sql2008 management studio. Is there any option that recall our  Old executed query statement ?
    Friday, August 31, 2012 4:32 AM

Answers

  • Hi

    you may be able to use this query to retrieve recent old queries in cache

    select text from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_sql_text(cp.plan_handle)

    Friday, August 31, 2012 8:05 AM
  • Hi Sam,

    Greetings of the day,

    As per the case to the best of my knowledge:

    Every time you close query analyzer editor pane You are asked to save the query to a location if you don't save it. You only have an option of using the below query to retrive querys test and query execution plan in XML from the memory cache.

    SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query], decp.query_plan FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS decp ORDER BY deqs.last_execution_time DESC

    But their is a limatation using this:

    DBCC FREEPROCCACHE cleans all the exec plans and queries hence the same can't be retrived after freeproccache is used.

    Regards,

    Kalyan,

    VLDB


    Kalyan

    Monday, September 3, 2012 8:22 AM

All replies

  • No, there isn't such an option. If you want to re-use your queries, then you have to save them as a file.

    Olaf Helper
    Blog Xing

    Friday, August 31, 2012 4:45 AM
    Moderator
  • Hi

    you may be able to use this query to retrieve recent old queries in cache

    select text from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_sql_text(cp.plan_handle)

    Friday, August 31, 2012 8:05 AM
  • A caution: Kevin's method will be able to fetch the data, however it depends on the cache plans. So any activities that can be cleaned the plan from cache, you will never be able to get the data correctly.


    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Friday, August 31, 2012 8:48 AM
    Answerer
  • Hi Sam,

    Greetings of the day,

    As per the case to the best of my knowledge:

    Every time you close query analyzer editor pane You are asked to save the query to a location if you don't save it. You only have an option of using the below query to retrive querys test and query execution plan in XML from the memory cache.

    SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query], decp.query_plan FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS decp ORDER BY deqs.last_execution_time DESC

    But their is a limatation using this:

    DBCC FREEPROCCACHE cleans all the exec plans and queries hence the same can't be retrived after freeproccache is used.

    Regards,

    Kalyan,

    VLDB


    Kalyan

    Monday, September 3, 2012 8:22 AM