First of all I want to thank Rob and Lockwood for helping me with XQuery to find "missingindexes" in the queryplans. It is so cool that you can ask a question and the next minute you get an excellent answer.
Next I want to ask all of you if you would even consider adding an index on a system table in MSDB? It goes against my gut feeling and my policies to do any modifications in system databases, but in this I am uncertain. We are using redgate backup and I think the GUI is polling information about backup status very frequent, and the query used is not optimized ( or actually it is the other way around, the table in msdb is not optimized ). This is what the execution plan tells me ( a lot of times ) and the estimate cost is ~6.5 and the impact is 63%. It this was a query once a day, i wouldn't bother but this is a frequent used query.
<MissingIndexes>
<MissingIndexGroup Impact="63.5054">
<MissingIndex Database="[msdb]" Schema="[dbo]" Table="[backupset]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[database_name]" ColumnId="34" />
</ColumnGroup>
<ColumnGroup Usage="INCLUDE">
<Column Name="[backup_set_id]" ColumnId="1" />
<Column Name="[backup_set_uuid]" ColumnId="2" />
<Column Name="[media_set_id]" ColumnId="3" />
<Column Name="[first_lsn]" ColumnId="21" />
<Column Name="[last_lsn]" ColumnId="22" />
<Column Name="[checkpoint_lsn]" ColumnId="23" />
<Column Name="[database_backup_lsn]" ColumnId="24" />
<Column Name="[type]" ColumnId="28" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>
