Need some help from the Powershell people. Script too slow

PowerShell script exchange

Need some help from the Powershell people. Script too slow

Veeam Logoby electricd7 » Tue May 26, 2015 7:47 pm

Hello,

I have a little powershell script that is fed a list of VM names from an external database, then it uses the Veeam PS snapin to query the Veeam database for that particular VM and find out what job it is in and when its last successful good backup is. It takes these values and writes them back to the same external database. The script i have copied below works fine but is VERY VERY slow! Like takes 4-6 hours to complete and chews up a lot of memory. Can anyone take a look at what I have and perhaps suggest some tweaks or a better method?

Code: Select all
#Add Veeam Powershell snapin
if ((Get-PSSnapin -Name VeeamPSSnapIn -ErrorAction SilentlyContinue) -eq $null)
   {
   Add-PsSnapin VeeamPSSnapIn
   }

#this part sets up the connection to the external DB and grabs the list of VMs to check

$SQLServer = “AppServer”
$SQLDBName = “BackupView”
$SqlQuery = "SELECT * FROM SCCM where ServerArchitecture = 'Virtual' and (select count(*) from SCCMServerExceptions where Name0 = SCCM.Name)=0  order by Name"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Server=$SQLServer;Database=$SQLDBName;Integrated Security=True”
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()

foreach ($row in $DataSet.Tables[0].Rows)

{

#This part loops through the list of VM names and grabs $LastCompletion and $JobName from the Veeam DB
Write-host $row["Name"]
$VM = $row["Name"]
$LastCompletion = (Get-VBRRestorePoint -name "$VM" | where {$_.FindSourceJob().JobType -eq "Backup"} | sort creationtime -Descending | select -first 1).CreationTimeString
$JobName = (Get-VBRRestorePoint -name "$VM" | sort creationtime -Descending | select -first 1).FindSourceJob().name

If (($LastCompletion -ne $Null) -and ($JobName -ne $Null)){

#This part writes the resultant #LastCompletion and #JobName back to the external DB
   $sqlQuery = "Delete from dbo.VeeamStatus where Name = '$VM';INSERT INTO dbo.VeeamStatus (Name, JobName, LastCompletionTime) VALUES ('$VM', '$JobName', '$LastCompletion')"
   $SqlCmd.CommandText = $SqlQuery
   $SqlCmd.Connection = $SqlConnection
   $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
   $SqlAdapter.SelectCommand = $SqlCmd
   $DataSet = New-Object System.Data.DataSet
   $SqlAdapter.Fill($DataSet)
   $SqlConnection.Close()
   #write-host $sqlQUery
}else{
   write-host "Both variables must have been null"
}

}


The part I really need help with is below the "#This part loops through the list of VM names and grabs $LastCompletion and $JobName from the Veeam DB" comment as that is the slow section of the script.
electricd7
Expert
 
Posts: 103
Liked: 5 times
Joined: Tue Mar 27, 2012 10:13 pm
Full Name: Chad Killion

Re: Need some help from the Powershell people. Script too s

Veeam Logoby tsightler » Tue May 26, 2015 8:30 pm

I don't know how many total VMs you have, but my guess is most of the speed is lost due to the iterative calls to Get-VBRRestorePoint, which is a pretty heavy cmdlet, especially if you have a lot of restore points in the DB. So for the super low hanging fruit you can do something like:
Code: Select all
#This part loops through the list of VM names and grabs $LastCompletion and $JobName from the Veeam DB
Write-host $row["Name"]
$VM = $row["Name"]
$LastRestorePoint = Get-VBRRestorePoint -name "$VM" | where {$_.FindSourceJob().JobType -eq "Backup"} | sort creationtime -Descending | select -first 1
$LastCompletion = $LastRestorePoint.CreationTimeString
$JobName = $LastRestorePoint.FindSourceJob().name

That alone should cut the execution time roughly in half, which is still slow, but hey, it's an immediate improvement for almost no change to the code logic so it's really safe.

What you really need to do is get down to a single call to Get-VBRRestorePoint that simply loads a hash table with the information you want. That way you can iterate through the loop and just pull the values from the pre-created hash table almost immediately. I suspect this would get the execution down to minutes. I think I have some example code that does this laying around somewhere. Let me see if I can dig it up.
tsightler
Veeam Software
 
Posts: 4768
Liked: 1737 times
Joined: Fri Jun 05, 2009 12:57 pm
Full Name: Tom Sightler

Re: Need some help from the Powershell people. Script too s

Veeam Logoby tsightler » Tue May 26, 2015 11:40 pm

