Can I Improve this code by using AsJob (Help)?

by schwizla at 2013-04-24 03:21:38

Hi All,

This is my first post so apologies if its in the wrong place. I am working on a script which checks a server for ping and then checks all SQL Server Services status and stores this in a table. It also caters for SQL Instances. However I have to check across 70 servers and it takes over a minute to run. I looked into the AsJob parameter and when I added this to all my "Get_WMIObject xxService" Commands it started returning the wrong information for each Service. i.e it started returning "running" status for all so I suspect it was just repeatiung the previous data capture. Although it did run in just 20 seconds. Can someone take a look below and advise where I am going wrong or what I can do to make the retrieval of the server service information asynchronous?

I thought it might be better the to store the Service status for all servers in a hashtable and update the SQL DataTable in one go but its proving quite complicated. I feel my code needs a complete rethink in order to run asynchronously but I am trying to avoid this!


Tests all Servers for Ping and SQL Server Service Status

# Return the Ping Status
Function GetStatusCode
Param([int] $StatusCode)
0 {"Success"}
11001 {"Buffer Too Small"}
11002 {"Destination Net Unreachable"}
11003 {"Destination Host Unreachable"}
11004 {"Destination Protocol Unreachable"}
11005 {"Destination Port Unreachable"}
11006 {"No Resources"}
11007 {"Bad Option"}
11008 {"Hardware Error"}
11009 {"Packet Too Big"}
11010 {"Request Timed Out"}
11011 {"Bad Request"}
11012 {"Bad Route"}
11013 {"TimeToLive Expired Transit"}
11014 {"TimeToLive Expired Reassembly"}
11015 {"Parameter Problem"}
11016 {"Source Quench"}
11017 {"Option Too Big"}
11018 {"Bad Destination"}
11032 {"Negotiating IPSEC"}
11050 {"General Failure"}
default {"Failed"}

# Format the Server Up-time
Function GetUpTime
param([string] $LastBootTime)
$Uptime = (Get-Date) - [System.Management.ManagementDateTimeconverter]::ToDateTime($LastBootTime)
"Days: $($Uptime.Days); Hours: $($Uptime.Hours); Minutes: $($Uptime.Minutes); Seconds: $($Uptime.Seconds)"

#Main Body
# Populate Table MyDB.dbo.tbl_ServerPingTest

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=MyServer; Initial Catalog=MyDB; Integrated Security=SSPI")

$cmd = $conn.CreateCommand()
$cmd.CommandText ="DELETE FROM MyDB.dbo.tbl_ServerPingTest
INSERT INTO MyDB.dbo.tbl_ServerPingTest (ServerName, InstanceName)
SELECT ServerName, InstanceName FROM MyDB.dbo.tbl_servers
WHERE ServerCategory <> 'DECOMMED'"

$cmd2 = $conn.CreateCommand()
$cmd2.CommandText = "SELECT * FROM MyDB.dbo.tbl_ServerPingTest"
$da = New-Object System.Data.SqlClient.SqlDataAdapter #($cmd2)
$da.SelectCommand = $cmd2
$dt = New-Object System.Data.DataTable
$da.Fill($dt) | Out-Null

# Cycle through Server and Instances and retrieve information
Foreach($row in $dt.rows)
$ServerName = $row.ServerName
$InstanceName = $row.InstanceName

$pingStatus = Get-WmiObject -Query "Select * from win32_PingStatus where Address='$ServerName'"

$Uptime = $null
$SQLServerStatus = $null
$SQLAgentStatus = $null

# Enter the Loop if a Server is Pingable
if($pingStatus.StatusCode -eq 0)
# Trap needed for server where Access is Denied causes the SQL Job to fail
trap {continue}
$OperatingSystem = Get-WmiObject Win32_OperatingSystem -ComputerName $ServerName -ErrorAction SilentlyContinue -ErrorVariable wmiResults
$Uptime = GetUptime( $OperatingSystem.LastBootUpTime )

if ($wmiResults -ne $null)
$tmperr = "Uptime Info Could Not be Obtained"
$Uptime = $null
$tmperr = ""

filter SvcFilter {
if ($.StartMode -eq "Disabled") {$.StartMode }
else {$.State}


if ($InstanceName -eq 'DEFAULT')
Write-Host $ServerName + '&#39; + $InstanceName
$SQLServerStatus = Get-WMIObject win32_service -property $props -filter "name='MSSQLSERVER'" -computer $ServerName | SvcFilter
#Write-Host $SQLServerStatus
$SQLAgentStatus = Get-WMIObject win32_service -property $props -filter "name='SQLSERVERAGENT'" -computer $ServerName | SvcFilter
#Write-Host $SQLAgentStatus
$RSAgentStatus = Get-WMIObject win32_service -property $props -filter "name='ReportServer'" -computer $ServerName | SvcFilter
#Write-Host $RSAgentStatus
Write-Host $ServerName + '&#39; + $InstanceName
$NamedInstanceSQLService = "MSSQL$" + $InstanceName
$NamedInstanceAgentService = "SQLAgent$" + $InstanceName
$NamedInstanceRSService = "ReportServer$" + $InstanceName
$SQLServerStatus = Get-WMIObject win32_service -property $props -computer $ServerName | where {$
.name -eq $NamedInstanceSQLService} | SvcFilter
$SQLAgentStatus = Get-WMIObject win32_service -property $props -computer $ServerName | where {$.name -eq $NamedInstanceAgentService} | SvcFilter
$RSAgentStatus = Get-WMIObject win32_service -property $props -computer $ServerName | where {$
.name -eq $NamedInstanceRSService} | SvcFilter

$ASAgentStatus = Get-WMIObject win32_service -property $props -filter "name='MSSQLServerOLAPService'" -computer $ServerName | SvcFilter


$IPAddress = $pingStatus.IPV4Address
$PingTest = GetStatusCode( $pingStatus.StatusCode )
$ErrMSG = $tmperr

# Update Table MyDB.dbo.tbl_ServerPingTest with all retreived information
$updateRow = $dt.Select("ServerName = '$ServerName' AND InstanceName = '$InstanceName'")
$updateRow[0].IPAddress = $IPAddress
$updateRow[0].PingTest = $PingTest
$updateRow[0].ErrMSG = $ErrMSG
$updateRow[0].Uptime = $Uptime
$updateRow[0].SQLServerStatus = $SQLServerStatus
$updateRow[0].SQLAgentStatus = $SQLAgentStatus
$updateRow[0].RSAgentStatus = $RSAgentStatus
$updateRow[0].ASAgentStatus = $ASAgentStatus

$cmdUpd = $conn.CreateCommand()
$cmdUpd.CommandText = "UPDATE MyDB.dbo.tbl_ServerPingTest
SET IPAddress = @IPAddress, PingTest = @PingTest, ErrMSG = @ErrMSG, Uptime = @Uptime, SQLServerStatus = @SQLServerStatus, SQLAgentStatus = @SQLAgentStatus, RSAgentStatus = @RSAgentStatus, ASAgentStatus = @ASAgentStatus
WHERE ServerName = @ServerName AND InstanceName = @InstanceName"

# Add parameters to pass values to the UPDATE statement
$cmdUpd.Parameters.Add("@ServerName", "nvarchar", 50, "ServerName") | Out-Null
$cmdUpd.Parameters["@ServerName"].SourceVersion = "Original"
$cmdUpd.Parameters.Add("@InstanceName", "nvarchar", 50, "InstanceName") | Out-Null
$cmdUpd.Parameters["@InstanceName"].SourceVersion = "Original"
$cmdUpd.Parameters.Add("@IPAddress", "nvarchar", 50, "IPAddress") | Out-Null
$cmdUpd.Parameters["@IPAddress"].SourceVersion = "Current"
$cmdUpd.Parameters.Add("@PingTest", "nvarchar", 50, "PingTest") | Out-Null
$cmdUpd.Parameters["@PingTest"].SourceVersion = "Current"
$cmdUpd.Parameters.Add("@ErrMSG", "nvarchar", 50, "ErrMSG") | Out-Null
$cmdUpd.Parameters["@ErrMSG"].SourceVersion = "Current"
$cmdUpd.Parameters.Add("@Uptime", "nvarchar", 50, "Uptime") | Out-Null
$cmdUpd.Parameters["@Uptime"].SourceVersion = "Current"
$cmdUpd.Parameters.Add("@SQLServerStatus", "nvarchar", 50, "SQLServerStatus") | Out-Null
$cmdUpd.Parameters["@SQLServerStatus"].SourceVersion = "Current"
$cmdUpd.Parameters.Add("@SQLAgentStatus", "nvarchar", 50, "SQLAgentStatus") | Out-Null
$cmdUpd.Parameters["@SQLAgentStatus"].SourceVersion = "Current"
$cmdUpd.Parameters.Add("@RSAgentStatus", "nvarchar", 50, "RSAgentStatus") | Out-Null
$cmdUpd.Parameters["@RSAgentStatus"].SourceVersion = "Current"
$cmdUpd.Parameters.Add("@ASAgentStatus", "nvarchar", 50, "ASAgentStatus") | Out-Null
$cmdUpd.Parameters["@ASAgentStatus"].SourceVersion = "Current"

# Set the UpdateCommand property
$da.UpdateCommand = $cmdUpd

# Update the database
$RowsUpdated = $da.Update($dt)


by mjolinor at 2013-04-24 04:21:48
It sounds like it taking a little over a second per computer. Trying to use -AsJob to speed it up is probably going to be counter-productive. Using a job adds a few seconds of setup and teardown time per job, so you could easily end up making it worse.

You can look at doing it with runspaces, but that’s considerably more complicated.

It appears you could probably do some optimizing on the script you have and cut the run time down.

In your service status check, you’re doing get-wmiobject 3 times on the same wmi class.
Don’t do that. Get it once and save the result to a variable, then get the various properties you need from there.

Since you’re getting multiple WMI classes from the same computer, you may be able to save a measureable amout of time switching to CIM, and using a persistent CIM session (create the CIM session, get all the instances you need saved to variables, then remove the session).

Edit: I think I’d also trade that Switch for a simple hash table. Remeber that a Switch will test the input value against every test in the stack, unless you put in a Break, and even then it’s going to run every test until it gets a match. A simple look up hash table to map the return codes to the verbose error should accomplish the same thing with considerably less overhead.
by schwizla at 2013-04-24 05:33:57
Hi, thank for you reply, I will look into your points. A couple of questions. Excuse my ignorance but what do you mean by CIM? Also when you say "Switch" which bit of code are you referring to?

Thanks again
by mjolinor at 2013-04-24 05:56:06
Jeff Hicks has a nice set of articles going on CIM: … art-1.aspx

By "switch" I’m referring to that switch statement (switch($StatusCode) ) at the top of the script.
by schwizla at 2013-04-24 06:03:45
ahh, thanks