Reduce query time for BackupSessions

[Talom]

Hello,

i am curious if it's possible to get the BackupSessions filtered directly and not after getting the result from the command.
Right now im using the following command to get all backup sessions for the last few days.
Get-VBRBackupSession | Where-Object { $_.EndTime -ge $date}

When i'm executing this command on a huge server i have to wait hours for a result.

Is there an alternative command where i can specify the endtime as a parameter directly?

[Egor Yakovlev]

Hi Talom.

- I am wondering how big of a query we are talking here - at the end of those hours, how many objects it returns to you?
- Is it just this kind of query that lags? Can you run like Get-VBRRestoreSession and see it's performance...

I just ran same query in our biggest internal datacenter and it returned result in 5 minutes, totaling 103,769 sessions with EndTime greater than -365 days, which is about 284 backup sessions per day on average. I can hardly imagine you have more sessions in 2 days than we have in a year...it does seem more like a technical problem rather than query itself.

Things to check:
- Memory on a machine executing query
- SQL performance of VBR server - we read data from it, so that might be the obvious bottleneck(give it a shot with "SELECT TOP (1000) * FROM [VeeamBackup].[dbo].[Backup.Model.BackupJobSessions] WHERE total_objects >=1")

/Cheers!

[tdewin]

There seems to be a very unsupported way to query them by ranges. Normally I'm not keen on posting these kind of solution but it might be useful for other users (notice I'm just part of "sales") and again this unsupported

Code: Select all

$days = 2

$end = (get-date)
$start = ($end).AddDays(-$days)

foreach($job in ( Get-VBRJob )) {
    [Veeam.Backup.Core.CBackupSession]::GetAllSessionsByPolicyJobAndTimeRange($job.Id,$start,$end)
}

Did test this on V11 RTM so it might be that it doesn't work for V10 but in that case, hey good news is on the way, you can request the build via support.

[oleg.feoktistov]

Hi guys,

Even with fast and unsupported methods, I'd first try to figure out why such query takes hours to complete under particular circumstances before diving into internal classes, whose properties/methods might change from release to release.

Thanks,
Oleg

[oleg.feoktistov]

But just in case, the method Timothy mentioned also works in v10. Thanks!

[Talom]

Hello together,

first of all, thanks for the great answers.

@Egor 284 Sessions per day is not big for us. It's a customers server, so i'll have to contact him to check it. Memory on the system is about 64GB afak.
The problem is, it doesn't matter if i query all sessions or specify an endtime, it will query all data and takes it's time.

@tdewin I will test this. I have to support multiple Veeam versions with my script, but i can do this and if it's not working, i use the old command as a fallback.

@oleg I will post the results for Egor as soon as i have them. Is there anything else i should check?

[oleg.feoktistov]

Hi @Talom,

Assuming that you use per-VM backups and have more than one VM backed up per job, I'd also test other conceptually similar queries like Get-VBRRestorePoint. It should return more or less the same amount of objects with relevant properties, so will be easier to compare the execution time and troubleshoot.

Thanks,
Oleg