Alright, so here's attempt #2. It's still non-ideal, but my guess is that it will take the execution time down to be measured in minutes vs the hours you are currently are seeing:
Code: Select all
$RestorePoints = Get-VBRRestorePoint | ?{$_.FindSourceJob().JobType -eq "Backup"} | Select -Property Name, @{Name="JobName";Expression={$_.FindSourceJob().Name}}, @{Name="LastCompletion";Expression={$_.CreationTimeString}} | sort Name,LastCompletion

foreach ($row in $DataSet.Tables[0].Rows)
{
#This part loops through the list of VM names and grabs $LastCompletion and $JobName from the Veeam DB
Write-host $row["Name"]
$LastRestorePoint = $RestorePoints | ?{$_.Name -eq $row["Name"]} | Sort -Property LastCompletion -Descending | Select -First 1
$LastCompletion = $LastRestorePoint.LastCompletion
$JobName = $LastRestorePoint.JobName
}

This code makes a single call to the Get-VBRRestorePoint cmdlet and loads only the VM Name, Job Name, and Last Completion Time properties into a single array. Since this array contains only those three simple string properties it should be much smaller in memory than looping through all of the objects over and over. Only then do we enter the loop and iterate over the VMs returned by the query, searching the now much smaller, in-memory array for the for the most recent point for that VM.

In my own lab, which is likely much smaller than your production environment, the original code took 3.5 minutes, while the new code runs in about 25 seconds, 20 seconds of which is spent building the initial in-memory array. The loop now only takes about 5 seconds. I'm sure I could still do better, but probably only the 5 second portion, but doesn't seem worth the added complication unless it's still really slow in your environment.

I'll certainly be interested to hear if it helps in your real-world case.
tsightler
Veeam Software
 
Posts: 4768
Liked: 1737 times
Joined: Fri Jun 05, 2009 12:57 pm
Full Name: Tom Sightler

Re: Need some help from the Powershell people. Script too s

Veeam Logoby tsightler » Wed May 27, 2015 1:55 am

OK, here's my final attempt. After thinking about this a little more I decided that it really didn't add much complication to use a hash table if I simply loaded the initial results from Get-VBRRestorePoint directly into a hash table using Group-Object. Sure enough, this worked great and cut the final execution time for the loop from the 5 seconds in the code from the previous post to <1 second (likely to be an even bigger improvement for large environments as this code should scale roughly linearly instead of exponentially as the number of restore points increase). While I can think of a few additional inefficiencies that could be improved upon, I don't think they would make much difference to the final run time and I think this will probably run at least 30x faster than the original code and perhaps even faster.

Code: Select all
$RestorePoints = Get-VBRRestorePoint | ?{$_.FindSourceJob().JobType -eq "Backup"} | Select -Property Name, @{Name="JobName";Expression={$_.FindSourceJob().Name}}, @{Name="LastCompletion";Expression={$_.CreationTimeString}} | Group-Object -Property Name -AsHashTable

foreach ($row in $DataSet.Tables[0].Rows)

