Comprehensive data protection for all workloads
Post Reply
broth-itk
Novice
Posts: 5
Liked: 5 times
Joined: Jul 22, 2021 3:25 am
Full Name: Bernhard Roth
Contact:

Veeam B&R: Missing SQL indices

Post by broth-itk »

Dear Veeam Developers,

some SQL indices are missing.
Please check, fix and make your customers happy :)

Code: Select all

/*
Missing Index Details from SQLQuery2.sql - UMBVEEAM01\VEEAMSQL2012.VeeamBackup (UMBVEEAM01\Administrator (77))
The Query Processor estimates that implementing the following index could improve the query cost by 70.6172%.
*/

/*
USE [VeeamBackup]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Backup.Model.JobSessions] ([job_type])
INCLUDE ([job_id],[creation_time],[end_time],[state],[result],[operation],[progress],[usn])
GO
*/

Code: Select all

/*
Missing Index Details from SQLQuery2.sql - UMBVEEAM01\VEEAMSQL2012.VeeamBackup (UMBVEEAM01\Administrator (77))
The Query Processor estimates that implementing the following index could improve the query cost by 24.0106%.
*/

/*
USE [VeeamBackup]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Backup.Model.JobSessions] ([job_id])
INCLUDE ([job_type],[creation_time],[end_time],[state],[result],[operation],[progress],[usn])
GO
*/
Best regards,
Bernhard
HannesK
Product Manager
Posts: 14287
Liked: 2877 times
Joined: Sep 01, 2014 11:46 am
Full Name: Hannes Kasparick
Location: Austria
Contact:

Re: Veeam B&R: Missing SQL indices

Post by HannesK »

Hello,
and welcome to the forums.

just to be sure... which VBR version are you using and which SQL version? It looks like you are using an old SQL 2012 instead of recommended newer versions.

And are you sure that this performance improvement has no side effects on all possible configurations that 400,000 Veeam customers can have ;-)

Best regards,
Hannes
broth-itk
Novice
Posts: 5
Liked: 5 times
Joined: Jul 22, 2021 3:25 am
Full Name: Bernhard Roth
Contact:

Re: Veeam B&R: Missing SQL indices

Post by broth-itk »

Dear Hannes,

thanks for your quick reply!

I'm using VBR 11.0.0.837 with SQL 2019 (latest CU)

I noticed the missing indices while doing my job as DBA and checking the backup jobs in the Veeam GUI.
My job session table has around 15.000 entries.

I'm pretty sure that an SQL Index has no side effects as it only helps the SQL server to get results quicker.
They will join the group of many other already exising indices.

I added those indices in my SQL database and everything is running fine.

You may or may not consider my unsoliciated help for your dev team :)

Have a great day!
Best regards,
Bernhard
HannesK
Product Manager
Posts: 14287
Liked: 2877 times
Joined: Sep 01, 2014 11:46 am
Full Name: Hannes Kasparick
Location: Austria
Contact:

Re: Veeam B&R: Missing SQL indices

Post by HannesK »

Hello,
sounds good. I just wanted to make sure that you have the current version before I talk to developers.

Thanks for pointing it out,
Hannes
alexander sh
Veeam Software
Posts: 40
Liked: 28 times
Joined: Dec 13, 2012 8:52 am
Full Name: Alexander Shelemin
Location: Prague
Contact:

Re: Veeam B&R: Missing SQL indices

Post by alexander sh » 3 people like this post

Hi Bernhard, Hannes!

Thanks for taking the time to suggest performance improvements, I do appreciate it.

A few notes on the indices, however: indices of course do not have side-effects in terms of changing the results of queries etc (that's why it's ok for DBAs to add custom indices to VBR database). But their effects on DB performance is not 100% positive: indices take up space and slow down inserts, updates and deletes.
In general it's not a great idea to create all the indices that SQL Server suggests for every query; having, say, a hundred indices on a table will just crush write performance.

That's why we analyze impact of the index before creating it, using tools like Query Store to figure out if a query that has a missing index is actually running often enough and has noticeable impact on overall DB performance.

So before considering adding those indices to the product, I'd have to know which queries were affected, especially seeing that both suggested indices have identical INCLUDED columns - I'd rather try to create one index to satisfy both queries (or just create the first one, because 24% improvement in query cost is often not enough to justify a new index).

Cheers, Alexander.
Gostev
Chief Product Officer
Posts: 31460
Liked: 6648 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Veeam B&R: Missing SQL indices

Post by Gostev » 3 people like this post

broth-itk wrote: Jul 22, 2021 3:47 amDear Veeam Developers
There you go, looks like you accidentally summoned one who's actually in charge of our SQL development :wink:
Post Reply

Who is online

Users browsing this forum: Google [Bot] and 240 guests