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.
This article is for you then.
One quote directly in regards to your question of multi-threading.
The package property that determines how many executables can run in parallel is MaxConcurrentExecutables, as shown in Figure 2. The default is -1, which means that the control flow engine will use the number of logical processors on the server plus 2. For example, if you’re executing the package with the default setting on a quad-core server, you can have up to six executables running in parallel.
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.
To clarify, you have a single SSIS package that:
- Restores a list of 2000 databases
- Extracts data from each of those 2000 databases to a partitioned table in a central (permanent) database, then
- Drops the source database?
Assuming you are working on a multi-core server, I think you'd see a good increase in performance by:
- Copying your package, and make each package responsible for an equal subset of the databases (let's say 10 packages each responsible for 200 dbs)
- Schedule each one via a separate SQL agent job, even if they all kick off at the same time. These jobs/packages will run concurrently, this is a form of "cheap and easy" SQL multitasking.
- Restores will contend for IO, but shouldn't block each other
- Inserts might contend with each other for table access, you might have to play with your data copy settings to get this optimized
- Let SQL handle the rest. Packages that are done can go on to their other tasks (dropping the db, restoring the next).
You might have to play with the number of packages to get it right.
One more idea: If the data from this monthly import makes up a brand new month of this monthly partitioned table, then definitely import all the new data into a brand new (empty) table, then use the "sliding window" technique to roll it into the main table with 0 impact. See Managing Sliding Windows in SQL Server.