Maintenance plan vs Ola Hallengren

SQL Server è dotato di un Maintenance Plan Wizard. È grafico, è integrato, è tentatore. Ed è anche ampiamente considerato dannoso.

Questa lezione parla del perché la community raccomanda universalmente al suo posto gli script di manutenzione di Ola Hallengren, di cosa fanno gli script, di come installarli e dello schedule di default sensato che puoi copiare e incollare.

Ola regala il suo lavoro gratis da 17 anni, e su praticamente ogni SQL Server di produzione su cui ho lavorato si è finiti per usare i suoi script. Questa è l’unica lezione in cui ti dico di andare su un sito di terze parti e di fidarti di loro più che dello strumento integrato di Microsoft.

Perché il piano integrato è cattivo

I default del maintenance plan wizard:

• Rebuild di ogni indice, indipendentemente dalla frammentazione.
• Update delle statistiche su ogni tabella, indipendentemente dal tasso di modifica.
• Usa default di campionamento che vanno bene per tabelle piccole, sbagliati per tabelle grandi.
• Il task Shrink Database (abilitato di default sulle versioni più vecchie) riframmenta tutto.
• Nessuna logica selettiva; fa ogni task su ogni database alla stessa maniera.

Risultato netto: un job long-running, CPU-intensivo che fa un sacco di lavoro inutile e occasionalmente fa lavoro dannoso. Su un server di produzione di taglia media, il piano di manutenzione notturno può girare per quattro ore facendo cose che richiederebbero dieci minuti di sforzo mirato.

Puoi tunare il maintenance plan wizard, ma le manopole di tuning sono limitate e il piano risultante è comunque un blob XML pesante difficile da leggere e da mettere sotto version control.

Cosa fanno gli script di Ola

Tre script principali:

1. DatabaseBackup — backup intelligenti. Gestisce FULL, DIFF, LOG. Supporta backup su disco, network share, Azure Blob, S3. Compressi, con checksum, verificati.
2. IndexOptimize — manutenzione intelligente di indici e statistiche. Fa rebuild solo degli indici frammentati, fa update solo delle statistiche che ne hanno bisogno, con default sensati per le soglie.
3. DatabaseIntegrityCheck — esegue DBCC CHECKDB su tutti i database.

Più alcune procedure di supporto (CommandExecute, CommandLog).

Tutte e tre sono stored procedure. Le schedi via Agent. Accettano parametri, loggano la propria storia e si comportano bene con qualsiasi cosa gli butti contro.

Installazione

# Scarica MaintenanceSolution.sql da ola.hallengren.com
# Eseguilo contro master in SSMS

Eseguire MaintenanceSolution.sql crea:

• Una tabella dbo.CommandLog per il logging.
• Le stored procedure dbo.CommandExecute, dbo.DatabaseBackup, dbo.IndexOptimize, dbo.DatabaseIntegrityCheck.
• Opzionalmente (configurabile), i job di SQL Agent per ognuna.

Imposta il flag @CreateJobs = 'Y' prima di eseguire e ottieni i job già pronti. Aggiusti gli schedule dopo.

Lo schedule canonico

Ecco cosa esegue Runehold:

Giornaliero

00:00  Full backup di master
00:05  Full backup di msdb
00:10  Full backup di model
00:30  Full backup di Runehold
02:00  IndexOptimize (solo Runehold, con @UpdateStatistics = 'ALL')

Ogni 30 minuti, 06:00–22:00

Backup del transaction log di Runehold

Settimanale (domenica)

01:00  DatabaseIntegrityCheck su tutti i database

Mensile

03:00 prima domenica: CHECKDB completo con EXTENDED_LOGICAL_CHECKS (data purity).

Tutto qua. Sei job dell’Agent coprono l’intera storia di manutenzione.

Parametri di IndexOptimize, quelli importanti

EXEC master.dbo.IndexOptimize
    @Databases         = 'USER_DATABASES',
    @FragmentationLow  = NULL,          -- salta bassa frammentazione
    @FragmentationMedium = 'INDEX_REORGANIZE',
    @FragmentationHigh   = 'INDEX_REBUILD_ONLINE',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @UpdateStatistics    = 'ALL',
    @OnlyModifiedStatistics = 'Y',
    @LogToTable          = 'Y';

