Partition split

will 1

The problem involves trying to split a partition table which did not as an empty partition at the end. The table also has a columnstore index. Though it was dropped prior to trying to split the partition and the last partition was switch out.

When trying to split a heap table, it will fail with the following error. Is it because there is another partition table2 using the same filegroup? table2 has it own partition function and scheme. What is preventing the split?

SPLIT clause of ALTER PARTITION statement failed because the partition is not empty.  Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.

9 answers

will 1

anonymous user
Here are the results. As you can see partition 28 is empty. But it is still not permit adding a new partition.

partition_number	row_count  
1	0  
2	764365  
3	1947913  
4	9557952  
5	27624757  
6	50458571  
7	44443201  
8	61740713  
9	87396953  
10	58255125  
11	80414892  
12	44353571  
13	41169213  
14	37814501  
15	47813687  
16	63350026  
17	48993126  
18	50130871  
19	63185287  
20	60620138  
21	53509630  
22	57301035  
23	58564594  
24	60276995  
25	54829324  
26	50389878  
27	59874418  
28	0  

boundary_id	rows_per_boundary  
1	6  
2	6  
3	6  
4	6  
5	6  
6	6  
7	6  
8	6  
9	6  
10	6  
11	6  
12	6  
13	6  
14	6  
15	6  
16	6  
17	6  
18	6  
19	6  
20	6  
21	6  
22	6  
23	6  
24	6  
25	6  
26	6  
27	6  
28	1  
29	1  
30	1  
31	1  
32	1  
33	1  
34	1  
35	1  
36	1  
37	1  
38	1  
39	1  
40	1  
41	1  
42	1  
43	1  
44	1  
45	1  
46	1  
47	1  
48	1  
49	1  
50	1  
51	1  
52	1  
53	1  
54	1  
55	1  
56	1  
57	1  
58	1  
59	1  
60	1  
61	1  
62	1  
63	1

Ben Miller (DBAduck) 956 Reputation points

2020-12-15T05:10:12.643+00:00

And what is the boundary of 27 and 28?

Ben Miller (DBAduck) 956

Here is a repro, so that you can see what I am illustrating. When you drop the Columnstore index, the partition will succeed no matter where you are splitting.

If you keep the Columnstore index on, you can split as long as you are splitting on an empty boundary.

-- Create the Partition function
CREATE PARTITION FUNCTION myRangePF1 (INT) AS RANGE LEFT FOR VALUES (1, 100, 1000)
-- Create the partition scheme
CREATE PARTITION SCHEME myscheme AS PARTITION myRangePF1 ALL TO ([PRIMARY])

-- Create the table on the scheme
CREATE TABLE partitionedtable (col1 INT, col2 CHAR(10)) ON myscheme (col1)

-- Create a columnstore index on the table
CREATE CLUSTERED COLUMNSTORE INDEX cl_1 ON partitionedtable 

-- insert 1 row which goes into the 2nd partition
INSERT INTO dbo.partitionedtable (col1, col2)
VALUES (2, 'Ben' )

-- Split the first range which should split because it is empty
ALTER PARTITION FUNCTION myrangepf1() SPLIT RANGE (500)

-- insert another row
INSERT INTO dbo.partitionedtable ( col1, col2 )
VALUES (302, 'Curt')

-- set the next filegroup to use
ALTER PARTITION SCHEME myscheme NEXT USED [PRIMARY]
-- attempt to split and get the error because it is not empty at the end
ALTER PARTITION FUNCTION myrangepf1() SPLIT RANGE (300)

-- You get the error on SPLIT

-- DROP the index
DROP INDEX cl_1 ON dbo.partitionedtable

-- This should work now that the index is dropped and it is now a heap
ALTER PARTITION FUNCTION myrangepf1() SPLIT RANGE (300)

will 1 Reputation point

2020-12-15T17:01:45+00:00
Boundary for both partition 27 and 28.

N'2017-11-01T00:00:00.000', N'2017-12-01T00:00:00.000'

