Ottimizzazione degli indici

Home | SQL Server

Come e quale tipo di indice creare?

Un indice non può essere generato senza motivo è necessario capire quali sono le query più frequenti e quali campi sono realmente quelli che se indicizzati migliorano le prestazioni. E' sempre importante tenere presente che se gli indici in fase di ricerca velocizzano le query in fasi in update e di inserimento rallentano notevolmente il database perchè oltre a scrivere nella tabella il motore deve anche aggiornare gli indici, quindi più indici abbiamo su una tabella più tempo sara necessario per l'aggiornamento di un record o l'inserimento di uno nuovo.

Per capire quindi quali sono le query più frequenti e come queste fanno muovere il motore di SQL Server possiamo utilizzare il Profiler ed il Query Analyzer.

Un indice su una colonna può essere generato in modi differenti, e tutte le modalità incidono in modo differente sulle prestazioni. Questo significa che dopo aver generato un indici su una tabella non è detto che quello che abbiamo creato sia la versione ottimale. Una variante dello stesso indice può essere più o meno veloce. Infatti lo stesso indice può essere cluster o non-clustered, altro parametro importante è il FILLFACTOR , ancora se un indice è composto o meno. Regole generali non ce ne sono è necessario affidarsi alla propria esperienza e un mancanza di questa provare le varianti, analizzare i risultati e cercare la migliore soluzione per ogni determinata situazione.

I campi da prendere in considerazione per la creazione di un indice sono tutti quelli coinvolti frequentemente dai comandi WHERE, ORDER BY, GROUP BY, TOP e DISTINCT. In mancaza di indici queste istruzioni prevedono la scansione completa di tutta la tabella, il famigerato Table Scan che in molti casi vuol dire: crollo delle prestazioni.

Questo però non sempre molte volte quando i valori di una colonna hanno poca diversità (immaginiamo una tabella con qualche milione di righe ed un acampo che puà avere al più tre o quattro valori) SQL Server non utilizza l'indice perchè ritiene può performante fare la scansione della tabella. E' importante quindi precisare che non tutti i campi hanno bisogno di un indice, c'è sempre da verificare se effettivamente è il caso o meno di crearne uno.

Indici cluster

La creazioni di un indice cluster comporta il riordinamento dell'intera tabella. In effetti non esiste fisicamente in indice in questo è la stessa tabella che viene ordinata in base al campo presente nell'indice, è evidente che questo è il tipo di indice più performante dal punto di vista delle richieste ma il più pesante durante l'aggiornamento e l'inserimento, non a caso il più delle volte coincide con il campo identificatore del record che è immodificabile e viene incrementato ad ogni inserimento di un record. Quindi la scelta di quale campo inserire in questo tipo di indice è strategica, anche perchè è evidente il motivo per il quale non possono esserci due indici cluster per una tabella perchè i dati fisicamente o sono ordinati rispetto ad un campo o sono ordinati rispetto ad un altro.

Indici univoci

La creazione di un indice impedisce la duplicazione di un valore nella colonna. Se tramite il comando INSERT vengono aggiunti valori di chiave duplicati e non duplicati, SQL Server rifiuta tutte le righe, compresi i valori chiave non duplicati. Comunque è possibile specificare la clausola IGNORE_DUP_KEY, in fase di generazione dell'indice, per rifiutare soltanto i valori duplicati. I valori non duplicati verranno aggiunti. Non è possibile quindi creare un indice univoco se esistono valori di chiave duplicati.

FillFactor di un indice

Questo parametro permette di indicare quanto, in percentuale, le pagine degli indici devono essere riempite. Ovvero è possibile lasciare uno spazio vuoto nella pagina in modo da facilitare l'aggiornamento dell'indice in caso di inserimento di un nuovo valore. E' evidente che più il FillFactor è alto più perfoirmante è l'indice, ma è anche evidente che un indice con alto FillFactor molto aggiornato rischia di saltare, ovvero di frammentarsi. SQL Server è costretto a creare nuove pagine spezzettando quella precedente perchè la pagina si è ormai riempita.

Le statistiche per capire quali indici creare

Per capire se un database crea automaticamente le statistiche e le aggiorna basta lanciare la query

sp_dboption 'nomedeldatabase'
oppure dall'Enterprise Manager andiamo sulle proprietà di del database e controlliamo il flag "Auto Create Statistics"

Quindi se il database crea automaticamente le statistiche, durante le query l'ottimizatore di SQL server valuta se è il caso di aggiungere o meno delle statistiche per una determinata colonna. Questo avviene per le colonne senza indici. Queto ci viene incontro perchè ci aiuta a capire se esistono dei campi per i quali forse è meglio inserire un indice, ovvero se SQL Server crede di dover aggiungere delle statistiche ad una colonna per migliorare i tempi di risposta di una query vuol dire che molto probabilmente quella colonna avrà bisogno di un indice perchè per il modo col quale è coinvolta nelle query. Per capire quali sono i campi per i quali l'ottimizzatore ha generato statistiche basta lanciare la query :

SELECT name
FROM sysindexes
WHERE (name LIKE '%_WA_Sys%')

Questa query restituisce tutte le colonne che hanno delle loro statistiche aggiunte automaticamente dall'ottimizzatore. Questo non vuol dire che per ogni campo presente nei risultati di questa query bisogna creare un indice, semplicemente per ogni campo restituito dalla sarebbe opportuno indagare e capire se è il caso o meno di generare un indice.

Stato di frammentazione di una tabella SQL Server

Per controllare lo stato di frammentazione di una tabella è possibile utilizzare l'istruzione DBCC SHOWCONTIG (nometabella, nomeindice), la quale ci restituisce un insieme di informazioni sullo stato dell'indice.
Omettendo il parametro nomeindice l'istruzione

DBCC SHOWCONTIG (nometabella)

ci restituisce lo stato di frammentazione della tabella, la sola istruzione senza parametri ci permette di visionare lo stato di frammentazione di tutte le tabelle del database.

Premettendo che un extent di database è formato da otto pagine di 2 KB, questa è la tabella che viene fuori dai risultati:
Statistiche Descrizione
Pages Scanned (Pagine sottoposte a scansione) Numero di pagine della tabella o dell'indice.
Extents Scanned (Extent sottoposti a scansione) Numero di extent della tabella o dell'indice.
Extent Switches (Opzioni di extent) Numero di passaggi dell'istruzione DBCC da un extent all'altro durante l'accesso alla sequenza di pagine della tabella o dell'indice.
Avg. Pages per Extent (Media pagine per extent) Numero di pagine per extent della sequenza di pagine.
Scan Density [Best Count:Actual Count]
Densità scansione [conteggio ottimale: conteggio effettivo]
Il conteggio ottimale è il numero teorico di passaggi di extent se i vari elementi fossero contigui. Il conteggio effettivo è il numero effettivo di passaggi di extent. Il valore della Densità di scansione è 100 se tutti gli elementi sono contigui, è minore di 100 se esistono frammentazioni. La densità di scansione è un valore percentuale.
Logical Scan Fragmentation (Frammentazione scansione logica) Percentuale di pagine non ordinate restituite dalla scansione delle pagine foglia di un indice. Questo valore non è rilevante per heap e indici di testo. Una pagina risulta non ordinata quando la pagina successiva corrispondente indicata in un IAM è diversa da quella indicata come pagina successiva dal puntatore nella pagina foglia.
Extent Scan Fragmentation (Frammentazione scansione di extent) Percentuale di extent non ordinati rilevati durante la scansione delle pagine foglia di un indice. Questo valore non è rilevante per gli heap. Un extent risulta non ordinato quando l'extent contenente la pagina corrente di un indice non corrisponde fisicamente all'extent successivo a quello che contiene la pagina precedente di un indice.
Avg. Bytes Free per Page (Media byte disponibili per pagina) Numero medio di byte disponibili nelle pagine sottoposte a scansione. Maggiore è la media di byte, minore è il grado di completamento delle pagine. Sono preferibili valori bassi. Le dimensioni delle righe influiscono inoltre su questo valore, che risulta maggiore per righe di grandi dimensioni.
Avg. Page Density (full) (Media densità pagina (completa)) Densità media della pagina (in percentuale). Questo valore prende in considerazione le dimensioni della riga ed è pertanto un indicatore più accurato del grado di completamento delle pagine. Sono preferibili valori elevati.

Ecco infine una query, tratta dal manuale stesso di SQL Server, che utilizza DBCC SHOWCONTIG e DBCC INDEXDEFRAG per reindicizzare le tabella con una percentuale di frammentazione oltre un determinato livello


SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr   VARCHAR (255)
DECLARE @objectid  INT
DECLARE @indexid   INT
DECLARE @frag      DECIMAL
DECLARE @maxfrag   DECIMAL

-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0

-- Declare cursor
DECLARE tables CURSOR FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table
CREATE TABLE #fraglist (
   ObjectName CHAR (255),
   ObjectId INT,
   IndexName CHAR (255),
   IndexId INT,
   Lvl INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity DECIMAL,
   BestCount INT,
   ActualCount INT,
   LogicalFrag DECIMAL,
   ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
   FROM tables
   INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist 
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
	  WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
   FETCH NEXT
	  FROM tables
	  INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
	  AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
	  ' + RTRIM(@indexid) + ') - fragmentation currently '
	   + RTRIM(CONVERT(varchar(15),@frag)) + '%'
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
	   ' + RTRIM(@indexid) + ')'
   EXEC (@execstr)

   FETCH NEXT
	  FROM indexes
	  INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist
GO
	

Ricostruzione degli indici

Durante gli inserimenti e le modifiche in una tabella è possibile che gli indici vengano a deframmentarsi, facendo scadere le performance del database. E' necessario quindi, periodicamente, riorganizzare gli indici.
Ci sono diversi modi per farlo, ad esempio è possibile ricrearlo completamente, oppure sfruttare il comando DBCC DBREINDEX che prende come parametro il nome di una tabella e ricrea automaticamente tutti gli indici a questa associati.
Lo script che segue apre un cursore che cicla su tutte le tabelle e rigenera gli indici.

	USE <miodatabase>
	
	DECLARE @tabella varchar(255)

	DECLARE cursore_tabella CURSOR FOR
	SELECT table_name FROM information_schema.tables
	WHERE table_type = 'base table'

	OPEN cursore_tabella

	FETCH NEXT FROM cursore_tabella INTO @tabella
	WHILE @@FETCH_STATUS = 0
	BEGIN 
	DBCC DBREINDEX(@tabella,' ',90)
	FETCH NEXT FROM cursore_tabella INTO @tabella
	END

	CLOSE cursore_tabella

	DEALLOCATE cursore_tabella
	

miodatabase è il database su cui voglio fare la ricostruzione, aprendo il Query Analizer basta selezionarlo e rimuovere l'istruzione.
Lo script ha come parametro di fillfactor 90% che è il default di SQL Server, ma può essere modificato a proprio piacimento e schedulato periodicamente nei momenti in cui il database non viene utilizato in quanto, durante la rigenerazione degli indici, il comando DBCC DBREINDEX previene ogni accesso alla tabella.

Tabella Statiche

Le tabelle statiche sono quelle che di solito non sono quasi mai aggiornate, esse coincidono quasi sempre con le tabelle delle decodifiche, su queste è possibile generare molti indici (non è detto che tutti i campi debbano essere indicizzati), su queste tabelle il FILLFACTOR ed il PAD_INDEX può essere tranquillamente 100 in quanto come abbaimo detto precedentemente, gli aggiornamenti sono rarissimi. Quindi se impostiamo a 100 questi parametri dopo un aggiornamento sarebbe il caso di ricreare l'indice.

Home