• dma-669038 wrote: We typically go by below DMV based query - it has some limitations, only gives what is in cache since last reboot, but depending on your environment it might work well

    Not so sure about that "since last reboot". DMVs like sys.dm_exec_query_stats have a creation time; observation has shown a relatively short life (in the order of hours) on an active system. Appears it has a lot to do with statistics updates. Observation has also shown sys.dm_sql_text to have a limited lifespan. There is nothing in SQL BOL that implies the life of sys.dm_exec_cached_plans exists beyond the cache entry lifetime. The implication is that you need a feeling for the average cache lifetime in your particular server before you can reasonably set a monitor interval. By contrast cache entries on inactive (test) servers may appear to last forever

    It appears there may also be (rare) circumstances where cache entries are purged, and thus missed from your stats. SQL entries may also not necessarily be kept under certain circumstances