Piani di manutenzione: Strategie per proteggere e ottimizzare i tuoi database!

In questo articolo tratteremo i task fondamentali che un piano di manutenzione per database SQL Server deve avere. Esploreremo il significato di alcune impostazioni di default e la possibilità di arricchire il task di backup database con un task di tipo T-SQL script in grado di testare il ripristino del database archiviato. Dettagli aggiuntivi sono disponibili nella documentazione SQL Server Maintenance plans.

Un piano di manutenzione database è un insieme di attività specifiche e proattive che devono essere eseguite regolarmente sui database di una istanza SQL Server o Azure SQL per garantirne il corretto funzionamento, performance adeguate, e disponibilità. I piani di manutenzione creano un flusso di lavoro che organizza le attività necessarie a garantire che il database sia ottimizzato, sottoposto regolarmente a backup e consistente.

I piani di manutenzione possono essere implementati con:

• Maintenance Plan Wizard (SSMS)
• Maintenance Plan Designer (SSMS)
• T-SQL script
• PowerShell script
• SQL Server Maintenance Solution by Ola Hallengren (ottima soluzione per gestire la manutenzione database)

Questo articolo descrive i principali task di manutenzione per un piano creato con SQL Server Management Studio che, grazie alla disponibilità di un wizard, abbassa notevolmente la soglia d’ingresso relativa alle competenze necessarie per creare un piano di manutenzione database.

Per creare e gestire i piani di manutenzione da SQL Server Management Studio, è necessario essere membri del ruolo (fixed server role) sysadmin. L’output (log) generato dalle attività di manutenzione può essere in formato testo (file) o in formato tabellare (all’interno del database di sistema msdb). In caso di formato tabellare, il risultato si troverà in dbo.sysmaintplan_log e dbo.sysmaintplan_logdetail.

Le attività fondamentali di manutenzione database sono:

• Check database integrity
• Backup database
• Reorganize index
• Rebuild index
• Update statistics
• Shrink database

a cui si aggiungono due task di cleanup:

• History cleanup
• Maintenance cleanup task

Check database integrity

Il task check database integrity controlla l’integrità logica e fisica di tutti gli oggetti nel database specificato eseguendo le seguenti operazioni:

• DBCC CHECKALLOC sul database
• DBCC CHECKTABLE su ogni tabella e vista nel database
• DBCC CHECKCATALOG sul database
• Convalida il contenuto di ogni vista indicizzata nel database
• Convalida la coerenza a livello di collegamento tra i metadati della tabella, le directory e i file del file system durante l’archiviazione dei dati di tipo varbinary(max) nel file system utilizzando FILESTREAM
• Convalida i dati di Service Broker nel database

Quando il task check database integrity viene creato dal wizard, l’opzione Physical Only è selezionata by default. L’opzione limita il controllo all’integrità della struttura fisica della pagina, alle intestazioni dei record e alla coerenza dell’allocazione del database. L’utilizzo di questa opzione riduce il tempo di esecuzione del comando DBCC CHECKDB, specialmente su database di grandi dimensioni ma non rileva i casi di corruzione logica (esempio quelli che si possono simulare con DBCC WRITEPAGE).

L’uso frequente del task di controllo integrità su database di produzione può essere eseguito con l’opzione Physical Only selezionata ma consiglio di prevedere anche un controllo integrità completo nelle finestre di manutenzione programmata.

Backup e restore database

Il task backup database esegue il backup completo di un database SQL Server oppure di uno o più file o filegroup per creare un backup di file in accordo con la strategia di backup scelta.

SQL Server Management Studio, purtroppo, non fornisce un task di ripristino database. Ricordo che non è sufficiente eseguire i backup; è necessario testare periodicamente anche il ripristino e l’integrità del backup archiviato.

Ogni task di backup deve avere un corrispondente task di restore in grado di testare il ripristino dell’ultimo backup. L’operazione di ripristino può essere implementata manualmente all’interno di un task di tipo T-SQL script; consiglio l’utilizzo della stored procedure dbo.sp_RestoreGene di Paul Brewer. Questa stored procedure interroga la cronologia di backup e restituisce i comandi RESTORE DATABASE come output (senza eseguirli).

Riporto di seguito un esempio di utilizzo della stored procedure dbo.sp_RestoreGene per il ripristino del database StackOverflowMini.

USE [tempdb];
GO

DROP TABLE IF EXISTS #RestoreCommands;

CREATE TABLE #RestoreCommands
(
  [TSQL] NVARCHAR(MAX)
  ,BackupDate DATETIMEOFFSET
  ,BackupDevice NVARCHAR(MAX)
  ,first_lsn NUMERIC(25, 0)
  ,last_lsn NUMERIC(25, 0)
  ,fork_point_lsn NUMERIC(25, 0)
  ,first_recovery_fork_guid UNIQUEIDENTIFIER
  ,last_recovery_fork_guid UNIQUEIDENTIFIER
  ,[database_name] SYSNAME
  ,SortSequence INTEGER
)

INSERT INTO
  #RestoreCommands
