Improve Get-VBRRestorePoint performance

[Daniel N.]

Please improve the Get-VBRRestorePoint cmdlet. In our case the cmdlet returns over 18.000 records, which takes over 16 minutes. That is simply not practical.

There should be an option to pass a filter to refine the database query that is sent to the database in the first place. I don't want to wait 16 minutes only to get restore points created in the last 4 hours. This is how it could look like.

Code: Select all

Get-VBRRestorePoint -Filter { $_.CreationTime -gt (Get-Date).AddHours(-4) }

[david.domask]

Hi @Daniel N.,

The request makes some sense, but I would ask if the existing filters and some slight refactoring of your script might help here. Instead of summoning all restore points and then filtering, use the -Backup parameterand loop over the backups and then apply the same time logic. The -Backup parameter should significantly reduce the volume of data to be returned and let you process a lot faster once you apply the time logic.

BTW, I also suspect you might want to check the performance of the Powershell + SQL instance hosting the Veeam Configuration Database, as even for 18k records it's by far an abnormal amount.

In lab, I have execution time of around 23 seconds for 7.4k records; granted, it's not as many as your environment and likely you have other active jobs, but my point more is while I expect an unfiltered Get-VBRRestorePoint to take long, I don't expect 16 minutes long so I don't think it's entirely just the cmdlet performance here.

Code: Select all

PS C:\Users\Administrator> Measure-Command -Expression { $rp = Get-VBRRestorepoint }


Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 23
Milliseconds      : 812
Ticks             : 238123195
TotalDays         : 0.000275605549768519
TotalHours        : 0.00661453319444444
TotalMinutes      : 0.396871991666667
TotalSeconds      : 23.8123195
TotalMilliseconds : 23812.3195



PS C:\Users\Administrator> $rp.count
7381

[Daniel N.]

> Instead of summoning all restore points and then filtering, use the -Backup parameterand loop over the backups and then apply the same time logic.

That hits the database exactly the same, but only broken up in more steps. There is no difference between

Code: Select all

$restorePoints = Get-VBRRestorePoint | % { $_.CreationTime -gt (Get-Date).AddHours(-4) }

and

Code: Select all

$restorePointsJob1 = Get-VBRRestorePoint -Backup "Job1" | % { $_.CreationTime -gt (Get-Date).AddHours(-4) }
$restorePointsJob2 = Get-VBRRestorePoint -Backup "Job2" | % { $_.CreationTime -gt (Get-Date).AddHours(-4) }
$restorePointsJob3 = Get-VBRRestorePoint -Backup "Job3" | % { $_.CreationTime -gt (Get-Date).AddHours(-4) }
$restorePointsJob4 = Get-VBRRestorePoint -Backup "Job4" | % { $_.CreationTime -gt (Get-Date).AddHours(-4) }
$restorePoints = $restorePointsJob1 + $restorePointsJob2 + $restorePointsJob3 + $restorePointsJob4

In both cases the database will return the exact same amount of records. One is just unnecessarily more verbose.

My suggestion is actually to not query the irrelevant records at all by adjusting the query on the SQL level, so that the database doesn't waste CPU time on searching and returning irrelevant data.

> BTW, I also suspect you might want to check the performance of the Powershell + SQL instance hosting the Veeam Configuration Database, as even for 18k records it's by far an abnormal amount.

Regarding your suggestion to check PowerShell and SQL instance hosting: Our Backup Server runs on vSphere, has 4 CPUs and 16 GB of memory. We backup just under 300 VMs. The configuration database has 7.5 GB in size. The entire configuration is in the realm of what is suggested by Veeam, last time I checked.. The installation of Veeam was not customized in any way. It's all default settings. I'm not sure what I could do more, but I'm open to suggestions.

Ps, the PowerShell modules are terribly optimized anyway and I would really appreciate if some more work could be put into them. I regularly come across properties that are calculated when called, instead of just having the property calculated once and the result stored into the PSObject. It really makes working with the module a pain and it doesn't give me the impression that a lot of thought went into the modules at all. But that's material for its own thread here.

[david.domask]

Well, not quite on the -Backup flag, it does do some filtering and it should be overall less painful. But I digress, while your point is valid, I still feel you've got an underlying issue elsewhere.

For the database performance, I'd be curious about checking the same queries directly in MSSQL as opposed to powershell; frankly speaking your VM is more powerful than my lab VM, but you're seeing much worse performance. What version of SQL btw (edition + version). Check the Disk Usage by Top Tables report to see what's eating up space -- it's not necessarily "wrong", but it depends on what takes the space.

Feel free to open a Support Case for help on the DB size, but keep in mind that depending on what's using the space, it might be expected, but I think best to let evidence speak for itself.

For the performance part though, I would be curious if you could humor me and time the processing of:

From SQL:

select * from [backup.model.OIBs]
select * from [backup.model.points]

And just how long the execution takes.

As for the rest, well, posting is free And the feedback is taken fairly seriously, so share the pain points as you see them

[oleg.feoktistov]

Hi Daniel,

First, good point on CreationTime filter, noted. Second, 18k records in 16 minutes sounds tremendously long. I don't have a lab with even a comparable amount of restore points at hand to reproduce it, but, like David, I also checked it on a backup server in my case with 1 CPU and 8 GB RAM - it took about a minute to retrieve 5k restore points there. Not the best performance either, but comparing restore points / time ratio, nowhere near the time you experience.

Now, regarding powershell module being terribly optimized and properties being calculated when called - could you please submit a separate thread/threads on these topics? It would really help to see a detailed feedback with examples.

Thank you,
Oleg