DBCC SHRINKDATABASE – wirklich ein Segen für dba?

In der letzten Woche wurde ich zu einem “Kurzeinsatz” hinzu gezogen, bei dem es darum ging, den hohen I/O bei Abfragen zu untersuchen. Die Programmierer waren alles erfahrene Leute und kannten sich sehr gut mit den Möglichkeiten der Indexanalyse und der Bewertung von Indexstatistiken aus. Die Programmierer haben die Datenbank der Produktionsumgebung in eine Testumgebung portiert, die Indexe defragmentiert und das I/O für die Abfragen gemessen. Anschließend wurden diese Ergebnisse mit dem I/O der Produktionsumgebung verglichen und man stellte fest, dass das I/O für identische Abfragen nahezu doppelt so hoch war. Man stellte anschließend fest, dass die Indexe in der Produktion sehr stark fragmentiert waren. Also wurden die Indexe neu aufgebaut und man glaubte, damit das Problem gelöst zu haben. Am nächsten Tag ist das Problem erneut aufgetreten und man konnte sich dieses Verhalten nicht erklären, war doch der Clustered Index in allen Relationen so definiert, dass immer ein Surrogatschlüssel auf einem IDENTITY-Attribut lag. Um die Ursache zu finden, musste man nicht lange suchen – die DBA des Unternehmens haben für alle SQL-Server im Unternehmen einen Standardauftrag implementiert, der am Abend die Datenbanken "verkleinert”. Warum der Unsinn einer Verkleinerung einen massiven Einfluss auf die Performance von Datenbanken hat, beschreibt der folgende Artikel mit einem Beispiel. 

Testumgebung

Für die Auswirkungen von SHRINKDATABASE auf die Fragmentierung wird eine neue Datenbank erstellt, in der sich zwei Relationen mit einem Datenvolumen von ca. 5 MB befinden. Nachdem die Testumgebung aufgebaut ist, werden die Fragmentierungen der Clustered Indexe ermittelt.

Definition der Datenbank

 1: USE master;
 2: GO
 3:  
 4: IF db_id('demo_db') IS NULL
 5:     CREATE DATABASE demo_db
 6:     ON 
 7:     (
 8:         NAME = demo_dat,
 9:         FILENAME = 'S:\MSSQL11.MSSQLSERVER\MSSQL\DATA\demo_data.mdf',
 10:         FILEGROWTH = 1MB
 11:     )
 12:     LOG ON
 13:     (
 14:         NAME = demo_log,
 15:         FILENAME = 'S:\MSSQL11.MSSQLSERVER\MSSQL\DATA\demo_data.ldf',
 16:         SIZE = 5MB,
 17:         MAXSIZE = 25MB,
 18:         FILEGROWTH = 5MB
 19:     );
 20: GO
 21:  
 22: ALTER DATABASE demo_db SET RECOVERY SIMPLE;
 23: ALTER AUTHORIZATION ON DATABASE::demo_db TO sa;

Erstellen der Relation [dbo].[table_a] und füllen mit Testdaten

 1: IF OBJECT_ID('dbo.table_a', 'U') IS NOT NULL
 2:     DROP TABLE dbo.table_a;
 3:     GO
 4:  
 5: CREATE TABLE dbo.table_a
 6: (
 7:     Id      int       NOT NULL    IDENTITY (1, 1),
 8:     col1    char(197) NOT NULL    DEFAULT ('just stupid stuff')
 9: );
 10: GO
 11:  
 12: SET NOCOUNT ON;
 13: GO
 14:  
 15: INSERT INTO dbo.table_a DEFAULT VALUES
 16: GO 15000
 17:  
 18: CREATE UNIQUE CLUSTERED INDEX ix_table_a_Id ON dbo.table_a (Id);
 19: GO

Erstellen der Relation [dbo].[table_b] und füllen mit Testdaten

 1: IF OBJECT_ID('dbo.table_b', 'U') IS NOT NULL
 2:     DROP TABLE dbo.table_b;
 3:     GO
 4:  
 5: CREATE TABLE dbo.table_b
 6: (
 7:     Id    int        NOT NULL    IDENTITY (1, 1),
 8:     col1  char(197)  NOT NULL    DEFAULT ('just stupid stuff')
 9: );
 10: GO
 11:  
 12: INSERT INTO dbo.table_b DEFAULT VALUES
 13: GO 15000
 14:  
 15: CREATE UNIQUE CLUSTERED INDEX ix_table_b_Id ON dbo.table_b (Id);

Analyse der physikalischen Indexdaten (Fragmentierung und Datenmenge)

 1: SELECT  OBJECT_NAME(object_id)    AS    object_name,
 2:         fragment_count,
 3:         avg_fragmentation_in_percent,
 4:         page_count,
 5:         record_count
 6: FROM    sys.dm_db_index_physical_stats(db_id(), DEFAULT, DEFAULT, DEFAULT, 'DETAILED')
 7: WHERE   object_id IN (OBJECT_ID('dbo.table_a', 'U'), OBJECT_ID('dbo.table_b', 'U'))

Perfekt – die Indexe sind nicht fragmentiert! Nachdem die Testumgebung erstellt wurde, wird im nächsten Schritt die Relation [dbo].[table_a] aus der Datenbank entfernt. Anschließend wird die Datenbank verkleinert und erneut die physikalische Beschaffenheit der verbliebenen Relationen geprüft.

 1: -- Löschen der Tabelle dbo.table_a
 2: DROP TABLE table_a;
 3: GO
 4:  
 5: -- Verkleinern der Datenbank
 6: DBCC SHRINKDATABASE ('demo_db', 0);
 7:  
 8: -- Überprüfung der Datenbankgröße
 9: SELECT  size / 128.0                             AS size_mb,
 10:         FILEPROPERTY(name, 'spaceused') / 128.0  AS used_MB
 11: FROM    sys.sysfiles;
 12:  
 13: -- Stufe der Fragmentierung analysieren
 14: SELECT  OBJECT_NAME(object_id)    AS    object_name,
 15:         fragment_count,
 16:         avg_fragmentation_in_percent,
 17:         page_count,
 18:         record_count
 19: FROM    sys.dm_db_index_physical_stats(db_id(), DEFAULT, DEFAULT, DEFAULT, 'DETAILED')
 20: WHERE   object_id IN (OBJECT_ID('dbo.table_a', 'U'), OBJECT_ID('dbo.table_b', 'U'))

Die folgende Abbildung zeigt, dass der zuvor ideal aufgebaute Index für die Relation [dbo].[table_b] nun vollständig fragmentiert wurde. Woran liegt das?

Ursache der hohen Fragmentierung

Das Beispiel zeigt, dass SHRINKDATABASE kausal für die hohe Fragmentierung ist. Diese Kausalität ist bedingt durch die Art und Weise, wie SHRINKDATABASE intern arbeitet. Bei der Verwendung von SHRINKDATABASE werden die Datenseiten am Ende der Datenbankdatei an die nächste freie Position in der der Datenbankdatei verschoben. Berücksichtigt man, dass in einem FRISCHEN Clustered Index die Datenseiten sequentiell angeordnet sind, kann man nun nachvollziehen, warum der Index so stark fragmentiert ist.

Die nachfolgende Abbildung zeigt beide Tabellen schematisch auf Datenseiten aufgeteilt vor dem Löschen der Relation [dbo].[table_a].

Die blauen “Datenseiten” repräsentieren das Objekt [dbo].[table_a] während die braunen “Datenseiten” das Objekt [dbo].[table_b] repräsentieren. Wird nun die Relation [dbo].[table_a] gelöscht, stellt sich die Datenseite anschließend wie folgt dar:

Wenn mit SHRINKDATABASE die Datendatei neu organisiert wird, werden IMMER die Datenseiten beginnend mit dem Ende des belegten Bereichs verschoben. Intern ergibt sich nach der Organisation der Datenseiten das folgende Bild:

Die zuvor letzte Datenseite wurde an die erste Position verschoben, die vorletzte Datenseite an die zweite Position, …! Da ist es verständlich, dass anschließend der Index nicht mehr fortlaufend gelesen werden kann. Auf Basis dieser Erkenntnis wurde der Job vollständig aus der täglichen Datenbank-Maintenance entfernt.

Fazit

Leider ist die Zahl der Microsoft SQL Server, bei denen SHRINKDATABASE eingesetzt wird, erschreckend hoch. Ich weiß nicht, warum manche dba auf dieses Verfahren “schwören”. Selbst in den Microsoft Foren wird teilweise empfohlen, nach dem Löschen großer Datenmengen ein SHRINKDATABASE durchzuführen.

Neben dem oben genannten Verhalten hat SHRINKDATABASE so viele weitere negative Merkmale, dass ein Einsatz wirklich IMMER wohl überlegt sein sollte:

  • Hohe Fragmentierung auf Dateisystem-Ebene
  • Erneutes Wachsen der Datenbankdatei beim Hinzufügen neuer Daten
  • Waitstats treten während des Wachsens auf, wenn kein Instant File Initialization möglich ist

Muss man SHRINKDATABASE unbedingt nutzen, sollte anschließend ein INDEX-REBUILD durchgeführt werden. Hahahahaha – das Bild der Katze, die versucht, sich selbst in den Schwanz zu beißen, drängt sich mir gerade auf. Ein INDEX-REBUILD wird die Datendatei erneut vergrößern, da für einen REBUILD immer neue Datenseiten allokiert werden – und somit die Datenbank erneut wächst – ein Teufelskreis.

Die beste Wahl ist, SHINKRDATABASE nicht einzusetzen und ein vernünftiges “Capacity Management” zu implementieren. Mit geeigneter Planung und richtiger Konfiguration der Wachstumsraten ist SHRINKDATABASE obsolet. Leider wissen das noch viel zu wenig Administratoren “da draußen”; aber auch im Management werden solche Positionen eher stiefmütterlich behandelt. :)

Herzlichen Dank fürs Lesen!

SHRINKDATABASE http://technet.microsoft.com/de-de/library/ms190488.aspx
Instant File Initialization http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx
  • *****
    Kommentar von: tosc
    09.09.13 @ 13:39:40

    Hallo Uwe, ich dachte über diesen Punkt sind die meisten DBA's schon raus - naja. Kleine Anmerkung noch zur hohen Fragmentierung auf Dateisystemebene, hier hilft Contig von Mark Russinovich (http://technet.microsoft.com/en-us/sysinternals/bb897428). Mit contig -a DatenbankName.mdf kann man sich die physikalische Fragmentierung ansehen. Und nach einem ALTER DATABASE [DatenbankName] SET OFFLINE kann man mit contig DatenbankName.mdf die Datenbank auf Dateisystemebene defragmentieren.

  • Kommentar von: Uwe Ricken
    09.09.13 @ 16:02:50

    Lieber Torsten, herzlichen Dank für den Link; das Tool kannte ich noch nicht. Da aber für eine Defragmentierung ein DB-File offline genommen werden muss, ist es nicht wirklich sinnvoll in einer 24 / 7 - Anwendung ;( Bezüglich der "Erfahrungen eines dba" solltest Du nicht zu optimistisch sein :) Gerade im Segment Betrieb / Operations weltweit agierender Konzerne sollte man die Messlatte nicht sehr hoch setzen. Ich habe schlechte Erfahrungen in diesem Bereich mit den Kollegen "jenseits des Pazifks" machen müssen. Sicherlich nicht repräsentativ aber immer wieder erstaunlich!

  • Kommentar von: tosc
    09.09.13 @ 16:32:32

    gerne :-) ... auch bei 24/7 muss es ein Maintenance Fenster geben.

  • Kommentar von: tosc
    10.09.13 @ 07:59:54

    p.s.: kleiner Nachtrag zur starken Fragmentierung von Dateien im NTFS-Volume http://support.microsoft.com/kb/967351

  • Kommentar von: Uwe Ricken
    10.09.13 @ 17:13:32

    Hallo Torsten, ACK! - aber eine 600 GB-Datenbank für mehrere Stunden offline zu nehmen, ist ein NOGO :) Es gibt SLA, die pro Jahr maximal 6 Stunden definieren! Ein SHRINKDATABASE ist in solchen Systemen IMMER der falsche Ansatz. Aber leider so z. B. in einer Anwendung erlebt, bei der JEDEN Montag ein Incident geöffnet wurde, um die Datenbank (DATA und LOG) zu verkleinern! Das die Kosten für diese "Snnlosaktion" deutlich über den Kosten für zusätzlichen Storage liegen, war/ist dem Business bis heute nicht zu vermitteln!

  • Markus
    Kommentar von: Markus
    29.04.15 @ 10:58:54

    Hallo Herr Ricken, danke für diesen Artikel. Allerdings hilft er mir nicht weiter, wenn eine Datenbank wirklich verkleinert werden muss um freien Speicher zu bekommen. Ein Kunde hat eine 800 GB Datebank. Nach einer Datenbereinigung ist die DB eigentlich nur noch ca. 80 GB groß und wird durch den regelmäßigen Einsatz von Löschskripten auch nicht mehr größer als ca. 100 GB. Ohne Shrinken kommt man aber nicht mehr an den Speicherplatz heran. Was wäre denn Ihre Empfehlung für so einen Fall? Viele Grüße Markus Meyer

Einen Kommentar hinterlassen

In order to leave a comment Jetzt einloggen!