Adding Indexes on VeeamBackup Database Tables

Availability for the Always-On Enterprise

Adding Indexes on VeeamBackup Database Tables

Veeam Logoby thecyborg » Wed Aug 09, 2017 7:22 pm 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=?);
thecyborg
Novice
 
Posts: 4
Liked: 1 time
Joined: Wed Jun 22, 2016 4:20 pm

Re: Adding Indexes on VeeamBackup Database Tables

Veeam Logoby Dima P. » Wed Aug 09, 2017 10:26 pm

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.
Dima P.
Veeam Software
 
Posts: 6242
Liked: 440 times
Joined: Mon Feb 04, 2013 2:07 pm
Location: SPb
Full Name: Dmitry Popov

Re: Adding Indexes on VeeamBackup Database Tables

Veeam Logoby alexander sh » Thu Aug 10, 2017 10:44 am

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.
alexander sh
Veeam Software
 
Posts: 22
Liked: 11 times
Joined: Thu Dec 13, 2012 8:52 am
Full Name: Alexander Shelemin

Re: Adding Indexes on VeeamBackup Database Tables

Veeam Logoby thecyborg » Thu Aug 10, 2017 3:50 pm

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!
thecyborg
Novice
 
Posts: 4
Liked: 1 time
Joined: Wed Jun 22, 2016 4:20 pm


Return to Veeam Backup & Replication



Who is online

Users browsing this forum: No registered users and 12 guests