
guide
Tutti i linguaggi per diventare uno sviluppatore di app per Android.
Come configurare e usare SQL Server Maintenance Solution di Ola Hallengren: lo standard per la manutenzione dei database 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.
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:
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.
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.
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
Tutte e tre tali stored procedure hanno in comune i seguenti parametri fondamentali:
@Databases nvarchar(max)
oppure una delle seguenti keywords
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’
@Execute nvarchar(max) = ‘Y’
@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:
@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.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.
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)
È 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à.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
@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;@FragmentationLevel2
) superata la quale un indice viene assoggettato alla manutenzione specificata nel parametro @FragmentationHigh
@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
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
sarà realizzata
Reorganize
se il livello di frammentazione del singolo indice è maggiore o uguale a 10% e minore di 40%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%.Se vuoi aggiornamenti su SQL Maintenance Solution: soluzione free per la manutenzione di SQL Server inserisci la tua email nel box qui sotto:
Compilando il presente form acconsento a ricevere le informazioni relative ai servizi di cui alla presente pagina ai sensi dell'informativa sulla privacy.
La tua iscrizione è andata a buon fine. Se vuoi ricevere informazioni personalizzate compila anche i seguenti campi opzionali:
Compilando il presente form acconsento a ricevere le informazioni relative ai servizi di cui alla presente pagina ai sensi dell'informativa sulla privacy.
Tutti i linguaggi per diventare uno sviluppatore di app per Android.
Come creare applicazioni per il Web con PHP e MySQL per il DBMS.
Tutte le principali tecnologie per diventare uno sviluppatore mobile per iOS.
I fondamentali per lo sviluppo di applicazioni multi piattaforma con Java.
Diventare degli esperti in tema di sicurezza delle applicazioni Java.
Usare Raspberry Pi e Arduino per avvicinarsi al mondo dei Maker e dell’IoT.
Le principali guide di HTML.it per diventare un esperto dei database NoSQL.
Ecco come i professionisti creano applicazioni per il Cloud con PHP.
Lo sviluppo professionale di applicazioni in PHP alla portata di tutti.
Come sviluppare applicazioni Web dinamiche con PHP e JavaScript.
Fare gli e-commerce developer con Magento, Prestashop e WooCommerce.
Realizzare applicazioni per il Web utilizzando i framework PHP.
Creare applicazioni PHP e gestire l’ambiente di sviluppo come un pro.
Percorso base per avvicinarsi al web design con un occhio al mobile.
Realizzare siti Web e Web application con WordPress a livello professionale.
Come creare database e collection, inserire, estrarre, aggiornare e rimuovere dati da una base di dati MongoDB con Python
Iniziamo ad utilizzare le api tinkerpop con neo4j: come esempio implementiamo la mappa di un ipotetico gioco.
Linux sta subendo una forte espansione nei mercati asiatici: sia per questioni politiche (come nel caso della Cina) che economiche, […]
Jelastic Cloud è un servizio PaaS (Platform as a Service) offerto da Aruba, e destinato a tutte le aziende e gli sviluppatori interessati al deploy di applicazioni complesse direttamente su un’infrastruttura potente e rodata in Cloud. In questa guida scopriremo le tecnologie supportate (WordPress, Magento, Docker, Kubernetes, PHP, Java, Node.js) e come sfruttarle per un deploy di applicazioni e CMS.