SQL Maintenance Solution: soluzione free per la manutenzione di SQL Server

In un precedente articolo abbiamo esaminato gli strumenti che SQL Server (all’interno del Management Studio) mette a disposizione di un database manager per gestire la manutenzione del proprio database.

SQL Server Maintenance Solution, che vedremo in questo secondo articolo, è la soluzione free basata sul linguaggio T-SQL realizzata nel 2008 da un DBA svedese chiamato Ola Hallengren, e oggi, dopo aver superato le 3000 linee di codice e aver ricevuto più volte il premio di Best Free Tool, Gold e Silver award della rivista SQL Server Magazine, è divenuto lo standard de-facto per la manutenzione dei database SQL Server, sia di piccole che di medie-grandi dimensioni.

Questa soluzione garantisce un’efficiente e flessibile manutenzione dei backup, degli indici e delle statistiche e il controllo dell’integrità dei database. L’ultima versione disponibile risale al Gennaio 2012 ed è supportata dalle versioni SQL Server 2005 / 2008 / 2008 R2 / 2012 RC0 (il rilascio della RTM di SQL Server 2012 è previsto per Maggio 2012) e da tutte le Edition fatta eccezione per la SQL Express in quanto non dispone del SQL Server Agent, il servizio di schedulazione dei Job.

Di seguito esamineremo gli aspetti fondamentali della SQL Server Maintenance Solution rimandando per i dettagli alla documentazione disponibile sul sito ufficiale dell’autore al seguente indirizzo http://ola.hallengren.com/Documentation.html.

SQL Maintenance Solution Setup

Il setup è davvero molto semplice. Basta infatti scaricare e lanciare un unico script (MaintenaceSolution.sql dal sito http://ola.hallengren.com/downloads.html) prestando attenzione però a modificarlo impostando correttamente:

• Il flag di creazione dei Job (linea 29)
• Il percorso in cui indirizzare i backup (linea 30)

USE [master] -- Specify the database in which the objects will be created.

SET NOCOUNT ON

DECLARE @CreateJobs nvarchar(max)
DECLARE @BackupDirectory nvarchar(max)
DECLARE @OutputFileDirectory nvarchar(max)
DECLARE @LogToTable nvarchar(max)
DECLARE @Version numeric(18,10)
DECLARE @Error int

SET @CreateJobs          = 'Y'          -- Specify whether jobs should be created.
SET @BackupDirectory     = N'C:Backup' -- Specify the backup root directory.
SET @OutputFileDirectory = NULL         -- Specify the output file directory. If no directory is specified, then the SQL Server error log directory is used.
SET @LogToTable          = 'Y'          -- Log commands to a table.

Se impostata la variabile @CreateJobs a “Y”, saranno generati automaticamente tutti i job di cui abbiamo bisogno e che potremo poi schedulare e configurare nel modo più opportuno.

Figura 1: I job creati dallo script

Quindi dopo aver eseguito lo script di Setup passiamo ad analizzarne internamente gli aspetti più importanti. La soluzione è basata su un ristrettissimo numero di stored procedure, funzioni e tabelle create nel database di sistema master che automatizzano molto facilmente le attività di backup, controllo integrità e manutenzione indici e statistiche.

Figura 2:  stored procedure, funzione e tabelle

Di questi oggetti, tre soltanto sono le stored procedure che il DBA deve preoccuparsi di conoscere per poter configurare opportunamente le manutenzioni in quanto realizzano i task fondamentali di

• Backup (dbo.DatabaseBackup)
• Controllo dell’integrità (dbo.DatabaseIntegrityCheck)
• Re-build e reorganize degli indici e aggiornamento delle statistiche (dbo.IndexOptimize )

Tutte e tre tali stored procedure hanno in comune i seguenti parametri fondamentali:

@Databases nvarchar(max)

• l’elenco (separato da virgola) dei nomi dei database oggetto del task (es. ‘AdventureWorks, AdventureWorksLT, Pubs, Northwind, Master’)

oppure una delle seguenti keywords

• SYSTEM_DATABASES: tutti e solo i database di sistema (‘master’,’model’,’msdb’,’tempdb’)
• USER_DATABASES: tutti e solo i database utente
• ALL_DATABASES: tutti i database, sia essi utente che di sistema

In entrambi i casi è possibile escludere dall’insieme di database uno o più nomi di database anteponendo il segno – (meno) (es. USER_DATABASES, -dventure equivale a tutti i database utente eccetto quelli il cui nome contiene la parola dventure, quindi per esempio i database AdventureWorks e AdventureWorksLT)

@LogToTable nvarchar(max) = ‘N’

• Se impostato a ‘Y’ attiva il log del comando nella tabella dbo.CommandLog.

@Execute nvarchar(max) = ‘Y’

•  Se impostato a ‘N’ si limita a visualizzare i messaggi di output della stored procedure e eventualmente a loggare nella dbo.CommandLog in presenza di @LogToTale = 'Y' – molto utile per verificare l’attività che la procedura si appresta a svolgere prima di eseguirla realmente in produzione.

I restanti oggetti sono:

• la stored procedure denominata dbo.CommandExecute:viene richiamata dalle tre fondamentali di cui sopra per comandare al Database Engine l’esecuzione vera e propria del task;
• la table-valued function denomianata dbo.DatabaseSelect: viene richiamata anch’essa in tutte e tre le stored procedure fondamentali. Ha lo scopo di restituire in formato tabellare i nomi di database oggetto della manutenzione che sono passati come parametro alla stessa in formato stringa separati da virgola. Questa funzione verifica anche l’esistenza dei database passati come parametro, e in caso uno di questi nomi non corrisponde a un database realmente presente questo non viene restituito dalla funzione.

Figura 3: la funzione di selezione del database

• la tabella dbo.CommandLog: dove si può scegliere di loggare l’esito dei vari task impostando a “Y” il parametro @LogToTable che come già detto in precedenza è presente in tutte e tre le stored procedure fondamentali (dbo.DatabaseBackup, dbo.DatabaseIntegrityCheck, dbo.IndexOptimize). Attenzione però che di default @LogToTable è sempre impostato a “N”, quindi non avremo nessun log nella tabella dbo.CommandLog se non mettiamo mano ai vari job aggiungendo appunto @LogToTable = ‘Y’ tra i parametri della stored procedure.

Figura 3: il sistema di log

Per eliminare i vecchi log contenuti in questa tabella, il setup crea il job CommandLog Cleanup che di default cancella le righe più vecchie di 30 giorni (limite che può essere facilmente cambiato modificando la DELETE contenuto nel job stesso). Bisogna però ricordarsi di schedulare il job che come tutti gli altri viene creato senza alcuna schedulazione.

DatabaseBackup

La stored procedure per i backup è straordinariamente completa.

CREATE PROCEDURE [dbo].[DatabaseBackup]
@Databases nvarchar(max),
@Directory nvarchar(max) = NULL,
@BackupType nvarchar(max),
@Verify nvarchar(max) = 'N',
@CleanupTime int = NULL,
@Compress nvarchar(max) = NULL,
@CopyOnly nvarchar(max) = 'N',
@ChangeBackupType nvarchar(max) = 'N',
@BackupSoftware nvarchar(max) = NULL,
@CheckSum nvarchar(max) = 'N',
@BlockSize int = NULL,
@BufferCount int = NULL,
@MaxTransferSize int = NULL,
@NumberOfFiles int = 1,
@CompressionLevel int = NULL,
@Description nvarchar(max) = NULL,
@Threads int = NULL,
@Throttle int = NULL,
@Encrypt nvarchar(max) = 'N',
@EncryptionType nvarchar(max) = NULL,
@EncryptionKey nvarchar(max) = NULL,
@LogToTable nvarchar(max) = 'N',
@Execute nvarchar(max) = 'Y'

Fra tutti i parametri vale la pena di notare @BackupSoftware che rende possibile il supporto dei più comuni software di terze parti per il backup di SQL Server (nel momento in cui viene scritta questa guida sono Red Gate SQL HyperBac, Quest LiteSpeed for SQL Server, Red Gate SQL Backup, Idera SQL safe backup)

Questo vuol dire che, chi adopera per motivi diversi un tool di terze parti per realizzare i backup, può continuare a farlo pur non rinunciando all’utilizzo di questa stored procedure che può fungere da wrapper al tool esterno, pilotandone l’esecuzione.

Correlati ai BackupSoftware di terze parti sono i parametri @CompressionLevel, @Threads, @Throttle, @EncryptionType (per i dettagli rimando alla documentazione ufficiale sul sito dell’autore)

DatabaseIntegrityCheck

È la stored procedure che realizza il comando DBCC CHECKDB su ciascuno dei database specificati nel parametro @Databases.

CREATE PROCEDURE [dbo].[DatabaseIntegrityCheck]

@Databases nvarchar(max),
@PhysicalOnly nvarchar(max) = 'N',
@NoIndex nvarchar(max) = 'N',
@ExtendedLogicalChecks nvarchar(max) = 'N',
@TabLock nvarchar(max) = 'N',
@LogToTable nvarchar(max) = 'N',
@Execute nvarchar(max) = 'Y'

AS

Particolarmenti importanti i parametri:

• @PhysicalOnly che se impostato a ‘Y’ attiva l’opzione WITH PHYSICAL_ONLY che limita il CHECKDB alla sola struttura fisica delle pagine di dati, alle intestazioni, la struttura fisica degli alterbi B-tree, e alla consistenza di allocazione del database, realizzando così un check meno approfondito ma molto più veloce, quindi particolarmente suggerito per database di grandi dimensioni e frequentemente controllati.
• @TabLock che se impostato a ‘Y’ evita la creazione dello snapshot di database ottenendo invece un lock esclusivo sulle tabelle in corso di verifica. Questa opzione consente di tempi di esecuzione del CHECKDB più bassi ma una concorrenza ridotta per via del lock esclusivo acquisito per potare avanti il controllo di integrità.

IndexOptimize

Questa procedura è molto probabilmente quella riuscita meglio dell’intera soluzione, in quanto riesce a realizzare il delicato task di manutenzione degli indici e delle statistiche in modo intelligente, potendo calibrare il tipo di manutenzione in base al livello di frammentazione degli indici e al numero di pagine di dati. Infatti una ben nota best practice in materia di indici ne suggerisce la ricostruzione se la percentuale media di frammentazione supera il 30% e la riorganizzazione se compresa tra il 5% e il 30%, e in ogni caso solo per indici che occupano più di 1000 data pages. Ovviamente si tratta solo di una linea guida soggetta a più o meno sensibili modifiche a seconda dei casi.

Di seguito i parametri che ammette la stored procedure e i valori di default.

CREATE PROCEDURE [dbo].[IndexOptimize]

@Databases nvarchar(max),
@FragmentationLow nvarchar(max) = NULL,
@FragmentationMedium nvarchar(max) = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 int = 5,
@FragmentationLevel2 int = 30,
@PageCountLevel int = 1000,
@SortInTempdb nvarchar(max) = 'N',
@MaxDOP int = NULL,
@FillFactor int = NULL,
@PadIndex nvarchar(max) = NULL,
@LOBCompaction nvarchar(max) = 'Y',
@UpdateStatistics nvarchar(max) = NULL,
@OnlyModifiedStatistics nvarchar(max) = 'N',
@StatisticsSample int = NULL,
@StatisticsResample nvarchar(max) = 'N',
@PartitionLevel nvarchar(max) = 'N',
@TimeLimit int = NULL,
@Indexes nvarchar(max) = NULL,
@Delay int = NULL,
@LogToTable nvarchar(max) = 'N',
@Execute nvarchar(max) = 'Y'

AS

L’idea di fondo è quella di lasciare alla procedura l’onere di decidere se riorganizzare piuttosto che ricostruire un indice e, in quest’ultimo caso, se ricostruire l’indice in modalità OFFLINE oppure ONLINE in accordo anche con la Edition di SQL Server (ricordo che la Rebuild ONLINE è possibile solo con la Enterprise e la Developer Edition). Al DBA non resta che configurare

1. La soglia minima del numero di pagine di dati del singolo indice (@PageCountLevel): in altre parole un indice il cui numero di pagine di dati è inferiore a questa soglia non sarà preso in considerazione dalla procedura di manutenzione;
2. La soglia di frammentazione alta (@FragmentationLevel2) superata la quale un indice viene assoggettato alla manutenzione specificata nel parametro @FragmentationHigh
3. La soglia di frammentazione media (@FragmentationLevel1) superata la quale un indice viene assoggettato alla manutenzione specificata nel parametro @FragmentationMedium, e al di sotto della quale a quella specificata nel parametro @FragmentationLow.

Con il parametro @Indexes si può restringere l’attività di manutenzione a un insieme ben specifico di indici oppure escludere uno o più indici anteponendo il segno – (meno) al nome dell’indice (es: @Indexes = '-[Sales].[Individual].[XMLPATH_Individual_Demographics]').

Per quanto riguarda l’aggiornamento delle statistiche, possiamo realizzare l’aggiornamento solo di quelle i cui dati sono stati modificati dalla data di ultimo aggiornamento statistiche impostando il parametro @OnlyModifiedStatistics = 'Y', e decidere di procedere all’aggiornamento o meno impostando il parametro @UpdateStatistics con uno dei seguenti valori

•  ‘ALL’: tutte le statistiche presenti sia esse su indici che su colonne
•  ‘COLUMNS’: solo statistiche su colonne
•  ‘INDEX’: solo statistiche su indici
•  NULL: nessun aggiornamento statistiche

In ogni caso, la stored procedure non aggiorna le statistiche degli indice che sono stati ricostruiti.

Facciamo un esempio:

EXEC [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES, -[AdventureWorksLT]',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 10,
@FragmentationLevel2 = 40,
@PageCountLevel = 2000,
@Indexes = '-[AdventureWorks].[Sales].[Individual].[XMLPATH_Individual_Demographics]'

Con i parametri così configurati, ecco cosa comanderà la stored procedure:

per tutti gli indici che

• occupano più di 2000 data pages;
• che appartengono a tutti i database utente;
•  eccetto per tutti gli indici del database [AdventureWorksLT];
•  eccetto l’indice [Sales].[Individual].[XMLPATH_Individual_Demographics] del database [AdventureWorks].

sarà realizzata

• una Reorganize se il livello di frammentazione del singolo indice è maggiore o uguale a 10% e minore di 40%
• una Rebuild in modalità Online, se la Edition di SQL Server è Enterprise o Developer, o in caso contrario in modalità Offline, se il livello di frammentazione è maggiore o uguale al 40%.