With CLUE as (Select * from Random_Thought ORDER BY Common_Sense DESC)

SQL Server thoughts, observations, and comments
posts - 52, comments - 79, trackbacks - 0

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                                    *

*    Modifications:                                    *

*                                                *

*    01-01-2001                                        *

*        Sample Entry                                *

*                                                *

*                                                *

************************************************************************/

use msdb

go

--backupset

Create index IX_backupset_backup_set_id on backupset(backup_set_id)

go

Create index IX_backupset_backup_set_uuid on backupset(backup_set_uuid)

go

Create index IX_backupset_media_set_id on backupset(media_set_id)

go

Create index IX_backupset_backup_finish_date on backupset(backup_finish_date)

go

Create index IX_backupset_backup_start_date on backupset(backup_start_date)

go

--backupmediaset

Create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id)

go

--backupfile

Create index IX_backupfile_backup_set_id on backupfile(backup_set_id)

go

--backupmediafamily

Create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id)

go

--restorehistory

Create index IX_restorehistory_restore_history_id on restorehistory(restore_history_id)

go

Create index IX_restorehistory_backup_set_id on restorehistory(backup_set_id)

go

--restorefile

Create index IX_restorefile_restore_history_id on restorefile(restore_history_id)

go

--restorefilegroup

Create index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id)

go

 

/************************************************************************

*    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

go

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.

 UPDATE:

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. 

Print | posted on Monday, January 21, 2008 11:30 AM |

Feedback

Gravatar

# re: MSDB Performance Tuning

Hi Geoff
I don't think we ca supply GETADTE() as a parameter to sp_delete_backuphistory
1/22/2008 2:34 AM | Uri Dimant
Gravatar

# 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
go

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;
go

declare @oldest_date smalldatetime;
set @oldest_date = dateadd(dd, -30,getdate())
EXEC sp_delete_backuphistory @oldest_date;
1/23/2008 10:58 AM | Sunny
Gravatar

# 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.
1/23/2008 11:35 AM | Geoff N. Hiten
Gravatar

# re: MSDB Performance Tuning

Wow what a difference this made!

THANKS
3/13/2008 11:34 AM | Georg Zimmer
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET