Lightweight query profiling

Lightweight query profiling (LWP) provides DBAs with the capability to monitor queries in real time at a significantly reduced cost of the standard query profiling method. The expected overhead of LWP is at 2% CPU, as compared to an overhead of 75% CPU for the standard query profiling mechanism.

For a more detailed explanation on the query profiling infrastructure, refer to https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-profiling-infrastructure?view=sqlallproducts-allversions.

New functionality in 2019

In SQL Server 2019, we have now improved LWP with new features and enhancements to the existing capabilities.

• In SQL Server 2016 and 2017, lightweight query profiling was deactivated by default and you could enable LWP at the instance level by using trace flag 7412. In 2019, we have now turned this feature ON by default.
• You can also now manage this at the database level through Database Scoped Configurations. In 2019, you have a new database scoped configuration, lightweight_query_profiling, to enable or disable the lightweight_query_profiling infrastructure at the database level.
• We have also introduced a new extended event. The new query_post_execution_plan_profile extended event collects the equivalent of an actual execution plan based on lightweight profiling,unlike query_post_execution_showplan, which uses standard profiling.
• We also have a new DMF sys.dm_exec_query_plan_stats; this DMF returns the equivalent of the last known actual execution plan for most queries, based on lightweight profiling.

The syntax for sys.dm_exec_query_plan_stats is as follows:

sys.dm_exec_query_plan_stats(plan_handle)

For a more detailed analysis, refer to this online documentation: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-plan-stats-transact-sql?view=sql-server-2017.

sys.database_scoped_configurations

If you are not certain of the current status of LWP, you can use the following code to check the status of your database scoped configurations. The value column is 1; therefore, using the sys.database_scoped_configurations view, you see that Query Plan Stats is currently enabled:

SELECT * FROM sys.database_scoped_configurations 
WHERE name = 'LAST_QUERY_PLAN_STATS'

The output is as follows:

Figure 1.22: Check the status of the database scoped configurations

To enable or disable LWP, you will use the database scoped configuration lightweight_query_profiling. Refer to the following example:

ALTER DATABASE SCOPED CONFIGURATION 
SET LIGHTWEIGHT_QUERY_PROFILING = OFF;

Activity monitor

With LWP enabled, you can now look at active expensive queries in the activity monitor. To launch the activity monitor, right-click on the instance name from SSMS and select Activity Monitor. Below Active Expensive Queries, you will see currently running queries, and if you right-click on an active query, you can now examine the Live Execution Plan!

Figure 1.23: The activity monitor