How to Persist Information from Lightweight Query Profiling?

September 14, 2020

Accessing runtime information of a query execution plan is vital to get a precise understanding of resource usage, execution patterns to resolve performance issues. Runtime statistics is beneficial while debugging query performance issues, such as long-running queries, and queries that run indefinitely and never finishes. Historically query completion was a prerequisite for the availability of an actual query plan which encompasses query runtime statistics. Often time, it is not practical to re-run the problematic query to capture runtime statistics for troubleshooting purposes.

Starting with SQL Server 2016 (13.x) Management Studio, and SQL Server 2014 (12.x) Live Query Statistics can provide real-time insights into the query execution process. You can only use this feature while your query is running and cannot persist with this information to review later. Live Query Statistics uses standard profiling infrastructure, which is expensive.

One way to collect and save runtime information is to use extended event query_post_execution_showplan. Article Monitor System Activity Using Extended Events explains how you can use this extended event. Using query_post_execution_showplan extended event has a significant impact on the performance of the current workload. You can read about the effect in this article written by Jonathan Kehayias (Blog|Twitter).

Lightweight query execution statistics profiling (lightweight profiling) was launched with SQL Server (SQL Server 2014 (12.x) SP2. The performance overhead of collecting information about execution plans was reduced with the introduction of lightweight profiling.

So far three versions of lightweight profiling was released:

With the release of lightweight profiling, you can monitor real-time query progress while the query is in execution. There are a few ways to do this.

  • Using System dynamic management view sys.dm_exec_query_profiles which monitors real-time query progress while the query is in execution.
  • Using System dynamic management view sys.dm_exec_query_statistics_xml  which returns returns query execution plan for in-flight requests.

You can also persist in some of the runtime information by using extended events so you can examine the run-time statistics without rerunning the query.

  • query_thread_profile (starting SQL Server 2014 SP2) extended event exposes per-operator execution statistics of each node and thread.
  • query_plan_profile (starting SQL Server 2016 SP2 CU3 and SQL Server 2017 CU11) extended event captures the actual execution plan when you use query hint ‘QUERY_PLAN_PROFILE’.
  • query_post_execution_plan_profile (starting with 2017 CU14) extended event to capture actual execution plan. Because it is using a lightweight pooling these plans don’t contain the session wait types, CPU times.

Starting with SQL Server 2019 (15.x) a new database scoped configuration option ‘LAST_QUERY_PLAN_STATS’ was introduced that will let you enable or disable the collection of the last query plan statistics. Once this option is enabled, you can use dynamic management view sys.dm_exec_query_plan_stats which returns the equivalent of the last known actual execution plan for a previously cached query plan.

The Showplan output contains the following information (Quoted from Microsoft Documentation):

  • All the compile-time information found in the cached plan
  • Runtime information such as the actual number of rows per operator, the total query CPU time and execution time, spill warnings, actual DOP, the maximum used memory and granted memory

Here is the TSQL code to try this new feature:

--Turning on the Database Scoped Configuration
USE AdventureWorks;
GO
ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;
GO

--Running a Query
--I created this stored procedure for demo purpose
Exec dbo.demoExecutionplan;
GO

--Retrieving the plan_handle from cache
--Use the plan_handle to retrieve the last last execution plan
DECLARE @PlanHandle VARBINARY(64);
SELECT  @PlanHandle = cp.plan_handle 
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st 
WHERE OBJECT_NAME (st.objectid) LIKE '%demoExecutionplan%';
IF @PlanHandle IS NOT NULL
    BEGIN
        SELECT *
				FROM sys.dm_exec_query_plan_stats(@PlanHandle);
    END;
GO

LAST_QUERY_PLAN_STATS database scoped configuration assumes that you did not change the default setting of LIGHTWEIGHT_QUERY_PROFILING which is turned on in SQL Server 2019.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.