{"id":3352,"date":"2021-07-26T08:00:00","date_gmt":"2021-07-26T05:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=3352"},"modified":"2023-05-05T19:16:58","modified_gmt":"2023-05-05T16:16:58","slug":"ti-simainei-o-kathe-execution-plan-operator-ston-sql-server","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/ti-simainei-o-kathe-execution-plan-operator-ston-sql-server\/","title":{"rendered":"How to see what operations a query performs to complete it in SQL Server (Execution Plan Operators)"},"content":{"rendered":"

To run a query in SQL Server, the Query Optimizer<\/strong> 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<\/strong>.<\/p>\n\n\n\n

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

How we see the Execution Plan<\/h2>\n\n\n\n

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<\/strong> the button Include Actual Execution Plan<\/strong> as below:<\/p>\n\n\n\n

\"\"
01<\/figcaption><\/figure>\n\n\n\n

Some of the most common Execution Plan Operators in detail<\/h2>\n\n\n\n

Table Scan Operator<\/h4>\n\n\n\n

When we see him Table Scan<\/strong> Operator<\/strong> means that the Query Optimizer had to read all the entries of the table to return the desired result:<\/p>\n\n\n\n

select * from users;<\/pre>\n\n\n\n
\"\"
02<\/figcaption><\/figure>\n\n\n\n
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. <\/p>\n\n\n\n
Index Scan Operator<\/h4>\n\n\n\n
THE Index Scan Operator<\/strong> 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 <\/em>and since we only ask for this field, it prefers to bring the information from there (due to fewer pages):<\/p>\n\n\n\n
select displayname from users;<\/pre>\n\n\n\n
\"\"
03<\/figcaption><\/figure>\n\n\n\n
Index Seek Operator<\/h4>\n\n\n\n
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<\/strong> Operator<\/strong>:<\/p>\n\n\n\n
select * from users where id < 100;<\/pre>\n\n\n\n
\"\"
04<\/figcaption><\/figure>\n\n\n\n
Key Lookup Operator<\/h4>\n\n\n\n
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<\/strong> Operator<\/strong>, which through pointers <\/strong>located in the Non-clustered Index will search the rest of the fields that are stored in the Clustered Index of the table<\/strong>:<\/p>\n\n\n\n
select * from users where displayname like 'Joe%';<\/pre>\n\n\n\n
\"\"
05<\/figcaption><\/figure>\n\n\n\n
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 <\/strong>on the Non-clustered Index the fields we want to appear in the Select.<\/p>\n\n\n\n
RID Lookup Operator<\/h4>\n\n\n\n
The RID Lookup<\/strong> Operator <\/strong>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 <\/strong>i.e. unclassified:<\/p>\n\n\n\n
ALTER TABLE [dbo].[Users] DROP CONSTRAINT [PK_Users_Id] WITH ( ONLINE = OFF )\nGO\n\nselect * from users where displayname like 'Joe%';<\/pre>\n\n\n\n
\"\"
06<\/figcaption><\/figure>\n\n\n\n
Stream Aggregate Operator<\/h4>\n\n\n\n
When we use Aggregate Functions<\/strong> like SUM,MIN,MAX,AVG\u2026 then o Stream Aggregate Operator <\/strong>separates entries into Groups <\/strong>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<\/strong>:<\/p>\n\n\n\n
select avg(reputation) from users where displayname = 'Tom';<\/pre>\n\n\n\n
\"\"
07<\/figcaption><\/figure>\n\n\n\n
Sort Operator<\/h4>\n\n\n\n
When we want to have the results sorted or remove duplicates then we will see its use Sort Operator<\/strong>:<\/p>\n\n\n\n
select * from users where id < 100 order by DisplayName;<\/pre>\n\n\n\n
\"\"
08<\/figcaption><\/figure>\n\n\n\n
Nested Loops Operator<\/h4>\n\n\n\n
When we use Inner and Left Joins <\/strong>then we will see them Nested Loops Operators<\/strong>, which search the inner table for every record that meets the criteria of the outer table:<\/p>\n\n\n\n
select * from Comments\ninner join users on comments.id = users.id\nwhere comments.id =2;<\/pre>\n\n\n\n
<\/a><\/p>\n\n\n\n
\"\"
09<\/figcaption><\/figure>\n\n\n\n
Parallelism Operator<\/h4>\n\n\n\n
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<\/strong>:<\/p>\n\n\n\n
select avg(reputation) from users;<\/pre>\n\n\n\n
\"\"
10<\/figcaption><\/figure>\n\n\n\n
Sources:<\/h2>\n\n\n\n