Index Erstellung in SQL Server: Included Columns bereiten Probleme

Überblick

Das MSSQL Feature, das es erlaubt, einen Nonclustered Index um zusätzliche Spalten zu erweitern, wird eingesetzt, um die Performance bestimmter Abfragen zu steigern: Erfolgt eine Abfrage über einen Nonclustered Index und benötigt man nur einen kleinen Teil der Spalten der zugrunde liegenden Tabelle, so erspart man sich den Zugriff auf die eigentliche Tabelle, da die benötigten Spaltendaten durch die Include-Klausel bereits in den jeweiligen Index-Knoten enthalten sind. In bestimmten Fällen - und hier trifft das immer wieder benötigte “it depends” zu - kann es zu unerwünschtem Verhalten kommen. Dies soll in diesem Beitrag näher beleuchtet werden.

Wo ist das Problem?

Die Verwendung von Include-Spalten führt zu einer erhöhten Transaktionsprotokollierung in der TLog Datei und dieses wird in den folgenden Unterkapiteln experimentell dargestellt.

Versuchsaufbau

Es werden zwei leere Datenbanken erstellt, die lediglich jeweils eine gleichnamige Tabelle enthalten:

Datenbank- bzw. Tabellenname:

NonIncludedColumns
IncludedColumns

In diese beiden Tabellen wird ein Testdatensatz eingefügt.
Um die durchgeführten Änderungen im TLog einfacher identifizieren zu können, werden

unmittelbar vor der geplanten Test-Transaktion ein Checkpoint erstellt, so dass sich keine Dirty Pages mehr im Buffer Cache befinden
unmittelbar vor der geplanten Test-Transaktion die Inhalte der TLog-Datei in eine temporäre Tabelle geschrieben. Hierzu wird die nicht dokumentierte Systemfunktion fn_dblog() verwendet
unmittelbar nach der Test-Transaktion die Inhalte der TLog-Datei in eine zweite temporäre Tabelle geschrieben
abschließend wird die Differenz der Inhalte der beiden temporären Tabellen gebildet und zwei der Spalten der Differenzmenge betrachtet

Um den hexadezimalen Inhalt des RowLogs leicht identifizieren zu können, werden für die drei Spalten die folgenden Inhalte Änderungen durchgeführt:

Wert10150x3ADAFA

Anlegen der Test-Datenbanken

USE MSDB
GO

CREATE DATABASE [NonIncludedColumns]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'NonIncludedColumns', FILENAME = N'DB-File-Verzeichnis\NonIncludedColumns.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'NonIncludedColumns_log', FILENAME = N'Log-Verzeichnis\NonIncludedColumns_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 WITH LEDGER = OFF
GO

CREATE DATABASE [IncludedColumns]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'IncludedColumns', FILENAME = N'DB-File-Verzeichnis\IncludedColumns.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'IncludedColumns_log', FILENAME = N'Log-Verzeichnis\IncludedColumns_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 WITH LEDGER = OFF
GO

Anlegen der Tabellen inkl. einer Zeile

Im nächsten Schritt wird in jeder der beiden neu erstellten Datenbanken eine Tabelle erstellt. Beide Tabellen haben die exakt gleiche Struktur, lediglich der Index auf der Spalte Indexspalte enthält in der einen Variante zwei inkludierte Spalten, IntWert und Binärwert. In beiden Tabellen wird eine Zeile angelegt. Zur Identifizierung im Transaktionslog werden die drei Spalten mit den Werten 1, 2 bzw. 0x3456789A initialisiert.

USE NonIncludedColumns
GO 

CREATE TABLE dbo.NonIncludedColumns
(
    ID INT IDENTITY,
    Indexspalte INT NOT NULL,
    IntWert INT,
    Binaerwert BINARY(4) NOT NULL,
    INDEX PK_NonIncludedColumns UNIQUE CLUSTERED (ID)
);

CREATE INDEX IX_NonIncludedColumns ON dbo.NonIncludedColumns (Indexspalte)
GO

INSERT dbo.NonIncludedColumns(Indexspalte, IntWert, Binaerwert)
VALUES(1,2,0x3456789A)

USE IncludedColumns
GO 

CREATE TABLE dbo.IncludedColumns
(
    ID INT IDENTITY,
    Indexspalte INT NOT NULL,
    IntWert INT,
    Binaerwert BINARY(4) NOT NULL,
    INDEX PK_IncludedColumns UNIQUE CLUSTERED (ID)
);

CREATE INDEX IX_IncludedColumns ON dbo.IncludedColumns (Indexspalte)
INCLUDE(IntWert,Binaerwert)
GO

INSERT dbo.IncludedColumns(Indexspalte, IntWert, Binaerwert)
VALUES(1,2,0x3456789A)

Änderung des Datensatzes

Im nächsten Schritt wird, wie bereits oben beschrieben, ein Checkppoint erstellt und der TLog-Inhalt mittels fn_dblog() in eine temporäre Tabelle geschrieben. Anschließend erfolgt die Aktualisierung der Daten mit den markanten Werten 10 (0xA), 15 (0xF) und 0x3ADAFA (“MADAFA”).

-- Ausführung in der Datenbank NonIncludedColumns
use NonIncludedColumns
GO

drop table if exists #StartLSNs
drop table if exists #EndLSNs
checkpoint
select * into #StartLSNs from fn_dblog(null,null)
update dbo.NonIncludedColumns
set Indexspalte = 10
  , IntWert = 15
  , Binaerwert = 0x3ADAFA;
select * into #EndLSNs from fn_dblog(null,null)

-- Ausführung in der Datenbank IncludedColumns
use IncludedColumns
GO

drop table if exists #StartLSNs
drop table if exists #EndLSNs
checkpoint
select * into #StartLSNs from fn_dblog(null,null)
update dbo.IncludedColumns
set Indexspalte = 10
  , IntWert = 15
  , Binaerwert = 0x3ADAFA;
select * into #EndLSNs from fn_dblog(null,null)

Vergleich der Ergebnisse

Als Ergebnis haben wir jetzt die TLog-Informationen vor und nach Ausführung des Update Statements in den temporären Tabellen vorliegen. Hier bilden wir abschließend lediglich die Mengendifferenz der Tabelleninhalte, wobei uns in diesem Zusammenhang lediglich die Spalten [Log Record Length] und [RowLog Contents 0] interessieren.

Dem entsprechend selektieren wir

select [Log Record Length], [RowLog Contents 0], [Log Record] from #EndLSNs
except
select [Log Record Length], [RowLog Contents 0], [Log Record] from #StartLSNs

und erhalten u.a. die folgenden Ergebnisse, die lediglich im Fall der Included Columns das Muster 3ADAFA enthalten.

-- NonIncludedColumns
[Log Record Length] [RowLog Contents 0]
108                 0x160100000001000000020000                
112                 0x160A00000001000000020000
136                 0x01000000020000003456789A

-- IncludedColumns mit Teilzeichenkette "3ADAFA"
[Log Record Length] [RowLog Contents 0]
116                 0x160100000001000000020000003456789A040000
120                 0x160A000000010000000F0000003ADAFA00040000
136                 0x01000000020000003456789A

Die Ergebnisspalten der Funktion fn_dblog() sind übrigens sehr umfangreich und umfasssen in der hier verwendeten MSSQL Version 2022 insgesamt 124 Spalten. Von Interesse könnten für Sie evtl. auch die Spalten [Lock Information] und der gesamte Log-Datensatz [Log Record] sein, der hier wegen der Länge nicht wiedergegeben wurde.

Handelt es sich hier nur um DB-interne Information?

Man könnte jetzt argumentieren, dass es sich bei den von fn_dblog() gelieferten Spalten ausschließlich um DB-interne Informationen handelt. Zum Nachweis, dass die TLog-Informationen im Fall der im Index inkludierten Spalten tatsächlich umfangreicher sind, als die eines Index ohne Icludes, wird der folgende Massentest durchgeführt:

-- Löschen und Neuanlegen der beiden Datenbanken
use master
go
ALTER DATABASE [IncludedColumns] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE [IncludedColumns]
GO
ALTER DATABASE [NonIncludedColumns] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE [NonIncludedColumns]
GO

-- Hier Anlegen der beiden Datenbanken wie zu Beginn dieses Beitrags

-- Hier Anlegen der beiden Tabellen wie zu Beginn dieses Beitrag

-- Die beiden DBs. wurden mit einer initialen TLog-Größe von 8MB angelegt.
-- Trotzdem: Es wurde eine Tabelle mit einem Index und einem initialen
-- Datensatz angelegt. Prüfen der Größen der beiden TLog-Dateien
-- (Angabe in Anzahl 8KB Seiten) 
use NonIncludedColumns
GO
select [size] from sys.database_files where type_desc = 'LOG'
-- Ergebnis: 1024

use IncludedColumns
GO
select [size] from sys.database_files where type_desc = 'LOG'
-- Ergebnis: 1024


-- Abschliesend erfolgt hier der Massentest. Hierbei muss sichergestellt
-- werden, dass während dieses Tests keine TLog-Backups erfolgen, denn
-- diese würden die Größße der TLog-Datei ggf. beeinflussen.

-- Masssentest für die Datenbank NonIncludedColumns
use NonIncludedColumns
GO

-- begin tran (zu dieser Zeile siehe nächstes Unterkapitel)
DECLARE @i INT = 0
WHILE @i < 1000000
BEGIN
  UPDATE dbo.NonIncludedColumns
  SET Indexspalte += 1
  WHERE ID = 1
  SET @i += 1
END

select [size] from sys.database_files where type_desc = 'LOG'
-- commit (zu dieser Zeile siehe nächstes Unterkapitel)

-- Masssentest für die Datenbank IncludedColumns
use IncludedColumns
GO

-- begin tran (zu dieser Zeile siehe nächstes Unterkapitel)
DECLARE @i INT = 0
WHILE @i < 1000000
BEGIN
  UPDATE dbo.IncludedColumns
  SET Indexspalte += 1
  WHERE ID = 1
  SET @i += 1
END

select [size] from sys.database_files where type_desc = 'LOG'
-- commit (zu dieser Zeile siehe nächstes Unterkapitel)

Ergebnisse der Massentests

Die folgende Tabelle enthält die Ergebnisse der TLog-Dateigrößen in 8 kB Seiten nach Durchführung verschiedener Massentests. Es wurden mehrere Testreihen durchgeführt, nämlich

mit den drei Kompatibilitätsstufen MSSQL 2016, MSSQL 2019 und MSSQL 2022
mit implizitem Transaktions-Handling (= 10.000.000 einzelne Transaktionen)
Massentest innerhalb einer einzelnen Transaktion (begin tran … commit)

Bezogen auf die Laufzeit der jeweiligen Tests hat sich kein signifikanter Unterschied ergeben. Hier ist zu bemerken, dass die Testreihen mit den vielen impliziten Transaktionen unabhängig von den Includes ungefähr 5 - 6 mal so lange dauerten, wie die Testreihen mit den einzelnen großen Transaktionen für den gesamten Testlauf.

Interessant hierbei sind die folgenden Beobachtungen:

Die Verwendung von Included Columns macht sich nur in einer größeren TLog-Datei bemerkbar, wenn die Updates innerhalb nur einer Transaktion erfolgen. Im Fall der Include-Spalten ist die resultierende TLog-Datei ca. 4% größer (Testläufe 4, 5, 6 vs. 10, 11, 12).
Bei Verwendung vieler impliziter Transaktionen ist keine Änderung der TLog-Größen zu verzeichnen (Testläufe 1, 2, 3 vs. 7, 8, 9).
Der Wechsel von der Version 2019 zur Version 2022 hat bezüglich der Protokollierung der Transaktionen zu einer erheblichen Mehrprotokollierung geführt (Testläufe 2, 3, 8, 9 vs. 1, 7).

Kompatibilität202220192016202220192016202220192016202220192016

Wer ist betroffen?

Wie bereits erwähnt, hat sich durch die zusätzliche Protokollierung kein erkennbarer Performancenachteil in einer einzelnen Instanz ergeben. Trotzdem sollten bei kritischen Datenbanken folgende Punkte berücksichtigt werden

Wieviele Transaktionen treten in der betrachteten Datenbank auf?
Werden Transaktionsinformationen für eine / weitere Instanz(en) benötigt (Replikation, AlwaysOn Verfügbarkeitsgruppen)
Welche Kapazität hat das Netzwerk über das die TLog-Informationen übertragen werden, gibt es hier bereits Engpässe?
Bei extrem großen Datenbanken mit sehr vielen Transaktionen existieren auch Auswirkungen auf das TLog-Backup
Hier wurde lediglich eine Include-Spalte verwendet. In welchem Umfang werden Include-Spalten eingesetzt?

Fazit

Im Zusammenhang mit der Verwendung von Include-Spalten existiert in bestimmten Fällen eine Abweichung der TLog-Protokollierung die im Normalfall nicht signifikant ist. Bei kritischen Datenbanken kann diese aber u.U. ins Gewicht fallen.

Wenn Sie mehr über dieses Thema erfahren möchten, stehen Ihnen unsere Expert:innen gerne zur Verfügung. Vereinbaren Sie gerne ein unverbindliches Beratungsgespräch über unser Kontaktformular.

Wir helfen Ihnen gerne weiter!