Frage Physische Dateifragmentierung der SQL-Datenbank


Ich weiß, dass es wirklich ist drei Arten von Fragmentierung, über die ich als DBA besorgt sein muss:

  1. Index Fragmentierung in den SQL-Datendateien, einschließlich Fragmentierung des Clustered-Index (Tabelle). Identifizieren Sie dies mithilfe von DBCC SHOWCONTIG (in SQL 2000) oder sys.dm_ db_ index_ physical_stats (in 2005+).

  2. VLF-Fragmentierung innerhalb von SQL-Protokolldateien. Führen Sie DBCC LOGINFO aus, um zu sehen, wie viele VLFs in jeder Ihrer SQL-Protokolldateien enthalten sind.

  3. Physische Dateifragmentierung der Datenbankdateien auf der Festplatte. Diagnostizieren Sie dies mithilfe des Dienstprogramms "Defragmentierung" in Windows. (inspiriert von dieser ausgezeichnete Blogbeitrag)

Der Indexfragmentierung wird viel Aufmerksamkeit geschenkt (vgl Diese ausgezeichnete Serverfault-Antwort von Paul Randall), das ist also nicht der Schwerpunkt meiner Frage.

ich weiss ich kann verhindern physische Fragmentierung (und VLF-Fragmentierung), wenn die Datenbank ursprünglich erstellt wurde, indem eine vernünftige erwartete Datendatei und Protokollgröße geplant wurde, da diese Fragmentierung am häufigsten von häufigen Vergrößerungen und Verkleinerungen ausgeht, aber ich habe einige Fragen dazu Fix physische Fragmentierung, sobald sie identifiziert ist:

  • Ist die physische Fragmentierung überhaupt erst in einem Enterprise SAN relevant? Kann / sollte ich Windows Defragmentierung auf einem SAN-Laufwerk verwenden oder sollte das SAN-Team interne Defragmentierungsprogramme verwenden? Ist die Fragmentierung Analyse ich sogar aus dem Windows-Tool genau wenn auf einem SAN-Laufwerk ausgeführt?

  • Wie groß ist die physische Fragmentierung der SQL-Leistung? (Nehmen wir ein internes Laufwerksarray an, in Erwartung des Ergebnisses der vorherigen Frage.) Ist es ein größerer Deal als die interne Indexfragmentierung? Oder ist es wirklich das? selbe Art of problem (das Laufwerk muss zufällige Lesevorgänge anstelle von sequentiellen Lesevorgängen durchführen)

  • Ist das Defragmentieren (oder Wiederherstellen) von Indizes eine Zeitverschwendung, wenn das Laufwerk physisch fragmentiert ist? Muss ich den einen reparieren, bevor ich den anderen anspreche?

  • Was ist der beste Weg, um physische Dateifragmentierung in einer Produktions-SQL-Box zu beheben? Ich weiß, ich kann SQL-Dienste deaktivieren und Windows Defrag ausführen, aber ich hörte auch von einer Technik, bei der Sie eine vollständige Sicherung durchführen, die Datenbank löschen und dann von der Sicherung auf ein leeres Laufwerk wiederherstellen. Wird diese letztere Technik empfohlen? Ist das Wiederherstellen von einem Backup wie folgt ebenfalls Indizes von Grund auf neu erstellen und interne Indexfragmentierung eliminieren? Oder gibt es die Seitenreihenfolge einfach so zurück, wie wenn die Sicherung ausgeführt wurde? (Wir verwenden Quest Lightspeed-Backups mit Komprimierung, wenn das wichtig ist.)

AKTUALISIEREN: Bisher gute Antworten darauf, ob SAN-Laufwerke defragmentiert werden sollen (NO) und ob sich die Index-Defragmentierung auch auf physikalisch fragmentierten Laufwerken lohnt (YES).

Will jemand sonst die besten Methoden für die eigentliche Defragmentierung berücksichtigen? Oder eine Schätzung über die Länge der Zeit, die Sie erwarten würden, um eine große fragmentierte Festplatte zu defragmentieren, sagen wir 500 GB oder so? Relevant, natürlich, weil zu diesem Zeitpunkt mein SQL-Server nicht verfügbar ist!

Wenn jemand irgendwelche anekdotischen Informationen über SQL-Leistungsverbesserungen hat, die Sie durch das Korrigieren der physischen Fragmentierung gemacht haben, wäre das auch großartig. Mikes Blogeintrag spricht über die Aufdeckung des Problems, ist aber nicht spezifisch, was für eine Verbesserung es gemacht hat.


19
2018-06-24 14:29


Ursprung




Antworten:


Ich denke, dieser Artikel gibt einen hervorragenden Überblick über die Defragmentierung von SAN-Laufwerken

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

Grundlegend ist, dass eine Defragmentierung im SAN-Speicher nicht empfohlen wird, da es schwierig ist, den physischen Speicherort von Blöcken auf dem Datenträger zu korrelieren, wenn der Standort beim Präsentieren der LUN vom SAN virtualisiert wurde.

Wenn Sie RAW-Gerätezuordnungen verwenden oder direkten Zugriff auf ein RAID-Set haben, bei dem es sich um die LUN handelt, mit der Sie arbeiten, kann die Degfragmentierung einen positiven Effekt haben. Wenn Sie jedoch eine "virtuelle" LUN von einem gemeinsam genutzten RAID erhalten. 5 Satz, nein.


9
2018-06-24 14:38



Ausgezeichneter Artikel. Punktgenau in Bezug auf SAN-Laufwerke. - BradC


Mehrere Teile zu dieser Frage und Antwort:

Physische Dateifragmentierung ist nicht wirklich relevant für Enterprise SAN-Speicher, wie Kevin bereits darauf hingewiesen hat - also nichts hinzuzufügen. Es kommt wirklich auf das E / A-Subsystem an und wie wahrscheinlich es ist, dass Sie die Laufwerke von mehr zufälligen E / As bei der Ausführung eines Scans zu mehr sequenziellen E / As führen können, wenn Sie einen Scan durchführen. Für DAS ist es wahrscheinlicher, dass Sie das für ein komplexes Slice-n-Dice-SAN wahrscheinlich nicht tun werden.

Defragmentierung auf Dateisystemebene - nur mit SQL-Shutdown. Ich habe hier selbst nie Probleme erlebt (da ich noch nie eine Online-Defragmentierung von SQL-Datenbankdateien durchgeführt habe), aber ich habe viele anekdotische Beweise von Kunden und Kunden von seltsamen Korruptionsproblemen gehört. Allgemeine Weisheit ist es nicht mit SQL Online zu tun.

Die Indexfragmentierung ist vollständig orthogonal zur Dateifragmentierung. SQL Server hat keine Ahnung von Dateifragmentierung - zu viele virtualisierte Schichten dazwischen, um auf die tatsächlichen E / A-Subsystemgeometrien zu hoffen. Fragmentierung des Index, SQL weiß jedoch alles darüber. Ohne sich zu sehr aus der Antwort zu replizieren, auf die Sie bereits verwiesen haben, verhindert die Indexfragmentierung, dass SQL effiziente Bereichsüberprüfungen vornimmt, unabhängig davon, wie fragmentiert (oder nicht) die Dateien auf der Dateisystemebene sind. Sie sollten also unbedingt die Indexfragmentierung mindern, wenn Sie eine Verschlechterung der Abfrageleistung feststellen.

Ach nein haben Wenn Sie sich um die Fragmentierung des Dateisystems kümmern und dann alle Ihre Indizes neu erstellen und mehr Dateisysteme fragmentieren, indem Sie mehrere Dateien auf einem defragmentierten Volume anwachsen lassen, werden Sie wahrscheinlich abgehakt. Wird es jedoch Perf-Probleme geben? Wie oben besprochen, hängt es ab: -D

Hoffe das hilft!


7
2018-06-24 15:01



Ah, ändert sich die interne Indexfragmentierung tatsächlich in das Verhalten des Optimierers, um volle Scans anstelle von geeigneten Indexbereich-Suchvorgängen zu bevorzugen? - BradC
Nein. Das Optimierungsprogramm hat keine Kenntnis darüber, wie die Daten auf der Festplatte gespeichert sind, abgesehen von der Tatsache, dass es Indizes gibt, deren Größe und Spaltenwertverteilungsstatistik. Es ist die Storage Engine, die den Readahead antreibt und die einzelnen I / O-Größen basierend auf der logischen Fragmentierung dessen, was gescannt wird, ändert. - Paul Randal


Was ist der beste Weg, um körperliche Probleme zu beheben?   Dateifragmentierung in einem Produktions-SQL   Box?

Ich laufe SYSINTERNALS Contig auf meinen Datenbankdateien.

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


3
2018-06-24 14:35



Sieht interessant aus. Ich nehme an, da die SQL-Defrag-APIs verwendet werden müssten, müssten diese SQL-Dienste ausgeschaltet sein? Oder würde dies laufen, während der Server / die Datenbank online ist? - BradC
Ich habe es erfolgreich in Online-MSSQL-Server-Datenbanken verwendet. Aber wohl waren das wenig Verkehr und kleine Datenbanken (weniger als 10 GB) - Vincent Buck
Dies ist ein großartiges Werkzeug! Ich denke, dass Anwendungen für Datenbanken ziemlich begrenzt sind, wie von anderen Leuten erwähnt, aber ich liebe es für andere Arten von Laufwerken. Der Analysemodus -a ist sicher, während die Dinge laufen. Ich würde mich jedoch nicht sicher fühlen, wenn ich es gegen eine Festplatte laufen lasse, die zu einem Live SQL Server gehört. - Kendra


Ich würde empfehlen, die Datenbank entsprechend zu dimensionieren, den SQL-Server herunterzufahren, die Datenbankdatei in einen anderen Plattenarray zu kopieren und sie dann zurück zu kopieren, um sie zu defragmentieren. Viel schneller als Windows Defrag nach meiner Erfahrung.


2
2018-06-29 17:57





Ich habe versucht, die physischen Festplatten in einer SCSI-Lösung einmal zu defragmentieren, aber überhaupt wenig oder keinen Leistungsschub. Die Lektion, die ich gelernt habe, ist, dass, wenn Sie aufgrund des Plattensystems eine langsame Leistung erfahren, es nichts mit der Fragmentierung zu tun hat, soweit wir Datendatei sprechen, da es einen Direktzugriff verwendet.

Wenn Ihre Indizes defragmentiert sind und Statistiken aktualisiert werden (sehr wichtig) und Sie immer noch I / O als Engpass sehen, leiden Sie unter anderen Dingen als der physischen Fragmentierung. Haben Sie mehr als 80% der Festplatte genutzt? Hast du genug Laufwerke? Sind Ihre Anfragen ausreichend optimiert? Machst du eine Menge Table-Scan oder noch schlimmer Indexsuche, gefolgt von Clustered-Index-Lookup? Sehen Sie sich die Abfragepläne an und verwenden Sie "set statistics io on", um herauszufinden, was wirklich mit Ihrer Abfrage passiert. (Suchen Sie nach einer hohen Anzahl von logischen oder physischen Lesevorgängen)

Bitte lassen Sie mich wissen, wenn ich völlig falsch liege.

/ Håkan Winther


1
2018-06-24 20:49



Nein, du liegst nicht falsch. Der Versuch, einige serverweite Verbesserungen vorzunehmen (wenn möglich), ist ein wenig attraktiver als der Einstieg in die über 150.000 verschiedenen SQL-Anweisungen, die während der wöchentlichen Analysen ausgeführt werden (nicht übertrieben. Wahrscheinlich eine Untertreibung). - BradC
Wenn Sie diese Situation haben, würde ich Veritas I3 empfehlen, Ihre Umgebung zu analysieren, um zu sehen, an welchem ​​Engpass Sie leiden und was den Engpass verursacht. Veritas I3 verfolgt alle Aussagen und wie oft sie aufgerufen werden und zu welchen Kosten. Es ist eine ausgezeichnete Software. - Hakan Winther


Möglicherweise sind die Indizes nicht für Ihre Anwendung optimiert und Sie haben nicht Veritas I3, um Ihre Datenbank zu optimieren, dann könnten Sie eine solche Anweisung verwenden, um fehlende Indizes zu finden:

       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 ;

Oder eine Anweisung wie diese, um Indizes zu finden, die nicht in SELECT-Anweisungen verwendet werden und die Aktualisierungs- / Einfügungsleistung verringern:

    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

Ich habe einige andere SQL-Anweisungen, die ich verwende, wenn ich Performance-Probleme in der Produktionsumgebung analysiere, aber diese beiden sind ein guter Anfang, denke ich.

(Ich weiß, dieser Beitrag ist ein kleines Thema, aber ich dachte, Sie könnten interessiert sein, da es mit der Indexierungsstrategie zu tun hat)

/ Håkan Winther


1
2018-06-25 06:02



Ausgezeichnete Skripte, ich habe einige sehr ähnlich. Leider sind wir immer noch 40% SQL 2000 (einschließlich des betreffenden Servers), der diesen DMVs "missing index" nicht entspricht. - BradC
Ich sehe, dann empfehle ich Ihnen, sich Veritas I3 anzuschauen. Es ist ein ausgezeichnetes Produkt, das Sie verwenden können, um Ihre Datenbanken zu optimieren, aber es ist keine billige Software. - Hakan Winther