Simple • Straightforward

So easy a Caveman
Programmer can do it.

›› Let's Get Started

Execution Plans

What They Are For

Execution plans are the main roadmap for optimizing queries. These outputs will let you know exactly where problems are at with your code. To enable execution plans, you can click on the button to enable it as seen below:

Hard Drive

This will add in an extra window to your result tabs, as seen here:

Hard Drive

SQL that generates the above execution plan:
SELECT *
FROM Items

SELECT *
FROM Items it
JOIN Lookups itype
ON itype.PK_Lookups = it.FK_Lookups_ItemType

The execution plan will display all of your joins for a single query and even break apart procedures with multiple queries into separate sections. Lets start first with reviewing what the information above means.



Query 1: Query Cost (relative to the batch): 12% vs. Query 2: Query Cost (relative to the batch): 88%

These are important lines, as they will help you to identify which parts of large procedures are causing slowness. Our query that is using 2 tables to join information together is taking up the larger portion of the all the queries that were run (the batch). One thing to remember is that the entire batch will always equal 100%, and each individual query will just be a percentage of that 100%. If you add up the costs, 12% + 88%, you get 100%.



SELECT * FROM Items

This line, and others like it that are listed right below the Query Cost, are just to help us find the specific location to hone in on when trying to find areas to optimize.



Table Scan

This icon represents the time it took for the server to search through the table to get the necessary rows it needed, in this case all of them listed. Table scans are the alternative to the using an index to find records. SQL will search through each row, one by one, to determine if it needs to be returned. If you only wanted to find the items that cost over $100, it would go through each record and evaluate the cost column to see if it contained a number higher than $100. On large tables, this can be a very bad thing. Searching through all 200,000 records can take a long time, so please see our section on Indexes to learn how to speed this up.



Hash Match (Inner Join)

This is how SQL is joining our information together to be returned into the results.



Cost: 74%, Cost 13%, Cost 13%

This is the cost relative to the individual query that was run. Notice how 74% + 13% + 13% = 100%.

When you are to improve performance, try to find the query that has the biggest overall batch cost first. Then look for the highest cost inside that individual query to see where your problem joins are at. Sometimes you need to reduce the number of records being returned, you might also need to add in some indexes, or other times you just might need to re-write the whole thing to break it down into smaller pieces.





Comments

Name:
Title:   
Commenting Currently Disabled


ravi
5/22/2012 5:20:42 AM
costs


nice
Ali_tailor
9/17/2011 4:41:43 AM
SQL DBA


Great...
Margery
8/4/2011 1:44:38 AM



Geez, that's unebilvebale. Kudos and such.
Gail Shaw
7/5/2011 5:37:55 AM
Costs


Just bear in mind that those costs are estimates and there are several things that can make them completely wrong.
Jason
7/3/2011 3:14:53 PM
Yeap, that was a typo


Sorry about that. Typo fixed!
Wim
7/2/2011 4:12:04 PM



"you might also need to add in some indexes to remove unnecessary tables, or" Indexes TO remove tables?