Frammento di file fisici di database SQL

So che ci sono davvero tre tipi di frammentazione che devo essere preoccupati come un DBA:

  1. Fragmentazione dell'indice nei file di dati SQL, inclusa la frammentazione dell'indice di cluster (tabella). Identificare questo utilizzando DBCC SHOWCONTIG (in SQL 2000) o sys.dm_ db_ index_ physical_ stats (nel 2005+).

  2. SQL Server 2005 Service Pack 3 non verrà installato
  3. Liberare lo spazio su disco
  4. Server SQL in VMware
  5. È ansible installare MS SQL Management Studio 2012 senza installare SQL Server?
  6. Diagnostica di elaborazione lenta di dati su un VM
  7. Quando si è configurato con successo SQL Server per il mirroring del database, si è verificato un errore che non è configurato per il mirroring del database
  8. Frammento VLF all'interno di file di log SQL. Esegui DBCC LOGINFO per vedere quanti VLF sono presenti in ciascuno dei file di log SQL.

  9. Frammentazione dei file fisici dei file di database sul disco rigido. Diagnose questo problema utilizzando l'utilità "Defragmentazione disco" in Windows. (ispirato da questo eccellente blog post )

Molta attenzione è riservata alla frammentazione dell'indice (vedi questa eccellente risposta da parte di Paul Randall), quindi non è la messa a fuoco della mia domanda.

