Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

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

DZone's Guide to

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

· Java Zone
Free Resource

The single app analytics solutions to take your web and mobile apps to the next level.  Try today!  Brought to you in partnership with CA Technologies

Introduction

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
Non-Blocking
A non-blocking operator creates output data at the same time as it receives the input.
Blocking
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]

Summary
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.

 

CA App Experience Analytics, a whole new level of visibility. Learn more. Brought to you in partnership with CA Technologies.

Topics:

Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

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

{{ parent.tldr }}

{{ parent.urlSource.name }}