Query Tuning (Part 3 - Graphical Execution Plans)

Hi everyone! Today's blog post will be focused around Graphical Execution Plans and is part of my Query Tuning series.

You can find previous parts of the series here:

Query Tuning (Part 1 - Evaluating Query Performance)
Query Tuning (Part 2 - Query Execution Order)

Now you have discovered that your queries are running slower than expected and you now have a grasp on the query execution order. The next step is to discover what exactly is causing the problem.

In order to do this, we use what is called Graphical Execution Plans, which is a graphical representation of the query as it has been executed. The purpose of this blog post is to explain how to successfully read and interpret these plans with examples.

Things To Be Aware Of

When looking at your plan, there are particular items that you should really look out for. These have been included in the table below (referenced from Microsoft SQL Server 2008 - Database Developer Training Kit: MCTS Exam 70-433 p 196-197, ISBN: 978-0-7356-2639-3): 

Examples of Graphical Execution Plans: 

Firstly, make sure that you have Actual Execution Plan included by right clicking and selecting "Include Actual Execution Plan" as highlighted in the screenshot below (or alternatively you can use the shortcut Ctrl+M): 

This will ensure that when you execute each of the queries, the plan will be generated as a separate tab along with the result set and system messages. Note that in some of these will be rather small on the blog itself so you will need to click on them in order to maximise them and see the image clearly. 

SELECT FROM TABLE

Query: 

Output: 

Performing a straight unfiltered SELECT on a table is the quickest query you will ever run. As you can see, the performance on the one below really can't be improved. 

For more information on each of the operations in the Execution Plan, just hover your mouse over the operation you wish to look at, as shown in the screenshot below: 

As you can see, a list of both actual and estimated costs are displayed. Generally, the higher the costs, the longer it will take for the query to run and bring back results. 

SELECT FROM VIEW

Query: 

Output: 

Performing a SELECT statement on a View will obviously take longer than a straight SELECT from a table as it contains JOINs to other tables and is returning those results as well.

JOINS 

In the examples below, take a look at how the performance of each query differs depending on the type of join used.

LEFT JOIN

Query: 

Output: 

RIGHT JOIN 

Query: 

Output: 

INNER JOIN

Query: 

Output: 

FULL OUTER JOIN 

Query: 

Output: 

As you can see, a FULL OUTER JOIN has many more operations, therefore return results slower than any of the other kinds of JOIN that I've looked at.

INSERT (Simple)

This is just a simple INSERT statement which is inserting data into a table which will have no impact on any other processes in the database.

Query: 

Output: 

All processes for this query are focused around the INSERT itself. 

INSERT (Complex with Stored Procedures attached)  

Now let's take a look at an INSERT statement which has other implications attached to it that run as a direct result of updating that particular table.

Query: 

Output: 

As you can see, the output for this INSERT query is huge! It has been split up into 8 different queries and form as part of a batch. Each part of the batch has a percentage at the top to aid the developer to see what part of the batch is slowing the query down. In this instance, the INSERT fires a trigger which opens a cursor to determine if a new row has been added. If so, then it generates a new Full Record Number for the new row (which is easier to display that a unique identifier). As you can see though, opening the cursor to retrieve the next AssetID is actually 19% of the entire batch of 8 queries, which is quite large. 

UPDATE (Simple)

This is just a simple UPDATE statement performed against as table which will have no impact on any other processes in the database.

Query: 

Output: 

As you can see, the majority of the query cost does towards searching for the type name.

UPDATE (Complex with Stored Procedures attached)

Now let's take a look at an UPDATE statement which has other implications attached to it that run as a direct result of updating that particular table.

Query: 

Output: 

Similarly to the INSERT statement that has been performed against the same table, the output has had to be broken down into subqueries as part of a batch.

DELETE 

The following query performs a DELETE on an Asset.

Query: 

Output: 

Despite how short the DELETE query is, it still takes up quite a few resources when it is run against the database.

Overall

Overall, Execution Plans are a great visual way of telling what could be reducing the speed of your queries. If you keep an eye on the items listed in this blog post and return Execution Plans for the core functions of your database, you should be able to determine where your issues lie pretty quickly.

What Next...

Now that the we have looked at Execution Plans and can analyse them to determine where potential problems lie, tomorrow I will be discussing best practices for reducing the query cost as much as possible.

As A Final Word...

Thank you for reading today's blog post! If you have any questions/comments/feedback, please leave them in the comments section below and I will get back to you as soon as I can. Alternatively, please like my SQL Genius Facebook Page and leave a message on there.