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?

Answer 1

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?