I have a monthly single threaded process that restores about 2,000 databases and pumps data into a table then drops the DB. It takes about 18 hours for the insert to complete (Its via an SSIS Package). How can I make this multi threaded; avoiding locking/deadlocks on the destination table. I have thought of partition switching but it won't work because the destination has to end into one partition in the destination table.
Sql-server – How to insert into a Destination Table from multiple tables concurrently..
concurrencypartitioningsql serverssistablespaces
Related Question
- Sql-server – Snapshotting Database Tables
- Sql-server – SQL Server 2014 SSIS Package Ignores Script Task when running as AGENT
- Sql-server – SSIS Data Transfer vs. Linked Server
- SQL Server Partitioning – Benefits of Partition Switch with Identical Staging and Destination Tables
- Sql-server – Deadlock on partitioned table
- Sql-server – simple way in VS SSIS to swap existing DB connection manager with existing project connection manager
Best Answer
This article is for you then.
One quote directly in regards to your question of multi-threading.
This article will talk about avoiding blocking and locking and should give you enough examples and ideas to give you your own questions to research on. Evaluating what types of locks and blocks will occur and what level of isolation you need will help you avoid blocking and locking.
Deadlocking does not sound like a problem you will encounter based on what you have typed. Here's a good explanation on how it occurs.