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.

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

Learn multithreading and asynchronous programming and parallel programming in C# and .Net

 In this course, we will cover the below topics

•Multithreading from basic to advance level •Asynchronous Programming in C# •how to write asynchronous programming using Task •Parallel Programming • Parallel For /Foreach in C# •Parallel Invoke in C# •Parallel Linq in C#

Prerequisite •Visual studio should install on system 2013 or higher version •Basic knowledge of C# programming •Willing to learn new things What You will get from this Course •Source code will be available as per the topic we cover. •Confident on multithreading,asynchronous, and parallel programming

Multithreading  in C#

1. MultiThreading introduction in C# | Video

2. What is multiTasking | Video

3. Threading in C# | Video

4. Thread Class in C# | Video

5. Life Cycle of a thread in C# | Video

6. Drawbacks of Single-Threaded Applications | Video

7. Implement multithreading in C# | Video

8. Constructors of Thread class in C# | Video

9. ParameterizedThreadStart delegate in C# | Video

10. Passing data to the Thread function in a type safe manner in C# | Video

11. Retrieving data from Thread function using callback method | Video

12. Thread.Join and Thread.IsAlive functions of thread class | Video

13. Protecting shared resources from concurrent access in multithreading by locking in C# | Video

14. Protecting shared resources from concurrent access in multithreading by monitor in C# | Video

15. Difference between Locking and Monitor in Multithreading | Video

16. Monitor pulse, wait and pulseAll Method in multithreading in C# | Video

17.  ManualResetEvent in Multithreading in C# | Video

18. AutoResetEvent in multithreading in C# | Video

19.  Mutex in multithreading in C#  | Video

20.  Semaphore in multithreading in C# | Video

21. Deadlock in multithreading in C# | Video

22. Resolve Deadlock in multithreading in C# | Video

23. Thread Pool in C# | Video

24.  Performance testing using and without using Thread Pool in C# | Video

25. Performance Testing of a Multithreaded Application in C# | | Video

Asynchronous Programming in C#

1. Asynchronous programming Introduction in C# | Video

2. Synchronous Programming With C# | Video

3. Solution to the Synchronous Problem With C# | Video

4. Asynchronous Programming Patterns in .Net |  Video

5. Asynchronous Programming Model Pattern in .Net | Video

6. Event Based Asynchronous Programming Patterns in .Net | Video

7. Task based Asynchronous Pattern (TAP) in .Net | Video

8. Thread Vs Task in C# | Video

9. Creating a task object using Factory Property in C# | Video

10.  Creating a Task object using the Run method in C# | Video

11. Task using Wait in C# | Video

12. Task Return Value in C# | Video

13.  Returning Complex Type Value From a task in C# | Video

14. Chaining Tasks by Using Continuation Tasks in C# |

15. Create a continuation for multiple antecedents in C# | Video

16. Different overloaded Continuation Tasks in C# |

17. Async Await In C# | Video

18. Return Type of Asynchronous Method In C# | Video

19.  Exception Handling in C# Asynchronous Programming | Video

20.  When To Use Task And Async Await in C# | Video

Parallel Programming in C#

1. Parallel Programming introduction in C# | Video 

2. Parallel Programming in C# | Video

3. Task Parallel Library in C#? | Video

4. Parallel For in C# with Examples | Video

5. ParallelOptions class in C# | Video

6. Terminating a Parallel Loop | Video

7.  Parallel ForEach Loop in C# | Video

8. Parallel Invoke in C# with Examples | Video

9. PLINQ in C# | Video

#csharp #multithread #csharpprogramming

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