I had no such issues with non-columnstore partitioned tables in the past. Can this issue due to another partitioned table using the same FG but different partition scheme and function?
Please sign in to rate this answer.
David Browne 111 Reputation points Microsoft Employee

2020-12-15T17:12:01.027+00:00

Can you share the partition function definition and the ALTER PARTITION FUNCTION statement?

Ben Miller (DBAduck) 956 Reputation points

2020-12-16T02:23:46.257+00:00

No Filegroups are not like that, they are storage compartments, so you can share Filegroups (think of all the sharing that goes on with PRIMARY).

This is most likely due to data that is in the boundary that doesn't appear to be. Columnstore changes the way things work due to how it stores the data. Very different than row store tables. So there has to be a row there somewhere. If you select * from table where datecolumn > '2017-12-01' and see if you get rows.

But if there are no longer columnstore indexes on the table then it should not act like that. If you do SELECT * FROM sys.indexes where object_id = OBJECT_ID(N'Tablename') are there any indexes at all? Trying to get to the root of this in forum is sometimes hard. You are hanging in there well.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

will 1

anonymous usere-9516

CREATE PARTITION FUNCTION [PFCIHistory](datetime) AS RANGE RIGHT FOR VALUES (N'2015-10-01T00:00:00.000', N'2015-11-01T00:00:00.000', N'2015-12-01T00:00:00.000', N'2016-01-01T00:00:00.000', N'2016-02-01T00:00:00.000', N'2016-03-01T00:00:00.000', N'2016-04-01T00:00:00.000', N'2016-05-01T00:00:00.000', N'2016-06-01T00:00:00.000', N'2016-07-01T00:00:00.000', N'2016-08-01T00:00:00.000', N'2016-09-01T00:00:00.000', N'2016-10-01T00:00:00.000', N'2016-11-01T00:00:00.000', N'2016-12-01T00:00:00.000', N'2017-01-01T00:00:00.000', N'2017-02-01T00:00:00.000', N'2017-03-01T00:00:00.000', N'2017-04-01T00:00:00.000', N'2017-05-01T00:00:00.000', N'2017-06-01T00:00:00.000', N'2017-07-01T00:00:00.000', N'2017-08-01T00:00:00.000', N'2017-09-01T00:00:00.000', N'2017-10-01T00:00:00.000', N'2017-11-01T00:00:00.000', N'2017-12-01T00:00:00.000')  
GO  
  

ALTER PARTITION SCHEME [PSCIHistory]  
NEXT USED [FGCI] -- New File Group for the Partition  
GO  
ALTER PARTITION FUNCTION [PFCIHistory]()   
SPLIT RANGE (N'2018-01-01T00:00:00.000') -- New Range  
GO

anonymous user. Ran the query to get row count per partition.
Row count for partition 27 matches results for "where [CONTACT_DATE] > '2017-11-01' "
But no records after 2017-12-01.

--  
SELECT p.partition_number, fg.name, p.rows  
FROM sys.partitions p  
    INNER JOIN sys.allocation_units au  
    ON au.container_id = p.hobt_id  
    INNER JOIN sys.filegroups fg  
    ON fg.data_space_id = au.data_space_id  
WHERE p.object_id = OBJECT_ID('[dbo].[CI_HISTORY]')  
  
  
24	FGCI	60276995  
25	FGCI	54829324  
26	FGCI	50389878  
27	FGCI	59874418  
28	FGCI	0  
  
  
select count(*)  
from [dbo].[CI_HISTORY]  
where [CONTACT_DATE] > '2017-11-01'  
  
59874418  
  
select count(*)  
from [dbo].[CI__HISTORY]  
where [CONTACT_DATE] > '2017-12-01'  
  
0

Ben Miller (DBAduck) 956 Reputation points

2020-12-16T23:32:49.777+00:00

Hit me up on my site contact and let's get this thing resolved and we can come back here and post what the solution was. https://dbaduck.com/contact

Share via

Partition split

9 answers

Your answer