• @FragmentationLow = NULL — salta tutto sotto il 5%. Risparmia un sacco di lavoro.
• @FragmentationMedium = 'INDEX_REORGANIZE' — reorganize tra il 5 e il 30%.
• @FragmentationHigh = 'INDEX_REBUILD_ONLINE' — rebuild sopra il 30%, online (Enterprise) oppure offline (Standard).
• @UpdateStatistics = 'ALL' — aggiorna le statistiche su indici e colonne.
• @OnlyModifiedStatistics = 'Y' — salta le statistiche dove non sono cambiate righe.
• @LogToTable = 'Y' — logga gli esiti su dbo.CommandLog così puoi interrogare cosa è successo.

Su Runehold gira in circa 35 minuti a settimana. Il maintenance plan integrato impiegava quattro ore per un risultato peggiore.

Parametri di DatabaseBackup

EXEC master.dbo.DatabaseBackup
    @Databases         = 'USER_DATABASES',
    @Directory         = 'D:\Backups',
    @BackupType        = 'FULL',
    @Verify            = 'Y',
    @Compress          = 'Y',
    @CheckSum          = 'Y',
    @CleanupTime       = 336,             -- tieni i backup per 14 giorni (14 * 24)
    @LogToTable        = 'Y';

I backup di log chiamano la stessa procedura con @BackupType = 'LOG'. I DIFF con @BackupType = 'DIFF'.

Backup su Azure Blob:

EXEC master.dbo.DatabaseBackup
    @Databases  = 'USER_DATABASES',
    @URL        = 'https://runehold.blob.core.windows.net/backups',
    @Credential = 'AzureStorageCredential',
    @BackupType = 'FULL',
    @Compress   = 'Y',
    @CheckSum   = 'Y';

Cambia una riga; tutto il resto resta uguale.

DatabaseIntegrityCheck

EXEC master.dbo.DatabaseIntegrityCheck
    @Databases = 'ALL_DATABASES',
    @CheckCommands = 'CHECKDB',
    @LogToTable = 'Y';

DBCC CHECKDB è lento sui database grandi — su database di taglia TB può prendere ore. Eseguilo settimanalmente in una finestra di manutenzione. Spezzarlo in controlli a livello di filegroup oppure eseguirlo su una copia ripristinata è un’opzione per sistemi molto grandi.

Logga gli esiti su una tabella

Siccome gli script di Ola loggano su dbo.CommandLog, hai un audit trail completo:

SELECT TOP (100) *
FROM master.dbo.CommandLog
ORDER BY StartTime DESC;

Ogni comando, ogni database, ogni durata, ogni errore. Perfetto per i post-mortem e per la compliance.

Per le aziende con compliance pesante (e Runehold sotto DORA, se applicabile): punta un alert di monitoraggio su CommandLog.ErrorMessage IS NOT NULL così qualsiasi fallimento di manutenzione viene notato subito.

Altri strumenti che vale la pena conoscere

• Brent Ozar’s First Responder Kit (lezioni 37-40) — diagnostica, non manutenzione. Focus diverso.
• dbatools — un modulo PowerShell che complementa gli script di Ola. Utile per le cose che Ola non copre (configurazione a livello istanza, copiare database tra server, eccetera).
• SQL Server Managed Backup to Azure — automatizza i backup su Azure Blob senza gli script di Ola. Più semplice ma meno flessibile.

Per un setup di produzione senza fronzoli: script di Ola + dbatools + l’interfaccia Query Store di SSMS. Quel trio gestisce il 95% del toolkit del DBA.

Prova questo sulla tua macchina

-- 1. Scarica MaintenanceSolution.sql da https://ola.hallengren.com
-- 2. Aprilo in SSMS.
-- 3. Prima di eseguire, imposta il parametro @CreateJobs a 'Y'.
-- 4. Eseguilo contro il database master.
-- 5. Controlla che i job siano stati creati:

SELECT name, enabled FROM msdb.dbo.sysjobs
WHERE name LIKE '%IndexOptimize%'
   OR name LIKE '%DatabaseBackup%'
   OR name LIKE '%DatabaseIntegrityCheck%';

-- 6. Prova a eseguire IndexOptimize manualmente (prima un DB piccolo!)
EXEC master.dbo.IndexOptimize
    @Databases = 'Runehold',
    @FragmentationLow  = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE',
    @LogToTable = 'Y';

-- 7. Controlla cosa ha fatto
SELECT TOP (20) * FROM master.dbo.CommandLog ORDER BY StartTime DESC;

Una volta che l’hai eseguito una volta e hai verificato l’output di CommandLog, configura i job dell’Agent per girare a schedule e dimenticati in larga parte della manutenzione. Quello è l’obiettivo. Noioso è bello.

Prossima lezione: DMV che ogni DBA dovrebbe sapere a memoria. Le query che esegui su un server freddo quando qualcuno ti dà l’accesso root e dice “scopri cosa c’è che non va.”