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

Introduction

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

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 [What happened When SQL statement Execute]

http://www.sqlknowledgebank.blogspot.in/2014/06/understanding-of-execution-plan-what.html

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

http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-of-execution-plan-ii.html

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.

Posted by: MR. JOYDEEP DAS