{

#This part loops through the list of VM names and grabs $LastCompletion and $JobName from the Veeam DB
Write-host $row["Name"]
$LastCompletion = $null
$JobName = $null
if($RestorePoints.ContainsKey($row["Name"]) {
    $LastRestorePoint = $RestorePoints | ?{$_.Name -eq $row["Name"]} | Sort -Property LastCompletion -Descending | Select -First 1
    $LastCompletion = $LastRestorePoint.LastCompletion
    $JobName = $LastRestorePoint.JobName
}
tsightler
Veeam Software
 
Posts: 4768
Liked: 1737 times
Joined: Fri Jun 05, 2009 12:57 pm
Full Name: Tom Sightler

Re: Need some help from the Powershell people. Script too s

Veeam Logoby electricd7 » Wed May 27, 2015 6:07 pm

Thanks for the suggestions and code! I am looping through about 600 vms. I will try attempt 3 and let you know what it does. Thanks!
electricd7
Expert
 
Posts: 103
Liked: 5 times
Joined: Tue Mar 27, 2012 10:13 pm
Full Name: Chad Killion

Re: Need some help from the Powershell people. Script too s

Veeam Logoby electricd7 » Wed May 27, 2015 6:36 pm

I am getting "Both variables must have been null" for every iteration now. I have the following script running for reference:

Code: Select all
#Add Veeam Powershell snapin
if ((Get-PSSnapin -Name VeeamPSSnapIn -ErrorAction SilentlyContinue) -eq $null)
   {
   Add-PsSnapin VeeamPSSnapIn
   }

$SQLServer = “AppServer”
$SQLDBName = “BackupView”
$SqlQuery = "SELECT * FROM SCCM where ServerArchitecture = 'Virtual' and (select count(*) from SCCMServerExceptions where Name0 = SCCM.Name)=0  order by Name"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Server=$SQLServer;Database=$SQLDBName;Integrated Security=True”
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()

$RestorePoints = Get-VBRRestorePoint | ?{$_.FindSourceJob().JobType -eq "Backup"} | Select -Property Name, @{Name="JobName";Expression={$_.FindSourceJob().Name}}, @{Name="LastCompletion";Expression={$_.CreationTimeString}} | Group-Object -Property Name -AsHashTable

foreach ($row in $DataSet.Tables[0].Rows)

  {

    #This part loops through the list of VM names and grabs $LastCompletion and $JobName from the Veeam DB
    $LastCompletion = $Null
    $JobName = $Null
    if ($RestorePoints.ContainsKey($row["Name"]))
      {
        $LastRestorePoint = $RestorePoints | ?{$_.Name -eq $row["Name"]} | Sort -Property LastCompletion -Descending | Select -First 1
        $LastCompletion = $LastRestorePoint.LastCompletion
        $JobName = $LastRestorePoint.JobName
      }

      Write-host $LastCompletion

      If (($LastCompletion -ne $Null) -and ($JobName -ne $Null)){


   $sqlQuery = "Delete from dbo.VeeamStatus where Name = '$VM';INSERT INTO dbo.VeeamStatus (Name, JobName, LastCompletionTime) VALUES ('$VM', '$JobName', '$LastCompletion')"
   $SqlCmd.CommandText = $SqlQuery
   $SqlCmd.Connection = $SqlConnection
   $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
   $SqlAdapter.SelectCommand = $SqlCmd
   $DataSet = New-Object System.Data.DataSet
   $SqlAdapter.Fill($DataSet)
   $SqlConnection.Close()
   #write-host $sqlQUery
     }else{
   write-host "Both variables must have been null"
     }

  }
electricd7
Expert
 
Posts: 103
Liked: 5 times
Joined: Tue Mar 27, 2012 10:13 pm
Full Name: Chad Killion

Re: Need some help from the Powershell people. Script too s

Veeam Logoby electricd7 » Wed May 27, 2015 8:46 pm

I have verified that the hash table contains the correct keys for the server names. A sample is below:
Name Value
---- -----
RALS_RALSRCS {@{Name=RALS_RALSRCS; JobName=vmds_nas05; LastCompletion=5/13/2015 11:01:02 PM}, @{Na...
WFAnalyticsAPP {@{Name=WFAnalyticsAPP; JobName=vmds_nas02; LastCompletion=5/11/2015 6:24:16 PM}, @{N...
vmturbo64.x86_64-4.0.0 {@{Name=vmturbo64.x86_64-4.0.0; JobName=vmds_nas02; LastCompletion=5/11/2015 6:03:44 ...
KronosWFCAppTest {@{Name=KronosWFCAppTest; JobName=vmds_nas9; LastCompletion=5/8/2015 6:32:24 PM}, @{N...

Then when I go to pull $LastCompletion, it always ends up NULL even if I hard-code the server name into the line (I changed $row["Name"] into $VM as a trial):
if ($RestorePoints.Contains($VM))

What am I missing?
electricd7
Expert
 
Posts: 103
Liked: 5 times
Joined: Tue Mar 27, 2012 10:13 pm
Full Name: Chad Killion

Re: Need some help from the Powershell people. Script too s

Veeam Logoby tsightler » Wed May 27, 2015 9:00 pm

Perhaps I have a typo in the script. Any chance you haven't updated to at least Powershell 3.0? I'm not at a place where I have access to my lab bit the first thing I would have had you test is the hash table creation itself so that looks good. Should be something simple. As soon as I get somewhere that I can access my lab I'll take a look. Might be a few hours though.
tsightler
Veeam Software
 
Posts: 4768
Liked: 1737 times
Joined: Fri Jun 05, 2009 12:57 pm
Full Name: Tom Sightler

Re: Need some help from the Powershell people. Script too s

Veeam Logoby electricd7 » Wed May 27, 2015 9:01 pm

I can confirm that the script is making inside of the if/then loop which follows, but the variables for $lastcompletion and $jobname are coming back as $null:

Code: Select all
#this if/then works
    if ($RestorePoints.ContainsKey($VM))
      {
        write-host "I made it into this section"
        $LastRestorePoint = $RestorePoints | ?{$_.Name -eq $VM} | Sort -Property LastCompletion -Descending | Select -First 1
        $LastCompletion = $LastRestorePoint.LastCompletion
        $JobName = $LastRestorePoint.JobName
      }
electricd7
Expert
 
Posts: 103
Liked: 5 times
Joined: Tue Mar 27, 2012 10:13 pm
Full Name: Chad Killion

Re: Need some help from the Powershell people. Script too s

Veeam Logoby electricd7 » Wed May 27, 2015 9:02 pm

we must have been posting at same time. I am on PS 3.0. I have copied the script as it currently is so you can see exactly what I am running:

Code: Select all
#Add Veeam Powershell snapin
if ((Get-PSSnapin -Name VeeamPSSnapIn -ErrorAction SilentlyContinue) -eq $null)
   {
   Add-PsSnapin VeeamPSSnapIn
   }

$SQLServer = “AppServer”
$SQLDBName = “BackupView”
$SqlQuery = "SELECT * FROM SCCM where ServerArchitecture = 'Virtual' and (select count(*) from SCCMServerExceptions where Name0 = SCCM.Name)=0  order by Name"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Server=$SQLServer;Database=$SQLDBName;Integrated Security=True”
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()

$RestorePoints = Get-VBRRestorePoint | ?{$_.FindSourceJob().JobType -eq "Backup"} | Select -Property Name, @{Name="JobName";Expression={$_.FindSourceJob().Name}}, @{Name="LastCompletion";Expression={$_.CreationTimeString}} | Group-Object -Property Name -AsHashTable

$RestorePoints.GetEnumerator() | Sort-Object Value -descending
foreach ($row in $DataSet.Tables[0].Rows)

  {

    #This part loops through the list of VM names and grabs $LastCompletion and $JobName from the Veeam DB
    $LastCompletion = $Null
    $JobName = $Null
    $VM = $row["Name"]
    if ($RestorePoints.Contains($VM))
      {
   write-host "I got inside the if then"
        $LastRestorePoint = $RestorePoints | ?{$_.Name -eq $VM} | Sort -Property LastCompletion -Descending | Select -First 1
        $LastCompletion = $LastRestorePoint.LastCompletion
        $JobName = $LastRestorePoint.JobName
      }

      If (($LastCompletion -ne $Null) -and ($JobName -ne $Null)){


   $sqlQuery = "Delete from dbo.VeeamStatus where Name = '$VM';INSERT INTO dbo.VeeamStatus (Name, JobName, LastCompletionTime) VALUES ('$VM', '$JobName', '$LastCompletion')"
   $SqlCmd.CommandText = $SqlQuery
   $SqlCmd.Connection = $SqlConnection
   $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
   $SqlAdapter.SelectCommand = $SqlCmd
   $DataSet = New-Object System.Data.DataSet
   $SqlAdapter.Fill($DataSet)
   $SqlConnection.Close()
   write-host "$VM : DB has been updated with date of $LastCompletion"
     }else{
   write-host "$VM : Both variables must have been null"
     }

  }
electricd7
Expert
 
Posts: 103
Liked: 5 times
Joined: Tue Mar 27, 2012 10:13 pm
Full Name: Chad Killion

Re: Need some help from the Powershell people. Script too s

Veeam Logoby tsightler » Wed May 27, 2015 11:32 pm 1 person likes this post

I found the error, somehow when I copied and pasted I managed to mix some code from the earlier script versions. The line that reads:
Code: Select all
$LastRestorePoint = $RestorePoints | ?{$_.Name -eq $VM} | Sort -Property LastCompletion -Descending | Select -First 1

(it's right after your check to determine if it's getting into the loop), should instead read:
Code: Select all
$LastRestorePoint = $RestorePoints.$VM | Sort -Property LastCompletion -Descending | Select -First 1

Since $RestorePoints is a hash we can call right into it with the key to get the list of restore points quickly, no need for the pipe. That was pretty much the entire point of using a hash so not sure how I messed that up when I copied the script to the forum last night.

That should be it, but let me know if there's still something else not working. I expect this will run quite fast.

BTW, one other optimization you might consider is on your SQL query itself. You pulling in "*", which is everything in the table, but then you only use the name. Assuming that's a big table with a lot of columns this might be where some of the memory is being used. Just replace "Select * From..." with "Select Name From..." and the result should be the same. Unless you're using the other stuff somewhere else.
tsightler
Veeam Software
 
Posts: 4768
Liked: 1737 times
Joined: Fri Jun 05, 2009 12:57 pm
Full Name: Tom Sightler

Re: Need some help from the Powershell people. Script too s

Veeam Logoby electricd7 » Thu May 28, 2015 2:23 pm

Yep. That works great now. Cuts the time down to 5 or 6 minutes! I need to get better at powershell! Thanks for the help!
electricd7
Expert
 
Posts: 103
Liked: 5 times
Joined: Tue Mar 27, 2012 10:13 pm
Full Name: Chad Killion


Return to PowerShell



Who is online

Users browsing this forum: No registered users and 3 guests