/*
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
*/
/*
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
*/
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
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
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).
