Robert Panther: BASTA! vom 28.09.-02.10.2015 in Mainz

Ich freue mich, auch in diesem Jahr wieder auf der BASTA! in Mainz mit einem Vortrag vertreten zu sein.

Und zwar gebe ich am Donnerstag von 10:15 bis 11:30 Uhr einen Überblick über die inzwischen doch recht zahlreichen verschiedenen Indextypen, die SQL Server zur Verfügung stellt und beleuchte dabei deren Besonderheiten. Dabei werden auch Kriterien aufgezeigt, anhand derer man entscheiden kann, für welchen Einsatzweck welche Indexform geeignet ist.

Details zum Vortrag: https://basta.net/2015/sessions/sql-server-indizes-gestern-und-heute-verschiedene-varianten-im-vergleich

Die BASTA! läuft wie gewohnt ganze 5 Tage wovon am Pre-Conference-Day (Montag) und Post-Conference-Day (Freitag) ganztägige Workshops angeboten werden, während der Rest der Konferenz mit meist 75-minütigen Vorträge aus verschiedenen Bereichen rund um .NET, Windows und Javascript gefüllt ist. Weitere Infos zur Veranstaltung gibt es auf der offiziellen Veranstaltungs-Website: http://www.basta.net


Robert Panther: Tipps & Tricks: Bedeutung und Wartung von Indexstatistiken

“Traue keiner Statistik, die Du nicht selbst aktualisiert hast!”

Eigentlich jedem, der sich etwas intensiver mit SQL Server Datenbanken beschäftigt, ist die hohe Bedeutung von Indizes für performante Datenbankabfragen bewusst. Was aber oft und gerne vernachlässigt wird, sind die Indexstatistiken.
Diese speichern Informationen über die Werteverteilung der indizierten Spalten und müssen – genau wie die Indizes selbst – regelmäßig gewartet werden. Eine veraltete Indexstatistik kann nämlich leicht dazu führen, dass der dazu gehörende Index einfach nicht verwendet wird.

Ähnlich wie bei der Indexwartung, die entweder per Reorganize oder aber per Rebuild durchgeführt werden kann, stehen auch für Indexstatistiken verschiedene Varianten zur Wartung zur Verfügung. Die gründlichere aber auch zeitaufwändigste Variante ist die Statistikerstellung per FULLSCAN. Hier werden die gesamten Indexdaten eingelesen und daraufhin die Statistik aktualisiert. In den meisten Fällen reicht jedoch auch eine Statistikaktualisierung auf Basis von Stichproben (SAMPLED) aus. Hierbei wird nur ein Teil der Indexdaten gelesen und der Rest quasi hochgerechnet.

(Wenn ein Index mittels Rebuild neu aufgebaut wird, werden auch die dazu gehörenden Indextstatistiken quasi im Vorbeimarsch per Fullscan aktualisiert, da die Indexdaten ohnehin komplett gelesen werden müssen.)

Bei ungleichmäßiger Verteilung der Indexwerte kann eine stichprobenbasierte Indexstatistik jedoch auch dazu führen, dass der Index nicht optimal oder unter Umständen auch gar nicht genutzt wird. Denn wenn die gewählte Stichprobe nicht repräsentativ für die Mehrzahl der Daten ist, wird von einer falschen Verteilung der Daten ausgegangen.
Das ist auch einer der Gründe, warum die Datenbankoption “Statistiken automatisch aktualisieren” nicht zu empfehlen ist. Hier werden nämlich – sobald bestimmte Grenzwerte bzgl. Alter der Statistik und Änderungen in der Tabelle überschritten sind – die Indexstatistik generell stichprobenbasiert aktualisiert. Stattdessen macht es mehr Sinn, auch Indexstatistiken gezielt mit Hilfe von SQL Server Agent Jobs zu aktualisieren, wodurch man diese Wartungsaufgaben auch in Zeiten mit geringer Systemlast (Nachts oder am Wochenende) verlagern kann. Nun kann man bei kleineren Datenbanken leicht eine Routine entwickeln, die anhand der Systemtabelle sys.objects die Statistiken jeder Tabelle mit der FULLSCAN-Option aktualisert. Spätestens bei etwas größeren Datenbanken kann dieses Vorgehen aber leicht das nächtliche Wartungsfenster sprengen.

Daher empfehle ich folgende Strategie für die Wartung von Indexstatistiken:

  1. Statistiken zu Indizes auf Identity-Spalten können täglich (genaue: jede Nacht) strichprobenbasiert erstellt werden, da Identity-Werte automatisch gleichmäßig verteilt sind (sofern nicht übermäßig viele Zeilen in einem Bereich der Tabelle gelöscht werden).
  2. Alle anderen Indexstatistiken sollten generell mit FULLSCAN neu erstellt werden.
  3. Um das Wartungsfenster unter der Woche möglichst klein zu halten, werden am Wochenende alle Indexstatistiken mit FULLSCAN neu erstellt.
  4. Unter der Woche werden nur die Statistiken zu Indizes, deren Daten sich stark geändert haben, täglich mit FULLSCAN neu aufgebaut. Als Ausgangswert für die jeweiligen Schwellwerte kann man sich hier an den Parametern orientieren, die Microsoft auch für die automatische Statistikaktualisierung nutzt:
    • mindestens 50.000 Zeilen in der Tabelle
    • mindestens 20% geänderte Zeilen
    • mindestens 500 geänderte Zeilen (ist aufgrund der anderen beiden Bedingungen ohnehin erfüllt)

Natürlich kann man sowohl die Schwellwerte als auch die Häufigkeit der Aktualisierungen individuell anpassen. Es handelt sich hier lediglich um Startwerte, die für viele Datenbanken einen sinnvollen Kompromiss zwischen Wartungsaufwand und Aktualität der Statistiken darstellen.

Um die Statistiken, deren Tabellen, Indizes und Änderungswerte zu ermitteln kann man folgende Abfrage verwenden:

SELECT
sch.name AS [Schemaname],
obj.name AS [Tablename],
s.name AS [Statisticname],
STATS_DATE(s.[object_id],s.[stats_id]) AS [StatisticUpdateDate],
sp.[rows],
sp.rows_sampled,
sp.modification_counter
FROM sys.stats s
INNER JOIN sys.objects obj ON s.object_id = obj.object_id
INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
LEFT JOIN sys.indexes i ON i.object_id = obj.object_id AND i.index_id = s.stats_id
CROSS APPLY sys.dm_db_stats_properties (obj.object_id, s.stats_id) AS sp
WHERE
obj.type = 'U'
ORDER BY
s.name

Diese Abfrage durchläuft man dann mit einem SQL Cursor und führt pro Zeile die entsprechende UPDATE Statistics-Anweisung aus:

EXEC ('UPDATE STATISTICS [' + @Schemaname + N'].[' + @Tablename + N'] [' + @Statisticname + '] WITH FULLSCAN')

Von diesem Ablauf müssen nun nur noch anhand der oben beschriebenen Kriterien für die Fälle 1 bis 4 unterschiedliche Varianten erstellt werden, die in die jeweiligen SQL Server Agent Jobs kommen. Das einzige was hierbei nicht ganz offensichtlich ist, ist die Erkennung von Statistiken zu Indizes auf Identity-Spalten. Da sich die Namen von Statistiken aber an denen der dazu gehörenden Indizes orientieren, sind diese leicht von den anderen zu unterscheiden, sofern man sich an entsprechende Namenskonventionen gehalten hat (z.B. “PK” als Präfix für Primärschlüssel, “IX” für normale Indizes).


Christoph Muthmann: Varchar-Spalten im Report filtern

Die wenigsten werden drüber nachdenken, aber hier kann sich ein echter Performance-Killer verbergen. Die Berichtsparameter vom Typ Text sind nämlich immer (n)varchar!

Ganze Geschichte »

Christoph Muthmann: Power BI Desktop August 2015 Update

From the Business Intelligence Blogs:

Announcing the Power BI Desktop August Update

Ganze Geschichte »

Andre Essing: 1. Geburtstagsfeier der SQL Usergroup Emsland – Ich bin dabei

Seit dem William Durkin (b|t|l) die SQL Server User Group im Emsland gegründet hat, ist nun schon fast 1 Jahr vergangen und der erste Geburtstag steht vor der Tür. Ein Grund dies ausgiebig zu feiern, natürlich mit einem Sondertreffen – und was für eins. Am 04.09.2015 ab 15:00 Uhr findet im IT Zentrum Lingen ein […]

Dieser Beitrag wurde auf dem Blog von Andre Essing veröffentlicht.

Uwe Ricken: Forwarded Records intern

Im Juli 2015 war ich als Sprecher auf dem SQL Saturday in Manchester mit dem Thema “DML deep dive”  vertreten. Unter anderem wurde im Vortrag gezeigt, wie Forwarded Records entstehen und welchen Einfluss sie auf Abfragen haben. Das Thema ist recht komplex und kompliziert. Daher soll dieser Artikel die Besonderheiten von Forwarded Records detailliert – und durch Beispiele untermauert – hervorheben.

Interner Aufbau eines HEAPS

Forwarded Records können nur in Heaps auftreten. Als Heap werden Tabellen bezeichnet, die keiner logischen Ordnung nach einem Attribut der Tabelle unterworfen sind. Werden in Heaps Datensätze gespeichert, scannt Microsoft SQL Server die PFS (Page Free Space) und sucht nach Datenseiten, die von der Tabelle allokiert sind. Ist auf einer Datenseite ausreichend Platz für die Speicherung des Datensatzes vorhanden, wird der Datensatz auf der entsprechenden Datenseite gespeichert; sind keine freien Datenseiten mehr vorhanden, werden bis zu acht neue Datenseiten für die Tabelle reserviert und der Datensatz wird auf einer neuen, leeren Datenseite gespeichert.

PFS – Datenseite

PFS-Seiten (Page Free Space) zeichnen den Zuordnungsstatus sowie den belegten Speicherplatz der einzelnen Datenseiten auf. Die PFS-Seite verwaltet jede Datenseite einer Datenbank durch die Belegung von 1 Byte pro Datenseite. Somit können pro PFS-Seite 8.088 Datenseiten verwaltet werden! Ist eine Datenseite zugeordnet und es handelt sich um die Zuordnung zu einem HEAP, wird in der PFS hinterlegt, wie die Datenseite bereits prozentual gefüllt ist. Hierzu werden die ersten beiden Bits gesetzt:

Bit-Wert Bedeutung
0x00 Die Datenseite ist leer
0x01 Die Datenseite ist bis zu 50% gefüllt
0x02 Die Datenseite ist zwischen 51% und 85% gefüllt
0x03 Die Datenseite ist zwischen 86% und 95% gefüllt
0x04 Die Datenseite ist zwischen 96% und 100% gefüllt

Die Höhe des freien Speicherplatzes einer Datenseite wird ausschließlich für Heap- und Text/Image-Seiten verwaltet. Indexe erfordern keine Verwaltung in der PFS, da die Stelle, an der eine neue Zeile eingefügt werden soll, von den Indexschlüsselwerten abhängig ist und nicht vom möglichen freien Platz auf einer Datenseite.

IAM – Datenseite

Als IAM Datenseite (Index Allocation Map) wird eine Systemdatenseite in Microsoft SQL Server bezeichnet, in der zugehörige Datenseiten EINER Tabelle oder eines Indexes verwaltet werden. Microsoft SQL Server verwendet die IAM Datenseiten für Bewegungen innerhalb eines Heaps. Die Zeilen innerhalb eines Heaps weisen keine bestimmte Reihenfolge auf und die Datenseiten sind nicht verknüpft. Die einzige logische Verbindung zwischen den Datenseiten sind die Informationen, die auf den IAM-Seiten aufgezeichnet sind!

SELECT P.index_id,
       P.rows,
       SIAU.type_desc,
       SIAU.total_pages,
       SIAU.used_pages,
       SIAU.data_pages,
       SIAU.first_page,
       SIAU.first_iam_page
FROM   sys.partitions AS P
       INNER JOIN sys.system_internals_allocation_units AS SIAU
       ON (P.hobt_id = SIAU.container_id)
WHERE  P.object_id = OBJECT_ID('dbo.demo_table', 'U');

RECORDSET_03

Die Spalte [first_iam_page] aus [sys].[system_internals_allocation_units] verweist auf die erste IAM-Datenseite in der Kette möglicher IAM-Datenseiten, die zur Verwaltung des Speicherplatzes verwendet werden, der dem Heap zugeordnet ist.

FORWARDED RECORDS?

Ein Forwarded Record ist ein Datensatz in einem HEAP, der – bedingt durch eine Aktualisierung – im Volumen so stark anwächst, dass er nicht mehr vollständig auf die ursprüngliche Datenseite passt. Microsoft SQL Server erstellt eine neue Datenseite und speichert den Datensatz auf der neu erstellten Datenseite. Auf der ursprünglichen Datenseite verbleibt ein Eintrag, der auf die neue Adresse/Datenseite verweist. Dieses Verfahren ist einem “Nachsendeantrag der Post” ähnlich. Obwohl Microsoft SQL Server den Datensatz auf einer neuen Datenseite speichert, bleibt die Originaladresse immer noch gültig und ein Update der Position in eventuell vorhandenen Non Clustered Indexes muss nicht ausgeführt werden.

Testumgebung

Für die Demonstration wird eine Tabelle angelegt, in der sich 20 Datensätze befinden. Von diesen 20 Datensätzen wird ein Datensatz durch Aktualisierungen so weit vergrößert, dass der Inhalt des Datensatzes nicht mehr auf eine Datenseite passt; die Daten müssen also auf eine neue Datenseite verschoben werden. Im Ergebnis erzielt man so einen Forwarded Record.

/* Create the demo table for 20 records */
CREATE TABLE dbo.demo_table
(
   Id   INT           NOT NULL IDENTITY (1, 1),
   C1   VARCHAR(4000) NOT NULL
);
GO

/* Now insert 20 records into the table */
INSERT INTO dbo.demo_table (C1) VALUES
(REPLICATE('A', 2000)),
(REPLICATE('B', 2000)),
(REPLICATE('C', 2000)),
(REPLICATE('D', 2000));
GO 5

/* On what pages are the records stored? */
SELECT FPLC.*,
       DT.*
FROM   dbo.demo_table AS DT
       CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS FPLC;
GO

Der Code erstellt eine neue Tabelle [dbo].[demo_table] und füllt sie mit 20 Datensätzen. Da jeder Datensatz eine Satzlänge von 2.015 Bytes besitzt, sind die Datenseiten mit 8.060 Bytes zu 100% gefüllt.

RECORDSET_01

Bei 20 Datensätzen ergibt sich eine Gesamtzahl von 5 Datenseiten für die Speicherung  der Daten und eine zusätzliche Datenseite für die IAM Datenseite.

RECORDSET_02

Ein INDEX SCAN über die vollständige Tabelle generiert ein I/O von 5 Datenseiten!

-- INDEX SCAN über 5 Datenseiten
SET STATISTICS IO ON;
SELECT * FROM dbo.demo_table AS DT;
SET STATISTICS IO OFF;
GO

RESULT_01

Die IAM-Datenseite wird nicht als I/O gewertet, da sie nichts zur Ausgabe beiträgt. Es werden ausschließlich Datenseiten berücksichtigt, die Daten für die Ausgabe bereithalten.

FORWARDED RECORDS generieren

Welcher Prozess erstellt einen Forwarded Record? Ein Forwarded Record kann nur generiert werden, wenn ein Datensatz geändert wird und der auf der Datenseite zur Verfügung stehende Platz nicht mehr ausreicht, den vollständigen Datensatz zu speichern. In diesem Fall muss der Datensatz die ursprüngliche Datenseite verlassen und auf eine neue Datenseite “umziehen”. Der nachfolgende Code erweitert den Wert in C1 von 2.000 Bytes auf 2.500 Bytes für den Datensatz mit der [Id] = 1. Anschließend wird die Transaktion aus dem Transaktionsprotokoll ausgelesen um die einzelnen Transaktionsschritte sichtbar zu machen.

 UPDATE dbo.demo_table
SET   C1 = REPLICATE('Z', 2500)
WHERE Id = 1;
GO

Nachdem der Inhalt von [C1] von 2.000 Bytes auf 2.500 Bytes angewachsen ist, reicht der vorhandene Platz auf der Datenseite 119 nicht mehr aus. Microsoft SQL Server muss den Datensatz auf eine andere Datenseite verschieben, die ausreichend Platz zur Verfügung stellt, um den Datensatz zu speichern. Was genau während dieser Transaktion passiert, wird mit einem Blick in das Transaktionsprotokoll sichtbar gemacht.

-- Check the transaction log for every single step
SELECT FD.[Current LSN],
       FD.Operation,
       FD.Context,
       FD.AllocUnitName,
       FD.[Page ID],
       FD.[Slot ID]
FROM   sys.fn_dblog(NULL, NULL) AS FD
WHERE  FD.Context <> N'LCX_NULL'
ORDER BY
       FD.[Current LSN];
GO

RECORDSET_04

Der Inhalt des Transaktionsprotokolls zeigt, dass zunächst Metadaten in der Datenbank angepasst wurden (Zeile 1 – 7). Diese Informationen sind für die Betrachtung irrelevant, da sie lediglich das Erstellen einer Statistik für das Attribut [Id] der Tabelle [dbo].[demo_table] protokolliert haben. Der eigentliche Prozess beginnt in Zeile 8 des Auszugs. In der SGAM-Datenseite wird eine Aktualisierung durchgeführt, da eine neue Datenseite / Extent hinzugefügt wird.
Sobald die neue Datenseite im System bekannt ist, folgt der weitere Ablauf einem fest vordefinierten Muster:

  • in der PFS wird die neue Seite als “leer” gekennzeichnet (Zeile 9)
  • Anschließend wird die neue Datenseite in die Verwaltung der IAM-Datenseite der Tabelle [dbo].[demo_table] aufgenommen (Zeile 10)
  • Die neue Datenseite (0x9C = 156) wird zunächst formatiert (Zeile 11)
  • um anschließend den ALTEN Datensatz mit der [Id] = 1 auf die neue Datenseite zu schreiben (Zeile 12)
  • und die PFS-Datenseite zu aktualisieren, da die Datenseite nun <= 50% gefüllt ist (Zeile 13).
  • Mit dem Verschieben des ALTEN Datensatzes geht einher, dass er nach der Speicherung aktualisiert werden muss (Zeile 14).
  • Gleiches gilt natürlich für den ursprünglichen Speicherort. Dort wird statt des ursprünglichen Datensatzes lediglich ein Verweis auf den neuen Speicherort geschrieben (Zeile 15)
  • Bedingt durch die Aktualisierung (aus 2.000 Bytes werden nun 8 Bytes) muss auch die PFS-Datenseite erneut aktualisiert werden; schließlich ist Seite 119 nun nicht mehr zu 100% gefüllt (Zeile 16)

FORWARDED RECORS erkennen

Forwarded Records können nur in Heaps auftreten und haben ähnliche Auswirkungen auf das I/O wie fragmentierte Indexe. Ein Forwarded Record bedeutet erhöhtes I/O, da von der Original-Datenseite, auf der der Datensatz gespeichert wurde, nur noch ein Verweis auf den tatsächlichen Speicherort zeigt. Damit wird Microsoft SQL Server gezwungen, das Lesen zunächst auf der verwiesenen Datenseite fortzusetzen. Es gilt also, rechtzeitig festzustellen, ob – und wie viele – Forwarded Records in einem Heap existieren.

SELECT DDIPS.index_id,
       DDIPS.index_type_desc,
       DDIPS.page_count,
       DDIPS.record_count,
       DDIPS.min_record_size_in_bytes,
       DDIPS.max_record_size_in_bytes,
       DDIPS.forwarded_record_count
FROM   sys.dm_db_index_physical_stats
       (
         DB_ID(),
         OBJECT_ID('dbo.demo_table', 'U'),
         0,
         NULL,
         'DETAILED'
       ) AS DDIPS;
GO

PHYSICAL_STATS_01

Die Analyse des Index zeigt, dass nunmehr 6 Datenseiten im Heap vertreten sind. Besonders interessant ist, dass – obwohl kein neuer Datensatz hinzugefügt wurde – ein weiterer Datensatz hinzugekommen ist. Das liegt daran, dass der Verweis auf den neuen Speicherort innerhalb von Microsoft SQL Server wie ein normaler Datensatz behandelt wird. Das hier ein “besonderer” Datensatz gespeichert wird, erkennt man an der [min_record_size_in_bytes], die bei 9 Bytes liegt. Alle eingetragenen Datensätze haben eine Länge von 2.015 Bytes! Die Spalte [forwarded_record_count] weist darauf hin, dass es einen Datensatz gibt, der so groß ist, dass er mit seinem Volumen nicht mehr auf die ursprüngliche Datenseite passt.

FORWARDED RECORDS lesen

Ein Forwarded Record kann einen erheblichen Einfluss auf das IO für eine Abfrage haben wie das nachfolgende Beispiel zeigt. Es wird exakt die gleiche Abfrage ausgeführt wie bereits weiter oben beschrieben. Zu erwarten wäre ein IO von 6 Datenseiten wie die Statistik des Heaps in der obigen Abbildung vermuten lässt; das Ergebnis ist überraschend:

-- INDEX SCAN über 6 Datenseiten?
SET STATISTICS IO ON;
SELECT * FROM dbo.demo_table AS DT;
SET STATISTICS IO OFF;
GO

RESULT_02

Insgesamt muss für die Abfrage auf 7 Datenseiten zugegriffen werden; und diese Zahl ist – basierend auf der internen Struktur – vollkommen in Ordnung wie die nachfolgende Abbildung demonstriert!

PHYSICAL_READS

Wenn ein Heap gelesen wird, gibt es keinen Index, an dem sich die “Leserichtung” oder “Sortierung” orientieren kann. Zunächst wird durch einen Zugriff auf die IAM-Datenseite festgestellt, welche Datenseiten durch den Heap allokiert wurden. Durch den Forwarded Record ist – intern – eine weitere Datenseite hinzugekommen. Microsoft SQL Server “weiß” durch das Lesen der IAM-Datenseite, dass die Datenseiten 119, 121, 126, 127, 142 und 156 gelesen werden müssen. Das sind die Datenseiten, die durch den Heap belegt werden.
Mit dem ersten IO wird die Datenseite 119 gelesen. Während die Datensätze von 119 gelesen werden, trifft Microsoft SQL Server auf einen Forwarded Record und liest diesen Datensatz von der “neuen” Adresse auf Seite 156 (2. IO). Nachdem der Forwarded Record gelesen wurde, wird mit Seite 121 fortgefahren: 121 –> 126 –> 127 –> 142 -> 156! Microsoft SQL Server muss Datenseite 156 zwei Mal lesen! Beim ersten Lesevorgang ist ausschließlich der Forwarded Record betroffen. Er ist initial für den Zugriff auf Datenseiten 156. Die Reihenfolge der Lesezugriffe sieht wie folgt aus:
119 –> 156 –> 121 –> 126 –> 127 –> 142 –> 156 = 7 IO.
Die Lesevorgänge können durch den Aufruf von Sperren, die durch Microsoft SQL Server beim Lesen gesetzt werden, transparent gemacht werden. Der nachfolgende Code zeigt für jeden Zugriff die gesetzten und freigegebenen Sperren in der Tabelle.

