优化从SQL获取服务器列表并执行Ping测试的PowerShell脚本

Hey there, let's dig into why your script's running slow and fix it up with practical optimizations. I've spotted several key bottlenecks (plus a critical bug!) that are dragging down performance:

1. Replace Sequential Pings with Parallel Checks #

Your current script pings servers one at a time, which wastes tons of waiting time. Switching to parallel checks will cut your runtime drastically. For PowerShell 7+, use ForEach-Object -Parallel (simple and efficient); for Windows PowerShell, you could use runspaces, but parallel is cleaner.

# PowerShell 7+ parallel ping example
$pingStatuses = $serverList | ForEach-Object -Parallel {
    $computerName = $_
    Write-Verbose "Checking connectivity to $computerName"
    $isPingable = 0
    
    try {
        # Use -Quiet for fast boolean results, reduce ping count to 1 (adjust if needed)
        if (Test-Connection -ComputerName $computerName -Count 1 -Quiet -ErrorAction Stop) {
            $isPingable = 1
        }
    }
    catch {
        Write-Verbose "Ping failed for $computerName : $_"
    }

    [PSCustomObject]@{
        MachineName = $computerName
        Is_Pingable = $isPingable
    }
} -ThrottleLimit 50 # Adjust based on your network capacity (don't go too high!)

Note: Your original script had redundant Test-Connection calls in the catch block—there's no need to re-run the ping if it already failed.

2. Fix the Critical Server List Bug #

Your line $Server = $ping.Item(0) doesn't get your list of machine names—it just grabs the first column object from your DataTable. This means you're probably only pinging one server (or looping through column properties by accident!). Fix it by grabbing the actual machinename values:

# Correct way to get server names from your DataTable
$serverList = $ping.Rows | ForEach-Object { $_.machinename }
# Or using SqlServer module (simpler):
$serverList = Invoke-SqlCmd -ServerInstance $MasterServerConnString -Database DB -Query "SELECT DISTINCT machinename FROM [DB].[dbo].[TABLE]" | Select-Object -ExpandProperty machinename

3. Batch SQL Updates (Stop Reopening Connections!) #

Your original script opens/closes a SQL connection for every single update—this creates massive overhead. Instead, use a single connection and batch your updates, or better yet, use a Table-Valued Parameter (TVP) for the most efficient and secure approach.

Option 1: Simple Batch Update #

# Build a single batch of update statements
$updateBatch = @()
foreach ($status in $pingStatuses) {
    # Escape single quotes to avoid SQL injection
    $safeName = $status.MachineName.Replace("'", "''")
    $updateBatch += "UPDATE [DB].[dbo].[TABLE] SET IS_PINGABLE = $($status.Is_Pingable) WHERE MachineName = N'$safeName';"
}

# Execute all updates in one connection
$connectionString = "Data Source=$MasterServerConnString;Integrated Security=SSPI;Initial Catalog=DB"
using ($conn = New-Object System.Data.SqlClient.SqlConnection($connectionString)) {
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = $updateBatch -join "`n"
    $rowsAffected = $cmd.ExecuteNonQuery() # Use ExecuteNonQuery for updates, not ExecuteReader
    Write-Verbose "Updated $rowsAffected rows"
}

Option 2: Table-Valued Parameter (Most Efficient & Secure) #

First, create a custom table type in your SQL database:

CREATE TYPE dbo.PingStatusType AS TABLE (
    MachineName NVARCHAR(128) PRIMARY KEY,
    Is_Pingable BIT
)

Then use it in PowerShell:

# Convert ping results to a DataTable matching the SQL type
$tvpTable = New-Object System.Data.DataTable
$tvpTable.Columns.Add("MachineName", [string]) | Out-Null
$tvpTable.Columns.Add("Is_Pingable", [bool]) | Out-Null

foreach ($status in $pingStatuses) {
    $row = $tvpTable.NewRow()
    $row["MachineName"] = $status.MachineName
    $row["Is_Pingable"] = [bool]$status.Is_Pingable
    $tvpTable.Rows.Add($row)
}

# Execute the batch update
$connectionString = "Data Source=$MasterServerConnString;Integrated Security=SSPI;Initial Catalog=DB"
using ($conn = New-Object System.Data.SqlClient.SqlConnection($connectionString)) {
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = @"
UPDATE t
SET t.IS_PINGABLE = tvp.Is_Pingable
FROM [DB].[dbo].[TABLE] t
INNER JOIN @PingStatus tvp ON t.MachineName = tvp.MachineName
"@
    $tvpParam = $cmd.Parameters.Add("@PingStatus", [System.Data.SqlDbType]::Structured)
    $tvpParam.Value = $tvpTable
    $tvpParam.TypeName = "dbo.PingStatusType"

    $rowsAffected = $cmd.ExecuteNonQuery()
    Write-Verbose "Updated $rowsAffected rows"
}

4. Simplify SQL Data Retrieval #

Your original DataTable loading code can be simplified with SqlDataAdapter (no need to manually open/close connections):

$ping_cmd = "SET NOCOUNT ON; SELECT DISTINCT machinename FROM [DB].[dbo].[TABLE] ORDER BY machinename"
$ping_cn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$MasterServerConnString;Integrated Security=SSPI;Initial Catalog=DB")
$ping_da = New-Object System.Data.SqlClient.SqlDataAdapter($ping_cmd, $ping_cn)
$ping = New-Object System.Data.DataTable
$ping_da.Fill($ping)
$ping_cn.Dispose() # Clean up resources properly

Here's the complete script with all fixes and optimizations:

# Configuration
$MasterServerConnString = "dbserver,1433"
$DatabaseName = "DB"
$TableName = "[DB].[dbo].[TABLE]"
$ThrottleLimit = 50

#region Get Server List
Write-Verbose "Retrieving server list from SQL..."
$serverList = Invoke-SqlCmd -ServerInstance $MasterServerConnString -Database $DatabaseName -Query "SET NOCOUNT ON; SELECT DISTINCT machinename FROM $TableName ORDER BY machinename" | Select-Object -ExpandProperty machinename
#endregion

#region Parallel Ping Checks
Write-Verbose "Starting parallel ping tests..."
$pingStatuses = $serverList | ForEach-Object -Parallel {
    $computerName = $_
    $isPingable = 0
    try {
        if (Test-Connection -ComputerName $computerName -Count 1 -Quiet -ErrorAction Stop) {
            $isPingable = 1
        }
    }
    catch {
        Write-Verbose "Ping failed for $computerName : $_"
    }
    [PSCustomObject]@{
        MachineName = $computerName
        Is_Pingable = $isPingable
    }
} -ThrottleLimit $ThrottleLimit
#endregion

#region Batch Update to SQL (TVP Method)
Write-Verbose "Updating results in SQL..."
$tvpTable = New-Object System.Data.DataTable
$tvpTable.Columns.Add("MachineName", [string]) | Out-Null
$tvpTable.Columns.Add("Is_Pingable", [bool]) | Out-Null

foreach ($status in $pingStatuses) {
    $row = $tvpTable.NewRow()
    $row["MachineName"] = $status.MachineName
    $row["Is_Pingable"] = [bool]$status.Is_Pingable
    $tvpTable.Rows.Add($row)
}

$connectionString = "Data Source=$MasterServerConnString;Integrated Security=SSPI;Initial Catalog=$DatabaseName"
using ($conn = New-Object System.Data.SqlClient.SqlConnection($connectionString)) {
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = @"
UPDATE t
SET t.IS_PINGABLE = tvp.Is_Pingable
FROM $TableName t
INNER JOIN @PingStatus tvp ON t.MachineName = tvp.MachineName
"@
    $tvpParam = $cmd.Parameters.Add("@PingStatus", [System.Data.SqlDbType]::Structured)
    $tvpParam.Value = $tvpTable
    $tvpParam.TypeName = "dbo.PingStatusType"

    $rowsAffected = $cmd.ExecuteNonQuery()
    Write-Verbose "Successfully updated $rowsAffected rows"
}
#endregion

内容的提问来源于stack exchange，提问作者Jeremy F.