Database Drop takes a long time when deleting backup history option is selected in SQL Server

Here's a workaround to speed up database drop. When there are a long history of backup in MSDB database, dropping a database with "Delete backup history" option could take a while.

Update statistics on MSDB tables and create an index for backupset table for [database_name] column. 

/* Update statistics in [msdb] database */
   
USE msdb; 
GO
UPDATE STATISTICS backupfile; 
GO
UPDATE STATISTICS backupmediafamily; 
GO
UPDATE STATISTICS backupmediaset; 
GO
UPDATE STATISTICS backupset; 
GO
UPDATE STATISTICS restorefile; 
GO
UPDATE STATISTICS restorefilegroup; 
GO
UPDATE STATISTICS restorehistory; 
GO


/* Create an index on [backupset] table in [msdb] database for [database_name] column */

Create index IX_backupset_database_name on backupset(database_name); 
GO