1

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.

0

2 Answers 2

4

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

Whilst answering a different question I found that the following query seems to reliably "fail" in this respect and SSMS just shows the live query plan after the event rather than during it (with no per operator timings).

DECLARE @X VARCHAR(MAX) = REPLICATE('X',8000);

SET @X = REPLICATE(@X,125)

SELECT TOP (5000)  @X
FROM master.dbo.spt_values v1, master.dbo.spt_values v2

Though strangely the live execution plan does show up if an OPTION (RECOMPILE) is added to the above.

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 does not show the operator statuses (e.g. OPEN, RUNNING, CLOSED) that are visible in the properties window in the live plan and it requires more elevated permissions than just SHOWPLAN.

The per operator timings are also different between the two views with the plan from dm_exec_query_statistics_xml being more reflective of the time the operator was actually busy.

You can also run

SELECT *
FROM sys.dm_exec_query_profiles
WHERE session_id = @session_id_of_other_session

to get the results in a tabular form.

I assume that the time shown in the "Live Query Statistics" plan is based on some calculation involving some of first_active_time, last_active_time,open_time, first_row_time, last_row_time, close_time whereas dm_exec_query_statistics_xml uses elapsed_time_ms.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.