Partitioning - Merging an Empty Partition is not instantaneous - why?

Question

Partitioning - Merging an Empty Partition is not instantaneous - why?

Gabriel P

SSCarpal Tunnel

Points: 4289
More actions
January 18, 2009 at 1:25 pm

#204155

Does anyone know if there's a way to see what is going on when altering a partition function (like an execution plan)? I am doing a merge on an empty partition, and for whatever reason it is not happening instantaneous (it takes 4-5 minutes). I've checked all indexes, and they are all aligned - and the partitioning key is also the clustered index.
I believe it's doing an index scan on the partition next to it before it merges with it - but that makes no sense. If one of the partitions being merged is empty, why is it doing a scan on the other partition?
I when I tested this I didn't have a problem - however I didn't have the same size of data either.
Here is the code:
CREATE PARTITION FUNCTION [pf_30_day](datetime)
AS RANGE RIGHT FOR VALUES
(N'2008-12-19T00:00:00.000'
, N'2008-12-20T00:00:00.000'
, N'2008-12-21T00:00:00.000'
, N'2008-12-22T00:00:00.000'
, N'2008-12-23T00:00:00.000'
, N'2008-12-24T00:00:00.000'
, N'2008-12-25T00:00:00.000'
, N'2008-12-26T00:00:00.000'
, N'2008-12-27T00:00:00.000'
, N'2008-12-28T00:00:00.000'
, N'2008-12-29T00:00:00.000'
, N'2008-12-30T00:00:00.000'
, N'2008-12-31T00:00:00.000'
, N'2009-01-01T00:00:00.000'
, N'2009-01-02T00:00:00.000'
, N'2009-01-03T00:00:00.000'
, N'2009-01-04T00:00:00.000'
, N'2009-01-05T00:00:00.000'
, N'2009-01-06T00:00:00.000'
, N'2009-01-07T00:00:00.000'
, N'2009-01-08T00:00:00.000'
, N'2009-01-09T00:00:00.000'
, N'2009-01-10T00:00:00.000'
, N'2009-01-11T00:00:00.000'
, N'2009-01-12T00:00:00.000'
, N'2009-01-13T00:00:00.000'
, N'2009-01-14T00:00:00.000'
, N'2009-01-15T00:00:00.000'
, N'2009-01-16T00:00:00.000'
, N'2009-01-17T00:00:00.000')
GO
CREATE PARTITION SCHEME [ps_Logging]
AS PARTITION [pf_30_day]
ALL TO ([FG_Log])
GO
CREATE PARTITION SCHEME [ps_Index]
AS PARTITION [pf_30_day]
ALL TO ([FG_Index])
GO
CREATE TABLE dbo.LoggingTable
(
LoggingTableID int NOT NULL
person_id int NOT NULL,
account_id int NOT NULL,
menu varchar(256) NULL,
page_detail varchar(256) NULL,
url varchar(256) NULL,
web_server varchar(50) NULL,
insert_date datetime NOT NULL,
hhld_id varchar(12) NOT NULL,
underlying varchar(12) NOT NULL,
log_type_id int NOT NULL,
ip_address varchar(15) NULL
) ON ps_Logging(insert_date)
GO
ALTER TABLE dbo.LoggingTable ADD CONSTRAINT
DF_1_L__perso__2B0A656D DEFAULT ((0)) FOR person_id
GO
ALTER TABLE dbo.LoggingTable ADD CONSTRAINT
DF_1_L__accou__2BFE89A6 DEFAULT ((0)) FOR account_id
GO
ALTER TABLE dbo.LoggingTable ADD CONSTRAINT
DF__part_inser__24134F1B DEFAULT (getdate()) FOR insert_date
GO
ALTER TABLE dbo.LoggingTable ADD CONSTRAINT
PK_pt_Logging PRIMARY KEY NONCLUSTERED
(
LoggingTableID,
insert_date
) ON ps_Index(insert_date)
GO
CREATE CLUSTERED INDEX IX_pt_insert_date ON dbo.LoggingTable
(
insert_date
) ON ps_Logging(insert_date)
GO
CREATE NONCLUSTERED INDEX IX_pt_AccountID_Insert_Date_PersonID ON dbo.LoggingTable
(
account_id,
person_id,
insert_date
) ON ps_Index(insert_date)
GO
CREATE NONCLUSTERED INDEX IX_pt_AccountID_QuoteLogTypeID ON dbo.LoggingTable
(
account_id,
quote_log_type_id,
insert_date
) ON ps_Index(insert_date)
GO
ALTER PARTITION FUNCTION [pf_30_day]() SPLIT RANGE (N'2009-1-18T00:00:00.000')
GO
--Code which switches out partition to auxilary table
ALTER TABLE dbo.LoggingTable SWITCH PARTITION 1 TO dbo.LoggingTable_Purge
GO
--Problem code, should be instantaneous, instead takes 4 minutes
ALTER PARTITION FUNCTION [pf_30_day]() MERGE RANGE (N'2008-12-19T00:00:00.000')
GO
Other info:
-There are no foreign keys on this table
-Each partition averages about 860MB of data (1.2 GB including nonclustered indexes)
-I have double and triple checked to make sure all indexes are aligned

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Have you tried a trace or Profiler?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Gabriel P SSCarpal Tunnel Points: 4289 More actions · Answer 2

