WorkFlow increasing limit of parallel processes

PowerShell Help
1

I’ve got a WorkFlow that I use for managing indexes in Sql Server databases. It works fine except, except for two issues. One is the Throttlelimit, I want to impose a limit at the server level, so that only 4 databases get processed at a time on each server. But What is happening is that only 4 databases are done on 1 server and 1 database gets done on the 2nd server. That is due to the default of 5 parallel processes for WorkFlows, I want to over ride that but I’m not entirely sure how to. I have found several articles but I’m not totally sure what I need to do.

  • Update: I'm running this on 8 servers. So rather than have 8 sets of 4 sessions, I've only ever got a total of 5 sessions across the 8 servers.
I've read these:

https://social.microsoft.com/Forums/en-US/3ae1efc9-46d7-426f-b26f-c43dab43adb4/change-powershell-parallel-limit?forum=Offtopic

https://stackoverflow.com/questions/12985392/how-do-you-increase-the-number-of-processes-in-parallel-with-powershell-3

 

The other issue I’m having is, due to the lack of MS documentation, I’m not understanding how to get the update stats to be done when it’s a reorg and SortInTempdb in both cases. I’ve added what I thought would be teh command but whilst the reorg & rebuilds are being done, it’s not being done in tempdb and the stats are not done.

 

The function I use is below.

Any help greatfully received.

WorkFlow Indexes
{
[CmdletBinding()]
Param (
[Parameter(Mandatory=$true)]
[string[]]$SqlServers
)

# v1 or v2
$prefix = "cs"
# This limits the concurrent processes
$Throttle = 4

$ErrorActionPreference= "Continue"

Foreach -Parallel ($SqlServer in $SqlServers) {

$Databases = (Invoke-Sqlcmd -ServerInstance $SqlServer -Query "Select name From master.sys.databases Where database_id > 4 AND name LIKE '$prefix%'").name

Foreach -Parallel -throttlelimit $Throttle ($Item in $Databases) {
Function Maintain-Indexes
{
Param (
[string]$SqlServer ,
[string]$Database
)

BEGIN {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
}
PROCESS {
$Srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SqlServer
$Srv.Databases[$Database].Tables | where {$_.IsSystemObject -ne $true -And $_.RowCount -ge 1000} | % {
$Table = $_.name
$RowCount = $_.RowCount
$_.Indexes | % {
$Index = $_
$Index.EnumFragmentation() | % {
$Item = $_
"$Database Index $Index in Table $Table has $RowCount rows on $($Item.pages) pages and fragmentation of: $($item.AverageFragmentation)"
if ($item.AverageFragmentation -ge 10 -And $item.AverageFragmentation -le 30 -And $Item.pages -ge 1000) { "$Index will be Reorganised " ; $Index.SortInTempdb=$True ; $index.Reorganize() ; $Index.UpdateStatistics(1, 10, $True) ;}
elseif ($item.AverageFragmentation -gt 30 -And $Item.pages -ge 1000) { "$Index will be Rebuilt" ; $Index.SortInTempdb=$True ; $index.Rebuild() ;}
}
}
}
}
END {
"Index maintenance on $SqlServer completed"
}
}

Maintain-Indexes -SqlServer $SqlServer -Database $Item
}

}
}