Yet another SQL Server enthusiast

2017-07-17

SQL Server partition elimination – a simple example

Filed under: Uncategorized — sa @ 11:07 AM

On a partitioned table, partition elimination (ref #1 – section Partition Eliminationref #1 – section Partition Elimination, ref #2) is an query optimization

  1. Available to predicates defined on partitioning key columns (DateKey in our example) and
  2. It allows to avoid a full table scan/full [clustered] index scan
  3. By scanning records from one or more partitions.

Example:

sql.server.partition.elimination

In this case

  • DateKey is the partitioning key of dbo.FactOnlineSales_Archive table and
  • WHERE clause includes one predicate defined on this column (DateKey = <constant>).

Execution plan shows how partition elimination works: Seek Predicates (green box) property of Clustered Index Scan includes one predicate (PtnId1000 = …) which is going to do the partition elimination (it limits the Scan operator to a single partition in this case). Also, please note the Predicate property (red box) of the same Scan operator which is going to filter all records from current partition in order to count only records for DateKey = ‘2007-01-04’.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: