SQL Server Partition Switch

Question

I have a partitioned table in SQL server 2017. I want to switch partition number 2 of this table the the staging table which is created using the same partition scheme as the original table.

I'm using the following query :

ALTER TABLE original_table SWITCH PARTITION 2 TO staging_table PARTITION 2

Normally this query finishes in no time (the partition contains ~ 12M records).

the issue is when there is at the same time a query which is inserting data to original_table , knowing that the new inserted data resides in a different partition that I'm trying to switch. In this case , when I run the alter table query , it is locked by the insert query.

How to manage this issue ?

Answer 1

Not sure what you mean by "manage"... one of them has to wait for the other.

Imagine you need to change your tires or get an oil change, but your son took the car to the mall.

That is what is happening here. The metadata change has to wait for the data change to commit. You can control which has higher priority, though, by adding a wait option to the switch:

WITH 
(
  WAIT_AT_LOW_PRIORITY
  (
    MAX_DURATION = n MINUTES, 
    ABORT_AFTER_WAIT = BLOCKERS 
  )
);

At the specified timeout you can choose to kill the blockers (BLOCKERS) or die gracefully (SELF). Number of rows in the partition is largely irrelevant (unless you are waiting for ~12M rows to actually update).

See Kendra's post here (do look through the comments) and this background post on low priority.