Partition Elimination in Action
Published: Feb 12, 2017
There are many reasons to use partitioning in order to make maintenance easier (stats and reindexing) and allowing for partition switching (fast loading and deleting of data), but there is another which is Partition Elimination.

Partition Elimination isn’t a reason for using partitioning, but it is a pleasant after-effect of the process. In a nutshell it can help with query performance.

So what’s going on?

Well we know that partitioning is effectively splitting a large table into many small tables behind the scenes, therefore what SQL Server can do is look at your queries and decide whether or not you require all partitions... if you need less partitions than there are in your table then SQL Server can eliminate partitions leaving you to use only those required and thus making your query more efficient.

Simple.

Now I know this could lead to arguments over indexing strategies and blah blah, but I’m not interested in that… this is literally just to show what SQL Server does and not to state the right or wrongs ways of doing things because that can very much depend on the environment.

So… with that said, let’s look at an example…

We’ll use the same partitioned table from my last post:

use AdventureWorks2012
go

if object_id('testPartition') is not null drop table testPartition
go

if exists
(
  
select *
  
from sys.partition_schemes
  
where name = 'testPartitionScheme'
)
begin
   drop
partition scheme testPartitionScheme
end

if
exists
(
  
select *
  
from sys.partition_functions
  
where name = 'testPartitionFunction'
)
begin
   drop
partition function testPartitionFunction
end

create
partition function testPartitionFunction(date)
  
as range left
  
for values('2008-01-01', '2008-02-01', '2008-03-01', '2008-04-01')
go

create partition scheme testPartitionScheme
  
as partition testPartitionFunction
  
all to ([primary])
go

create table testPartition
(
  
id int identity,
  
orderDate date,
  
accountNumber varchar(20),
  
purchaseOrderNumber varchar(20),
  
totalDue decimal(19, 8),
  
constraint pk_testPartition primary key clustered(id, orderDate)
)
on testPartitionScheme(orderDate)
go

insert into testPartition
select orderDate, AccountNumber, PurchaseOrderNumber, TotalDue
from sales.SalesOrderHeader
where orderDate between '2007-12-01' and '2008-05-01'
go


Now what we’ll do is also create a non-partitioned version of the table to compare against:

if object_id('testNonPartition') is not null drop table testNonPartition
go

create table testNonPartition
(
  
id int identity,
  
orderDate date,
  
accountNumber varchar(20),
  
purchaseOrderNumber varchar(20),
  
totalDue decimal(19, 8),
  
constraint pk_testNonPartition primary key clustered(id, orderDate)
)
go

insert into testNonPartition
select orderDate, AccountNumber, PurchaseOrderNumber, TotalDue
from sales.SalesOrderHeader
where orderDate between '2007-12-01' and '2008-05-01'
go


Okay, so now we’re all set. Now, again ignoring any arguments over indexing, let’s just run the following query:

select *
from testNonPartition
where orderDate = '2008-02-15'

select *
from testPartition
where orderDate = '2008-02-15'


Both queries required a Clustered Index Scan and both (if you were to check) have the same estimated and actual rows… so what’s causing the Optimizer to list one as 80% of the batch cost and the other 20%?

Well, it’s Partition Elimination… hovering over the bottom Clustered Index Scan shows this (near the bottom)…

Which tells you that SQL Server has realized the table is partitioned and that it knows that the date in question is only held in 1 partition therefore, despite performing a Clustered Index Scan, SQL Server has only had to scan one partition out of 5 and therefore is MUCH more efficient.

Quick look at the reads:

A small sample, but clearly highlights the benefits of Partition Elimination.

When used well and with a good indexing strategy it can dramatically improve your server’s performance.
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