Donnerstag, 6. Juni 2013

Aggregationsfunktionen beschleunigen

Wie häufig werden in Abfragen Aggregationen verwendet, um die Anzahl von Datensätzen, das Minimum, das Maximum oder andere Aggregationen zu ermitteln? Solche Aufgabenstellungen kommen in fast jeder Datenbankanwendung – irgendwie – vor. Die Standardabfragen hierzu lautet in der Regel:

SELECT AGGREGATION(*) FROM dbo.myRelation [WHERE Attribute = Predicate]

SELECT [Attribute], AGGREGATION(*) FROM dbo.myRelation [WHERE Attribute = Predicate] GROUP BY [Attribute]

Sehr häufig lassen sich solche Abfragen deutlich in der Ausführung beschleunigen, wenn man ein paar Hinweise für die Verwendung von Aggregationen berücksichtigt. Der nachfolgende Artikel verwendet COUNT()/COUNT_BIG() als Beispiel; es lässt sich aber auch auf alle anderen Aggregatsfunktionen anwenden.

Für die Beispiele wird eine Relation mit folgender Basisstruktur erstellt:

CREATE TABLE dbo.tbl_Members
(
    SId
          int IDENTITY(1,1)  NOT NULL,
    FirstName
    varchar(50)        NULL,
    LastName
     varchar(50)        NOT NULL,
    MemberSince
  date               NOT NULL,

    CONSTRAINT pk_tbl_Members_SId PRIMARY KEY CLUSTERED (SId)
)

Die Relation besitzt einen Clustered Index mit dem Attribut [SId] als Clustered Key. Das Attribut [FirstName] erlaubt NULL-Werte. Diese Relation hat insgesamt 80.000 Datensätze. Von diesen 80.000 Datensätzen haben 195 Datensätze einen NULL-Wert im Attribut [FirstName].

Beginnen wir zunächst mit einer einfachen Abfrage aller Datensätze ungeachtet jeglicher Gruppierungen. Das Script erzeugt folgende Ergebnisse in Bezug auf IO und Ausführungsplan:

SET STATISTICS IO ON;
SET STATISTICS PROFILE ON;
GO

SELECT COUNT(*) FROM dbo.tbl_Members;

tbl_Members-Tabelle. Scananzahl 1, logische Lesevorgänge 352, physische Lesevorgänge 0,…

PROFILE_STATISTICS_01

EXECUTION_PLAN_01

