Index on MSDB

I have a query that looks at the backup tables for any databases on the
server that have not been backed up in the last n days. This server has been
running for 3-4 years doing nightly backups and transaction log backups all
during the day. I couldn't find a system procedure or any of the schema
views I needed so I and running these against the system tables
(specifically msdb..BackUpSet, msdb..BackupMediaFamily, and
master..sysdatabases). The 2 MSDB tables I'm using have large numbers of
rows & the query is doing clustered index scans.
My question is what is the danger of adding my own indexes on these tables
to make the query more efficient?
They will be non-clustered indexes on a single column. I've tried it on one
of our development servers with a HUGE improvement in the query plan &
performance. I know that a service pack or hotfix may delete the indexes or
prevent the sp from installing. I'll drop the index before any sp / hotfix
is installed in case the columns are no longer supported.
(as a follow-up to this, I have a script that needs the indexes to mimic the
procedure sp_delete_backuphistory. I have so much old useless data in the
tables from the last 3 years, this system procedure is useless the way it's
currently written using a cursor.)
The machines are:
WIN2000 sp4
SQL2K sp3a Standard with 2GB RAM
Thanks!If you are very sure about the impact of the new non clustered index that yo
u
are planning to add and tested it well, u can go ahead and add it. I sugges
t
that you take a Backup of your MSDB database before adding the index and be
prepared to drop the index if you have to approach Microsoft PSS for any
technical support (they may call this as un supported scenario). Again
monitor your Server for any issues after you add the index at least for
couple of days.
Thanks
"cw" wrote:

> I have a query that looks at the backup tables for any databases on the
> server that have not been backed up in the last n days. This server has be
en
> running for 3-4 years doing nightly backups and transaction log backups al
l
> during the day. I couldn't find a system procedure or any of the schema
> views I needed so I and running these against the system tables
> (specifically msdb..BackUpSet, msdb..BackupMediaFamily, and
> master..sysdatabases). The 2 MSDB tables I'm using have large numbers of
> rows & the query is doing clustered index scans.
> My question is what is the danger of adding my own indexes on these table
s
> to make the query more efficient?
> They will be non-clustered indexes on a single column. I've tried it on on
e
> of our development servers with a HUGE improvement in the query plan &
> performance. I know that a service pack or hotfix may delete the indexes
or
> prevent the sp from installing. I'll drop the index before any sp / hotfix
> is installed in case the columns are no longer supported.
> (as a follow-up to this, I have a script that needs the indexes to mimic t
he
> procedure sp_delete_backuphistory. I have so much old useless data in the
> tables from the last 3 years, this system procedure is useless the way it'
s
> currently written using a cursor.)
> The machines are:
> WIN2000 sp4
> SQL2K sp3a Standard with 2GB RAM
> Thanks!
>
>sql