question

sakuraime avatar image
0 Votes"
sakuraime asked pituach edited

SQL Server 2019 sys.dm_exec_query_profiles

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 ??

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
0 Votes"
pituach answered pituach edited

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.