Another set of DMVs that are helpful when troubleshooting T-SQL query performance are the query plan cache related DMVs. While the execution DMVs we discussed in the previous section contain point-in-time information that changes frequently, these DMVs contain information about queries that are currently in the plan cache, which can contain information all the way back to when the server was last restarted, depending on how long query plans remain in the cache.
The amount of time a plan remains in the cache depends on several factors, such as memory pressure, recompilation, schema changes, and so on. Provided that the server has been online for some time and no cache-flushing events have occurred, such as changing MAXDOP, or manually clearing the plan cache by running ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, these plan cache DMVs...