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.

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

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.

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

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.

Categories: SQL Server, SQL Server 2017