none
Running sproc in a package RRS feed

  • Question

  • Running a stored procedure from SSMS takes 5 minutes.   Running the same stored procedure from SSIS package (sql task), takes 45 minutes.  It appears, in the later test, only one processor was used (no parallelism). 

    The server is....

    Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: ) (Hypervisor)  

    Thursday, December 14, 2017 4:57 PM

All replies

  • Running a stored procedure from SSMS takes 5 minutes.   Running the same stored procedure from SSIS package (sql task), takes 45 minutes.  It appears, in the later test, only one processor was used (no parallelism). 

    The server is....

    Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: ) (Hypervisor)  

    • Merged by Olaf HelperMVP Thursday, December 14, 2017 5:04 PM Same question from same OP
    Thursday, December 14, 2017 4:54 PM
  • Hi rgelfand,

    What's the stored procedure doing? What does the stored procedure return? Where is the SSIS package executing, with regards to the database server? Same server, over network? And is there a blocking error on the server?

    In SSIS, the default transaction isolation level of SSIS objects is Serializable and you could try to set to Read Committed.

    Besides, you can monitor the package executions and other operation to see if there are exceptions on different phases. See: Monitoring for Package Executions and Other Operations

    Check if this helps.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 15, 2017 5:58 AM
    Moderator