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


Diese Seite für

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 Ausführungspläne sind zur besseren Lesbarkeit abgekürzt.

Tabellen Setup

Die Tabelle TBL wird als Clustered-Index auf dem Primärschlüssel angelegt. Stattdessen kann man mittels PRIMARY KEY NONCLUSTERED auch ohne Clustered-Index testen.

CREATE TABLE tbl (
  id          NUMERIC NOT NULL,
  date_column DATE,
  a           NUMERIC,
  b           NUMERIC,
  text        VARCHAR(255),
  state       CHAR(1),
  CONSTRAINT tbl_pk PRIMARY KEY (id)
);
GO
IF OBJECT_ID('rand_helper') IS NOT NULL
   DROP VIEW rand_helper;
GO

CREATE VIEW rand_helper AS SELECT RND=RAND();
GO
IF OBJECT_ID('random_string') IS NOT NULL
   DROP FUNCTION random_string;
GO

CREATE FUNCTION random_string (@maxlen int)
   RETURNS VARCHAR(255)
AS BEGIN
   DECLARE @rv VARCHAR(255)
   DECLARE @loop int
   DECLARE @len int

   SET @len = (SELECT CAST(rnd * (@maxlen-3) AS INT) + 3
                 FROM rand_helper)
   SET @rv = ''
   SET @loop = 0

   WHILE @loop < @len BEGIN
      SET @rv = @rv 
              + CHAR(CAST((SELECT rnd * 26
                             FROM rand_helper) AS INT )+97)
      IF @loop = 0 BEGIN
          SET @rv = UPPER(@rv)
      END
      SET @loop = @loop +1;
   END

   RETURN @rv
END
GO
IF OBJECT_ID('random_int') IS NOT NULL
   DROP FUNCTION random_int;
GO

CREATE FUNCTION random_int (@min int, @max int)
   RETURNS INT
AS BEGIN
   DECLARE @rv INT
   SET @rv = (SELECT rnd * (@max) + @min
                FROM rand_helper)
   RETURN @rv
END
GO
WITH generator_first (n) AS
( SELECT 1
   UNION ALL
  SELECT n + 1
    FROM generator_first
   WHERE N < 300
),
 generator (n) AS
( SELECT ROW_NUMBER() OVER (ORDER BY f1.n, f2.n)
    FROM generator_first f1
   CROSS JOIN generator_first f2
)
INSERT INTO tbl
SELECT n
     , DATEADD(day, -n, GetDate())
     , n % 1234
     , [dbo].random_int(1, 10)
     , [dbo].random_string(20)
     , CASE WHEN (n % 5) = 0 THEN 'X' ELSE 'A' END
  FROM generator
OPTION (MAXRECURSION 30000);
GO
exec sp_updatestats;
GO

Frage 1 — DATE Anit-Pattern

CREATE INDEX tbl_idx ON tbl (date_column);
GO

SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON

SELECT text, date_column
  FROM tbl
 WHERE DATEPART(yyyy, date_column) = 2011;
GO

SELECT text, date_column
  FROM tbl
 WHERE date_column >= CAST('2011-01-01' AS DATE)
   AND date_column <  CAST('2012-01-01' AS DATE)
GO

SET STATISTICS PROFILE OFF

DROP INDEX tbl_idx ON tbl;
GO

Der erste Ausführungsplan liest den ganze Index (Index Scan). Der zweite Ausführungsplan verwendet den Index (Index Seek).

|--Nested Loops(Inner Join)
     |--Index Scan(OBJECT:([tbl].[tbl_idx]),
             WHERE:(datepart(year,[tbl].[date_column])=2011))
     |--Clustered Index Seek(OBJECT:([tbl].[tbl_pk]),
              SEEK:([tbl].[id]=[tbl].[id]))

Table 'tbl'. Scan count 1, logical reads 806,
             physical reads 0, read-ahead reads 0, lob logical reads 0,
             lob physical reads 0, lob read-ahead reads 0.



|--Nested Loops(Inner Join)
     |--Index Seek(OBJECT:([tbl].[tbl_idx]),
              SEEK:([tbl].[date_column] >= '2011-01-01'
                AND [tbl].[date_column] < ' 2012-01-01'))
     |--Clustered Index Seek(OBJECT:([tbl].[tbl_pk]),
              SEEK:([tbl].[id]=[tbl].[id]))

Table 'tbl'. Scan count 1, logical reads 628,
             physical reads 0, read-ahead reads 0, lob logical reads 0,
             lob physical reads 0, lob read-ahead reads 0.

Frage 2 — Indiziertes Top-N

CREATE INDEX tbl_idx ON tbl (a, date_column);
GO

SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON

DECLARE @a numeric
SET @a = 123;
SELECT TOP 1 id, date_column
  FROM tbl
 WHERE a = @a
 ORDER BY date_column DESC
GO

SET STATISTICS PROFILE OFF

DROP INDEX tbl_idx ON tbl;
GO

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

|--Top(TOP EXPRESSION:((1)))
     |--Index Seek(OBJECT:([tbl].[tbl_idx]),
             SEEK:([tbl].[a]=[@a]) ORDERED BACKWARD)

