SQL DW - Partitioning using split

Question

I have a SQL DW table partitioned by month. Our DBA moved on a few months ago and so our tables haven't been partitioned since. We noticed only due to slow down on the tables.

When we attempt to add a partition by using the split function we get the error below

"SPLIT clause of ALTER PARTITION statement failed because the partition is not empty."

We have tried the following

CREATE TABLE [data].[sessions_range]
WITH
(
    CLUSTERED COLUMNSTORE INDEX,
     DISTRIBUTION = HASH([sesh_id]),
    PARTITION 
    (
        -- SAMPLE RANGE
        [session_start_dt] RANGE RIGHT FOR VALUES
        (
            '2016-12-01'
        )
    )
)
AS
SELECT *
FROM    [data].[sessions]
WHERE   1=2
;

ALTER TABLE [data].[sessions] SWITCH PARTITION <guessed at a partition number> 
TO [data].[sessions_range] PARTITION 2;

ALTER TABLE [data].[sessions] SPLIT RANGE ('2016-12-01');

Its after the last row there it fails. The Switch partition seems to take ok although we did have to go through the partition numbers until we found the corresponding partition within range.

Can anyone help us as to why the partition would still be saying not empty during the split even after we have switched?

Disable the Columnstore index, perform the SPLIT, then rebuild the Columnstore index. Only empty partitions can be split in when a Columnstore exists on the table. Sorry. — Laughing Vergil, Dec 16 '16 at 22:53
See https://redphoenix.me/2014/08/18/how-to-split-non-empty-partitions-when-a-clustered-columnstore-index-exists-on-the-table/ for some good information on how to deal with this. — Laughing Vergil, Dec 16 '16 at 22:54
Msg 7725, Level 16, State 1, Line 31 Alter partition function statement failed. Cannot repartition table 'Table_1b651cb9dc3149d1ba96dfac0533b021_4' by altering partition function 'PFunc_ce19acb8949f4e248be4ce51ec680b3b' because its clustered index 'Idx_3b8705810d524f0797838b47ef870b4f' is disabled. Operation cancelled by user. — Thomas, Dec 16 '16 at 23:01
If you try to disable and the split you get the error above. Also the link you have posted is not for SQL DW. — Thomas, Dec 16 '16 at 23:03

score 2 · Answer 1 · edited May 23 '17 at 11:59

There is no need to guess. Azure SQL Data Warehouse supports the following DMVs:

sys.partitions
sys.partition_functions
sys.partition_parameters
sys.partition_range_values
sys.partition_schemes

This will help you find out how your table maps to values, so you can empty the relevant partition.

Review this article for tips on partitioning in Azure SQL Data Warehouse:

https://learn.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-partition

Also review this recent answer which demos partition switching and shows how the target table has to have the same partition layout as the source in order for this to work:

Using Polybase to load data into an existing table in parallel

Did your DBA really not leave any kind of plan or handover?

SQL DW - Partitioning using split

1 Answers1