Introduction to SQL Server Query Optimization with Database Engine Tuning Advisor


By:   |   Updated: 2018-11-12   |   Comments (1)   |   Related: More > Performance Tuning

Itís the Database. Or Is It? Put an End to Finger Pointing!

Free MSSQLTips Webinar: Itís the Database. Or Is It? Put an End to Finger Pointing!

During this webinar, we'll show how SolarWinds Server & Application Monitor (SAM) can enable your IT team to monitor the health and availability of your Microsoft SQL Servers. SAM in the hands of your IT teams provides broad visibility across the stack to easily identify the underlying cause for database availability and performance problems.


Problem

Most of the times that we need to optimize a SQL Server query, a lot of questions and uncertainties come to our minds, just like if we could use any tool that would help with this type of improvement not only regarding the performance itself, but also in structural terms like with indexes, partitioning, DDL and DML, etc. Imagine you, being a SQL Server developer that needs to deal with lots of queries every day and still must consult the SQL Server DBA to check each of them, having such kind of optimizer in hands. That's when the Database Engine Tuning Advisor, present in SQL Server, comes to the table, providing a lot of great analysis and recommendations based on our queries and workloads. In this tip we will cover the use of this tool, especially improving the performance of our queries based on structural changes.

Solution

The famous DTA (Database Engine Tuning Advisor), basically analyzes databases and gives some recommendations. From the official docs, you can find a series of things it helps with:

  • Troubleshoot the performance of a specific problem query
  • Tune a large set of queries across one or more databases
  • Perform an exploratory what-if analysis of potential physical design changes
  • Manage storage space

For this example, we're going to use the AdventureWorks Databases and Scripts for SQL Server, which is great as a data sample for test purposes. So, first, open SQL Server Management Studio and import the database following the official steps.

Then, let's define the query we want to optimize

-- sales for group of customers by year
SELECT
    year = YEAR(header.OrderDate),
    sum = SUM(detail.LineTotal) 
FROM 
    Sales.SalesOrderDetail detail 
INNER JOIN 
    Sales.SalesOrderHeader header ON detail.SalesOrderID = header.SalesOrderID
INNER JOIN 
    Sales.Customer cust ON header.CustomerID = cust.CustomerID
WHERE
    cust.CustomerID > 10000 and cust.CustomerID < 10000000 
GROUP BY
    YEAR(header.OrderDate)

Make sure to have the proper database selected in order to run the query and get the following results

sql server management studio

What we need to do now is to open the "SQL Server Database Engine Tuning Advisor" going to the menu Tools > Database Engine Tuning Advisor. Authenticate and create a new session name with the same options we have below.

SQL Server Database Engine Tuning Advisor

Then, get back to the query, right click it and click on the option "Analyze Query in Database Engine Tuning Advisor":

Analyze Query in Database Engine Tuning Advisor

The Analyzer is going to run and give us an estimated improvement over the performance of our queries:

dta improvement recommendations

DTA includes a lot of different reports, from the performance and how costly the query is, to the way each entity relates to the other, the balance between the keys (indexes, etc.), and much more:

dta improvement recommendations

With them, we can check, for example, how much improvement we'll have when we decide to apply one of the suggested improvements. Let's look at the "Statement cost report", e.g., which says that our query can be improved by 33.07% if we apply the suggestions:

dta improvement recommendations

In order to check each improvement, go to the "Definition" column and click in one of them. Let's try the first one, related to an index recommendation:

dta improvement recommendations

Then, go back to SQL Server Management Studio and run the recommended script that'll create a new non-clustered index to the CustomerID column, just like:

create index

When finished, let's run the Tuning Advisor once again, go to the Reports tab and check the "Statement cost report" again:

dta improvement recommendations

The index creation allowed our select to execute in a more optimized way. If you follow the other recommendations, you can further improve the query and the percent improvement should get smaller with each change you make.

This concludes this article, where we covered a small fraction of the range of information we will have regarding the Database Engine Tuning Advisor and how it helps us to get recommendations based on the workloads we present for performance optimization.

Next Steps


Last Updated: 2018-11-12


get scripts

next tip button



About the author
MSSQLTips author Diogo Souza Diogo Souza has been passionate about clean code, data manipulation, software design and development for almost ten years.

View all my tips
Related Resources





Comments For This Article




Friday, November 16, 2018 - 1:57:05 PM - Tim Cartwright Back To Top

Beware the nonclustered indexes that the DTA will give you. It will give you the index keys in column order as they are listed in the table.†Which is often not the best order as your first column should provide the most distinctness. It will also suggest near or almost duplicate indexes. Take its recommendations with a grain of salt and do some due diligence before applying its suggestions blindly.†



download


Recommended Reading

Different Ways to Flush or Clear SQL Server Cache

Fastest way to Delete Large Number of Records in SQL Server

UPDATE Statement Performance in SQL Server

How to find out how much CPU a SQL Server process is really using

SQL Server stored procedure runs fast in SSMS and slow in application





get free sql tips
agree to terms


Learn more about SQL Server tools