>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ć 
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.
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.
Advertisements

Posted on March 11, 2011, in Performance Tuning, Sql Server 2005, Sql Server 2008, Third Party Tools. Bookmark the permalink. Leave a comment.

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

%d bloggers like this: