User Profile
Firmbyte
Copper Contributor
Joined May 24, 2019
User Widgets
Recent Discussions
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 { } }3.7KViews0likes6Comments
Recent Blog Articles
No content to show