Tagliando di... un database

Dovendo per lavoro ottimizzare un database SQL sono stato costretto a studiarmi un po' di principi di manutenzione. Ne è venuta fuori una piccola guida su cosa fare e soprattutto cosa non fare.
Trovandomi a lavorare con un database che negli anni è cresciuto come struttura in modo non molto strutturato, l'inserimento di svariati milioni di record ha portato negli anni ad un rallentamento delle prestazioni. Per ovviare a questi problemi nell'esecuzione delle ricerche (e di conseguenza anche nella reportistica) sono stati inseriti indici un po' qua ed un po' là; ovviamente questa soluzione non è la soluzione, ma un palliativo affinché il cliente possa lavorare il meglio possibile. La creazione di indici, però, è un po' come una coperta quando sei a letto: tiri da una parte, ti scopri dall'altra.
Uno dei principali elementi da tenere in considerazione è la frammentazione di ciascun indice, ovvero un parametro che indica l'efficienza dello stesso; la frammentazione si produce nel tempo, quando vengono aggiunti o cancellati record di una tabella o quando viene modificato un valore di un campo indicizzato, ed il motore SQL Server sistema le pagine dell'indice per mantenere indicizzati i dati memorizzati. 
Gli aggiustamenti che avvengono nelle pagine dell'indice sono noti come page split. Il processo di divisione (splitting process) incrementa la dimensione della tabella e aumenta il tempo necessario a processare le query. [1]
In principio il mio collega aveva studiato una piccola query da eseguire all'occorrenza ed individuare quali fossero gli indici frammentati ed intervenire direttamente con SQL Management Studio per ricostruirli: questo implicava di connettersi al loro server una volta ogni 1-2 mesi, eseguire la query ed andare nella sezione degli indici di ciascuna tabella e ricostruirli (o in determinati casi semplicemente riorganizzarli). [2]

--- La query viene eseguita sul DB in uso [rif. variabile DB_ID()]
SELECT
  db_sistema.[name] AS [Nome DataBase]
, tbl_sistema.[name] AS [Nome Tabella]
, ind_sistema.[name] AS [Nome Indice]
, ROUND(oggetti.avg_fragmentation_in_percent,2) AS [% Frammentazione]
, ind_sistema.type AS Tipo
, ind_sistema.type_desc AS [Descrizione Tipo]
FROM sys.dm_db_index_physical_stats (DB_ID(), null, null, null, null ) AS oggetti
INNER JOIN sys.databases AS db_sistema ON db_sistema.[database_id] = oggetti.[database_id]
INNER JOIN sys.tables AS tbl_sistema ON tbl_sistema.[object_id] = oggetti.[object_id]
INNER JOIN sys.indexes AS ind_sistema ON
(
  ind_sistema.[object_id] = oggetti.[object_id]
  AND ind_sistema.[index_id] = oggetti.[index_id]
)
WHERE ind_sistema.[name] IS NOT NULL
--AND oggetti.avg_fragmentation_in_percent >= 10
ORDER BY tbl_sistema.[name], ind_sistema.[name]

Leggendo alcuni post dei vari Microsoft MVP, il mio database (3-4 GB) viene considerato piccolo, ma consigliano comunque un programma di manutenzione giornaliera. Si era pensato, però, di limitarci ad inserire un'operazione pianificata nel sistema una volta alla settimana che ricreasse tutti gli indici di un database, ma la soluzione non era quella sperata, anzi era un'operazione pesantissima che non andava nemmeno a buon fine a causa di qualche proprietà legata al fattore di riempimento dell'indice e alla capacità accrescimento del file PRIMARY.

DECLARE @Database VARCHAR(255)  
DECLARE @Table VARCHAR(255) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR 
SELECT name FROM master.dbo.sysdatabases  
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')  
ORDER BY 1 

OPEN DatabaseCursor 

