Geoff N. Hiten Blog

SQL Server thoughts, observations, and comments

MSDB Performance Tuning

Backup and Restore is arguably the most important task a DBA can do, but sometimes it is very frustrating to manage these elements. Some common problems include very slow UI interaction, both for SQL Enterprise Manager and for SQL Server Management Studio, mysterious timeouts, failure to delete old backup files, and other intermittent failures. Most of these failures can be traced back to the same underlying problem and thus have the same solution.

The MSDB database contains a series of system tables that track everything having to do with backups on that server. One of the limitations of vendor testing is that no single test platform ever runs for anything like the lifetime of a typical production SQL Server. Because of this, no test server accumulates the history in the MSDB tables that real-world systems do. Since SQL uses its standard table storage and retrieval mechanisms to store this information, scalability should not be a problem. Wrong. Just as with user tables, system tables will perform poorly if they are not correctly indexed. Now for the real kicker:

There are no indexes on the MSDB backup tracking tables.

That is right. Not a single index. No Referential Integrity, nothing.

In SQL 2000, this is even worse as there is no MSDB cleanout component in the maintenance plan wizard. SQL 2005 has it in SP2 and higher releases.

Fixing this problem is actually fairly easy. Here is a script to create indexes on the MSDB backup tables. This handles the basic performance issue, however it is still a good idea to clean out the old backup information. Unless you have an amazing archive system, tracking a transaction log backup from three years ago will not do you much good.



    Title:    msdb index creation                        

    Author:    Geoff N. Hiten                            

    Purpose: Index msdb database                            

    Date:    12/12/2005                                    




        Sample Entry                                




use msdb



Create index IX_backupset_backup_set_id on backupset(backup_set_id)


Create index IX_backupset_backup_set_uuid on backupset(backup_set_uuid)


Create index IX_backupset_media_set_id on backupset(media_set_id)


Create index IX_backupset_backup_finish_date on backupset(backup_finish_date)


Create index IX_backupset_backup_start_date on backupset(backup_start_date)



Create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id)



Create index IX_backupfile_backup_set_id on backupfile(backup_set_id)



Create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id)



Create index IX_restorehistory_restore_history_id on restorehistory(restore_history_id)


Create index IX_restorehistory_backup_set_id on restorehistory(backup_set_id)



Create index IX_restorefile_restore_history_id on restorefile(restore_history_id)



Create index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id)




    End Script                                        



Notice that these are performance indexes only; they do not enforce any constraints.

For SQL 2000 users, there is one more bit of work to do. You need to schedule a job to clean out MSDB backup history on a regular basis. SQL 2005 users can set the maintenance plans to handle this. The command is:

use msdb


declare @OldestDate datetime

set @OldestDate = getdate() -120


exec sp_delete_backuphistory @OldestDate

If you try and run this command on a large set of data without indexing, it may take hours to days to complete, meanwhile it will block backups while it locks the tables.

120 is the number of days to retain backup data. This number should be slightly longer than your maximum live backup retention time. Ignore any backups archived to tape since those likely will get restored through an alternate path anyway.


As Uri pointed out, we can't do date math in the procedure call, so I wrote a minor modification to create a parameter and feed it to the procedure.  Good Catch.  Thanks. 

Legacy Comments

Uri Dimant
re: MSDB Performance Tuning
Hi Geoff
I don't think we ca supply GETADTE() as a parameter to sp_delete_backuphistory

re: MSDB Performance Tuning
-- To delete the backup history one day at a time if you have the backup history are years' old

use msdb

set nocount on
declare @oldest_date smalldatetime;
select @oldest_date = min(backup_finish_date) from backupset with (nolock)
set @oldest_date = dateadd(dd, +1 ,@oldest_date)
EXEC sp_delete_backuphistory @oldest_date;

-- If you'd like to keep the backup history for 30 days
use msdb;

declare @oldest_date smalldatetime;
set @oldest_date = dateadd(dd, -30,getdate())
EXEC sp_delete_backuphistory @oldest_date;

Geoff N. Hiten
re: MSDB Performance Tuning
I have used "nibbler" apps like this before as well. Without indexing, this is the only way to get the contents trimmed down to reasonable size.

Good addition.

Georg Zimmer
re: MSDB Performance Tuning
Wow what a difference this made!