SQL Server: How to Find Costliest Nodes in Graphical Execution Plan

Graphical Execution Plan is first place, a DBA like to visit, during query optimization. If execution plan contains few nodes then it is easy to find out the culprit node. But what if target query is complex and resultant execution plan contains dozens of nodes. That’s, what happened this morning to one of my colleague, who was trying to optimize a complex query but execution plan was showing near 100 nodes.

Now finding out expensive nodes for such execution plans is also a challenge. This problem of finding costliest nodes, can easily be solved by getting execution plan XML and executing it with a query as explained by Mladen Prajdić 

http://weblogs.sqlteam.com/mladenp/archive/2010/01/21/SQL-Server-ndash-Find-the-most-expensive-operations-in-Execution.aspx

But, I think most beautiful way to solve this problem is SQL Sentry Free Tool “Plan Explorer”. You can download it free from follow link.

http://www.sqlsentry.com/plan-explorer/sql-server-query-view.asp

For example consider following query

USE AdventureWorks

GO

SELECT  *

FROM    Sales.vSalesPerson

Here is graphical execution plan of above query, produced by SQL Server Management Studio

And following is the execution plan generated by SQL Sentry Plan Explorer. Costliest nodes are already marked as red and orange as per their cost. So in just one look, one can easily find out these costliest nodes.