SQL Server Skripts für den „3-Minuten Test“

Dieser Abschnitt enthält die create, insert und select Kommandos für den „3-Minuten Test“. Mach den Test doch selbst, bevor du weiter liest.

Die create und insert Anweisungen findest du in diesem Download.

Die Ausführungspläne sind zur besseren Lesbarkeit abgekürzt.

Frage 1 — DATE Anti-Pattern

CREATE INDEX tbl_idx ON tbl (date_column);

SELECT COUNT(*)
  FROM tbl
 WHERE DATEPART(YEAR, date_column) = 2024;

SELECT COUNT(*)
  FROM tbl
 WHERE date_column >= {d'2024-01-01'}
   AND date_column <  {d'2018-01-01'};

|--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1005],0)))
   |--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
      |--Index Scan(OBJECT:(tbl.tbl_idx),
             WHERE:(datepart(year,tbl.date_column)=(2017)))

|--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1013],0)))
   |--Stream Aggregate(DEFINE:([Expr1013]=Count(*)))
      |--Nested Loops(Inner Join)
         |--Merge Interval
         |  |--Concatenation
         |     |--Compute Scalar(DEFINE:(([Expr1005],[Expr1006],[Expr1004])=...))
         |     |  |--Constant Scan
         |     |--Compute Scalar(DEFINE:(([Expr1008],[Expr1009],[Expr1007])=...))
         |        |--Constant Scan
         |--Index Seek(OBJECT:(tbl.tbl_idx),
                 SEEK:(tbl.date_column > [Expr1010]
                   AND tbl.date_column < [Expr1011])
               ORDERED FORWARD)

Frage 2 — Indiziertes Top-N

CREATE INDEX tbl_idx ON tbl (a, date_column);

SELECT TOP 1*
  FROM tbl
 WHERE a = 12
 ORDER BY date_column DESC
 ;

Die Abfrage verwendet den Index (Index Seek) in absteigender Reihenfolge (ORDERED BACKWARD). Beachte, dass keine Sortieroperation aufscheint.

|--Top(TOP EXPRESSION:(1))
   |--Nested Loops(Inner Join)
      |--Index Seek(OBJECT:(tbl.tbl_idx),
      |       SEEK:(a=12.)
      |      ORDERED BACKWARD)
      |--Clustered Index Seek(OBJECT:(tbl.PK__tbl__3213E83F20C1E124),
                        SEEK:(tbl.id=tbl.id)
                       LOOKUP ORDERED FORWARD)

Frage 3 — Spaltenreihenfolge

CREATE INDEX tbl_idx ON tbl (a, b);

SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1;

SELECT *
  FROM tbl
 WHERE b = 1;

DROP INDEX tbl_idx ON tbl;

CREATE INDEX tbl_idx ON tbl (b, a);

SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1;

SELECT *
  FROM tbl
 WHERE b = 1;

Die erste Abfrage kann beide Indizes optimal nutzen:

|--Nested Loops(Inner Join)
   |--Index Seek(OBJECT:(tbl.tbl_idx), 
   |       SEEK:(tbl.a=38. AND tbl.b=1.)
   |     ORDERED FORWARD)
   |--Clustered Index Seek(OBJECT:(tbl.PK__tbl__3213E83F20C1E124),
                     SEEK:(tbl.id=tbl.id)
                    LOOKUP ORDERED FORWARD)

|--Nested Loops(Inner Join)
   |--Index Seek(OBJECT:(tbl.tbl_idx), 
   |       SEEK:(tbl.b=1. AND tbl.a=38.)
   |     ORDERED FORWARD)
   |--Clustered Index Seek(OBJECT:(tbl.PK__tbl__3213E83F20C1E124),
                     SEEK:(tbl.id=tbl.id)
                    LOOKUP ORDERED FORWARD)

Die zweite Abfrage kann den ersten Index nicht optimal nutzen und muss den ganzen Index lesen (Index Scan).

|--Nested Loops(Inner Join)
   |--Index Scan(OBJECT:(tbl.tbl_idx),
   |      WHERE:(tbl.b=1.))
   |--Clustered Index Seek(OBJECT:(tbl.PK__tbl__3213E83F20C1E124),
                     SEEK:(tbl.id=tbl.id)
                     LOOKUP ORDERED FORWARD)

Wenn die Spalten im Index umgedreht werden, können beide Abfragen optimal vom Index profitieren (Index Seek).

|--Nested Loops(Inner Join)
   |--Index Seek(OBJECT:(tbl.tbl_idx), 
   |       SEEK:(tbl.b=1.)
   |      ORDERED FORWARD)
   |--Clustered Index Seek(OBJECT:(tbl.PK__tbl__3213E83F20C1E124],
                     SEEK:(tbl.id=tbl.id)
                    LOOKUP ORDERED FORWARD)

Frage 4 — LIKE

CREATE INDEX tbl_idx ON tbl (text);

SELECT *
  FROM tbl
 WHERE text LIKE 'TJ%';

Der Ausführungsplan zeigt klar, dass ein Index Seek durchgeführt wird. Da der Suchbegriff 'TERM%' nur ganz am ende ein Wildcard-Zeichen hat, kann der komplette Suchbegriff als Index-Zugriffsprädikat genutzt werden.

|--Nested Loops(Inner Join, OUTER REFERENCES:(tbl.id))
   |--Index Seek(OBJECT:(tbl.tbl_idx)
   |             , SEEK:(tbl.text >= 'TÏþ'
   |                 AND tbl.text <  'TK')
   |             ,  WHERE:(tbl.text like 'TJ%')
   |             ORDERED FORWARD
   |            )
   |--Clustered Index Seek(OBJECT:(tbl.tbl_pk)
                 , SEEK:(tbl.id=tbl.id)
                 LOOKUP ORDERED FORWARD
                )

Frage 5 — Index Only Scan

CREATE INDEX tbl_idx ON tbl (a, date_column)

SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
 GROUP BY date_column

SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
   AND b = 1
 GROUP BY date_column

Die erste Abfrage nutzt den Index, um auf der Spalte A zu suchen, kann aber auch die selektierte Spalte DATE_COLUMN aus dem Index lesen. Die zweite Abfrage muss zusätzlich in den Clustered-Index sehen (bzw. RID Lookup (HEAP)), um den Filter auf der Spalte B zu prüfen. Obwohl dieser Zugriff das Ergebnis schmälert, wird die Abfrage viel langsamer.

|--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1006],0)))
   |--Stream Aggregate(GROUP BY:(tbl.date_column)
   |  DEFINE:([Expr1006]=Count(*)))
   |--Index Seek(OBJECT:(tbl.tbl_idx),
           SEEK:(tbl.a=38.)
          ORDERED FORWARD)

|--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1006],0)))
   |--Stream Aggregate(GROUP BY:(tbl.date_column)
      |  DEFINE:([Expr1006]=Count(*)))
      |--Nested Loops(Inner Join)
         |--Index Seek(OBJECT:(tbl.tbl_idx),
         |       SEEK:(tbl.a=38.)
         |     ORDERED FORWARD)
         |--Clustered Index Seek(OBJECT:(tbl.PK__tbl__3213E83F20C1E124),
                           SEEK:(tbl.id=tbl.id),
                          WHERE:(tbl.b=1.)
                          LOOKUP ORDERED FORWARD)