Ich habe in dieser Woche einen Fall zu untersuchen gehabt, der ein System für mehrere Stunden (teilweise sogar Tage) komplett lahm gelegt hat. Die Analyse hat gezeigt, dass (unter anderem) regelmäßige Index- und Statistikaktualisierungen durchgeführt wurden. Was ich dann herausgefunden habe, mag man kaum glauben. In diesem konkreten Beispiel wurden wirklich ALLE Fehler gemacht, die man in Verbindung mit sys.dm_db_index_phyical_stats und dessen Anwendungsspektrum überhaupt machen kann.

Ausgangsstellung der Untersuchung

Bei dem zu untersuchenden System handelte es sich um eine ca. 1.500 GB große Datenbank, in der sich mehrere hundert (~650) Relationen befanden. Insgesamt gab es – inklusive Clustered Index – ca. 1.200 Indexe in der Datenbank. Die größten Relationen hatten ein Volumen von ca. 70 GB / Clustered Index. Ca. die Hälfte aller Relationen hatte KEINE Daten.

Mittels SQL Agent Job wurden Aufträge in eine “Jobrelation” geschrieben, die vielfältige Aufgaben bereitstellten. Unter anderem wurde für JEDE Relation ein Auftrag eingestellt, der eine Prozedur [dbo].[proc_app_Reindex] aufruft, dessen Übergabeparameter der Name der zu prüfenden Relation ist. Der Inhalt der Prozedur sah (stark vereinfacht) wie folgt aus:

ALTER PROC dbo.proc_app_ReIndex
    @TableName    sysname
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE    @Table_Id    int
    SET        @Table_Id    =    OBJECT_ID(@TableName);
 
    SELECT    'ALTER INDEX ' + r.name + ' ON ' + @TableName +
    CASE WHEN r.avg_fragmentation_in_percent < 30.0 AND r.index_id != 1
         THEN ' REORGANIZE'
         ELSE ' REBUILD'
    END
    FROM (
           SELECT i.object_id,
                  i.name,
                  i.index_id,
                  ps.avg_fragmentation_in_percent
           FROM   sys.dm_db_index_physical_stats (db_id(), 0, DEFAULT, DEFAULT, 'DETAILED') AS ps INNER JOIN sys.indexes i
                   ON (
                         ps.Object_id = i.Object_id AND
                         ps.Index_id = i.Index_id
                       )
            WHERE  ps.avg_fragmentation_in_percent > 10.0
          ) AS r
    WHERE r.object_id = @Table_Id

    -- Cursordurchlauf mit sp_executeSQL
    SET NOCOUNT OFF END

Analyse der Prozedur

Die Prozedur hatte einige eklatante Schwächen. Insbesondere ist die Routine für die Ermittlung der richtigen Strategie die Bearbeitung der Reorganisation / Neuaufbau eines Index mehr als mangelhaft. Grundsätzlich wird gemäß der obigen Abfrage für den Clustered Index IMMER ein Neuaufbau durchgeführt. Eklatant ist auch die Tatsache, dass pauschal ohne Bewertung von Anzahl der Pages / Datensätze eine solche Strategie gefahren wird. Dies soll aber nicht das Thema dieses Artikels sein.

Ein weiteres – nicht zu unterschätzendes – Problem ist, dass ein Index auch häufiger pro Durchlauf neu strukturiert werden kann. Es wird zwar geprüft, dass nur Ebenen des Index ausgegeben werden, die eine Fragmentierung von mehr als 10% aufweisen aber dabei wurde nicht berücksichtigt, dass bei einem Clustered Index eine hohe Fragmentierung in den B-Tree als auch in den Leaf Levels vorkommen kann. In diesem Fall wird der Index zwei Mal in der Ergebnismenge vorhanden sein und somit auch zwei Mal neu aufgebaut.

Neben diesen Schwachstellen war jedoch mein Fokus zuerst auf die Verwendung von sys.dm_db_index_physical_stats gelengt worden. Hier wurden von den Entwicklern gleich zwei heftige Fehler gemacht, die darauf schließen liessen, dass sie sich über die Anwendung dieser FUNKTION keine Gedanken gemacht haben.

Filterung von Ergebnismengen

Bei der DMV sys.dm_db_index_physical_stats handelt es sich nicht um eine View/Abfrage sondern um eine Funktion. Dies wird bereits dadurch erkennbar, dass Argumente als Kriterium übergeben werden können. Genau dies haben aber die Entwickler sehr vernachlässigt. Folgende Argumente können der Funktion übergeben werden:

Wie aus der obigen Abfrage hervor geht, wird eine Filterung aller Indexe einer Relation ausserhalb der Funktion durchgeführt, Die Variable @Table_Id kommt explilzlit in einer WHERE-Klausel zum Tragen. Das bedeutet aber für die Funktion, dass zunächst eine Analyse über ALLE Objekte durchgeführt wird. Anschließend erst wird die Teilmenge herausgefiltert, die für den weiteren Vorgang von Relevanz ist. Dieses Verfahren – insbesondere bei dem oben genannten Datenvolumen – erzeugt aber ein ERHEBLICHES IO, das so hätte nicht sein müssen.

Das nachfolgende Beispiel soll den Unterschied demonstrieren. In der obigen Abbildung ist erkennbar, dass eine Relation dbo.tblStammClasses insgesamt 105 MB im Clustered Index belegt.  Für die nachfolgenden zwei Abfragen – die beide das gleiche Ergebnis haben – wird der IO gemessen:

SELECT * FROM sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.tblStammClasses', 'U'), DEFAULT, DEFAULT, 'DETAILED') GO

SELECT * FROM sys.dm_db_index_physical_stats(db_id(), 0, DEFAULT, DEFAULT, 'DETAILED') WHERE  object_id = OBJECT_ID('dbo.tblStammClasses', 'U'); GO

Das Ergebnis (selbst bei der “kleinen” Relation) zeigt deutliche Unterschiede…

Die erste Abfrage verwendet das Selektionskriterium IN der Funktion während die zweite (originale) Abfrage die Filterung ausserhalb der Funktion durchführt. Insgesamt ergibt sich ein um das 5-fache reduziertes IO-Verhalten bei Verwendung des Filterkriteriums IN der Funktion. Dieses Verhalten ist logisch – wird doch IN der Funktion bereits nur die Relation berücksichtigt, dessen object_Id als Parameter übergeben wurde. In der Originalversion muss zunächst einen Analyse über ALLE Indexe aller Objekte durchgeführt werden. Anschließend wird das gewünschte Objekt aus der Ergebnismenge herausgefiltert. Dadurch wird natürlich ein deutlich höheres IO generiert. Mulipliziert man dieses Verhalten mit der Gesamtanzahl aller Relationen (in diesem konkreten Fall 650 Relationen), kann man sich ausmalen, wie lang der Prozess PRO Relation dauerte.

Kommt dann noch dazu, dass einige Indexes von großen Relationen sogar zwei / dreimal neu erstellt wurden, durfte man sich nicht wundern, dass der Prozess sich über mehr als 18 Stunden hinzog und so das ganze System mehrmals pro Tag lahm legte.

Modus der Analyse

Ein weiterer Schwachpunkt war die Art und Weise der Analyse der Indexe. Die DMV stellt für die Analyse von Indexen drei unterschiedliche Verfahren bereit. Diese Verfahren werden durch den Parameter [Modus] entsprechend für die DMV aktiviert.

Auch hierzu wurden Tests durchgeführt, um den IO für die einzelnen Stati zu überprüfen.

SELECT * FROM sys.dm_db_index_physical_stats(db_id(), 0, DEFAULT, DEFAULT, 'DETAILED') WHERE  object_id = OBJECT_ID('dbo.tblStammClasses', 'U');

SELECT * FROM sys.dm_db_index_physical_stats(db_id(), 0, DEFAULT, DEFAULT, 'LIMITED') WHERE  object_id = OBJECT_ID('dbo.tblStammClasses', 'U');

SELECT * FROM sys.dm_db_index_physical_stats(db_id(), 0, DEFAULT, DEFAULT, 'SAMPLED') WHERE  object_id = OBJECT_ID('dbo.tblStammClasses', 'U');

SELECT * FROM sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.tblStammClasses', 'U'), DEFAULT, DEFAULT, 'DETAILED')

SELECT * FROM sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.tblStammClasses', 'U'), DEFAULT, DEFAULT, 'LIMITED')

SELECT * FROM sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.tblStammClasses', 'U'), DEFAULT, DEFAULT, 'SAMPLED')

Das Ergebnis dieser Analyse spricht für sich und zeigt einen erheblichen Unterschied in den einzelnen Modi.

Ganz klarer Gewinner in diesem Verfahren ist die Abfrage mit dem Modus [LIMITwürdeED], gefolgt von [SAMPLED]-Modus und Schlußlicht ist – wie erwartet – der Modus [DETAILED]. Einen weiteren – auch für die obige Ausführung wesentlichen – Vorteil hat die Verwendung des Modus [LIMITED]; die Ergebnismenge wird reduziert auf den Leaf-Level. Die nachfolgende Abbildung zeigt das Ergebnis der DMV bei Ausführung mit den Modi [DETAILED], [LIMITED], [SAMPLED]

Das Ergebnis erklärt sich in Bezug auf die Arbeitsweise der DMV von allein. Während im Modus [DETAILED] tatsächlich ALLE Datenseiten analysiert werden, betrifft das bei den beiden anderen Modi ausschließlich die LEAF-Level. Bezogen auf den Inhalt der Prozedur würde das bedeuten, dass im Moduls [DETAILED] der Clustered Index ZWEI mal neu erstellt wird (LEAF und B-TREE), da beide eine Fragmentierung von > 10% haben.

Wir haben die Prozedur nur geringfügig geändert (größere Änderungen konnten wir nicht machen, da erst der Vendor eine Freigabe erteilen muss). Das Ergebnis “unserer” Anpassungen sieht wie folgt aus:

SELECT 'ALTER INDEX ' + r.name + ' ON ' + @TableName +
       CASE WHEN r.avg_fragmentation_in_percent < 30.0 AND r.index_id != 1
            THEN ' REORGANIZE'
            ELSE ' REBUILD'
        END
FROM    (
            SELECT  i.object_id,
                    i.name,
                    i.index_id,
                    ps.avg_fragmentation_in_percent
            FROM    sys.dm_db_index_physical_stats (db_id(), @Table_Id, DEFAULT, DEFAULT, 'LIMITED') AS ps INNER JOIN sys.indexes i
                    ON (
                          ps.Object_id = i.Object_id AND
                          ps.Index_id = i.Index_id
                        )
            WHERE    ps.avg_fragmentation_in_percent > 10.0
        ) AS r

• Die Abfrage nach dem zu untersuchenden Objekt wurde IN die DMV verlagert, indem die Variable @Table_Id als Parameter für die DMV übergeben wird
• Die Auswertung erfolgt nicht mehr vollständig sondern nur auf Ebene des LEAF-Levels, der ja den Datenbestand des Index repräsentiert

technisches Fazit

Insgesamt wurde das komplette System durch einen ineffektive Indexoptimierungs-Strategie vollständig lahm gelegt. Durch das Verlagern der Filterung in die DMV konnte das IO – gesehen auf 650 Relationen – bei jedem Durchlauf um mehrere GB reduziert werden, da eine Abfrage aller Relationen für jeden Durchlauf unterbunden werden konnte. Weiterhin wurde als Modus für die DMV [LIMITED] gewählt, da es vollkommen ausreichend ist, nur den LEAF-Level des Index auf Fragmentierung zu untersuchen. Sofern es tatsächlich intensiverer Analysen bedarf, sollten solche Aktionen eher manuell als automatisch gemacht werden.

Von ursprünglich 18 – 22 Stunden Ausführungszeit wurde der gesamte Prozess auf 22 Minuten reduziert; jedoch versteht sich von selbst, dass diese Zeit selbst nur relativ ist, da eine mögliche Neuorganisation von Indexen im konkreten Fall nicht durchgeführt wurde.

persönliches Fazit

Warum diesmal ein persönliches Fazit, mag sich der Eine oder Andere fragen. Nun, ich bin seit über 20 Jahren im Projektgeschäft und speziell im Bereich SQL Server unterwegs. Seit den letzten 6 Jahren fast ausschließlich für gobale Konzerne, die entweder eine eigene IT-Abteilung oder aber die IT-Abteilung komplett ausgelagert haben. Das gleiche gilt auch für die eingesetzten Systeme. Sie werden entweder von Vendoren eingekauft oder aber von Entwicklungsteams off shore entwickelt. In diesem Fall war es eine “Out of the Box” Lösung.

Spricht man mit einem Techniker des Vendors und fragt, warum der Prozess so lange dauert und was man dagegen tun könnte, erhält man als Antwort, dass es sicherlich ein IO-Problem / Storage / Netzwerk / sonstige Hardware ist. Man möge doch die Datenbank auf einem dedizierten Server mit … bla bla bla bla… Mir ist bei diesen Aussagen schlicht und einfach die Kinnlade heruntergefallen. Wie kann man einem Kunden so einen Unsin erzählen? Da sprach der Blinde von der Farbe.

Sehr häufig kommt es vor, dass ich hinzu gezogen werde, wenn “das Kind in den Brunnen gefallen ist”. Das hängt damit zusammen, dass meine Aufgaben primär im Engineering und im 3rd-Level Support liegen. In diesem speziellen Fall war die Produktionsumgebung für mehrere Stunden nicht nutzbar, weil der Prozess das System stellenweise komplett lahm gelegt hat – der Grund war ein schlechtes Design UND vollkommene Unkenntnis über Verfahren, die angewendet wurden.

Es steht mir nicht zu, zu bewerten, ob ein Entwickler schlecht oder gut ist – aber es war in diesem Fall so , dass eine Lösung implementiert worden ist, die so eventuell aus dem Internet mittels COPY und PASTE in eine “Prozedur” kopiert worden ist, ohne Gedanken über die Auswirkungen gemacht zu haben. Hier fehlte jegliche Sachkenntnis. Sei es der Unterschied von REBUILD und REORG oder die Bedingungen im CASE-Konstrukt und last but not least die Kenntnis über eingesetzte Werkzeuge (DMV).

Es wäre wünschenswert, wenn sich jemand, der sich Entwickler nennt, im Vorfeld mit der Technologie auseinander setzt und nicht nur oberflächlich Themen aus dem Handbuch überfliegt und/oder aus dem Internet kopiert. Der Leidtragende ist der Kunde, der für dieses – in der Regel dann auch noch sehr teuren – mangelhafte Produkt zahlen muss. Das schadet nicht nur dem Kunden sondern stellt auch die Fähigkeiten des Vendors in Frage.

Herzlichen Dank für’s Lesen