Blog Post

Parallel Powershell for Running SQL

,

Parallel Powershell for Running SQL

This is just a quick look. I plan on diving into this in the future more, as I'm still working through some of the changes being made in the main parallel modules I utilize for SQL server. In the meantime, if you are looking for a quick way to leverage some parallel query running, take a look at PSParallel. I've avoided Powershell Jobs/Workflow due to limitations they have and the performance penalty I've seen is associated with them.

For my choice, I've explored PSParallel & PoshRSJob.

I've found them helpful for running some longer running queries, as I can have multiple threads running across server/database of my choice, with no query windows open in SSMS.

Another great option that is under more active development is PoshRsJob. Be clear that this will have a higher learning curve to deal with as it doesn't handle some of the implicit import of external variables that PSParallel does. You'll have to work through more issues initially to understand correctly passing parameters and how the differents scope of runspaces impact updating shared variables (ie, things get deeper with synchronized hashtables and more 🙂 )

Hope this helps get you started if you want to give parallel query execution a shot. Here's a function using PSParallel to get you started. Let me know if it helps

#requires -Version 3.0 -Modules SQLPS
<#
Setup Tips:
Link: [PSParallel](http://bit.ly/2gcXl7H)
Install-Command: Requires Powershell 5.0
Install-Package -Name PSParallel -Scope AllUsers
#>
function Start-ParallelQueries {
[cmdletbinding()]
param(
[Parameter(Mandatory = $true)][string]$Server #server.... nuff said. However, you could also customize this to be cross server if you want!
,[Parameter(Mandatory = $true)][string]$Database #database to run on, else replace in the pscustom object to customize further, or even populate from a csv. Sky is the limit!
)
#because I like timers, makes me feel like a powershell hackah
$startmaster = (Get-Date)
#I'm using RunOnThisDatabase as a param, but I did this so if you wanted to code in different databases to run the individual queries on, you could.
$QueriesToRun = @()
$QueriesToRun += [pscustomobject]@{
Server = $Server;
RunOnThisDatabase = $Database;
InputFile = 'C:\temp\MyAnnoyingLongSQLRunningQuery.sql';
}
$QueriesToRun += [pscustomobject]@{
Server = $Server;
RunOnThisDatabase = $Database;
InputFile = 'C:\temp\MyAnnoyingLongSQLRunningQuery.sql';
}
$QueriesToRun += [pscustomobject]@{
Server = $Server;
RunOnThisDatabase = $Database;
InputFile = 'C:\temp\StarsWillBeAligned.sql';
}
$QueriesToRun | Invoke-Parallel -Throttle 2 -Verbose:$True -ScriptBlock {
$starttime = (Get-Date)
try
{
"Starting: $($_.Server) on $($_.RunOnThisDatabase) - Running $($_.InputFile)"
Invoke-Sqlcmd -Server $_.Server -Database $_.RunOnThisDatabase -QueryTimeout 0 -Verbose:$True -InputFile $_.InputFile
"Finishing: $($_.Server) on $($_.RunOnThisDatabase) - DONE $($_.InputFile) DONE: total time to process: {0}" -f [timespan]::fromseconds(((Get-Date)-$starttime).Totalseconds).ToString('mm\:ss')
}
catch
{"CATCH: $($_.Exception.Message)"} #using this type of format without write-error due to some limitations/issues with runspaces and this module at this time
}
'DONE: total time to process: {0}' -f [timespan]::fromseconds(((Get-Date)-$startmaster).Totalseconds).ToString('mm\:ss')
}
Start-ParallelQueries #-Server ServerName -Database 'MyDb'

Rate

4 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (1)

You rated this post out of 5. Change rating