Get-WmiObject Performance

If you are new to PowerShell and have searched through the “google-verse” you’ll likely find millions of examples with most if not all filtering the results via “WHERE” cmdlet. One thing I noticed today while conducting research on Get-WmiObject vs Get-Service was a serious performance difference. I wanted to understand the reason why Get-Service was lighting fast in contrast to Get-WmiObject. I mean literally 10 to 1 in regards to performance (measured in seconds in my particular case). After reviewing a few sites and articles and growing increasingly impatient I found no resource that would answer my question. So instead of continuing the search I turned to tinkering and I tell you I am glad I did. The best 10 mins that would completely change my life! I was able to tune my process and speed up performance by 62%. From the previous 2 mins down to 46 seconds. Here’s what I did. Below is merely an example for illustration purposes. Get-Service function GetServices ($server) { $StartDate = (Get-Date) $services = @(Get-Service -ComputerName $server | Where-Object {$_.name -like '*sql*' -or $_.name -like '*MsDTS*' -or $_.name -like '*ReportServer*'} | Select-Object MachineName, Status, Name, DisplayName | Sort-Object Status -Descending) $services | ft -AutoSize # DISPLAY DURATION $EndDate = (Get-Date) $r = New-TimeSpan -Start $StartDate -End $EndDate | select Seconds, Minutes, Hours Write-Output '' Write-Output "Elapsed Time: $($r.Hours) Hrs $($r.Minutes) Mins $($r.Seconds) Secs" } Get-WmiObject using the WHERE cmdlet function GetServicesWMI ($server) { $StartDate = (Get-Date) $services = @(Get-WmiObject -computername $server -Class win32_service | Where-Object {$_.name -like '*sql*' -or $_.name -like '*MsDTS*' -or $_.name -like '*ReportServer*'} | SELECT-Object SystemName, state, name, startmode,...

PowerShell Get-Service

Since I have written a process that checks all of our SQL Services I have been researching other methods and might have found a quicker way using Get-Service but would require an addition step to pass the arguments accordingly on a server by server basis. Potentially I could setup a nightly process to query all of the sql related services for all our environments and save the results to a table. Then loop through the records by server type (Production, Development, Test, Staging, etc…) for any service with a startup -eq “Auto” with a state -ne “running”. If configured properly this could potentially report a service outage for a given server. This option is great for getting information about a single service but one thing I read was that you can pass multiple arguments for the service name; however, this method doesn’t format it well. function GetServiceInfo ($server, $service) { Get-Service -Name $service -ComputerName $server } GetServiceInfo Server01 SQLWriter This option loads the results into an array and then formats the hash table nicely especially when sending multiple arguments for the service name. function GetServiceInfo ($server, $service) { $svcs = @(Get-Service -Name $service -ComputerName $server) $svcs | ft -AutoSize } GetServiceInfo Server01 SQLWriter, SQLServerAgent, SQLBrowser, ReportServer, MSSQLServerOlapService, MSSQLServer, MSDTSServer110 The only problem you will have with this particular method is with the error message. It will not accurately report a “permissions issue” as Get-WmiObject would. It will falsely report that it cannot find any service with the service name: ‘service name would go here’ instead of reporting that it encountered a permissions issue against the said server. That’s only...

PowerShell Change Service Startup

In the event you need to change the service startup from either “Manual”, “Automatic”, or “Disabled” on a remote server, PowerShell makes it extremely easy and here’s one of likely many ways you can do it. function ChangeServiceStartup ($server, $service, $startup) { Set-Service -Name $service -Computername $server -StartupType $startup } ChangeServiceStart Server01 SQLWriter...
Samson

Samson

SQL Server DBA.