EXEC [master].[dbo].[sp_RestoreGene]
  @Database = 'StackOverflowMini',
  @WithRecovery = 1,
  @WithCHECKDB = 1,
  @TargetDatabase = 'StackOverflowMini-Check-Restore'
GO

DECLARE @RestoreCmd AS VARCHAR(MAX) = ''

SELECT
  @RestoreCmd =
    @RestoreCmd + STRING_AGG([TSQL], '; ' + CHAR(13) + CHAR(10)) WITHIN GROUP (ORDER BY SortSequence)
FROM
  #RestoreCommands;
GO

Cosa c’è di peggio che non avere un backup? Avere un backup danneggiato che non può essere ripristinato!

Manutenzione indici

SQL Server Management Studio offre due task di manutenzione indici: Reorganize Index e Rebuild Index.

Reorganize Index

La riorganizzazione indici viene implementata con il comando ALTER INDEX. La riorganizzazione indici agisce sul livello foglia degli indici clustered e non-clustered definiti su tabelle e viste, l’operazione viene sempre eseguita online e con micro-transazioni, per questo motivo, i blocchi sulla tabella non vengono mantenuti a lungo. La riorganizzazione indici utilizza risorse di sistema minime, questo aspetto unito alla modalità di esecuzione a micro-transazioni, tipicamente non blocca l’operatività del database. La riorganizzazione indici non è consentita per gli indici disabilitati.

Rebuild Index

La ricostruzione indici viene implementata tipicamente con il comando DBCC DBREINDEX. La ricostruzione di un indice consiste nell’eliminare e ricreare l’indice. Ciò rimuove la frammentazione, recupera spazio su disco compattando le pagine in base all’impostazione del fattore di riempimento specificato o esistente e riordina le righe dell’indice in pagine contigue. In funzione del tipo di indice e dell’edizione di SQL Server, può essere eseguita offline oppure online. È importante tenere in considerazione che la ricostruzione abilita un indice disabilitato.

Aggiornamento statistiche

SQL Server Management Studio offre un task specifico per l’aggiornamento statistiche che viene implementato con il comando UPDATE STATISTICS che aggiorna le statistiche di distribuzione dei dati per una tabella o una vista indicizzata.

Query Optimizer aggiorna le statistiche automaticamente, ma spesso non è sufficiente a garantire prestazioni ottimali. È quindi consigliato aggiornare le statistiche più frequentemente rispetto agli aggiornamenti predefiniti. La ricostruzione dell’indice (Rebuild Index) aggiorna automaticamente le statistiche collegate. L’aggiornamento delle statistiche non è al momento supportato su tabelle esterne.

Per aggiornare le statistiche su una tabella esterna si può utilizzare la stored procedure dbo.sp_drop_create_stats_external_table che fornisce i comandi per effettuare l’eliminazione e la creazione delle statistiche, può essere utilizzata all’interno di un paino di manutenzione. Trovate tutti i dettagli in questo repository GitHub.

Shrink

SQL Server Management Studio offre un task specifico di compattazione, tuttavia, lo shrink del database non deve essere considerata un’operazione di manutenzione regolare. Ogni volta che un database viene compattato, un arcobaleno, nel mondo, perde uno dei suoi colori 🙁

Manutenzione database in Azure SQL

Azure SQL database non ha un servizio di pianificazione nativo come SQL Agent per istanze on-premise. Trattandosi di una soluzione PaaS potremmo essere spinti a pensare che non sia necessario fare manutenzione database perché già garantita dalla piattaforma. Probabilmente non tutti sanno che Microsoft è responsabile solo dell’infrastruttura e della gestione dei backup; il controllo dell’integrità logica e fisica dei database, la manutenzione degli indici e delle statistiche e gli altri task di manutenzione database, sono sotto la responsabilità del DBA!

Di seguito trovate i link a tre articoli che descrivono le attività di cui è necessario farsi carico per effettuare la manutenzione dei database in Azure SQL, vengono inoltre spiegate le possibili soluzioni e descritti i servizi più opportuni da utilizzare:

1. Automazione delle attività di manutenzione in Azure SQL Database
2. Automazione delle attività di manutenzione in Azure SQL Database (2 Parte)
3. Automazione delle attività di manutenzione in Azure SQL Database (3 Parte)

Conclusioni

In questo articolo abbiamo trattato i task fondamentali di un piano di esecuzione costruito con SQL Server Management Studio, task necessari per mantenere in salute un database SQL Server. Ecco i punti chiave:

• La manutenzione regolare dei database SQL Server è fondamentale per garantirne la disponibilità, l’integrità e prestazioni elevate
• Non è sufficiente eseguire il backup database; è necessario testare periodicamente il ripristino e l’integrità dei backup archiviati
• Ogni volta che viene eseguito uno Shrink, nel mondo, un arcobaleno perde uno dei suoi colori

Per chi desidera approfondire, segnalo la registrazione della sessione SQL Server Maintenance Plan – Fondamenti e best practices essenziali, disponibile sul canale UGISS di Video.