So che posso impedire la frammentazione fisica (e la frammentazione VLF) quando il database viene creato originariamente pianificando un file di dati e un formato log logico ragionevole perché questa frammentazione si verifica più spesso da frequenti cresce e si restringe, ma ho alcune domande su come risolvere frammentazione fisica una volta identificato:

  • Innanzitutto, è la frammentazione fisica anche rilevante su una SAN Enterprise? Posso utilizzare / utilizzare l'unità di deframmentazione di Windows in un'unità SAN o il team SAN utilizzare le utilità di deframmentazione interna? L'analisi di frammentazione che ottengo dallo strumento Windows è perfettamente esatta quando viene eseguita su un'unità SAN?

  • Quanto grande un accordo è la frammentazione fisica sulle performance SQL? (Supponiamo un arrays di unità interno, in attesa dell'esito della domanda precedente.) È un affare più BIGGER rispetto alla frammentazione dell'indice interno? O è veramente lo stesso tipo di problema (l'unità deve fare casualmente le letture invece delle letture sequenziali)

  • La deframmentazione (o la ricostruzione) indica una perdita di tempo se l'unità è frammentata fisicamente? Devo correggerlo prima di affrontare l'altro?

  • Qual è il modo migliore per risolvere la frammentazione di file fisici in una casella SQL di produzione? So che posso distriggersre i servizi SQL e eseguire Windows Defrag, ma ho anche sentito parlare di una tecnica in cui eseguire un backup completo, lasciare il database, quindi ripristinare dal backup in un'unità vuota. È raccomandata questa ultima tecnica? Il ripristino da un backup come questo crea anche indici da zero, eliminando la frammentazione interna dell'indice? O semplicemente restituisce l'ordine di pagina allo stesso modo in cui è stato eseguito il backup? (Stiamo usando i backup di Quest Lightspeed con compressione, se ciò è importnte.)

AGGIORNAMENTO : Buone risposte finora su se deframmentare le unità SAN (NO) e se la deframmentazione di indice è ancora valida per le unità fisicamente frammentate (YES).

Qualcuno altro si preoccupa di valutare i methods migliori per effettivamente eseguire la deframmentazione? O una stima sulla durata del tempo che ci si aspetterebbe che avrebbe bisogno di deframmentare un'unità grande frammentata, ad esempio 500 GB o così? Rilevante, ovviamente, perché è il momento in cui il mio server SQL sarà in giù!

Inoltre, se qualcuno ha informazioni aneddotiche sui miglioramenti delle performance SQL che hai apportto fissando la frammentazione fisica, sarebbe anche grande. Il post di blog di Mike parla di scoprire il problema, ma non è specifico di quale tipo di miglioramento ha fatto.

  • La frammentazione MFT potrebbe essere un problema sul mio server file occupato?
  • Cosa potrebbe impedire il successo di handicap di IKE nella costruzione di un tunnel IPSEC?
  • Come posso deliberatamente frammentare file specifici? (NTFS)
  • 6 Solutions collect form web for “Frammento di file fisici di database SQL”

    Penso che questo articolo dia un'ottima panoramica della deframmentazione delle unità SAN

    http://www.las-solanas.com/storage_virtualization/san_volume_defragmentation.php

    I punti fondamentali sono che la deframmentazione non è raccomandata per l'archiviazione SAN perché è difficile correlare la posizione fisica dei blocchi sul disco quando la posizione è stata virtualizzata da SAN quando presenta il LUN.

    Se stai utilizzando mappe di periferiche RAW o hai accesso diretto a un set RAID che è il LUN con cui stai lavorando, ho potuto vedere che la degrafrazione ha un effetto positivo, ma se ti viene dato un LUN "virtuale" fuori da un RAID condiviso, 5 set, no.

    Più parti a questa domanda e risposta:

    La frammentazione di file fisici non è veramente rilevante per l'archiviazione di SAN Enterprise, come ha già sottolineato Kevin – quindi niente da aggiungere. Viene veramente sceso al sottosistema I / O e quanto è probabile che sia ansible distriggersre le unità da I / O più casuali quando si esegue una scansione ad altri I / O sequenziali quando si esegue una scansione. per DAS, è più probabile che tu, per una complessa fice-n-dice SAN, probabilmente non.

    Defragmentare il livello del file system – fai solo con SQL arrestato. Non ho mai avuto problemi qui (perché non ho mai eseguito una deframazione in linea di file di database SQL), ma ho sentito molte prove aneddotiche da parte dei clienti e dei clienti di strano problema di corruzione. La saggezza generale non è farlo con SQL online.

    La frammentazione dell'indice è completamente ortogonale alla frammentazione dei file. SQL Server non ha idea di frammentazione di file – troppi livelli di virtualizatina tra di loro per avere alcuna speranza di elaborare geometrie di sottosistema di I / O effettivo. La frammentazione dell'indice, tuttavia, SQL sa tutto su. Senza ripetermi troppo dalla risposta che hai già fatto riferimento, la frammentazione dell'indice impedirà a SQL di effettuare un'efficace readahead di scansione di gamma, indipendentemente dal fatto che i file siano frammentati (o non) a livello di file system. Quindi – assolutamente dovresti ridurre la frammentazione dell'indice se stai visualizzando performance degradanti della query.

    Non è necessario fare queste operazioni in un ordine particolare, anche se si prende cura della frammentazione del file system e quindi ricostruisce tutti i tuoi indici e causa una maggiore frammentazione del file system creando più file su un volume deframmentato, probabilmente andrai essere spento. Forse causerà eventuali problemi di perf? Come sopra discusso, dipende 😀

    Spero che questo ti aiuti!

    Qual è il modo migliore per risolvere la frammentazione di file fisici in una casella SQL di produzione?

    Correndo SYSINTERNAL 'contig sui miei file di database.

    Vedere http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx

    Vorrei raccomandare di ridimensionare il db in modo appropriato, arrestando il server sql, copiando il file di database in un'altra matrix di dischi, quindi copiandolo per deframerlo. Molto più veloce di usare windows defrag nella mia esperienza.

    Ho cercato di deframmentare i dischi fisici in una soluzione scsi una volta, ma non ho avuto poca o nessuna spinta di performance a tutti. La lezione che ho appreso è che se si sperimenta performance lente a causa del sistema di disco, non ha nulla a che fare con la frammentazione, per quanto si parla di file di dati, poiché utilizza l'accesso random.

    Se i tuoi indici sono deframmentati e le statistiche vengono aggiornate (molto importnti) e ancora vedi I / O come bottiglia, allora soffri di altre cose che la frammentazione fisica. Hai utilizzato più dell'80% dell'azionamento? Hai dischi sufficienti? Le tue query sono ottimizzate abbastanza? Stai facendo un sacco di scansione tabella o peggio ancora un sacco di indice di ricerca seguita da un indice di indagine cluster? Guarda i piani della query e utilizza "statistiche impostate su" per scoprire cosa è veramente in corso con la tua query. (cercare un numero elevato di letture logiche o fisiche)

    Fathemes sapere se sono completamente sbagliato.

    / Håkan Winther

    Forse gli indici non sono sufficientemente ottimizzati per la tua applicazione e non hai Veritas I3 per ottimizzare il tuo database, allora puoi usare un'istruzione come questa per trovare indici mancanti:

    SELECT mid.statement, mid.equality_columns, mid.inequality_columns, mid.included_columns, migs.user_seeks, migs.user_scans, migs.last_user_seek, migs.avg_user_impact, user_scans, avg_total_user_cost, avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) AS [weight]--, migs.*--, mid.* FROM sys.dm_db_missing_index_group_stats AS migs INNER JOIN sys.dm_db_missing_index_groups AS mig ON (migs.group_handle = mig.index_group_handle) INNER JOIN sys.dm_db_missing_index_details AS mid ON (mig.index_handle = mid.index_handle) ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) DESC ; 

    Oppure un'istruzione come questa per trovare gli indici che non vengono utilizzati nelle istruzioni selezionate e diminuisce l'aggiornamento / inserimento delle performance:

      CREATE PROCEDURE [ADMIN].[spIndexCostBenefit] @dbname [nvarchar](75) WITH EXECUTE AS CALLER AS --set @dbname='Chess' declare @dbid nvarchar(5) declare @sql nvarchar(2000) select @dbid = convert(nvarchar(5),db_id(@dbname)) set @sql=N'select ''object'' = t.name,i.name ,''user reads'' = iu.user_seeks + iu.user_scans + iu.user_lookups ,''system reads'' = iu.system_seeks + iu.system_scans + iu.system_lookups ,''user writes'' = iu.user_updates ,''system writes'' = iu.system_updates from '+ @dbname + '.sys.dm_db_index_usage_stats iu ,' + @dbname + '.sys.indexes i ,' + @dbname + '.sys.tables t where iu.database_id = ' + @dbid + ' and iu.index_id=i.index_id and iu.object_id=i.object_id and iu.object_id=t.object_id AND (iu.user_seeks + iu.user_scans + iu.user_lookups)<iu.user_updates order by ''user reads'' desc' exec sp_executesql @sql set @sql=N'SELECT ''object'' = t.name, o.index_id, ''usage_reads'' = user_seeks + user_scans + user_lookups, ''operational_reads'' = range_scan_count + singleton_lookup_count, range_scan_count, singleton_lookup_count, ''usage writes'' = user_updates, ''operational_leaf_writes'' = leaf_insert_count + leaf_update_count + leaf_delete_count, leaf_insert_count, leaf_update_count, leaf_delete_count, ''operational_leaf_page_splits'' = leaf_allocation_count, ''operational_nonleaf_writes'' = nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count, ''operational_nonleaf_page_splits'' = nonleaf_allocation_count FROM ' + @dbname + '.sys.dm_db_index_operational_stats(' + @dbid + ', NULL, NULL, NULL) o, ' + @dbname + '.sys.dm_db_index_usage_stats u, ' + @dbname + '.sys.tables t WHERE u.object_id = o.object_id AND u.index_id = o.index_id and u.object_id=t.object_id ORDER BY operational_reads DESC, operational_leaf_writes, operational_nonleaf_writes' exec sp_executesql @sql GO 

    Ho altre dichiarazioni SQL che sto usando quando analizzo i problemi di performance nell'ambiente di produzione, ma questi due sono un buon inizio che penso.

    (Lo so, questo post è un po 'di argomento, ma ho pensato che potrebbe essere interessato dato che ha a che fare con la strategia di indexing)

    / Håkan Winther

    Suggerimenti per Linux e Windows Server, quali Ubuntu, Centos, Apache, Nginx, Debian e argomenti di rete.