Di: Brady Upton | Aggiornato: 2016-06-22 | Comments (5) | Related: More > Frammentazione e manutenzione dell’indice
Problema
Abbiamo tutti sentito parlare di database/indice frammentazione (e se non l’hai fatto, continuereading), ma che cos’è? E ‘ un problema? Come faccio a sapere se risiede nel mio database?Come faccio a risolvere il problema? Queste domande potrebbero essere un suggerimento tutto in sé, ma cercherò di dare un’idea di ciascuno in questo suggerimento.
Soluzione
Senza entrare in molti dettagli, SQL Server memorizza i dati su pagine 8KB. Quando inseriamo i dati in una tabella, SQL Server allocherà una pagina per memorizzare quei dati senza che i dati inseriti siano più di 8 KB in cui si estenderebbero su più pagine.Ogni pagina è assegnata a una tabella. Se creiamo 10 tabelle, avremo 10 pagine diverse.
Quando si inseriscono i dati in una tabella, i dati andranno prima al file di registro delle transazioni. Thetransaction log file è un significato record sequenziale come si inserisce, aggiornare ed eliminare recordsthe log registrerà queste transazioni dall’inizio alla fine. Il file di dati d’altra parte non èsequenziale. Il file di registro scaricherà i dati nel file di dati creando pagine ovunque nel luogo.
Ora che abbiamo un’idea di come vengono memorizzati i dati, cosa ha a che fare con la frammentazione?
Esistono due tipi di frammentazione: Frammentazione interna e Frammentazione esterna.
Frammentazione interna di SQL Server
La frammentazione interna di SQL Server è causata da pagine che hanno troppo spazio libero.Facciamo finta che all’inizio della giornata abbiamo una tabella con 40 pagine che sono100% pieno, ma entro la fine della giornata abbiamo una tabella con 50 pagine che sono solo80% pieno a causa di varie istruzioni di cancellazione e inserimento durante il giorno.Ciò causa un problema perché ora quando abbiamo bisogno di leggere da questa tabella abbiamo toscan 50 pagine invece di 40 che dovrebbe comportare una diminuzione delle prestazioni.Vediamo un esempio veloce e sporco.
Diciamo che ho la seguente tabella con una chiave primaria e un indice non cluster su FirstNameand LastName:
Parlerò di modi per analizzare la frammentazione più avanti in questo suggerimento, ma per orapossiamo fare clic destro sull’indice, fare clic su Proprietà e frammentazione per vedere fragmentationand pagina pienezza. Questo è un indice nuovo di zecca quindi è a 0% frammentazione.
Inseriamo 1000 righe in questa tabella:
INSERT INTO Person VALUES('Brady', 'Upton', '123 Main Street', 'TN', 55555)GO 1000
Ora, ricontrolliamo il nostro indice:
Puoi vedere che il nostro indice diventa frammentato del 75% e la percentuale media di pagine complete (pienezza della pagina) aumenta all ‘ 80%. Questa tabella è ancora così piccola che il 75% di fragmentation probabilmente non causerebbe problemi di prestazioni, ma man mano che la tabella aumenta di dimensionee i conteggi delle pagine aumentano, è possibile che le prestazioni diminuiscano. Puoi anche vedere da thescreenshot sopra che questa tabella è passata da 0 pagine a 4.
Frammentazione esterna di SQL Server
La frammentazione esterna è causata da pagine fuori servizio. Let’spretend all’inizio della giornata abbiamo un tavolo perfettamente ordinato. Durante il giorno emettiamo centinaia di istruzioni di aggiornamento che lasciano eventualmente spazio vuoto su una pagina e cercano di inserire lo spazio in altre pagine. Ciò significa che il nostro storage deve saltare per ottenere i dati necessari invece di leggere in una direzione.
Analizzare la frammentazione di SQL Server
Quindi la frammentazione è un problema? Credo di si’. Se riesci a memorizzare l’intero database in memoria o se il tuo database è di sola lettura, non mi preoccuperei, ma la maggior parte di noi non ha quel lusso.Ho lavorato su migliaia di server e l’analisi dei livelli di frammentazione è una delle prime cose che guardo. Infatti, solo fissando la frammentazione, ho visto fino a200 % miglioramenti nelle prestazioni delle query.
Parlando di analizzare i livelli di frammentazione ci si potrebbe chiedere come possiamo fare questo. Bene, ci sono alcuni modi DB
DBCC SHOWCONTIG-questa funzione è vecchia e verrà rimossa nelle versioni future di SQL Server, ma se stai ancora usando SQL Server 2000 o inferiore, questo ti aiuterà. Invece di scrivere su di esso, ti indicherò qui o puoi controllare il suggerimento di Chad Boyd qui. Entrambi sono buone risorse.
sys.dm_db_index_physical_stats – Introdotto in SQL Server 2005, questo dynamic managementview (DMV) restituisce le informazioni di dimensione e frammentazione per i dati e gli indici della tabella o vista specificata.
SELECT OBJECT_NAME(ips.OBJECT_ID) ,i.NAME ,ips.index_id ,index_type_desc ,avg_fragmentation_in_percent ,avg_page_space_used_in_percent ,page_countFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ipsINNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)ORDER BY avg_fragmentation_in_percent DESC
Questo è probabilmente il metodo più utilizzato per analizzare la frammentazione. Puoi vedere dallo screenshot qui sopra che ho un indice chiamato IX_RPCust_1 sulla tabella theRPCust che è frammentato al 98,83%. Puoi vedere maggiori informazioni su questo DMVHIDA Arshad Ali.
Software di terze parti-Software di terze parti vi aiuterà a identificare fragmentationso non è necessario. Greg Robidoux offre un buon consiglio su SQL Defrag Managerhere di Idera.
Correzione della frammentazione di SQL Server
Ora che abbiamo trovato la frammentazione nel database, come risolverlo? Likeanalyzing indici, ci sono diversi modi.
SQL Server Maintenance plans-I piani di manutenzione vengono forniti con SqlServer e sono piacevoli per alcune attività. La manutenzione dell’indice a mio parere non è una deiloro. È possibile aggiungere un’attività rebuild index o riorganizzare l’attività index in maintenanceplan, ma il problema è che non è possibile applicare alcuna logica al piano. Withoutlogic, il piano ricostruirà / riorganizzerà OGNI indice. Alcuni indici potrebbero non essere frammentati, quindi non è necessario ricostruirli. Alcuni indici possono avere una frammentazione minimain modo che starebbero bene con una riorganizzazione. Non c’è modo di specificarlo in un maintenanceplan. InSQL Server 2016 ci sono nuove opzioni per la gestione della frammentazione.
Script personalizzato-Gli script personalizzati sono probabilmente la strada da percorrere perché si può applicare la logica per controllare la frammentazione dell’indice e quindi decidere se saltare l’indice, riorganizzare o ricostruire. Uso le sceneggiature di Hallengren e fanno esattamente quello che mi serve. MSSQLTips.com ha anche theseresources-SQL Server Frammentazione e suggerimenti per la manutenzione dell’indice.
Software di terze parti-Software di terze parti vi aiuterà a deframmentare indexesso non è necessario.
Passi successivi
- Questo suggerimento ti dà un’idea di alcuni dei componenti coinvolti nell’indexfragmentation. MSSQLTips.com offre punte più complete messe a fuoco su eachof i soggetti che ho citato qui.
- Assicurati di controllare il tutorial sulla frammentazione di SQL Server di Chad Boyd che offre una grande comprensione di numerosi argomenti riguardanti la frammentazione
Ultimo aggiornamento: 2016-06-22
About the author
Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.View all my tips
- More SQL Server DBA Tips…