Free Press

Lo scopo della creazione degli indici SQL sulle tabelle del database è quello di migliorare le prestazioni delle query che leggono i dati dalla tabella base, accelerando i processi di recupero dei dati. Ma queste tabelle transazionali non sono statiche e cambiano molto frequentemente nel tempo. Questi cambiamenti che vengono eseguiti sulla tabella del database includono l’aggiunta di nuovi record, la modifica o la cancellazione dei record esistenti. Poiché questi cambiamenti devono essere replicati agli indici delle tabelle correlate, l’indice della tabella si frammenterà nel tempo.

Panoramica della frammentazione dell’indice SQL Server

Ci sono due tipi principali di frammentazione dell’indice SQL; frammentazione interna ed esterna. La frammentazione interna è causata dall’inserimento di un nuovo record o dall’aggiornamento di un record esistente con valori che non si adattano allo spazio libero attuale nella pagina dei dati e risultano nella divisione della pagina in due pagine per adattarsi al nuovo valore. In questo caso, il motore di SQL Server sposterà circa il 50% dei dati dalla pagina corrente alla nuova pagina di dati che viene generata dall’operazione di divisione, al fine di mantenere l’albero degli indici SQL bilanciato. La frammentazione interna può essere causata anche dalle operazioni di cancellazione casuale che provocano spazio vuoto sulle pagine di dati. Questo tipo di operazioni lascia le pagine di dati non riempite e si traduce in una maggiore dimensione dell’indice SQL Server e in prestazioni più lente a causa della necessità di leggere più pagine di dati per recuperare i dati richiesti.

La frammentazione esterna è causata quando le pagine di dati dell’indice SQL Server sono sparse sul file fisico del database, a causa del fatto che l’ordine logico dell’indice SQL Server non corrisponde all’ordine fisico sull’unità disco sottostante. La frammentazione esterna può essere causata da operazioni di cancellazione casuale che lasciano un certo numero di pagine di estensione vuote pur avendo l’estensione stessa riservata. Questo tipo di frammentazione si traduce in una degradazione delle prestazioni, in quanto richiederà più tempo e consumerà più risorse nel saltare tra le pagine di dati sparsi per recuperare i dati richiesti dall’unità disco sottostante.

Il motivo per cui ci preoccupiamo molto della frammentazione dell’indice SQL è che l’indice che avete creato per migliorare le prestazioni del sistema, può degradare le prestazioni delle vostre query dopo essere stato altamente frammentato. Immaginate la differenza di tempo e di numero di letture su disco tra la lettura di un numero specifico di righe da un indice SQL sano in cui i dati sono memorizzati in pagine di dati contigue all’interno del file di database, e la lettura dello stesso numero di righe da un indice SQL Server altamente frammentato, in cui i dati sono sparsi in pagine di dati non contigue all’interno del file di database.

Se si trova che l’indice SQL della tabella è frammentato, è necessario risolvere il problema della frammentazione eseguendo SQL Server Index Reorganization o Index Rebuild, in base alla percentuale di frammentazione dell’indice SQL. Per esempio, un indice SQL Server con una percentuale di frammentazione inferiore al 30% può essere riorganizzato, mentre l’indice SQL con un livello di frammentazione superiore al 30% dovrebbe essere ricostruito.

• Nota: controlla Mantenere gli indici SQL Server per maggiori informazioni.

È possibile superare proattivamente i problemi di frammentazione dell’indice SQL Server e di divisione delle pagine progettando l’indice correttamente e impostando le opzioni di creazione dell’indice Fill Factor e pad_index con i valori propri.

• Nota: controllare Operazioni indice SQL Server per ulteriori informazioni.

SQL Server Index Fragmentation Report

Non è considerato una buona pratica creare un indice SQL nella tua tabella di database per trarre vantaggi dal miglioramento delle operazioni di recupero dei dati e lasciare quell’indice SQL Server per sempre senza monitorare continuamente la sua frammentazione e mantenerlo.

SQL Server ci fornisce diversi metodi che possono essere utilizzati per controllare la percentuale di frammentazione dell’indice SQL.

