SQL Server nelle sue versioni a pagamento (quindi sulla Express no) mette a disposizione tramite Sql Server Management Studio (SSMS) lo strumento dei piani di manutenzione, che consentono di eseguire operazioni di manutenzione sui database SQL quali, ad esempio, il controllo integrità, l'aggiornamento delle statistiche, la ricostruzione/riorganizzazione degli indici e, eventualmente, i backup.

Per i backup si consiglia comunque di effettuare processi di backup distinti, senza far uso dei piani di manutenzione.

La procedura sottostante da indicazioni e consigli sui piani di manutenzione. Ma sono solo indicazioni! Le impostazioni potrebbero differire da cliente e cliente, in presenza di personalizzazioni e della versione di sql server in uso.

Consigliamo quindi prima di applicare qualsiasi impostazione di interfacciarsi con il DBA del cliente (come anche per i backup dei database).

Partiamo con una domanda: come sappiamo bene è fondamentale fare i backup dei database (obbligatorio), ma è altrettanto fondamentale impostare dei piani di manutenzione che eseguono operazioni di controllo integrità, aggiornamento statistiche e ricostruzione/riorganizzazione indici?

E' molto più importante di quanto spesso si pensi!

La risposta breve è i piani di manutenzione non sono obbligatori come i backup, ma sono comunque fondamentali per garantire performance, stabilità e prevenire corruzioni.

Vediamo perché e con che priorità.

Quanto sono fondamentali le varie attività di manutenzione?

Di seguito una classificazione pratica basata su quanto siano realmente critiche.

1. CheckDB (controllo integrità database) — CRITICO al pari dei backup

DBCC CHECKDB è assolutamente essenziale.
È l'unico strumento che rileva la corruzione fisica/logica del database.

Perché è critico?

• La corruzione può propagarsi senza sintomi iniziali
• Il backup può già contenere corruzione, se non fai CheckDB regolarmente
• L'unica via d'uscita a volte è RESTORE... ma se i backup sono corrotti?

Best practice: eseguirlo almeno 1 volta a settimana (anche giornaliero se DB piccolo).

2. Aggiornamento statistiche — ESSENZIALE per le performance

SQL Server aggiorna automaticamente le statistiche... ma non abbastanza e non sempre accuratamente.

Statistiche obsolete ➝ pessimi piani di esecuzione ➝ query lente.

Best practice classica:

• Aggiornamento statistiche manuale giornaliero o settimanale (in base al carico di scrittura)
• Usare WITH FULLSCAN su tabelle critiche se servono piani di qualità

3. Manutenzione indici — IMPORTANTE, ma meno critica dei primi due

Riorganizzazione / ricostruzione indici è importante, ma non è "salvavita".

Cosa comporta non farla?

• Crescita degli indici
• Frammentazione elevata ➝ accesso alle pagine meno efficiente
• Piani subottimali in alcuni casi

Però:

• SQL Server moderno (dal 2012+) gestisce piuttosto bene indici frammentati
• Il beneficio è molto meno critico rispetto a CheckDB e statistiche

Best practice moderna:

• Riorganizza solo se frammentazione tra 10% e 30%
• Ricostruisci solo se >30%
• Evitare la ricostruzione eccessiva "per sport"

Quindi quali sono veramente fondamentali?

Ordine di importanza reale (mantenuto da Microsoft e dalla community SQL):

1. Backup affidabili
2. CheckDB regolare
3. Aggiornamento statistiche
4. Manutenzione indici (smart, non cieca)

Molti DBA fanno troppa manutenzione sugli indici e troppo poca su CheckDB e statistiche.

Conclusione

Sì, oltre ai backup è davvero fondamentale avere un piano di manutenzione, almeno con:

Necessari

• DBCC CHECKDB
• Aggiornamento statistiche

Molto consigliati

• Rebuild/Reorganize degli indici (con soglie intelligenti)

PIANI DI MANUTENZIONE PROGRAMMATA DALLO STRUMENTO DI AMMINISTRAZIONE DI BUSINESS (busadmintools.exe)

Dal tab Backup/Restore di Busadmintools è possibile richiamare, con il pulsante "Piani man. Program." l'accesso agli attuali piani di manutenzione inseriti da Business, per inserirli o eliminarli (non si possono modificare).

La funzionalità replica parzialmente la gestione dei piani di manutenzione da parte di SSMS (in quanto non sono presenti tutti i possibili filtri presenti nel Wizard dei Piani di Manutenzione di SSMS), in particolare a:

• Controllo integrità database
• Aggiornamento Statistiche
• Riorganizzazione Indici
• Ricostruzione Indici

Una volta connessi all'istanza di Sql Server sarà possibile avere una maschera di selezione delle attività, data\ora\periodicità della stessa, che verrà inserita nei piani di manutenzione dell'Agent di SQL Server sotto la tipologia Script Transact-SQL (T-SQL).

Proviamo quindi ad impostare un piano di manutenzione per effettuare il controllo integrità database da questo strumento come avevamo fato prima da SSMS.

Dalla maschera in oggetto premere Nuovo.

Poi impostiamo un nome al piano di manutenzione, selezioniamo di eseguire l'attività di controllo integrità database, i database da elaborare ed impostiamo una pianificazione.

Una volta salvato uscirà il seguente messaggio.

Premuto Ok troveremo nella lista il piano di manutenzione appena impostato.

Piano di manutenzione che troviamo ovviamente anche in SSMS sotto a SQL Agent\Processi.

Se andiamo a visualizzare le proprietà del processo in modifica.

Troveremo il dettaglio del comando T-SQL che ha impostato Business per un controllo completo.

Dettaglio del comando T-SQL impostato dalle attività di manutenzione presenti nel Busadmintools:

• Controllo integrità database

DBCC CHECKDB

• Aggiornamento Statistiche

EXEC sp_updatestats

• Riorganizzazione Indici

DECLARE @NomeTabella sysname
DECLARE ListaTabelle CURSOR FOR SELECT name FROM sys.objects WHERE type = 'U'
OPEN ListaTabelle
FETCH NEXT FROM Listatabelle INTO @NomeTabella
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC('ALTER INDEX ALL ON ['+ @NomeTabella + '] REORGANIZE')
FETCH NEXT FROM ListaTabelle INTO @NomeTabella
END
CLOSE ListaTabelle
DEALLOCATE ListaTabelle

• Ricostruzione Indici

DECLARE @NomeTabella sysname
DECLARE ListaTabelle CURSOR FOR SELECT name FROM sys.objects WHERE type = 'U'
OPEN ListaTabelle
FETCH NEXT FROM Listatabelle INTO @NomeTabella
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC('ALTER INDEX ALL ON ['+ @NomeTabella + '] REBUILD')
FETCH NEXT FROM Listatabelle INTO @NomeTabella
END
CLOSE ListaTabelle
DEALLOCATE ListaTabelle

NB: le attività di manutenzione impostate dal busadmintools non applicano nessun filtro particolare quindi può andare bene per database 'piccoli', situazioni semplici o per operazioni volutamente complete.

Dettaglio per la creazione di un piano di manutenzione da SSMS per effettuare il controllo integrità database

Ora vediamo come replicare la stessa configurazione di controllo integrita database effettuata prima tramite busadmintools, tramite i piani di manutenzione di SSMS.

Entrare in SQL Server Management Studio e nella cartella Gestione scegliere la voce di menu Creazione guidata piano di manutenzione.

Premere Avanti.

Nella pagina di Selezione delle proprietà del piano indicare un Nome del piano e scegliere Singola pianificazione per l'intero piano.

Quindi si preme su Pianificazione, Cambia per impostare la frequenza del processo di pianificazione. Impostare l'esecuzione in un orario dove non sono presenti altri lavori schedulati o altre attività interattive su Business e dopo il backup completo.

Nella pagina Seleziona attività di manutenzione scegliere la voce come sottoindicato.

Nella pagina Seleziona ordine attività di manutenzione confermare visto che stiamo eseguendo la sola attività di Controlla integrità database.

Sulla pagina Definizione attività controlla integrità database selezionare i database di Business, nell'esempio selezioniamo solo PROVA, in caso se sull'istanza sql sono presenti solo database di Business potremmo anche usare l'opzione Tutti i database utente (esclusi master, model, msdb e tempdb) per coinvolgere nel controllo tutti i database presenti (attenzione però perché su sql potrebbero esserci anche altri database di altri software).

Ed impostare le impostazioni.

Sulla pagina Selezione opzioni report selezionare il flag Scrivi report in un file di testo per conservare in un file di log l'esito del piano di manutenzione del database.

Quindi si preme Fine sulla pagina di riepilogo.

Premere Chiudi.

A questo punto in gestione dovremmo vedere il piano di manutenzione appena creato.

Se facciamo tasto destro e scegliamo modifica.

Troveremo il dettaglio del piano della pianificazione, delle attività e delle impostazioni effettuate che in caso possiamo modificare. In particolare, se poi effettuiamo sempre con il tasto destro modifica sull'attività di controllo integrità database.

Troviamo le configurazioni scelte in precedenza.

Se guardiamo con il pulsante Visualizza T-SQL aggiunge al comando DBCC CHECKDB il parametro WITH NO_INFOMSGS che serve semplicemente a nascondere i messaggi informativi ("informational messages") che non indicano errori. Quindi l'eleborazione non è veramente uguale a quella del busadmintools ma è molto simile.

Altro esempio, se proviamo a mettere un altro flag, quello di 'Solo struttura fisica'.

Se guardiamo nuovamente il pulsante Visualizza T-SQL aggiunge al comando DBCC CHECKDB il parametro WITH PHYSICAL_ONLY.

Una domanda potrebbe essere a questo punto: è giusto mettere questo flag (o altri)? Dipende sempre da cosa si vuole fare. Il parametro PHYSICAL_ONLY di DBCC CHECKDB serve a eseguire un controllo molto più veloce, limitato alla coerenza fisica delle pagine e delle strutture di basso livello del database, evitando tutte le verifiche logiche più lente. Il parametro non controlla:

• integrità logica delle tabelle
• vincoli (foreign key, check, ecc.)
• relazioni tra indici e dati
• consistenza tra le pagine a livello di logica SQL

Quindi non garantisce che i dati siano logicamente corretti, ma solo che non ci sia corruzione fisica. Quindi è impossibile dire se è meglio impostare o meno il parametro WITH PHYSICAL_ONLY, non c'è un'impostazione che va bene in tutte le situazioni, dipende da caso a caso, da cliente a cliente ed anche in base a quello che si vuole fare. Sicuramente se si vuole fare un controllo completo è meglio non metterlo ed eseguire quindi DBCC CHECKDB(N'PROVA').

Facciamo anche notare che l'impostazione di una attività tramite i piani di manutenzione di SQL imposta anche in questo caso un processo sotto il SQL Agent ma il tipo di passaggio non esegue un comando T-SQL ma utilizzano il pacchetto di sql server integration service.

Dettaglio per la creazione di un piano di manutenzione da SSMS per effettuare l'aggiornamento statistiche

In questo altro esempio impostiamo tramite piani di manutenzione di SSMS l'attività di aggiornamento statistiche il più possibile simile a quello che viene generato dal busadmintools. Anche in questo caso riportiamo solo una parte delle schermate (quelle che differiscono dalla precedente impostazione del controllo integrità database).

Nella pagina di Selezione delle proprietà del piano indicare un Nome del piano e scegliere Singola pianificazione per l'intero piano.

Nella pagina Seleziona attività di manutenzione scegliere la voce come sottoindicato.

