Searching in SQL Execution Plans – SSMS 2017


Analyzing large execution plan is challenging – it takes time, requires manual effort and is tedious. SQL Server Management Studio 2017 gives us the ability to search on execution plans. If you want to look at say a particular table, index, index scan/seek operation in the execution plan, you can now just search for those operators in the execution plan.

Why do you need to look at Execution Plans?
The Query Optimizer in SQL Server is responsible to find the most efficient way to execute a TSQL query. The optimizer will generate and evaluate many plans (unless there is already a cached plan) and will choose the lowest-cost plan – the plan it thinks will execute the query as fast as possible and use the least amount of resources – CPU and I/O. A query execution plan is the breakdown of how the optimizer executes a query.

To troubleshoot performance issues, most of the times we generate the execution plan and look at the expensive operators.

How do you search in Execution Plans?
To search in the execution plans in SSMS 2017, you can do any of the following 2 ways —

  • Use CTRL + F
  • Right click on the execution plan, and click on ‘Find Node’ option.

Find Node

You can search on a number of properties, making navigation in graphical execution plan even easier —

Search in Execution Plans

Let’s say you want to search on a large execution plan and find all operators involving a particular table, you can select ‘Table’ , ‘Contains’ and ‘TableName’. When you click the next arrow, it will highlight you the matching operators only.

Search Tables

 

Another useful tip will be to search your execution plan for all ‘Scan’ operations –

Physical Op Scan

 

SSMS 2016 gave us an Execution Plan Comparison Tool and its integration into Management Studio makes troubleshooting performance issues so much easier. You can read about it here –

Compare Execution Plans in SQL Server 2016

Conclusion

Searching in Execution Plan is a great addition to SQL Server Management Studio and will be immensely helpful when analyzing large execution plans.

Read more about this feature on the MSSQL Tiger Team blog here.

One thought on “Searching in SQL Execution Plans – SSMS 2017

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s