Adding Indexes on VeeamBackup Database Tables

[thecyborg]

Is it supported to add SQL indexes to tables in the VeeamBackup database? There are obvious index optimizations that should be made (findings shown below) that would greatly reduce the load on our SQL server. I'm curious if creating these indexes would pose a problem the next time database schema is changed in a Veeam update.

Database Name: VeeamBackup
Details: [VeeamBackup].[dbo].[Tombstones.Backups] Est. benefit per day: 1,124,210
Usage: 47,862 uses; Impact: 84.1%; Avg query cost: 15.0595
Size: 0 NC indexes exist (0.00MB); 87,544 Estimated Rows;
Create TSQL: CREATE INDEX [ix_Tombstones.Backups_backup_id_tombstone_usn_includes] ON [VeeamBackup].[dbo].[Tombstones.Backups] ([backup_id], [tombstone_usn]) INCLUDE ([tombstone_id]) WITH (FILLFACTOR=100, ONLINE=?, SORT_IN_TEMPDB=?);

Database Name: VeeamOne
Details: [VeeamOne].[reporter].[ObjectProperty] Est. benefit per day: 169,337
Usage: 4,598 uses; Impact: 98.3%; Avg query cost: 20.2014
Size: 2 NC indexes exist (78.11MB); 1,384,717 Estimated Rows;
Create TSQL: CREATE INDEX [ix_ObjectProperty_PropertyID_ModificationID_includes] ON [VeeamOne].[reporter].[ObjectProperty] ([PropertyID], [ModificationID]) INCLUDE ([ID], [ObjectID], [PropKey], [ParentID]) WITH (FILLFACTOR=100, ONLINE=?, SORT_IN_TEMPDB=?);

Database Name: VeeamBackup
Details: [VeeamBackup].[dbo].[Tombstones.Backups] Est. benefit per day: 166,227
Usage: 7,022 uses; Impact: 89.4%; Avg query cost: 14.2776
Size: 0 NC indexes exist (0.00MB); 87,544 Estimated Rows;
Create TSQL: CREATE INDEX [ix_Tombstones.Backups_tombstone_usn_backup_id_includes] ON [VeeamBackup].[dbo].[Tombstones.Backups] ([tombstone_usn], [backup_id]) INCLUDE ([tombstone_id]) WITH (FILLFACTOR=100, ONLINE=?, SORT_IN_TEMPDB=?);

Database Name: VeeamBackup
Details: [VeeamBackup].[dbo].[Backup.Model.OIBs] Est. benefit per day: 155,594
Usage: 482,976 uses; Impact: 28.5%; Avg query cost: 0.6095
Size: 17 NC indexes exist (11.48MB); 2,445 Estimated Rows;
Create TSQL: CREATE INDEX [ix_Backup.Model.OIBs_usn_includes] ON [VeeamBackup].[dbo].[Backup.Model.OIBs] ([usn]) INCLUDE ([id], [object_id], [point_id], [storage_id], [link_id], [is_corrupted], [is_consistent], [state], [type], [alg], [inside_dir], [creation_time], [approx_size], [process_id], [parent_id], [original_oib_id], [is_recheck_corrupted], [product_id], [product_version], [product_version_flags]) WITH (FILLFACTOR=100, ONLINE=?, SORT_IN_TEMPDB=?);

Database Name: VeeamOne
Details: [VeeamOne].[reporter].[ObjectProperty] Est. benefit per day: 154,287
Usage: 4,104 uses; Impact: 76.0%; Avg query cost: 26.6723
Size: 2 NC indexes exist (78.11MB); 1,384,717 Estimated Rows;
Create TSQL: CREATE INDEX [ix_ObjectProperty_ObjectTypeID_ParentTypeID_ModificationID_includes] ON [VeeamOne].[reporter].[ObjectProperty] ([ObjectTypeID], [ParentTypeID], [ModificationID]) INCLUDE ([ID], [ObjectID], [PropertyID], [ParentID]) WITH (FILLFACTOR=100, ONLINE=?, SORT_IN_TEMPDB=?);

[Dima P.]

Hello thecyborg,

Thanks for the interesting question! Before we take it to our DEV team can you please provide us with the exact version of VeeamOne and Veeam B&R you are using. Thanks in advance.

[alexander sh]

Hey thecyborg!

Important disclaimer: I am only responsible for VBR database (VeeamBackup), for indices on VeeamOne DB you'll need to wait for Veeam One devs to reply.

You can safely create these indices on VeeamBackup DB, they will not interfere with the update process in the future.
Actually, two suggested indices on [dbo].[Tombstones.Backups] are included in 9.5 U3 (which will be released later this year), so I can confirm they indeed make a lot of sense.
I'm not 100% certain about the third one (one on [Backup.Model.OIBs] table). It includes almost every column in the table, so it will affect both table size and insert/update performance quite significantly. I'll have to investigate further if it's justified for everyone. However, you are of course welcome to create it if your data distribution suggests it's a good idea.

[thecyborg]

Thanks! I implemented the two indices on [Tombstones.Backups]. It appears to have markedly increased the performance of certain views in the console, and now I don't have to see those suggested index notifications from my optimization scripts!