Tiny query used to be sub-second speed, now takes 60+ seconds Ok, I've got a monitoring process that runs the following query to make sure the databases are backing up regularly.  This process used to take 1-2 seconds for connection, query execution, and everything.  Now the process is timeing out and if I run the query in SMSS it takes over a minute.

SELECT D.name AS [database_name], D.recovery_model, BS1.last_backup, BS1.last_duration
FROM sys.databases D
LEFT JOIN (
    SELECT BS.[database_name],
    DATEDIFF(HH,MAX(BS.backup_finish_date), GETDATE()) AS last_backup,
    DATEDIFF(MI,MAX(BS.backup_start_date),MAX(BS.backup_finish_date)) AS last_duration
    FROM msdb.dbo.backupset BS
    WHERE BS.type = 'L'
    GROUP BY BS.[database_name]
) BS1 ON D.name = BS1.[database_name]
ORDER BY D.[name]


Now the sub-select by itself still takes less than a second and returns about 20 rows (the msdb.dbo.backupset table has ~500K rows).  The sys.databases table has about only about 30 rows.  So I'm lost as to why this query now takes a minute to run.

The problem started after the C: drive was full for a few minutes so I thought maybe an index for corrupted. So I've rebuilt all the indexes (including PK) on the msdb.dbo.backupset table.  I can't find the sys.databases table so I'm guessing it's a "virtual table" or something?  I looked at the estimated execution plan but that stuff is mostly over my head.

Any suggestions?
the sub-select by itself still takes less than a second and returns
  about 20 rows ... Any suggestions?


In that case you may as well materialize it into a #temp table and join onto that instead.

SELECT BS.[database_name],
       DATEDIFF(HH, MAX(BS.backup_finish_date), GETDATE())                 AS last_backup,
       DATEDIFF(MI, MAX(BS.backup_start_date), MAX(BS.backup_finish_date)) AS last_duration
INTO   #BS1
FROM   msdb.dbo.backupset BS
WHERE  BS.type = 'L'
GROUP  BY BS.[database_name];

SELECT D.name AS [database_name],
       D.recovery_model,
       BS1.last_backup,
       BS1.last_duration
FROM   sys.databases D
       LEFT JOIN #BS1 BS1
         ON D.name = BS1.[database_name]
ORDER  BY D.[name];

DROP TABLE #BS1 ;