SQL Server lightweight query profiling

Question

In SQL Server 2019 , I disable database scope option LIGHTWEIGHT_QUERY_PROFILING, however in the xevent , I still see query_post_execution_plan_profile has event come out . Is it normal ??

and also query_post_execution_plan_profile and query_post_execution_showplan capturing the same thing ?

Answer 1

Hi @sakuraime ,

Agree with Erland.

and also query_post_execution_plan_profile and query_post_execution_showplan capturing the same thing ?

No. For query_post_execution_plan_profile, This is a new xEvent available starting with SQL Server 2017 CU14 and SQL Server 2019. Much like the query_post_execution_showplan xEvent, the new query_post_execution_plan_profile outputs the actual query execution plan with fundamental data that’ usually required to troubleshoot query performance, such as the row counts flowing through the plan operators.
Please refer to this:https://techcommunity.microsoft.com/t5/sql-server/using-xevents-to-capture-an-actual-execution-plan/ba-p/392136

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

In SQL Server 2019 , I disable database scope option LIGHTWEIGHT_QUERY_PROFILING, however in the xevent , I still see query_post_execution_plan_profile has event come out . Is it normal ??

Depends. I would assume that if you included both events in your event session, it is normal. Now, if your event session includes events you did not selected, it starts to get interesting.

and also query_post_execution_plan_profile and query_post_execution_showplan capturing the same thing ?

Almost. But there is some data missing from the lightweight one. The best way to see the difference is to save the XML to file, and them compare them in a good diff tool, for instance Beyond Compare.