PowerShell脚本优化：将foreach转为ForEach-Object -Parallel遇阻

一、ForEach-Object -Parallel 无结果返回的排查与修复 #

• 变量作用域问题：并行块无法直接访问外部变量，必须用$using:前缀引用，或者通过-ArgumentList传递。示例写法：

  $counters = @("\Processor(_Total)\% Processor Time", "\Memory\Available MBytes")
  $sqlConnStr = "Server=YourSQLServer;Database=PerfDB;Integrated Security=True"
  
  $results = $counters | ForEach-Object -Parallel {
      $counter = $_
      $connStr = $using:sqlConnStr
      # 收集计数器数据
      $sample = Get-Counter -Counter $counter -MaxSamples 1
      # 返回结果对象
      [PSCustomObject]@{
          Counter = $counter
          Value = $sample.CounterSamples[0].CookedValue
          Time = Get-Date
      }
  }
  

• 输出被抑制：检查是否用了-ErrorAction SilentlyContinue屏蔽错误，导致异常时无输出。可以暂时移除该参数，或用try/catch捕获错误：

  $counters | ForEach-Object -Parallel {
      try {
          $sample = Get-Counter -Counter $_ -MaxSamples 1
          Write-Output $sample.CounterSamples
      } catch {
          Write-Error "收集计数器$_失败：$_"
      }
  }
  

• 并行度过载：如果并行数过高，可能导致Perfmon采样超时。用-ThrottleLimit限制并发数，比如-ThrottleLimit 6（根据CPU核心数调整）。

二、Workflow 中 $cmd.ExecuteNonQuery() 无效的问题 #

• ADO.NET对象序列化限制：Workflow会自动序列化对象，SqlCommand这类无法序列化的对象在并行块中会失效。解决办法是在InlineScript块内创建和使用数据库对象：

  Workflow Collect-PerfData {
      param($counters, $connStr)
      foreach -parallel ($counter in $counters) {
          InlineScript {
              # 所有数据库操作都在InlineScript内完成
              $conn = New-Object System.Data.SqlClient.SqlConnection($using:connStr)
              $conn.Open()
              $cmd = $conn.CreateCommand()
              $cmd.CommandText = "INSERT INTO PerfLogs (CounterName, Value, CollectTime) VALUES (@name, @val, @time)"
              
              $sample = Get-Counter -Counter $using:counter -MaxSamples 1
              $cmd.Parameters.AddWithValue("@name", $using:counter)
              $cmd.Parameters.AddWithValue("@val", $sample.CounterSamples[0].CookedValue)
              $cmd.Parameters.AddWithValue("@time", Get-Date)
              
              $cmd.ExecuteNonQuery()
              $conn.Close()
          }
      }
  }
  

• 连接复用问题：不要在Workflow外部创建数据库连接后传入并行块，必须在每个InlineScript内独立创建、打开、关闭连接，避免跨上下文连接状态异常。

三、通用性能优化建议（针对Perfmon+SQL场景） #

• 批量写入替代单条插入：单条插入SQL的IO开销极大，建议先并行收集所有数据，再用SqlBulkCopy批量写入：

  # 并行收集数据
  $perfData = $counters | ForEach-Object -Parallel {
      $sample = Get-Counter -Counter $_ -MaxSamples 1
      [PSCustomObject]@{
          CounterName = $_
          Value = $sample.CounterSamples[0].CookedValue
          CollectTime = Get-Date
      }
  }
  
  # 批量写入SQL
  $conn = New-Object System.Data.SqlClient.SqlConnection($sqlConnStr)
  $conn.Open()
  $bulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($conn)
  $bulkCopy.DestinationTableName = "PerfLogs"
  # 将PS对象转为DataTable（可自行实现或用第三方模块）
  $dataTable = $perfData | ConvertTo-DataTable
  $bulkCopy.WriteToServer($dataTable)
  $conn.Close()
  

• 优化计数器采样：避免重复采样同一组计数器，用Get-Counter一次性获取多个计数器比循环单取更高效：

  # 一次获取所有计数器，减少系统调用
  $allSamples = Get-Counter -Counter $counters -MaxSamples 1
  $perfData = $allSamples.CounterSamples | ForEach-Object {
      [PSCustomObject]@{
          CounterName = $_.Path
          Value = $_.CookedValue
          CollectTime = Get-Date
      }
  }
  

内容的提问来源于stack exchange，提问作者LoriB