Live query statistic is waiting for query plan forever

Question

I have a large query with performance issues. I want to run it in live query statistics mode, but instead of the execution plan, I see the inscription "Waiting for query plan".

In sys.dm_os_waiting_tasks I see that the session blocks itself with wait_type CXPACKET, CXCONSUMER, HTDELETE.

At the same time, I can simply build estimated execution plan without any problems.

Please advise what steps I can take to sort out performance issues.

Answer 1

I've found SSMS sometimes does not display the plan in a timely manner.

In those cases I just go to another window and run

SELECT *
FROM sys.dm_exec_query_statistics_xml(session_id_of_other_session)

to get the live plan.

It is not the same experience as you just get a snapshot of the live query statistics rather than a plan that updates itself automatically but you can just rerun the query against dm_exec_query_statistics_xml if you want another snapshot.

Also this requires more elevated permissions than just SHOWPLAN

Answer 2

Agreed with Martin, it's kind of a known issue within SSMS, that the Live Query Plan can be unreliable with rendering.

Aternative ways to view a snapshot of the plan while it's executing is to use:

1. sp_WhoIsActive - Which can be executed with EXEC sp_WhoIsActive @get_plans = 1

   or

2. The First Responder Kit - Which can be executed with EXEC sp_BlitzWho @GetLiveQueryPlan = 1

They're both probably similarly just calling the system function Martin mentioned under the hood though.

Regardless of methodology you use to view a snapshot, you should wait a short time and view another snapshot and compare the two to see which operation is hanging. One way you can see this is by finding the operation whose actual number of rows is still growing (and likely the operation to the left of it will still have 0 actual number of rows waiting on it). That'll help highlight the performance issue in your query plan.