Here's something I found a few months ago, and it seems to come in
handy pretty often, so I figured I'd share.
Disclaimer: you are modifying a SYSTEM TABLE. This may not work in
the next version of SQL Server.
When you right click on a Database in the Enterprise Manager and
choose Properties, in order to show the last backup time, it has to go
through MSDB and find the last backup date for the database. Unless
you're carefully pruning that (via sp_delete_backuphistory), it can
get slow. Eventually, getting database properties can literally take
minutes, which locks up the Enterprise Manager for that period of
time.
After watching via the profiler, I added two indexes in MSDB:
in backupset: 1 index, on "media_set_id" and "backup_finish_date"
in restorefile: 1 index, on "restore_history_id"
(both are nonclustered, though you probably could cluster the
restorefile index)
Now, getting properties takes about 4 seconds.
Hope this helps.
