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 same data more than once.

Let us start with following query then jump into the subject.

USE [AdventureWorks2012]

SELECT SUM(Product.SafetyStockLevel)
FROM [Production].[Product]
WHERE Product.ProductID IN (1,2,3,4)

QueryPlan_1

IndexSeekToolTip

From the above plan, clustered index seek performed on ProductID to fetch data specified in IN clause then query optimiser used StreamAggregate to compute sum. As of now we do not see Merge Interval in the query plan. Let me modify our query little bit as following.

   
USE [AdventureWorks2012]

DECLARE @ID1 Int = 1,
		@ID2 Int = 2,
		@ID3 Int = 3,
		@ID4 Int = 4 

SELECT SUM(Product.SafetyStockLevel)
FROM [Production].[Product]
WHERE Product.ProductID IN (@ID1,@ID2,@ID3,@ID4)

MergeInterval_1

MergeInterval_2

Note: Query plan image is broken into two parts in order to fit into the window.

I’ve used variables instead of constant values and for that query optimizer created a different plan altogether. The reason behind this is, SQL Server does not know the values of the constants and in case these values turns out to be duplicates it will read same data twice and for the same reason it uses Merge intervals to remove duplicate occurrences.

We are going to explore more on this tomorrow, stay tuned.

Happy learning!

Regards,

Kanchan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook

   

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published.