First published on MSDN on Aug 08, 2017
Have you ever analyzed a somewhat big plan? And while doing so did you ever wish you could search for something like table name, index name or column name?
17.2, we are including the ability to search all these and more in graphical showplan.
Let’s look at an example. I executed a query while an xEvent session with
is running (see sample session
). Opening the xel file provides a lot of insight. For example, going through the performance information collected per node, I see node 30 is doing a good part of the IO for this query.
Great. So now I want to open the associated cached plan, so I can see which operator this one is, and where in the plan it sits. I will correlate the query_plan_signed xEvent action with DMVs/DMFs (see example
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE CAST(qs.query_plan_hash AS BIGINT) = -832496756154281217
How will I search for node_id 30? The showplan xml is available to search on in SSMS:
Ok, so it’s a Table Spool, but the plan XML has more Table Spools, and it’s fairly large, so I still need to visually search for this Table Spool manually. Not the best or fastest experience.
But what if I could search for node id 30 while looking at the graphical showplan?
17.2, just use CTRL+F to start a search in graphical showplan (or right-click on a blank area of the plan, and in the context menu click on
option), and you can quickly see exactly where node id 30 is:
But I could search on all physical operations containing the word Spool, and move thru them using the arrows (highlighted):
I can actually search on any property I want to, such as table name, column name, schema name, index name, index type and many other properties, making navigation in graphical showplan even easier: