Sample Header Ad - 728x90

Partitioned tables and locking issues

0 votes
0 answers
49 views
I have now 3 tables which are partitioned on *datetime* (a timestamp for when the app writes data) with the same partitioning function (so partition switch works). Table A is the table which is written to by the application. Table B contains old data with partitions from 2024 and older on yearly partitions, until December 5, 2024, when we renamed the old table (old table A became table B) and created a new, empty one (which is now Table A). Table C is a table I created, which I switched out the yearly data from December 5, 2024, until 2024-12-31 to have an empty partition in the production table (table A). The situation now is that we have a partition 19 which contains data in 2 tables (B and C, because I switched partition 19 from Table A to Table C and Table B already contained most of the 2024-data. The partitioning function has yearly partitions until end of 2024. For 2025 we have monthly partitions (and a few months forward in 2025). I was under the impression that I could Split partition 19 on the exact date of December 5, 2024 to create 2 partitions for 2024, and that it wouldn't create any locking issues for the app since the app is writing to partition 20 in table A. The split will involve partition 19, which only has data in Table B and Table C. All data is located on the primary filegroup. When I run alter partition function AUDITSPartitionFunction_V1() split range ('2024-12-05 12:10:28.529999'); it definitely blocks inserts with LCK_M_IX waits. Oldest record in table A is now about **2025-01-01 00:00:01.4800000* and the app is writing to partition 20. The partitioning function is defined as:
CREATE PARTITION FUNCTION [AUDITSPartitionFunction_V1](datetime2(7)) 
AS RANGE RIGHT FOR VALUES (
N'2007-01-01T00:00:00.000', N'2008-01-01T00:00:00.000', N'2009-01-01T00:00:00.000', 
N'2010-01-01T00:00:00.000', N'2011-01-01T00:00:00.000', N'2012-01-01T00:00:00.000', 
N'2013-01-01T00:00:00.000', N'2014-01-01T00:00:00.000', N'2015-01-01T00:00:00.000', 
N'2016-01-01T00:00:00.000', N'2017-01-01T00:00:00.000', N'2018-01-01T00:00:00.000', 
N'2019-01-01T00:00:00.000', N'2020-01-01T00:00:00.000', N'2021-01-01T00:00:00.000', 
N'2022-01-01T00:00:00.000', N'2023-01-01T00:00:00.000', N'2024-01-01T00:00:00.000', 
N'2025-01-01T00:00:00.000', N'2025-02-01T00:00:00.000', N'2025-03-01T00:00:00.000', 
N'2025-04-01T00:00:00.000', N'2025-05-01T00:00:00.000', N'2025-06-01T00:00:00.000', 
N'2025-07-01T00:00:00.000', N'2025-08-01T00:00:00.000', N'2025-09-01T00:00:00.000');
The final goal is to split 2024 into monthly chunks, switching them into Table A (and eventually make an automatic switch-procedure which have 12-13 months of data available in Table A, and keeping old data in Table B. Is there a way to split this data without blocking writing to Table A? I tried to search for similar cases, but couldn't find anything useful.
Asked by Reinert Hansen (1 rep)
Jan 14, 2025, 08:52 AM
Last activity: Jan 14, 2025, 01:32 PM