Parallel T-SQL execution in PowerShell

Question

Can anyone help with this problem? I am referring to a example from the internet for executing T-SQL statements in parallel.

https://www.mssqltips.com/sqlservertip/3539/complete-common-sql-server-database-administration-tasks-in-parallel-with-powershell-v3-workflow/

I want to be able to execute the same T-SQL on the same instance at once for proof of concept work on locking. In order to do this, I have tweaked the script so that I can execute any number of iterations by changing the

while ($counter -le 5)

Here is the full script. Basically the primary statement can be whatever T-SQL you want and this will populate $sqlcmds to have that statement passed through as many iterations as you wish.

Import-Module sqlps -DisableNameChecking;
Set-Location c:
# create a workflow to run multiple sql in parallel
workflow Run-PSQL #PSQL means Parallel SQL {
    Param(
        [Parameter(Mandatory=$true)]
        [string]$ServerInstance,

        [Parameter(Mandatory=$false)]
        [string]$Database,

        [Parameter(Mandatory=$true)]
        [string[]]$Query # a string array to hold t-sqls
    )

    foreach -parallel ($q in $query)  {
        Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $q -QueryTimeout 60000;
    }
} # Run-PSQL

# prepare a bunch of sql commands in a string arrary

#####new bit to make it dynamic sql multiple times
[string[]]$sqlcmds
$sqlcmds = ""
$counter = 0
do {
    "Starting Loop $Counter"

    $PrimaryStatement = '"SELECT TOP 1 * FROM sys.objects"'
    if ($counter -eq 5) {
        $sqlcmds = $sqlcmds + "$PrimaryStatement"
        Write-Host "this is what sqlcmds is $sqlcmds loop 5"
    } else {
        $sqlcmds = $sqlcmds + "$PrimaryStatement,``"
        Write-Host "this is what sqlcmds is now $sqlcmds"
    }

    $counter++
} while ($counter -le 5)

# now we can run the workflow and measure its execution duration
$dt_start = Get-Date; #start time
Run-PSQL -Server &&&&&&& -Database master -Query $sqlcmds;
$dt_end = Get-Date; #end time
$dt_end - $dt_start; # find execution duration

When this is executed, I get this message:

Run-PSQL : Cannot bind argument to parameter 'Query' because it is an empty string.

I have no experience with workflows, but the obvious question is: Does $sqlcmds have a value? Does elastic syntax apply to workflows too? In this case, will -Server bind to $ServerInstance, despite being only a prefix? — mklement0
– mklement0, Commented Aug 26, 2018 at 3:46
"proof of concept work on locking" -this is somewhat simple if you open up SSMS and start a transaction.... — Mitch Wheat
– Mitch Wheat, Commented Aug 26, 2018 at 4:40
I managed to get this to work. Thanks for those that responded. I avoided the workflow-parallel in powershell and did a job. Essentially the reason i couldnt use SSMS was because i needed to create 100+ sessions firing sql statements — ADTJOB
– ADTJOB, Commented Aug 26, 2018 at 12:17

Jana Sattainathan · Accepted Answer · 2019-12-03 13:42:52Z

There are a few minor corrections I had to make and below is the final code that seems to work as expected

Moved the first { since it got commented!
Removed $sqlcmds = ""
Changed how SQL is aggregated in the array $sqlcmds
Removed the if/else inside as it did not seem to serve a purpose
Changed the text printed


    Import-Module sqlps -DisableNameChecking;
    Set-Location c:
    # create a workflow to run multiple sql in parallel
    workflow Run-PSQL #PSQL means Parallel SQL 
    {
        Param(
            [Parameter(Mandatory=$true)]
            [string]$ServerInstance,

            [Parameter(Mandatory=$false)]
            [string]$Database,

            [Parameter(Mandatory=$true)]
            [string[]]$Query #a string array to hold t-sqls
        )

        foreach -parallel ($q in $query)  {
            Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $q -QueryTimeout 60000;
        }
    } # Run-PSQL

    # prepare a bunch of sql commands in a string arrary

    ##### new bit to make it dynamic sql multiple times
    [string[]]$sqlcmds = @()

    $counter = 0
    do {
        "Starting Loop $Counter"

        $PrimaryStatement = 'SELECT TOP 1 * FROM sys.objects'

        $sqlcmds += "$PrimaryStatement"
        Write-Host ("this is what sqlcmds has [$($sqlcmds.Count)] statements at loop counter [$Counter]")

        $counter++
    } while ($counter -le 5)

    # now we can run the workflow and measure its execution duration
    $dt_start = Get-Date; #start time
    Run-PSQL -Server 'myserver\myinstance' -Database master -Query $sqlcmds;
    $dt_end = Get-Date; #end time
    $dt_end - $dt_start; # find execution duration

Collectives™ on Stack Overflow

Parallel T-SQL execution in PowerShell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related