Performance Improvement using Database Engine Tuning Advisor

Back to: SQL Server Tutorial

Performance Improvement using Database Engine Tuning Advisor

In this article, I am going to discuss Performance Improvement using Database Engine Tuning Advisor in SQL Server in detail with examples. Please read our previous article where we discussed how to create indexes on the proper columns to improve the SQL Server performance.

Understanding the need for Database Engine Tuning Advisor and SQL Server Profiler

The SQL Server performances largely depend on how you created the indexes i.e. clustered, non-clustered indexes, etc. Most of the time, developers do not think about the indexes. By default SQL Server created a clustered index on the primary key column and that the only index you can find in most of the databases.

Next, what happens is, when these default settings are taken into the production server, then it is not going to be worked for a longer period of time i.e. it is not going to be worked when the workload increases.

So, when the workload changes or increases over a period of time (i.e. rows increase in the tables) then these default indexes are not going to handle the query and your SQL Server performance degraded.

As the workload increase in your production server, then you need to rethink your indexing strategy and this rethinking about the indexing strategy is not that simple. It’s actually a three-step complex process as shown in the below image.

First thing is that you have to collect the workload. In other words, initially, when you deploy your indexes to the production server, the workload maybe 10%, and now it increases to 80%.

Once you collect the workloads then you need to analyze that, as per the current workload, are the current indexes or the default indexes are appropriate. If they are appropriate then it is fine and you need to think something else to improve the SQL performance. If they are not appropriate, then you need to update the current or default indexes or you may need to create new indexes as per the workload.

The activities i.e. collecting the workload and analyzing as per the current workload, whether the current indexes are appropriate or not, cannot be a manual process. We need to use some kind of tool. This is because if you are working on a database having hundreds of tables and thousands of stored procedures and lots of transactions, then it is not possible to manually analyze the workload and come up with the appropriate indexes. For the above two activities, SQL Server provides two tools i.e. SQL Server Profiler and Tuning Advisor.

The SQL Server Profiler will help us to automate the collection of workload while the Tuning Advisor will help us to analyze the workload which is collected by SQL Profiler and come up with appropriate indexes.

Note: I am going to use SQL Server Developer Edition here. The Database Engine Tuning Advisor option is not going to work on Express Edition of SQL Server.

Example to understand SQL Profiler and Tuning Advisor:

What we will do here is, we will create a database (EmployeeDB) and will also create two tables (Employee and Company). We will insert a few records to one table (Company) and a large number of records to the other table (Employee). Then we will collect the workload using the SQL Server Profiler. Once we collect the workload, then we will run the Tuning Advisor on the workload to see whether the current indexes are appropriate or not. And then we will see how the performance can improve using the Tuning Advisor.

-- Create EmployeeDB database
CREATE DATABASE EmployeeDB
GO

USE EmployeeDB
GO

-- Create Company table
CREATE TABLE Company
(
   ID INT NOT NULL,
   CompanyCode INT NOT NULL,
   CompanyName VARCHAR(50) NOT NULL
)
GO

-- Create Employee table
CREATE TABLE Employee
(
   ID INT NOT NULL,
   EmployeeCode INT NOT NULL,
   EmployeeName VARCHAR(50) NOT NULL,
   EmployeeSalary INT NOT NULL
)
GO

Note: We created the above two tables without any keys or indexes.

Once you create the required database and tables, let us insert the required data into the tables.

Inserting data into Company table:

Please use the below SQL Script to insert 7 records into the Company table.

INSERT INTO Company VALUES (101, 100001, 'TEST1')
INSERT INTO Company VALUES (102, 100002, 'TEST2')
INSERT INTO Company VALUES (105, 100005, 'TEST3')
INSERT INTO Company VALUES (107, 100007, 'TEST4')
INSERT INTO Company VALUES (106, 100006, 'TEST5')
INSERT INTO Company VALUES (103, 100003, 'TEST6')
INSERT INTO Company VALUES (104, 100004, 'TEST7')

Inserting data into Employee table

Please execute the below SQL Script. Here, I am inserting around 39000 records into the Employee table using a while loop.

DECLARE @CODE INT;
DECLARE @Name VARCHAR(50);
DECLARE @ID INT;
DECLARE @Salary INT;
SET @CODE = 1000;
SET @ID = 1;
WHILE @CODE < 40000
BEGIN
   SET @Name = 'Name - ' + CAST(@ID AS VARCHAR(10))
   IF(@CODE < 5000)
   BEGIN
        SET @Salary = 10000;
        INSERT INTO Employee VALUES(@ID, @CODE, @Name, @Salary);
   END
   ELSE IF(@CODE < 15000)
   BEGIN
        SET @Salary = 20000;
        INSERT INTO Employee VALUES(@ID, @CODE, @Name, @Salary);
   END
   ELSE IF(@CODE < 25000)
   BEGIN
        SET @Salary = 30000;
        INSERT INTO Employee VALUES(@ID, @CODE, @Name, @Salary);
   END
   ELSE IF(@CODE < 30000)
   BEGIN
        SET @Salary = 40000;
        INSERT INTO Employee VALUES(@ID, @CODE, @Name, @Salary);
   END
   ELSE IF(@CODE < 35000)
   BEGIN
        SET @Salary = 50000;
        INSERT INTO Employee VALUES(@ID, @CODE, @Name, @Salary);
   END
   ELSE
   BEGIN
       SET @Salary = 60000;
        INSERT INTO Employee VALUES(@ID, @CODE, @Name, @Salary);
   END
   SET @ID = @ID + 1
   SET @CODE = @CODE + 1
END;

Let say the following SQL Query is frequently used.

SELECT * FROM Company WHERE ID = 105;
GO

SELECT * FROM Employee WHERE EmployeeCode = 30000;
GO

SELECT * FROM Employee WHERE EmployeeCode = 30000 AND EmployeeSalary = 50000;
GO

So, what we are going to do here is, we will first run the SQL Profiler and then we will execute the above SQL statements so that the SQL Profiler will generate the workload. Once we generate the workload file (technically called the Trace file), then we will run the Tuning Advisor on the workload to see what kind of indexes it suggests to us.

What is SQL Server Profiler?

The SQL Server Profiler is a nice tool which basically used to capture which type of SQL Statements fired on your SQL Server database.

How to Run SQL Server Profiler?

Click on the tools option and then select SQL Server Profiler which will open the SQL Server profiler window. Then provide the necessary credentials and click on the Connect button as shown below.

Once you click on the Connect button it will open the following window and from this window click on the Run button as shown below.

Once you click on the Run button, then it will start capturing lots of events, services, SQL Statements, Procedures, activities as shown in the below image. That means SQL Server Profiler is just working like a listener which sits in the backend and listens to what kind of SQL statements are fired or activities are going on the database.

Note: This tool is heavily used when you want to debug your application and when you want to know what kinds of SQL statements are fired and what kind of data is sent to the SQL queries.

By default, it captures the information for all the databases. But here we are only interested to capture the workload or SQL statement fired on the EmployeeDB database. In order to capture the workload only for the EmployeeDB database, what I am going to do is, first I will close the currently opened SQL Server profiler and then again login to the SQL Server Profiler.

Once you login to the SQL Server Profiler by providing the necessary credentials and clicking on the Connect button it will open the following window. As our main focus is on Tuning, so here we are going to choose the Tuning template instead of the default standard template as shown below.

Once you select the Tuning template, then you need to apply some kind filters so that it only listens to our EmployeeDB database. To do so, just click on the Event Selection button which appears next to the General button. From this window, click on the Custom filters button as shown in the below image.

Once you click on the Custom Filters button, it will open the Edit Filter window. From this, first, select the DatabaseName option from the left panel. From the right panel, select the Like option and provide your database name (in my case it is EmployeeDB) and click on the OK button as shown in the below image.

Once you click on the OK button then click on the Run button. With this change, now the SQL Server profiler will listen only to the EmployeeDB database.

Executing the SQL Statements:

Now, execute the following SQL statements 10 or 15 times.

SELECT * FROM Company WHERE ID = 105;
GO

SELECT * FROM Employee WHERE EmployeeCode = 30000;
GO

SELECT * FROM Employee WHERE EmployeeCode = 30000 AND EmployeeName = 'Name - 29001';
GO

Now, go to the SQL Server Profiler window and you will see that it captures the workload as shown below.

Now, stop the listener by clicking on the Stop option as shown below.

Next what we need to do is, we need to save this workload (i.e. the Trace file), and then we need to run the Tuning Advisor on this workload to see what kind of suggestions we will get.

Saving the Workload File:

Here we need to save the workload file as a Trace file. So, click on the File => Save As => Trace File option as shown below and then give a meaningful name (I am giving the name as SQLTuningOptimization) to the workload file.

Once you save your workload file, the next step is to run the Tuning Advisor.

Run Tuning Advisor:

In order to run the Tuning Advisor, click on the tools and then select the Database Engine Tuning Advisor option which will open the Tuning Advisor login window. From this window, provide the necessary credentials and click on the Connect button as shown below.

Once you click on the Connect button it will open the Database Engine Tuning Advisor window. From this window provides the workload file (i.e. the Trace file) and selects the database (in my case it is the EmployeeDB database) and finally clicks on the Start Analysis button as shown in the below image.

Once you click on the Start Analysis button, it will take some time analysis the workload and then it will show an estimated improvement and the index recommendations as shown below.

As you can see in the above image, it recommended us to create two indexes for an estimated improvement of 97%. Now you can click on the definition to see the script for the required index.

So, in this way, with the help of SQL Server Profiler and the Database Engine Tuning Advisor, you can improve the SQL Performance.

That’s it for today. Here, in this article, I try to explain Performance Improvement using Database Engine Tuning Advisor in SQL Server. I hope now you understood the need and use of both SQL Server Profiler and Database Engine Tuning Advisor in SQL Server.