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