Freitag, 29. März 2013

Inside sys.dm_db_index_physical_stats

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_physical_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 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 gelenkt worden. Hier wurden von den Entwicklern gleich zwei Fehler gemacht, die darauf schließen ließen, dass sie sich über die Anwendung dieser FUNKTION keine Gedanken gemacht haben.

Filterung von Ergebnismengen

Bei dem DMO 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:

Parameter Beschreibung
database_id Eindeutige Id der Datenbank für die eine Statistik der Indexe ausgegeben werden soll.
object_id Eindeutige Id des Objekts (Relation/View), dessen Indexe analysiert werden sollen
Index_Id Eindeutige Id des Indexes, der analysiert werden soll
Partition_Id Bei partitionierten Objekten die Id der Partition, auf dem sich der Index befindet
Mode Name des Modus der Analyse. Hierzu weiter unten deutlich mehr Details

Wie aus der obigen Abfrage hervor geht, wird eine Filterung aller Indexe einer Relation außerhalb der Funktion durchgeführt, Die Variable @Table_Id kommt explizit in einer WHERE-Klausel zum Tragen. Das bedeutet jedoch 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.

Übersicht Relationen

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…

Analyse IO dmv

Die erste Abfrage verwendet das Selektionskriterium IN der Funktion während die zweite (originale) Abfrage die Filterung außerhalb 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. Multipliziert 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. Das DMO stellt für die Analyse von Indexen drei unterschiedliche Verfahren bereit. Diese Verfahren werden durch den Parameter [Modus] entsprechend für die DMF aktiviert.

Modus Beschreibung
LIMITED ermittelt die logische Fragmentierung des LEAF-Levels und die Anzahl der Pages
Dieser Modus wird als Standard verwendet, wenn nicht explizit ein anderer Modus angegeben wird.
SAMPLED Ermittlung der logischen Fragmentierung wie LIMITED
ist die Anzahl der Seiten im Leaf-Level < 10.000, werden ALLE anderen Indexseiten in allen Indexebenen untersucht. Ist die Anzahl der Seiten im Leaf-Level >= 10.000 wird nur jede 1.000 Seite untersucht.
DETAILED Ermittlung der logischen Fragmentierung wie LIMITED
Alle zusätzlichen Informationen (Pages / Records / …) werden ermittelt, indem ALLE anderen Indexseiten in allen Indexebenen untersucht werden.

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.

Analyse IO dmv #2

Ganz klarer Gewinner in diesem Verfahren ist die Abfrage mit Modus [LIMITED], gefolgt von [SAMPLED und Schlusslicht 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 DMF bei Ausführung mit den Modi [DETAILED], [LIMITED], [SAMPLED]

Output of Analysis

Das Ergebnis erklärt sich in Bezug auf die Arbeitsweise der DMF 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 DMF verlagert, indem die Variable @Table_Id als Parameter für die DMF ü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 DMF 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 DMF [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 globale 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 offshore 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 Unsinn 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 (DMO).

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

 

sys.dm_db_index_physical_stats http://msdn.microsoft.com/de-de/library/ms188917.aspx
Inside dm_db_index_… Paul Randall http://www.sqlskills.com/blogs/paul/inside-sys-dm_db_index_physical_stats/
   

Keine Kommentare :

Kommentar veröffentlichen