sql server execution plan operators

One Operator a Day

1_sql server execution plan operators

   

 

 

 

 

 

Hi SQL folks, my name is Kanchan Bhattacharyya. I work as a Sr. DBA in a leading IT services company. In this “One Operator a Day” series, I intend to write about SQL Server Execution Plan Operators. Hope you enjoy! Happy Learning!


 

SQL Server Eager Spool operator – Part1

Hi Geeks, SQL Server eager spool operator takes the entire input then store each row in a hidden object in temdb database. This operator reads all rows from the previous operator …

Read More »


Sequence Project Operator

Hi Geeks, Sequence project operator adds columns in order to perform computations over an ordered set and divides the input set into segments based on the value of one or more …

Read More »


Online Index Insert Operator

Hi Geeks, SQL Server allows you to create, alter or drop indexes online i.e. while underlying table is available to users. Online index operator implements these operations and is a physical …

Read More »


SQL Server execution plan Filter Operator

Hi Geeks, SQL Server execution plan filter operator scans the input and return only those rows that satisfy filter expression specified in predicate. Let’s see this operator in action. USE [AdventureWorks2012] …

Read More »


Merge Interval Operator – Part2

Hi Geeks, Welcome to Part2 of Merge Interval Operator. If you missed Part1, you can click here and read it before today’s post. Let me modify our query from yesterday as following. USE …

Read More »


Merge Interval Operator – Part1

Hi Geeks, Merge interval operator used to remove duplicated predicates in a query. It also helps to find possible overlapping intervals which in turn helps to optimize filters to avoid scanning …

Read More »


SQL Server Table Spool Operator (Lazy Spool) – Part3

Hi Geeks, Welcome to the Part3 of SQL Server Table Spool Operator (Lazy Spool). If you reading this post before earlier two posts on the subject, you can click on Part1 and …

Read More »


SQL Server Table Spool Operator (Lazy Spool) – Part2

Hi Geeks, Today we will explore the first step of execution plan (highlighted in red) in detail and is a continuation of yesterday’s post on SQL Server Table Spool Operator (Lazy …

Read More »


SQL Server Table Spool Operator (Lazy Spool) – Part1

Hi Friends, To understand SQL Server Table Spool Operator, let us consider following query as an example. USE [AdventureWorks2012] SELECT SD1.SalesOrderID,SD1.SalesOrderDetailID FROM Sales.SalesOrderDetail SD1 WHERE SD1.OrderQty > (SELECT AVG(SD2.OrderQty) FROM Sales.SalesOrderDetail …

Read More »


SQL Server Segment Operator

Hi Geeks, SQL Server segment operator is a physical as well as logical operator. It divides its inputs into different segments based on their values and outputs one segment at a …

Read More »


SQL Server Merge Join Operator – Part2

Hi Geeks, I hope you liked yesterday’s post SQL Server Merge Join operator Part1. This operator requires an equality operator and inputs sorted in join predicate. In our example from yesterday, …

Read More »


SQL Server Merge Join Operator – Part1

Hi Friends, SQL Server Merge Join operator performs the inner join, left outer join, left semi join, left anti semi join, right outer join, right semi join, right anti semi join, …

Read More »


SQL Server Nested Loop Join Operator – Part2

Hi Geeks, If you missed my yesterday’s post SQL Server nested loop join part1, you can read that by clicking here before today’s post. Let us change our query from yesterday and …

Read More »


SQL Server Nested Loop Join Operator – Part1

Hi Geeks, Let’s start with following query that uses SQL Server nested loop operator in the query plan. USE [AdventureWorks2012] SELECT EMP.BusinessEntityID FROM HumanResources.Employee EMP INNER JOIN Sales.SalesPerson AS SLS ON …

Read More »


SQL Server hash match aggregate operator – Part3

Hi Friends, I hope you liked SQL Server hash match aggregate Part1 and Part2. Today we are going to see if input data is not sorted and we explicitly specify the order in …

Read More »


SQL Server hash match aggregate operator – Part2

Yesterday we discussed about SQL Server hash match aggregate opertor and today’s post is a continuation of that. If you missed yesterday’s post, you can click here to read it. Hash aggregate …

Read More »


SQL Server hash match aggregate operator – Part1

Hi Friends, SQL Server hash match aggregate is selected by query optimizer for the tables with large data and when they are not sorted. Cardinality estimates few groups only and there …

Read More »


SQL Server Hash Match join operator

Hi Geeks, Today we are going to discuss on SQL Server hash match join operator but before we do that, we need to understand the concept of hashing and a hash …

Read More »


Assert operator SQL Server - Part3

Dear Friends, I hope you liked my previous posts Assert operator SQL Server Part1  and Part2. Today, we are going to see that this operator is used to check sub query too. …

Read More »


Assert operator SQL Server - Part2

Hi Friends, Today we are going to explore how operator assert works with foreign keys. I recommend you to read yesterday’s post Assert operator SQL Server Part1 first in case you are …

Read More »


« Newer EntriesOlder Entries »
   

Leave a Reply

Your email address will not be published.