Thank you Grant Fritchey!
- how can we check the cache for DMVs and how to set the cache for DMVs( or how to set the cache for different DMVs) ?
- aside from cache and DBCC FREEPROCCACHE, if there is any other factor to affect the data in DMVs? thanks!
You don't want to try to control the behavior of the cache. Let SQL Server handle that. Just know that you're dependent on the cache for the behavior of the DMVs. That's just how it works. There's nothing you do to turn it on, off, up or down. If you need more query information than is available through the DMVs, look to Query Store or Extended Events to gather that information.
As to things other than cache, it depends on the DMV. There are a lot of them for different purposes. However, we started talking about the query DMVs. Those are cache dependent and cache only. Anything that affects the cache, affects them. So, as I said, aging out of cache, clearing the cache (FREEPROCCACHE, failovers, reboots, dropping a db, detaching a db, off the top of my head list), never in cache in the first place (optimize for ad hoc, recompile hints). I'm sure I'm missing one or two things, but that's about it. Memory pressure can affect how much cache is available and how long stuff remains in cache.
I hope that helps.