May 1, 2024 at 4:25 pm
Hi all,
We have a SSIS package that does 2 things in parallel: moves data from one partition to another within the same table.
First, it truncates destination partition, then selects from source partition and inserts to destination one.
But my select process is blocking truncate process, and truncate in turn is blocking insert to destination.
system view sys.dm_tran_locks shows that my table (resource_associated_entity_id) is granted SCH-S lock mode.
What would be a solution in this situation?
Thanks
May 1, 2024 at 4:33 pm
Why are you truncating partitions and loading them why not just use the partition sliding window to move partitions along and drop any you don’t need
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 1, 2024 at 4:41 pm
Run the tasks in series?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 1, 2024 at 4:51 pm
Run the tasks in series?
that too, but more overhead than required considering partitions can be slid out
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 1, 2024 at 6:03 pm
Phil Parkin wrote:Run the tasks in series?
that too, but more overhead than required considering partitions can be slid out
I don't think you have a choice. I believe the truncate needs to take a sch-m lock on the table, not just the partition. Even switching the partition into an empty holding table requires the same lock. Does the SSIS insert have Fast Load selected? If so I think it will try to lock the table for bulk load. SQL can juggle locks from SSIS data flow inserts running in parallel, but I think the truncate has to run serially.
How are you moving data between partitions? Are you changing the partition key in the data flow? What is the use case for this? How big is the table and how many partitions do you have?
May 1, 2024 at 6:21 pm
I believe the truncate needs to take a sch-m lock on the table, not just the partition. Even switching the partition into an empty holding table requires the same lock.
yes but its part of the sliding partition operation
How are you moving data between partitions? Are you changing the partition key in the data flow? What is the use case for this? How big is the table and how many partitions do you have?
OP is manually truncating a partition and then loading it based on their description
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 1, 2024 at 7:52 pm
change process so that you use staging tables (not tempdb!!!) for each task - and you switch in/out of these and it is also where you do your inserts (so it won't block the other operations on main tables)
1 table name per parallel thread you have.
thread 1
thread 2
there will still be some locks - but as long as you don't do the above in a single transaction (if this is a requirement you should consider changing the order slightly so that loading into staging_table is done prior to truncates - see below for an approach)
thread 1
thread 2
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply