Introduction

As a SQL Server developer or administrator, you’re always looking for ways to optimize query performance and ensure your databases are running smoothly. One powerful tool that can help you achieve this is the LAST_QUERY_PLAN_STATS feature. In this article, we’ll dive into what LAST_QUERY_PLAN_STATS is, how it works, and whether it’s safe to enable in your SQL Server environment.

What is LAST_QUERY_PLAN_STATS?

LAST_QUERY_PLAN_STATS is a feature introduced in SQL Server 2019 that captures the last actual execution plan for each query statement. This information is stored in the sys.dm_exec_query_plan_stats dynamic management view (DMV). With this data, you can gain valuable insights into how your queries are performing and identify potential bottlenecks.

How Does LAST_QUERY_PLAN_STATS Work?

When LAST_QUERY_PLAN_STATS is enabled, SQL Server captures the last actual execution plan for each query statement and stores it in memory. This information includes details such as:

• Query text
• Execution plan XML
• Performance metrics (e.g., CPU time, logical reads)
• Parameter values

The data is updated every time a query is executed, ensuring you have access to the most recent execution plan information.

Enabling LAST_QUERY_PLAN_STATS

To enable LAST_QUERY_PLAN_STATS, you need to set the LAST_QUERY_PLAN_STATS database scoped configuration option to ON. Here’s an example:

ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;

Once enabled, SQL Server will start capturing the last actual execution plan for each query statement.

Is It Safe to Enable LAST_QUERY_PLAN_STATS?

In general, enabling LAST_QUERY_PLAN_STATS is safe and has minimal impact on performance. However, there are a few considerations to keep in mind:

1. Memory usage: Storing execution plans in memory does consume additional resources. If you have a large number of queries, this can add up. Monitor your server’s memory usage to ensure it remains within acceptable limits.
2. Overhead: Capturing and storing execution plans introduces a small amount of overhead. In most cases, this overhead is negligible, but it’s something to be aware of, especially in high-performance environments.
3. Security: Execution plans can contain sensitive information, such as parameter values. Ensure that access to the sys.dm_exec_query_plan_stats DMV is properly restricted to authorized users.

Using LAST_QUERY_PLAN_STATS

To view the last actual execution plan for a query, you can query the sys.dm_exec_query_plan_stats DMV. Here’s an example:

SELECT 
    qps.plan_handle,
    qps.query_plan,
    qsq.query_id,
    qsq.query_hash,
    qsq.query_plan_hash
FROM 
    sys.dm_exec_query_plan_stats AS qps
    CROSS APPLY sys.dm_exec_sql_text(qps.plan_handle) AS qst
    CROSS APPLY sys.dm_exec_query_stats(qps.plan_handle) AS qsq;

This query retrieves the plan handle, execution plan XML, query ID, query hash, and query plan hash for each captured query. You can use this information to analyze query performance and identify potential optimization opportunities.

Conclusion

LAST_QUERY_PLAN_STATS is a powerful feature in SQL Server 2019 and later that allows you to capture and analyze the last actual execution plan for each query statement. By enabling this feature and querying the sys.dm_exec_query_plan_stats DMV, you can gain valuable insights into query performance and make data-driven optimization decisions.

To learn more about LAST_QUERY_PLAN_STATS and how to use it effectively, check out the official Microsoft documentation: sys.dm_exec_query_plan_stats (Transact-SQL)

Happy optimizing!