Exclusive Clothing Sale On Flipkart

Get up to 50% off on our latest collection.

Shop Now

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

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



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

Exclusive Clothing Sale On Flipkart

Get up to 50% off on our latest collection.

Shop Now