I thought I would post this quick for the community benefit. Here are some indexes that I create on all my SQL Server machines to speed up some default purging routines. The default indexes that come in msdb just don’t cut it.
-- To speed up execution of sp_delete_backuphistory IF NOT EXISTS(SELECT * FROM msdb.dbo.sysindexes WHERE id=object_id('msdb.dbo.backupset') AND name = 'ixFinDate') CREATE nonclustered INDEX [ixFinDate] ON msdb.dbo.backupset (backup_finish_date ASC) go IF NOT EXISTS(SELECT * FROM msdb.dbo.sysindexes WHERE id=object_id('msdb.dbo.backupset') AND name = 'ixMedia') CREATE nonclustered INDEX [ixMedia] ON msdb.dbo.backupset (media_set_id ASC) go IF NOT EXISTS(SELECT * FROM msdb.dbo.sysindexes WHERE id=object_id('msdb.dbo.restorefile') AND name = 'ixHistory') CREATE nonclustered INDEX [ixHistory] ON msdb.dbo.restorefile (restore_history_id ASC) go IF NOT EXISTS(SELECT * FROM msdb.dbo.sysindexes WHERE id=object_id('msdb.dbo.restorefilegroup') AND name = 'ixHistory') CREATE nonclustered INDEX [ixHistory] ON msdb.dbo.restorefilegroup (restore_history_id ASC) go -- To speed up deletes from sysjobhistory IF NOT EXISTS(SELECT * FROM msdb.dbo.sysindexes WHERE id=object_id('msdb.dbo.sysjobhistory') AND name = 'ixRun') CREATE nonclustered INDEX [ixRun] ON msdb.dbo.sysjobhistory (run_date ASC, run_time ASC) go
{ 4 comments… read them below or add one }
Hi, Eric.
What about the BACKUP_SET_ID column on the BACKUPSET table? When searching around for speedups for this godforsaken stored procedure, this column is mentioned a lot. You, however, seem to omit it, but at the same time you seem to have a much more optimized set of indexes (esp. the backup_finish_date).
Did you deem an index on this ID as unnecessary?
Thanks.
Oh, also for:
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysindexes WHERE id=object_id(‘msdb.dbo.backupset’) AND name = ‘ixFinDate’)
CREATE nonclustered INDEX [ixMedia] ON msdb.dbo.backupset (media_set_id ASC)
go
The EXISTS check is incorrect; it is checking for the wrong name.
I never really spent too much time looking into it. With the these indexes I got the results that I was looking for.
Thanks for pointing it out. I will update the post.