In questo articolo, impareremo come identificare e risolvere la frammentazione degli indici in SQL Server. L’identificazione della frammentazione dell’indice e la manutenzione dell’indice sono parti importanti del compito di manutenzione del database. Microsoft SQL Server continua ad aggiornare le statistiche dell’indice con l’attività di Insert, Update o Delete sulla tabella. La frammentazione dell’indice è il valore di performance dell’indice in percentuale, che può essere recuperato da SQL Server DMV. Secondo il valore di performance dell’indice, gli utenti possono prendere gli indici in manutenzione rivedendo la percentuale di frammentazione con l’aiuto dell’operazione Rebuild o Reorganize.
Perché la percentuale di frammentazione dell’indice varia?
La percentuale di frammentazione dell’indice varia quando gli ordini di pagina logici non si coordinano con l’ordine di pagina fisico nell’assegnazione di pagina di un indice. Con la modifica dei dati nella tabella, le informazioni possono essere ridimensionate sulla pagina dei dati. La pagina era piena al massimo prima dell’operazione di aggiornamento della tabella. Tuttavia, lo spazio libero potrebbe essere trovato sulla pagina dei dati con un’operazione di aggiornamento della tabella. Gli utenti possono osservare l’ordine inquietante della pagina con l’operazione di cancellazione massiccia sulla tabella. Con le operazioni di aggiornamento e cancellazione, la pagina dei dati non sarà una pagina piena o vuota. Pertanto, lo spazio libero non utilizzato aumenta il disallineamento dell’ordine tra la pagina logica e la pagina fisica con l’aumento della frammentazione, e ciò può causare la peggiore prestazione della query e consuma anche più risorse del server.
È più essenziale precisare che la frammentazione dell’indice influisce sulla prestazione della query solo con la scansione della pagina. In questi casi, aumenta le possibilità di scarse prestazioni anche di altre richieste SQL, perché una query con un indice molto frammentato sulla tabella richiede più tempo per essere eseguita e consuma più risorse come Cache, CPU e IO. Pertanto, il resto delle richieste SQL ha difficoltà a finire l’operazione con le risorse del server incoerenti. Anche il blocco può verificarsi con le operazioni di aggiornamento e cancellazione perché l’ottimizzatore non raccoglierà le informazioni sulla frammentazione dell’indice mentre genera il piano di esecuzione della query.
Ci può essere un certo numero di indici creati su una singola tabella con la combinazione di varie colonne, e ogni indice può avere una diversa percentuale di frammentazione. Ora, prima di rendere appropriato o prendere un indice in manutenzione, gli utenti devono trovare quel valore di soglia dal database. La seguente dichiarazione T-SQL è un modo efficiente per trovarlo con i dettagli dell’oggetto.
Trova lo stato di frammentazione dell’indice usando l’istruzione T-SQL
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECT S.name as ‘Schema’,
T.name as ‘Table’,
I.name as ‘Index’,
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T su T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S su T.schema_id = S.schema_id
INNER JOIN sys.indexes I su I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
e I.name non è null
E DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc
> |
Qui, possiamo vedere che la percentuale massima di frammentazione media è notevole come 99%, che deve essere impegnata con un’azione per ridurre la frammentazione con le scelte di REBUILD o REORGANIZE. REBUILD o REORGANIZE è il comando di manutenzione dell’indice che può essere eseguito con l’istruzione ALTER INDEX. Gli utenti possono eseguire questo comando usando anche SSMS.
Rebuild and Reorganize Index using SQL Server Management Studio (SSMS)
Trova ed espandi la tabella in Object Explorer >> Apri indici >> Clicca con il tasto destro sull’indice di destinazione >> Ricostruisci o riorganizza.
Come visibile nell’immagine sopra, REBUILD e REORGANIZE sono le due scelte disponibili per eseguire l’operazione di taglio sulla pagina. Idealmente, questa operazione dovrebbe essere fatta nel tempo non di punta per evitare il suo impatto su altre transazioni e utenti. Microsoft SQL Server Enterprise Edition supporta le funzioni online e offline dell’indice con REBUILD dell’indice.
REBUILD INDEX
INDEX REBUILD abbandona sempre l’indice e lo riproduce con nuove pagine di indice. Questa attività può essere eseguita in parallelo usando un’opzione online (Enterprise Edition) con il comando ALTER INDEX, che non influisce sulle richieste e i compiti in esecuzione di una tabella simile.
REBUILD L’indice può essere impostato online o offline usando i seguenti comandi SQL:
|
1
2
3
4
5
|
–Comando base di ricostruzione
ALTER INDEX Index_Name ON Table_Name REBUILD
–REBUILD Indice con OPZIONE ONLINE
ALTER INDEX Index_Name ON Table_Name REBUILD WITH(ONLINE=ON) | WITH(ONLINE=ON)
|
Se un utente esegue il REBUILD INDEX offline, allora la risorsa oggetto (tabella) dell’indice non sarà accessibile fino alla fine del completamento del processo REBUILD. Ciò influisce anche su numerose altre transazioni che sono associate a questo oggetto. L’operazione Rebuild index ricrea l’indice. Pertanto, genera nuove statistiche e aggiunge anche i record di log dell’indice nel file di log delle transazioni del database.
Per esempio, prima di ricostruire l’indice, prendiamo l’attuale assegnazione di pagine per l’indice del database AdventureWorks, tabella Sales.OrderTracking e indice IX_OrderTracking_CarrierTrackingNumber.
|
1
2
3
4
5
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id, previous_page_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),NULL, NULL, ‘DETAILED’) IX
INNER JOIN sys.indexes si su IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‘IX_OrderTracking_CarrierTrackingNumber’
ORDER BY allocated_page_page_id
|
Qui, 1961 pagine esistono nel file di database per questo indice, e le prime 5 pagine sono le 861, 862, 1627, 1628, e 1904 in ordine di numero di pagina. Ora, ricostruiamo l’indice usando SSMS.
L’operazione di REBUILD dell’indice è completata con successo e prendiamo nuovamente i riferimenti di allocazione delle pagine per lo stesso indice con l’aiuto della stessa query T-SQL.
|
1
2
3
4
5
6
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id,
previous_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),NULL, NULL, ‘DETAILED’) IX
INNER JOIN sys.indexes si su IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‘IX_OrderTracking_CarrierTrackingNumber’
ORDER BY allocated_page_page_id
|
Dopo aver ricostruito l’indice, il numero di pagine aggiornate è 1457, che prima era 1961. Se si controllano le prime 5 pagine allocate dello stesso indice, è stato cambiato con i nuovi riferimenti di pagina. Si presume che l’indice sia stato abbandonato e rifatto. Dovremmo controllare la percentuale di frammentazione rinfrescata per lo stesso indice, e come si può vedere sotto, è 0,1% ora.
REBUILD indice clustered sulla tabella influenza anche altri indici della tabella perché il REBUILD indice clustered ricostruisce anche l’indice non clustered della tabella. Eseguire l’operazione di ricostruzione su tutti gli indici della tabella o del database insieme; un utente può usare il comando DBCC DBREINDEX().
|
1
|
DBCC DBREINDEX (‘Nome database’, ‘Nome tabella’);
|
REORGANIZED INDEX
Il comando REORGANIZE INDEX riordina la pagina dell’indice eliminando lo spazio libero o inutilizzato sulla pagina. Idealmente, le pagine di indice sono riordinate fisicamente nel file di dati. REORGANIZE non elimina e crea l’indice ma semplicemente ristruttura le informazioni sulla pagina. REORGANIZE non ha alcuna scelta offline, e REORGANIZE non influenza le statistiche rispetto all’opzione REBUILD. REORGANIZE esegue sempre online.
Per esempio, prima di eseguire il REORGANIZE sull’indice, prendiamo la lettura della frammentazione per il database ‘AdventureWorks’, tabella ‘Sales.OrderTracking’ e indice chiamato ‘IX_OrderTracking_SalesOrderID’.
Qui, la percentuale di frammentazione dell’indice è 98.39 prima di REORGANIZE. La lista qui sotto nell’immagine sono le pagine di allocazione all’indice.
|
1
2
3
4
5
6
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id,
previous_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),NULL, NULL, ‘DETAILED’) IX
INNER JOIN sys.indexes si su IX.object_id = si.object_id E IX.index_id = si.index_id
WHERE si.name = ‘IX_OrderTracking_CarrierTrackingNumber’
ORDER BY allocated_page_page_id
|
Qui, un totale di 459 pagine sono elencate nell’immagine sopra, e le prime cinque pagine sono 1065, 1068, 1069, 1944 e 1945. Ora, eseguiamo il comando REORGANIZE sull’indice usando la seguente istruzione T-SQL e guardiamo di nuovo l’allocazione delle pagine.
|
1
|
ALTER INDEX IX_OrderTracking_SalesOrderID ON Sales.OrderTracking REORGANIZE
|
Qui, il numero totale di pagine è diminuito a 331, che era 459 prima. Inoltre, non vediamo nuove pagine nella lista delle prime cinque pagine, il che implica che i dati sono solo ristrutturati – non riempiti di nuovo. Anche tu potresti vedere nuove pagine, succede nella situazione in cui il grande indice è pesantemente frammentato, e il rimescolamento dei dati usa una nuova pagina.
Per eseguire l’operazione REORGANIZE index su tutti gli indici della tabella o del database insieme, l’utente può usare il comando DBCC INDEXDEFRAG():
|
1
|
DBCC INDEXDEFRAG(‘DatabaseName’, ‘TableName’);
|
Come visto, c’è una differenza sostanziale tra l’indice REBUILD e REORGANIZE. Qui gli utenti hanno la possibilità di scegliere una delle alternative in base alla percentuale di frammentazione dell’indice. Possiamo capire che non ci sono standard documentati; tuttavia, l’amministratore del database segue l’equazione standard secondo il requisito della dimensione dell’indice e il tipo di informazioni.
Determinazione usuale dell’uso dell’equazione :
- Quando la percentuale di frammentazione è tra 15-30: REORGANIZE
- Quando la frammentazione è maggiore di 30: REBUILD
L’opzione REBUILD è più utile con l’opzione ONLINE quando il database non è disponibile per fare la manutenzione dell’indice nelle ore non di punta.
Conclusione
La frammentazione dell’indice è una frammentazione interna nel file di dati. I parametri fondamentali per una performance veloce del vostro database sono l’architettura del database, il design del database e la scrittura delle query. Un buon design dell’indice con manutenzione aumenta sempre le prestazioni delle query nel motore del database.
- Autore
- Post recenti
Vedi tutti i post di Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- Come automatizzare il partizionamento delle tabelle in SQL Server – 7 luglio 2020
- Configurare SQL Server Always On Availability Groups su AWS EC2 – 6 luglio 2020