SQL Server Skripts für „Testen und Skalierbarkeit“

Dieser Abschnitt enthält die create und insert Anweisungen sowie den T-SQL Code um den Skalierbarkeitstest aus Kapitel 3, „Performance und Skalierbarkeit“ in einer SQL Server Datenbank durchzuführen.

Der Test muss auf einer Tabelle laufen, die groß genug ist, um Caching-Effekte auszuschließen. Abhängig von der Umgebung, muss die Tabelle eventuell noch größer angelegt werden, um ein vergleichbares Ergebnis zu erhalten.

CREATE TABLE scale_data (
   section NUMERIC NOT NULL,
   id1     NUMERIC NOT NULL,
   id2     NUMERIC NOT NULL,
   UNIQUE  (section, id1)
);

Bemerkungen:

• Es gibt keine Primärschlüssel um die Datengenerierung einfach zu halten.

• Die Tabelle wird anfangs ohne Index angelegt (folgt später).

• Die Tabelle hat keine JUNK-Spalte, da auf die Tabelle während des Tests nicht zugegriffen wird.

DECLARE @section INT
SET @section = 300

WHILE (@section >= 0) BEGIN

   WITH generate_series (n) AS (
      SELECT 1
      UNION ALL
      SELECT n + 1
        FROM generate_series
       WHERE N < 3000
   ), generate_series2 (n) AS (
      SELECT ROW_NUMBER() OVER(ORDER BY g1.n, g2.n)
        FROM generate_series g1
       CROSS JOIN generate_series g2
       WHERE g2.n <= @section
   )
   INSERT INTO scale_data
   SELECT @section, gen.*
        , CEILING(ABS(CAST(NEWID() AS BINARY(6)) %100))
     FROM generate_series2 gen
    WHERE gen.n <= @section * 3000
   OPTION(MAXRECURSION 32767);

   SET @section = @section -1
END;
GO

Bemerkungen:

• Der Code erzeugt 300 Sektionen. In deiner Umgebung brauchst Du eventuell noch mehr. Wenn Du die Anzahl der Sektionen erhöhst, musst du auch den zweiten Generator erweitern. Er muss mindestens 3000 x <anzahl der Sektionen> Zeilen erzeugen.

• Die Tabelle benötigt einige Gigabytes.

CREATE INDEX scale_slow ON scale_data(section, id1, id2);
GO

Bemerkungen:

• Der Index benötigt ebenfalls einige Gigabyte.

• Die Ausführung kann sehr lange dauern.

CREATE VIEW rand_helper AS SELECT rnd=RAND();
GO 

CREATE FUNCTION [dbo].test_scalability (@n int)
   RETURNS @table TABLE
( section  NUMERIC NOT NULL PRIMARY KEY,
  duration NUMERIC NOT NULL,
  rows     NUMERIC NOT NULL)
AS BEGIN
   DECLARE @strt DATETIME2
   DECLARE @iter INT
   DECLARE @xsec INT
   DECLARE @xcnt INT
   DECLARE @xrnd INT

   SET @iter = 0
   WHILE (@iter < @n) BEGIN
      SET @xsec = 0
      WHILE (@xsec < 300) BEGIN
         SELECT @xrnd=CEILING(rnd * 100) FROM rand_helper;
         SET @strt = SYSDATETIME()

         SELECT @xcnt = COUNT(*)
           FROM (SELECT *
                   FROM scale_data
                  WHERE section=@xsec
                    AND id2=@xrnd) tlb; 

         IF @iter = 0 BEGIN
           INSERT INTO @table
           VALUES ( @xsec
                  , datediff(microsecond, @strt, SYSDATETIME())
                  , @xcnt);
         END; ELSE BEGIN
           UPDATE @table
              SET duration = duration 
                  + datediff(microsecond, @strt, SYSDATETIME())
                , rows = rows + @xcnt
            WHERE section = @xsec
         END;
         SET @xsec = @xsec + 1
      END;
      SET @iter = @iter + 1
   END;

   RETURN;
END;

GO

Bemerkungen:

• Die TEST_SCALABILITY.RUN Funktion liefert eine Tabelle als Ergebnis.

• Die Anzahl der Sektionen ist hard-coded (300, hervorgehoben).

• Die Anzahl der Wiederholungen ist konfigurierbar.

• Der RAND_HELPER View wird benutzt um RAND() in einer benutzerdefinierten Funktion zu verwenden.

Das folgende select ruft die Funktion auf, und übergibt die Testabfrage als Parameter:

SELECT * FROM [dbo].[test_scalability] (10);

Die Gegenprobe, mit einem besseren Index:

CREATE INDEX scale_fast ON scale_data(section, id2, id1);
GO

SELECT * FROM [dbo].[test_scalability] (10);
GO