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.

Jag har upptäckt att MSDB i en server jag hanterar är ALLDELES för välfylld. Att droppa en databas har tagit flera timmar om jag valt att radera backup-historik samtidigt. Därför googlade jag och fann en användbar SP: spDeleteBackupRestoreHistory hos DBA and SysAdmin workd. Det är en procedur som rensar backuphistorik ändre än X dagar. Mycket användbar SP som redan hamnat som ett veckojobb på en testmaskin och som jag körde över helgen på en produktionsmaskin, för att den skulle rensa bort all historik som är äldre än två år (jag hade sex år gammal historik sparad).

Det hade gått alldeles utmärkt om inte nedanstående “trevliga” lilla felmeddelande uppenbarat sig i Query Analyzer strax efter att jag lämnat datorn att jobba över helgen:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).Server: Msg 11, Level 16, State 1, Line 0
Generalnetwork error. Check your network documentation.Connection Broken.

Jobbigt tänkte jag, men det är säkert något tillfälligt – någon som i helgen ryckte och drog i någon sladd. Så jag testade att köra igång den igen, och fick direkt samma fel. Jag bytte databas och testade lite olika queries utan att få något felmeddelande. Jag gick tillbaka till MSDB och körde igen, och fick samma fel. Andra queries gick utmärkt att köra i MSDB, det var bara spDeleteBackupRestoreHistory som ballade ur.

Jag loggade in direkt på servern och öppnade en anslutning mot Localhost och körde spDeleteBackupRestoreHistory och fick åter samma fel. Ganska säker på att det verkligen inte är ett nätverksproblem körde jag DBCC CHECKDB på databasen och upptäckte ett fel på indexet backupsetuuid i tabellen backupset.

Nu när jag visste vad felet var kunde jag åtgärda det genom att bygga om indexet. Tabellen innehåller ungefär en miljon rader, och att bygga om indexet tog fem sekunder. “Förarbetet” – det vill säga att hitta vad som faktiskt var fel – tog däremot ungefär en timme. Nu är felet åtgärdat, och spDeleteBackupRestoreHistory är igång igen. Återstår att söka efter orsaken till att ett index gått sönder från början.

CREATE
  INDEX [backupsetuuid]
ON backupset ([backup_set_uuid])
  WITH DROP_EXISTING