none
Live execution plan disappear RRS feed

  • Question

  • Any chance that the Live execution plan disappear option is disappear in Activity monitor ?

    Friday, July 31, 2020 3:36 PM

All replies

  • Which version of SQL Server are you connected to? The DMVs that give the data for the live query plan were added in SQL 2014, so on SQL 2012 you can never get any Live Query Plan.

    But I don't use Activity Monitor much, and maybe there are some conditions that needs to be fulfilled for the Live Query plan to be available.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, July 31, 2020 9:56 PM
  • SQL 2016
    Saturday, August 1, 2020 6:30 PM
  • Hi sakurai_db,

    For SQL server 2016, you have to enable extended event: query_thread_profile – this event allows you to get Live Query Statistics via Activity Monitor.

    Please refer to the blog How to get Live Execution Plan using Extended Events to get more detail steps.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Monday, August 3, 2020 2:46 AM
  • Thanks for reminding me Cathy!

    I'm not 100 per cent sure, but I think that for Live Query plan to be available from Activity Monitor, the infrastructure for lightweight query profiling must be active. This framework was introduced in SQL 2016 SP1, but in SQL 2016 and SQL 2017 it is not enabled by default. (It is on by default in SQL 2019.)

    As Cathy says, the the extended event query_thread_profile can be used to activate the new infrastructire. However, there is an easier way: enable trace flag 7412:

    DBCC TRACEON(7412, -1)

    I have this as a startup parameter on my SQL 2016 and SQL 2017 instances, so I did not think of it.

    I should add that it seems that it seems that there is a small delay before the alternative becomes enabled in the context menu.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, August 3, 2020 8:26 AM
  • Hi sakurai_db,

    Any update for this thread? Did the replies could help you?

    Best regards,
    Cathy 

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Tuesday, August 4, 2020 2:38 AM