Over a million developers have joined DZone.

Understanding of Execution Plan - III - A [ The OPERATORS ]

DZone 's Guide to

Understanding of Execution Plan - III - A [ The OPERATORS ]

· Java Zone ·
Free Resource


In this article we are mostly focusing on the Logical and Physical Operators and trying to understand each as it is very important to understand them before understanding the Execution Plan.
The key to understanding execution plans is to start to learn how to understand what the operators do and how this affects your query.
As this tropics is so large we are decide to introduce is part by part.
Different Type of Operators
Each operator has the different type of characteristics, as they manage the memory in different ways. Some operators – primarily Sort, Hash Match (Aggregate) and Hash Join – require a variable amount of memory in order to execute. Because of this, a query with one of these operators may have to wait for available memory prior to execution, possibly adversely affecting performance.
The lists of the operators are mentioned bellow
1. Select (Result)
9. Sort
17. Spool
2. Clustered Index Scan
10. Key Lookup
18. Eager Spool
3. NonClustered Index Scan
11. Compute Scalar
19. Stream Aggregate
4. Clustered Index Seek
12. Constant Scan
20. Distribute Streams
5. NonClustered Index Seek
13. Table Scan
21. Repartition Streams
6. Hash Match
14. RID Lookup
22. Gather Streams
7. Nested Loops
15. Filter
23. Bitmap
8. Merge Join
16. Lazy Spool
24. Split
Most of the operators behave like two was
1.   Non-Blocking
2.   Blocking
A non-blocking operator creates output data at the same time as it receives the input.
Blocking operator has to get all the data prior to producing its output. A blocking operator might contribute to concurrency problems, hurting performance.
Queering a Single Table
Here we start a very simple execution plan by Queering a Single Table like
SELECT * FROM Table_Name;
Clustered Index Scan
It occurs when seek against any clustered index or others index can not satisfied. To get the more information about it we have to move the Tool tips. When we look at the bottom of the Tool tip we find the Table Object named tbl_EMPLOYEERECORD and the Clustered Index named PK__tbl_EMPL__14CCD97D5D626C84
The Estimated I/O Cost and Estimated CPU Cost are measures assigned by the optimizer, and each operator's cost contributes to the overall cost of the plan.
Indexes in SQL Server are stored in a b-tree. A clustered index not only stores the key structure, like a regular index, but also sorts and stores the data at the lowest level of the index, known as the leaf. This means that a Clustered Index Scan is very similar in concept to a Table Scan. The entire index, or a large percentage of it, is being traversed, row by row, in order to retrieve the data needed by the query.
Why it Occurs
1.   Situation when large number of data must be retrieve by the query. For example a Query without any WHERE clause included.
2.   When the Statistic of the Index is out of date or incorrect
3.   When Query use any Inline Function
Clustered Index Seek
Clustered Index Seek operator occurs when a query uses the index to access only one row, or a few contiguous rows. It's one of the faster ways to retrieve data from the system. We can easily make the previous query more efficient by adding a WHERE clause.
When an index is used in a Seek operation, the key values are used to look up and quickly identify the row or rows of data needed. This is similar to looking up a word in the index of a book to get the correct page number. The benefit of the Clustered Index Seek is that, not only is the Index Seek usually an inexpensive operation when compared to a scan, but no extra steps are required to get the data because it is stored in the index, at the leaf level.
We can see the Tool tip page and see the Ordered property to True.
As in the WHERE clause we use WHERE EMPID=2 but in the Execution plan we can see the WHERE [EMPID] = @1 as it showing the parameter options.
Related Reference

Understanding of Execution Plan – II [Reuse of the Execution Plan]

In our next level we are going to discuss about more operator one by one. Hope you like it and need your valuable comments related to it.



Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}