Concurrent processing in SSIS – Part 1

  • Elliott Whitlow

    SSC Guru

    Points: 102296

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

  • ziangij

    SSCertifiable

    Points: 7000

    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: 281205

    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

    Thanks guys, I hope it helps..

    CEWII

  • pcannon

    Valued Member

    Points: 63

    Nice article Elliott.

  • richard.maw

    SSC Veteran

    Points: 226

    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

    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

    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

    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

    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

    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

    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

    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/

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

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