Geeks With Blogs
Evan Koch Musings on BizTalk Server and SQL Server

 For the purposes of this post I'llbe using hte following Continent/Country tables.

We’ll analyze a few different queries that, in terms of the end result, all do the same thing: they list out countries and their respective continent and order them by the country name.
So here’s our base case.

If you look at the steps, you can see that the Sort accounts for a little over half of the entire time that the query takes to run.  This seems a bit extraneous, so we’ll see what else can be done.
Below is our first attempt at reducing the execution time.  In the Country2 table I’ve placed a non-clustered index on the Country2Name column while still keeping the clustered index on Country2Key.

Here’s another attempt at speeding things up.  In the Country3 table I dropped the clustered index on Country3Key and created a clustered index on Country3Name.  In this case the ORDER BY clause is not required because the records are already stored in the desired order because of the clustered index.

So let’s try to plug in some numbers and see how much time each query would take.   The metrics for the Clustered Index Seek in each execution plan are the same, so I’ll assign it an arbitrary value of 16 ms just so we have something with which to work.  The math isn’t perfect, but neither is the costs displayed within the execution plan.
Query 1:
batch = sort + clustered index scan + clustered index seek
100% = 55% + 16% + 28%
56ms = 31ms + 9ms + 16ms
Query 2:
batch = index scan + key lookup + clustered index seek
100% = 22% + 39% + 39%
41ms = 9ms + 16ms + 16ms
Query 3:
batch = clustered index scan + clustered index seek
100% = 36% + 63%
25ms = + 9ms + 16ms
So just by altering the index, we’re able to reduce our execution time by more than half.  In most realistic scenarios the data probably needs to be sorted many ways, so changing the clustered index might help one query’s performance only to hurt another, but it might be worth adding some non-clustered indexes to realize performance gains.  An index consumes disk space and requires resources to maintain, so be sure to take these things into consideration when adding one. 
One of the other things that can be easiy addressed is table scans.  When this happens, the database engine has to iterate through all of the records in the table looking for the specified value.  When you see this happening, consider putting an index on the field.  With the index, the database engine can look for the particular value within the index and then get a list of rows that contain the value for which you're searching.  In the example below, Continent1 has a non-clustered index on ContinentName while Continent2 has no index.  One caveat to that, however, is that it may be faster to perform a table scan than check an index when the number of rows is small.  It's worth noting that SQL will add a clustered index when you define the primary key, just be aware you may want to change what fields are used in the clustered index.

Source code for this example can be found here.

Posted on Tuesday, August 14, 2007 9:22 PM | Back to top

Comments on this post: Basic Execution Plan Analysis

# re: Basic Execution Plan Analysis
Requesting Gravatar...
As a beginner to Query Plan analysis I found this article is useful for me

But the Time estimation is bit difficult to understand. However I appreciate this one.
Left by Tapas on Apr 01, 2008 9:35 AM

# re: Basic Execution Plan Analysis
Requesting Gravatar...
good one
Left by Sriju on Oct 10, 2012 8:10 PM

Your comment:
 (will show your gravatar)

Copyright © Evan Koch | Powered by: