SQL Server Performance Improvement using Database Engine Tuning Advisor and SQL profiler

In this article, we will learn about Performance Improvement using Database Engine Tuning Advisor in SQL Server with examples.  

Need of Database Engine Tuning Advisor and SQL Server Profiler 

The SQL Server performances mainly depend on how we created the indexes meaning clustered and non-clustered indexes, etc. while creating an SP or any query developers don't think about the indexes in the table.

By default SQL Server create a clustered index top of the primary key column and that is the only index which we have most of the tables. 

so when we push these tables into the production server, for a longer time, the SQL server is not sustainable with respect to performance when as time goes on, data increase into the table or workload increase in the table, the SQL  server gives poor performance.

Approach to creating a good database tables

So as the data increase in the production server or in a table, then we need to think with respect to the indexing in a table we have to keep below a couple of points for making a table.

1. Think of the data workload in production

So as per this, we have to think about the workload of data in production, which means how much of rows can insert into a table 

2. Rethink indexes in a table

After collecting the data, we have to think like the default indexes which we have currently in a table are appropriate or if they may affect the performance in production. If indexes look good then it's fine and we can see something else to improve the SQL performance. If indexes are not appropriate, then we need to change the existing indexes or we need to create new indexes as per the data. 

3. Create/change the indexes in the SQL server

If we see in the table that indexes are not appropriate in a table, or not appropriate as per the table workload then we need to either change the indexes or we need to update them.

Now collecting the SQL table data or finding the indexes of a table is not a manual process, which means we can have hundreds of tables and SP, and finding the data/indexes in the tables or not an easy process if we do it manually, so we need to use tools for it.

So to find the workload of tables and indexes of tables we have below tools

1. SQL Server Profiler

2. Tuning Advisor.

The SQL Server Profiler helps us to find the data or workload automatically and Tuning Advisor helps us to analyze the data which is taken by the SQL Profiler and come up with the appropriate indexes.

Note: we are using SQL Server Developer Edition. The Tuning Advisor option is not available in the Express Edition of SQL Server.

SQL Profiler and Tuning Advisor

So to understand it let's create two tables called student and Teacher tables and then we will insert a couple of records like below

-- Create CollageManagement database
CREATE DATABASE CollageManagement
GO
USE CollageManagement
GO
-- Create a Teacher table
CREATE TABLE Teacher
(
   ID INT NOT NULL,
   TeacherName VARCHAR(50) NOT NULL,
   TeacherSalary INT NOT NULL
)
GO
-- Create a Student table
CREATE TABLE Student
(
   ID INT NOT NULL,
   StudentName VARCHAR(50) NOT NULL,
   StudentClass VARCHAR(50) NOT NULL,
)
GO
 

JavaScript

Note: - we created tables without indexes. 

Insert data into Teacher Table

INSERT INTO Teacher VALUES (101, 'Teacher1', 10000)
INSERT INTO Teacher VALUES (102, 'Teacher2', 20000)
INSERT INTO Teacher VALUES (103, 'Teacher3', 30000)
INSERT INTO Teacher VALUES (104, 'Teacher4', 40000)
INSERT INTO Teacher VALUES (105, 'Teacher5', 50000)
INSERT INTO Teacher VALUES (106, 'Teacher6', 60000)
INSERT INTO Teacher VALUES (107, 'Teacher7', 70000)
INSERT INTO Teacher VALUES (108, 'Teacher8', 80000)
INSERT INTO Teacher VALUES (109, 'Teacher9', 90000)
INSERT INTO Teacher VALUES (110, 'Teacher10',100000)

JavaScript

Insert data into Student tables

In the student table we will insert more data using the loop like below, if we have a large amount of data then we see performance tuning as well.

DECLARE @StudentName VARCHAR(50);
DECLARE @ID INT;
DECLARE @StudentClass  VARCHAR(50);
 
SET @ID = 1;
WHILE @ID < 51000
BEGIN
   SET @StudentName = 'Name - ' + CAST(@ID AS VARCHAR(10))
 
   IF(@ID < 2000)
   BEGIN
        SET @StudentClass = 'Class - 1';
        INSERT INTO Student VALUES(@ID, @StudentName, @StudentClass);
   END
   ELSE IF(@ID < 10000)
   BEGIN
        SET @StudentClass = 'Class - 2';
        INSERT INTO Student VALUES(@ID, @StudentName, @StudentClass);
   END
   ELSE IF(@ID < 20000)
   BEGIN
        SET @StudentClass = 'Class - 3';
        INSERT INTO Student VALUES(@ID, @StudentName, @StudentClass);
   END
   ELSE IF(@ID < 35000)
   BEGIN
        SET @StudentClass = 'Class - 4';
        INSERT INTO Student VALUES(@ID, @StudentName, @StudentClass);
   END
   ELSE IF(@ID < 45000)
   BEGIN
        SET @StudentClass = 'Class - 5';
        INSERT INTO Student VALUES(@ID, @StudentName, @StudentClass);
   END
   ELSE
   BEGIN
       SET @StudentClass = 'Class - 6';
        INSERT INTO Student VALUES(@ID, @StudentName, @StudentClass);
   END
   SET @ID = @ID + 1
  
END;
 

JavaScript

Now if select the teacher and student records from the table then we will see output like below

 

select * from Teacher
select * from Student

JavaScript

 

 Below is the Student Data.

Run SQL Server Profiler 

The SQL Server Profiler is a tool that helps us to find, which type of SQL Statements are triggered on the SQL Server database.

To open SQL server profiler then go to the tools option and then click on SQL server profiler.

Once SQL server profiles open, give the necessary information and click on connect.

 

After clicking on connect you will see a window like the below ad then click on Run

Note: - If you want to give a trace name, then you can give

Once you click on the Run button, the SQL server profiler will start capturing lots of events, SQL Statements, services, and SPs as shown below.

 

By default, the SQL server profile captures the information for all the databases which we have in the SQL server. But we want to only capture the information only for the CollageManagement database so for that. Close the SQL server profile and then again open the SQL server profile with credentials and now we basically want to do tuning for CollageManagement DB, so for that, we need to select the tuning template below

 

After selecting the Tuning template, then we need to apply some filters so that it only captures our CollageManagement database information. Tso for that, just click on the Event Selection tab which shows next to the General Button tab at the top. From this tab, click on the Column filters button as shown below.

 

After clicking on the Column Filters button, it will open the Edit Filter window. From this, select the DatabaseName option from the left panel, and From the right panel, select the Like option and give your database name (in my case it will be CollageManagement) and then click on the OK button as shown below.

 

 

Now click on the Run button. Now after this SQL server profile will capture only our database information.

Executing the SQL Statements:

Now let’s run the below query a couple of times

select * from Student where ID between 5 AND 100
 
select * from Teacher where ID = 105
 
select * from Student where StudentName = 'Name - 100'

JavaScript

Now go to SQL server Profiler and see, if it is capturing the information

 

Now we can stop capturing the information by clicking on top of the window icon

 

Now let’s save this record information and then we see the performance tuning for it.

So for it, go to the Fileà Save Asà Trace file

And then give trace file and save it

Run Tuning Advisor:

For running tuning Advisor, go to SQL server Tools and then select the Database Engine Tuning Advisor option. After clicking on this option, it will open a window like SQL server profiler, there also you give credentials and click connect

 

After clicking on Connect button it will open the Database Engine Tuning Advisor window. From here select the Trace file and then selects the database and then click on the Start Analysis button as shown below.

 

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

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

 

So in the popup window, we can see, that it suggests creating the indexes.

This is the way we can improve the SQL performance using SQL server Profiler and database engine tuning adviser.

--------------------------------------------------------------------------------

Differences Among CTE, Derived Table, Temp Table, Sub Query And Temp Variable

Sometimes we're required to store the results of queries temporarily for further use in future queries. SQL Server provides CTE, Derived table, Temp table, subqueries and Temp variables for this. All of these can be used to store the data for a temporary time. We will learn the differences among all these temporary storage mechanisms and also in what type of scenario or condition which mechanism will be used.

Today I will explain all of these temporary storage mechanisms and also explain the conditions and queries where they can be used.

CTE:
The CTE is an abbreviation of “Common Table Expression.” CTE was introduced in SQL Server. It works as a temporary result set that is defined within the execution scope of a single select, insert, update, delete statements. CTE is typically the result of complex sub queries. Similar to temporary tables CTE doesn’t store as an object; the scope is limited to the current query. CTE improves readability and ease in maintenance of complex queries and sub-queries.

Example:

1. WITH CTE(Emp_ID, Emp_Name, Project_Name)  
2. AS  
3.     (  
4.         SELECT e.Emp_Id, e.EmployeeName, p.Project_NameFROMdbo.Employee e INNERJOIN dbo.Project p ON e.Emp_Id = p.Project_Id  
5.     )  
6. SELECT * FROM CTE  

Output:


CTE with recursion query:

1. DECLARE @Min int;  
2. DECLARE @Max int;  
3. SET @Max = 10;  
4. SET @Min = 1;  
5.   
6. WITH Sequence_ AS  
7.   
8. (  
9.     SELECT @Min ASnum UNIONALL SELECT num + 1 FROM Sequence_ WHERE num + 1 <= @Max  
10. )  
11. SELECTnumFROM Sequence_  

Output:


In the above example we used the recursive CTE and generated the sequence between 1 and 10.

When to use CTE

• Use for recursive query.
• CTE is easy to implement compared to complex queries which involves several sub-queries.

Temp Variables:
Temp Variables are also used for holding the data fora  temporary time just like Temp tables. Temp variables are created using “DECLARE” statements and are assigned values by using either a SET or SELECT command. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration. This acts like a variable and exists for a particular batch of query executions. It gets dropped once it comes out of batch. Temp variables are also created in the Tempdb database but not the memory.

Example:

1. Declare Temp variable:  
2. Declare @My_var2TABLE  
3.     (  
4.         IIDint,  
5.         NameNvarchar(50),  
6.         SalaryInt,  
7.         City_NameNvarchar(50)  
8.     )  

Insert result set of another query into temp variable,

1. Insert Into @My_var2   
2. Select * from Employee  
3. WHERE IID<8 AND Salary>20000  

Retrieve data from temp variable: 

1. Select*from@My_var2  

Output:


Limitations Of Temp Variables

1. Unlike the majority of the other data types in SQL Server, you cannot use a table variable as an input or an output parameter.
   
2. The variable will no longer exist after the procedure exits - there will be no table to clean up with a DROP statement.
   
3. We cannot create a non-clustered index on a table variable, unless the index is a side effect of a PRIMARY KEY or UNIQUE constraint on the table. It can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement. Indexes cannot be added after the table has been created.

When to use Temp Variables:

• When you are required to use the current result set in next queries then store result into temp variables.
  
• When your data set is smaller, approximately less than 1000 records, then use temp variables , if your result set is larger then use temp table instead of temp variables.

Temp Table:
Temporary tables are tables that are available only to the session that created them. 

These tables are automatically destroyed at the termination of the procedure or session that created them. SQL Server provides the concept of temporary table which helps the developers in a great way. These tables can be created at runtime and can do all kinds of operations that one normal table can do. In SQL Server all temporary tables are present in tempdbdatabase.

Types of Temporary Tables:
SQL Server contain two types of Temporary tables:

1. Local Temporary Tables
2. Global Temporary Tables

Local Temporary Tables:
Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them. Local temp tables are only available to the current connection for the user. Names of Local temp tables start with(“#”) hash sign.

Example:

1. Create local temp table.  
2. CREATE TABLE# TEMP_SAVE  
3.     (  
4.         Emp_IdINT,  
5.         Emp_NameVARCHAR(30),  
6.         Project_NameVARCHAR(30)  
7.     )  
8.   
9. Insert data into temp table.  
10.   
11. INSERT INTO # TEMP_SAVE  
12. SELECT e.Emp_Id, e.EmployeeName, p.Project_NameFROMdbo.Employee e  
13. INNERJOIN  
14. dbo.Project p  
15. ON  
16. e.Emp_Id = p.Project_Id  

Select data from temp table.



Global Temporary Tables:
Global temporary tables are temporary tables that are available to all sessions and all users. Once this table has been created by a connection, like a permanent table, then it is available to any user by any connection. They are dropped automatically when the last session using the temporary table has completed. Names of Global temp tables start with (“##”) double hash sign.

Example:

Create a Global temp table 

1. Create Table## TEmp  
2.     (  
3.         IIDintidentity(1, 1),  
4.         Namenvarchar(50),  
5.         Salaryint  
6.     )  

Insert Data into Global Temp table

1. Insert Into##TEmp  
2. Select 'Pankaj', 25000 UnionAll  
3. Select 'Rahul', 24000 UnionAll  
4. Select 'Sanjeev', 23000 UnionAll  
5. Select 'Sandeep', 22000 UnionAll  
6. Select 'Naru', 27000   

Fetch Data From Global temp Table



When to Use Temporary Tables

1. We required to hold data from further query.
2. When we have a complex Join operation.
3. In some cases it may be a replacement of cursors.

Derived Table:
Derived tables are the tables which are created on the fly with the help of the Select statement. Derived table expression appears in the FROM clause of a query. In derived table server create and populate the table in the memory, we can directly use it and we also don’t require to drop the table. But scope of derived table is limited to the outer Select query who created it. Derived table can’t be used further outside the scope of outer select query.

Example:

1. SELECT * FROM  
2. (SELECT e.Emp_Id,e.EmployeeName,p.Project_Name FROM dbo.Employee e  
3. INNERJOIN  
4. dbo.Project p  
5. ON  
6. e.Emp_Id=p.Project_Id)Tab  
7. WHERE Tab.Emp_Id%2=1  

Output:


Example:


Consider the above table, now we have the sum the salary of employees for each group of age.

1. SELECT SUM(Tab.Salary)AS Total_Salary,Tab.Age  
2. FROM  
3. (SELECT e.Salary,e.AgeFROMdbo.Emp e  
4. WHERE e.Age>20)Tab  
5. GROUPBY Tab.Age  

Output:


When to Use:

• When you are required to perform some complex query and result of inner query work as table for outer query.

Subquery: 
A subquery is a SELECT statement that is nested within another statement. Subquery is also known as nested query. Result of subquery is used in the main query as a condition to further restrict the data to be retrieved. The subquery used in select, update, delete and insert command.

Example:

1. SELECT * FROM dbo.Emp e  
2. WHERE e.Id IN(SELECT e2.Emp_Id fromdbo.Employee e2);  

Output:


Example:

1. SELECT * FROM dbo.Emp e  
2. WHERE e.Id>(SELECT max(e2.Emp_Id) from dbo.Employee e2);  

Output:


When to use:

• When you require that inner query or condition must be fulfilled before generating any result.
• Use subquery when the result that you want requires more than one query and each subquery provides a subset of the table involved in the query.
• If the query requires a NOT EXISTS condition, then you must use a subquery because NOT EXISTS operator only work with subquery.

After the basic introduction of temp table, temp variable, subquery, derived table and CTE now we read the difference between all these.

Difference between subquery and derived table:

Subquery	Derived
Subqueries must be enclosed within parentheses.	Derived table must be enclosed within parentheses and table name must be provided.
Subquery can have only one column.	Derived table can have one or more column.
Subquery mainly use in where clause.	Derived table used in from clause.

Difference b/w Temp table and Temp variable:

Temp Table	TempVariable
Scope of Temp Table is wider the temp variables of. Local temporary tables are temporary tables that are available only to the session that created them and Global temporary tables are temporary tables that are available to all sessions and all users.	Scope of the Temp variables are limited up to current batch and current stored procedure.
Temp tables can be create using Create Table and Select Into commands	Temp variables only through Declare command can’t be created using select into command.
Temp tables can be drop through Drop Command.	We can not drop a Temp variables but we can use truncate command for temp variables.
Name of Temp table can have maximum 116 characters.	Name of a temp variables can have MAXIMUM 128 characters.
Temp table support foreign key concept.	Temp variable doesn’t support foreign key.
Temp tables support transaction management.	Temp variables doesn’t support transaction management. Rollback not work for temp variables.
Temp tables can easily handle large amount of data.	Temp variables are suitable with small amount of data.

Difference b/w CTE and Derived table:

CTE	Derived Table
A CTE can be referenced multiple times in the same query. So CTE can use in recursive query.	Derived table can’t referenced multiple times. Derived table can’t use in recursive queries.
CTE are better structured compare to Derived table.	Derived table’s structure is not good as CTE.