New in SSMS: Searching in Showplan

Cross post with https://aka.ms/sqlserverteam

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?

In SSMS 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 query_thread_profile is running (see sample session here). 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 here).

 SELECT qp.query_plan 
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?

Starting with SSMS 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 Find Node 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:

This feature is available anywhere a graphical showplan is open, including Plan Comparison, Plan Scenarios and Query Store. More information on these other SSMS features available in New in SSMS: Query Performance Troubleshooting made easier!

Pedro Lopes (@sqlpto) – Senior Program Manager