Runspaces Simplified (as much as possible)

Last year, I was looking into multi-threading in PowerShell and, with the help of Dr. Tobias Weltner, Boe Prox and Martin Pugh ultimately decided on runspaces.

Then, I presented at psconf.eu about using runspaces to speed up SQL Server/CSV imports. Runspaces took me from about 90,000 rows per second to 230,000 rows per second on average.

rps

Runspaces can be intimidating. I had heard about them, took a look at the code and was like “Ah, that looks complex. I’ll learn that later.” Because of this, I wanted to ease the audience into runspaces and repeatedly went over the bulk insert code to familiarize the audience with the functionality that I was eventually going to multi-thread.

It seems like that approach worked. The audience wasn’t overwhelmed (or didn’t admit to it ;)) — mission accomplished!

All of the code and the PowerPoint seen in the video can be downloaded in my directory on PSConfEU’s GitHub Repository.

Runspace Template

Ultimately, when I want to add a runspace to a command, I download code.zip from my presentation and copy/paste/edit 6-runspace-concept.ps1. When I add multi-threading to my scripts, I just copy and paste the code below, then modify. Understanding everything that’s going on isn’t immediately necessary.

If you’re using Runspaces as an end-user, try Boe Prox’s PSJobs module instead. But if you don’t want dependencies, you can paste the code below.

# BLOCK 1: Create and open runspace pool, setup runspaces array with min and max threads
$pool = [RunspaceFactory]::CreateRunspacePool(1, [int]$env:NUMBER_OF_PROCESSORS+1)
$pool.ApartmentState = "MTA"
$pool.Open()
$runspaces = $results = @()
    
# BLOCK 2: Create reusable scriptblock. This is the workhorse of the runspace. Think of it as a function.
$scriptblock = {
    Param (
    [string]$connectionString,
    [object]$batch,
    [int]$batchsize
    )
       
    $bulkcopy = New-Object Data.SqlClient.SqlBulkCopy($connectionstring,"TableLock")
    $bulkcopy.DestinationTableName = "mytable"
    $bulkcopy.BatchSize = $batchsize
    $bulkcopy.WriteToServer($batch)
    $bulkcopy.Close()
    $dtbatch.Clear()
    $bulkcopy.Dispose()
    $dtbatch.Dispose()

    # return whatever you want, or don't.
    return $error[0]
}

# BLOCK 3: Create runspace and add to runspace pool
if ($datatable.rows.count -eq 50000) {

    $runspace = [PowerShell]::Create()
    $null = $runspace.AddScript($scriptblock)
    $null = $runspace.AddArgument($connstring)
    $null = $runspace.AddArgument($datatable)
    $null = $runspace.AddArgument($batchsize)
    $runspace.RunspacePool = $pool

# BLOCK 4: Add runspace to runspaces collection and "start" it
    # Asynchronously runs the commands of the PowerShell object pipeline
    $runspaces += [PSCustomObject]@{ Pipe = $runspace; Status = $runspace.BeginInvoke() }
    $datatable.Clear()
}

# BLOCK 5: Wait for runspaces to finish
 while ($runspaces.Status.IsCompleted -notcontains $true) {}

# BLOCK 6: Clean up
foreach ($runspace in $runspaces ) {
    # EndInvoke method retrieves the results of the asynchronous call
    $results += $runspace.Pipe.EndInvoke($runspace.Status)
    $runspace.Pipe.Dispose()
}
    
$pool.Close() 
$pool.Dispose()

# Bonus block 7
# Look at $results to see any errors or whatever was returned from the runspaces

So that’s basically it. Adding multithreading honestly just requires a bunch of copy/pasting. I generally modify Steps 2 and 3.

Step-by-Step

  • BLOCK 1: Create and open a runspace pool. You don’t have to, but it increases performance so I always just leave it in. CreateRunspacePool() accepts min and max runspaces.I’ve found that 1 and number of processors+1 (thanks to Steffan for informing me why 5 was always my quad processor’s sweet spot.) Then I play with MTA and STA and see which one works better.I also create a runspaces array to keep track of the runspaces. Unfortunately, you can’t do like $pool.runspaces to get the collection, so you have to make your own.
  • BLOCK 2: Create reusable scriptblock. This is the workhorse of the runspace. Think of it as a function.
  • BLOCK 3: Create the runspace and add to runspace pool.If you write a lot of PowerShell functions, it should be apparent what it’s doing. Basically AddScript($scriptblock) is the function name, then AddArgument($connstring), .AddArgument($datatable) and AddArgument($batchsize) are the parameters.

    Note that you may find yourself passing a lot of parameters because the runspace will be mostly unaware of the variables that exist outside of the $scriptblock.

  • BLOCK 4: Add runspace to runspaces collection and start it
  • BLOCK 5: Wait for runspaces to finish
  • BLOCK 6: Clean up
  • BLOCK 7: Look at $results to see any errors or any other return

Up next

Now there are a couple Runspace commands in v5 but there’s no New-Runspace so this code still applies to v5. I generally code for v3 so this script won’t be changing much in the near future.

In the next blog post, I’ll detail a slightly different runspace and immediately outputs the results of the runspace to the pipeline.

Edit: Actually, I just finished that blog post and decided to paste the code here. It’s good for repetition and shows another simplified runspace, this time without comments. If you’d like more info on this runspace, check out the post.

$pool = [RunspaceFactory]::CreateRunspacePool(1, [int]$env:NUMBER_OF_PROCESSORS + 1)
$pool.ApartmentState = "MTA"
$pool.Open()
$runspaces = @()

$scriptblock = {
    Param (
        [string]$server,
        [int]$count
    )
    # Pretend I connected to a server here and gathered some info
    Write-Output "Pretended to connect to $server $count times"
}

1..10 | ForEach-Object {
    $runspace = [PowerShell]::Create()
    $null = $runspace.AddScript($scriptblock)
    $null = $runspace.AddArgument("sql2016")
    $null = $runspace.AddArgument(++$i)
    $runspace.RunspacePool = $pool
    $runspaces += [PSCustomObject]@{ Pipe = $runspace; Status = $runspace.BeginInvoke() }
}

# output right to pipeline / console
while ($runspaces.Status -ne $null)
{
    $completed = $runspaces | Where-Object { $_.Status.IsCompleted -eq $true }
    foreach ($runspace in $completed)
    {
        $runspace.Pipe.EndInvoke($runspace.Status)
        $runspace.Status = $null
    }
}

$pool.Close()
$pool.Dispose()

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in PowerShell