Alter Partition split going on hang stat when inserting large set of rows in table in sql server 2016

Question

Hi ,

We are inserting 30 M records hourly in partition table(partition on Insert date) using Java ETL(Multiple threads). Simultaneously We are creating future partition(>30 Days) using alter partition split via Jobs in sql server daily basis(1 partition every day). alter partition split is going on hanged/lock state sometime. Is there any other way to achieve future date partition or we can avoid this hang/lock issue. Please suggest.

Answer 1

Splitting the partition function will require a restrictive schema modification lock on the underlying tables/indexes during the process. Although the operation will be fast when no data movement is needed, the operation will be blocked by concurrent activity. All you can do is ensure data movement is not needed to accommodate the new boundary and schedule the partition maintenance around other activity.

Answer 2

Make sure there are empty partitions at both ends of the partition range, which guarantees that the partition split doesn't incur any data movement.