Have one scheduled to run for tonight's purge process.

Gabriel P SSCarpal Tunnel Points: 4289 More actions · Answer 3

I figured it out. Well not really, but I figured out a way around it.

For some reason, both partitions have to be empty for it to be an instant O(1) operation. So I just created an extra empty one.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

Gabe (1/18/2009)
I figured it out. Well not really, but I figured out a way around it.
For some reason, both partitions have to be empty for it to be an instant O(1) operation. So I just created an extra empty one.

Isn't there a remark in BOL or in the WP that you should put a constraint on the partitioned table to be sure the boundaries are empty ?

http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Gabriel P SSCarpal Tunnel Points: 4289 More actions · Answer 5

I did some more research and I figured out how the split/merge works although I'm still working on the "why". However, the return on reading this explaination is minimal - if you're looking for a quick fix, it's just easier to make sure both partitions are empty 🙂

Basically when you merge two partitions, one of them has to eventually dissappear, right? I was under the assumption that when a merge occurs, if one of the partitions were empty, SQL Server would just update the metadata, and erase the pointer to the empty partition. If they both contained data, it would keep the physical partition with the larger dataset, insert the data from the smaller partition, then remove it (when an index on the partitioning key is available). Makes sense logically right?

Not the case.

SQL Server actually has already picked (depending on how you defined your partition function) which partition it's going to keep physically - whether it's empty or not.

Basically it depends on which direction you chose in your partition function (LEFT/RIGHT). If you choose LEFT, the partition to the right of the merged range is the one that gets to stay, and vice versa.

Same goes for SPLIT range function - when you split a partition, one would assume that the smaller dataset would go into the newly created partition. Again, not the case. It is possible that you can end up moving all of the data physically into the new partition.

Code sample below (change "RANGE RIGHT" to "RANGE LEFT" to see how the statistics change):

create partition function pf_TestFunction(int)

AS RANGE RIGHT FOR VALUES (1000)

--AS RANGE LEFT FOR VALUES (1000)

create partition scheme ps_TestFunction

AS PARTITION pf_TestFunction ALL TO ([PRIMARY])

;with t1 as (select 1 n union all select 2 n),

t2 as (select 1 n from t1 a, t1 b),

t3 as (select 1 n from t2 a, t2 b),

t4 as (select 1 n from t3 a, t3 b),

t5 as (select top 100 Row_Number() OVER (order by (Select 1)) n from t4)

select Cast(n as int) n into number_table from t5

create clustered index ix_table on number_table (n) on ps_TestFunction(n)

SET STATISTICS IO ON

ALTER PARTITION FUNCTION pf_TestFunction() MERGE RANGE (1000)

ALTER PARTITION FUNCTION pf_TestFunction() SPLIT RANGE (1000)

SET STATISTICS IO OFF

DROP TABLE number_table

DROP PARTITION SCHEME ps_TestFunction

DROP PARTITION FUNCTION pf_TestFunction

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

Nice testing Gabe.

Thank you for the feedback.

One of my co-workers discovered the same and it is a valuable lesson.

It is certainly worth to know, because everyone experimenting with partitioning and split or merge operations will somehow scratch the head on this one.