How to see what operations a query performs to complete it in SQL Server (Execution Plan Operators)

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How we enable Unified Auditing in Oracle Database - 7 October 2024
• What is PostgreSQL and how do we do a full installation - September 2, 2024
• How do we configure GoldenGate replication to read from Oracle Data Guard Standby - 2 August 2024

To run a query in SQL Server, the Query Optimizer it accepts information such as the query, database schema, statistics and derives the optimal way to access them. The result is called Plano or otherwise Execution Plan.

How SQL Server will finally execute the query is described step by step in the Blueprint through the Operators. In other words, the Operators describe each action that must be done separately in order for the query to return the desired result.

How we see the Execution Plan

The easiest way to see beyond the results of a query and the Execution Plan that ran, is to select before its execution in SQL Server Management Studio the button Include Actual Execution Plan as below:

Some of the most common Execution Plan Operators in detail

Table Scan Operator

When we see him Table Scan Operator means that the Query Optimizer had to read all the entries of the table to return the desired result:

select * from users;

This process is demanding and should be avoided, but if for some reason we need all the records of the table or it is very small there is no problem, in any other case creating an Index would help.

Index Scan Operator

THE Index Scan Operator appears respectively when the Query Optimizer chooses to read the data of the entire Index. In our case we have a Non-clustered Index at displayname and since we only ask for this field, it prefers to bring the information from there (due to fewer pages):

select displayname from users;

Index Seek Operator

When we have an Index either Clustered or Non-clustered the data in it is sorted, thus enabling the Query Optimizer to search and load only the records that meet the criteria we want with the Index Seek Operator:

select * from users where id < 100;

Key Lookup Operator

When we search on a field that has an Index it is sorted, so it is easy for the Query Optimizer to find the values that meet this criterion. But if we request other fields that are not in the Non-clustered Index (as in our case). Then he will make use of it Key Lookup Operator, which through pointers located in the Non-clustered Index will search the rest of the fields that are stored in the Clustered Index of the table:

select * from users where displayname like 'Joe%';

This process is particularly demanding as it is repeated for each record that meets the criteria. To not have Key Lookups we should add with INCLUDE on the Non-clustered Index the fields we want to appear in the Select.

RID Lookup Operator

The RID Lookup Operator is the same as Key Lookup only it appears in the case that our table does not have a Clustered Index but it does Heap i.e. unclassified:

ALTER TABLE [dbo].[Users] DROP CONSTRAINT [PK_Users_Id] WITH ( ONLINE = OFF )
GO

select * from users where displayname like 'Joe%';

Stream Aggregate Operator

When we use Aggregate Functions like SUM,MIN,MAX,AVG… then o Stream Aggregate Operator separates entries into Groups and then holds the result for each one separately. The Stream Aggregate Operator requires that in the Group field the entries are sorted, so if they are not already from an Index, then it does so first Sort:

select avg(reputation) from users where displayname = 'Tom';

Sort Operator

When we want to have the results sorted or remove duplicates then we will see its use Sort Operator:

select * from users where id < 100 order by DisplayName;

Nested Loops Operator

When we use Inner and Left Joins then we will see them Nested Loops Operators, which search the inner table for every record that meets the criteria of the outer table:

select * from Comments
inner join users on comments.id = users.id
where comments.id =2;

Parallelism Operator

When the Query Optimizer judges that it can and should run the query in parallel, then it divides it into Streams and we see its use Parallelism Operator:

select avg(reputation) from users;

Sources:

• Showplan Logical and Physical Operators Reference