von Markus Winand.

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 Anit-Pattern

CREATE INDEX tbl_idx ON tbl (date_column)
SELECT COUNT(*)
  FROM tbl
 WHERE DATEPART(YEAR, date_column) = 2017
SELECT COUNT(*)
  FROM tbl
 WHERE date_column >= {d'2017-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)

Über den Autor

Foto von Markus Winand

Markus Winand lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

Sein Buch bei Amazon kaufen

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Bei Amazon kaufen
(Taschenbuch)

Taschenbuch und PDF auch auf Markus' Webseite erhältlich.

Sein Training

Sein beliebtes Training stimmt Entwickler auf SQL Performance ein.

Erfahren Sie mehr»

„Use The Index, Luke!“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz | CC-BY-NC-ND 3.0 Lizenz