Comprehensive data protection for all workloads
Post Reply
thecyborg
Novice
Posts: 4
Liked: 1 time
Joined: Jun 22, 2016 4:20 pm
Contact:

Adding Indexes on VeeamBackup Database Tables

Post by thecyborg » 1 person likes this post

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.
Product Manager
Posts: 14396
Liked: 1568 times
Joined: Feb 04, 2013 2:07 pm
Full Name: Dmitry Popov
Location: Prague
Contact:

Re: Adding Indexes on VeeamBackup Database Tables

Post by 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
Veeam Software
Posts: 40
Liked: 28 times
Joined: Dec 13, 2012 8:52 am
Full Name: Alexander Shelemin
Location: Prague
Contact:

Re: Adding Indexes on VeeamBackup Database Tables

Post by 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
Novice
Posts: 4
Liked: 1 time
Joined: Jun 22, 2016 4:20 pm
Contact:

Re: Adding Indexes on VeeamBackup Database Tables

Post by 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!
Post Reply

Who is online

Users browsing this forum: brodyk, robert.vonmehren and 145 guests