VBR Database load after V12 upgrade

[johannesk]

Hi,

We upgraded to Veeam V12 yesterday. We see one issue after the upgrade. The SQL server hosting the VBR database now has much more CPU load. This is a dedicated SQL 2019 for VBR. I have created ticket #06033530 for this.

Now we have looked at what's going on with the SQL server and it turnes out that 2 of the 3 most heavey queries have index recommendation from the SQL server. Two of those 3 most heavey queries have select * in them.

Now I'm wondering how is this going in other installations? are you seeing same? And to Veeam I want to ask, can Veeam consider to add indexes?

Regards,
Jóhannes

[PetrM]

Hi Johannes,

The version 12 has many performance enhancements as every major release does so it's not an expected behavior that we can see on the majority of other installations. This technical issue must be investigated by our support engineers and I guess there are 2 different ways to solve this issue: to simplify the query itself or add indexes as you say (usually we add them). However, I cannot say without looking at the query itself and our debug logs which method is better or if there are completely different ways to sort it out, let's wait for the conclusion from our support.

Thanks!

[johannesk]

Glad to hear that performance is gererally getting better with v12. In our case the SQL server will perform better if queries have appropriate indexes and queries that make use of them. So I hope I have some success with my support case. I'll update this thread when the support case progresses.

Regards,
Jóhannes

[johannesk]

The issue with the SQL server for VBR database is much more CPU loaded after V12 upgrade has been resolved. First we were looking at most heavy queries on the SQL that were reported by our SQL server monitor. Those pointed mostly to the VBR database. We added the index the SQL server recommended for the most heavy query, but that did not change much.

Then we created another ticket with support because we had issues with stopping Enterprise Manager case #06058207. The support noticed errors in the logs

'Error Failed to execute SQL stored procedure "[usp.Repl.ProcessBackupChains]". (Veeam.Backup.Common.CRegeneratedTraceException)'

And adviced to install private fix TF488844. After that the SQL server whent from being constantly in 80-90% CPU usage to 40-50%. So it was a big change for us.

Now the SQL server is performing better than when we were running VBR V11.

Regards,
Jóhannes

[JaySt]

can you share some numbers about your environment like number of jobs, repositories, backed up virtual machines, copy jobs etc? not sure to what environments i'm seeing i need to compare you against.

[johannesk]

An approximate numbers.

50 SQL servers, and transaction log backups running every 5 minutes
backing up 900 vm's
Tape jobs running almost daily
half of the VM backups are copied to remote repository

But if you see the error I mention above, you possibly have the same issue. You would then need to contact support to get the fix I mention. The symptom that led us to the fix was that Enterprisemanager service did not stop when we tried to. We were killing the EM process, and that's why we contacted support. This fix fixed that also as well as the CPU issue.

regards,
Jóhannes