WorkFlow increasing limit of parallel processes

Welcome Forums General PowerShell Q&A WorkFlow increasing limit of parallel processes

This topic contains 0 replies, has 1 voice, and was last updated by  Iain 7 months, 1 week ago.

  • Author
    Posts
  • #133896

    Participant
    Topics: 36
    Replies: 149
    Points: 437
    Helping Hand
    Rank: Contributor

    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

    https://docs.microsoft.com/en-us/powershell/module/psworkflow/new-psworkflowexecutionoption?view=powershell-5.1

     

    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
    }
    
    }
    }
    
    

The topic ‘WorkFlow increasing limit of parallel processes’ is closed to new replies.