Sonntag, 8. September 2013

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'))

INDEX_FRAGMENTATION_01


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?


INDEX_FRAGMENTATION_02


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].


DATAFILE_01


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:


DATAFILE_02


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:


DATAFILE_03


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!









SHRINKDATABASEhttp://technet.microsoft.com/de-de/library/ms190488.aspx
Instant File Initializationhttp://blogs.msdn.com/b/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx

Kommentare :

  1. Wirklich guter Artikel! - hat mir beim Argumentieren / Verdeutlichen meiner Meinung bei meinen Kollegen geholfen.

    Danke

    AntwortenLöschen
  2. Es sollte noch erwähnt werden, dass das gleiche Problem auftritt, wenn man DBCC SHRINKFILE ohne den TRUNCATE_ONLY-Parameter aufruft.

    Siehe https://www.mssqltips.com/sqlservertip/4368/execute-sql-server-dbcc-shrinkfile-without-causing-index-fragmentation/

    PS: Microsoft könnte da ja endlich mal richtig machen. In Zeiten von SSDs und SANs kommt es auf die Fragmentierung auf NTFS- bzw. Datenträgerebene ja nicht mehr an, also könnte MS ja hingehen und einfach die nicht belegten Speicherbereiche freigeben (ohne überhaupt Daten hin- und herzuschieben).

    AntwortenLöschen
  3. PS: mit einer Enterprise-Version kann man beim INDEX REBUILD auch SORT_IN_TEMPDB angeben, wodurch die Datenbank meinem Verständnis nach beim Rebuild nicht wieder wachsen sollte (hab das ehrlich gesagt aber noch nicht ausgetestet)

    AntwortenLöschen