Ein von mir sehr geschätzter Kollege – der SQL Paparazzi der PASS Deutschland – Dirk Hondong (t | w) – hat mich während meines Vortrags bei der PASS Usergroup in Köln gefragt, inwieweit man belegen kann, ob die Änderung der Sortierung einer Spalte eine reine Metadaten-Operation ist oder ob eine solche Änderung auch die Änderung von Daten nach sich zieht. Die Frage fand ich so interessant, dass ich mich gleich an die Untersuchung gemacht hatte, um selbst festzustellen, welche Auswirkungen die Änderung der Sortierung auf die entstehenden Transaktionen haben.

Inhaltsverzeichnis

Warum eine Datenbank oder ein Objekt eine Sortierung braucht.

Sobald mit einem Datenbanksystem gearbeitet wird, werden Tabellen angelegt, Daten gespeichert und mit Hilfe von Indexen organisiert. Für die Reihenfolge der Daten in einem Index gibt es zwei grobe Richtungen. Handelt es sich um numerische Werte, ist die Sortierung einer Datenbank oder einer Spalte irrelevant. Sobald jedoch ein Index auf ein nicht numerisches Feld angewendet wird, muss Microsoft SQL Server für die richtige Sortierung der Werte im Index die Datenbank- oder Spaltensortierung berücksichtigen. Das nachfolgende Beispiel zeigt eine Tabelle mit zwei Textspalten. Beide Textspalten haben unterschiedliche Sortierungen und werden mit einem dedizierten Index pro Spalte versehen.

/* Create the demo table with different collations */
CREATE TABLE dbo.demo_table
(
    Id    INT      NOT NULL,
    c1    CHAR(1)  COLLATE Latin1_General_CI_AS    NOT NULL,
    c2    CHAR(1)  COLLATE Latin1_General_BIN      NOT NULL
);
 
/* Create an index on each different column */
CREATE INDEX ix_demo_table_c1 ON dbo.demo_table (c1);
CREATE INDEX ix_demo_table_c2 ON dbo.demo_table (c2);
GO
 
/* fill the table with A-Z and a-z */
DECLARE @i INT = 65;
WHILE @i <= 90
BEGIN
    INSERT INTO dbo.demo_table (Id, C1, C2)
    SELECT @i, CHAR(@i), CHAR(@i)
    UNION ALL
    SELECT @i + 32, CHAR(@i + 32), CHAR(@i + 32)
 
    SET @i += 1;
END
GO
 
/* Select the different columns by usage of it's index */
SELECT c1 FROM dbo.demo_table;
SELECT c2 FROM dbo.demo_table;
GO

Für die beiden Abfragen werden die auf den Spalten befindlichen Indexe verwendet und man kann im Ergebnis deutlich erkennen, dass beide Spalten nach unterschiedlichen Mustern sortiert werden.

Die nebenstehende Abbildung zeigt auf der linken Seite die Spalte [c1]. Diese Spalte verwendet eine Sortierung, die Groß- und Kleinschreibung nicht unterscheidet (“_CI_” = Case Insensitive) während die Spalte [c2] eine binäre Sortierung anwendet. Bei der binären Sortierung werden die Werte in der Spalte nach ihrem Binärwert sortiert. Da die Kleinbuchstaben einen höheren Binärwert haben (Großbuchstaben beginnen bei 0x41 und Kleinbuchstaben beginnen bei 0x5B) werden sie erst NACH den Großbuchstaben einsortiert.

Die einstellte Sortierung für eine Spalte, die alphanumerische Werte enthält ist beim Einsatz eines Indexes (egal ob Clustered Index oder Nonclustered Index) ein ausschlaggebendes Moment für die Einordnung der Werte, die in diese Spalte gespeichert werden sollen.

Ändern einer Sortierung

Man kann jederzeit für einzelne Attribute, eine Datenbank oder für den Server die Sortierung ändern. Während die Änderung für Datenbanken und Tabellen nachträglich mit ertragbarem Aufwand verbunden ist, ist die Änderung der Sortierung für den Server mit deutlich mehr Aufwand verbunden. Weitere Informationen zum Ändern der Sortierungen finden sich hier:

Sobald eine Datenbank Daten enthält und es sollen nachträglich Änderungen an der Sortierung vorgenommen werden, dann ist es nur recht, dass man sich Gedanken darüber macht, wie hoch wohl das Transaktionsvolumen dieser Transaktion ist. Sofern es sich nur um Änderungen am Schema handelt (Schemaänderungen), wird das Transaktionsvolumen in einem verträglichen Rahmen bleiben; sollten jedoch die gespeicherten Daten betroffen sein, muss man sich eine entsprechende Strategie zurechtlegen, um die Änderungen sorgfältig zu planen.

Beispielszenario

Um zu prüfen, welche Ressourcen bei der Änderung der Sortierung an der Spalte einer Tabelle beteiligt sind, wird zunächst eine Beispieltabelle mit 1.000 Datensätzen erstellt. Diese Tabelle besitzt – im ersten Beispiel – keine Indexe; es handelt sich also um einen HEAP.

/* Create a HEAP with a few demo data and default collation */
CREATE TABLE dbo.demo_table
(
    Id    INT        NOT NULL    IDENTITY(1, 1),
    C1    CHAR(3)    NOT NULL    DEFAULT ('DE'),
    C2    CHAR(5)    NOT NULL    DEFAULT ('12345')
);
GO
 
/* what is the collation of the string attributes? */
SELECT  C.name            AS column_name,
        S.name            AS type_name,
        C.max_length      AS data_length,
        C.collation_name  AS collation_name
FROM    sys.tables AS T INNER JOIN sys.columns AS C
        ON (T.object_id = C.object_Id) INNER JOIN sys.types AS S
        ON  (
               C.user_type_id = S.user_type_id AND
               C.system_type_id = S.system_type_id
            )
WHERE   T.name = 'demo_table'
ORDER BY
        C.column_id;
GO

Zunächst wird die Tabelle mit der Sortierung der Datenbank angelegt. Die Abfrage zeigt, welche Sortierung für die einzelnen Spalten verwendet werden (in meinem Beispiel ist es Latin1_General_CI_AS).

Anschließend wird diese Tabelle mit 1.000 Datensätzen gefüllt und die Test können beginnen.

Änderung der Sortierung in HEAP

Die Tabelle besitzt keine Indexe – sie ist ein HEAP. Um für eine Spalte eine Eigenschaft zu ändern, muss mit Hilfe von ALTER TABLE … ALTER COLUMN die Eigenschaft angepasst werden. Das nachfolgende Beispiel verwendet eine explizite Transaktion für diese Anpassungen. Diese explizite Transaktion muss verwendet werden, um nach der Aktion festzustellen, welche Ressourcen durch die Aktion gesperrt/verwendet werden. Gleichfalls kann mit Hilfe einer benannten Transaktion der entsprechende Eintrag im Transaktionsprotokoll gefunden werden (siehe Code).

/* to monitor the behavior of the transaction we wrap it in a named transaction */
BEGIN TRANSACTION ChangeCollation;
GO
    ALTER TABLE demo_table ALTER COLUMN C1 CHAR(3) COLLATE Latin1_General_BIN NOT NULL;
    GO
 
-- what resources are blocked by the transaction?
SELECT  DTL.resource_type,
        DTL.resource_description,
        DTL.request_mode,
        DTL.request_type,
        DTL.request_status,
        CASE WHEN DTL.resource_type = N'OBJECT'
             THEN OBJECT_NAME(DTL.resource_associated_entity_id)
             ELSE NULL
        END        AS resource_Object_Name
FROM    sys.dm_tran_locks AS DTL
WHERE   DTL.request_session_id = @@SPID AND
        DTL.resource_type != N'DATABASE';

-- what has happend in the transaction log?
SELECT  Operation,
        Context,
        AllocUnitName,
        [Page ID],
        [Slot ID]
FROM    sys.fn_dblog(NULL, NULL)
WHERE   [TRANSACTION ID] IN
        (
            SELECT [Transaction ID]
            FROM sys.fn_dblog(NULL, NULL)
            WHERE [Transaction Name] = N'ChangeCollation'
) AND
Context != N'LCX_NULL'
ORDER BY
        [Current LSN],
    [Transaction ID];
 
COMMIT TRANSACTION ChangeCollation;
GO

Zunächst wird die Sortierung der Spalte [C1] umgestellt. Um festzustellen, welche Ressourcen dabei von Microsoft SQL Server verwendet werden, hilft die Funktion [sys].[dm_tran_locks]. Sie zeigt, welche Ressourcen aktuell von offenen Transaktionen verwendet werden.

Die obige Abbildung zeigt, welche Objekte durch die Transaktion gesperrt sind. Die Beispieltabelle [dbo].[demo_table] wird mit einer SCH-M-Sperre versehen. Hierbei handelt es sich um eine Sperre, die gesetzt werden muss, um Änderungen an den Objekten (Schemata) vornehmen zu können. Ebenfalls ist zu erkennen, dass X-Sperren (Exklusivsperren) auf Datensätzen (KEY) liegen. Da es sich nicht um die Benutzertabelle handelt (die ist mit einer SCH-M-Sperre versehen), kann es sich nur um die drei Systemtabellen handeln, die mit einer IX-Sperre versehen wurden.

Ein Blick in das aktive Transaktionsprotokoll bestätigt diesen Verdacht. Tatsächlich befindet sich im Transaktionsprotokoll lediglich EIN Transaktionseintrag, der im Zusammenhang mit der Änderung der Sortierung steht!

Änderung der Sortierung in einem Clustered Index

Wie sieht es mit der Änderung der Sortierung aus, wenn die Tabelle ein Clustered Index ist und der Schlüssel selbst eine Textspalte ist? Das nachfolgende Skript erstellt eine Tabelle mit einem Clustered Index auf der Spalte [Id]. In diese Tabelle werden ein paar Datensätze eingetragen um anschließend die Sortierung anzupassen.

CREATE TABLE dbo.demo_table
(
    Id    CHAR(4) COLLATE Latin1_General_CS_AI NOT NULL PRIMARY KEY CLUSTERED,
    C1    CHAR(300)    NOT NULL,
    C2    CHAR(500)    NOT NULL
);
GO
 
/* Fill the table with a few values */
DECLARE @i INT = 65;
WHILE @i <= 90
BEGIN
    INSERT INTO dbo.demo_table (Id, C1, C2)
    SELECT CHAR(@i), 'Das ist ein Test', 'Ja, das ist ein Test'
    UNION ALL
    SELECT CHAR(@i + 32), 'Das ist ein Test', 'Ja, das ist ein Test';
 
    SET @i += 1;
END
GO

Versucht man, nachträglich die Sortierung der Spalte [Id] zu ändern, erhält man einen “klassischen” Fehler, der eindeutig darauf hinweist, dass Sortierungen auf indexierte Spalten nicht anwendbar sind.

Diese Fehlermeldung macht im Zusammenhang mit der Effizienz einer DDL-Operation Sinn. Würde Microsoft SQL Server erlauben, dass Sortierungen in Spalten geändert werden, die von einem Index berücksichtigt werden, dann müsste Microsoft SQL Server die Schemasperre auf dem Tabellenobjekt so lange aufrecht erhalten, bis die geänderte Sortierung in jedem betroffenen Index berücksichtigt wurde. Das bedeutet für die Indexe jedoch eine vollständige Neusortierung, da sich – bedingt durch Groß-/Kleinschreibung, Akzente, etc – die Sortierung der Einträge ändert.

Damit die Schemasperre so schnell wie möglich wieder aufgehoben werden kann, sind solche lang laufenden Transaktionen in Microsoft SQL Server nicht erlaubt! Andere Prozesse können auf die Tabelle nicht zugreifen und die Applikationen müssten warten, bis der Sortiervorgang und Neuaufbau der Indexe abgeschlossen ist.

Zusammenfassung

Die Änderung der Sortierung ist ein DDL-Befehl und setzt voraus, dass die betroffenen Spalten einer Tabelle nicht von Indexen verwendet werden. Die eigentliche Operation der Änderung der Sortierung geht mit minimalem Aufwand, da ausschließlich Metadaten geändert werden.

Herzlichen Dank fürs Lesen!

Änderung der Sortierung und Einfluss auf das Transaktionsprotokoll