SQL Server 2016: Was ist neu? – Teil 2: Database Engine Features

Nach der Aktivierung des Query Stores wird im Hintergrund ein Prozess angestoßen, um die Abfragestatistiken zu sammeln. Die Einstellungen, die in Abb.1 zu sehen sind, erlauben eine Feinsteuerung des Sammelprozesses, um mehr oder weniger Granularität bei der Datensammlung zu ermöglichen. Zusätzlich dazu ist es möglich, einzustellen, wie viele Statistikdaten insgesamt gesammelt und wie lange diese Daten vorgehalten werden sollen. Die Standardwerte sind für den ersten Einsatz ausreichend, sollten aber je nach System überarbeitet werden.

Nach nur wenigen Minuten, selbst auf einem "ruhigen" System, kann man schon Daten im Query Store finden. Diese Daten können entweder per T-SQL über eine Reihe an Dynamic Management Objects[1] oder über die vier in SSMS mitgelieferten Berichte abgerufen werden (Abb.3).

Mit den mitgelieferten Berichten kriegt man einen schnellen Überblick über die "schlimmsten" Abfragen, die vom Query Store aufgenommen wurden. Mit "Regressed Queries" sieht man, welche Abfragen sich über die Zeit verschlechtert haben (langsamer geworden sind bzw. mehr Ressourcen in Anspruch nehmen). Overall Resource Consumption zeigt den Ressourcenverbrauch der einzelnen Abfragen. Top Resource Consuming Queries zeigt die Abfragen an, die die meisten Ressourcen benutzen (hier kann man nach unterschiedlichen Ressource-Typen filtern). Tracked Queries zeigt Abfragen an, die von einem DBA gezielt mitprotokolliert werden sollen.

Eine weitere, ganz neue Möglichkeit, die Query Store-Daten anzuzeigen, kommt aus der SQL Server Community. Enrico van der Laar hat das "Query Store Dashboard" im November 2016 veröffentlicht, um die Query Store-Daten etwas schlichter darzustellen [2]. Das Dashboard muss – aufgrund der Einschränkungen in SSMS Custom Reports – auf etwas Interaktivität verzichten, zeigt aber sehr viele Daten in einem gut überschaubaren Dashboard.

Da Enrico alles auf GitHub und als Open Source veröffentlicht hat, kann man davon ausgehen, dass es in nächster Zeit Erweiterungen bzw. Verbesserungen geben wird. Eine besondere Stärke des Query Stores ist, dass man damit ein Datenbankupgrade vorbereiten kann. Man nimmt eine Datenbank von einer früheren Version des SQL Servers und bindet sie in eine SQL Server 2016-Instanz ein. Von dort lässt man die Datenbank erst einmal in dem "alten" Kompatibilitätsmodus weiterlaufen. Als nächstes aktiviert man den Query Store und lässt Testabfragen bzw. Testläufe gegen die Datenbank laufen. Dadurch werden die Abfragen und deren Statistiken und Ausführungspläne im Query Store gespeichert. Anschließend ändert man den Kompatibilitätsmodus auf 2016 und führt die Tests erneut durch. Der Query Store zeichnet sowohl die Abfragen- als auch die Planänderungen auf und zeigt zudem Regressed Queries an (die Abfragen, die nun durch das Upgrade "schlechter" laufen). So kann man im Nachhinein sehen, ob Abfragen vor einem Upgrade geändert oder anderweitig angepasst werden müssen.

Der Query Store erlaubt einem DBA, eine bessere Übersicht über die Datenbanken und Datenbankabfragen zu erhalten. Diese Informationslücke war früher nur durch teure Überwachungstools oder aufwändige Selbstversuche zu realisieren. Wir müssen uns nicht mehr vor unangenehmen Fragen zu Performanceproblemen verstecken, wenn wir den Query Store in SQL 2016 einsetzen. Die gute Nachricht dabei: Query Store ist sowohl in der teuren Enterprise Edition als auch in der Standard Edition nutzbar! Das ist ein riesiges Geschenk von Microsoft und erlaubt es DBAs, in allen Preislagen ihre Arbeit richtig zu machen.

Das nächste Feature, über das ich berichten möchte, ist eher für die Entwickler unter uns bestimmt. Wer wissen möchte, wie die Daten in einer Abfrage tatsächlich durchfließen, musste sich bisher einen Ausführungsplan anschauen und die einzelnen Knoten sowie die Pfeile dazwischen irgendwie als Film im Kopf abspielen lassen. Dieses "Kopfkino" klappt bei manchen etwas leichter als bei anderen.

Mit Live Query Statistics (LQS) ist es von nun an mittels eines Mausklicks möglich, die tatsächlichen Datenbewegungen, die während einer Abfrage prozessiert werden, live darzustellen!

Nicht selten soll für bestimmte Geschäftsprozesse eine Art "Audit" aufgebaut werden. Oft wird eine lückenlose Änderungshistorie erwünscht, um einen genauen Ablauf eines Prozesses nachzuvollziehen. In der Vergangenheit musste man zu Triggern greifen, wenn man diese Datenänderungen dauerhaft festhalten wollte. Mit Temporal Tables soll diese Arbeit einfacher gemacht werden.

Das gesamte Konzept der Temporal Tables ist es, die Einrichtung, Verwaltung und Abfragen von Tabellen inklusive Archivdaten so einfach wie möglich zu gestalten. Zusätzlich dazu sollte die Lösung genauso flexibel sein, wie es die heutigen Entwicklungsprozesse sind.
Fangen wir mit einer Tabelle und der Einrichtung eines Temporal Tables an. Beispiele werden mit der neuen Beispieldatenbank "Wide World Importers" von Microsoft geliefert [3].

Die Tabelle Sales.Customers ist bereits als Temporal Table angelegt. Die Tabelle wird im SQL Server Management Studio (SSMS) besonders mit einer Uhr und einer Zusatzbeschreibung dargestellt (Abb.7).

Wie in Abb.7 zu sehen, wird zusätzlich die Archivtabelle direkt mit der Tabelle in SSMS dargestellt. Diese Tabelle ist physisch von der "normalen" Tabelle getrennt, ist aber durch die Temporal-Table-Logik logisch mit der Grundtabelle verbunden. Beide Tabellen können einzeln abgefragt werden, dazu reicht eine einfache Select-Abfrage. Die "Magie" bei den Temporal Tables liegt darin, den zeitlichen Abgleich von Daten in einer einfachen Abfrage zu ermöglichen.

Lassen wir diese Tabelle von SSMS als Skript ausgeben, sehen wir ein gewöhnliches CREATE-TABLE-Skript, allerdings mit ein paar Erweiterungen. Zum einen müssen zwei Spalten zu der Tabelle hinzugefügt werden, um die Gültigkeit der Datensätze festzuhalten. Bei der Sales.Customers-Tabelle wurden diese Spalten wie folgt definiert:

[ValidFrom] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
[ValidTo] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,

Wir sehen, dass es zwei Spalten vom Datentyp datetime2 sind. Dieser Datentyp erlaubt eine sehr genaue Aufnahme von Zeitstempeln bis zu einer Genauigkeit von Nanosekunden. Zum anderen sehen wir die Schlüsselwörter GENERATED ALWAYS AS ROW START/END. Diese Eigenschaft definiert, dass diese Spalten jeweils einen Zeitstempel speichern sollen, und zwar wenn ein Datensatz erstellt (ValidFrom) oder geändert wurde (ValidTo). Ebenfalls zu notieren ist die Tatsache, dass beide Spalten mit NOT NULL erstellt werden. Der Anfang der Gültigkeit kann klar erkannt werden, nämlich dann, wenn der Datensatz erstellt wurde. Was aber, wenn der Datensatz immer noch gilt? Man könnte annehmen, die ValidTo-Spalte wäre in diesem Fall NULL. Da jedoch die Spalte mit NOT NULL erstellt wurde, muss hier ein Wert stehen. Hier wird das Datum 9999-12-31 23:59:59.9999999 eingetragen, um den Datensatz als "noch gültig" zu markieren.

Nun fehlt noch die Definition der Archivtabelle:

WITH
    ( SYSTEM_VERSIONING = ON 
        (
         HISTORY_TABLE = [Sales].[Customers_Archive], 
         DATA_CONSISTENCY_CHECK = ON
        )
    );

Hiermit geben wir an, wie die Archivtabelle heißen soll. Die Eigenschaft DATA_CONSISTENCY_CHECK gibt an, ob SQL Server davor schützen soll, dass die Archivdaten sich überlappen.

Bei unserer Beispieltabelle können wir herausfinden, wie ein Kunde vor einem Jahr aussah, ohne dass wir aufwändig nach Datensätzen in den beiden Tabellen suchen müssen. Wir fragen die Grundtabelle ab und geben dabei an, dass wir uns den Stand von vor einem Jahr ansehen wollen.

DECLARE @ValidDate DATE = '2015-07-01';
SELECT CustomerId, CustomerName, CreditLimit, ValidFrom, ValidTo
FROM Sales.Customers
    FOR SYSTEM_TIME AS OF @ValidDate
WHERE CustomerID = 978;

Diese Abfrage lädt der Kunde mit CustomerId 978, allerdings mit dem Datenstand vom 02.07.2015. Diese Daten sind nicht in der Sales.Customers-Tabelle zu finden, sondern in der Archivtabelle. Wie diese Archivtabelle heißt oder aussieht, kann uns egal sein, denn darum kümmert sich der SQL-Server.

In Abb.8 sehen wir, dass Kunde 978 am 01.07.2015 ein CreditLimit von 3300 hatte. Allerdings sehen wir auch, dass dieser Datensatz um 16:00 Uhr seiner Gültigkeit verloren hat.