-- make locks and releases visible!
DBCC TRACEON (3604, 1200, -1);
SELECT * FROM dbo.demo_table AS DT;
DBCC TRACEOFF(3604, 1200, -1);
GO

Die aktivierten Traceflags bewirken, dass verwendete Sperren und Freigaben in SSMS protokolliert werden. Das Ergebnis stellt sich wie folgt dar:

Process 60 acquiring IS lock on OBJECT: 6:245575913:0 (class bit0 ref1) result: OK
Process 60 acquiring IS lock on PAGE: 6:1:119 (class bit0 ref1) result: OK
Process 60 acquiring S lock on RID: 6:1:119:0 (class bit0 ref1) result: OK
Process 60 acquiring IS lock on PAGE: 6:1:156 (class bit0 ref1) result: OK
Process 60 acquiring S lock on RID: 6:1:156:0 (class bit0 ref1) result: OK
Process 60 releasing lock on RID: 6:1:156:0
Process 60 releasing lock on PAGE: 6:1:156
Process 60 releasing lock on RID: 6:1:119:0
Process 60 releasing lock on PAGE: 6:1:119
Process 60 acquiring IS lock on PAGE: 6:1:121 (class bit0 ref1) result: OK
Process 60 releasing lock on PAGE: 6:1:121
Process 60 acquiring IS lock on PAGE: 6:1:126 (class bit0 ref1) result: OK
Process 60 releasing lock on PAGE: 6:1:126
Process 60 acquiring IS lock on PAGE: 6:1:127 (class bit0 ref1) result: OK
Process 60 releasing lock on PAGE: 6:1:127
Process 60 acquiring IS lock on PAGE: 6:1:142 (class bit0 ref1) result: OK
Process 60 releasing lock on PAGE: 6:1:142
Process 60 acquiring IS lock on PAGE: 6:1:156 (class bit0 ref1) result: OK
Process 60 releasing lock on PAGE: 6:1:156
Process 60 releasing lock on OBJECT: 6:245575913:0

Nachdem eine IS-Sperre auf die Tabelle gesetzt wurde, wird die Datenseite 119 gelesen. Hierbei wird gleich beim ersten Datensatz eine Zeilensperre angewendet, um anschließend auf die neue Datenseite zu gelangen, auf der sich der Datensatz als „Forwarded Record“ befindet. Sobald der Datensatz gelesen wurde, wird die Sperre auf Datenseite 156 und anschließend auf Datenseite 119 wieder aufgehoben und der Prozess liest alle anderen Datenseiten. Die Datenseite 156 muss zwei Mal gelesen werden, da die zu lesenden Datenseiten über die IAM festgelegt waren.
Herzlichen Dank fürs Lesen!

Uwe Ricken: Auswirkung von vollständig qualifizierten Objekten auf den Plancache von SQL Server

Bei der täglichen Arbeit mit Microsoft SQL Server in mittelständischen und großen Unternehmen kommt es immer wieder mal vor, dass Programmcodes in die Testsysteme und Produktionssysteme implementiert werden mussten. Beim durchgeführten Code Review stößt man immer wieder auf die Verwendung einer einfache Notation für die Aufrufe von Prozeduren oder SQL-Abfragen. Insbesondere seit der Trennung von Schemata und Benutzern ist diese “Unart” nicht nur schwieriger zu bearbeiten (aus welchem Schema wird das Objekt aufgerufen?) sondern kann auch gravierende Auswirkungen auf den Plan Cache von Microsoft SQL Server haben.

Warum vollständig qualifizierte Objektverweise?

Aus Sicht des „Suchalgorithmus“ von Microsoft SQL Server nach ausführbaren Objekten ist eine vollständig qualifizierte Notation auf ein Objekt sinnvoll, da Microsoft SQL Server dadurch unmittelbar auf das richtige Schema verwiesen wird, in dem sich das referenzierte Objekt befindet. Hintergrund dafür ist, dass bei „unqualifizierten“ Objekten zunächst im Standardschema des Benutzers nach einem entsprechenden Objekt gesucht wird. Befindet sich ein Objekt im Schema [dbo], muss SQL Server zunächst im Standardschema des Benutzers suchen und – wenn das Objekt dort nicht gefunden wird – im [dbo]-Schema nach dem Objekt suchen. Diese Suchen nach referenzierten Objekten sind immer mit Zeiteinbußen verbunden.
Neben dem oben beschriebenen Effekt kommt aber noch ein anderer – nicht zu unterschätzender Effekt zum Tragen; die Speicherung und Wiederverwendung von Ausführungsplänen (Prozedurcache) kann bei vielen Benutzern mit eigenen Schemata über Gebühr beansprucht werden. Die Aufgabe des Prozedurcaches von Microsoft SQL Server ist die Speicherung von Abfrageplänen für eine weitere Verwendung, sofern die gleiche Abfrage erneut aufgerufen wird. Findet sich bereits ein Ausführungsplan im Cache, muss Microsoft SQL Server keinen neuen Plan erstellen sondern kann die Abfrage unmittelbar ausführen. Weitere Informationen zum Prozedurcache finden sich z. B. hier:
http://blogs.msdn.com/b/sqlserverfaq/archive/2011/12/12/procedure-cache.aspx
Das nachfolgende Beispiel zeigt den Zusammenhang zwischen der Benutzung von „voll qualifizierten“ Objekten und der Speicherung von Abfrageplänen.

Testumgebung

Zunächst wird eine Testdatenbank alle benötigten Objekte für die Demonstration (Tabellen / Schema / User) angelegt.

-- Erstellen einer Testdatenbank
CREATE DATABASE [demo_db];
GO

-- Anlegen von 3 Benutzern in der Datenbank demo_db!
USE demo_db;
GO

CREATE USER demo_1 WITHOUT LOGIN WITH DEFAULT_SCHEMA = dbo;
CREATE USER demo_2 WITHOUT LOGIN WITH DEFAULT_SCHEMA = dbo;
CREATE USER demo_3 WITHOUT LOGIN WITH DEFAULT_SCHEMA = demo_3;
GO

-- Anlegen eines expliziten Schemas für den Benutzer demo_3!
CREATE SCHEMA [demo_3] AUTHORIZATION demo_3;
GO

-- Anlegen einer Tabelle für den gemeinsamen Zugriff!
CREATE TABLE dbo.foo
(
    id         int          NOT NULL    IDENTITY (1, 1),
    FirstName  nvarchar(20) NOT NULL,
    LastName   nvarchar(20) NOT NULL,

    CONSTRAINT pk_foo PRIMARY KEY CLUSTERED (Id)
)
GO

-- Berechtigungen für ALLE Benutzer einrichten
GRANT SELECT ON dbo.foo TO public;
GO

-- Eintragen von Testdaten
INSERT INTO dbo.foo (FirstName, LastName)
VALUES
('Uwe','Ricken'),
('Max','Muster'),
('Michael','Schumacher'),
('Kimi','Räikkönen');

Abfragen ohne qualifizierte Objektnamen

Sind alle Vorbereitungen abgeschlossen, kann der Prozedurcache für die Datenbank „geleert werden (BITTE NICHT IN PRODUKTIONSSYSTEMEN ANWENDEN!)

DECLARE @db_id int=db_id();
DBCC FLUSHPROCINDB(@db_id);

Um Informationen zum Plancache / Prozedurcache abzufragen, werden seit SQL Server 2005 „Dynamic Management Objects (dmo)“ verwendet. Um zu überprüfen, welche Informationen der aktuellen Datenbank sich derzeit im Plancache befinden, wird die nachfolgende Abfrage verwendet.

SELECT  cp.plan_handle,
        cp.usecounts,
        cp.size_in_bytes,
        cp.cacheobjtype,
        st.text
FROM    sys.dm_exec_cached_plans AS cp
        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE   st.dbid=DB_ID() AND
        st.text NOT LIKE '%exec_cached_plans%';

Bei der Ausführung von „Ad hoc“-Abfragen (z. B. konkatenierte Abfragestrings aus einer .NET-Anwendung) wird der Text von SQL Server analysiert und mit den Einträgen im Plancache verglichen. Wird der Abfragetext nicht gefunden, muss ein neuer Abfrageplan erstellt werden, der dann im Plancache abgelegt wird. Selbst „kleinste“ Abweichungen im Text werden als “„neu”“ interpretiert!

-- Abfrage 1
SELECT * FROM foo WHERE id = 3;
GO

-- Abfrage 2 (unterschiedlich)
SELECT * FROM foo
WHERE id = 3;
GO

-- Abfrage 3 (Leerzeichen)
SELECT  * FROM foo WHERE id = 3;
GO

Alle drei Abfragen sind – scheinbar – identisch. Dennoch muss Microsoft SQL Server für jede Abfrage eine Speicherungen im Plancache vornehmen, da sich Abfrage 1 von Abfrage 2  z. B. durch den Zeilenumbruch unterscheidet während Abfrage 3 Leerzeichen zwischen dem „*“ besitzt. Selbst Kommentare erzwingen einen neuen Abfrageplan, wenn die Kommentare Bestandteile des auszuführenden Textes sind! Die Ausführung der obigen drei Abfragen wird wie folgt im Plancache gespeichert:

RECORDSET_01

Ein ähnliches Verhalten kann bei der Ausführung von identischen Abfragen unter verschiedenen Benutzerkontexten beobachtet werden.

EXECUTE AS User = 'demo_1'
SELECT * FROM foo WHERE id = 3;
REVERT
GO

EXECUTE AS User = 'demo_2'
SELECT * FROM foo WHERE id = 3;
REVERT
GO

EXECUTE AS User = 'demo_3'
SELECT * FROM foo WHERE id = 3;
REVERT
GO

Der Code wechselt vor jeder Abfrage den Kontext des Benutzers und führt anschließen die Abfrage aus. Interessant ist dabei, dass alle drei Benutzer exakt das gleiche SQL‑Statement ausführen. Ein Blick in den Plancache zeigt ein “seltsames” Verhalten.
ResultSet_02
Ein Blick auf das Ergebnis überrascht, da eine IDENTISCHE Abfrage mehrmals im Plancache steht. Während die Benutzer „demo_1“ und „demo_2“ als Standardschema [dbo] verwenden, benutzt Benutzer „demo_3 sein eigenes Schema als Standard. Um mehr Informationen über die Attribute zum Plancache zu erhalten, verwendet man die Systemview sys.dm_exec_plan_attributes.

ResultSet_03 ResultSet_04

Links werden die Attribute des ersten Plans aufgezeigt; auf der rechten Seite finden sich die Informationen zum zweiten Plan. Alle Attribute (bis auf [user_id]) sind identisch. Der Eintrag [user_id] ist im Zusammenhang eher unglücklich gewählt worden. Der Eintrag repräsentiert NICHT wie bei MSDN angegeben wird, die principal_id eines Datenbankbenutzers sondern die [schema_id] aus sys.schemas. Bemerkenswert bei diesem Ergebnis ist, dass – sofern man nicht voll qualifizierte Objektnamen verwendet – immer das Standardschema des Benutzers Bestandteil des Plans ist. Für den Benutzer „demo_3“ wurde als Standardschema nicht [dbo] angegeben.

Abfragen mit qualifizierten Objektnamen

Nachdem die Ergebnisse des ersten Tests bekannt sind, wird der Prozedurcache für die Datenbank wieder geleert und die Abfrage, die von allen Benutzern ausgeführt werden soll, geringfügig geändert; es wird nicht nur der Name der Relation angegeben sondern durch die Angabe des Schemas wird das Objekt „qualifiziert“.

—Löschen des Prozedurcaches
DECLARE@db_id int=db_id();
DBCC FLUSHPROCINDB(@db_id);

EXECUTE AS User = 'demo_1';
SELECT * FROM dbo.foo WHERE id = 3 ;
REVERT;
GO

EXECUTE AS User = 'demo_2';
SELECT * FROM dbo.foo WHERE id = 3 ;
REVERT;
GO

EXECUTE AS User = 'demo_3';
SELECT * FROM dbo.foo WHERE id = 3;
REVERT;
GO

Die Analyse des Plancaches zeigt, dass für alle drei Abfragen der gleiche Abfrageplan verwendet worden ist.
ResultSet_05
Grund für dieses Verhalten ist, wie schon im vorherigen Beispiel gezeigt, dass alle Planattribute identisch sind. Durch die explizite Angabe des Schemas, in dem sich das Objekt befindet, kann der Abfrageplan für alle drei Benutzer verwendet werden!

Zusammenfassung

Dass die Verwendung von qualifizierten Objekten nicht nur freundlicher zu lesen ist sondern auch umständliche Suchen des SQL Servers nach dem geeigneten Objekt vermieden werden, sind nur einige Vorteile. Besonders hervorzuheben bleibt jedoch der immense Vorteil bei die Wiederverwendung von Abfrageplänen, da sie nicht mehrfach im Plancache hinterlegt werden müssen. Die Abfragen können optimiert ausgeführt werden und der Speicher von SQL Server dankt es auch noch.

Verweise

DBCC DROPCLEANBUFFERS http://msdn.microsoft.com/de-de/library/ms187762.aspx
DBCC FREEPROCCACHE http://msdn.microsoft.com/de-de/library/ms174283.aspx
DBCC FREESESSIONCACHE http://msdn.microsoft.com/de-de/library/ms187781.aspx
DBCC FREESYSTEMCACHE http://msdn.microsoft.com/de-de/library/ms178529.aspx

Herzlichen Dank fürs Lesen!

Robert Panther: “SQL Server Performance-Ratgeber” zum Sonderpreis – Nur noch wenige Exemplare verfügbar!

Mein Buch “SQL Server Performance-Ratgeber: Datenbank-Optimierung für Architekten, Entwickler & Administratoren” ist fast vergriffen (über eine aktualisierte Neuauflage wird bereits nachgedacht). Der Verlag hat mir freundlicherweise die letzten Exemplare zur Verfügung gestellt, die ich nun zum deutlich reduzierten Preis anbieten kann. (Alles natürlich mit offizieller Rechnung inkl. ausgewiesener Mehrwertsteuer.)

Wer sich also eines der letzten Exemplare (quasi direkt vom Autor) sichern will, sollte sich also möglichst schnell (am besten per Mail an buch@panthersql.de) bei mir melden, denn es sind jetzt bereits nur noch ca. 70 Exemplare übrig.

sqlserverperformanceratgeber

Robert Panther
SQL Server Performance-Ratgeber: Datenbank-Optimierung für Architekten, Entwickler & Administratoren
entwickler.press
292 Seiten (Softcover)
ISBN: 978-3-86802-030-4
alter Preis: 39,90 € inkl. MwSt.
Sonderpreis: 9,90 € inkl. MwSt. (zzgl. 3,- € Versandkosten)


Andre Essing: PASS Regionalgruppe Bayern – SQL Server Usergroup Treffen am 13.08.2015 um 18:30 bei Microsoft in Unterschleißheim

Liebe PASS'ler und SQL Server Enthusiasten in Bayern,

für unser nächstes Usergroup Treffen im August hat sich Klaus Sobel angeboten, bei uns in der Gruppe einen Vortrag zum Thema Power BI zu halten. Wir freuen uns schon darauf, ihn am Donnerstag, den 13.08.2015 bei der Microsoft Deutschland in Unterschleißheim begrüßen zu dürfen. Wir starten wie immer um 18:30 Uhr.

Dieser Beitrag wurde auf dem Blog von Andre Essing veröffentlicht.

Philipp Lenz: SSRS: Inhaltsverzeichnis / Document Map

(english Version below)

Bisher hatte ich immer nur das Inhaltsverzeichnis in Reporting Services durch ein Tablix erstellt, bzw. lediglich verwendet. Durch die Eigenschaft “DocumentLabel” kann man jedoch auch Textfelder in das Inhaltsverzeichnis mit aufnehmen. Wenn man dann auch noch einem Tablix eine DocumentMap hinzufügt, sollte man auch dem Tablix eine DocumentLabel Eigenschaft geben, diese gruppiert dann die Inhalte und ordnet das entsprechend:

26-07-_2015_16-10-102

 

 

 

 

English Version:
So far I had only the table of contents in Reporting Services created by a Tablix , or merely used . Through the property ” Document Label ” you can but also record text fields in the table of contents . If you then still a Tablix adds a DocumentMap , you should also give the Tablix a Document Label property , then grouped the contents and assigns the corresponding

Philipp Lenz: SSRS: Summen aus anderen Gruppen erhalten

(see english Version below)

In diesem Szenario will ich aufzeigen, wie man in SQL Server Reporting Services die Summen aus andere Gruppen (über- wie auch untergeordnet) wiederverwenden kann, bspw. um Anteile berechnen zu können.
tablix2

Im diesem Screen sieht man ein Tablix welches Gruppiert nach Ort ist und die Kopfzeile bildet. Darunter ist nach einem Status gruppiert und die Summen zu der jeweiligen Gruppe werden ausgegeben sowie auch für die Kopfzeile (fett gedruckt).

Nun ist das Ziel, dass man anteilig ausrechnen kann, welchen Anteil der jeweilige Status an dem Ergebnis pro Ort hat.

 

 

 

 

Dafür kann man die SUM oder auch COUNT Funktion erweitern indem man den Gruppennamen aus dem Tablix mitgibt:

=SUM(Fields!Betrag.Value)/SUM(Fields!Betrag.Value, "Wohnort")

tablix1

Weitere Informationen gibt es hier: https://technet.microsoft.com/en-us/library/bb630415(v=sql.100).aspx

 

English Version

In this scenario, I will show how you can in SQL Server Reporting Services to reuse the totals from other groups (over like subordinate), eg. To calculate the shares.
In the following screen you can see a Tablix is that groups by location and forms the header. Among them is for a status groups and the totals for each group will be issued as well as for the header (in bold).

tablix2
Now, the goal is that you can calculate proportionally the share has the respective status of the earnings per site.
For this you can expand the SUM or COUNT function by mitgibt the group name from the Tablix:
= SUM (Fields! Betrag.Value) / SUM (Fields! Betrag.Value, Location”)

Further information is available here: https://technet.microsoft.com/en-us/library/bb630415(v=sql.100).aspx

Andreas Wolter: Security-Fixes for SQL Server and why Security Best Practices Matter / Sicherheitsfixe für SQL Server und warum Sicherheits Best Practices wichtig sind (MS15-058 SQL Server Security Bulletin)

Ziemlich genau ein Jahr nachdem die für 5 Jahre ersten Sicherheits-Bugs in SQL Server gefixt werden mussten, gibt es nun, seit 14.7.2015, wieder einen Grund, seine Security-Patching-Policy zu testen.

- Wenn man denn eine solche für SQL Server hat.

Und damit möchte ich jetzt niemanden anprangern. Denn: die letzten Jahre waren wir aufgrund des Mangels an Sicherheit bedingten Fixen in SQL Server schlicht verwöhnt. Sicherheitslecks (zumindest solche, die bekannt geworden sind), sind seit SQL Server 2000 so gut wie nicht mehr aufgetreten.

- Dazu gibt es diverse Statistiken und Aufsätze, wie diesem von dem amerikanischen Sicherheitsexperten David Litchfield (www.davidlitchfield.com/security.htm) oder dem ITIC-Report von 2010: SQL Server Most Secure Database; Oracle Least Secure Database Since 2002 oder auch die Statistik vom NIST (National Institute of Standards and Technology) von 2013, auf der die folgende Grafik basiert:

Durch 5 Jahre in Folge als sicherstes Datenbanksystem auf Basis der NIST-Daten, mag der SQL Server in Sachen Sicherheitspatching durchaus aus dem Fokus der Administratoren gewandert sein.

Der Security Bulletin vom Juli 2015:

Vulnerabilities in SQL Server Could Allow Remote Code Execution (3065718)

https://technet.microsoft.com/en-us/library/security/MS15-058

Beschrieben werden 3 Sicherheitslecks:

Welche SQL Server Versionen sind betroffen?

Die Liste, beginnend mit SQL Server 2008 Service Pack 3 und endend mit SQL Server 2014 findet sich im Bulletin.
Genauer kann man anhand seiner jeweiligen SQL Engine Nummer in diesem Blog-Artikel von Microsoft suchen:

http://blogs.msdn.com/b/sqlreleaseservices/archive/2015/07/14/ms15-058-sql-server-security-bulletin-released.aspx

Unter welchen Umständen kann ein System durch diese Lücken angegriffen werden?

Die Voraussetzungen sind für die 3 Sicherheitslecks natürlich unterschiedlich und auch (absichtlich) nicht bis ins letzte Detail beschrieben.
WAS man jedoch herauslesen kann, ist, dass diejenigen, die Rechte nur sehr feingradig und dediziert gesetzt haben, wesentlich weniger angreifbar sind, da teilweise Schemaänderungsrechte im Datenbank-Bereich Voraussetzungen sind.
Daher erinnere ich an dieser Stelle gern daran, dass man weise beraten ist, wirklich nur benötigte Rechte zu vergeben, und Schemas als Sicherheitsbereiche zu verstehen. Die Verwendung von db_owner beispielsweise sollte Tabu für Applikations-Benutzer sein.
Wer solche Regeln bisher beherzigt hat, kann sich auch weiterhin einigermaßen sicher fühlen, da die Möglichkeit, diese Sicherheitslecks auszunutzen somit stark verringert ist.

Deswegen sollte man Best Practices für Sicherheit immer beherzigen.
Eine vulnerability ist eben noch kein exploit.

Hier noch ein paar Links in Sachen Security in SQL Server:

Securing SQL Server

SQL Server Security Blog

SQL Server Best Practices – Implementation of Database Object Schemas

SQL Server 2012 Security Best Practices – Operational and Administrative Tasks 

SQL Server 2012 Label Security Toolkit and white paper

SQL Server Database Ownership: recommendations

Guest account in User Databases

 

Happy patching

Andreas

Christoph Muthmann: MS15-058 SQL Server Security Bulletin Released

A security bulletin for SQL Server has been released on 7/14/2015. This applies to SQL Server 2008, 2008 R2, 2012, and 2014 releases.

Ganze Geschichte »

Sascha Dittmann: SQL Saturday #409 Rheinland – Slides und Demos

SQL Saturday #409 RheinlandAm Samstag, dem 13. Juni 2015, fand der dritte deutsche SQL Saturday statt. Wie auch im letzten Jahr konnte dafür als Austragungsort die Hochschule Bonn-Rhein-Sieg genutzt werden.
Gemeinsam mit Alexander Karl durfte ich dort eine Session zum Thema "SQL Server vs. Azure DocumentDB – Ein Battle zwischen XML und JSON" halten.
Die verwendeten Slides und Code Beispiele findet ihr in diesem Blog Post.

IoT Camp/Hackathon: Das Internet der Dinge live erleben

Doch bevor es mit dem eigentlichen SQL Saturday los ging, fanden am Tag davor zwei Pre-Conference Workshops statt.
Gemeinsam mit Olivia Klose und Kostja Klein habe ich das IoT Camp mitgestalten dürfen.

In diesem Workshop wurde erläutert, welche Typen von IoT Lösungen es gibt und wie man sie realisiert. An praktischen Beispielen wurden verschiedene Lösungen demonstriert und welche Anforderungen an die einzelnen Typen gestellt werden. Schwerpunkt lag dabei auf der praktischen Umsetzung auf Basis von Microsoft Azure und richtete sich sowohl an den Hobbyisten als auch an den Profi, der kommerzielle und hoch verfügbare Lösungen entwickeln muss.

Mehr dazu gibt es auf Olivia's Blog.

 

SQL Server vs. Azure DocumentDB – Ein Battle zwischen XML und JSON

Am Samstag waren dann Alexander Karl und ich an der Reihe…
Hier haben wir einen Vergleich zwischen SQL Server XML und der Microsoft Azure DocumentDB gezogen.

Abstract

Seit dem SQL Server 2000 hielt Stück für Stück die XML-Unterstützung Einzug in die Microsoft RDBMS Welt.
Mit der Azure DocumentDB kam die zweite, hauseigene NoSQL-Datenbank in der Microsoft Cloud hinzu, welche die Daten im JSON-Format verarbeitet.
In dieser Session werden wir anhand eines Praxisbeispiels step-by-step, d.h. von den vorbereitenden Schritten, über das Schreiben bis hin zum Lesen der Daten, diese beiden Technologien gegenüberstellen. Dabei arbeiten wir die Vor- und Nachteile der einzelnen Ansätze heraus und zeigen Best Practices auf.

Slides



Der Beitrag SQL Saturday #409 Rheinland – Slides und Demos erschien zuerst auf Sascha Dittmann.

Thomas Glörfeld: Weiteres eBook kostenlos "Introducing Microsoft SQL Server 2014"

Wie schon erwähnt stellt Microsoft wieder sehr viele kostenlose E-Books online. Darunter etliche rund um SQL Server-Themen. Interessant finde ich das Buch "Introducing Microsoft SQL Server 2014" von Ross Mistry und Stacia Misner (ISBN 9780735684751). Im Aufgab ähnelt es der Ausgabe zum SQL Server 2012 (und vermutlich auch der zum SQL Server 2008 R2), die […]

Thomas Glörfeld: eBook kostenlos "Introducing Microsoft SQL Server 2012"

Microsoft stellt wieder sehr viele kostenlose E-Books online. Besonders interessant finde ich das Buch "Introducing Microsoft SQL Server 2012" von Ross Mistry und Stacia Misner (ISBN 9780735665156): ePub Mobi PDF

Christoph Muthmann: Anzeigen der Datenbanken im Objektexplorer dauert sehr lange

Heute greife ich mal einen Thread aus dem TechNet Forum auf. Dort wurde berichtet, dass das Anzeigen der Datenbanken im Objekt-Explorer sehr lange (ca. 20 Sekunden für 35 Datenbanken) dauert.

Ganze Geschichte »

Andre Essing: PASS Regionalgruppe Bayern – SQL Server Usergroup Treffen am 16.07.2015 um 18:30 bei Microsoft in Unterschleißheim

Liebe PASS'ler und SQL Server Enthusiasten in Bayern,

für unser nächstes Usergroup Treffen im Juli konnten wir Tillmann Eitelberg, Mitglied des PASS Vorstandes und MVP gewinnen, bei uns in der Regionalgruppe einen Vortrag zu halten. Wir freuen uns schon darauf, ihn am Donnerstag, den 16.07.2015 bei der Microsoft Deutschland in Unterschleißheim begrüßen zu dürfen. Wir starten wie immer um 18:30 Uhr.

Dieser Beitrag wurde auf dem Blog von Andre Essing veröffentlicht.

Andre Essing: Microsoft veröffentlicht Cumulutive Updates für SQL Server 2014

Diesmal bin ich etwas spät dran, denn Microsoft hat vor 5 Tagen Updates für den SQL Server 2014 veröffentlicht. Am 22.06.2015 wurden durch Microsoft das Cumulative Update 1 für Service Pack 1 und das Cumulative Update 8 für SQL Server 2014 RTM veröffentlicht. Das SQL Server 2014 Service Pack 1 Cumulative Update 1 schließt die Lücken zu […]

Dieser Beitrag wurde auf dem Blog von Andre Essing veröffentlicht.

Andre Essing: PASS Bayern – SQL Server Usergroup Treffen am 22.06.2015 um 18:30

Liebe PASS’ler in Bayern,

ausnahmsweise melden wir uns ein zweites Mal in diesem Monat bei euch, mit einer Einladung zu einem weiteren Treffen der PASS Regionalgruppe, hier in Bayern. Dieses Sondermeeting haben wir aufgesetzt, da wir kurzfristig einen ganz besonderen Sprecher für einen Vortrag in unserer Regionalgruppe gewinnen konnten. Ihr habt Mal die seltene und einmalige Gelegenheit Niko Neugebauer (b|t) zu hören, dabei hat er seinen Deutschland Aufenthalt extra für uns verlängert.

Aus diesem Grund ist unser nächstes Treffen schon am Montag, den 22.06.2015 um 18:30 Uhr. Dies ist zwar sehr kurzfristig, jedoch bietet sich die Möglichkeit, Niko bei seinem ersten Vortrag in einer deutschen Usergroup zu hören und dabei etwas über Columnstore Indexes zu lernen. Wie Niko bereits auf seinem Blog in bereits 56 Artikeln bewiesen hat, ist er der Meister der Columnstore Indexes. Nutzt also die Chance und lernt in einer 2 stündigen Session die Clustered Columnstore Indexes in SQL Server 2014 kennen.

Dieser Beitrag wurde auf dem Blog von Andre Essing veröffentlicht.

Christoph Muthmann: Public Release for Microsoft SQL Server 2012 and 2014 Integration Services Feature Pack for Azure

Microsoft SQL Server 2012/2014 Integration Services (SSIS) Feature Pack for Azure provides SSIS the capability of connecting to Azure Blob Storage and Azure HDInsight. It enables you to create SSIS packages that transfer data between an Azure Blob Storage and on-premises data sources, and process data using Azure HDInsight.

Ganze Geschichte »

Uwe Ricken: Temporäre Tabellen vs. Tabellenvariablen

Welche Strategie ist am besten geeignet, um Daten, die temporär für weitere Aufgaben benötigt werden, zu speichern und zu verwalten? Mit Microsoft SQL Server 2000 wurden zum ersten Mal Tabellenvariablen als Erweiterung eingeführt. Die Arbeit mit temporären Tabellen war zu diesem Zeitpunkt bereits Alltag und jeder Datenbankprogrammierer hat diese bewährte Technik verwendet. Mit der Einführung von Tabellenvariablen wird alles besser – so dachte man zumindest. Dieser Artikel zeigt die Unterschiede zwischen beiden technischen Möglichkeiten.

Temporäre Tabellen

Temporäre Tabellen sind, wie der Name es bereits sagt, “temporär”. Temporäre Tabellen unterscheiden sich technisch nicht durch normale Tabellen, die sich in den Datenbanken befinden. Temporäre Tabellen können jederzeit erstellt und gelöscht werden. Daten können in temporären Tabellen beliebig hinzugefügt, geändert oder gelöscht werden. Der Unterschied zu “normalen” Relationen besteht in zwei wesentlichen Punkten, die nachfolgend etwas näher beleuchtet werden sollen.

Temporäre Tabellen befinden sich in der TEMPDB

Die Datenbank TEMPDB ist eine Systemdatenbank von SQL Server, die für JEDEN Benutzer einer Datenbank verfügbar ist. In dieser globalen Systemdatenbank werden alle temporären Benutzerobjekte angelegt. Diese Datenbank wird bei jedem Start von Microsoft SQL Server neu angelegt und beginnt somit mit einer “sauberen Arbeitsfläche”. Temporäre Relationen unterscheiden sich durch folgende Merkmale von normalen Relationen:

  • Temporäre Tabellen beginnen IMMER mit “#” (lokal) oder “##” (global)!
  • Temporäre Tabellen werden automatisch gelöscht, sobald sie nicht mehr verwendet werden!
  • Globale temporäre Relationen sind für ALLE Datenbanksitzungen verfügbar!

Lokale temporäre Tabellen

Lokale temporäre Tabellen sind nur innerhalb der Datenbanksitzung gültig, in der sie erstellt wurde. Lokale temporäre Tabellen beginnen immer mit einem Hashtag (“#”). Wird die Datenbankverbindung getrennt, werden lokale temporäre Objekte, die während dieser Datenbanksitzung erstellt wurden, automatisch gelöscht.

-- Gültigkeitsbereich von temporären Tabellen
USE tempdb;
GO

-- Erstellen von zwei Testbenutzern in TEMPDB
CREATE USER User1 WITHOUT LOGIN;
CREATE USER User2 WITHOUT LOGIN;
GO

-- Im Kontext von Benutzer User 1 ausführen
EXECUTE AS USER = 'User1'
     CREATE TABLE #User1_Table
     (
         Id int NOT NULL IDENTITY,
         spid int NOT NULL DEFAULT (@@spid),
         myText varchar(100) NOT NULL DEFAULT (suser_sname())
     )
     GO

     INSERT INTO #User1_Table DEFAULT VALUES
     GO 10

     SELECT * FROM #User1_Table
REVERT
GO

-- Ausführen als User2
EXECUTE AS USER = 'User2'
    SELECT * FROM #User1_Table;
REVERT
GO

Wird der obige Code in EINEM Ausführungsfenster von Microsoft SQL Server Management Studio ausgeführt, kann die vom ersten Benutzer (“User1”) erstellte temporäre Tabelle auch von Benutzer 2 (“User2”) verwendet werden. Die Demonstration zeigt verschiedene Besonderheiten von temporären Tabellen, die bei der Wahl der geeigneten Technologie zu berücksichtigen sind:

  • Lokale temporäre Tabellen sind nicht auf einen Ersteller begrenzt
  • Lokale temporäre Tabellen sind nur auf die Verbindung beschränkt

Wird das zweite Codeelement (die Ausführung als “User2”) in einem neuen Abfragefenster von Microsoft SQL Server ausgeführt, wird eine Fehlermeldung ausgelöst, die besagt, dass eine temporäre Tabelle mit dem Namen [#User1_Table] nicht vorhanden ist.

Msg 208, Level 16, State 0,
Line 4 Ungültiger Objektname '#User1_Table'.

Globale temporäre Tabellen

Globale temporäre Tabellen weisen im Unterschied zu lokalen temporären Relationen als erste Zeichen im Namen zwei Hashtags („##“) auf. Eine globale temporäre Tabelle ist für JEDE Sitzung sichtbar. Globale temporäre Tabellen werden gelöscht, wenn ALLE Sitzungen, in denen auf diese Tabelle verwiesen wurde, die Verbindung mit der Instanz von SQL Server getrennt haben. Verwendet man obiges Script und ändert man die Definition der temporären Tabelle zu einer globalen Tabelle, kann in einer zweiten Session ohne Probleme auf diese Tabelle und deren Daten zugegriffen werden. Dieses Verhalten zeigt einen Schwachpunkt am Konzept der temporären Relationen:

  • Globale temporäre Tabellen können von allen Datenbankbenutzern uneingeschränkt verwendet werden.
  • Es gibt KEINE Möglichkeit, diese Berechtigungen mittels DENY oder REVOKE einzuschränken.
  • Globale temporäre Tabellen werden gelöscht, wenn keine Session mehr existiert, die auf die temporäre Tabelle zugegriffen hat

Aus diesen Gründen gilt es, bereits im Vorfeld eines Einsatzs von globale Tabellen eine Risikobewertung durchzuführen. Bei sensiblen und vertraulichen Daten sollte möglichst auf die Verwendung von globalen temporären Relationen verzichtet werden!

Temporäre Tabellen und Indexierung

Temporäre Tabellen verhalten sich wie “gewöhnliche” Datenbanktabellen. Das schließt ein, dass sie indiziert werden können und das Statistiken gepflegt werden. Der folgende Beispielcode erstellt eine temporäre Tabelle mit 1.010 Datensätzen. Anschließend wird die Tabelle indexiert und eine Abfrage auf den Index ausgeführt.

USE tempdb;
GO

-- Erstellen einer Demo-Tabelle
CREATE TABLE ##customer_country
(
    customer_id INT	    NOT NULL   IDENTITY (1, 1),
    CCode       CHAR(3)	    NOT NULL,
    c1	        CHAR(2000)  NOT NULL   DEFAULT ('just a filler')
);

-- Erstellen eines Clustered Index auf Customer_Id
CREATE UNIQUE CLUSTERED INDEX cix_customer_Id ON ##customer_country (customer_id);

-- Erstellen eines Index auf CCode
CREATE INDEX ix_CCode ON ##customer_country (CCode);

-- 1000 Datensätze für Deutschland sowie 10 Datensätze für Österreich eintragen
SET NOCOUNT ON;
GO

INSERT INTO ##customer_country(CCode) VALUES  ('DE');
GO 1000

INSERT INTO ##customer_country(CCode) VALUES ('AT');
GO 10

SET NOCOUNT OFF;
GO
SELECT * FROM ##customer_country WHERE CCode = 'AT';
SELECT * FROM ##customer_country WHERE CCode = 'DE';

Execution_Plan_01
Der Ausführungsplan zeigt, dass für beide Abfragen unterschiedliche Strategien (und Indexe) verwendet werden.
Vorsicht ist angeraten, wenn innerhalb einer Stored Procedure eine temporäre Tabelle erstellt und nachträglich Metadaten der Tabelle geändert werden! Das folgende Beispiel verdeutlicht das Problem. Innerhalb der Stored Procedure wird der Clustered Index mit Hilfe eines Constraints (Primary Key) erstellt und im Anschluss wird ein zusätzlicher non clustered Index erstellt.

CREATE PROC dbo.demo
AS
    SET NOCOUNT ON;
    DECLARE @i INT = 1;

    -- Erstellen der temporären Tabelle
    CREATE TABLE #master_table
    (
        Id       int      NOT NULL  IDENTITY(1, 1),
        Customer char(89) NOT NULL  DEFAULT ('a new customer'),
        c2       DATE     NOT NULL  DEFAULT (GETDATE())
    );

    -- Eintragen von 100 Datensätzen
    WHILE @i <= 100
    BEGIN
        INSERT INTO #master_table DEFAULT VALUES;
        SET @i += 1;
    END

    -- Provokation eines RECOMPILE, da Metadaten der Tabelle geändert werden!
    ALTER TABLE #master_table ADD CONSTRAINT pk_master_table PRIMARY KEY CLUSTERED (Id);

    -- Erstellung eines nonclustered Index ohne RECOMPILE!
    CREATE INDEX ix_master_table ON #master_table (c2);

    -- Auswahl eines beliebigen Datensatzes
    SELECT * FROM #master_table AS MT WHERE Id = 10;

    -- Löschen der temporären Tabelle
    DROP TABLE #master_table;

    SET NOCOUNT OFF;
GO

In einem Profiler Trace wird während der Ausführung der Prozedur protokolliert, wann ein RECOMPILE stattfindet.
SQLProfiler_01
Die Abbildung zeigt, innerhalb der Prozedur jedes Mal ein RECOMPILE ausgelöst wird, wenn die ALTER TABLE-Zeile ausgeführt wurde. Hierbei handelt es sich um eine Schemaänderung die zur Folge hat, dass ursprüngliche – mögliche – Ausführungspläne im Zusammenhang mit der temporären Tabelle verworfen werden müssen. Ebenfalls erkennbar ist, dass das Hinzufügen eines Indexes nicht automatisch zu einem RECOMPILE führt; ein neuer Index bedeutet nicht, dass sich Metadaten der Tabelle selbst ändern!

Temporäre Tabelle und Statistiken

Statistiken sind für adäquate Ausführungspläne unerlässlich. Wenn Microsoft SQL Server keine oder veraltete Statistiken zu einem Index besitzt, kann sich das negativ auf die Ausführung von Abfragen auswirken. Das folgende Beispiel zeigt den Zusammenhang zwischen – notwendigen – Statistiken und geeigneten Ausführungsplänen.

-- Erstellen der temporären Tabelle
CREATE TABLE ##customer_country
(
   customer_id INT        NOT NULL   IDENTITY(1, 1),
   CCode       CHAR(3)    NOT NULL,
   C1          CHAR(2000) NOT NULL   DEFAULT ('only a filler')
);
GO

SET NOCOUNT ON;
GO

-- 1.000 Datensätze für Deutschland eintragen
INSERT INTO ##customer_country (CCode) VALUES ('DE');
GO 1000

-- 10 Datensätze für Österreich eintragen
INSERT INTO ##customer_country (CCode) VALUES ('AT');
GO 10

-- Erstellung des Clustered Index ...
CREATE UNIQUE CLUSTERED INDEX cix_customer_id ON ##customer_country (customer_id);

-- Erstellen des non clustered index auf CCode
CREATE INDEX ix_CCode ON ##customer_country (CCode);
GO

Zunächst wird eine temporäre Tabelle erstellt und insgesamt 1.010 Datensätze mit einer unterschiedlichen Verteilung von Daten (CCode) hinzugefügt. Basierend auf Statistiken kann Microsoft SQL Server die geeigneten Ausführungspläne für die jeweiligen Abfragen nach den unterschiedlichen Länderkennzeichen ermitteln und umsetzen:

-- Auswahl aller Kunden in Österreich!
SELECT * FROM #customer_country AS CC WHERE CCode = 'AT';

-- Auswahl aller Kunden in Deutschland!
SELECT * FROM #customer_country AS CC WHERE CCode = 'DE';

Execution_Plan_01
Die Abbildung zeigt verschiedene Ausführungspläne. Eine Abfrage nach dem Länderkennzeichen “AT” führt zu einem INDEX SEEK mit einer Schlüsselsuche. Für Microsoft SQL Server ist diese Variante die effektivste Variante, da Microsoft SQL Server “weiß”, dass es nur 10 Datensätze sind. Die zweite Abfrage nach dem Länderkennzeichen “DE” ist kostenmäßig für einen INDEX SEEK zu teuer. Microsoft SQL Server kann auf Grund der bekannten Zeilenzahl von 1.000 Datensätzen das erforderliche IO berechnen und entscheidet sich für einen INDEX SCAN!

Temporäre Tabellen und Transaktionen

Temporäre Tabellen verhalten sich in konkurrierenden Systemen wie herkömmliche Tabellen. Wie bereits weiter oben erwähnt, gibt es lokale und globale temporäre Tabellen. Während lokale temporäre Tabellen nur innerhalb einer Session sichtbar sind, verhält es sich mit globalen temporären Tabellen anders. Dieser Umstand birgt Gefahren in Form von Sperren, die in einer Applikation auftreten können. Im vorherigen Beispiel wurde eine globale temporäre Tabelle verwendet. Wird nun in einer Session ein Update auf Zeilen in der Tabelle ausgeführt, wird auf diese Zeilen eine Sperre gesetzt:

-- Transaktion beginnen
BEGIN TRANSACTION Demo;
    -- Aktualisierung aller Länderkennzeichen von Österreich
    UPDATE ##customer_country
           SET CCode = 'FR'
    WHERE  CCode = 'AT';

    -- Welche Sperren wurden auf das Objekt gesetzt
    SELECT  I.name                      AS index_name,
            DTL.resource_type,
            DTL.resource_description,
            DTL.request_mode
    FROM    sys.dm_tran_locks AS DTL INNER JOIN sys.partitions AS P
            ON P.hobt_id = DTL.resource_associated_entity_id INNER JOIN sys.indexes AS I
            ON I.OBJECT_ID = P.OBJECT_ID AND I.index_id = P.index_id
    WHERE   DTL.resource_database_id = DB_ID() AND
            DTL.request_session_id = @@SPID
    ORDER BY
            request_session_id,
            resource_associated_entity_id 

    -- Transaktion bleibt geöffnet und in einer zweite Session wird versucht
    -- auf die Ressourcen mittels SELECT zuzugreifen
ROLLBACK TRANSACTION;

ROWSET_01
In der Abbildung ist zu erkennen, dass sowohl Teile des Clustered Index [cix_customer_id] als auch des Index auf das Feld CCode von Microsoft SQL Server blockiert werden. Auf die Datensätze selbst (KEY) wird eine eXklusive Sperre gesetzt. Die Intent eXclusive Sperren dienen nur einer möglichen Lock-Eskalation”. Wird in einer zweiten Sitzung versucht, auf die Daten der Kunden aus dem Land “AT” zuzugreifen, werden so lange keine Daten geliefert, bis die Sperren der aktualisierenden Transaktion wieder aufgehoben werden.

Temporäre Tabellen und Collation

Eine der größten Herausforderungen für Datenbankprogrammierer ist bei Verwendung von temporären Tabellen die Berücksichtigung der eingestellten Collation für Datenbanken und Tabellen. Die Herausforderung besteht darin, dass – ohne explizite Angabe einer Collation – immer die Einstellung der Datenbank TEMPDB verwendet wird. Das folgende Szenario verdeutlicht den Sachverhalt:

-- Welche Collation gibt es für TEMPDB und Server?
SELECT name                        AS DatabaseName,
       collation_name              AS DatabaseCollation,
       SERVERPROPERTY('Collation') AS ServerCollation
FROM   sys.databases AS D
WHERE  D.database_id = 2;

Mit Hilfe des obigen Codes werden Informationen über die aktuelle Collation von Server und TEMPDB abgerufen. Da es sich bei TEMPDB um eine Systemdatenbank handelt, sind die Einstellungen identisch.

-- Erstellung einer neuen Datenbank mit unterschiedlicher Collation!
CREATE DATABASE [demo_db]
COLLATE Latin1_General_BIN;

Im Anschluss wird eine neue Datenbank [demo_db] erstellt. Diese Datenbank verwendet eine andere Collation als den Serverstandard.

-- Erstellen einer Tabelle in demo_db mit Standardeinstellungen
USE demo_db;
GO

CREATE TABLE dbo.Customers
(
   Customer_Id	    INT          NOT NULL IDENTITY (1, 1),
   Customer_Number  CHAR(5)      NOT NULL,
   Customer_Name    VARCHAR(255) NOT NULL,
	
   CONSTRAINT pk_Customers_Id PRIMARY KEY CLUSTERED (Customer_Id)
);
GO

-- Zusätzlicher Index auf Customer_Number
CREATE UNIQUE INDEX ix_Customer_Number ON dbo.Customers(Customer_Number)
INCLUDE (Customer_Name);

-- Eintragen von 3 Datensätzen
INSERT INTO dbo.Customers (Customer_Number, Customer_Name)
VALUES
('A0001', 'db Berater GmbH'),
('A0092', 'ABC GmbH'),
('B2345', 'ZYX AG');
GO

In er neuen Datenbank wird eine Tabelle mit 3 Datensätzen angelegt. Da keine explizite Collation für die Textattribute angegeben wurden, wird die Collation der Datenbank [demo_db] übernommen.

-- Information über die Metadaten der Tabelle dbo.Customers
SELECT	OBJECT_NAME(C.object_id)	AS Table_Name,
	C.name				AS Column_Name,
	S.name				AS Type_Name,
	C.column_id,
	C.max_length,
	C.collation_name
FROM	sys.columns AS C INNER JOIN sys.types AS S
	ON (C.system_type_id = S.system_type_id)
WHERE   object_id = object_id('dbo.Customers', 'U');

ROWSET_03
Der nachfolgende Code erstellt eine temporäre Tabelle und eine Kundennummer wird als Suchkriterium eingetragen. Anschließend wird die Tabelle [dbo].[Customer] mit dieser temporären Tabelle mittels JOIN verbunden und die Abfrage ausgeführt.

-- Erstellen einer temporären Tabelle
CREATE TABLE #t (Customer_Number CHAR(5) PRIMARY KEY CLUSTERED);
GO

-- Auswahl aus dem Frontend wird in temporärer Tabelle gespeichert
INSERT INTO #t (Customer_Number) VALUES ('A0001');
GO

-- Auswahl von Datensätzen aus Kundentabelle
SELECT C.*
FROM   dbo.Customers AS C INNER JOIN #t AS CN
       ON (C.Customer_Number = CN.Customer_Number);

Die Auswahl führt zu einem klassischen Fehler, der darauf hinweist, dass die Collation beider Verbindungsfelder nicht übereinstimmen.

Meldung 468, Ebene 16, Status 9, Zeile 3
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_BIN"
in the equal to operation.

Tabellenvariablen

Tabellenvariablen wurden mit Microsoft SQL Server 2000 erstmals eingeführt. Mit dem Einzug von Tabellenvariablen in die Programmiersprache von Microsoft SQL Server sollten viele Dinge vereinfacht.Während temporäre Tabellen während einer ganzen Session gültig sind, beschränkt sich die Gültigkeit von Tabellenvariablen immer auf den BATCH! Einige Vor- und Nachteile als auch hartnäckige Mythen rund um Tabellenvariablen werden nachfolgend beschrieben.

Gültigkeit von Tabellenvariablen

Tabellenvariablen sind nur innerhalb eines Batches gültig. Das bedeutet für die Programmierung, dass in dem Moment, in dem ein GO den Batch abschließt, die Tabellenvariable ihre Gültigkeit verliert. Hier unterscheidet sich die Tabellenvariable nicht von einer herkömmlichen Variable, wie der nachfolgende Code zeigt:

-- Definition einer Tabellenvariablen
DECLARE @customer_country TABLE
(
    customer_id  INT        NOT NULL IDENTITY (1, 1),
    CCode        CHAR(3)    NOT NULL,
    c1           CHAR(2000) NOT NULL DEFAULT ('just a filler')
);

-- 100 Datensätze aus Deutschland
DECLARE @I INT = 1;
WHILE @I <= 100
BEGIN
    INSERT INTO @customer_country(CCode) VALUES ('DE')
    SET @I += 1;
END

-- Anzeige aller Datensätze IM Batch!
SELECT * FROM @customer_country AS CC;
GO

-- Batch ist beendet und Tabellenvariable ist nicht mehr gültig
SELECT * FROM @customer_country AS CC;
GO

Wenn der obige Code vollständig ausgeführt wird, läuft das erste SELECT einwandfrei, da es sich innerhalb des Batches befindet. Nach dem GO werden erneut Daten aus der Tabellenvariable angefordert. Da jedoch mittels GO der vorherige Batch abgeschlossen ist, wird die Tabellenvariable nicht erkannt.

Tabellenvariablen und Indexierung

Bis Microsoft SQL Server 2012 waren Indexe in Tabellenvariablen nicht möglich. Sofern man eine “Indexierung” wünschte, konnte man sich nur über den Umweg eines “Constraints” helfen. Der nachfolgende Code zeigt, wie innerhalb einer Tabellenvariable bis SQL Server 2012 Indexe erstellt werden konnten.

DECLARE @customer_country TABLE
(
    customer_id INT        NOT NULL  IDENTITY (1, 1),
    CCode       CHAR(3)    NOT NULL,
    c1          CHAR(2000) NOT NULL  DEFAULT ('just a filler'),

    PRIMARY KEY CLUSTERED (customer_id),
    UNIQUE ix_ccode (CCode)
);

Seit Microsoft SQL Server 2014 ist diese Einschränkung jedoch aufgehoben und man kann nun auch non clustered Indexe innerhalb der Tabellendefinition deklarieren.

DECLARE @customer_country TABLE
(
    customer_id INT        NOT NULL  IDENTITY (1, 1),
    CCode       CHAR(3)    NOT NULL,
    c1          CHAR(2000) NOT NULL  DEFAULT ('just a filler'),

    PRIMARY KEY CLUSTERED (customer_id),
    INDEX ix_ccode NONCLUSTERED (CCode)
);

Auf Grund der restriktiven Regeln für Tabellenvariablen ist kein Risiko der Neukompilierung einer Stored Procedure vorhanden. Für Tabellenvariablen gilt:

  • Tabellenvariablen sind nur im aktuellen Batch gültig
  • Die vollständige Tabelle muss im DECLARE-Block beschrieben werden
    Zusätzliche Indexe oder Attribute sind nach der Deklaration nicht mehr möglich
  • Es wird die COLLATION der aktuellen Datenbank für Textdatentypen verwendet
  • TRUNCATE wird nicht unterstützt (DDL-Befehl)

Da außerhalb der Deklaration einer Tabellenvariable keine zusätzlichen Änderungen an der Tabellenvariable erlaubt sind, ist eine Rekompilierung – sofern sie nicht explizit angegeben wird – ausgeschlossen.

Tabellenvariablen und Statistiken

Statistiken werden für temporäre Tabellen NICHT gepflegt. Diese Einschränkung kann bei der Wahl von Tabellenvariablen ein echter Performancekiller werden. Als Beispiel soll folgende Prozedur (Microsoft SQL Server 2014) dienen:

-- Deklaration der Tabellenvariable
DECLARE @customer_country TABLE
(
    customer_id  INT        NOT NULL  IDENTITY (1, 1),
    CCode        CHAR(3)    NOT NULL,
    c1           CHAR(2000) NOT NULL  DEFAULT ('just a filler'),

    PRIMARY KEY CLUSTERED (customer_id),
    INDEX ix_ccode NONCLUSTERED (CCode)
);

-- 1.000 Datensätze aus Deutschland
DECLARE @I INT = 1;
WHILE @I <= 1000
BEGIN
    INSERT INTO @customer_country(CCode) VALUES ('DE')
    SET @I += 1;
END

-- 10 Datensätze aus Österreich
DECLARE @I INT = 1;
WHILE @I <= 10
BEGIN
    INSERT INTO @customer_country(CCode) VALUES ('AT')
    SET @I += 1;
END

-- Abfrage nach Datensätzen aus Deutschland!
SELECT * FROM @customer_country AS CC WHERE CCode = 'DE';

-- Abfrage nach Datensätzen aus Österreich!
SELECT * FROM @customer_country AS CC WHERE CCode = 'AT';

Im Code wird zunächst eine Tabellenvariable deklariert, die mit 1.000 Datensätzen aus Deutschland und 10 Datensätzen aus Österreich gefüllt wird. Bei der Auswahl der Datensätze aus Deutschland – wie auch aus Österreich – werden jedoch die gleichen Ausführungspläne verwendet!
Execution_Profile_01
Die Abbildung zeigt, dass Microsoft SQL Server davon ausgeht, dass nur eine Zeile in der Tabelle steht. Somit ist für Microsoft SQL Server ein INDEX SCAN über den Clustered Index optimaler als ein INDEX SEEK über den Index ix_ccode und eine weitere Schlüsselsuche. Das Problem in diesem Beispiel liegt auf der Hand; Microsoft SQL Server wird NIE den Index verwenden, der auf dem Attribut CCode liegt. Microsoft SQL Server geht grundsätzlich von einem Datensatz aus, der geliefert werden soll. Somit wird einem INDEX SCAN der Vorzug gegeben. Die Ausführung der Abfrage nach Kunden aus Österreich belegt diese Vermutung.
Execution_Profile_02
Noch deutlicher wird die “Nichtverwendung” von Statistiken mit dem nachfolgenden Skript. Die Verwendung der Traceflags bewirkt eine Ausgabe der von Microsoft SQL Server geprüften und verwendeten Statistiken für die Abfrage.

SET NOCOUNT ON;
GO

DECLARE	@t TABLE
(
    Id  INT         NOT NULL  IDENTITY(1,1),
    C1  CHAR(1000)  NOT NULL  DEFAULT ('a filler only!'),
    C2  DATE        NOT NULL  DEFAULT (GETDATE()),

    PRIMARY KEY CLUSTERED (Id)
);

DECLARE @i INT = 1
WHILE @i <= 1000
BEGIN
    INSERT INTO @t DEFAULT VALUES;
    SET @i += 1;
END


SELECT * FROM @t AS T
WHERE T.Id = 100
OPTION
(
    RECOMPILE,
    QUERYTRACEON 3604,
    QUERYTRACEON 9292,
    QUERYTRACEON 9204
);

Mit Traceflag 9292 werden die Statistiken angezeigt, die für den Query Optimizer von Microsoft SQL Server als „interessant“ für den Ausführungsplan eingestuft werden; Traceflag 9204 zeigt, welche Statistiken der Query Optimizer von Microsoft SQL Server vollständig lädt, um einen Ausführungsplan zu generieren. Das Ergebnis ist LEER!

Tabellenvariablen und Transaktionen

Im Gegensatz zu Temporären Tabellen sind Tabellenvariablen nicht transaktionsgebunden. Wird der Wert einer Datenzeile in einer Tabellenvariable innerhalb einer expliziten Transaktion geändert, wird der ursprüngliche Wert bei einem möglichen ROLLBACK nicht wiederhergestellt. Tabellenvariablen speichern UPDATES unabhängig von einer expliziten Transaktion.

DECLARE @t TABLE
(
    Id  INT         NOT NULL  IDENTITY(1,1),
    C1  CHAR(1000)  NOT NULL  DEFAULT ('a filler only!'),
    C2  DATE        NOT NULL  DEFAULT (GETDATE()),

    PRIMARY KEY CLUSTERED (Id)
);

DECLARE @i INT = 1
WHILE @i <= 10
BEGIN
   INSERT INTO @t DEFAULT VALUES;
   SET @i += 1;
END

BEGIN TRANSACTION
    UPDATE @t
    SET    c1 = 'AT'
    WHERE  Id = 1;
ROLLBACK TRANSACTION

SELECT * FROM @t;
GO

Der Vorteil dieses Verfahrens ist natürlich naheliegend; da Tabellenvariablen nicht transaktionsgebunden sind, benötigen sie keine Sperren und müssen kein Transaktionsprotokoll führen – sie sind bei DML-Operationen schneller!

Verwendung von Tabellenvariablen

Bei den vielen Nachteilen, die Tabellenvariablen – offensichtlich – haben, stellt man sich die Frage, wozu man Tabellenvariablen verwenden sollte. Ein sehr großer Vorteil von Tabellenvariablen ist, dass man sie wie ein Array an Funktionen und Stored Procedures übergeben kann. Man kann also zunächst Werte in eine Tabellenvariable füllen um sie anschließend in einer Stored Procedure zu verarbeiten. Der folgende Code zeigt ein mögliches Szenario, das mit Hilfe von Tabellenvariablen gelöst werden kann.
“In einer Applikation können aus einer Liste von Kunden mehrere Kunden ausgewählt werden, um sie anschließend in einem Report auszugeben”

-- Zunächst wird ein neuer Datentyp TABLE angelegt
CREATE TYPE Customer_Id AS TABLE (customer_id int NOT NULL PRIMARY KEY CLUSTERED);
GO

-- Erstellung einer Prozedur für die Auswahl von Kunden
-- Der zu übergebende Datentyp ist der zuvor definierte TYPE
CREATE PROC dbo.proc_CustomerList
	@C AS Customer_Id READONLY
AS
    SET NOCOUNT ON;

    SELECT * FROM dbo.Customer
    WHERE  Customer_Id IN (SELECT Customer_Id FROM @c);

    SET NOCOUNT OFF;
GO

-- Erstellung einer Tabelle vom Typen [Customer_Id]
DECLARE	@t AS Customer_Id;

-- Eintragen der gewünschten Kunden-Id
INSERT INTO @t(customer_id)
VALUES (1), (2), (3), (4), (5);

-- Ausführung der Prozedur
EXEC dbo.proc_CustomerList @t;
GO

Tabellenvariablen sind nur im RAM persistent?

Diese Aussage ist so nicht richtig. Selbstverständlich muss für die Erstellung einer temporären Tabelle ebenfalls Speicher in TEMPDB allokiert werden. Hier spielen jedoch andere Faktoren eine Rolle, die zu der Annahme führen könnten, dass Tabellenvariablen nur im RAM vorhanden sind – die Art und Weise, wie Microsoft SQL Server (wie übrigens auch andere RDBMS-Systeme) Transaktionsdaten behandelt. Beim Schreiben von Datensätzen werden – entgegen landläufiger Meinung – die Daten nicht sofort in die Datenbank selbst geschrieben. Vielmehr werden lediglich Transaktionen unmittelbar in das Transaktionsprotokoll geschrieben; die Datenseiten selbst verbleiben zunächst im RAM. Durch einen CHECKPOINT werden die Daten selbst (dirty pages) erst in die Datenbankdatei(en) geschrieben. Mit dem nachfolgenden Skript wird eine Tabellenvariable erzeugt und 100 Datensätze in diese Tabellenvariable geschrieben. Anschließend werden die Daten mit ihrer physikalischen Position in der Datenbank ausgegeben. Nachdem ein CHECKPOINT ausgeführt wurde, werden die Daten unmittelbar in der Datenbankdatei untersucht!

SET NOCOUNT ON;
GO

-- Was steht derzeit im Transaktionsprotokoll?
SELECT * FROM sys.fn_dblog(NULL, NULL);
GO

-- Beginn der Transaktion
BEGIN TRANSACTION
DECLARE	@t TABLE
(
    Id  INT        NOT NULL  IDENTITY(1,1),
    C1  CHAR(1000) NOT NULL  DEFAULT ('a filler only!'),
    C2  DATE       NOT NULL  DEFAULT (GETDATE()),

    PRIMARY KEY CLUSTERED (Id)
);

-- Eintragen von 100 Datensätzen
DECLARE @i INT = 1
WHILE @i <= 100
BEGIN
    INSERT INTO @t DEFAULT VALUES;
    SET @i += 1;
END

-- Was wurde in das Transaktionsprotokoll geschrieben?
SELECT * FROM sys.fn_dblog(NULL, NULL);

-- Wo werden die Daten physikalisch gespeichert?
SELECT * FROM @t AS T
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS FPLC
GO
COMMIT TRANSACTION;

-- Anzeigen der ersten Datenseite, 
-- auf der Daten der Tabellenvariable gespeichert sind.
DBCC TRACEON (3604);
DBCC PAGE ('tempdb', 1, 127, 1);
GO

Die nachfolgende Abbildung zeigt, dass die Daten der Tabellenvariable auf die Disk geschrieben wurden – sie sind also nicht nur im RAM präsent!
ROWSET_02
DBCC_PAGE_01

Warum Tabellenvariablen, obwohl es bereits temporäre Tabellen gibt?

Die nachfolgende – nicht abschließende – Liste gibt einen Überblick über die Besonderheiten von Tabellenvariablen.

  • Tabellenvariablen haben wie lokale Variablen einen definierten Bereich, an dessen Ende sie automatisch gelöscht werden.
  • Im Vergleich zu temporären Tabellen haben Tabellenvariablen weniger Neukompilierungen einer gespeicherten Prozedur zur Folge.
  • Transaktionen, dauern nur so lange wie eine Aktualisierung der Tabellenvariable. Deshalb benötigen Tabellenvariablen weniger Ressourcen für Sperren und Protokollierung.
  • Da Tabellenvariablen einen beschränkten Bereich haben und nicht Bestandteil der persistenten Datenbank sind, sind sie von Transaktionsrollbacks nicht betroffen.

Wann mit Tabellenvariablen oder temporärer Tabelle arbeiten?

Die Antwort auf diese Frage ist recht einfach: “It depends”. Es hängen viele Faktoren von der Entscheidung für die richtige Strategie ab:

  • Anzahl der Zeilen, die in die Tabelle eingefügt werden.
  • Anzahl der Neukompilierungen, aus denen die Abfrage gespeichert wird.
  • Typ der Abfragen und deren Abhängigkeit von Indizes und Statistiken bei der Leistung.

In manchen Situationen ist es nützlich, eine gespeicherte Prozedur mit temporären Tabellen in kleinere gespeicherte Prozeduren aufzuteilen, damit die Neukompilierung für kleinere Einheiten stattfindet. Im Allgemeinen werden Tabellenvariablen verwendet, wenn es um ein kleines Datenvolumen geht! Wenn große Datenmengen wiederholt verwendet werden müssen, hat die Arbeit mit temporären Tabellen deutliche Vorteile. Außerdem können Indizes für die temporäre Tabelle erstellt werden, um die Abfragegeschwindigkeit zu erhöhen. Microsoft empfiehlt zu testen, ob Tabellenvariablen für eine bestimmte Abfrage oder gespeicherte Prozedur geeigneter sind als temporäre Tabellen. Eine Aufzählung von Vor- und Nachteilen temporärer Tabellen und Tabellenvariablen hat Yogesh Kamble in einem “Quiz” gegeben.

Herzlichen Dank für’s Lesen.

Weiterführende Weblinks

Datenbank TEMPDB http://msdn.microsoft.com/de-de/library/ms190768.aspx
CREATE TABLE http://msdn.microsoft.com/de-de/library/ms174979.aspx
EXECUTE AS http://msdn.microsoft.com/de-de/library/ms188354.aspx
Tabellenvariablen http://msdn.microsoft.com/de-de/library/ms175010.aspx

Uwe Ricken: Performancevorteile durch Instant File Initialization

Beim Anlegen von Datenbankdateien (Daten, Log) werden standardmäßig die zu erstellenden Dateien beim Initialisieren mit “0” aufgefüllt, damit eventuell auf dem Datenträger verbliebende Daten von vorherigen (gelöschten) Dateien überschrieben werden. Dieses Verfahren betrifft nicht nur das Erstellen neuer Datenbanken sondern auch die Wiederherstellung von Datenbanken aus einem Backup oder die Vergrößerung einer Datenbank. Welchen Einfluss diese Vorgänge auf die Leistung von Microsoft SQL Server hat, beschreibt der nachfolgende Artikel.

Was ist Instant File Initialization?

Duch “Instant File Initialization” lässt sich der Prozess des Erstellens oder Vergrößerns von Dateien beschleunigen, indem das Überschreiben von neuem Speicher für die Datenbankdatei (Zeroing out) nicht durchgeführt wird. Microsoft SQL Server erstellt die Datei und alloziert den benötigten Speicher; aber der langwierige Prozess des Überschreibens bleibt aus. Dieser Vorteil kann aber nur auf Datenbankdateien angewendet werden, Transaktionsprotokolle können diesen Vorteil nicht nutzen. Dass Transaktionsprotokoll-Dateien diesen Vorteil nicht nutzen können, hängt damit zusammen, dass das Transaktionsprotokoll ein rotierendes Verfahren verwendet, um freie / ungenutzte VLF (Virtual Log File) wiederzuverwenden.

Vorteil von Instant File Initialization

Durch das Verhindern von “Zeroing Out” steht eine Datenbank schneller (wieder) zur Verfügung. Instant File Initialization kann bei den folgenden Prozessen einen erheblichen Geschwindigkeitsvorteil bringen:

  • CREATE DATABASE
  • ALTER DATABASE … MODIFY FILE
  • RESTORE DATABASE
  • AUTOGROWTH für Datenbanken

Alle vier genannten Prozesse haben eines gemeinsam; sie erstellen oder ändern Datenbankdateien, die von Microsoft SQL Server verwaltet werden.

Nachteil von Instant File Initialization

Da Instant File Initialization vorhandenen und allozierten Speicher nicht überschreibt, besteht die Gefahr, dass mit geeigneter Software Daten, die vorher von der Festplatte gelöscht wurden, ausgelesen werden können. Es muss vor der Aktivierung eine Sicherheitsbewertung erfolgen; wird der Speicher auch für “normale” Filesystem-Aktivitäten verwendet oder werden Datenbanken häufig gelöscht, sollte auf Instant File Initialization eventuell verzichtet werden.

Wie kann man erkennen, ob Microsoft SQL Server “Instant File Initialization” verwendet?

Instant File Initialization kann NICHT in Microsoft SQL Server konfiguriert werden, da es sich dabei nicht um eine Funktionalität von Microsoft SQL Server handelt. Es handelt sich um ein Sicherheitsprivileg, dass dem Dienstkonto der ausgeführten Instanz von Microsoft SQL Server zugewiesen werden kann. Um festzustellen, ob Instant File Initialization aktiviert ist, stehen zwei Möglichkeiten zur Verfügung:

  • Außerhalb von Microsoft SQL Server: Überprüfung der lokalen Sicherheitsrichtlinie
  • In Microsoft SQL Server: Anlegen einer neuen Datenbank bei gleichzeitiger Protokollierung in das Fehlerprotokoll

Lokale Sicherheitsrichtlinie

Instant File Initialization ist ein Sicherheitsprivileg, dass standardmäßig nur Administratoren zugewiesen ist. Die lokalen Sicherheitsrichtlinien wird durch den Start von [secpol.msc] ausgeführt.
Lokale_Sicherheitsrichtlinie_01
In einem englischsprachigen System heißt die oben gezeigte Richtlinie “Perform Volume Maintenance Tasks” und findet sich in [User Rights Assignment]. Ist das Dienstkonto von Microsoft SQL Server dieser Sicherheitsrichtlinie zugeordnet, ist Instant File Initialization für Microsoft SQL Server aktiviert.

Prüfung aus Microsoft SQL Server

In einem Umfeld, in dem ein DBA keinen unmittelbaren Zugang zum Betriebssystem besitzt (Segregation of Duty), gibt es ebenfalls eine Möglichkeit, zu testen, ob das Dienstkonto von Microsoft SQL Server das Privileg besitzt. Das nachfolgende Script startet die Protokollierung und legt eine neu Datenbank mit einer Initialgröße von 1.500 MB (1 GB für Daten und 500 MB für das Protokoll) an.

/* Aktivierung der Protokollierung */
DBCC TRACEON (3004, 3605, -1);

/*
   Erstellung einer neuen Datenbank mit einer initialen Größe */
   von 1 GB für Daten und 500 MB für das Protokoll
*/
CREATE DATABASE Test
ON PRIMARY
(
    NAME = 'Test',
    FILENAME = 'S:\BACKUP\Test.mdf',
    SIZE = 1000MB,
    MAXSIZE = 10000MB,
    FILEGROWTH = 0MB
)
LOG ON
(
    NAME = 'Test_Log',
    FILENAME = 'S:\BACKUP\Test.ldf',
    SIZE = 500MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 0MB
);
GO

-- Auslesen des Fehlerprotokolls von Microsoft SQL Server
EXEC xp_readerrorlog;

Nachdem die neue Datenbank angelegt wurde, zeigt, der Inhalt des ausgelesenen Fehlerprotokolls folgende Einträge:
XP_READERRORLOG_01
Die Zeilen 12 – 18 zeigen, dass “Instant File Initialization” für das Dienstkonto NT Service\MSSQL$SQL_2012 nicht zur Verfügung steht; die Datendatei wurde mittels “Zeroing” mit 0 gefüllt. Für das Anlegen einer Datenbankdatei für die TEST-Datenbank benötigte das System 17 Sekunden (Zeile 12 – 13). Für das Anlegen der Protokolldatei wurden 9 Sekunden benötigt. Insgesamt werden für das Erstellen der Datenbank [Test] 26 Sekunden benötigt. Das Protokoll für das Erstellen der Datenbank bei Zuweisung des Rechts für das Dienstkonto sieht wie folgt aus:
XP_READERRORLOG_02
Deutlich ist zu erkennen, dass – wie bereits oben ausgeführt – ausschließlich die Protokolldatei den Prozess des “Zeroing” über sich ergehen lassen muss. Da die Datenbankdatei unmittelbar erstellt wurde, ist die Datenbank innerhalb von 9 Sekunden betriebsbereit.

Instant File Initialization bei neuen Datenbanken

Wie die Tests demonstrieren, liegt der Vorteil bei der Anlage von neuen Datenbanken in der Bereitstellung der Datenbank innerhalb weniger Sekunden. In der Regel ist das Erstellen von neuen Datenbanken – sofern es nicht aus Applikation selbst geschieht – kein zeitkritischer Vorgang.

Instant File Initialization bei Wiederherstellung von Datensicherungen

Die Wiederherstellung einer Datenbank kann ein zeitkritisches Problem werden, wenn zum Beispiel die Produktionsdatenbank betroffen ist. Exakt dieser Umstand wurde einem Kunden zum Opfer, der eine Datenbank mit einer Dateigröße für die Daten von 750 GB wiederherstellen musste. Tragisch an dieser Situation war, dass die Datenbank selbst nur zu ca. 50% mit Daten gefüllt war. Die Wiederherstellung verzögert sich um die Zeit, die für das “Zeroing” benötigt wird. Dieser Vorgang kann jedoch eingespart werden, da die Daten unmittelbar nach der Initialisierung in die neu angelegten Datenbank-Dateien geschrieben werden.

Instant File Initialization beim Vergrößern von Datenbanken

In vielen Microsoft SQL Server Installationen ist auffällig, dass die Vergrößerung einer Datenbank entweder sehr klein gewählt wurde (1 MB) oder aber – für den angegebenen Workload – zu groß. Entscheidend für beide Szenarien ist, dass bei fehlendem Recht für “Instant File Initialization” die Anwendungen unverhältnismäßig lange warten müssen, bis das “Zeroing” abgeschlossen ist. Ist ein hoher Workload in der Applikation erkennbar, wird bei einem Vergrößerungsintervall von 1 MB zu oft die Datenbank erweitert und ein “Zeroing” initiiert. Bei einer Größe von 500 MB müsste die Applikation ca. 9 Sekunden warten, bis der Prozess abgeschlossen ist. Für eine Anwendung, die fast ausschließlich auf hohe OLTP-Vorgänge beschränkt ist, ein absolutes K.O-Kriterium.

Testmessungen

Um ein Gefühl für die Zeitunterschiede zu vermitteln, wurde eine Testdatenbank mit verschiedenen Größen und auf verschiedenen Medien erstellt. Für die Messungen wird die Testdatenbank in vier unterschiedlichen Größen (100 MB, 500 MB, 1.000 MB, 2.000 MB) mit identischer Größe für die Protokolldatei (100 MB) sowohl auf einer HDD als auch einer SDD erstellt. Eine Messung erfolgt mit jeweils aktivierter (IFI) und deaktivierter (No IFI) Berechtigung. Für die Messungen wurden als HDD eine [Toshiba MK5061GSY] mit einer Blockgröße von 64 KBytes verwendet. Für die Tests auf der SSD wurde eine [CRUCIAL CT960M500] verwendet, die ebenfalls eine Blockgröße von 64 KBytes verwendet.

Tabelle_Messergebnisse

Klar erkennbar ist, dass – unabhängig von HDD und/oder SDD die Erstellungszeiten fast proportional wachsen, wenn “Instant File Initialization” nicht möglich ist. Bei Aktivierung sind die Zeiten nahezu identisch, da die Größe der Protokolldatei in allen Tests identisch ist. Die Variationen rühren wohl eher aus Streuungen!

Messungen_Grafik

Bei einer 500 GB großen Datenbank würde die Erstellung auf einer HDD bei deaktiviertem “Instant File Initialization” nahezu 3 Stunden benötigen! Selbst auf einer SSD müssen immer noch ca. 35 Minuten vergehen, bevor die Datenbank online ist. Da in der Regel neue Datenbanken eher in kleineren Dimensionen erstellt werden, sind diese Zeiten wohl eher zu vernachlässigen. Jedoch sieht es bei der Wiederherstellung von Datenbanken ganz anders aus. Müssen erst die erstellten Datenbankdateien mittels “Zeroing out” überschrieben werden, kann so eine nicht unerhebliche Zeit für die Wiederherstellung vergehen. Ein Umstand, der in einer Produktionsumgebung bei entsprechendem SLA schnell zu Problemen führen kann.

Zeitweise Deaktivierung von Instant File Initialization

Die durchgeführten Tests kann man auf dem eigenen Datenbanksystem selbst durchspielen, ohne die Berechtigung immer wieder zu ändern (und damit einen Neustart des Dienstes durchzuführen). Sollte auf den eigenen Systemen Instant File Initialization aktiviert sein, so kann man diese Option zeitweilig mit dem Traceflag 1806 zwischenzeitlich deaktivieren. Der Vorteil bei der Verwendung dieses Traceflags besteht vor allen darin, dass Datenbanken, die einer höhere Sicherheitsstufe unterliegen, bei der Erstellung  einem “Zeroing out” unterzogen werden.

Einschränkungen von Instant File Initialization

Instant File Initialization unterliegt besonderen Einschränkungen, die im Vorfeld geprüft werden müssen. So ist Instant File Initialization nur möglich, wenn die nachfolgenden Voraussetzungen erfüllt sind:

Zusammenfassung

Instant File Initialization gibt dem DBA die Möglichkeit, Datenbankoperationen, die einen unmittelbaren Einfluss auf die Eigenschaften der Datenbankdateien besitzen, durch “Instant File Initialization” zu beschleunigen. Bevor man Instant File Initialization aktiviert, sollte das betriebliche Umfeld sehr genau geprüft werden. Insbesondere ein Blick auf das Dateisystem in Verbindung mit der Sensibilität der Daten ist ein wichtiges Kriterium für die Aktivierung / Deaktivierung. In größeren Unternehmen gibt es eine klare Trennung von Aufgaben (Segregation of Duty). Ein Gespräch mit dem verantwortlichen Administrator für das Betriebssystem schafft schnell Klarheit.

Insgesamt kommt die Bedeutung von Instant File Initialization nicht bei der Erstellung von “neuen” Datenbanken zum tragen. Vielmehr ist bei zeitkritischen Wiederherstellungsszenarien diese Option von größerer Bedeutung. Auch bei Applikationen, die sehr viele Daten schreiben und somit die Datenbank regelmäßig vergrößern müssen, ist Instant File Initialization ein Gewinn für die Geschwindigkeit und Stabilität der Applikation.

Sollten die Richtlinien des Unternehmens Instant File Initialization verhindern, so wäre aus meiner Sicht das Recht zu erteilen und der SQL Server Dienst mit dem Traceflag 1806 zu starten. Somit wäre Instant File Initialization grundsätzlich nicht möglich – es wäre aber für einen Administrator im Falle einer Wiederherstellung einer großen Datenbank aus einem Backup möglich, für diese Operation Instant File Initialization zeitweise zu aktivieren.

Herzlichen Dank fürs Lesen!

Andre Essing: Ich spreche auf dem SQL Saturday Rheinland

In einer Woche ist es wieder so weit, am Samstag, den 13.06.2015 findet in der Hochschule Bonn-Rhein-Sieg der nächste deutsche SQL Saturday statt. Das Aufgebot an Sprechern und Vorträgen ist wie immer hochkarätig und die internationale Beteiligung ist dieses Jahr bombastisch. Deshalb freue ich mich ganz besonders dieses Jahr auch die Bühne der Sprecher zu […]

Dieser Beitrag wurde auf dem Blog von Andre Essing veröffentlicht.

Sascha Dittmann: Performance Optimierung von SQL Server Azure VMs

Performance Optimierung von SQL Server Azure VMsDie Virtuellen Maschine der D-Serie in Azure eigenen sich besonders gut für Anwendungen mit einer hohen Arbeitslast.
In diesem Blog Post möchte ich deshalb zwei kleine Optimierungen vorstellen, mit denen man bei SQL Server Azure VMs der D-Serie zusätzliche Performance rausholen kann…

Im September letzten Jahres hat Microsoft eine neue Reihe an »Azure Virtual Machines« vorgestellt, die besonderen Wert auf Performance legen. Diese D-Serien VMs bieten bis zu 112GB Arbeitsspeicher, bis zu 800GB lokalen SSD-Festplattenspeicher und Prozessoren die ca. 60% schneller sind als die der A-Serie.

Durch die lokale SSD, welche als D: Laufwerk in der Windows-VM zur Verfügung gestellt wird, kann man bei SQL Server Azure VMs zusätzliche Performance rauskitzeln.

Verschieben der tempdb-Datenbank

Eine Möglichkeit, diese SSDs zu auszunutzen, ist es die tempdb-Datenbank zu verschieben.

Hierzu richtet man 1-2 zusätzliche Verzeichnisse auf der SSD ein, wie beispielsweise D:\SQLData und D:\SQLLog, und weißt der tempdb-Datenbank den neuen Speicherort zu:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'D:\SQLLog\templog.ldf');

Zum Prüfen der Änderung kann dann folgendes Skript genutzt werden:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');

Anschließend muss der SQL Server Dienst neu gestartet werden, damit die Änderung aktiv wird.

Buffer Pool Extensions

Eine weitere Möglichkeit kam mit dem SQL Server 2014 (Standard, Business Intelligence und Enterprise) hinzu.

Mit den Buffer Pool Extensions (BPE) nutzt der Buffer Pool der SQL Engine zusätzliche die SSD-Festplatte, um somit noch mehr Daten im Cache halten zu können.

Der Buffer Pool ist ein globaler In-Memory-Cache des SQL Servers, welcher Data-Pages zwischenspeichert, um somit die Lese-Leistung zu verbessern.

Zur Aktivierung der Buffer Pool Extension wird erneut ein Verzeichnis, wie beispielsweise D:\SQLCache, auf der SSD-Platte benötigt.

Anschließend kann mit folgendem Skript das BPE-Feature konfiguriert und aktiviert werden:

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(
  FILENAME = 'D:\SQLCache\SQLSERVER.BPE'
  , SIZE = <Gewünschte-Cache-Größe> [ KB | MB | GB ]
);

Vorsicht beim Starten / Neustart der VM

Einen kleinen Wermutstropfen haben diese Änderungen allerdings.

Wenn die Virtuelle Maschine gestoppt und wieder gestartet wird, werden alle Daten auf der SSD gelöscht.

Dies wäre zwar kein Problem, da der SQL Server die Dateien wiederherstellen würde; Allerdings wird dies, durch die fehlenden Verzeichnisse, fehlschlagen.

Eine Möglichkeit dies zu umgehen ist ein Skript, das bei Starten des Computers ausgeführt wird.

Deshalb habe ich auf der Systemplatte ein Verzeichnis angelegt (C:\Scripts) und dort folgendes Skript (Dateiname SQLStartup.cmd) hinterlegt:

@echo off
md D:\SQLCache
md D:\SQLData
md D:\SQLLog
net start MSSQLSERVER
net start SQLSERVERAGENT

Zusätzlich muss noch der Start-Typ des SQL Server Dienstes auf »Manual« umgestellt …

Start-Typ des SQL Servers aus Manuell umstellen

… und das neu erstellte Skript – als Startup-Script – in den Lokalen Gruppenrichtlinien hinzugefügt werden.

Zum Öffnen des Local Group Policy Editors kann in der Befehlszeile »gpedit.msc« eingeben.

Startup-Skript hinzufügen (1)

Startup-Skript hinzufügen (2)

 



Verwendete Bildquellen:
© Armin Tost (AT) / pixelio.de

Der Beitrag Performance Optimierung von SQL Server Azure VMs erschien zuerst auf Sascha Dittmann.

Uwe Ricken: Eindeutigkeit von Indexschlüsseln in Clustered Index

Die Verwendung von Indexen in Datenbanksystemen stellt einen wichtigen Schritt dar, um eine Datenbank performant zu gestalten. Die Wahl eines geeigneten Schlüsselattributs für den Clustered Index stellt bereits die Weichen für die Performance und die Größe aller weiteren – non-clustered – Indexe. Neben einem geeigneten Datentypen spielt die Eindeutigkeit der Werte bei der Definition eines Clustered Index eine entscheidende Rolle, die über die Performance entscheiden kann. Der nachfolgende Artikel beschreibt im Detail, wie Microsoft SQL Server sicherstellt, dass Werte in einem Clustered Index eindeutig identifiziert werden können, obwohl der Index nicht “UNIQUE” ist.


Testumgebung

Für die Beispiele in diesem Artikel wird eine Tabelle mit Informationen zu Kunden und deren Kostenstellenzuordnungen verwendet. Die Tabelle hat folgenden Aufbau:

CREATE TABLE [dbo].[Companies]
(
    [Id]         int             NOT NULL IDENTITY(1,1),
    [Name]       nvarchar(128)   NOT NULL,
    [TaxNo]      varchar(24)     NOT NULL,
    [CostCenter] char(7)         NOT NULL,
    [UpdateBy]   varchar(20)     NOT NULL

)

Die Auswahl eines geeigneten Attributs für einen „clustered Index” muss man sorgfältig planen, da diese Entscheidung Auswirkungen auf das gesamte Layout der Datenstruktur hat. Der Clustered Index kann – wie ein non clustered Index auch – wahlweise als eindeutiger Index oder als Index mit redundanten Schlüsselwerten definiert werden.

Kardinalität der Schlüsselattribute

Die Kardinalität eines Attributs bestimmt, ob der Schlüssel eines Index als UNIQUE definiert werden kann. Für das erste Beispiel soll ein Clustered Index auf dem Attribut [Name] angelegt werden. Mit der folgenden Abfrage wird aus den Beispieldaten ermittelt, wie häufig der Firmenname in der Beispieltabelle [dbo].[Companies] verwendet wird:

SELECT TOP 10
       [Name]           AS CompanyName,
       COUNT_BIG(Id)    AS Kardinalität
FROM   [dbo].[Companies]
GROUP BY
       [Name]
ORDER BY
       COUNT_BIG(*) DESC;

Das Ergebnis der Abfrage zeigt, dass Firmennamen in der Beispieldatenbank redundant vorkommen.

RECORDSET_01Auf Grund der Redundanzen im Firmennamen kann für das Attribut [Name] kein eindeutiger Clustered Index angewendet werden.

Ein Clustered Index ist – wie bereits oben erwähnt – nicht dadurch in seiner Anwendung begrenzt, dass er nicht eindeutig sein kann. Vielmehr reguliert ein Clustered Index die logische Sortierung von Datensätzen in einer Tabelle; respektive er repräsentiert die Tabelle.

 

Clustered Index mit redundanten Schlüsselattributen

Um zu zeigen, wie Microsoft SQL Server redundante Schlüsselattribute in einem Index verwaltet, wird im ersten Beispiel das Attribut [Name] mit einem Clustered Index versehen. Auf Grund der redundanten Daten darf der Index nicht eindeutig sein.

CREATE CLUSTERED INDEX [cix_companies_name] ON [dbo].[Companies] ([Name]);

Obwohl der erstellte Clustered Index nicht eindeutig ist, muss Microsoft SQL Server sicherstellen, dass der Datensatz “eindeutig identifizierbar” in der Tabelle ist. Diese “Eindeutigkeit” ist um so wichtiger, als dass diese Eindeutigkeit als Referenz in jedem non-clustered Index der Tabelle gespeichert werden muss. Es stellt sich also die Frage, wie Microsoft SQL Server die Datensätze verwaltet, um die Eindeutigkeit eines Datensatzes zu gewährleisten. Hierzu muss man tiefer in die Database Engine von Microsoft SQL Server eindringen.

Microsoft SQL Server verwaltet Datensätze in Datenseiten. Eine Datenseite hat eine feste Größe von 8.192 Bytes und kann bis zu einer Datenmenge von 8.060 Bytes vollständig für die Speicherung von Datensätzen verwendet werden. Um einen Blick auf eine Datenseite zu werfen, muss der – nicht dokumentierte – Befehl DBCC PAGE verwendet werden. Zuvor wird mit Hilfe der Funktion [sys].[fn_PhysLocCracker] ermittelt, auf welchen Datenseiten die Datensätze der Tabelle [dbo].[Companies] gespeichert wurden:

SELECT  P.[file_id],
        P.[page_id],
        P.[slot_id],
        C.name
FROM    [dbo].[Companies] AS C
        CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS P;

RECORDSET_02

Die Abbildung zeigt die ersten Datensätze der Beispieltabelle. Die ersten drei Spalten der Ergebnismenge verweisen auf die jeweilige Position jedes einzelnen Datensatzes der Tabelle. Der erste Datensatz befindet sich in Datei 1 der Datenbank auf Datenseite 146 in Slot 0. Der Wert des Indexschlüssels kommt in den Beispieldaten nur einmal vor. Das nächste Unternehmen wird auf der gleichen Datenseite gespeichert, kommt aber mehrmals in den Beispieldaten vor.

Mit Hilfe des nachfolgenden Befehls kann der Inhalt und die Struktur der Datenseite 146 im Management Studio von Microsoft SQL Server ausgegeben werden:

DBCC TRACEON (3604);
DBCC PAGE (‚demo_db‘, 1, 146, 3) WITH TABLERESULTS;

Um detaillierte Informationen zu einer Datenseite zu erhalten, muss das Traceflag 3604 aktiviert werden. Durch die Aktivierung wird die Ausgabe von DBCC PAGE nicht in das Fehlerprotokoll von Microsoft SQL geleitet sondern clientseitig ausgegeben. Die Option TABLERESULTS wurde für die bessere Darstellung des nachfolgenden Ergebnisses gewählt!

DBCC_PAGE_01

Die Abbildung zeigt die Informationen des ersten Datensatzes, der auf der Seite gespeichert wurde. Man erkennt, dass Microsoft SQL Server neben den gespeicherten Daten eine weitere Spalte VOR den eigentlichen Daten verwaltet. Hierbei handelt es sich um das Systemattribut [UNIQUIFIER]. Hierbei handelt es sich um ein internes – ausschließlich für die Verwaltung der Eindeutigkeit genutztes – Attribut, dass nicht nach außen angezeigt wird.

Ein UNIQUIFIER wird von Microsoft SQL Server dann für einen Index verwendet, wenn der Index nicht als eindeutig definiert wurde. Mit Hilfe eines UNIQUIFIER gelingt Microsoft SQL Server die “interne” Eindeutigkeit eines Datensatzes. Ein genauer Blick auf die Abbildung zeigt, dass Microsoft SQL Server trotz “zusätzlicher” Informationen sehr sparsam mit dem zur Verfügung gestellten Platz einer Datenseite umgeht. Der jeweils ERSTE Datensatz eines Schlüsselattributs bekommt zwar den Wert 0 für den UNIQUIFIER zugeteilt; dieser Wert wird aber nicht physikalisch auf der Datenseite gespeichert. Die Speicherung eines UNIQUIFIER-Wertes benötigt den Datentypen INT. Somit müssen weitere 4 Bytes zum eigentlichen Datensatz hinzu addiert werden, die ebenfalls gespeichert werden müssen.

Werden Datensätze mit redundanten Indexschlüsseln gespeichert, wird ab dem zweiten Datensatz mit gleichem Indexschlüssel der UNIQUIFIER physikalisch auf der Datenseite gespeichert.

DBCC_PAGE_02

Die Abbildung zeigt zwei Kundendatensätze, deren Indexschlüssel ([Name]) redundant in der Tabelle vorkommt. Der erste Datensatz erhält den – internen – UNIQUIFIER-Wert 0 während der zweite Eintrag den Wert 1 erhält. Ist der erste Eintrag nur eine berechnete Anzeige muss für die Speicherung des zweiten UNIQUIFIER-Werts physikalisch Speicher auf der Datenseite reserviert werden. Der Datensatz ist insgesamt um 4 Bytes gewachsen! Diese zusätzlichen 4 Bytes für den UNIQUIFIER werden zwischen den Informationen über die Struktur der variablen Spalten einer Tabelle und den variablen Daten eines Datensatzes zusätzlich gespeichert. Obwohl es sich bei dem Datentypen INT um einen festen Datentypen handelt, wird er – in diesem Fall – wie ein variabler Datentyp behandelt.

DBCC_PAGE_03

Die Abbildung zeigt – rot markiert – das Offset für die Werte jeder einzelnen Spalte mit variabler Datenlänge (jeweils 2 Bytes). So beginnt der Name des Unternehmens bei Offset 28, die Steuernummer bei Offset 72, usw.). Die Abbildung zeigt keinen Eintrag für den UNIQUIFIER, da es sich bei dem Datensatz um den ersten Eintrag mit gleichem Wert im Indexschlüssel handelt.

DBCC_PAGE_04

Die zweite Abbildung zeigt den zweiten Datensatz mit identischem Schlüsselattribut. In diesem Fall muss Microsoft SQL Server den Wert für den UNIQUIFIER physikalisch speichern. Obwohl es sich um einen Datentypen mit fester Datenlänge handelt, wird er im Bereich der variablen Daten gespeichert. Durch die zusätzlichen 4 Bytes VOR den eigentlichen Daten des Datensatzes verschieben sich die Offsets. Der Name des Unternehmens beginnt nun bei Offset 32, die Steuernummer verschiebt sich ebenfalls um 4 Bytes und beginnt nun bei Offset 76 (4C)! Insgesamt wächst der Datensatz um 4 Bytes (siehe Record Size im Header).

Clustered Index mit eindeutigen Schlüsselattributen

Wenn die Schlüsselattribute eines Clustered Index eindeutig sind, benötigt Microsoft SQL Server keine zusätzlichen Informationen, um die – interne – Eindeutigkeit eines Datensatzes zu gewährleisten. In diesem Fall kann der Index selbst die Datenintegrität gewährleisten. Neben dem fehlenden Verwaltungsaufwand ändert sich auch die Länge/Größe eines Datensatzes nicht mehr.

Die in Beispiel 1 verwendete Tabelle wird im zweiten Beispiel mit einem eindeutigen Clustered Index auf dem Feld [Id] erstellt.

CREATE UNIQUE CLUSTERED INDEX [cix_companies_id] ON [dbo].[Companies](Id);

Nach der Erstellung des Index zeigt ein erneuter Blick auf die Datenseiten des Clustered Index, dass die vormals mit UNIQUIFIER gespeicherten Datensätze nun ohne diesen Zusatz auskommen.

DBCC_PAGE_05

Die Abbildung zeigt den ersten Beispieldatensatz mit der [Id] = 2 (blaue Kennzeichnung). Auffällig ist bei der Wahl eines eindeutigen Index, dass die Anzahl der Spalten von vormals 6 auf 5 Spalten reduziert wurde, die in der Tabelle selbst definiert wurden! Die grüne Markierung zeigt die Offsets der Spalten mit variablen Datentypen. Vergleicht man die Struktur des Datensatzes mit dem zweiten – identischen – Eintrag, erkennt man, dass (bis auf den Wert für [Id]) keine Änderungen in der Struktur des Datensatzes vorgenommen wurde.

DBCC_PAGE_06

Zusammenfassung

Nicht nur die Wahl der geeigneten Datentypen für einen Clustered Index entscheidet über mögliche Performanceeinbußen. Auch über die Eindeutigkeit eines Clustered Index sollte man sich bei der Erstellung Gedanken machen. Ist ein Clustered Index nicht eindeutig, muss Microsoft SQL Server selbst für die Eindeutigkeit sorgen und verwaltet diese Information mit Hilfe eines – internen – UNIQUIFIER-Attributs. Ein UNIQUIFIER belegt 4 Bytes zusätzlich im Datensatz und wird – trotz fester Datenlänge – wie ein variabler Datentyp behandelt.

Übrigens gilt die Verwendung eines UNIQUIFIER-Attributs ausschließlich für einen Clustered Index. Für non-clustered Indexe ist dieser Verwaltungsaufwand nicht erforderlich, da die Eindeutigkeit entweder durch den Clustered Key (+[UNIQUIFIER] oder die RID (Row Locator ID) eines Heap gewährleistet wird.

Herzlichen Dank fürs Lesen!

Referenzen

SQL Server Clustered Index http://msdn.microsoft.com/de-de/library/ms190639.aspx
Gruppierte Indexstrukturen http://msdn.microsoft.com/de-de/library/ms177443.aspx
Kardinalität (Datenbanken) http://de.wikipedia.org/wiki/Kardinalit%C3%A4t_(Datenbanken)
DBCC IND http://www.sascha-dittmann.de/post/Undokumentierter-SQL-Befehl-DBCC-IND.aspx
DBCC PAGE http://www.sascha-dittmann.de/post/Undokumentierter-SQL-Befehl-DBCC-PAGE.aspx
TRACEFLAG 3604 http://blogs.msdn.com/b/askjay/archive/2011/01/21/why-do-we-need-trace-flag-3604-for-dbcc-statements.aspx

Beispielskript

Für das obige Beispiel kann das Beispielskript aus dem Bereich [Downloads] heruntergeladen werden!

Sascha Dittmann: Hadoop on Linux mit Azure VMs (2) – Basis Image erstellen

Hadoop on Linux (Teil 2)Am Montag hatte ich bereits aufgezeigt, wie man ein Storage Account, den Cloud Service, sowie das Virtuelle Netzwerk für ein manuell erstelltes Hadoop on Linux Cluster anlegt.
In diesem Teil meiner dreiteiligen Serie stelle ich vor, wie man ein Basis-Image für die Cluster-Knoten erstellen kann…

Da das Ganze für einen einzelnen Blog Post etwas zu umfangreich ausfallen würde, habe ich diese Step-By-Step Anleitung in 3 Teile aufgesplittet:

  1. Vorbereitung der Azure Umgebung
    (Storage Account, Cloud Service und Virtuelles Netzwerk)
  2. Erstellen eines Basis-Images
  3. Erzeugen des Clusters

Virtuelle Maschine Erstellen

Virtuelle Machine erstellenDa ich natürlich nicht komplett bei Null anfangen möchte, nutze ich als Basis für unsere Hadoop on Linux Vorlage ein CentOS 6.x Image aus der Azure VM Gallery.
Für die neue Virtuelle Maschine wähle ich deshalb New -> Compute -> Virtual Machine -> From Gallery aus.

HDP Image - Basis auswählenIm ersten Schitt des Assistenten-Dialogs wählen ich dann entsprechend die aktuellste OpenLogic 6.x Vorlage aus.

HDP Image - VM DatenIm zweiten Schritt muss ich Angaben zur Virtuellen Maschine machen.
Hier verpasse ich der Maschine den Namen »hdp-image«, da diese ohnehin am Ende des Prozesses in ein Basis-Image umgewandelt wird.
Ausserdem wähle ich die VM-Größe »Standard_D2« aus.
Im Gegensatz zur A-Serie der Azure VMs, haben die Maschinen der D-Serie besonders viel Arbeitsspeicher und eine lokale SSD, die für das Temporäre Laufwerk genutzt wird.
Für das Administratorkonto muss ich noch einen Benutzernamen und Passwort angeben.
Ein Zertifikat für die Secure Shell (SSH) möchte ich nicht benutzen.

HDP Image - Infrastruktur auswählenIn Schritt 3 werden der Cloud Service, das Virtuellen Netzwerk, das Subnetz und das Storage Account ausgewählt, die ich im ersten Teil dieser Step-By-Step Anleitung erstellt hatte.

HDP Image - ExtentionsAls Erweiterung für Azure VMs, sollte noch der VM Agent installiert werden.
Dieser wird am Ende dieses Prozesses genutzt, um die VM für die Image-Erstellung vorzubereiten.

Aktualisieren des Betriebssystems

Damit die Virtuelle Maschine für unsere Zwecke angepasst werden kann, muss man sich als erstes eine Secure Shell (SSH) Verbindung herstellen.

SSH ist auf Unix-Betriebssystemen und OS X meist vorinstalliert.
Für Windows-Systeme kann das kostenlose Tool PuTTY genutzt werden.

PuTTY

Administrator SessionAnschließend sollte man die Session mit »sudo -s« dauerhaft in den Administrator-Modus heben, da man ansonsten vor jedem der folgenden Befehle separat »sudo« voranstellen müsste.

Update Linux (1)Um alle installierten Pakete des Betriebsystems auf den aktuellen Stand zu bringen, führe ich als erstes den Befehl »yum update« aus.

Update Linux (2)Nach dessen initialer Prüfung, muss der Download zuvor noch mit einem »y« bestätigt werden.

Passwortloses SSH

Damit später das Hadoop Installations- und Verwaltungssystem Ambari eine Administratorverbindung zu den einzelnen Knoten herstellen kann, muss vorher das Passwortlose SSH eingerichtet werden.

Hierzu muss zuerst ein RSA-Schlüsselpaar erstellt werden.
Anschließend wird dieses als Autorisiertes Schlüsselpaar für das Basis-Image (und dementsprechend für alle später erstellten Knoten) bekannt gemacht und die entsprechenden Dateisystemrechte gesetzt:

ssh-keygen
cat /root/.ssh/id_rsa.pub >> /root/.ssh/authorized_keys
chmod 700 ~/.ssh
chmod 600 ~/.ssh/*

SSH Keys erzeugen

Mit »ls -ld ~/.ssh & ls -l ~/.ssh« lassen sich die vorgenommenen Einstellungen nochmals prüfen.

Passwortloses SSH

Network Time Protocol (NTP)

Damit später auch alle Cluster-Knoten im "Gleichen Takt" ticken, muss der Network Time Protocol (NTP) Dienst installiert und aktiviert werden.
Geprüft werden kann die Installation mit »chkconfig –list ntpd«.

yum install ntp
service ntpd start
chkconfig ntpd on

NTP Dienst installieren

SELinux Deaktivieren

Da sich die Security-Enhanced Linux (kurz SELinux) Kernel-Erweiterung mit Ambari beißt, muss diese deaktiviert werden.
Hierzu führt man folgenden Befehl aus:

setenforce 0

SELinux abschalten (1)

Damit diese auch nach dem Neustart der VMs nicht wieder aktiv wird, muss man die entsprechende Konfigurationsdatei (/etc/selinux/config) angepasst werden.
Dabei muss »SELINUX« auf »disabled« gesetzt werden.

vi /etc/selinux/config

SELinux abschalten (2)

PackageKit Deaktivieren

Auch das PackageKit kann zu Problemen führen, weshalb dies deaktiviert werden sollte.

Hierzu muss »enabled=0« in der entsprechenden Konfigurationsdatei (/etc/yum/pluginconf.d/refresh-packagekit.conf) gesetzt werden.

vi /etc/yum/pluginconf.d/refresh-packagekit.conf

PackageKit abschalten

Da diese in der aktuellen Vorlage der Azure VM Gallery nicht vorhanden ist, kann auch folgender Befehl genutzt werden.

echo 'enabled=0' >> /etc/yum/pluginconf.d/refresh-packagekit.conf

Transparent Huge Pages (THP) Deaktivieren

Um etwaigen Performance-Problemen aus dem Weg zu gehen, sollten auch die Transparent Huge Pages (THP) abgeschaltet werden.

Dieser Mechanismus ist ähnlich der Windows-Auslagerungsdatei (pagefile.sys) und lagert Daten des virtuellen Speichers aus.

Hierzu muss man 2 kleinere Änderungen in der /etc/grub.conf vornehmen und bei den Kernel Aufrufen den Parameter »transparent_hugepage=never« anhängen. 

vi /etc/grub.conf

Transparent Huge Pages (THP) abschalten

Netzwerk Konfiguration

Bei der Netzwerk Konfiguration habe ich zwei Änderungen vorgenommen.

Zum einen hab ich die IP v6 Unterstützung aktiviert, indem ich bei der /etc/sysconfig/network Konfigurationsdatei »NETWORKING_IPV6« auf »yes« gesetzt habe.

vi /etc/sysconfig/network

oder

echo 'NETWORKING_IPV6=yes' >> /etc/sysconfig/network

IPv6 aktivieren

Außerdem habe ich die hosts-Datei bereits mit den entsprechenden Einträgen der zukünftigen Cluster-Knoten versehen.

Der in den »Azure Virtual Networks« integrierte DNS-Dienst verrichtet zwar hervorragend seinen Dienst, allerdings fehlt bei der Hadoop Installation ein »Fully Qualified Domain Name (FQDN)« für jeden Cluster-Knoten.
Deshalb habe ich die pragmatischen Schritt gewählt.

vi /etc/hosts

Hosts-DateiDie hierbei verwende Domain-Struktur ist komplett ausgedacht und setzt auf der Domain des Cloud-Service-Endpunkts auf.
Über »hostname -f« läßt sich der interne FQDN anzeigen, welchen ich auch mit in die hosts-Datei übernommen habe.

Zukünftige Domain-Struktur
IP FQDN HOSTNAME
10.10.0.4 hdp-head-01.hdp-demo.cloudapp.net hdp-head-01
10.10.1.4 hdp-data-01.hdp-demo.cloudapp.net hdp-data-01
10.10.1.5 hdp-data-02.hdp-demo.cloudapp.net hdp-data-02
10.10.1.6 hdp-data-03.hdp-demo.cloudapp.net hdp-data-03

 

Festplatte hinzufügen

Als letzten Punkt für das Basis-Image, füge ich noch eine zweite Festplatte hinzu, welche später als Datenplatte für das HDFS dienen soll.

Hierzu wechsel ich im Azure Management Portal in das Dashboard der Virtuellen Machine und wähle die Aktion »Attach empty disk« aus:

Neue VHD hinzufügen

Attach empty diskIm daraufhin erscheinenden Dialog wähle ich die entsprechende Plattengröße (in diesem Fall 100GB) aus und passe ggf. den Namen der VHD-Datei an.

Anschließend wechsel ich wieder zur SSH-Konsole zurück.
Dort partitioniere und formatiere ich die neue Festplatte, mounte diese als Verzeichnis /mnt/datadrive und passe die Konfigurationsdatei /etc/fstab entsprechend an, damit diese Veränderung auch nach einem Neustart zur Verfügung steht.

parted /dev/sdc mklabel msdos
parted /dev/sdc mkpart primary 1 100%
mkfs.ext4 /dev/sdc1
mkdir /mnt/datadrive
mount /dev/sdc1 /mnt/datadrive
echo '/dev/sdc1 /mnt/datadrive ext4 defaults 1 2' >> /etc/fstab

Neue VHD in Linux bereitstellen

 

VM in ein Image umwandeln

Nachdem die Virtuelle Maschine soweit vorbereitet wurde, kann diese jetzt in ein Image umgewandelt werden.

Dazu verwende ich als Erstes den VM Agent, um in Linux alles zu entfernen, was eine Dublizierbarkeit behindern könnte.

waagent -deprovision
shutdown -h 0

Deprovision Linux VM

Anschließend kann ich die VM im Azure Management Portal herunterfahren…

VM Herunterfahren

… und in ein Image umwandeln.

Image Erzeugen (1)

Image Erzeugen (2)In dem Capture-Dialog gebe ich dem Image einen Namen, wie beispielsweise »hdp-image«, und setzte das Häkchen, dass der Deprovision-Vorgang durchgeführt worden ist.

Wie geht’s weiter?

Nachdem jetzt das Basis-Image für die Cluster-Knoten erstellt wurde, wird im dritten Teil das eigentliche Hadoop on Linux-Cluster erzeugt.
 

Der Beitrag Hadoop on Linux mit Azure VMs (2) – Basis Image erstellen erschien zuerst auf Sascha Dittmann.

Sascha Dittmann: Hadoop on Linux mit Azure VMs (1) – Vorbereitungen

Hadoop on Linux (Teil 1)Vor kurzem benötigte ich ein Hadoop on Linux Test-Cluster, welches relativ nah an eine lokale Produktionsumgebung rankommen sollte. Somit fielen leider HDInsight (inkl. der Hadoop on Linux Preview), durch die Nutzung des Blob Storages aus.
Grund genug, um einmal aufzuzeigen, wie man ein Hadoop on Linux Cluster mit Azure VMs aufsetzen kann.

Da das Ganze für einen einzelnen Blog Post etwas zu umfangreich ausfallen würde, möchte ich dies in folgende 3 Teile aufsplitten:

  1. Vorbereitung der Azure Umgebung
    (Storage Account, Cloud Service und Virtuelles Netzwerk)
  2. Erstellen eines Basis-Images
  3. Erzeugen des Clusters

Storage Account & Cloud Service

Wie für die Virtuellen Maschinen in Azure üblich, benötigt man hierzu einen »Storage Account« für die VHD Dateien, sowie einen »Cloud Service« als Container und Endpunkt für die VMs.

Beides lässt sich zwar auch während des Erstellens der VMs erzeugen, allerdings finde ich zum Einen sprechende Namen für die jeweiligen Dienste sinnvoller und zum Anderen weiß man dann auch genau, was bzw. wofür etwas erstellt worden ist.

Beides lässt sich ganz einfach über das Azure Management Portal erstellen.

Azure Storage Account erstellenFür das Azure Storage Account geht man auf New -> Data Services -> Storage -> Quick Create und gibt dem Storage Account einen Namen.
Außerdem sollte man noch das gewünschte Rechenzentrum, sowie den Replikationstyp, auswählen.
Für mein Test-Cluster langt die Replikation innerhalb des ausgewählten Rechenzentrums völlig aus, weshalb ich „Locally Redundant“ ausgewählt habe.

Azure Cloud Service erstellenAuch der Cloud Service ist schnell erstellt.
Hierzu wählt man New -> Compute -> Cloud Service -> Quick Create aus und gibt auch hier Name und das gleiche Rechenzentrum an.

Virtuelles Netzwerk

Nachdem man nun das Storage Account und die Cloud Service erstellt hat, kommt der etwas komplexere Teil an die Reihe – das Virtuelle Netzwerk.

Ich möchte innerhalb meines Virtuellen Netzwerks zwei Subnetze haben (eines für die Head-Nodes und eines für die Data-Nodes meines Hadoop Clusters), um erneut möglichst nah an die spätere Produktivumgebung heranzukommen.

Für etwaige Applikationen, die dieses Hadoop-Cluster benutzen, würde ich dann später ein weiteres Subnetz hinzufügen.
Soweit zum Plan – Kommen wir also zur Umsetzung…

Virtuelles Netzwerk erstellenFür das neue Netzwerk wähle ich New -> Network Services -> Virtual Network -> Custom Create aus.

Virtuelles Netzwerk - Name und RechenzentrumIm ersten Schritt des Assistenten-Dialogs gebe ich meinem neuen Netzwerk einen Namen und wähle erneut das gewünschte Rechenzentrum aus.

Virtuelles Netzwerk - DNS und VPNDen zweiten Schritt kann ich überspringen, da ich weder einen eigenen DNS Server angeben möchte, noch einen VPN Tunnel benötige.

Der von Azure bereitgestellte DNS Dienst würde zwar vollkommen ausreichen, allerdings werde ich ohnehin für mein kleines Cluster die hosts-Datei entsprechend anpassen.

Virtuelles Netzwerk - Adressräume und SubnetzeIm letzten Schritte gebe ich einen Adressraum (10.10.0.0/16), sowie die zwei Subnetze (Master-Subnet 10.10.0.0/24 und Worker-Subnet 10.10.1.0/24) an.

Wie geht’s weiter?

Im kommenden Teil erstelle ich ein Basis-Image für unsere Hadoop on Linux Cluster-Knoten.
 

Der Beitrag Hadoop on Linux mit Azure VMs (1) – Vorbereitungen erschien zuerst auf Sascha Dittmann.

Christoph Muthmann: Verbessertes Handling der tempdb beim Setup

Andreas Wolter hat ja bereits gestern über die Neuigkeiten beim SQL Server 2016 berichtet. Ein Teil davon bezieht sich auf die erweiterten Möglichkeiten beim Setup bereits die Konfiguration der tempdb zu beeinflussen. Aber das ist nicht genug.

Ganze Geschichte »

Andreas Wolter: SQL Server 2016 – the Security & Performance Release / ein Sicherheits- und Performance-Release

Christoph Muthmann: Bye-bye, Heidi

Seit gestern ist es endlich vorbei. Zumindest für den Rest des Jahres bleiben wir dann von Germany’s next Topmodel (GNTM) verschont.

Ganze Geschichte »

Andre Essing: Erste Public Preview von SQL Server 2016 erschienen

Auf der Microsoft Ignite in Chicago wurde es ja bereits angekündigt, die erste öffentliche Preview des kommenden SQL Server 2016 sollte noch diesen Sommer veröffentlicht werden. Das der Sommer so schnell kommt, damit hat glaube ich keiner gerechnet, denn gestern veröffentlichte Microsoft die Customer Technology Preview 2 (CTP2). Den offizielle Beitrag dazu findet Ihr im […]

Dieser Beitrag wurde auf dem Blog von Andre Essing veröffentlicht.

Andre Essing: PASS Regionalgruppe Bayern – SQL Server Usergroup Treffen am 11.06.2015 um 18:30 bei Microsoft in Unterschleißheim

Liebe PASS'ler und SQL Server Enthusiasten in Bayern,

auf unserem nächsten Usergroup Treffen im Juni werde ich selbst, als kleines Warmup und für alle die nicht beim SQL Saturday Rheinland dabei sein können, einen Vortrag halten. Ich freue mich schon darauf, euch am Donnerstag, den 11.06.2015 bei der Microsoft Deutschland in Unterschleißheim begrüßen zu dürfen. Wir starten wie immer um 18:30 Uhr.

Dieser Beitrag wurde auf dem Blog von Andre Essing veröffentlicht.

Andreas Wolter: My conference-sessions in 2015: from Extended Events over In-Memory to Security

Andre Essing: Ein Überblick über die SQL Server Monitoring Lösung von Redgate

Die Überwachung von Servern ist eine wichtige Aufgabe, die leider allzu häufig vernachlässigt wird. Da bilden die Administratoren von SQL Servern auch keine Ausnahme. Dies gleicht einer Schifffahrt bei starkem Nebel auf hoher See. Ihr wisst nicht was als nächstes passiert und ob der Eisberg schon näher kommt. Um dem Thema Monitoring ein wenig den Mythos der Komplexität […]

Dieser Beitrag wurde auf dem Blog von Andre Essing veröffentlicht.

Christoph Muthmann: Mehrfache Leerzeichen einfach entfernen

Kurz vor dem langen Wochenende nur ein kleines Skript mit einem tollen Trick, wie man mehrfache Leerzeichen in einem String einfach entfernen kann.

Ganze Geschichte »

Andre Essing: Microsoft veröffentlicht Cumulutive Updates und Service Pack für SQL Server 2012 und 2014

Microsoft war fleißig und in den letzten Tagen und hat gleich 3 Updatepakete für den SQL Server veröffentlicht. Angefangen mit der erneuten Veröffentlichung von Service Pack 1 für SQL Server 2014, über das letzte Cumulative Update für den SQL Server 2012 Service Pack 1 (SQL Server SP1 CU16), bis hin zum Cumulative Update 6 für SQL […]

Dieser Beitrag wurde auf dem Blog von Andre Essing veröffentlicht.

Andreas Wolter: SQL Server in Microsoft Azure: How to gain performance by flexibility and save costs at the same time

Marcel Franke: PASS Business Analytics Conference 2015 – Day 2

Good morning. So we here go with the second day of the PASS BA Conference. After a really good first day todays keynote started with a Lady I have never heard before. Her name is Mico Yuk, CEO of BI Brainz and she is a great lady. And although it was very hard to have a keynote after Day 1 keynote of Carlo Ratti, who showed us more the visionary future of Analytics, she really made it very well and grounded the people with the day to day challenges.

WP_20150422_08_08_02_Pro

Keynote – Visual Storyboarding

Her talk was about Visual Storyboarding and there where plenty other talks about that subject today.  So let me try to get that story first for you. She started with the general disappointments companies have with Business Intelligence (BI) projects. We hear that a lot these days, normally addressed by some software vendors mostly out of the Hadoop world, but she made it very clear. Let’s start with 2 facts she presented:

  • $97 Billion annual BI spend by 2016
  • But the average Business Intelligence user adoption rate is only 21%

P1030409

Lot’s of BI users still seem to use a lot of “export to Excel” functionality and do the Analytics work on their own. That must make us think. So why is the user adoption rate of BI and Data Warehouse projects so low? It reminds me of a quote of John Naisbitt:

“We are drowning in Information but starved for knowledge”.

But what are the reasons for? BI projects in general are still to technical driven by the IT and we still have lot’s of silos in organizations, like a BI Team, Analysts, Data Science Team, etc. But these teams have to work together, because a lot of Data Scientists spent a long time at school and are smart, but with not much experience in the field and not enough business understanding. That is a gap that business departments and analysts can fill. In the world of BI the main focus is about explaining the things that happened (the past). A lot of Data Scientists can ask question on things, nobody thought before and can give you insides in your data and your business people never noticed before. So again, these teams have to work together and we can’t give it just to the IT.

As Mico said a lot of BI projects stop on just presenting numbers, but as a decision maker your are more focused on actions. So the last step of creating the right actions out of my numbers & trends is often the missing part. But it is also not that easy to achieve, because in order to get it right, you first need the trust of the users and this has a lot to do with communication. So what has this to do with Visual Storyboarding?

Visual Storytelling is the common language that can break silos and bring people together to talk in the same language (http://visual.ly/science-storytelling) and focus on the important things. Mico presented some nice samples on slide 20 that you also find here:

Something that I also recognized in the keynote of day 1 was that a lot of great visualizations are still hand made by graphic designers and developers. So there is still a larger gap in the ease of use of tools and the quality of presenting data. This has also to do with a new kind of user experience that people also expect in business application that is more driven by consumer products and apps. 

Summary

For me it was great to had the chance to visit the PASS Business Analytics conference this year. California in general is a great place for that kind of conference. As already said it is a different kind of community. I learned a lot of new interesting thoughts on data analytics and get to know new interesting people. For me I take 3 main things away from this conference:

  • Data Analytics is still hard work. You have to leverage so many skills, also non-technical skills, to get it really right.
  • Visualization and Storytelling is something we will see more in the future and is for me definitely a way to go to close the last mile to the user.
  • I see many people catching up with R. R is more and more used in the community and also in business departments, so I think Microsoft made a big step in the right direction by acquiring Revolution Analytics.

Sources


Filed under: Uncategorized

Marcel Franke: PASS Business Analytics Conference 2015 – Day 1

Pic1This is my first time to visit the PASS Business Analytics Conference and so I want to take the chance to give a wrap up of my impressions of the first day. The conference takes place in Santa Clara and I personally think that California as a great place to be. The sunny weather makes it very easy to combine interesting and challenging sessions and an open mood of people for networking and get to know each other. I really like the very friendly and open nature of the people here.

Maybe I give you some more details on the conference itself. The clear focus of that conference is Data Analytics. It’s a very good mixture of technical, business and strategic sessions. The audience is very different to the typical and more technical PASS Conference in Seattle. Altogether there are 600 participants out of 24 countries and more than 60 hours of sessions with great speakers. Most of them I never heard before, but they really give you some new ideas or food for thought that you never be thought of before.

Pic2

Keynote

The keynote started with the PASS president Thomas LaRock, followed by a great talk of Carlo Ratti, the Director of the MIT Senseable City Lab. The MIT Senseable City Lab is focused on who sensors are changing cities into real-time cities with lot’s of new opportunities. Today cities are already covered with digital sensors, so that people can react on it. There is an estimation that by 2020  about 50 billion connections need to handled in the world. Most of these connections will be sensors and devices than mobile phones. He showed several examples how cities are covered with digital sensors, react on it and become more smart cities these days. There are several research projects that the MIT is doing in that area like:

Mobility and Smart Cities

The Hub Cab is a project started on analyze Taxi Peaks in New York and try to bring in shared mobility. Beside the MIT also Audi and GE are part of that project, which makes me, as a German, also proud of that we are part of that innovation.  He presented some interesting numbers that although safe driving cars are on the way only 20% of the cars are need today for our mobility. An average car is only used 5% of the time so there is a huge opportunity to make mobility more efficient, especially in large city, also in order enhance the quality of live. So sharing is much more intelligent and what if we take the self-driving experience and the the sharing concepts together and even think about how traffic is organized. Do we still then need traffic lights? There are some nice concepts and researches on self-driving intersection that organizes traffic much more intelligent and efficient as traffic lights did for the last 100 years. This is a big opportunity for all of us.

The Copenhagen wheel

Another interesting and fascinating thing that came out of a MIT research project is the Copenhagen Wheel. This is a complete new way of bicycle mobility and a great example how sensors and technology can help us to innovate new experiences. And the best way to understand it, is to watch the video: https://www.youtube.com/watch?v=S10GMfG2NMY


Follow the trash

One last interesting project to me was the “moma-followup”. The idea is to follow obsolete electronics, that is normally just thrown away, across the world for recycle or reuse. In order to do this the MIT developed two different types of self-reporting technologies that are coupled with the obsolete electronics and than see what happened: https://www.youtube.com/watch?v=5fHZYyOZU4c


Summary

For me the first day really gave me some new ideas what is going on in Data Analytics. I think it is an amazing time to see, what can be achieved with technologies in these days and this is not always just to earn more money. It is interesting to see how fast the technology adoption happens and what role these great Universities like the MIT, Boston, Harvard, Stanford and many others play in that area. So a really great day one and I will follow up on day 2.

Sources:


Filed under: Big Data, Conferences, Data Analytics, Data Science

Andreas Wolter: Received 2nd MVP Award for SQL Server - Zum 2. Mal ausgezeichnet mit dem MVP-Award für SQL Server

Philipp Lenz: Excel 2013 and the Flash Fill Function (ETL for Beginners ;-) )

Off topic but really good to combine with PowerPivot or PowerQuery: The flash fill function.
With this feature in Excel 2013 you can automatically extract text into columns. It works better than the text in columns feature or Left, Right or Find functions in Excel or PowerPivot for me.

Example:

This column contains surname, middle names and last names into one column. First you must convert this data into a table:

10-04-_2015_15-01-42

Next add a column “surname” – please do not add the other columns. Sometimes Excel is confused by the other columns…
Then type in the new column the first surname “Franz”. In the next row start typing the next surname “Karl”. Excel writes automatically the other names into the cells. In this case you can also use the Flash Fill Function in the Ribbon “Data” – but this function will not work by the middle names.

10-04-_2015_15-04-55

 

 

 

 

 

 

 

Now add a new column middle name and type in the first data cell the first middle name “Dieter”. If your data have no double name in the first rows, sometimes you must type much more names if Excel use the Flash Fill Function.

10-04-_2015_15-05-40

 

 

 

 

 

 

 

 

In the last row you can see the surname in the middle name column. If you have middle names or data with more text parts it is sometimes not perfect! The last step is to add the last name column. The data has in the first 2 rows some different data – a single last name and double name. So you must type 2 rows till the Flash Fill Function takes effect:

10-04-_2015_15-06-08

Now you can add this table into your PowerPivot or PowerView model. Enjoy this feature!

More information: http://blogs.office.com/2012/08/09/flash-fill/

SQLPodcast: #011 – T-SQL on Steroids

In dieser Folge habe ich mich mit Frank Geisler allgemein zum Thema T-SQL Entwicklung und den möglichen Entwicklungsumgebungen unterhalten.

Philipp Lenz: off topic: Office 365 / Office 2015 Click & Install

office365logoorange_webBeim installieren von Office 365 wie auch der Preview von Office 2015 habe ich immense Probleme gehabt. Einerseits konnte ich teilweise das Setup nicht starten, da ich laut Frontend zu wenig Speicherplatz oder keine Interverbindung hätte, oder das Setup brach mittendrin ab, mit der Meldung das eine Datei nicht gefunden werden konnte. Die Logdateien gaben hierzu ebenso wenig Hinweise. Das Problem tritt bei mir mit einem frischen Windows 8.1 Rechner sowie mit einem Windows 10 Preview (March Update) auf. Nachdem ich nun den Windows 10 Rechner neu aufgesetzt habe und die Windows Firewall sowie den Windows Defender deaktiviert habe, läuft nun die Installation. Nebenbei bemerkt, ist auch meine Internetverbindung nicht die schnellste und stabilste.

Update: Das Problem löste sich nur temporär auf. Nach weiterer Recherche liegt es wohl an meinem Speedport W 723 V Typ A. Die Installation wurde weiterhin unterbrochen. Nachdem das Problem mit weiteren Geräten im Netz nachvollziehbar war, und ein Tethering mir zeigte, dass es nur auf Rechnern auftritt die in diesem Netz sind, habe ich kurzerhand den Router gegen eine aktuelle Fritz! Box ausgetauscht – sieht da, es funktioniert. Wenn man den Speedport als Modem nur verwendet und die interne Firewall somit aus dem Spiel nimmt, funktioniert es auch. Das ändern der DNS Adressen hat auch nichts gebracht – Grausam!

Uwe Ricken: GUID vs INT/IDENTITY als Clustered Key

Immer wieder hört oder liest man, dass ein Clustered Index möglichst fortlaufend/aufsteigend organisiert sein soll. Am besten sei immer ein Clustered Index mit möglichst kleinen Datentypen (z. B. INT); außerdem sollte ein Clustered Index nach Möglichkeit nicht aus zusammengesetzten Attributen bestehen. Die mit Abstand größte Abneigung besteht bei vielen Entwicklern gegen den Einsatz von GUID als Clustered Keys. Die generelle “Verteufelung” von GUID ist nicht gerechtfertigt – GUID sind in einigen Workloadmustern performanter als die “Heilige Kuh” IDENTITY (1, 1).

Die Vorbehalte vieler SQL Experten gegen GUID kann man z. B. in den kontroversen Foreneinträgen zu dem Artikel “^Are there that many GUIDs?” von Steve Jones auf sqlservercentral.com nachlesen.
Der nachfolgende Artikel soll zeigen, dass der schlechte Ruf, der einer GUID vorausgeht, nicht immer gerechtfertigt ist sondern seiner Nutzung immer eine sorgfältige Betrachtung des Workloads vorausgehen sollte. Pro und Contra GUID soll der Artikel etwas näher beleuchten.

Testumgebung

Für die nachfolgenden Demonstrationen und Diskussionen wird eine Datenbank mit einer Initialgröße von 1 GB erstellt. In dieser Datenbank befinden sich zwei Tabellen mit identischer Datensatzlänge sowie eine Stored Procedure, die im Beispiel von jeweils 200 Benutzern gleichzeitig ausgeführt werden wird.

CREATE DATABASE [demo_db]
ON  PRIMARY
(
    NAME = N'demo_db',
    FILENAME = N'F:\DATA\demo_db.mdf',
    SIZE = 1000MB,
    MAXSIZE = 20000MB,
    FILEGROWTH = 1000MB
)
LOG ON
(
    NAME = N'demo_log',
    FILENAME = N'F:\DATA\demo_db.ldf',
    SIZE = 500MB,
    MAXSIZE = 1000MB,
    FILEGROWTH = 100MB
);
GO

ALTER AUTHORIZATION ON DATABASE::demo_db TO sa;
ALTER DATABASE [demo_db] SET RECOVERY SIMPLE;
GO

Die Initialgröße von 1 GB sorgt bei den Tests dafür, dass keine Abweichungen bei der Messung entstehen, weil die Datenbank auf Grund der hohen Datenmenge wachsen muss. Die Größe von 500 MB für die Protokolldatei ist ausreichend, da die Datenbank im Modus “SIMPLE” betrieben wird.

USE demo_db;
GO

-- Tabelle mit fortlaufender Nummerierung als Clustered Index
CREATE TABLE dbo.numeric_table
(
    Id    INT          NOT NULL    IDENTITY(1, 1),
    c1    CHAR(400)    NOT NULL    DEFAULT ('just a filler'),

    CONSTRAINT pk_numeric_table PRIMARY KEY CLUSTERED (Id)
);
GO

-- Tabelle mit zufälligem Clustered Index
CREATE TABLE dbo.guid_table
(
    Id    uniqueidentifier  NOT NULL    DEFAULT(NEWID()),
    c1    CHAR(388)         NOT NULL    DEFAULT ('just a filler'),

    CONSTRAINT pk_guid_table PRIMARY KEY CLUSTERED (Id)
);
GO

Beide Tabellen haben eine identische Datensatzlänge um Abweichungen bei den Messungen zu verhindern, weil unterschiedliche Datensatzlängen dazu führen können, dass mehr oder weniger Datensätze auf eine Datenseite passen. Jeder Datensatz hat eine Länge von 411 Bytes. Da ein [uniqueidentifier] eine Länge von 16 Bytes besitzt, muss lediglich der Füllbereich um 12 Bytes reduziert werden.

-- Procedure for insertion of 1,000 records in dedicated table
CREATE PROC dbo.proc_insert_data
    @type varchar(10)
AS
    SET NOCOUNT ON

    DECLARE    @i INT = 1;

    IF @type = 'numeric'
    BEGIN
        WHILE @i <= 1000
        BEGIN
            INSERT INTO dbo.numeric_table DEFAULT VALUES
            SET @i += 1;
        END
    END
    ELSE
    BEGIN
        WHILE @i <= 1000
        BEGIN
            INSERT INTO dbo.guid_table DEFAULT VALUES
            SET @i += 1;
        END
    END

    SET NOCOUNT OFF;
GO

Die Prozedur hat eine triviale Aufgabe; sie soll – abhängig vom übergebenen Parameter @type – jeweils 1.000 Datensätze pro Aufruf in eine der beiden Tabellen eintragen. Wird der Prozedur der Parameter @type mit dem Wert ‚numeric‘ übergeben, trägt die Prozedur entsprechende Daten in die Tabelle mit fortlaufendem Clustered Key ein während bei einem anderen Parameterwert Daten in die Tabelle mit zufälligem Clustered Key eingetragen werden.
Vor jeder Ausführung der Prozedur werden die Statistiken in sys.dm_os_wait_stats gelöscht. Dadurch sollen die während der Laufzeit registrierten Wartevorgänge analysiert werden:
DBCC SQLPERF(‚sys.dm_os_wait_stats‘, ‚CLEAR‘);
GO
Während die Prozedur ausgeführt wird, wird in einer zweiten Sitzung in SQL Server Management Studio die folgende Abfrage wiederholt ausgeführt:

SELECT  DOWT.session_id,
        DOWT.wait_duration_ms,
        DOWT.wait_type,
        DOWT.resource_description,
        DOWT.blocking_session_id
FROM    sys.dm_exec_sessions AS DES INNER JOIN sys.dm_os_waiting_tasks AS DOWT
        ON (DES.session_id = DOWT.session_id)
WHERE   DES.is_user_process = 1;
GO

Um einen Workload mit mehreren Clients zu simulieren, wird das Tool “ostress.exe” verwendet. Dieses – von Microsoft kostenlos angebotene Tool – ermöglicht die Simulation von Workloads mit mehreren Clients und ist – insbesondere für PoC-Simulationen – sehr zu empfehlen.

Daten in Tabelle mit fortlaufendem Clustered Key

Zunächst wird die Prozedur für die Ausführung in die Tabelle [dbo].[numeric_table] ausgeführt:

OSTRESS_NUMERIC_START

Mit einer vertrauten Verbindung (-E) wird auf den Testserver zugegriffen und die Prozedur [dbo].[proc_insert_data] (-Q) in der Datenbank [demo_db] (-d) für 200 Clients (-n) ausgeführt. Während der Ausführung dieser Prozedur wird mittels sys.dm_os_waiting_tasks überprüft, auf welche Ressourcen Microsoft SQL Server während der Clientzugriffe warten muss. Insgesamt benötigt das Eintragen von 200.000 Datensätzen ~12,500 Sekunden.

OSTRESS_NUMERIC_DURATION

Diese Zeitspanne mag für Datenbanken mit geringem Transaktionsvolumen ausreichend sein; jedoch gibt es Systeme, die noch deutlich mehr Datensätze in kürzerer Zeit verarbeiten müssen. Warum also dauert dieser Prozess so lange?

WAITING_TASKS_NUMERIC_INSERT

Die obige Abbildung zeigt die wartenden Prozesse während der Ausführung der Prozedur. Es ist deutlich zu erkennen, dass die Mehrzahl der Prozesse auf einen PAGELATCH_xx warten müssen.

PAGELATCH_XX

Als “Latch” bezeichnet man interne, nicht konfigurierbare Sperren, die von Microsoft SQL Server benötigt werden, um bei gleichzeitigem Zugriff (Konkurrierend) die im Speicher befindlichen Strukturen zu schützen. Latches werden häufig mit Locks verwechselt, da ihre Zwecke gleich gelagert aber nicht identisch sind. Ein Latch bezeichnet ein Objekt, das die Integrität der Daten anderer Objekte im Speicher von SQL Server gewährleistet. Sie sind ein logisches Konstrukt, das für einen kontrollierten Zugriff auf eine Ressource sorgt. Im Gegensatz zu Locks sind Latches ein interner SQL Server-Mechanismus. Man kann sie grob in zwei Klassen aufteilen – Buffer Latches und Non-Buffer Latches. Für den in diesem Artikel beschriebenen Fall handelt es sich um Sperren auf Datenseiten, die sich bereits im Buffer Pool befinden. Wer sich intensiver mit Latches beschäftigen möchte, dem sei das Dokument “Diagnosing and Resolving Latch Contention on SQL Server” von Ewan Fairweather und Mike Ruthruff für das weitere Studium empfohlen. Es mag zwar schon älter sein aber sein Inhalt ist auch für die neueren Versionen von Microsoft SQL Server noch relevant!

WAITING_TASKS_NUMERIC_INSERT_AGG

Auffällig ist, dass alle Prozesse auf die gleiche Ressource warten müssen. Die Wartevorgänge variieren zwischen SH (Shared) und EX (Exclusive) Ressourcen. Diese Wartevorgänge sind der – in diesem Fall sehr schlechten – Eigenschaft des Clustered Index geschuldet. Da die Daten in sortierter Reihenfolge in den Index eingetragen werden müssen, werden alle neuen Datensätze immer am Ende des Index eingetragen. Der Clustered Index repräsentiert die Tabelle selbst und definiert die Sortierung nach einem vorher festgelegten Schlüsselattribut. Wenn dieses Attribut mit einem fortlaufenden Wert befüllt wird, reduziert sich der Zugriffspunkt für Microsoft SQL Server immer auf die letzte Datenseite des Clustered Index im Leaf-Level.

CLUSTERED_INDEX_NUMERIC

Die Abbildung zeigt den – logischen – Aufbau eines Clustered Index. Aus Darstellungsgründen wurde auf die Beziehung der Datenseiten untereinander verzichtet. Alle Daten werden im Leaf-Level gespeichert. Da das Schlüsselattribut als fortlaufend definiert wurde, müssen ALLE Prozesse auf der letzten Seite des Index ihre Daten eintragen. Wenn – wie in diesem Beispiel gezeigt – mehrere Prozesse gleichzeitig eine INSERT-Operation durchführen, hat das den gleichen Effekt wie das Anstehen an einer Kasse im Supermarkt; man muss warten! Aber ACHTUNG: Das beschriebene Szenario gilt nicht für Workloads mit mehreren Verbindungen!

Fortlaufenden Index-Pages im Clustered Index

Sehr häufig verbindet man mit fortlaufender Nummerierung im Indexschlüssel die Vermeidung von Page Splits. Diese Aussage hat aber nur Bestand, wenn das Eintragen von neuen Datensätzen durch einen einzigen Thread geschieht. Wird jedoch – wie im aktuellen Beispiel – der Einfügevorgang von 200 Benutzern gleichzeitig gestartet, kann eine sequentielle Folge in den Eintragungen nicht mehr gewährleistet werden. Ein Blick in das Transaktionsprotokoll zeigt die Details:

SELECT  [Current LSN],
        Operation,
        Context,
        AllocUnitName,
        [Page ID],
        [Slot ID],
        [RowLog Contents 0]
FROM    sys.fn_dblog(NULL, NULL) AS FD
WHERE   AllocUnitName = N'dbo.numeric_table.pk_numeric_table'
ORDER BY
        [Current LSN];

Die nachfolgende Abbildung zeigt einen Extrakt aus dem Transaktionsprotokoll, der die Einfügevorgänge in chronologischer Reihenfolge ausgibt.

FN_DBLOG_01

Hervorzuheben ist die [SLOT ID]! Aus der Slot Id ist erkennbar, dass die Datensätze nicht hintereinander eingetragen wurden, sondern nach dem 5 Eintrag (Zeile 48) ein neuer Datensatz in Slot 1 eingetragen worden ist. Das lässt nur den Rückschluss zu, dass ein kleinerer Indexwert erst später eingetragen wurde. Mit dem nachfolgenden SELECT-Statement werden die eingetragenen Indexschlüssel “entschlüsselt”:

SELECT CAST(0x14 AS INT)    AS    [Slot_0],
       CAST(0x21 AS INT)    AS    [Slot 1],
       CAST(0x22 AS INT)    AS    [Slot 2],
       CAST(0x23 AS INT)    AS    [Slot 3],
       CAST(0x24 AS INT)    AS    [Slot 4],
       CAST(0x20 AS INT)    AS    [Slot w],
       CAST(0x1F AS INT)    AS    [Slot x],
       CAST(0x15 AS INT)    AS    [Slot y],
       CAST(0x1D AS INT)    AS    [Slot z];
GO

Auf eine vollumfängliche Beschreibung der Strukturen eines Datensatzes kann in diesem Artikel nicht eingegangen werden! Kurz und Knapp gilt für die Berechnung des eingetragenen Wertes für die ID in der Tabelle [dbo].[numeric_table] jedoch folgende Richtlinie:

  • Die ersten vier Bytes in [RowLog Contents 0] sind für den Rowheader reserviert und müssen extrahiert werden
  • Die nächsten 4 Bytes entsprechen dem Wert, der in das Attribut [ID] (INTEGER) eingetragen wird
  • Diese vier Bytes werden “geshifted” und übrig bleibt der Hexadezimalwert für den eingetragenen Dezimalwert
  • Die Berechnung des Dezimalwertes erfolgt mittels CAST-Funktion

Das Ergebnis der obigen Abfrage sieht dann wie folgt aus:

SLOT_ARRAY_01

Die ID-Werte sind in der chronologischen Reihenfolge ihres Einfügeprozesses dargestellt. Zunächst wurden die Werte 20, 33, 34, 35, 36 in die Tabelle eingetragen. Das entspricht exakt dem Auszug aus dem Transaktionsprotokoll (siehe [Slot ID]. Mit der sechsten Transaktion wurde ein neuer Datensatz mit dem Wert 32 in die Tabelle eingetragen. Der Clustered Key muss sortiert werden; also wird der Wert 32 in Slot 1 eingetragen. Anschließend wird der Wert 31 eingetragen und der Kreis schließt sich erneut!
Schaut man sich die Verteilung der eingetragenen Datensätze nach chronologischem Muster an, wird schnell klar, dass Page Splits zwangsläufig auftreten müssen. Der Grund für diese gemischte Verteilung ist schnell erkennbar, wenn man versteht, wie Microsoft SQL Server den Wert für IDENTITY ermittelt. Dieses Thema habe ich bereits ausführlich im Artikel “IDENTITY-Werte…–warum wird der Wert um <increment> erhöht, obwohl die Transaktion nicht beendet werden kann?” beschrieben.
Werden nur von einem Benutzer Daten in eine Tabelle eingetragen, wird der Indexschlüssel um jeweils 1 erhöht und der Datensatz wird eingetragen. Wir jedoch – wie im vorliegenden Beispiel – ein System mit hoher Concurrency betrieben, ist das nicht mehr gewährleistet. Die nachfolgende Abbildung zeigt den internen Sachverhalt:

IDENTITY_ASSIGNMENT_PROCESS

Jeder Thread fordert – sobald er CPU-Zeit erhält – einen IDENTITY-Wert aus dem Pool an. Dieser Prozess ist nicht mehr umkehrbar; einmal einen Wert erhalten, wird dieser Wert von diesem Prozess für den ganzen Einfügezyklus beibehalten! Nachdem der IDENTITY-Wert vergeben wurde, kann der Thread mit diesem erhaltenen Wert den Einfügevorgang beenden. Dazu muss sich jeder Thread (mit dem ihm zugeteilten IDENTITY-Wert in eine Reihe mit anderen Threads anstellen, die ebenfalls auf die Ressource (Datenseite) warten müssen. Hierbei kann es zu Verschiebungen kommen; der Thread mit dem Wert 4 muss warten, weil die Threads mit den Werten 7 und 11 noch vor ihm ausgeführt werden müssen. Wird die Seite vollständig gefüllt, wenn der Thread mit Wert 11 seine Daten gespeichert hat, wird es unweigerlich für den Thread mit Wert 4 zu einem Page Split kommen.

Clustered Index mit zufälligem Schlüssel

Mit dem nachfolgenden Test werden ebenfalls 200.000 Datensätze in die Tabelle [dbo].[guid_table] eingetragen und erneut die Zeiten gemessen.

OSTRESS_GUID_START

Während der Ausführung der Prozedur werden die wartenden Tasks überprüft und eine abschließende Abfrage über die Wartevorgänge ausgeführt. Insgesamt wird der Einfügevorgang in etwa 50% der Zeit ausgeführt. Das  Eintragen in die Tabelle mit fortlaufendem Clustered Key hat: 6,833 Sekunden betragen!

OSTRESS_GUID_DURATION

Während der Ausführung wurden die Wartevorgänge überprüft und das Ergebnis unterscheidet sich deutlich vom vorherigen Prozess.

WAITING_TASKS_GUID_INSERT_AGG

Während beim Eintragen von fortlaufenden Indexwerten PAGELATCH-Wartevorgänge das Bild dominiert haben, ist beim Eintragen von zufälligen Schlüsselwerten das Schreiben in die Protokolldatei der dominierende Wartevorgang – und das zu Recht!

CLUSTERED_INDEX_GUID

Wie die Abbildung zeigt, konzentriert sich der Prozess nicht mehr an das Ende des Indexes sondern verteilt sich gleichmäßig auf alle Datenseiten im Leaf-Level. Somit können mehrere Schreibvorgänge parallel durchgeführt werden. Dieser Umstand wird in den Wartevorgängen abgebildet – fast alle Prozesse warten darauf, dass die Transaktionen in die Protokolldatei geschrieben werden.

numeric_vs_guid

Die Abbildung zeigt einen Mitschnitt im Microsoft WIndows Performance Monitor während der Ausführung der Prozedur für die Tabelle [dbo].[numeric_table] und für die Tabelle [dbo].[guid_table]. Während für das Einfügen von Datensätzen in die Tabelle mit aufsteigendem Indexschlüssel Pagelatches zu beobachten sind, ist das Eintragen der gleichen Datenmenge in die Tabelle mit zufälligem Indexschlüssel nur eine kurze “Episode” da kaum zu messende Pagelatches auftreten.

Vor- und Nachteile der verschiedenen Varianten

Jede der oben vorgestellten Varianten hat seine Vor- und Nachteile. Die Geschwindigkeit beim Eintragen von Daten, die man sich mit einer GUID-Variante erkauft, muss unter Umständen mit teuren Table Scans und mit erhöhtem Aufwand für Index Maintenance bezahlt werden.

Fragmentierung

Wie bereits weiter oben dargestellt, können Page Splits auch bei IDENTITY-Werten für einen Clustered Key vorkommen. Dieser Umstand führt dazu, dass trotz fortlaufendem numerischen Clustered Key die logische Fragmentierung auf den Datenseiten unverhältnismäßig hoch sein kann.

SELECT  OBJECT_NAME(object_id),
        index_type_desc,
        fragment_count,
        page_count,
        record_count,
        avg_fragmentation_in_percent,
        avg_page_space_used_in_percent
FROM    sys.dm_db_index_physical_stats
(
    DB_ID(),
    OBJECT_ID('dbo.numeric_table', 'U'),
    1,
    NULL,
    'DETAILED'
) AS DDIPS;
GO

DM_DB_INDEX_PHYSICAL_STATS_01

Obwohl eine fortlaufende Nummerierung gewählt wurde, sind ca. 88% logische Fragmentierung zu verzeichnen. Insgesamt ist eine Datenseite zu ~65% gefüllt (Page Density). Das bedeutet für die Auslastung des Buffer Pools ca. 5.250 KB statt 8.060 Bytes.

DM_DB_INDEX_PHYSICAL_STATS_02

Ein direkter Vergleich zwischen [dbo].[numeric_table] und [dbo].[guid_table] zeigt, dass es keinen nennenswerten Unterschied im Fragmentierungsgrad gibt. Beide Tabellen sind zwar hochgradig fragmentiert aber es gibt auf den ersten Blick keinen Vorteil, der für die numerische fortlaufende Variante spricht. Jedoch besitzt der fortlaufenden Clustered Keys den Vorteil, dass nach einem INDEX-REBUILD dieser Bereich des Indexes nicht mehr fragmentiert werden kann, da neue Datensätze immer an das Ende des Indexes geschrieben werden! Bei einer GUID besteht auch bei neuen Datensätzen noch das Risiko, dass der Index innerhalb von bereits bestehenden Daten erneut durch Page Splits fragmentiert wird!
Sind gleichzeitige Workloads das bestimmende Bild in Microsoft SQL Server muss sowohl bei fortlaufenden Indexschlüssel als auch bei variablen Indexschlüsseln mit Fragmentierung gerechnet werden. Grundsätzlich kann man aber behaupten, dass bei deutlich weniger Concurrency sowie nach dem Neuaufbau eines Index ein Vorteil für den fortlaufenden Indexschlüssel besteht!

Größere NONCLUSTERED Indexe

Jeder NONCLUSTERED Index muss den Clustered Key eines Clustered Index zusätzlich abspeichern, damit der Index einen Verweis zu den Daten der Datenzeile hat, die nicht im NONCLUSTERED Index hinterlegt sind. Tatsächlich ergibt sich dadurch mathematisch ein Nachteil für die Verwendung einer GUID als Clustered Index. Eine GUID hat eine Datenlänge von 16 Bytes während der Datentyp INT lediglich 4 Bytes an Speichervolumen konsumiert.

STORAGE_VOLUMINA

Das Ergebnis sollte nicht weiter überraschen. Es versteht sich von selbst, dass bei Verwendung eines größeren Schlüssels das Datenvolumen entsprechend wächst. Hier liegt der Vorteil eindeutig bei einem Clustered Key mit einem kleinen Datentypen (4 Bytes vs. 16 Bytes).

Ascending Key Problem

Das Problem von aufsteigenden Schlüsselattributen im Index habe ich im Artikel “Aufsteigende Indexschlüssel – Performancekiller” sehr detailliert beschrieben. Dieses Problem tritt IMMER auf, wenn ein Schlüsselattribut verwendet wird, dass beständig größere Werte in einen Index einträgt. Da die Statistiken eines Index nicht bei jedem Eintrag neu erstellt werden, kann es vorkommen, dass bei veralteten Statistiken ein schlechter Ausführungsplan generiert wird. Selbstverständlich kann auch bei einer GUID ein solches Problem auftreten, wenn die neue GUID tatsächlich am Ende des Index erstellt wird. Mit zunehmender Datenmenge wird dieses Problem jedoch immer unwahrscheinlicher! Hier liegt ein Vorteil in der Verwendung einer GUID!
Dieser Vorteil ist jedoch für einen CLUSTERED INDEX mit fortlaufender Nummerierung eher vernachlässigbar da ein Wert immer nur ein Mal als Schlüsselattribut vorkommt. Somit ist das Problem “Ascending Key” hier eher eine unbedeutende Randerscheinung!

Replikation

Manche Replikationsszenarien (z. B. MERGE) verlangen ein Attribut in jeder Tabelle, die repliziert werden soll, dass von Datentyp [uniqueidentifier] ist und die Eigenschaft ROWGUIDCOL besitzt. Plant man das Datenbanksystem in einem Replikationsumfeld, bietet sich die GUID als Clustered Key an. Will man auf IDENTITY / INT als Clustered Key nicht verzichten, muss jeder Tabelle explizit ein neues Attribut hinzugefügt werden. Das nachfolgende Codebeispiel zeigt für beide Tabellen die jeweiligen “Änderungen/Anpassungen”:

-- table with contigious numbers as clustered key
CREATE TABLE dbo.numeric_table
(
    Id        INT                 NOT NULL    IDENTITY(1, 1),
    c1        CHAR(400)           NOT NULL    DEFAULT ('just a filler'),
    rowguid   UNIQUEIDENTIFIER    NOT NULL    ROWGUIDCOL,

    CONSTRAINT pk_numeric_table PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT uq_numeric_table UNIQUE (ROWGUID)
);
GO

-- table with random guid as clustered key
CREATE TABLE dbo.guid_table
(
    Id    UNIQUEIDENTIFIER    NOT NULL    ROWGUIDCOL    DEFAULT(NEWID()),
    c1    CHAR(388)           NOT NULL    DEFAULT ('just a filler'),

    CONSTRAINT pk_guid_table PRIMARY KEY CLUSTERED (Id)
);
GO

Das Skript zeigt beide Tabellen aus den vorherigen Beispielen, wie sie für eine MERGE oder PEER-TO-PEER Replikation vorbereitet sein müssen. Während es für die Tabelle [dbo].[guid_table] lediglich der zusätzlichen Eigenschaft “ROWGUIDCOL” für den Clustered Key bedarf, ist der Aufwand (und auch die Länge eines Datensatzes) in einer Tabelle mit einem INT deutlich höher. Zunächst muss ein weiteres Attribut vom Datentypen UNIQUEIDENTIFIER angelegt werden, dass ebenfalls die Eigenschaft “ROWGUIDCOL” besitzt. Für eine bessere Performance wird mittels eines UNIQUE CONSTRAINTS ein weiterer Index hinzugefügt, der die Eindeutigkeit sicherstellt.
Hier geht der Punkt eindeutig an die GUID als Clustered Key, da sich die Datenstruktur / Metadaten nicht verändern. Während die Länge eines Datensatzes in der Tabelle [dbo].[numeric_table] um 16 Bytes erweitert werden muss, bleibt die Datensatzlänge in der Tabelle [dbo].[guid_table] unverändert bei 411 Bytes. Ebenfalls wird kein weiterer Index für die Durchsetzung der Eindeutigkeit benötigt!

Zusammenfassung

Die generelle Ablehnung von GUID als Schlüsselattribut in einem Clustered Index wird zu häufig mit pauschalen / schon mal gehörten / Behauptungen untermauert. Sie ist ungerechtfertigt, wenn man nicht den Workload berücksichtigt, der einem DML-Prozess zu Grunde liegt.
Die Verwendung von GUID macht aus der Sicht des Autors dann Sinn, wenn sehr viele Daten von sehr vielen Prozessen eingetragen werden. Handelt es sich eher um ein System, mit dem sehr wenig Benutzer arbeiten oder aber das nur “gelegentlich” neue Datensätze in der Datenbank speichert, so sollte auch weiterhin mit dem favorisierten IDENTITY / INT gearbeitet werden.
GUID sind ideal für parallele ETL-Workloads in Stagingtabellen wenn man unbedingt einen Clustered Index verwenden möchte. Grundsätzlich sollten ETL-Workloads nur in HEAPS ihre Daten speichern. Alle anderen Varianten sind aus Sicht von Durchsatz und Zeit eher eine Bremse im sonst so performanten Ladeprozess! Aber das ist ein ganz anderes Thema, auf das ich im nächsten Artikel detaillierter eingehen werde.

Herzlichen Dank fürs Lesen!

Robert Panther: Neuer Datentyp für SQL Server vNext

Wieder einmal sind aus gut informierten Kreisen ein paar Details zu einem möglichen neuen Feature für die nächste Version von SQL Server bekannt geworden. Während allgemeine Trends wie bessere Performance, höhere Verfügbarkeit und bessere Cloud Integration sicherlich weiter fortgesetzt werden, ist dieses Feature für die meisten eher überraschend:

Neuer Datentyp: FUZZYBIT

Während der altbekannte Datentyp BIT nur eine zwei- (TRUE/FALSE) bzw. dreiwertige (TRUE/FALSE/NULL) Logik abbildet, sind mit dem neuen Datentyp FUZZYBIT auch Zwischenwerte möglich. Der Wert NULL erlaubt es ja heute bereits, einen unbekannten Wert zu repräsentieren. Mit dem FUZZYBIT kommen nun noch die Zwischenstufen LIKELY (wahrscheinlich) und UNLIKELY (unwahrscheinlich) dazu, so dass sich daraus eine sogenannte fünfwertige Logik ergibt. Um das Wording durchgehender zu gestalten, kann man anstelle von NULL auch das neue Schlüsselwort UNKNOWN verwenden, so dass sich damit insgesamt folgende Abstufungen ergeben:

TRUE – LIKELY – UNKNOWN – UNLIKELY – FALSE

Mit diesem neuen Datentyp wird es künftig einfacher möglich sein, Erkenntnisse aus unstrukturierten Big Data Datenquellen in einem relationalen Data Warehouse abzulegen.

Der Name FUZZYBIT leitet sich aus dem Begriff “Fuzzy Logic” ab, der ebenfalls für eine stärkere Differenzierung der zweifachen Booleschen Logik steht. Offen ist bisher noch, ob das Prinzip der Fuzzy Logic auch auf andere Datentypen ausgeweitet wird.


Andreas Wolter: How to import Extended Events session file target and parse deadlock-graph / Import von Extended Events Ereignisdateien und Parsen von Deadlock-Graphen

Andreas Wolter: Die SQL PASS Deutschland sucht Sprecher – Aufruf an alle SQL Server Fachleute

SQLPodcast: #010 – Microsoft PDW & APS

Im Gespräch mit Markus Raatz rund um das Thema Microsoft PDW & APS

Sascha Lorenz: Data Vault in der SQL Server Data Warehouse Welt

Meine Session auf der SQL Server Konferenz 2015 in Darmstadt zeigte mir, dass das Thema Data Vault durchaus polarisiert. Und da ich bei immer mehr Kundensituationen von uns auf Interesse zum Thema Data Vault treffe bzw. auch schon erste Architekturen vorfinde, möchte ich hier mal wieder ein paar Worte zu meiner Sicht der Lage äußern.

Meinem Verständnis nach brachte die Diskussion der letzten Monate um die Vor- bzw. Nachteile des Einsatzes der Data Vault Modellierung einen notwendigen frischen Wind in die Data Warehouse Welt. Und zwar jenseits des allgemeinen Big Data Hypes.

Data Vault kann durchaus als eine besondere Form des Stagings verstanden werden, wobei ich DV eher als einen Ansatz für die Modellierung eines Core Data Warehouses verstanden habe. Des Weiteren stellt Data Vault die rohen, unveränderten Daten in den Vordergrund. Die Idee dahinter ist, dass es keine einheitliche Wahrheit in einem Data Warehouse geben kann. Diese “Wahrheit” ist häufig durch eine Interpretation geprägt. Gerade die Suche nach der vermeintlich vorhanden Wahrheit in den Daten lässt viele Data Warehouse und Business Intelligence Projekte scheitern oder zumindest in Schönheit sterben.

Inwieweit passt nun die Data Vault Modellierung (& Nutzung) in die Microsoft Welt?

Das klassische Data Warehouse basiert auf relationalen Datenbanken. Die Bewirtschaftung mittels ETL- oder ELT-Strecke erfolgt meist mit einem Mix von SSIS und T-SQL Komponenten. Im Übrigen liegen häufig gerade in der Konzeptionierung der Strecke viele zukünftige (und zum Teil versteckte und damit nicht wahrgenommene) technische Schulden.

Eine bekannte Herausforderung bei DWH Projekten ist, dass sich diese häufig nicht wirklich eignen für agile Projektstrukturen. Auf der anderen Seite sterben viele DWH Projekte gerade wegen den sonst häufig starren Steuerungsstrukturen einen grausamen und meist auch teuren Tod. Es gibt mittlerweile eine Reihe von valide Ansätze den agilen Ansatz in die Data Warehouse Welt zu tragen, dennoch empfinde ich viele Ansätze, welche ich so vorfinde, als eher einen agil geprägten Projektverlauf, sprich völliges Chaos.

Data Vault kann tatsächlich mehr Agilität in DWH Projekte bringen. Ein Grundsatz im DV ist, dass es nur zusätzliche Objekte geben kann und keine Änderungen an bestehenden Strukturen durchgeführt werden. Durch diese und andere klare Regeln ist die Modellierung eines Data Vault erstaunlich stringent und passt sich dennoch dem unruhigen Fahrwasser im Datengeschäft an. 

Wichtig ist es dennoch nochmals hervorzuheben, dass ein Data Vault eben gerade nicht die traditionellen Data Warehouse Modellierungsmethoden ablösen will oder sollte. Meinem Verständnis nach ist es eine sinnvolle Ergänzung einer DWH Architektur. Primär wird sie von mir genutzt, um Quellen zu harmonisieren und ihre Strukturen homogen zu gestalten. Das vereinfacht die weitere Nutzung dieser Daten in der DWH Strecke ungemein. Gerade im Kontext Verarbeitung von Daten in Echtzeit kann diese Investition helfen.

Braucht nun wirkliches jedes (neue) Data Warehouse ein Data Vault? Eher nicht. Es kommt wie so häufig mal wieder drauf an.

Für mich ist der Grad an Stabilität eines geplanten Data Warehouses entscheidend. Inwiefern werden die Quellen als auch die Anforderungen stabil bleiben oder ist mit diversen Erweiterungen und Änderungen zu rechnen? Sofern diese Frage nicht beantwortet werden können oder die Antwort nicht belastbar genug ist, empfehle ich den Einsatz eines Data Vaults, um ein wenig Ruhe in die Strukturen zu bekommen.

In Abgrenzung zu klassischen DWH Modellierungen liefert das Data Vault Konzept auch Muster für die Bewirtschaftung mit. Unter anderem ist die Wiederholbarkeit von Bewirtschaftungsjobs möglich ohne sich mit der Herausforderung von möglichen Dubletten beschäftigen zu müssen. Wer sich schon länger mit DWH Umgebungen beschäftigt, weiß diese Eigenschaft durchaus schätzen zu lernen. 

Wenn wir kurz noch gedanklich in der SQL Server Welt bleiben, stellen sich natürlich Fragen, wie denn nun ein Data Vault zum Beispiel mittels der Analysis Services genutzt werden können. Diese erwarten ja einen klassischen Stern als Quelle. Meiner Erfahrung nach nutzen hier viele Anwender zurzeit die Option den Zugriff direkt mittels Views zu realisieren. Mit einer solchen Architektur bin ich nicht immer einverstanden, da ich gerade bei größeren Cubes hier durchaus Probleme bei der Verarbeitung erlebt habe. Häufig wird das Persistieren des Data Mart für einen Würfel als zu umständlich und zeitraubend angesehen. Es gilt noch belastbare Konzepte durch Lab Arbeit und Projekt Erfahrung zu sammeln, um diese Frage zufriedenstellend beantworten zu können.

Don't contact us via this (fleischfalle@alphasierrapapa.com) email address.