SQL Server 2019 sys.dm_exec_query_profiles

Question

LIGHTWEIGHT_QUERY_PROFILING is enable on database level by default in SQL Server 2019 .

If I disable it , will there still be results in querying sys.dm_exec_query_profiles ?

I have tried to disable LIGHTWEIGHT_QUERY_PROFILING , but there is still results in dm_exec_query_profiles , is it normal ??

Answer 1

You are only disabling it for one database, so it still in force for other databases.

Furthermore, the Docs say:

Starting with SQL Server 2016 (13.x) SP1, the standard query execution statistics profiling infrastructure exists side-by-side with a lightweight query execution statistics profiling infrastructure. SET STATISTICS XML ON and SET STATISTICS PROFILE ON always use the standard query execution statistics profiling infrastructure. For sys.dm_exec_query_profiles to be populated, one of the query profiling infrastructures must be enabled. For more information, see Query Profiling Infrastructure.

So if a process has issued commands to display query plan or Live query plan, the DMV will also be populated.

Answer 2

Good day @sakuraime ,

sys.dm_exec_query_profiles returns information when the profiling is enabled. The standard query execution statistics profiling infrastructure exists side-by-side with a lightweight query execution statistics profiling.

The profiling can be enabled in the session level as well for example.

According to your description you disabled LIGHTWEIGHT_QUERY_PROFILING in the database

But it might be enabled it in the session level for example by using

SET STATISTICS PROFILE on;    
GO  

or using

SET STATISTICS XML ON  
GO  

Another common scenario is If you are using the SSMS to view the live or actual execution plan. In this case the feature is enabled and the DMV will be filled with the information.

---

💬Ronen Ariely

Personal Site | Blog | Facebook | Linkedin

Answer 3

Hi,

>I have tried to disable LIGHTWEIGHT_QUERY_PROFILING, but there is still results in dm_exec_query_profiles, is it normal ??

In my environment, I use the following statement to turn off LIGHTWEIGHT_QUERY_PROFILING in the database scoped configuration, and then query dm_exec_query_profiles without returning results.
ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF

Please check whether to query the view sys.dm_exec_query_profiles under the context of the database with this configuration closed.