Proprietà indice SQL Server

Il primo metodo è la pagina Frammentazione della finestra Proprietà indice SQL Server. Clicca con il tasto destro del mouse sull’indice che devi controllare e scegli l’opzione Proprietà. Dalla pagina delle proprietà dell’indice di SQL Server, sfoglia la pagina Frammentazione, e vedrai la percentuale di frammentazione dell’indice e quanto le pagine dell’indice di SQL Server sono piene sotto la sezione Frammentazione, come mostrato di seguito:

In pochi passi, puoi facilmente controllare la percentuale di frammentazione dell’indice di SQL Server dell’indice selezionato dalla finestra delle proprietà dell’indice. Ma cosa succede se avete bisogno di avere una panoramica della percentuale di frammentazione per tutti gli indici della tabella o per tutti gli indici delle tabelle del database? In questo caso, è necessario andare a controllarli uno per uno. Di conseguenza, una sola settimana di lavoro non sarà sufficiente per un rapporto sul database!

sys.dm_db_index_physical_stats

Un altro metodo che può essere usato per controllare la percentuale di frammentazione degli indici del database è l’interrogazione della funzione di gestione dinamica sys.dm_db_index_physical_stats, che è stata introdotta per la prima volta in SQL Server 2005, come sostituzione del deprecato comando DBCC SHOWCONTIG. Fornisce informazioni sulla dimensione e la percentuale di frammentazione degli indici del database.

Per ottenere informazioni significative sulla percentuale di frammentazione di tutti gli indici sotto il database specificato dal DMF sys.dm_db_index_physical_stats, è necessario unirlo al sys.indexes DMV, come nello script T-SQL qui sotto:

SELECT OBJECT_NAME(Index_Info.OBJECT_ID) AS TableName ,Index_Info.name AS IndexName ,Index_Stat.index_type_desc AS IndexType ,Index_Stat.avg_fragmentation_in_percent IndexFragmPercentFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) Index_StatINNER JOIN sys.indexes Index_Info ON Index_Info.object_id = Index_Stat.object_id AND Index_Info.index_id = Index_Stat.index_idORDER BY IndexFragmPercent DESC

In base alle vostre capacità di sviluppo SQL, potete eseguire il filtraggio di cui avete bisogno per recuperare la frammentazione degli indici SQL nell’ambito richiesto, senza l’opzione di ottenere un riepilogo per l’intera istanza dei database, tenendo conto che i dati mostrati sono dall’ultimo riavvio del servizio SQL Server. Il risultato nel nostro caso sarà come:

SQL Server Index Physical Statistics Standard Report

SQL Server ci fornisce diversi report standard che contengono utili informazioni statistiche sul database selezionato. Il rapporto Index Physical Statistics, è uno dei rapporti standard di SQL Server che restituisce informazioni statistiche sulle partizioni dell’indice SQL Server, la percentuale di frammentazione e il numero di pagine su ogni partizione dell’indice SQL a livello di database, dall’ultimo riavvio del servizio SQL Server, con raccomandazioni su come risolvere il problema della frammentazione dell’indice, se esiste, in base alla percentuale di frammentazione dell’indice SQL Server.

Per visualizzare il rapporto SQL Server Index Physical Statistics, clicca con il tasto destro del mouse sul tuo database, scegli Reports -> Standard Reports e seleziona il rapporto Index Physical Statistics, come mostrato di seguito:

E il rapporto generato a livello di database, che non può essere filtrato e deve espandere manualmente i risultati degli indici di ogni tabella, sarà come mostrato di seguito:

ApexSQL Defrag

ApexSQL Defrag è uno strumento di deframmentazione dell’indice SQL di terze parti che può essere facilmente utilizzato per controllare la frammentazione dell’indice di SQL Server e le informazioni di utilizzo ed eseguire o programmare la correzione adatta per il problema della frammentazione dell’indice.

