Which DMV can be queried to capture the last actual execution plan for a specific query in SQL Server 2019?

To determine the last actual execution plan for a specific query in SQL Server 2019, the correct resource to query is sys.dm_exec_query_plan_stats. This dynamic management view (DMV) provides detailed execution plan statistics for each query execution. It includes information about how many times the query has been executed, the average and total execution times, and the total logical reads, among other metrics. Importantly, this DMV captures the execution plans that have been recently executed along with runtime statistics, making it possible to analyze how the query performed in practical scenarios.

On the other hand, sys.dm_exec_query_stats provides aggregate statistics for cached query plans, but it does not directly return execution plans. Instead, you would use it alongside sys.dm_exec_query_plan to get the execution plan details. The sys.dm_exec_query_plan specifically retrieves the XML representation of the execution plan for a specific plan handle but does not provide performance statistics. Lastly, sys.dm_exec_requests shows the current state of executing requests but does not give previous execution details or plans. Thus, for capturing the last actual execution plan along with its statistics, sys.dm_exec_query_plan_stats is the most appropriate choice.