Table 'tbl'. Scan count 1, logical reads 3,
             physical reads 0, read-ahead reads 0, lob logical reads 0,
             lob physical reads 0, lob read-ahead reads 0.

Frage 3 — Spaltenreihenfolge

CREATE INDEX tbl_idx ON tbl (a, b);
GO

SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON

DECLARE @a numeric
      , @b numeric;
SET @a = 123;
SET @b = 1;

SELECT id, a, b
  FROM tbl
 WHERE a = @a
   AND b = @b;
GO

DECLARE @b numeric;
SET @b = 1;
SELECT id, a, b
  FROM tbl
 WHERE b = @b;
GO

SET STATISTICS PROFILE OFF

DROP INDEX tbl_idx ON tbl;
GO

Die zweite Abfrage liest den ganzen Index (Index Scan). Wenn die Spalten im Index umgedreht werden, können beide Abfragen optimal vom Index profitieren (Index Seek).

|--Index Seek(OBJECT:([tbl].[tbl_idx]),
         SEEK:([tbl].[a]=[@a]
          AND  [tbl].[b]=[@b]) ORDERED FORWARD)


Table 'tbl'. Scan count 1, logical reads 3,
             physical reads 0, read-ahead reads 0, lob logical reads 0,
             lob physical reads 0, lob read-ahead reads 0.

|--Index Scan(OBJECT:([tbl].[tbl_idx]),
        WHERE:([tbl].[b]=[@b])


Table 'tbl'. Scan count 1, logical reads 372,
             physical reads 0, read-ahead reads 0, lob logical reads 0,
             lob physical reads 0, lob read-ahead reads 0.
|--Index Seek(OBJECT:([tbl].[tbl_idx]),
         SEEK:([tbl].[b]=[@b]
           AND [tbl].[a]=[@a]) ORDERED FORWARD)

Table 'tbl'. Scan count 1, logical reads 3,
             physical reads 0, read-ahead reads 0, lob logical reads 0,
             lob physical reads 0, lob read-ahead reads 0.



|--Index Seek(OBJECT:([tbl].[tbl_idx]),
         SEEK:([tbl].[b]=[@b]) ORDERED FORWARD)

Table 'tbl'. Scan count 1, logical reads 40,
             physical reads 0, read-ahead reads 0, lob logical reads 0,
             lob physical reads 0, lob read-ahead reads 0.

Frage 4 — LIKE

CREATE INDEX tbl_idx ON tbl (text);
GO

SET STATISTICS PROFILE ON

SELECT id, text
  FROM tbl
 WHERE text LIKE '%TERM%';

SET STATISTICS PROFILE OFF

DROP INDEX tbl_idx ON tbl;
GO

Das Wildcard am Anfang des Suchbegriffes macht einen Index Seek unmöglich, sodass der ganze Index gelesen werden muss (Index Scan).

|--Index Scan(OBJECT:([tbl].[tbl_idx]), 
       WHERE:([tbl].[text] like '%TERM%'))

Frage 5 — Index Only Scan

CREATE INDEX tab_idx ON tbl (a, date_column);
GO

SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON

DECLARE @a numeric;
SET @a = 123;

SELECT date_column, count(*)
  FROM tbl
 WHERE a = @a
 GROUP BY date_column;
GO


DECLARE @a numeric
      , @b numeric;
SET @a = 123;
SET @b = 1;

SELECT date_column, count(*)
  FROM tbl
 WHERE a = @a
   AND b = @b
 GROUP BY date_column;
GO

DROP INDEX tbl_idx ON tbl;
GO

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:([...])
     |--Stream Aggregate(GROUP BY:([tbl].[date_column]))
          |--Index Seek(OBJECT:([tbl].[tab_idx]),
                  SEEK:([tbl].[a]=[@a]) ORDERED FORWARD)

Table 'tbl'. Scan count 1, logical reads 3,
             physical reads 0, read-ahead reads 0, lob logical reads 0,
             lob physical reads 0, lob read-ahead reads 0.



|--Compute Scalar(DEFINE:[...])
     |--Stream Aggregate(GROUP BY:([tbl].[date_column]))
          |--Nested Loops(Inner Join)
               |--Index Seek(
                      OBJECT:([tbl].[tab_idx]),
                        SEEK:([tbl].[a]=[@a]))
               |--Clustered Index Seek(
                      OBJECT:([tbl].[tbl_pk]),
                        SEEK:([tbl].[id]=[tbl].[id]),
                       WHERE:([tbl].[b]=[@b]))

Table 'tbl'. Scan count 1, logical reads 235,
             physical reads 0, read-ahead reads 0, lob logical reads 0,
             lob physical reads 0, lob read-ahead reads 0.

Über den Autor

Photo of Markus Winand
Markus Winand stimmt Entwickler auf SQL-Performance ein. Er hat das Buch SQL Performance Explained veröffentlicht und bietet inhouse Schulungen sowie Tuning-Leistungen auf http://winand.at/ an.