Mittwoch, 4. Dezember 2013

Wie als Entwickler große Datenmengen testen?

In den letzten Wochen wurde ich zu einem “Notfall” hinzugezogen, bei dessen Problemanalyse es darum ging, dass eine Abfrage für eine einfache Liste (ca. 500.000 betroffene Datenzeilen) sehr langsam ausgeführt wurde. Nachdem wir den Fehler gefunden hatten und die Indexe entsprechend angepasst haben, war der nächste Schritt das Gespräch mit den Entwicklern. Von den Entwicklern haben wir dann folgende – leider immer wieder zu hörende – Aussage bekommen: “Wir haben während unserer Entwicklungsarbeit nicht mit so großen Datenmengen gearbeitet – daher ist uns der Fehler nicht aufgefallen!” Dieser Artikel soll zeigen, wie es auch OHNE große Datenmenge möglich ist, Szenarien mit vielen Datensätzen zu simulieren.

Testumgebung

Für die Tests werden zwei Tabellen mit jeweils 1.000 Datensätzen in der Master-Tabelle und 3.000 Datensätzen in der Child-Tabelle erstellt.

IF OBJECT_ID('dbo.tbl_child', 'U') IS NOT NULL
    DROP TABLE dbo.tbl_child;
    GO
 
IF OBJECT_ID('dbo.tbl_master', 'U') IS NOT NULL
    DROP TABLE dbo.tbl_master;
    GO
 
CREATE TABLE dbo.tbl_master
(
    Id    int             NOT NULL    IDENTITY (1, 1),
    c1    char(200)       NOT NULL,
    c2    varchar(200)    NOT NULL,
    c3    date            NOT NULL,
 
    CONSTRAINT pk_tbl_Master_Id PRIMARY KEY CLUSTERED (Id)
);
GO
 
CREATE TABLE dbo.tbl_child
(
    master_id   int        NOT NULL,
    child_id    int        NOT NULL    IDENTITY (1, 1),
    c1          char(200)  NOT NULL    DEFAULT ('Detailed record'),
 
    CONSTRAINT pk_tbl_child_id PRIMARY KEY CLUSTERED (child_id),
    CONSTRAINT fk_tbl_master_id FOREIGN KEY (master_id)
    REFERENCES dbo.tbl_master (id)
);
GO
 
SET NOCOUNT ON
GO
 
DECLARE @i int = 1
WHILE @i <= 1000
BEGIN
    INSERT INTO dbo.tbl_master (c1, c2, c3)
    VALUES    (
                'Columnvalue: ' + CAST(CAST(RAND() * 1000 AS int) AS varchar(5)),
                'Value of col: ' + CAST(CAST(@i % 100 AS int) AS varchar(5)),
                DATEADD(dd, CAST(RAND() * 100 AS int), getdate())
            )
 
    SET    @i += 1;
END
GO
 
DECLARE    @i int = 1
WHILE @i <= 3000
BEGIN
    INSERT INTO dbo.tbl_child(master_id, c1)
    VALUES    (
                CAST(RAND() * 1000 + 1 AS int),
                'Detailrecord: ' + CAST(CAST(@i % 100 AS int) AS varchar(5))
            )
 
    SET    @i += 1;
END
GO
 
CREATE INDEX ix_tbl_Master_c3 ON dbo.tbl_master (c3);
CREATE INDEX ix_tbl_master_c2 ON dbo.tbl_master (c2);
GO
 
CREATE INDEX ix_tbl_child_master_id ON dbo.tbl_child (master_id) INCLUDE (c1);
GO
Die beiden Tabellen [dbo].{tbl_master] und [dbo].[tbl_child] sind durch eine Foreign Key Beziehung miteinander verbunden. Beide Tabellen verfügen über Indexe, die während weiterer Tests verwendet werden. Für die Tests mit einer Abfrage auf das Attribut [c3] in der Tabelle [dbo].{tbl_master] wird explizit der Index [ix_tbl_master_c3] erstellt. Damit Abfragen auf beide Tabellen effizient ausgeführt werden können, wird für die Tabelle [dbo].{tbl_child] neben dem Clustered Index [pk_tbl_child_Id] ein zusätzlicher Index auf das Fremdschlüssel-Attribut [master_id] gesetzt. Da bei einer Abfrage immer alle Informationen aus [dbo].[tbl_child] ausgegeben werden sollen, wird das Attribut [c1] mittels INCLUDE in den Leaf-Level des Index übernommen.

Vorabanalyse


Basierend auf dem obigen Script in Verbindung mit der eingefügten Datenmenge ergibt sich für die Indexe der Tabellen folgende physikalische Struktur:



SELECT  OBJECT_NAME(i.object_id)    AS    table_name,
        i.index_id,
        i.name,
        i.type_desc,
        us.page_count,
        us.record_count
FROM    sys.indexes i CROSS APPLY sys.dm_db_index_physical_stats
        (
            db_id(),
            i.object_id,
            i.index_id,
            NULL,
            'DETAILED'
        ) us
WHERE   (
            i.object_id = OBJECT_ID('dbo.tbl_master', 'U') OR
            i.object_id = OBJECT_ID('dbo.tbl_child', 'U')
        ) AND
        us.index_level = 0
ORDER BY
        OBJECT_NAME(i.object_id),
        i.index_id;
Für die spätere “Konfiguration” ist die Information über die aktuelle Anzahl der Datensätze in Verbindung mit der Anzahl der Datenseiten, auf denen die Daten gespeichert sind, wichtig. Die Abfrage ermittelt für jeden Index im Leaf-Level (eigentliche Daten) die vorhandenen Datensätze und die Anzahl der Datenseiten.

INDEX_PHYSICAL_STATS_01


Wie ermittelt Microsoft SQL Server die optimale Ausführungsstrategie?


Der gesamte Prozess zum Finden eines geeigneten Ausführungsplans ist sehr komplex und besteht aus mehreren Schritten. Microsoft SQL Server sucht nicht immer den besten Ausführungsplan sondern muss immer einen Mittelweg finden zwischen Suchen eines geeigneten Ausführungsplans und Ausführungsdauer. Würde zum Beispiel das Suchen eines optimalen Ausführungsplans 5 Sekunden dauern um anschließend eine Ausführung von 1 Sekunde zu ermöglichen, wäre der “Vorteil” schnell dahin. Dennoch ist für Microsoft SQL Server für die Ermittlung eines Ausführungsplans Eines besonders wichtig – Statistiken wie ich bereits für einen authentischen Fall unter “Bedeutung von aktuellen Statistiken für Indexe” beschrieben habe.
Um die Wirkungsweise von Statistiken zu untersuchen, soll das folgende – einfache – Beispiel dienen: Die Relation [dbo].[tbl_master] besitzt das Attribut [c3], das mit zufälligen Datumswerten gefüllt ist. Um festzustellen, wie sich die Datumswerte auf die Tabelle verteilen, können die Histogramm-Daten für den entsprechenden Index mit dem folgenden Befehl abgerufen werden:



DBCC SHOW_STATISTICS ('dbo.tbl_master', 'ix_tbl_Master_c3') WITH HISTOGRAM;

DBCC_SHOW_STATISTICS_01


Wie aus der Abbildung erkennbar ist, gibt es für den 05.12.2013 insgesamt 6 Datensätze während es für den 06.12.2013 12 Datensätze gibt (Diese Werte können von Ihren Beispieldaten abweichen!). Führt man nun eine Abfrage auf beide Werte aus, ergeben sich daraus unterschiedliche Ausführungspläne:



SELECT * FROM dbo.tbl_master WHERE c3 = '2013-12-05';
SELECT * FROM dbo.tbl_master WHERE c3 = '2013-12-06';

EXECUTION_PLAN_01


Für die Ausführung der ersten Abfrage verwendet Microsoft SQL Server einen “INDEX SEEK” während für die zweite Abfrage ein “CLUSTERED INDEX SCAN” verwendet wird. Auf Basis von Statistiken trifft Microsoft SQL Server eine Entscheidung um die Abfrage so effizient wie möglich ausführen zu können. Um festzustellen, auf Basis welcher Statistikwerte Microsoft SQL Server die Ausführung der Abfrage durchführt, bewegen Sie die Maus auf die entsprechende physikalische Ausführungsoperation (INDEX SEEK oder CLUSTERED INDEX SCAN):






EXECUTION_PROPERTIES_01EXECUTION_PROPERTIES_02

Für beide Abfragen hat Microsoft SQL Server unterschiedliche Ausführungspläne - basierend auf Statistiken - gewählt. Da die Abfrage für den 05.12.2013 nur 6 Datensätze findet, ist ein INDEX SEEK die optimale Ausführungsstrategie während für die 12 gefundenen Datensätze aus der Abfrage für den 06.12.2013 ein CLUSTERED INDEX SCAN die optimale Ausführungsstrategie zu sein scheint. Genau diesem Problem unterliegen nun jedoch die Entwickler von Systemen, wenn nicht ausreichend Daten zur Verfügung stehen.


Test


Im Testsystem stehen ausschließlich kleine Datenmengen zur Verfügung. Eine Abfrage über [dbo].[tbl_master] und [dbo].[tbl_child] ergibt – basierend auf den Datenmengen – den nachfolgende Ausführungsplan:



SELECT  m.Id,
        m.c3,
        c.*
FROM    dbo.tbl_master m INNER JOIN dbo.tbl_child c
        ON (m.Id = c.master_id)
WHERE   m.c3 = '20131205';

EXECUTION_PLAN_02 
Basierend auf den aktuellen Statistiken ist ein NESTED LOOP die optimale Ausführungsstrategie, da sich in der Tabelle [dbo].[tbl_master] nur 6 Datensätze befinden, die der Bedingung der WHERE-Klausel entsprechend. Wie schaut es aber aus, wenn 1.000 mal so viele Datensätze in der Datenbank vorhanden sind. Um nicht diese Datenmenge vorhalten zu müssen, gibt es einen Trick, mit dem sich der Abfrageoptimierer überlisten lässt (Hinweis: Diese Optionen sind nicht dokumentiert und werden ausschließlich für Informationszwecke von Microsoft bereitgestellt!).



UPDATE STATISTICS dbo.tbl_master pk_tbl_Master_Id WITH ROWCOUNT = 10000000, PAGECOUNT = 300000;
UPDATE STATISTICS dbo.tbl_master ix_tbl_Master_c3 WITH ROWCOUNT = 10000000, PAGECOUNT = 20000;
UPDATE STATISTICS dbo.tbl_master ix_tbl_master_c2 WITH ROWCOUNT = 10000000, PAGECOUNT = 60000;
 
UPDATE STATISTICS dbo.tbl_child pk_tbl_child_id WITH ROWCOUNT = 30000000, PAGECOUNT = 840000;
UPDATE STATISTICS dbo.tbl_child ix_tbl_child_master_id WITH ROWCOUNT = 30000000, PAGECOUNT = 840000;
Zunächst werden für alle vorhandenen Indexe der Tabelle [dbo].[tbl_master] die Statistiken so “manipuliert”, dass Microsoft SQL Server bei der Ausführung der Abfrage von 1.000.000 Datensätzen auf 300.000 Seiten (Clustered Index) und jeweils 20.0000 und 60.000 Datenseiten auf den non clustered Indexen ausgeht. Im Anschluss werden die Statistiken der beiden Indexe für die Tabelle [dbo].[tbl_child] entsprechend angepasst. Führt man die obige Abfrage nun erneut aus – wobei vorher der Plan Cache geleert wird – ergibt sich für den Ausführungsplan ein vollständig anderes Bild:


DBCC FREEPROCCACHE;
GO
 
SELECT  m.Id,
        m.c3,
        c.*
FROM    dbo.tbl_master m INNER JOIN dbo.tbl_child c
        ON (m.Id = c.master_id)
WHERE   m.c3 = '20131205';

EXECUTION_PLAN_03


Aus einem NESTED LOOP wird nun ein MERGE JOIN; auch hat sich das Datenvolumen in den Pfeilen, die zu den nachfolgenden Operatoren führen, geändert. So ist der Pfeil für den Index Scan für [dbo].[tbl_child] deutlich dicker als noch zuvor. Bewegt man die Maus auf die einzelnen Operatoren, wird man überrascht feststellen, dass von einer deutlich höheren Datenmenge ausgegangen wird!


Zusammenfassung


Die oben gezeigte Lösung ist sicherlich keine Allzweckwaffe für Entwickler. Insbesondere, wenn es um sehr komplexe Abfragen geht, mag eine verlässliche Abschätzung der zu erwartenden Datenmenge nicht möglich sein. Dennoch ist diese Option eine – wie ich finde – sehr gute Möglichkeit, für unkomplizierte Abfragen Ausführungspläne zu simulieren, die bereits im Vorfeld als “problematisch” identifiziert werden könnten.
Wäre zum Beispiel der obige MERGE JOIN ein HASH JOIN, so würde ich versuchen, den Index für [dbo].[tbl_child] so zu erstellen, dass das Schlüsselattribut bereits im Index sortiert vorliegt. Warum? Das werde ich in den nächsten Artikeln genauer beleuchten, die sich mit den verschiedenen JOIN-Strategien genauer auseinander setzen. Da nun bald Weihnachten vor der Tür steht, ergibt sich sicherlich der eine oder andere Abend, an dem man zu diesem komplexen Thema ein paar Zeilen schreiben kann.


Herzlichen Dank fürs Lesen!



















FOREIGN KEYhttp://technet.microsoft.com/de-de/library/ms175464.aspx
CREATE INDEXhttp://technet.microsoft.com/en-us/library/ms188783.aspx
DBCC SHOW_STATISTICShttp://technet.microsoft.com/de-de/library/aa258821.aspx
NESTED LOOPhttp://technet.microsoft.com/de-de/library/ms191318.aspx
MERGE JOINhttp://technet.microsoft.com/de-de/library/ms190967.aspx
Inside the SQL Query Optimizerhttp://www.red-gate.com/community/books/inside-sql-server-query-optimizer

Keine Kommentare:

Kommentar veröffentlichen