Da una posizione centrale, è possibile analizzare e rivedere la frammentazione e le informazioni di utilizzo a diversi livelli, tra cui la frammentazione dell’indice SQL e le informazioni di utilizzo a livello di indice SQL Server, a livello di tabella, a livello di database e infine a livello generale di istanza SQL Server. Ci fornisce vari tipi di filtri che possono essere utilizzati per visualizzare e mantenere solo gli indici di destinazione.

ApexSQL Defrag ci permette anche di configurare l’impostazione del fattore di riempimento per gli indici delle tabelle, creare avvisi per tutti i lavori di deframmentazione e le politiche e inviare notifiche via e-mail per i lavori riusciti o falliti.

Le informazioni visualizzate forniranno una buona visione delle dimensioni dell’indice SQL e la percentuale di frammentazione, che non sarà influenzata da qualsiasi riavvio del servizio SQL Server o del server. Questo perché queste informazioni saranno memorizzate in un database di deposito centrale per lo strumento ApexSQL Defrag.

Installazione di ApexSQL Defrag

ApexSQL Defrag può essere facilmente scaricato dal Download Center di ApexSQL e installato sul tuo server seguendo la semplice procedura guidata di installazione, come mostrato di seguito:

Per prima cosa, ti verrà chiesto di specificare l’account di servizio che verrà utilizzato come contesto di esecuzione per l’agente ApexSQL Defrag e che trasmette i comandi all’istanza di SQL Server, come segue:

Dopo aver specificato l’account di servizio di ApexSQL Defrag, ti verrà chiesto di specificare dove installare lo strumento ApexSQL Defrag e se creare un’icona di collegamento per lo strumento sul desktop, come mostrato di seguito:

Controllando che il percorso fornito contenga almeno 127MB di spazio libero, ApexSQL Defrag verrà installato con successo sul tuo server. E verrà visualizzata la seguente notifica, che consente di avviare direttamente ApexSQL Defrag:

Se si esegue ApexSQL Defrag per la prima volta, verrà chiesto di confermare la creazione del database del repository centrale, dove verranno memorizzati i dati storici e di configurazione, come segue:

Add New Server

Dopo aver creato il database del repository, ApexSQL Defrag verrà avviato senza istanza SQL. Per controllare la percentuale di frammentazione degli indici SQL su una specifica istanza di SQL Server, è necessario aggiungere tale istanza allo strumento. Per farlo, fare clic sul pulsante Add, sotto la scheda Home, come mostrato di seguito:

Nella finestra Connect to SQL Server, verrà chiesto di fornire il nome dell’istanza di SQL Server che verrà utilizzata e le credenziali necessarie per la connessione a tale server, come segue:

SQL Server Index Analysis

Quando il server viene aggiunto, è possibile eseguire un nuovo controllo per tutte le informazioni sulla frammentazione degli indici SQL dalla scheda Fragmentation sottostante:

ApexSQL Defrag offre la possibilità di analizzare tutti gli indici di quell’istanza in base a tre modalità:

• DETTAGLIATO: in cui tutti i dati e le pagine dell’indice SQL saranno letti durante il processo di scansione
• SAMPLED: in cui solo l’1% delle pagine sarà letto, tenendo conto che l’indice SQL Server contiene più di 10.000 pagine
• LIMITATO: in cui saranno lette solo le pagine situate al livello padre del b-tree

Per analizzare le informazioni dell’indice di SQL Server dall’istanza di SQL Server collegata, cliccare sul pulsante Analyze, sotto la scheda Fragmentation, e scegliere se eseguire un’analisi Fast o Deep. Eseguiremo un’analisi profonda per ottenere informazioni complete, come mostrato di seguito:

Continua a monitorare la barra di progresso sul lato inferiore dello strumento per controllare il progresso dell’analisi, come segue:

E un rapporto completo, che contiene informazioni complete su tutti gli indici SQL creati in ogni database di questa istanza di SQL Server, verrà visualizzato come mostrato di seguito:

Con la possibilità di immergersi tra i diversi livelli; istanza di SQL Server, database, tabella o indice, con un solo clic sotto la stessa finestra centrale, e filtrare il risultato recuperato in base a un livello di frammentazione specifico o soglia, come mostrato di seguito:

Inoltre, è possibile trascinare qualsiasi colonna dalle colonne del rapporto per raggruppare il rapporto complessivo in base ai valori di quella colonna, come l’esempio seguente, in cui abbiamo raggruppato i dati del rapporto in base al nome della tabella:

ApexSQL Defrag Reports

ApexSQL Defrag ci fornisce una serie di rapporti che mostrano informazioni statistiche sugli indici del database. Questi rapporti possono essere configurati e controllati nella scheda Rapporti qui sotto:

Il primo rapporto è il rapporto Totale, che mostra una rappresentazione grafica della frammentazione degli indici SQL e dell’uso dello spazio su disco al livello selezionato. Per esempio, se clicchi sul nome dell’istanza, il report disegnerà il grafico della percentuale di frammentazione e il grafico dell’utilizzo dello spazio a livello dell’istanza di SQL Server, come segue:

Se clicchi su qualsiasi database dalla finestra Server Explorer, il report cambierà automaticamente la visualizzazione per mostrare il grafico a livello di database, come mostrato di seguito:

Il secondo report, che può essere visualizzato anche a diversi livelli, è il report Statistiche. Questo rapporto mostra informazioni statistiche sulla dimensione e la frammentazione totale degli indici e la distribuzione dei tipi di indice al livello selezionato, come mostrato di seguito:

Il terzo rapporto è il rapporto Top 10, che visualizza i primi 10 indici clustered e non clustered con la dimensione maggiore e la più alta percentuale di frammentazione degli indici SQL, al livello specificato. Per esempio, il seguente rapporto mostra i primi 10 indici costosi, basati sulla dimensione e la frammentazione, a livello di istanza del server SQL:

Scendendo ad un database specifico, il rapporto mostrerà automaticamente i primi 10 indici costosi, basati sulla dimensione e la frammentazione, al livello del database selezionato, come mostrato di seguito:

Scendendo profondamente a una specifica tabella sotto quel database, il rapporto mostrerà i primi 10 indici costosi, basati sulla dimensione e la frammentazione, come mostrato di seguito:

ApexSQL Defrag Report Export

ApexSQL Defrag ci permette di esportare i rapporti sulla frammentazione in diversi formati di file, tra cui PDF, IMG, HTML, CS e XML, con la possibilità di personalizzare le impostazioni del rapporto prima che venga generato. Sotto la scheda Rapporti, fare clic sul pulsante Opzioni:

Dalla finestra Modifica opzioni di esportazione, è possibile configurare le diverse opzioni, a partire dalla convenzione di denominazione del file generato, il percorso dove il file verrà salvato, l’orientamento della pagina e infine il contenuto di ogni rapporto, come mostrato di seguito:

Per esempio, per esportare il rapporto sul Totale generato al livello specificato, clicca sul pulsante Esporta, sotto la scheda Rapporti, e scegli il tipo di file, in cui questo rapporto sul Totale sarà esportato. In pochi secondi, il rapporto verrà generato, chiedendoti se vuoi aprire il rapporto generato. Clicca su Sì per aprire direttamente il rapporto, come mostrato di seguito:

Inoltre, è possibile esportare il rapporto Top 10 al livello specificato, cliccando sul pulsante Export, sotto la scheda Reports, e specificare il tipo di file di esportazione, e il rapporto verrà generato rapidamente, chiedendoti se vuoi visualizzare direttamente il rapporto generato. Fare clic su Yes per aprire il generato, come mostrato di seguito:

È chiaro dagli esempi precedenti, che possiamo facilmente utilizzare lo strumento ApexSQL Defrag per rivedere e analizzare le informazioni sulla percentuale di frammentazione dell’indice SQL e immergerci al diverso livello a partire dal livello dell’istanza di SQL Server fino al livello dell’indice SQL Server e visualizzarle in diversi formati esportabili, senza che le informazioni visualizzate siano influenzate da qualsiasi riavvio del server o servizio, poiché saranno memorizzate in un database repository.