Sunday, 5 October 2014

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


In this article we are mostly focus on the Logical and Physical Operators and try 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.

Posted by: MR. JOYDEEP DAS


  1. Thanks...Really awesome. Waiting for the next Level

  2. Thanks @ Niladri. It’s come gradually. Please look at my Blog post. Due to heavy press of my schedule work, I am unable to post frequently. But I promise to complete it.

  3. I feel satisfied to read your blog, you have been delivering a useful & unique information to our vision even you have explained the concept as deep clean without having any uncertainty, keep blogging. SQL server dba Online Training