THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: ALTERing a Partition Function

Finally, a real geeky post. I received two questions in as many weeks about what really happens to existing partitions when you alter the underlying partition function. The big question being… does altering a partition function cause any data movement? The answer, as usual, is “It depends”

Most people who work with partitioning are aware that the SWITCH operation does not cause data movement. But SWITCH is an option to ALTER TABLE, not ALTER PARTITION FUNCTION. ALTER TABLE SWITCH … allows you to ‘switch’ the contents of a populated partition with an empty one, and if all the prerequisite conditions are not met, the SWITCH cannot proceed and you’ll get an error.

However, altering a partition function  is different. When you alter a partition function with the SPLIT option, you can end up with an empty partition, so many people believe that it’s just a simple operation.  But it’s not always.

The ALTER PARTITION FUNCTION statement is deceptively simple:

ALTER PARTITION FUNCTION partition_function_name()
{ 
    SPLIT RANGE ( boundary_value )
  | MERGE RANGE ( boundary_value ) 
} [ ; ]

You can either specify a new boundary value (with SPLIT) or an existing one (with MERGE). Let’s look at SPLIT.

Suppose I have a partition function with boundary points at 1, 10, 100, 1000.   Suppose I have a table created on a partition scheme that uses this function, and I have populated the table.  If I alter the function with SPLIT RANGE (50), the partition between 10 and 100 will be split into 2. If I already have data between 10 and 50 and other data between 50 and 100, some of the data will have to move.  But which data?

Let me create a simple example in the AdventureWorks database. The following statements will create a copy of the Production.Product table and partition it into 5 partitions, with the boundary points suggested above.

USE AdventureWorks;
GO

CREATE PARTITION FUNCTION price_parts (money)
  AS RANGE LEFT FOR VALUES ( 1.00,10.00,100.00,1000.00) ;
GO

CREATE PARTITION SCHEME price_scheme
AS PARTITION price_parts
   ALL to ([PRIMARY]);
GO


SELECT * INTO NewProduct
FROM Production.Product;
GO  

CREATE CLUSTERED INDEX newproduct_pk
  ON NewProduct(ProductID)
  ON price_scheme (ListPrice);

You can use the $partition reference to see how many rows are in each partition:

  SELECT $partition.price_parts(ListPrice), COUNT(*)
  FROM dbo.NewProduct
  GROUP BY $partition.price_parts(ListPrice);
  GO

Or, you can use the sys.partitions catalog view:

SELECT * FROM sys.partitions
WHERE object_id = object_id('dbo.NewProduct');

image

The output shows me 5 partitions, and the number of rows in each.

Now lets see what happens when I split partition number 3, which contains the rows between 10 and 100.

ALTER PARTITION FUNCTION price_parts()
  SPLIT RANGE (50);
GO

Run the sys.partitions query again, and you’ll see 6 rows:

  image

Partitions 3 and 4 together now have 77 rows, which were in the previous partition 3. But how can we tell which rows moved? You need to look at partition_id. Do not confuse partition_number with partition_id. Partition_number values are always consecutive so where we had partition_number values 1-5 before, we now have 1-6. What was partition 3 is now split into 3 and 4, what was partition 4 is now 5 and what was 5 is now 6.  Partition_id values are actually tied to the physical storage and do NOT change unless the data moves. So by comparing the two outputs above, you can see that the old partition 5, using partition_id 72057594058571776 is now partition 6, but it still uses partition_id 72057594058571776. For partitions 3 and 4, we can see that partition 4 uses the partition_id of the old partition 3, and the new partition 3 has an entirely new partition_id. It was the data between 10 and 50 that moved.

WHY?

The answer is clearly stated in BOL, in the description of ALTER PARTITION FUNCTION:

Based on boundary_value, the Database Engine splits one of the existing ranges into two. Of these two, the one where the new boundary_value resides is considered the new partition.

This says that whatever partition contains the new value (50, in our case) is the one that moves! And how do we know which partition contains the boundary point? That depends on whether your partition function is defined using RANGE LEFT or RANGE RIGHT. In my case, the function was defined using RANGE LEFT, so any rows with a ListPrice equal to the boundary value go in the partition on the LEFT, that is, with values smaller that the boundary value. So the old partition 3 contained these values:

10 < ListPrice <= 100

The new partition 3 contains these values:

10 < ListPrice <= 50

And the new partition 4 contains these values:

50 < ListPrice <= 100

So what about when we add a new boundary point  at the end to create a new empty partition? The maximum ListPrice value is 3578.27, so I’ll add a new boundary point at 5000, after also altering the partition scheme to allow for another filegroup.

ALTER PARTITION SCHEME price_scheme
   NEXT USED [PRIMARY];
GO
ALTER PARTITION FUNCTION price_parts()
  SPLIT RANGE (5000);
GO

 

Here is what the new partitions look like:

image

Note that even though partition 7 is empty, it uses the existing partition_id 72057594058571776. The data in partition 6 was moved to a new physical location with a new partition_id. For 86 rows, it doesn’t take all that long to move the rows, but what if partition 6 had 86 BILLION rows? You may think that adding a new empty partition is a very fast operation, but if your partition function definition requires the existing 86 billion rows to move, it could be a extremely slow operation.

So what happens when you use the MERGE option to ALTER PARTITION FUNCTION? As a former university instructor, it’s really great to be able to say… that’s your homework!

So have fun!

~Kalen

Published Sunday, August 16, 2009 2:31 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Tom Tait said:

What about partitioning based on two fields like geography and date?  Can this be done?

September 23, 2009 2:14 PM
 

Kalen Delaney said:

Tom -- Partitioning can only be defined on a single column.

September 23, 2009 8:13 PM
 

Tom Tait said:

Ouch. That is disappointing.  Maybe they'll add that in the next release.   Ok. Thanks.  

September 24, 2009 7:08 PM
 

David Lean said:

"What about partitioning based on two fields like geography and date?  Can this be done?"

"Tom -- Partitioning can only be defined on a single column."

Kalen's answer is totally accurate but leads to an incorrect conclusion / belief.

1. You CAN.

The Partitioning can be done on a single COMPUTED column, So long as it is persisted. Thus with a small amount of effort you can Partition on multiple columns.

2. You CAN'T

But in your example you mentioned using the geography datatype. There are datatype restrictions on partitioning columns: CLR based types (like Geography), Binary & Varchar(max) are amoung those columns that don't make good indexes & aren't permitted as a Partition column.

3. You CAN

If you were really keen you could use a Geography Function convert it to WKT or some integer value that represented a square or other area. This would then work.

4. You probably don't want to.

Geography has great Spatial Indexes, attempting to partition via a non-spatial approach would require some deep thought on how your use the data, anything less would probably suck. Perhaps Partition on Date & compliment with a (non)alighned Spatial index  

January 11, 2010 6:29 AM
 

David Lean said:

Oh! PS: Kalen

Long time no chat.

Great Article,

And I've enjoyed reading the 2008 Series of "Inside SQL 2008" books by You,  Itzik  & others.

January 11, 2010 6:33 AM
 

Tatyana said:

I wonder how ALTER... SPLIT is logged. I tried to split a partition on a very large table and wounded up having transaction log filling up the whole disk. Now I put the database in a simple recovery mode and am trying to do the same... and I see, in the Windows resource Monitor, the transaction log gets hit big, again. I wonder, why? What's actually getting logged?

October 27, 2011 1:57 PM
 

Kalen Delaney said:

Hi Tatyana

If you SPLIT such that data moves, each row moved is logged. The removal from the old partition is logged as a DELETE, the addition to the new partition is logged as an INSERT.

As per my other blog posts, these operations are not logged any differently in SIMPLE recovery. SIMPLE does not affect the logging of DELETE and INSERT operations.

This is why it is recommended that you plan your SPLITs carefully, and try to only split at the beginning or end of the table so that no data is moved.

HTH

~Kalen

October 27, 2011 2:49 PM
 

Tatyana said:

Thank you, Kalen, you are very correct :) Right now I just added another log file, temporarily, on another drive; but in the future I'm going to follow simple and smart recommendations from your article! Thank you very much!

Tatyana

October 27, 2011 3:18 PM
 

GoodStuff said:

This is very helpful

Thanks.

December 29, 2011 12:18 PM
 

Roberto Reyes said:

If in the example, there was an extra empty partition (not the default partition) on the rightmost of the range, splitting that would have not caused any data movement (because there was no data in that range to begin with). This is the reason why some advice having an extra empty partition on the right of the range. Splitting between boundaries will, of course, cause data movement if there were data on the left and right of the new boundary.

Thanks this article is helpful.

May 31, 2012 10:19 AM
 

Kalen Delaney said:

Hi Roberto

Yes, thanks for the data point. This was not actually supposed to be a complete description of everything you need to know and do when working with partitions; that would be more than a simple blog post. My purpose was to show you how to use the metadata to tell what had happened when you split or merge.

Thanks

Kalen

May 31, 2012 1:46 PM
 

Alex said:

Hi Kalen,

Your article is very helpful.

I have done a lot of maintenance on partitioned tables in the past. Recently, I have encountered the following error for the first time:

Msg 7707, Level 16, State 1, Line 1

The associated partition function '<partition_function_name>' generates more partitions than there are file groups mentioned in the scheme '<partition_scheme_name>'.

I am trying to add partitions to the right of the last partition (it is a date based partition function and the last 8 partitions on the right are still unoccupied - so there should be no data movement involved). I issue an alter partition scheme command setting next used and then try to issue the alter partition function split range command.

Any ideas?

June 23, 2012 7:27 PM
 

Alex said:

I was able to resolve the issue successfully. The problem was that the underlying partition function was being shared by multiple partition schemes. Once I moved the additional partition schemes off of the partition function onto new ones, I was able to run the command successfully.

July 3, 2012 4:36 PM
 

Tiago said:

Although I've tried the split on a non empty partition(several times) with a range higher than the existent one but I never had data movement( the partition_number never changed and a new partition was created).

I cannot reproduce what you've done.

I really want to see a data movement splitting   a non-empty partition when the range is higher than the existing upper bound.

PS: Code used

http://pastebin.com/GnG29kyz

June 17, 2013 12:32 PM
 

Tiago said:

Hi Kalen.

I was able to reproduce the data movement with the RANGE LEFT instead of RANGE RIGHT..

There is any basic explanation for this?

Thanks for your post. It gave me a great help understanding better the partitioning split and merge.

Cheers,

Tiago

June 17, 2013 1:11 PM
 

al said:

Kalen, is there a way to record/monitor the amount of writes happening during a split of non-empty partition? The explanation of how data movement is related to the RANGE LEFT/RIGHT was great, thanks!  

August 23, 2013 9:02 PM
 

Todd said:

Hi Karen,

I want to set up table partitioning that has only two partitions: a "live" partition containing data for the current year; and an "archive" partition that holds all other historical data.  Each year I want to move the previous year's data from the "live" partition to the "archive" partition.  What is the simplest way to do this?  Can I simply modify the partition function with a new date?

Thanks for your time, knowledge and help!

tmcdaniel@follet.com

May 7, 2014 10:44 AM
 

Kalen Delaney said:

Hi Todd

(Note: my name is Kalen, not Karen.)

Although it's unusual to have a sliding window with just two partitions, it's not unheard of. And yes, you can modify the partition function with a new date so that the first partition now includes all the previous live data, but that will cause data movement, which will cause lots of I/O and logging, so just be aware.

I suggest you read the whitepaper on partitioning, which goes through a full example.

http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/PartTableAndIndexStrat.docx

May 7, 2014 2:36 PM
 

Todd said:

Kalen,

I'm so sorry for using the wrong name!

I've read numerous articles and each always uses multiple partitions.  If I have only one "archive" partition/filegroup, is there any way to avoid the I/O and logging when the current year changes and I want to move that data from the "current" partition/filegroup to the archive partition/filegroup?

Thanks for the information.

Todd

May 7, 2014 3:58 PM
 

Kalen Delaney said:

I am understanding that you have an archive partition and an active partition, and periodically you want to move the active into the archive. You are then combining data from two partitions into one, and there is no way to do that without actually moving data.

May 7, 2014 7:13 PM
 

Ashu said:

Awesome

May 8, 2014 6:04 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement