Exploring query plan cache DMVs

Another set of DMVs that are helpful when troubleshooting T-SQL query performance is 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 back to when the server was last restarted, depending on how long the query plans remain in the cache.

Note

The amount of time a plan remains in the cache depends on several factors such as memory pressure, recompilation, and schema changes. Provided that the server has been online for some time and no cache-flushing events have occurred, such as changing max degree of parallelism, or manually clearing the plan cache by running ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, these plan cache DMVs should give you a good idea of the overall query performance on the server.

Before describing...