Veeam SQL Backend and Impact of SQL Trans log backups on it.

[SyNtAxx]

I've been running into issues with the performance of my the SQL back end for my Veaam server. Specifically errors in jobs/logs indicating the SQL server is 'unresponsive or under heavy io/load' and jobs fail, etc.

I've opened a case (01313710) on the topic and have done a lot of the requested troubleshooting issues to alleviate the problem(s). Most recently I created a new DB container and restored the Veeam backup config into it. That did help clean, reduce the size of the db by almost 2gb. However the issues persist. Specifically what is noticed from a SQL standpoint by our DBA team is significant SQL 'blocking' going on where queries seem to trample over each other until nothing gets done and the DB becomes unavailable.

Some of the offending queries:

VeeamBackup.dbo.GetSqlBackupIntervalSessByTaskSessId;1
VeeamBackup.dbo.GetSqlBackupIntervalSessByTaskSessId;1
VeeamBackup.dbo.UpdateSqlBackupIntervalSess;1
VeeamBackup.dbo.FindLastJobSession;1
VeeamBackup.dbo.GetSqlBackupIntervalSessByTaskSessId;1
VeeamBackup.dbo.GetSqlBackupIntervalSessByTaskSessId;1
VeeamBackup.dbo.GetSqlBackupIntervalSessByTaskSessId;1
VeeamBackup.dbo.GetGuestDatabasesByObjId;1
VeeamBackup.dbo.GetSqlBackupIntervalSessByTaskSessId;1
VeeamBackup.dbo.GetGuestDatabaseById;1
VeeamBackup.dbo.UpdateSqlBackupIntervalSess;1
VeeamBackup.dbo.GetSqlBackupIntervalSessByTaskSessId;1
VeeamBackup.dbo.GetSqlBackupIntervalSessByTaskSessId;1


Based on the text of the statements above I concluded it was something to do with the SQL Transaction Log backups we have running on multiple SQL Server Job policies. To test my theory, I disabled the those jobs and let the transaction log backup jobs age off (15 minute interval). Once they aged out, all SQL level blocking on the Veeam back end DB stopped. So, the question now becomes, what is happening to cause this? What can be done to resolve this as stopping the jobs is not acceptable. The amount of time to reconfigure to native SQL backups is also large as this impacts hundreds of servers. Has anybody else noticed similar behavior or symptoms?

Some info on the Veeam backup Server the SQL back end is integrated on:

Virtual
Veeam v9 on Windows 2012R2
16 cores across 2 procs
128GB ram with 64 dedicated to SQL server
SQL Server 2008R2
hosted on a 3par v400 SAN with 1000 drives.
No CPU Contention
NO Ram Contention
No Disk Queuing
Single digit ms disk latencies.


Thanks,

Nick

[skrause]

How many jobs are you running concurrently? Are your Veeam jobs configured as 1 per VM and all set to start at/around the same time or do you have multiple VMs per job?

I ran into a problem with SQL server performance causing job failures at my previous position because we had several hundred individual VM jobs running at the same time and it would cause a lot of transactions on the SQL server (we were using Express) that would bog things down. Especially during synthetic/merge operations.

[SyNtAxx]

We have about 1800-2000 vms with about 1200 of those currently backed up by veeam. What seems to be causing the issue is specifically Veeam SQL Server Transaction log jobs. We are currently backing up SQL transaction log backups for about 150 vms with more to come. Those are spread across 13/14 Specific jobs declarations each with a 15 minute transaction log interval. We do not do synthetics of any kind so far. Strickly Active Fulls and Incrementals. My proxy server is maxed at 100 streams and barely breaks a sweat. Its specs are as follows:

physical server
win2k12r2
384gb ram
2 procs @ 14 core each @ 2Ghz
FC connected to SAN at 2x 8Gbps
2 @ 10gE @ LACP to Cisco 5ks connectted to a HPE StoreOnce 6500

-Nick

[foggy]

We are aware of Veeam DB performance issues in cases where transaction logs are being backed up for considerable amount of VMs. Some research in this area has already been performed and we are working on future optimizations.

[Gostev]

I will try to speed up the resolution from my end, as these sort of scalability issues are very important for us to have addressed. Because of that, we've actually spent an incredible amount of time optimizing SQL interaction in v9, so perhaps this scenario was simply overlooked (assuming you are using v9). Thanks!

[Gostev]

OK, so just to circle back on this and update everyone - we've made a number of changes in 9.5 that should significantly improve the situation. These are not exactly point fixes (as simply removing the bottleneck in one place instantly moved it to the next one), so we had to touch a lot of components and make optimizations even to some "unrelated" areas like memory consumption (which grew much after removing some bottlenecks) in the end. Because of that, we will not be able to include these improvements into Update 2 in Q2 - but hopefully this should not be a big problem, because our 9.5 release is planned for Q3 anyway. Thanks!