Window Functions in Batch Mode
Published: Jul 22, 2019
We all love Window Functions. Where would we be without ROW_NUMBER and RANK? But these can sometimes be performance killers as they loop through our data performing sorts and scans as they go.

Now, in SQL Server 2016+, there is a Batch Mode version of Window Functions and the improvement is huge.

So let’s have a look with a couple of quick examples:

First we’re going to use a traditional rowstore index…

drop table if exists salesOrderHeader
go

select *
into salesOrderHeader
from AdventureWorks2012.sales.SalesOrderHeaderEnlarged

create clustered index cix_salesOrderHeader
  
on SalesOrderHeader(orderDate)

select *
from
(
  
select row_number() over(partition by salesPersonID order by orderDate) id, *
  
from SalesOrderHeader
) x
where id = 1
go




Secondly we’ll use a Columnstore index in SQL Server 2014…

create clustered columnstore index cix_salesOrderHeader
  
on SalesOrderHeader with (drop_existing = on)

select *
from
(
  
select row_number() over(partition by salesPersonID order by orderDate) id, *
  
from SalesOrderHeader
) x
where id = 1
go




Now, the exact same thing but in SQL Server 2017 with Batch Mode Window Functions:



As you can see, the results are dramatic.

Rowstore and Columnstore are very similar in performance (the Columnstore lost slightly due to higher CPU and Reads)… but the Batch Mode Window Function was a massive improvement. The Duration was reduced by nearly 75%.

Therefore we no longer have a reason to shy away from using our favourite functions anymore. Batch mode, once again, solves many of our problems.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron