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.

  • What version of SQL Server are you using? Do you have any diagrams or additional information you can provide? What is the end goal of the destination DB, is it OLAP / OLTP / Other? – Shaulinator Jan 17 '17 at 19:36
  • @Shaulinator: I am using SQL Server 2014.. The destination is an archive table, partitioned per month.. DB is an OLAP.. – Rahim Jan 17 '17 at 19:43
  • Edited answer with new information, I believe it is what you need. – Shaulinator Jan 17 '17 at 20:27

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.

| improve this answer | |

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:

  1. 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)
  2. 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.
  3. Restores will contend for IO, but shouldn't block each other
  4. Inserts might contend with each other for table access, you might have to play with your data copy settings to get this optimized
  5. 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.

| improve this answer | |
  • Yes, the 2000 Databases pump into one DB.. all the tables are 1:1.. The destination table is partitioned per month.. I exeperimented with two packages 50 db per package and whichever starts 2nd end up being a victim of a deadlock.. The Sliding technique is what i am indenting to do once i have the data in the temp... The Inserts is my big issue.. How do i go about that.?? – Rahim Jan 17 '17 at 21:16
  • @Rahim Hmm, is it a pure raw row insert, or does it have lookups and transformations? Does the destination table have any indexes at all, even a clustered index? If so, drop all indexes (including a clustered one) and see if that eliminates the deadlocks. When all rows are in, then ALTER to add the cluster, and roll it into the partition. – BradC Jan 17 '17 at 21:53
  • This is an insert.. No data transformation whats so ever.. Matter of fact i have two sprocs that i execute in a script task in SSIS... (the Sprocs are identical) – Rahim Jan 19 '17 at 13:23
  • Consider indexes, then. A deadlock can occur on simple inserts when SQL works to update indexes for each inserted row. This is true even for the clustered index. – BradC Jan 19 '17 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.