Performance Improvements in SQL Server

Back to: SQL Server Tutorial

Performance Improvements in SQL Server

In this article, I am going to discuss Performance Improvements in SQL Server with a detail explanation with examples. This is the first article in this series and here in this article, we are going to discuss some of the basics but important concepts in order to improve the performance in SQL Query. As part of this article, we are going to discuss the following pointers in detail.

1. What are Logical and Physical Operators in SQL Server?
2. How to know what are the logical and physical operators used for a given SQL Statement?
3. What happens when we execute a SQL Statement in SQL Server?
4. What SQL Query Plan is and how to read the Query plan in SQL Server.
5. Understanding the Logical Read and Physical Read in SQL Server.
6. How to measure Logical and Physical Read in SQL Server?

What are Logical/Physical Operators in SQL Server?

Every SQL Statement or query that we execute on SQL Server has been broken down into several execution steps or processes. These execution steps or processes are called as Iterators or operators. For example, you have iterators or operators like Aggregation, Sum, Average, Insert, Update, Delete, etc. In simple words, we can say that the iterators or operators are nothing but the logic that we want to execute in the SQL Statement.

Types of Iterators/Operators in SQL Server:

There are two types of Iterators or Operators in SQL Server.

1. Logical Iterator/Operator
2. Physical Iterator/Operator

Every SQL statement that we execute on SQL Server will comprise of logical operators and these logical operators in turn point towards the physical operators. In order to understand this please have a look at the following image.

The Logical Operator is nothing but the conceptual plan it’s not the actual logic. For example, if you want to make an Inner Join, the logical operation or plan is to do is Inner Join but how do you do the inner join for that you need the actual logic and the actual logic is a physical operator. In order to understand this, please have a look at the following image.

As you can see in the above image, the Inner Join Logical operator can be computed by either using a Merge Join or by using a nested loop. So, the logical operators are the conceptual plan whereas the physical operators are the actual plan. One logical operator can point towards multiple physical operators. The SQL Server Engine will decide for a logical operator which physical operator to use. For example, For the Inner Join Logical Operator, the SQL Server Engine may use Merge Join or nested loops physical operator.

The following MSDN link gives you the relationship between all the physical and logical operators available in SQL Server.

https://docs.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference?view=sql-server-ver15

How to know what are the logical and physical operators used for a given SQL Statement?

In Order to understand this, we are going to use the following Employee table.

Please use the below SQL Script to create and populate the Employee table.

CREATE TABLE [Employee](
 [EmployeeID] [int] PRIMARY KEY,
 [Name] [varchar](50),
 [Code] [int]
)
GO

INSERT INTO Employee VALUES(1, 'James', 10001)
INSERT INTO Employee VALUES(2, 'David', 10002)
INSERT INTO Employee VALUES(3, 'Pam', 10003)
INSERT INTO Employee VALUES(4, 'Sara', NULL)
INSERT INTO Employee VALUES(5, 'Smith', NULL)
INSERT INTO Employee VALUES(6, 'Anurag', 1006)

Once you create and populate the Employee table, then execute the following select query to fetch all the records from the employee table.

SELECT * FROM Employee

Once you execute the above select query, let us see the execution plan by clicking on the Display estimated execution plan (Ctrl + L) button as shown below.

Once you click on the Display estimated execution plan button, you will get the following output. As you can see, to execute the SELECT * FROM Employee SQL Statement, it uses a Clustered Index Scan Physical Operator.

What happens when we execute a SQL Statement in SQL Server?

Executing a SQL Statement in SQL Server is a three-step procedure. Please have a look at the following image to understand the different steps.

Step1: Parse

Once you fire the SQL Statement to the SQL Engine, the first thing it does is checks for syntax. It checks whether all the syntaxes are fine or is there any error. Once the syntaxes are fine, it generates something called a Query Processor Tree. This Query Process Tree is given to the Optimizer.

Step2: Optimize

The Optimizer looks at the Query Processor Tree, and check what are the tables involved in the SQL Statement. It takes the table and analyzes the data statistics such as it analyzes how many records or rows exist in the table? Is there any kind of primary key or composite primary key exists in the table? Does the table span more than one data page, etc.?

Depending upon the Query Processor Tree and Data Statistics, it generates the estimated query plan. And this estimated plan is fed to the SQL Query Engine.

Step3: Execute

In this step, the actual plan is generated and the query is executed.

Note: The Estimated Query plan and the actual query plan are two different things altogether. The Estimated Query plan is generated by the Query Optimizer and at the execution time the SQL Engine tries to execute the query using estimated query plan but if it finds something better than the estimated query plan, then it executes the query using the better one and this is what we called an actual query plan. If you right on the context menu, then you will see both the estimated and actual query plan as shown in the below image.

Physical Read vs Logical Read in SQL Server:

Two more important concepts we need to understand in order to improve the performance of the SQL query. They are Physical Read and Logical Read.

When we fire the SQL Statements first time, it actually fires on the actual database i.e. the physical database. SQL Server allocates some kind of memory for SQL cache. The records which have been query, again and again, those records are stored on the SQL Cache. Initially, when we fire a SQL statement, there is a physical read and the data or records stored in the SQL cache and hence when we fire the same SQL statement again, it will try to read the records from the SQL Cache as shown in the below image.

So, whenever the data is read from the actual physical file, then it is called as Physical read. On the other hand, whenever the data is read from the SQL Cache, it is called Logical Read.

How to measure Logical and Physical Read in SQL Server?

In order to measure the Physical and Logical read in SQL Server, we need to use Set Statistics IO ON as shown below.

Let see the Logical and Physical read for SELECT * FROM Employee SQL Statement. So, please execute the below query.

SET STATISTICS IO ON
SELECT * FROM Employee

Once you execute the above two statement, then open the message which shows right to the Results window as shown below.

As you can see, there are two logical reads and 0 physical reads. This SQL Statement may be cached and this is the reason why you are seeing the Physical read as 0. If you want to see the physical read, then, restart your database and definitely you will see the physical read.

In the next article, I am going to discuss how to improve the table scan performance using a unique key in SQL Server. Here, in this article, I try to explain some of the basic concepts to understand the performance improvements in SQL Server with example. I hope you enjoy this Performance Improvements in SQL Server with Examples article.