Improving the performance of queries using SQL Server: Part 2

Introduction

In the previous article: Improving the performance of queries using SQL Server: Part 1, we discussed using SQL Profiler to identify queries that need looking at, to see if performance can be improved. We learned how to set the criteria that allow us to focus on the queries that are most likely to give us performance gains if we fix them.

In this article, we are going to look at how to take the information we gleaned from the SQL Profiler and use it to enhance our queries so that they are more performant.

Starting out

Before we dive into looking at indexes and execution plans, we need to consider whether or not a query is well written or not. There's no point in wasting time looking at plans if the fundamental query is so badly written that we would be better off rewriting it.

This query is a prime example of a badly written query that needs a serious kick up the backside before we even consider the plan:

CREATE PROCEDURE dbo.BadSelectUsingCursor
   @StartQTY INT,
   @EndQTY INT
AS
DECLARE @userID INT
DECLARE @forename NVARCHAR(30)
DECLARE @surname NVARCHAR(30)
DECLARE @qty INT
DECLARE @Price MONEY
DECLARE @ProductID INT

SET NOCOUNT ON

CREATE TABLE #Output
(
   Forename NVARCHAR(30),
   Surname NVARCHAR(30),
   Qty INT,
   TotalPrice MONEY
)
DECLARE getOrders_Cursor CURSOR FOR
SELECT Qty, UserId, productID FROM userOrders WHERE 
Qty BETWEEN @StartQty AND @EndQty

OPEN getOrders_Cursor
FETCH NEXT FROM getOrders_Cursor INTO @Qty, @userID, @productID

WHILE @@FETCH_STATUS = 0
BEGIN
   DECLARE userOrders_Cursor CURSOR FOR
   SELECT forename, surname FROM [user]
   WHERE [ID] = @userID
   OPEN userOrders_Cursor

   FETCH NEXT FROM userOrders_Cursor 
   INTO @forename, @surname

   SELECT @Price = Cost FROM
   Product WHERE Id = @productID

   SET @Price = @Price * @Qty

   WHILE @@FETCH_STATUS = 0
   BEGIN
       INSERT INTO #Output
       VALUES (@Forename, @Surname, @Qty, @Price)
       FETCH NEXT FROM userOrders_Cursor 
       INTO @forename, @surname
   END
   CLOSE userOrders_Cursor
   DEALLOCATE userOrders_Cursor

   FETCH NEXT FROM getOrders_Cursor INTO @Qty, @userID, @productID
END

CLOSE getOrders_Cursor
DEALLOCATE getOrders_Cursor

SELECT * FROM #Output

As we can see, the big problem with this query is that it is just badly written. It uses cursors and temporary tables completely unnecessarily. By the way, this is a version of a query that I had to rewrite a couple of years ago. Rewriting it saw performance gains of nearly 1500% - yes, that's right, 1500%.

Right, the first thing that we need to do then is to rewrite the query so that it cuts out the unnecessary work that it is doing. By reading through this query, we discover that it needs to return the customer first and last name, the quantity of goods they have ordered, and the total price based on a quantity range. Armed with this information, we rewrite this query as:

CREATE PROCEDURE dbo.BadSelectUsingCursor
  @StartQTY INT,
  @EndQTY INT
AS

SELECT
  forename,
  surname,
  qty,
  qty * Cost as TotalPrice
FROM
  [user] a
INNER JOIN
  userorders
ON
  userid = a.[id]
INNER JOIN
  product p
ON
  productId = p.[id]
WHERE 
  qty BETWEEN @StartQTY AND @EndQty
GO

Now, when this query runs, it returns the data far faster. In my test environment, the first version returns all of the records in 16 seconds, and the rewritten version returns the same records in less than a second.

At this point, we need to consider whether or not it is worth continuing to seek improvements in our query. Rerunning this query through SQL Profiler is a good way to see if we need to worry about this query.

Let's assume that we're done with this query now and move onto a query that appears to be well written.

Execution plans

Before we go any further, it's worth getting an understanding of what an execution plan is and why we should be bothered about it.

Basically speaking, an execution plan (sometimes called a query plan or query execution plan) is the series of steps the database will use to access information. Since the language that we use to access this data, SQL, is declarative, there are usually multiple ways to execute a query. Each method will have a different performance associated with it, and this is known as the cost.

It is the responsibility of the database engine to consider the different methods for determining which approach is the best. As developers, it is up to us to help the database choose the best method by making our queries as efficient as possible.