Nella pagina Seleziona ordine attività di manutenzione confermare visto che stiamo eseguendo la sola attività di Aggiorna statistiche.

Sulla pagina Definizione attività Aggiorna statistiche selezionare i database di Business, nell'esempio selezioniamo solo PROVA, in caso se sull'istanza sql sono presenti solo database di Business potremmo anche usare l'opzione Tutti i database utente (esclusi master, model, msdb e tempdb) per coinvolgere nel controllo tutti i database presenti (attenzione però perché su sql potrebbero esserci anche altri database di altri software).

Sempre sulla pagina Definizione attività Aggiorna statistiche non modifichiamo in questo caso le altre opzioni presenti.

Anche qui una volta confermato il piano lo troviamo in SSMS per eventuali modifiche.

Dettaglio per la creazione di un piano di manutenzione da SSMS per effettuare la riorganizzazione degli indici

In questo altro esempio impostiamo tramite piani di manutenzione di SSMS l'attività di riorganizzazione degli indici, il più possibile simile a quello che viene generato dal busadmintools. In questo caso riportiamo solo la finestra di configurazione definizione dell'attività.

Dettaglio per la creazione di un piano di manutenzione da SSMS per effettuare la ricompilazione degli indici

In questo altro esempio impostiamo tramite piani di manutenzione di SSMS l'attività di ricompilazione degli indici, il più possibile simile a quello che viene generato dal busadmintools. In questo caso riportiamo solo la finestra di configurazione definizione dell'attività.

Altro esempio per la creazione di un piano di manutenzione da SSMS per effettuare la riorganizzazione e la ricompilazione degli indici

In questo esempio impostiamo tramite piani di manutenzione di SSMS le attività di riorganizzazione e ricompilazione degli indici, in una modalità più mirata, utile magari in contesti di database 'grandi' o di maggiore complessità, senza riorganizzare e ricompilare sempre tutto. Anche qui solo le schermate rilevanti.

Nella pagina di Selezione delle proprietà del piano indicare un Nome del piano e scegliere Singola pianificazione per l'intero piano.

Nella pagina Seleziona attività di manutenzione scegliere la voce come sottoindicato.

Nella pagina Seleziona ordine attività di manutenzione confermare le voci come sottoindicate (abbiamo messo prima ricompila indice a poi riorganizza indice).

Sulla pagina Definizione attività Ricompila indice selezionare i database di Business, nell'esempio selezioniamo solo PROVA, in caso se sull'istanza sql sono presenti solo database di Business potremmo anche usare l'opzione Tutti i database utente (esclusi master, model, msdb e tempdb) per coinvolgere nel controllo tutti i database presenti (attenzione però perché su sql potrebbero esserci anche altri database di altri software).

Sempre sulla pagina Definizione attività Ricompila indice togliere il flag su "conteggio pagine" e lascia gli altri parametri invariati. Il flag "conteggio pagine" (Page Count) serve a limitare l'operazione agli indici che superano una certa dimensione minima. Di fatto, SQL Server NON esegue riorganizzazione/ricostruzione sugli indici che hanno meno pagine del valore impostato.

Sulla pagina Definizione attività Riorganizza indice selezionare i database di Business, nell'esempio selezioniamo solo PROVA, in caso se sull'istanza sql sono presenti solo database di Business potremmo anche usare l'opzione Tutti i database utente (esclusi master, model, msdb e tempdb) per coinvolgere nel controllo tutti i database presenti (attenzione però perché su sql potrebbero esserci anche altri database di altri software).

Sempre sulla pagina Definizione attività Riorganizza indice togliere il flag su "conteggio pagine" e lascia gli altri parametri invariati. Il flag "conteggio pagine" (Page Count) serve a limitare l'operazione agli indici che superano una certa dimensione minima. Di fatto, SQL Server NON esegue riorganizzazione/ricostruzione sugli indici che hanno meno pagine del valore impostato.

Anche qui una volta confermato il piano lo troviamo in SSMS per eventuali modifiche.