ALTER INDEX, alternativa a DBCC DBREINDEX
L'utilizzo eccessivo del db, provoca a lungo andare una frammentazione
dei dati nelle pagine, e si rileva quindi necessario riorganizzare o reindicizzare
una o più tabelle al fine di ripristinare una buona performance sia in lettura che in scrittura.
Normalmente la percentuale di frammentazione dovrebbe attestarsi
non oltre il 35% (Microsoft dice anche il 25%),
limite oltre il quale le performance della tabella o del db iniziano a scemare.
Per verificare la percentuale di frammentazione si può ricorrere a questa istruzione :
DECLARE @object_name VARCHAR(20);
SET @object_name = 'TableName'
DBCC SHOWCONTIG (@object_name) WITH TABLERESULTS, ALL_INDEXES
valida per SQL 2000 e SQL 2005 oppure una più elegante :
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'DBName');
SET @object_id = OBJECT_ID(N'TableName');
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'SAMPLED');
valida per il solo SQL 2005.
Nel primo caso la colonna di riferimento sarà la "LogicalFragmentation"
nel secondo "avg_fragmentation_in_percent".
Per ricostruire l'indice in linea o non in linea, in SQL Server 2008, viene utilizzata
l'istruzione ALTER INDEX che, come viene enunciato nel titolo dell'articolo,
sostituisce l'istruzione DBCC DBREINDEX adottata nelle versioni precedenti :
DBCC DBREINDEX ('NomeTabella', '', ValoreDiFrammentazione)
vi è anche una stored procedure che apre un cursore che cicla
su tutte le tabelle e rigenera gli indici :
USE [NomeDatabase]
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
L'istruziuone ALTER INDEX invece si presenta come segue :
Ricostruzione di un indice
Nell'esempio seguente viene ricostruito un singolo indice della tabella :
USE Nome Database;
GO
ALTER INDEX NomeIndice ON NomeTabella
REBUILD;
GO
Ricostruzione di tutti gli indici di una tabella e impostazione di opzioni
Nell'esempio seguente viene specificata la parola chiave ALL per ricostruire tutti
gli indici associati alla tabella e vengono inoltre impostate tre opzioni :
USE [NomeDatabase];
GO
ALTER INDEX ALL ON NomeTabella
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO
Le tre opzioni specificate sono :
FILLFACTOR
Specifica una percentuale che indica il livello di riempimento che deve essere applicato
da Database Engine (Motore di database) per il livello foglia di ogni pagina di indice
durante un'operazione di creazione o modifica di un indice.
fillfactor deve essere un valore integer compreso tra 1 e 100. Il valore predefinito è 0.
I valori 0 e 100 sono equivalenti;
SORT_IN_TEMPDB { ON | OFF }
Specifica se i risultati dell'ordinamento devono essere archiviati in tempdb.
Il valore predefinito è OFF;
STATISTICS_NORECOMPUTE { ON | OFF }
Specifica se devono essere ricalcolate le statistiche di distribuzione.
Per Ulteriori informazioni :
http://msdn.microsoft.com/it-it/library/ms188388.aspx
Ciao a Tutti 