Bei der Analyse des obigen – einfachen – Statements fallen zwei Dinge sofort ins Auge:

  • Der Ausdruck “COUNT(*)” wird explizit zu einem INT konvertiert (Zeile 2)
  • Es wird für die Ermittlung der Gesamtzahl aller Datensätze ein Clustered Index Scan durchgeführt (Zeile 4).
    Da es keine Predicates gibt, wird also das vollständige Datenvolumen von 2,75 MB für die Ermittlung benötigt!
  • Die Konvertierung in den Datentypen INT hängt mit der Besonderheit zusammen, dass COUNT() den Datentypen INT zurückliefert. Will man die Konvertierung (CPU-Kosten) vermeiden, ist es besser, gleich COUNT_BIG() zu verwenden (wie nachfolgend erkennbar wird). Ebenfalls von Bedeutung ist die Verwendung des Clustered Index für die Ermittlung der Gesamtzahl; für das vorliegende Beispiel in diesem Artikels ist eine andere Wahl nicht möglich, da der Clustered Index der einzige vorhandene Index in der Relation ist.

    Während ein Clustered Index IMMER die Relation selbst ist, besteht die Möglichkeit, mit Non clustered Indexen nur die Attribute zu berücksichtigen, die für eine Abfrage tatsächlich benötigt werden. Überträgt man das auf die Struktur der Relation, die für die Tests verwendet wird, besitzt das Attribut [SId] den kleinsten vorhandenen Datentyp. Er wird zwar als Clustered Key verwendet, hat aber – für Aggregationen – den Nachteil, dass er alle anderen Daten (FirstName, LastName, MemberSince) wie ein den Schweif eines Kometen hinter sich her zieht. Wird ein Index erzeugt, der ausschließlich das Attribut mit dem kleinsten Datentypen berücksichtigt, lässt sich IO reduzieren. Der folgende Index wird für die Relation neu angelegt

    CREATE INDEX ix_tbl_Members_COUNT ON dbo.tbl_Members (SId);

    Eine Analyse der bestehenden Indexe für die Relation kann mit der folgenden Abfrage durchgeführt werden; hierbei interessiert die Anzahl der Datenseiten und Datensätze.

    SELECT i.name,
            i.type_desc,
            ps.index_depth,
            ps.page_count,
            ps.record_count
    FROM    sys.indexes i INNER JOIN sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.tbl_Members', 'U'), DEFAULT, DEFAULT, 'DETAILED') ps
            ON
    (
                 i.index_id = ps.index_id AND
                 i.object_id = ps.object_id
               );

    INDEX_ANALYSIS_01

    Auf dem ersten Blick sieht man, dass der neu angelegte Index ~70% kleiner ist. Benötigt der Clustered Index 351 Datenseiten für die Speicherung so bedarf es für den neu angelegten Index lediglich 100 Datenseiten. Ein Blick “in” die Indexstrukturen zeigt den unmittelbaren Zusammenhang.

    Zunächst werden die belegten Datenseiten des Indexes ausgegeben. Hierzu gibt es zwei Möglichkeiten, die ich im Artikel “Neue DMV für Struktur der Datenseiten (Pages)” beschrieben. Da ich mit Microsoft SQL Server 2012 arbeite, verwende ich die DMF [sys].[dm_db_database_page_allocations]. Aus dem Ergebnis nehme ich zur Demonstration eine beliebige Indexseite (im Beispiel 248). Bitte achten Sie darauf, dass diese Informationen sich von Ihren Ergebnissen unterscheiden können.

    INDEX_PAGES_01

    DBCC TRACEON (3604);
    DBCC PAGE ('db_demo', 1, 248, 3);

    INDEX_PAGES_02

    Wie man erkennen kann, befindet sich auf einer Indexseite ausschließlich das Attribut [SId] als Index Key. Insgesamt (bedingt durch KeyHashValue) ergibt sich eine Zeilengröße von 8 Bytes (tatsächlich sind es ein paar mehr, sind für dieses Thema irrelevant!). Führt man die obige Abfrage – inklusive der minimalen Optimierung durch COUNT_BIG() – erneut aus, erhält man die folgenden Ergebnisse (Zum Vergleich wird die Abfrage ebenfalls unter Verwendung des Clustered Index ausgeführt, um die Ausführungspläne besser zu vergleichen!).

    SET STATISTICS IO ON;
    SET STATISTICS PROFILE ON;
    GO

    SELECT COUNT_BIG(*) FROM dbo.tbl_Members;

    tbl_Members-Tabelle. Scananzahl 1, logische Lesevorgänge 101, physische Lesevorgänge 0,…

    SELECT COUNT_BIG(*) FROM dbo.tbl_Members WITH (INDEX (pk_tbl_Members_SId ));

    tbl_Members-Tabelle. Scananzahl 1, logische Lesevorgänge 352, physische Lesevorgänge 0,…

    PROFILE_STATISTICS_02

    EXECUTION_PLAN_02

    Drei Auffälligkeiten sind zu beobachten:

  • Auf Grund der Tatsache, das COUNT() durch COUNT_BIG() ersetzt wurde, wird keine implizite Konvertierung mehr durchgeführt
  • Ohne Angabe von Indexhinweisen nimmt Microsoft SQL Server immer den Index mit dem geringsten I/O für die Operation
  • Bedingt durch das reduzierte I/O ist die ursprüngliche Abfrage um ca. 30% schneller
  • Verwendung von Predicates und GROUP BY

    Wie verhält es sich nun aber, wenn Predicates und Gruppierungen hinzukommen? Zunächst eine Abfrage aller Mitglieder aus dem Jahre 2012:

    SET STATISTICS IO ON;

    SELECT  COUNT_BIG(*)
    FROM    dbo.tbl_Members
    WHERE   MemberSince >= '20130101' AND
            MemberSince <
    '20140101';

    Sowohl I/O als auch der Ausführungsplan lassen bereits erahnen, welche Probleme Microsoft SQL Server mit der Abfrage hat:

    tbl_Members-Tabelle. Scananzahl 1, logische Lesevorgänge 352, physische Lesevorgänge 0,…

    EXECUTION_PLAN_03

    Für die Ermittlung von ca. 350 Datensätzen musste Microsoft SQL Server erneut eine vollständige Suche in der Relation selbst durchführen (Clustered Index Scan). Die Ursache ist schnell erklärt; das Attribut [MemberSince] ist nicht indiziert und aus diesem Grund muss Microsoft SQL Server erneut die vollständige Tabelle durchsuchen. Auch in solchen Fällen kann durch die Implementation eines Indexes die Abfrage optimiert werden:

    CREATE INDEX ix_tbl_Members_MemberSince ON dbo.tbl_Members (MemberSince);

    Das Ergebnis gibt uns Recht. Statt 352 Lesevorgänge sind es nur noch 2!

    tbl_Members-Tabelle. Scananzahl 1, logische Lesevorgänge 2, physische Lesevorgänge 0,…

    PROFILE_STATISTICS_03

    Aus dem Ausführungsprofil ist erkennbar, dass der neu erstellte Index für die Abfrage verwendet wird. Statt eines Index Scans kann der Index mit einer deutlich performanteren SEEK-Operation verwendet werden, da das Predicate als “SEEK-Predicate” verwendet werden kann (WHERE-Einschränkung wird vollständig durch Index abgedeckt!).

    Häufiger als obige Konstellationen werden Aggregationen jedoch im Zusammenhang mit Gruppierungen verwendet. Die nächsten zwei Beispiele zeigen solche typischen Gruppierungsabfragen:

    Abfrage 1

    Abfrage 2

    SELECT  YEAR(MemberSince),
            COUNT_BIG(*)
    FROM    dbo.tbl_Members
    GROUP BY
           YEAR(MemberSince)
    ORDER BY
            YEAR(MemberSince);
    SELECT LEFT(UPPER(LastName), 1),
          
    COUNT_BIG(SId)
    FROM   dbo.tbl_Members

    GROUP BY
           LEFT(UPPER(LastName), 1)
    ORDER BY
           LEFT(UPPER(LastName), 1);
     

    Die Ausführungsprofile und Ausführungspläne beider Beispiele sehen wie folgt aus:

    EXECUTION_PLAN_04

    Abfrage 1 kann den Index [ix_tbl_Members_MemberSince] verwenden, während für die zweite Abfrage kein geeigneter Index zur Verfügung steht. In diesem Fall muss wieder der Clustered Index bemüht werden. Es stellt sich in solchen Fällen die Frage, ob ein zusätzlicher Index auf dem Attribut [LastName] tatsächlich den erhofften Vorteil bringt. Jeder Index kostet Ressourcen (Datenvolumen) und beim Einfügen neuer Daten müssen diese Indexe mit gepflegt werden.

    Zu Demonstrationszwecken wird ein weiterer Index für die Relation erstellt.

    CREATE INDEX ix_tbl_Members_LastName ON dbo.tbl_Members (LastName);

    Der Index [ix_tbl_Members_LastName] deckt ausschließlich das Attribut [LastName] ab. Anschließend wird die Abfrage erneut ausgeführt und die nachfolgenden Ausführungspläne werden generiert:

    EXECUTION_PLAN_05

    Obwohl der neu erstellte Index verwendet wird, bringt er nicht wirklich den erhofften Vorteil. Insgesamt hat sich das Ausführungsverhältnis gerade mal um 2% geändert. Ursache ist die Größe des Index; der I/O, der durch den neuen Index erzeugt wird, ist nicht signifikant reduziert. Ein Index selbst ist also für Gruppierungen in Aggregationsabfragen nicht unbedingt hilfreich, wenn er nicht deutlich weniger Speicherplatz benötigt als ein Clustered Index. Sobald jedoch mit einer WHERE-Klausel – wie oben gesehen – Einschränkungen vorgenommen werden, wird die Verwendung eines Index sinnvoll

    Aggregatsfunktionen auf NULL-Attributen

    Um zu sehen, wie Microsoft SQL Server NULL-Attribute aggregiert, werden zunächst alle überflüssigen Indexe wieder entfernt, um die Unterschiede besser demonstrieren zu können.

    DROP INDEX ix_tbl_Members_MemberSince ON dbo.tbl_Members;
    DROP INDEX ix_tbl_Members_LastName ON dbo.tbl_Members;

    Anschließend werden zwei – scheinbar – identische Abfragen ausgeführt. Das Ergebnis ist “verblüffend”

    Abfrage 1

    Abfrage 2

    SELECT COUNT_BIG(FirstName)
    FROM dbo.tbl_Members;

    SELECT COUNT_BIG(LastName)
    FROM dbo.tbl_Member

    Die Ausführungsprofile für beide Abfragen sehen folgendermaßen aus:

    PROFILE_STATISTICS_04

    Das erste Ausführungsprofil zeigt die Aggregationsabfrage auf das Attribut [FirstName]. Obwohl – wie oben erwähnt – 195 Datensätze einen NULL-Wert besitzen, wurden während der Operation 80.000 Daten überprüft. Microsoft SQL Server musste den Clustered Index verwenden, da das Attribut NULL-Werte zulässt. Damit kann nicht der – optimierte – Index [ix_tbl_Members_COUNT] verwendet werden; es muss jede Zeile der Relation geprüft werden. In Zeile 2 des Ausführungsprofiles wird dieses Verhalten auch kenntlich gemacht. Statt eines COUNT(*) zählt Microsoft SQL Server vorhandene Daten im Attribut [FirstName]. Aus diesem Verhalten resultiert auch die sicherlich selbst schon mal beobachtete Nachricht von Microsoft SQL Server im [Meldungen]-Fenster nach der Ausführung einer solchen Abfrage:

    SSMS_MELDUNGEN_01

    Die Meldung sagt nur aus, dass alle Zeilen mit einem NULL-Wert in dem zu aggregierenden Attribut aus der Aggregation ausgeschlossen werden und somit nicht gezählt werden.

    Eine Abfrage auf JEDES Attribut, das die Einschränkung [NOT NULL] besitzt, bedarf keiner weiteren Prüfung. Prinzipiell ist es Microsoft SQL Server egal, welches Attribut abgefragt wird; letztendlich kann man auch einen konstanten Wert als Parameter übergeben. Häufig sieht man in den Foren die Bemerkung, dass ein numerischer Wert als Parameter in Aggregatsfunktionen den Spaltenindex repräsentiert. Das ist schlicht und einfach FALSCH. Ein  “Parameterwert” hat KEINEN Einfluss auf vorhandene Attribute und repräsentiert lediglich ein “NOT NULL”! Solche Stereotypen können den optimierten Index verwenden. Das zeigt abschließend das folgende Beispiel:

    -- Abfrage auf NOT NULL Attribut
    SELECT COUNT_BIG(MemberSince)
    FROM dbo.tbl_Members;

    PROFILE_STATISTICS_05

    -- Abfrage auf NOT NULL Attribut
    SELECT COUNT_BIG(LastName)
    FROM dbo.tbl_Members;

    PROFILE_STATISTICS_06

    -- Abfrage auf Konstante
    SELECT COUNT_BIG('Das ist eine Konstante')
    FROM dbo.tbl_Members;

    PROFILE_STATISTICS_07

    SELECT COUNT_BIG(1)
    FROM dbo.tbl_Members;

    PROFILE_STATISTICS_08

    SELECT COUNT_BIG(2)
    FROM dbo.tbl_Members;

    PROFILE_STATISTICS_09

    Wie man aus den Ausführungsprofilen deutlich erkennen kann, verwenden ALLE Abfragen als Aggregation ein COUNT(*) und können den performanten Index [ix_tbl_Members_COUNT] verwenden. Im ersten und zweiten Beispiel werden zwei Attribute aus der Beispielrelation verwendet, die als Einschränkung ein “NOT NULL” besitzen. Durch diese Einschränkung kann Microsoft SQL Server einen (den kleinsten) Index verwenden, der alle Zeilen der Relation abdeckt.

    Gleiches gilt auch für die drei weiteren Abfragen, die jedoch als Übergabeparameter kein Attribut erhalten sondern beliebige konstante Werte. Besondere Beachtung – in Bezug auf die Aussage, dass ein numerischer Wert immer das Attribut in der Relation bezeichnet – gilt den beiden letzten Abfragen. Wäre es tatsächlich so, wie immer wieder in den Foren behauptet wird, könnte die letzte Abfrage nicht den optimierten Index verwenden sondern müsste (wie weiter oben bereits gezeigt) einen Clustered Index Scan durchführen, da ja das Attribut #2 in der Relation durch das Attribut [FirstName] repräsentiert wird. Und dieses Attribut hat keine Einschränkung “NOT NULL”!

    Fazit

    Aggregationen sind aus einer Datenbankanwendung nicht weg zu denken. Applikationen benötigen aggregierte Ergebnisse für Dashboards oder für Reports, die kumulierte Daten anzeigen sollen. Durch geschickte Indexierung ist es möglich, diese Aggregationen so zu optimieren, dass Ergebnisse schneller ermittelt werden können und weniger I/O erzeugen.

    Herzlichen Dank fürs Lesen!

    SET STATISTICS IO ON/OFF http://msdn.microsoft.com/de-de/library/ms184361.aspx
    SET STATISTICS PROFILE ON/OFF http://msdn.microsoft.com/de-de/library/ms188752.aspx
    COUNT / COUNT_BIG http://msdn.microsoft.com/en-us/library/ms190317.aspx

    Kommentare :

    1. du kriegst nur noch ein + bei cross-posting :-)

      AntwortenLöschen
    2. Torsten,

      Morgen kommt der Artikel auch auf insidesql.org.
      Habe ich heute leider nicht mehr geschafft. :)

      AntwortenLöschen