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

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;

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;

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

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

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

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;

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

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!