Forum Discussion

Firmbyte's avatar
Firmbyte
Copper Contributor
Mar 07, 2022

Huge memory consumption using ForEach-Object -Parallel

I’ve a function that updates the stats in databases using ForEach-Object -Parallel. When I run it, to loop through the stats in a database and update them, it consumes a lot of memory on the server where I run the command from. In some cases 60/70Gb.
Why is it doing that? Is there a way I can have the function release the memory used for each stat it updates?

 

 

Function Update-Stats
{
<#
$Database = ''
$SqlInstance = ''
Update-Stats -SqlInstance $SqlInstance -Database $Database 
#>
    [cmdletbinding()]
    param(
    [Parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()]
    [string]$SqlInstance , 
    [Parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()]
    [string]$Database 
    )

BEGIN {

$FlagOn = "DBCC TRACEON (7471, -1) WITH NO_INFOMSGS"
$FlagOff = "DBCC TRACEOFF (7471, -1) WITH NO_INFOMSGS"

$Satslist = @()

$Srv = Get-SqlInstance -ServerInstance $SqlInstance 
$Srv | Get-SqlDatabase -Name $Database | %{
    $Db = $_    
    "Processing $($Db.Name)"
    $Db.Tables | ?{$_.RowCount -gt 10} | % { 
        $Table = $_
        $Table.Statistics | %{
            $Satslist += [PSCUstomObject]@{
            SqlInstance = $SqlInstance
            Database = $Database
            Schema = $Table.Schema
            Table = $Table.Name
            RowCount = $Table.RowCount
            Stats = $_.Name
            }
        }
    }
}

$Satslist
}
PROCESS {

    if($Srv.VersionMajor -ge 14 ) {
        Invoke-Sqlcmd -ServerInstance $SqlInstance -Database $Database -Query "$FlagOn"
        $Tflag = $true
    }

    $Satslist | Sort-Object {Get-Random} | % -parallel  {
        
        $Obj = $_ 
        $Table = "[$($Obj.Schema)].[$($Obj.Table)]"
        $Stats = "[$($Obj.Stats)]"

        switch($($Obj.RowCount)){
            {$_ -le 1000000} {$Percent = 20 ; break}
            {$_ -In 1000001..10000000} {$Percent = 15 ; break}
            {$_ -In 10000001..100000000} {$Percent = 10 ; break}
            {$_ -ge 100000001} {$Percent = 5 ; break}
        }
   
       "Invoke-Sqlcmd -ServerInstance $($Obj.SqlInstance) -Database $($Obj.Database) -Query `"Update Statistics $Table $Stats with Sample $Percent Percent`""
       Invoke-Sqlcmd -ServerInstance $($Obj.SqlInstance) -Database $($Obj.Database) -Query "Update Statistics $Table $Stats with Sample $Percent Percent"
   
       } -ThrottleLimit 12
   
   if($Tflag) {
       Invoke-Sqlcmd -ServerInstance $SqlInstance -Database $Database -Query "$FlagOff"
   }
   
}
END {

}

}

 

 

 

Resources