FETCH NEXT FROM DatabaseCursor INTO @Database 
WHILE @@FETCH_STATUS = 0 
BEGIN 

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
  table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES
  WHERE table_type = ''BASE TABLE'''  

   -- create table cursor 
   EXEC (@cmd) 
   OPEN TableCursor  

   FETCH NEXT FROM TableCursor INTO @Table  
   WHILE @@FETCH_STATUS = 0  
   BEGIN  

       IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
       BEGIN
           -- SQL 2005 or higher command
           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
           EXEC (@cmd)
       END
       ELSE
       BEGIN
          -- SQL 2000 command
          DBCC DBREINDEX(@Table,' ',@fillfactor) 
       END

       FETCH NEXT FROM TableCursor INTO @Table  
   END  

   CLOSE TableCursor  
   DEALLOCATE TableCursor 

   FETCH NEXT FROM DatabaseCursor INTO @Database 
END 
CLOSE DatabaseCursor  
DEALLOCATE DatabaseCursor

A questo punto ho pensato di fare una cosa un po' più raffinata, ma soprattutto riciclabile al 100% con pochissimo sforzo.

@mioDB
 Il nome del database che si vuole manutenzionare.
@fattoreRiempimento
 Il fattore di riempimento è un valore che
determina la percentuale di spazio in ogni pagina di livello foglia da riempire con dati, riservando pertanto una percentuale di spazio libero per l'aumento delle dimensioni successivo. [3]
@frammentazioneMinima E' la soglia minima al di sotto della quale la frammentazione dell'indice non richiede alcuna operazione di manutenzione. 
@frammentazioneSoglia
 E' la soglia che si vuole impostare al di sotto della quale l'indice viene riorganizzato e al di sopra della quale l'indice invece viene ricostruito.

In principio eseguo una compattazione su disco sia del file di dati che quello di log con l'operazione DBCC SHRINKDATABASE, operazione che 
generalmente aumenta la frammentazione degli indici del database; questo è un ulteriore motivo per evitare di compattare ripetutamente un database. [4]
Avendo il problema che le tabelle sys si riferiscono al contesto in cui vengono eseguite le query, ho provato a cambiare il contesto con l'istruzione USE ma senza successo; quindi per velocizzare lo sviluppo dell'attività sono stato obbligato a dichiarare il mio cursore passando come parametro il nome del mio database ad una stringa ed eseguirla con una EXEC.

 Per essere il più fedele possibile a quello che fa SQL Management Studio, ho messo una traccia SQL Profiler per analizzare le query passanti da un determinato engine e mi sono brutalmente copiato quella eseguita durante la ricostruzione dell'indice. A questo punto potevo scegliere se eseguire la ricompilazione dell'indice online e quindi dare la possibilità agli utenti di continuare ad accedere ai dati (come avviene per la riorganizzazione), attraverso il parametro ONLINE = ON, ma è un'opzione disponibile solo sulla Enterprise Edition dell'engine di SQL, facendo le prove su di un SQL Server 2005 Express ovviamente ho ripiegato sull'opzione ONLINE = OFF.

 Ed ecco quindi il risultato finale:

DECLARE @tabella VARCHAR(255) 
DECLARE @indice VARCHAR(255) 
DECLARE @fattoreRiempimento INT
DECLARE @frammentazione INT
DECLARE @frammentazioneMinima INT
DECLARE @frammentazioneSoglia INT
DECLARE @comando NVARCHAR(1000)
DECLARE @mioDB NVARCHAR(255)

SET @mioDB = 'Nome_Mio_Database'
SET @fattoreRiempimento = 80
SET @frammentazioneMinima = 10
SET @frammentazioneSoglia = 40

DBCC SHRINKDATABASE (@mioDB)

SET @comando = ' DECLARE CursoreTabella CURSOR FOR' + CHAR(13) + CHAR(10) +
'SELECT ' + CHAR(13) + CHAR(10) +
'db_sistema.[name] + ''.[dbo].'' + tbl_sistema.[name] AS NomeTabella' + CHAR(13) + CHAR(10) +
', ind_sistema.[name] AS NomeIndice' + CHAR(13) + CHAR(10) +
', ROUND(oggetti.avg_fragmentation_in_percent,2) AS Frammentazione' + CHAR(13) + CHAR(10) +
' FROM sys.dm_db_index_physical_stats (DB_ID(''' + @mioDB + '''), 0, NULL, NULL, NULL ) AS oggetti' + CHAR(13) + CHAR(10) +
' INNER JOIN ' + @mioDB + '.sys.databases AS db_sistema ON db_sistema.[database_id] = oggetti.[database_id]' + CHAR(13) + CHAR(10) +
' INNER JOIN ' + @mioDB + '.sys.tables AS tbl_sistema ON tbl_sistema.[object_id] = oggetti.[object_id]' + CHAR(13) + CHAR(10) +
' INNER JOIN ' + @mioDB + '.sys.indexes AS ind_sistema ON ' + CHAR(13) + CHAR(10) +
' (' + CHAR(13) + CHAR(10) +
'ind_sistema.[object_id] = oggetti.[object_id] ' + CHAR(13) + CHAR(10) +
'AND ind_sistema.[index_id] = oggetti.[index_id]' + CHAR(13) + CHAR(10) +
' )' +  CHAR(13) + CHAR(10) +
' WHERE ind_sistema.[name] IS NOT NULL' + CHAR(13) + CHAR(10) +
--' AND ind_sistema.type = 2 -- solo indici non clustered' + CHAR(13) + CHAR(10) +
--' AND oggetti.avg_fragmentation_in_percent >= 70' + CHAR(13) + CHAR(10) +
' AND oggetti.avg_fragmentation_in_percent > ' + CAST(@frammentazioneMinima AS NVARCHAR(13)) + CHAR(13) + CHAR(10) +
' ORDER BY tbl_sistema.[name], ind_sistema.[name]'


--PRINT @comando


EXEC (@comando)
OPEN CursoreTabella  


FETCH NEXT FROM CursoreTabella INTO @tabella, @indice, @frammentazione
WHILE @@FETCH_STATUS = 0  
BEGIN
  IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
    BEGIN
      -- SQL 2005 or higher command
      IF @frammentazione > @frammentazioneSoglia
        BEGIN
          PRINT 'REBUILD ' + @tabella + '.' + @indice
          SET @comando = 'ALTER INDEX ' + @indice + ' ON ' + @tabella + ' REBUILD WITH (' +
            --'FILLFACTOR = ' + CONVERT(VARCHAR(3),@fattoreRiempimento) + ',' +
            'PAD_INDEX  = OFF,' +
            'STATISTICS_NORECOMPUTE  = OFF,' +
            'ALLOW_ROW_LOCKS  = ON,' +
            'ALLOW_PAGE_LOCKS  = ON,' +
            'ONLINE = OFF,' +
            'SORT_IN_TEMPDB = OFF' +
            ')'
         END
       ELSE
         BEGIN
           PRINT  'REORGANIZE ' + @tabella + '.' + @indice
           SET @comando = 'ALTER INDEX ' + @indice + ' ON ' + @tabella + ' REORGANIZE WITH ( LOB_COMPACTION = ON )'
         END
         
       EXEC (@comando)
     END
   ELSE
     BEGIN
       -- SQL 2000 command
       DBCC DBREINDEX(@tabella,' ',@fattoreRiempimento)
     END
FETCH NEXT FROM CursoreTabella INTO @tabella, @indice, @frammentazione
END

CLOSE CursoreTabella  
DEALLOCATE CursoreTabella

Ovviamente questa si potrebbe includere sia in una stored procedure che creare un Piano di Manutenzione sul motore SQL Server, ma questa query si presta molto bene anche ad essere eseguita da un batch lanciato da un'operazione panificata di Windows, ad esempio con il comando:

 osql -S <server> -U sa -P <password> -i <path query>

 Fonti:
1 Index Fragmentation
2 Riorganizzare e ricompilare gli indici
3 Fattore di riempimento
4 DBCC SHRINKDATABASE (Transact-SQL)

Commenti

Posta un commento

Post popolari in questo blog

Polisportive

Immagine
Fonte: http://www.smviganello.ti.ch/node/370 Forse è utopia, sarebbe interessante tornare alle vecchie polisportive , ma con una ventata di rinnovamento.  Oltre alla già citata opera sociale nei confronti della comunità locale , dovrebbe permettere ed agevolare il passaggio interdisciplinare, soprattutto ai più piccoli che non hanno ancora le idee chiare sul proprio futuro sportivo, anche durante la stessa stagione sportiva: non più entità separate che cercano di rubarsi tesserati a vicenda, ma attività che lavorano in sinergia. 
Continua a leggere

Leicester City Footbal Club

Immagine
Il mio amore per il Leicester City Football Club è facile da descrivere: è un po' come quando durante la gita di terza media ti innamori della ragazza più racchia del pullman perchè scatta quell'alchimia che te la fa apparire come la più appetibile e poi te la ritrovi anni dopo che è Miss Italia . Tutto è nato durante le lunghe sessioni notturne di gioco a Football Manager assieme al mio amico Paolo: avevamo (poi nel frattempo sono arrivati i figli) l'abitudine di iniziare il gioco includendo i gironi di Italia, Spagna, Inghilterra e Scozia, con qualche new entry di tanto in tanto per esplorare nuove realtà.
Continua a leggere

Pagellone partita di calci8

Immagine
Il pagellone della partita di calci8 del  1° luglio 2014 fra i patentati UEFA B del corso 2013 di Rimini, sempre a cura dell'epico Michele Peruzzi ! Per la cronaca il risultato parla chiaro: ORTALLI’S BAND – DI NUNZIO’S BAND 9-8 A seguire un match ancora più impegnativo, quello del Birrodromo di Riccione!
Continua a leggere