Concurrent processing in SSIS – Part 1

Question

Concurrent processing in SSIS – Part 1

Elliott Whitlow

SSC Guru

Points: 102296
More actions
June 16, 2010 at 9:07 pm

#222874

Comments posted to this topic are about the item Concurrent processing in SSIS – Part 1

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

ziangij SSCertifiable Points: 7985 More actions · Answer 1

thanks Elliott 🙂

i was looking for various concurrency options in SSIS package, your example has helped me... looking forward for Part 2.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Nice article Elliott. It was a pleasure to read.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 3

Elliott Whitlow

SSC Guru

Points: 102296

June 17, 2010 at 7:06 am

#1181531

Thanks guys, I hope it helps..

CEWII

pcannon Valued Member Points: 63 More actions · Answer 4

pcannon

Valued Member

Points: 63

June 17, 2010 at 8:19 am

#1181588

Nice article Elliott.

richard.maw SSC Veteran Points: 226 More actions · Answer 5

Interesting stuff but I tried adding "waitfor delay '00:00:01';" to each of the sample work SQL tasks in the OnePackageMethod (before the update statement). That way you can see what is happening because the work takes longer. On my workstation I observed that none of the tasks executed concurrently. They each execute in turn. Maybe on a server it would behave differently?

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 6

richard.maw (6/17/2010)
Interesting stuff but I tried adding "waitfor delay '00:00:01';" to each of the sample work SQL tasks in the OnePackageMethod (before the update statement). That way you can see what is happening because the work takes longer. On my workstation I observed that none of the tasks executed concurrently. They each execute in turn. Maybe on a server it would behave differently?

I've seen wildly varying performance on different machines, and while SSIS will try to run multiple items if the package is built to do that it can't always do it, on a single processor machine (no hyperthreading either) I doubt it will do it. On a server and even a laptop with multiple cores and a couple GB of memory you will see it thread..

Thanks for reading..

CEWII

richard.maw SSC Veteran Points: 226 More actions · Answer 7

I have dual core and 4GB. When I get the chance I'll try using a remote SQL Server that should help.

Mike Dougherty Ten Centuries Points: 1112 More actions · Answer 8

This works very well for centralizing a task comprised of steps on remote servers. A package to manage the remote calls has very little overhead on the machine running the package. In our usage there's a server at corporate that calls many colo-hosted SQL instances.

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 9

richard.maw (6/17/2010)
I have dual core and 4GB. When I get the chance I'll try using a remote SQL Server that should help.

Interesting, I have seen significantly less hardware run 3 threads at a time.

Mike Dougherty (6/17/2010)
This works very well for centralizing a task comprised of steps on remote servers. A package to manage the remote calls has very little overhead on the machine running the package. In our usage there's a server at corporate that calls many colo-hosted SQL instances.

Good to know, thanks..

CEWII

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 10

Nice article. I am looking forward to the second part.

I am curious can you please explain the difference between concurrent processing and parallel processing in this context? If you are running processes concurrently, aren't you basically running them in parallel?

Thanks,

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 11

dbowlin (6/17/2010)
I am curious can you please explain the difference between concurrent processing and parallel processing in this context? If you are running processes concurrently, aren't you basically running them in parallel?

I would generally say yes they are. But additionally you don't necessarily need to have all the processes do they same thing, in these examples you could have each section do something different with the decider being handled in the "Assign Data to Processes" task. So parallel vs. concurrent is more about semantics than anything else..

CEWII

COZYROC One Orange Chip Points: 28499 More actions · Answer 12

Thank you for the article Elliott!

I think you will find this related information interesting. CozyRoc has recently released a new third-party commercial task called Parallel Loop Task. What this task allows is execution of multiple "Foreach Loop Container" iterations in parallel. The setup is very simple:

1. You point to existing loop container that you want executed in parallel.

2. You select how many concurrent threads you want to use for the loop execution.

In tests, a CPU intensive sequential process when executed in parallel on 4-core machine was executed 3 times faster compared to the sequential.

We believe this new task is very powerful tool that can help you use your computer resources in the most efficient way.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/