SQL Server: New Tools to Diagnose Index Health

Articolo
08/24/2016

SQL Server

Nuovi strumenti per diagnosticare l'integrità dell'indice

Randy Dyess

Panoramica:

Frammentazione dell'indice
Utilizzo dell'indice
Attività operativa dell'indice

Scarica il codice per questo articolo: DyessSQLIndex2007_03.exe (151KB)

Una delle voci di maggior interesse in qualsiasi elenco di controllo per la regolazione delle prestazioni di SQL Server è la modifica degli indici nel database. La capacità di Query Optimizer di SQL Server di fare buon uso degli indici durante l'esecuzione di una query dipende non solo dalla creazione di indici efficaci ma

anche dal buono stato di questi ultimi. Le serie di viste di gestione dinamiche (DMV) e funzioni di gestione dinamiche (DMF) introdotte in SQL Server™ 2005, consentono agli amministratori di database di determinare l'efficacia degli indici e scoprirne i problemi di prestazioni.

DMV e DMF consentono di esaminare un server e di ricavarne informazioni sullo stato, per monitorarne l'integrità e le prestazioni e diagnosticare i problemi dell'istanza server. Gli amministratori di database che hanno familiarità con le versioni precedenti di SQL Server troveranno che queste DMV e DMF sostituiscono i comandi DBCC per l'esecuzione di alcune stored procedure di sistema, operazioni di query di molte tabelle di sistema e la cattura di eventi con SQL Profiler.

Tre funzioni e viste principali, per l'esattezza sys.dm_db_index_physical_stats, sys.dm_db_index_usage_stats, e sys.dm_db_index_operational_stats, sono disponibili per scoprire se gli indici stanno funzionando come previsto. Permettono di riesaminare l'I/O e gli schemi di blocco degli indici e capire se questi siano utilizzati da Query Optimizer in maniera da non produrre conflitti inutili nel database.

Frammentazione dell'indice

La funzione DMF sys.dm_db_index_physical_stats è stata creata per sostituire DBCC SHOWCONTIG e indica la frammentazione dell'indice. A differenza di DBCC SHOWCONTIG, che posiziona un blocco condiviso sulla tabella contenente l'indice, sys.dm_db_index_physical_stats inserisce soltanto un tipo di blocco condiviso (IS) che riduce notevolmente il blocco della tabella durante l'esecuzione della funzione.

Per determinare la frammentazione di un indice quando si utilizza sys.dm_db_index_physical_stats, bisogna esaminare una combinazione di tre colonne nell'output della funzione. La frammentazione logica degli indici (frammentazione extent di heap) può essere determinata osservando il valore riportato nella colonna avg_fragmentation_in_percent. La frammentazione logica è la percentuale di pagine fuori posto nel livello foglia dell'indice, mentre la frammentazione extent è la percentuale di extent fuori posto nel livello foglia dell'indice. Le frammentazioni logica e extent possono influire sulle prestazioni di un indice in quanto richiedono I/O e movimento della testina del disco aggiuntivi, poiché la testina del disco deve cambiare di posizione per leggere le pagine in sequenza. L'ideale è mantenere le frammentazioni logica ed extent più vicine possibile al valore zero.

La frammentazione interna di un indice è la percentuale in cui la pagina è stata riempita. Nonostante l'obiettivo sia un livello di riempimento pagina più elevato possibile, è essenziale equilibrare il livello di riempimento pagina con il numero di inserimenti nelle pagine di indice, per mantenere il numero di divisioni di pagina al minimo.

L'argomento avg_page_space_used_in_percent di sys.dm_db_index_physical_stats deve essere esaminato per determinare il livello di riempimento pagina dell'indice. Per determinare correttamente quanto questo valore sia vicino al 100 percento, bisogna regolare il fillfactor dell'indice e osservare il numero di divisioni di pagina che si sta verificando. A un certo punto, il numero di divisioni di pagina inizierà ad aumentare in maniera notevole e questo indicherà che il fillfactor dell'indice è stato impostato su un valore superiore a quanto opportuno. La regolazione di un fillfactor di indice richiede tempo e verifiche e non deve essere eseguita senza un'opportuna pianificazione. Per gli indici privi di inserimenti casuali nell'indice è possibile impostare il fillfactor su 100, senza doversi preoccupare dell'aumento delle divisioni di pagina.

Per determinare i livelli di frammentazione di tutti gli indici nella tabella AdventureWorks.HumanResources.Employee, è possibile utilizzare un'istruzione come la seguente:

SELECT * 
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,OBJECT_ID('HumanResources.Employee')
,NULL 
-- NULL to view all indexes; 
-- otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') -- We want all information

Utilizzare questa DMF per determinare automaticamente gli indici che devono essere ricostruiti, quelli che devono essere riorganizzati e quelli che non hanno bisogno di manutenzione. Esaminare i valori delle colonne avg_page_space_used_in_percent e avg_fragmentation_in_percent di questa DMF per la frammentazione degli indici che supera la soglia di logica e densità accettate, può aiutare a determinare quale operazione eseguire sull'indice.

In funzione dello stato degli indici, l'esempio della Figura 1 potrebbe non restituire dati nella copia del database di esempio AdventureWorks, tuttavia può essere facilmente adattato ad altri database.

Figure 1 Analisi del livello di riempimento e della frammentazione della pagina

--Reorganize the following indexes in the AdventureWorks database
USE AdventureWorks
GO

SELECT OBJECT_NAME([object_id]) AS 'Table Name',
index_id AS 'Index ID'
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 10 
AND avg_fragmentation_in_percent < 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 75 
AND avg_page_space_used_in_percent > 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes

--Rebuild the following indexes in the AdventureWorks database
USE AdventureWorks
GO

SELECT OBJECT_NAME([object_id]) AS 'Table Name',
index_id AS 'Index ID'
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes

Sarebbe facile memorizzare i risultati delle query in una variabile di tabella e poi eseguire un ciclo nella variabile di tabella per costruire una stringa dinamica per la corretta istruzione ALTER INDEX (vedere la Figura 2).

Figure 2 Creazione di una stringa dinamica ALTER INDEX

--Rebuild the following indexes in the AdventureWorks database
USE AdventureWorks
GO

--Table to hold results
DECLARE @tablevar TABLE(lngid INT IDENTITY(1,1), objectid INT,
index_id INT)

INSERT INTO @tablevar (objectid, index_id)
SELECT [object_id],index_id
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes

SELECT 'ALTER INDEX ' + ind.[name] + ' ON ' + sc.[name] + '.'
+ OBJECT_NAME(objectid) + ' REBUILD' 
FROM @tablevar tv
INNER JOIN sys.indexes ind
ON tv.objectid = ind.[object_id]
AND tv.index_id = ind.index_id
INNER JOIN sys.objects ob
ON tv.objectid = ob.[object_id]
INNER JOIN sys.schemas sc
ON sc.schema_id = ob.schema_id

Utilizzo dell'indice

Mentre sys.dm_db_index_physical_stats è una sostituzione potente di DBCC SHOWCONTIG e può aiutare a determinare l'integrità di un indice, spesso è necessario affrontare il problema più complicato di scoprire quali indici siano utili per le query eseguite su una tabella. Spesso gli sviluppatori o gli amministratori di database creano indici su una tabella che pensano sarà utilizzata da Query Optimizer durante l'esecuzione di una query. Nelle versioni precedenti di SQL Server, era molto più complicato sapere se questi indici erano effettivamente utilizzati. Era necessario eliminare l'indice e vedere se la prestazione delle query ne veniva influenzata oppure catturare i progetti di esecuzione delle query e ricercare poi l'utilizzo dell'indice.

Esiste una nuova vista di gestione dinamica, view-sys.dm_db_index_usage_stats, che rende più facile capire in che modo gli indici siano utilizzati da Query Optimizer e quali siano le query eseguite su una tabella. Questa vista può essere esaminata per determinare l'utilità di un indice, consentendo di eliminare quelli non utilizzati da Query Optimizer. Non sarà più necessario preoccuparsi se gli indici stanno sprecando spazio di archiviazione o se la manutenzione di indici inutilizzati stia diminuendo le prestazioni del database.

Esaminando l'output di questa DMV per gli indici con ricerche e scansioni pari a zero, è possibile determinare se un indice è stato utilizzato dall'ultimo avvio di SQL Server. Bisogna tenere presente, tuttavia, che molte DMV e DMF non sono mantenute e si riazzerano una volta che SQL Server viene riavviato. Questo vale specialmente quando si utilizzano DMV o DMF per determinare l'utilizzo di un indice. È possibile semplicemente che l'indice non sia stato utilizzato dall'ultimo riavvio del servizio e che sia necessario solo per query a fine settimana, fine mese o ogni tre mesi.

Per visualizzare tutti gli indici di un'istanza che non sono stati utilizzati dall'ultimo riavvio del servizio SQL Server, è possibile utilizzare l'istruzione seguente:

SELECT DB_NAME(database_id),OBJECT_NAME([object_id])
FROM sys.dm_db_index_usage_stats
WHERE user_seeks = 0
AND user_scans = 0
AND user_lookups = 0
AND system_seeks = 0
AND system_scans = 0
AND system_lookups = 0

Attività operativa dell'indice

La funzione DMF sys.dm_db_index_operational_stats sarà molto utile per comprendere l'attività operativa degli indici. È possibile utilizzarla per osservare le attività di I/O, blocco, latch e del metodo di accesso su ogni indice di un database, per capire come siano utilizzati gli indici e diagnosticare i problemi di blocco degli indici dovuti ad attività estese di I/O o all'esistenza di un'area sensibile nell'indice.

Utilizzare le colonne di attesa latch di questa DMF per stabilire la durata delle operazioni di READ e WRITE per ottenere l'accesso alle risorse dell'indice. Ciò consente di determinare se il sottosistema del disco utilizzato per memorizzare l'indice sia adeguato per le attività di I/O dell'indice. Indica inoltre se il progetto e l'utilizzo dell'indice abbiano introdotto un'area sensibile dove attività estese in una o più pagine di un indice causino un conflitto nei dati contenuti in quelle pagine. Un conflitto di questo tipo conduce spesso a un blocco eccessivo delle operazioni di READ e WRITE in quest'area.

Nella Figura 3 viene illustrato come determinare i modelli di blocco e I/O di tutti gli indici nella tabella AdventureWorks.HumanResources.Employee.

Figure 3 Determinazione dei modelli di blocco e I/O

SELECT page_latch_wait_count --page latch counts
,page_latch_wait_in_ms --page latch wait times
,row_lock_wait_in_ms --row lock wait times
,page_lock_wait_in_ms --page lock wait times
,row_lock_count --row lock counts
,page_lock_count --page lock counts
,page_io_latch_wait_count --I/O wait counts
,page_io_latch_wait_in_ms --I/O wait times
FROM sys.dm_db_index_operational_stats (DB_ID('AdventureWorks')
,OBJECT_ID('HumanResources.Employee')
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
)

Ulteriori informazioni

Le DMV e DMF introdotte in questo articolo hanno molti altri usi oltre a quelli descritti. Consultare gli articoli della Documentazione in linea di SQL Server relativi alle funzioni e viste indicate nell'argomento complementare "Ulteriori riferimenti" per venire a conoscenza della vasta gamma di informazioni che si possono ottenere e analizzare dal loro utilizzo.

Per conoscere altre DMF e DMV di indici che non sono stati trattati in quest'articolo, vedere il post del blog del team di ottimizzazione delle query di SQL Server presso blogs.msdn.com/queryoptteam/570176.aspx.

Ulteriori riferimenti

Randy Dyess è mentore presso Solid Quality Learning ed è specializzato nei sistemi OLTP di SQL Server. Dyess è autore di numerosi libri e articoli dedicati a SQL Server. È fondatore e autore principale di www.TransactSQL.Com e www.Database-Security.Info.

Risorse aggiuntive