question

Håkan Winther avatar image
Håkan Winther asked

Missing index in MSDB...

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>
sql-server-2005indexingsystem-databasesred-gate-backup
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered

Håkan,

I personally don't add indexes to msdb, but I have seen it done in certain environments to speed up the performance for SSMS when browsing system tables i.e. backup/restore info.

If you have regular clean up jobs to trim the data, it may not be needed... But this is just my opinion.

I would test this 1st to see if you get any noticeable improvements

one min...

Just found a post that seems to cover in more detail what you are looking for.

http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Steinar avatar image
Steinar answered

I think it is worth trying. In the worst case you can always remove the index. I have actually run into problems with adding indexes, but only once that I can remember in 15 years. And that was a query that relied on data beeing returned in a specific order, even though ORDER BY was not specified. And that order changed when I added an index, so I had to remove it fast :-)

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

I have only ever added indexes to replication tables (if they count as 'system' tables?) and its been benficial. I think I would agree with other comments along the lines that if you are watching closely then you can easily remove the index if it causes any problems so go ahead and give it a whirl.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.