Right, now that we have a common understanding of what the execution plan is used for, how do we actually find out what the database is actually doing? If you remember, in the first article, we traced what SQL was being run on the database. The good news is that we can take this trace information and run it directly against the database. (In my examples, I'm using the Northwind database on SQL Server 2000 with Query Analyzer, but the results should be similar running this in SSIS and SQL Server 2005.)

The first query that we are going to look at is:

SELECT
   Title,
   FirstName,
   LastName
FROM
   Employees emp
LEFT JOIN
   Orders ord
ON
   emp.EmployeeId = ord.EmployeeId
INNER JOIN
   Shippers ship
ON
   ShipVia = ShipperID
WHERE
   Title LIKE 'Sales%' AND
   ShipName LIKE 'Chop%'

This is a fairly trivial query, but it yields an interesting plan. To run it, we copy it into Query Analyzer. Before we run the query, it is worth seeing what the database engine thinks the query will need to do. To get at this information, we will display the estimated execution plan. This can be found in Query > Display Estimated Execution Plan. This is the plan of this query:

Let's take a look at what this actually means.

Orders.PK_Order

This is a clustered index scan. A clustered index scan performs a scan on the clustered index (cunningly enough). In this case, the clustered index is the primary key on the orders table. Interestingly enough, there is a where clause that must be further considered by the engine here, which is the ShipName LIKE 'Chop%' portion of the query.

If the argument portion of this scan contains an item that is present in an ORDER BY clause, then the query engine must return the rows in the order which the clustered index has stored it. If it isn't present in the ORDER BY clause, then the engine is free to generate another (possibly better) approach to scan the index, which might not be sorted.

Shippers.PK_Shi and Employees.PK_Em

These are both clustered index seeks. They differ from the scan because they can use the ability of the RDBMS to seek indexes to retrieve rows from a clustered index. Similar to the scan, the where clause leads to the database engine determining which rows satisfy the seek condition, and then evaluates the rows that match the where condition from these. Like the scan, the presence of the item in an ORDER BY statement can affect the way that it is evaluated.

Nested loops

These are the physical ways in which join conditions are actually satisfied. They work by looping through the top query item and scanning the bottom row for matching rows. The database may, optionally, perform a sort first to improve the performance of the scanning because it will try to use indexes to match where possible.

The last part of the execution plan is the select. This is the physical act of retrieving the rows from the database.

At this point, we can run the query and see how it performs. If we want to see the execution plan that the database engine finally settled on, we just need to select Query > Show Execution Plan. When we run the query, the actual execution plan will be displayed. All being well, it shouldn't look much different to the estimated execution plan.

Index Tuning Wizard

With our query still showing in Query Analyzer, select Query > Index Tuning Wizard. This wizard will analyze the query to determine what indexes are needed to improve the performance of our query, and it should tell us what percentage performance gain we will get if we accept its recommendations.

Select Next > to get to the first real page of the wizard. Here we can choose the server and database that we want to analyze, plus how we want to manage existing indexes.

If we deselect Keep all existing indexes, the query engine has the chance to really change the way that the current query is optimized. Current indexes may be removed and new ones may be added to improve the performance, so at first glance, this seems to be an attractive option as it can generally recommend changes that offer more improvement than if you leave the indexes as they are. But we must beware of the temptation this offers. If we remove indexes to improve the performance of one query, we can end up ruining other queries that are running acceptably. Like so much else in life, just because we can do it doesn't mean that we should.

Right, we are going to select Thorough tuning and then Next >. The workload screen allows us to choose the query we are going to optimize. In this case, leave it set to SQL Query Analyzer selection, and then click Next >. This brings us to the table selection screen:

Choose Select All Tables and then click Next > again. The tuning wizard will now search analyze the query and recommend the indexes that are needed to improve the performance.

In our sample, we should see a 21% improvement, which isn't too shabby. Click Next > again, and we get the chance to save the changes or apply them immediately. Check the Apply changes box and then Next > again. Finally, select Finish to actually perform the update.

If we display the execution plan again, we can see that it differs from our original with the introduction of an Indexed Seek on Orders.Orders5. This means that it is now seeking on the index rather than performing the clustered index scan.

Now, we are going to move onto another more complex query.

SELECT DISTINCT
   Title,
   FirstName,
   LastName,
   ProductName,
   (dets.UnitPrice * dets.Quantity)
FROM
   Employees emp
LEFT JOIN
   Orders ord
ON
   emp.EmployeeId = ord.EmployeeId
INNER JOIN
   [Order Details] dets
ON
   ord.OrderID = dets.OrderID
INNER JOIN
   Products prod
ON
   prod.ProductID = dets.ProductID
INNER JOIN
   Shippers ship
ON
   ShipVia = ShipperID
WHERE
   (dets.UnitPrice * dets.Quantity) > 200

As you can see, we are picking up more tables, and the criteria is a little bit more complex. So, what does the database engine think of this? Well, this is the execution plan that this produces:

We can see that this looks pretty similar to the previous query. The only new things to look at are Compute Scalar and Sort/Distinct.

Compute Scalar

With the introduction of the calculation in the query, the optimizer knows that it needs to perform operations to return a calculated result. Here, we have used the same calculation in two places, so the optimizer knows that it can use the same scalar value in both locations.

Sort/Distinct

By adding the distinct keyword to the query, we tell the optimizer that it needs to sort the input so that it can remove duplicate rows.

If we run the Index Tuning Wizard on this query, following the same steps outlined above, we end up again with an estimated 21% improvement.

Running the query again gives us the following plan:

Hash Match/Inner Join

Note that we now have a different filter criteria added. We now have a Hash Match/Inner Join. This basically uses the top table to build the hash table, and the bottom table is compared against it to see if there are matches.

Gotchas

At this stage, you might think that the best thing that you can do is add an index to every field and that way the database engine will always query against the index.

It's tempting, but you should really avoid going this route because indexes must be maintained. This means that every time you do an insert, update, or delete, the indexes must be modified. The more indexes you have, the more that has to be done to maintain them. So, while selects may start to return quicker, you may find that all other operations degrade. Plus, as an added gotcha, indexes have a maximum size of 900 bytes. So, you can't index a Unicode field like this: NVARCHAR(500).

Conclusion

Using tools like SQL Profiler, Query Analyzer (or SSIS), and SQL Profiler, we can identify what the likely causes of slowdowns on our systems are. We can take long running queries and improve their performance, by rewriting or by tuning the indexes.

We haven't covered all types of execution plan operators, such as Sequential Scans, but I hope that I've presented enough information for you to want to explore this topic further.