Post reply

SSIS Execute Task PowerShell performance after migration from 2016 to 2019

  • May 16, 2023 at 8:18 pm

    Go to Answer

    #4190979

    We recently migrated our SQL platform from a server running SQL 2016 to a new one running SQL 2019. In one of our packages, we have an Execute Process Task consisting of a call to PowerShell and a ps1 script file.

    On the 2016 server, this ran within a second however on the 2019 server it takes about 8-10 seconds. As this is called numerous times in a loop, this is having a significant impact. For example one package has jumped from 8 minutes to about 90 minutes.

    If I run the same PowerShell command on the 2019 server from a Command Prompt, it still runs within a second. So the performance issue seems to be limited to the Execute Process Task.

    Can anyone suggest where the performance lag has come from and how we can resolve it? Thanks.

  • May 16, 2023 at 8:50 pm

    #4190990

    as a start I would look at AV on that server - it may be scanning things that were excluded on prior server.

    different powershell versions may also cause issues - as well as modules being loaded as part of the default and per user powershell profile.

    and the other obvious things to look at is server spec - number of cpus, speed of same (as well if the high performance setting is on all the time !!!), ram. - both total ram and ram allocated to processes outside the SQL Server instance.

  • May 17, 2023 at 6:59 pm

    #4191794

    Have you changed the target server of the SSIS package to SQL Server 2019, done a Rebuild and deployment? If the target server of the package is still set for SQL Server 2016, this may well impact execution times.

  • May 23, 2023 at 3:42 pm

    Answer

    #4195691

    So I've been doing some more investigations into the issue, running ProcMon against the PowerShell process while the task was running and found that there was a 5 second pause in the process - no idea why:

    image

    I did interactive tracing and the AWS modules are still referenced but the delay doesn't occur :-/

    Looking on the relevant AWS page it states "AWS.Tools is the new modular variant of AWS Tools for PowerShell" so tried the following:

    • Uninstall-Module AWSPowerShell
    • Install-Module AWS.Tools.Common
    • Install-Module AWS.Tools.S3

    because these are the only modules that we're using as a business - although not in this script, but hey!

    And this solved the problem! So there's either an issue in the AWSPowerShell module or it's so bloated that it's causing an unexpected outcome. By installing the cut-down modules, either the bloat has been removed or whichever part of AWSPowerShell that was causing the problem is no longer installed.

    Thanks all for your assistance.

Viewing 4 posts - 1 through 3 (of 3 total)

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