I mitt förra blogginlägg skrev jag bland annat om spDeleteBackupRestoreHistory, som är en Stored Procedure som rensar backup- och restore-historik i MSDB. Den gick VERKLIGEN långsamt i sitt ursprungsutförande. Därför började jag kolla indexeringen i MSDB och upptäckte att den är minst sagt sparsam. Det kan säkert göras massor för att snabba upp både det ena och det andra i MSDB. Men jag hade två specifika problem:

1) Tabellen backupset har ett enda index – backupsetuuid. Men det finns en Foreign Key-constraint som refererar kolumnen media_set_id till tabellen backupmediaset. I SPn spDeleteBackupRestoreHistory görs en sökning i just den kolumnen, vilket innebär en table-scan. Eftersom den sökningen görs väldigt många gånger innebär det väldigt många tablescans och därmed väldigt dålig prestanda.

2) I spDeleteBackupRestoreHistory läses kolumnerna backup_set_id och media_set_id i tabellen backupset. Det finns ett index (primärnyckel, klustrad) på backup_set_id, men inget index på media_set_id.

För att slå två flugor i en smäll skapade jag ett sammansatt index över kolumnerna media_set_id och backup_set_id. Då fick jag dels sökbarhet på media_set_id vilket gör att table_scan undviks, dels fick jag ett täckande index över de två kolumner i tabellen backupset som spDeleteBackupRestoreHistory använder.

Alltså:

CREATE 
  INDEX [backupset_mediaset_id]
ON
  [dbo].[backupset]([media_set_id],
  [backup_set_id])

Jag testkörde genom att:

• Först göra exec spDeleteBackupRestoreHistory 100 utan att mäta tiden.
• Sedan gjorde jag exec spDeleteBackupRestoreHistory 90 med tidsmätning innan jag skapat mitt nya index. Resultatet: 12 minuters körning.
• Sedan skapade jag indexet och gjorde exec spDeleteBackupRestoreHistory 90. Resultat: 5 sekunders körning.

Sensmoral: Ha alltid index på kolumner som refereras i främmande nycklar. Försök att skapa täckande index.