Query Optimization

Overview

Query optimization is an important administrative task. But it is a difficult and subtle process. It involves extensive testing of various query structures and indexes and comparing the results.

Sql Server has a built in optimazation engine (see below) that usually but not always provides the best execution plan. You can also look at the actual execution plans and compare statistics when running variations of a query. Sql Server also provides the syntax for getting "Hints" when running queries. Finally you can use the Database Tuning Advisor to get suggestions for what indexes to create.

---

SQL Servers Query Optimization

Sql Server has a built in query optimization engine. Every time a query is run it goes through the following steps:

Parsing makes sure the query is valid SQL. Binding is mostly about name resolution, getting the table and column names. Optimization generates candidate execution paths and determines which has the least cost in cpu and total execution time.

Query optimization is complex and even the best optimizer doesn't get it right all the time. Still most of the time the optimizer does generate the optimal path.

---

Looking at a query with the execution plan and statistics

Open SQL Server Management Studio.

Start a new Query window

Select the Actual Execution Plan, and the Include Client Statistics from the toolbar

We are going to use Adventure works because it has more records. Our query will focus on the sales and sales details tables, but we will also bring in the Product name from the product table. We will use the dates and salesperson IDs for criteria.

Here is the query:

Use AdventureWorks2012

Select s.SalesOrderID, OrderDate, 
SalesOrderNumber, SalesPersonID,
ORderQty, Name, unitPrice,
 UnitPRiceDiscount
From Sales.SalesOrderHeader s
Inner Join Sales.SalesOrderDetail sd
on s.SalesOrderID=sd.SalesOrderID
inner Join Production.Product p
on p.ProductID=sd.ProductID
Where OrderDate between '2008-1-1' and '2008-1-31'
And SalesPersonID is not null

After you run this click the tab Execution plan. You will have the following output

Notice that it suggests a couple of indexes that are missing--in other words should be created, particularly on SalesOrderDate and SalesPersonID. Notice also that the majority of the cost is incurred processing the clustered indexes--which means going row by row through the table.

Next look at the statistics output

Open a second query window. We are going to create part of the suggested index

Create index ix_salesDate on Sales.SalesOrderHeader(OrderDate)

Now go back and rerun the query. Notice the query results include the new index. Now all the cost is in SalesDetail clustered index. This would suggest we should add another index.

Look at the statistics. Notice, interestingly the total cost has actually gone up and many of the indicators are worse.

This suggests that the next step would be to try an index on SalesPersonID and see if that improves the stats.

---

Query Hints

Query hints are a set of commands that you can add to a query to suggest an execution path. I am only going to show a couple. Query hints start with the Option keyword and have various arguments in parenthesis. The first example is a merge join which suggest executing the Joins as merges. Here is the code. The only change is in the last line.

Select s.SalesOrderID, OrderDate, SalesOrderNumber, SalesPersonID,
ORderQty, Name, unitPrice, UnitPRiceDiscount
From Sales.SalesOrderHeader s
Inner Join Sales.SalesOrderDetail sd
on s.SalesOrderID=sd.SalesOrderID
inner Join Production.Product p
on p.ProductID=sd.ProductID
Where OrderDate between '2008-1-1' and '2008-1-31'
And SalesPersonID is not null
Option (Merge Join)

Notice the change in results and statistics. Notice the change of joins to merge join and the suggestion to create and index.

Here are the statistics

Most of the other query hints are suggestions to the query optimizer. Look at http://msdn.microsoft.com/en-us/library/ms181714.aspx for a complete descriptions.

---

The DataBase Engine Tuning Advisor

To start the Tuning advisor go to the TOOLS menu in the Sql Server Management Studio. Connect to Localhost.

In the general tab, select "Plain Cache", and check Automart

Click the Tuning Options Tab. Leave everything as default except the time.

In Advanced Options set the max space to 4 mbs or so

Move it ahead 10 minutes or so.

Click start analysis.

The Tuning adviser has no suggestions. (Automart is too small a database to really analyze.) Here is the report:

---

Useful links:

Query hints

http://msdn.microsoft.com/en-us/library/ms181714.aspx

Overview of query optimization

https://www.simple-talk.com/sql/sql-training/the-sql-server-query-optimizer/
http://sqlblog.com/blogs/paul_white/archive/2012/04/28/query-optimizer-deep-dive-part-1.aspx
http://sqlblog.com/blogs/paul_white/archive/2012/04/28/query-optimizer-deep-dive-part-2.aspx

Advice

http://exacthelp.blogspot.com/2012/04/sql-server-query-optimization-tips.html
http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/sql-server-tuning

Database tuning advisor

http://msdn.microsoft.com/en-us/library/ms174202.aspx