Über unsMediaKontaktImpressum
William Durkin 31. Januar 2017

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

Die Database Engine ist das Kernstück eines Datenbanksystems. © sk_design / Fotolia.com
© sk_design / Fotolia.com

In dieser Folge der Neuerungen bei SQL Server 2016 werden wir uns auf die Database Engine konzentrieren. Die Database Engine ist das Kernstück eines Datenbanksystems. Jeder Zugriff auf Daten wird durch die Engine verarbeitet, weswegen Änderungen sowie Optimierungen meistens eine große Auswirkung auf Entwickler, Datenbankadministratoren und Benutzer haben. Microsoft hat mit SQL Server 2016 einige Neuerungen in die Database Engine eingebaut, die das Leben eines Datenbankadministrators erleichtern. Wie im ersten Teil dieser Serie erwähnt, sind viele Änderungen durch die Cloud-Lösung "Azure" entstanden; zumeist, um die interne Administration bei Microsoft zu vereinfachen.

Query Store

Jede Datenbankabfrage besteht aus mehreren Teilen (unter anderem): Die Abfrage an sich, die Datenbankobjekte, die durch die Abfrage angesprochen werden, der Ausführungsplan bzw. die Ausführungspläne, die Ausführungsstatistiken.

Leider sind die letzten beiden Teile, Ausführungspläne und Ausführungsstatistiken, von Natur aus "flüchtig". Wenn der SQL Server neu gestartet wird, gehen uns diese Informationen verloren. Allerdings sind genau diese Informationen extrem wichtig, um Performanceprobleme zu erkennen und zu bekämpfen. Ohne diese Informationen tappt man im Dunkeln. Es gibt jedoch Drittanbieterlösungen, um diese Informationen dauerhaft zu sammeln und auszuwerten (z. B. SQLSentry von SentryOne), doch leider sind solche Drittanbieterlösungen nicht immer einsetzbar. Entweder sie sind den Firmen/Kunden zu teuer oder der Nutzen wird erst erkannt, wenn es zu spät ist. So musste man sich bisher mit selbstgestrickten Lösungen behelfen bzw. – wie ich sehr oft erleben muss – ohne diese Informationen arbeiten.

Genau diese Situation hat Microsoft selbst gestört. Wie in dem alten Spruch "Der Schuster trägt die schlechtesten Schuhe", trug bisher Microsoft als "Schuster" in Hinblick auf SQL-Abfrageüberwachung wirklich die "schlechtesten Schuhe". Nun sah Microsoft aber die Notwendigkeit, sich durch die Azure-Platform um Abfrageperformance sowie Abfragestatistiküberwachung zu kümmern. Daraus ist unter anderem der Query Store entstanden.

Es ist mit SQL Server 2016 jetzt möglich, die Sammlung von Abfragestatistiken in einer Datenbank mit einem einzigen Click in SSMS (Abb.1) oder T-SQL-Befehl (Abb.2) zu aktivieren.

Abb.1: Query Store-Einstellungen in SSMS. © William Durkin
Abb.1: Query Store-Einstellungen in SSMS. © William Durkin
Abb.2: Query Store mit T-SQL aktivieren. © William Durkin
Abb.2: Query Store mit T-SQL aktivieren. © William Durkin
Abb.3: Query Store Reports. © William Durkin
Abb.3: Query Store Reports. © William Durkin

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.

Abb.4: Query Store Dashboard. © William Durkin
Abb.4: Query Store Dashboard. © William Durkin

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.

Live Query Statistics

Abb.5: Live Query Statitics in SSMS einschalten. © William Durkin
Abb.5: Live Query Statitics in SSMS einschalten. © William Durkin

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!

Mit LQS werden die Datenbewegungen der Abfrage im graphischen Ausführungsplan-Fenster so dargestellt, dass man die einzelnen Pfeile und Knoten sehen kann, und der Datenfluss zwischen diesen Knoten deutlich gemacht wird (Abb.6).

Abb.6: Live Query Statistics in SSMS darstellen. © William Durkin
Abb.6: Live Query Statistics in SSMS darstellen. © William Durkin

Die LQS Anzeige ist in Abb.6 zu sehen. Links unten sieht man, zu wieviel Prozent die Abfrage verarbeitet worden ist. Die gestrichelten Linien zeigen den Datenfluss an und bewegen sich in die Richtung des Datenflusses. Jeder Knoten zeigt an, zu wieviel Prozent seine Verarbeitung abgeschlossen ist und wie viele Datensätze verarbeitet worden sind. Mit LQS kann man sehr einfach erkennen, wie die Daten fließen und besser verstehen, wie eine Abfrage verarbeitet wird.

Allerdings ist die Aufnahme des Abfrageverlaufs mit einem erhöhten Ressourcenbedarf verbunden. Deshalb sollte LQS lieber nur auf einer Entwicklungsumgebung eingesetzt werden, anstatt auf einem Produktivsystem. LQS ist eher dazu geeignet, die Verbesserung des Abfragedesigns als die Problemanalyse auf einem Produktivsystem zu unterstützen.

Temporal Tables

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].

Abb.7: Tabelle Sales.Customers. © William Durkin
Abb.7: Tabelle Sales.Customers. © William Durkin

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.

Abb.8: Temporal Tables Beispieldatensatz. © William Durkin
Abb.8: Temporal Tables Beispieldatensatz. © William Durkin

Ändern wir die Abfrage leicht um, damit wir sehen können, welche Werte der Kunde in einem Zeitraum vom 01.01.2015 bis 31.12.2016 hatte, sehen wir die Historie und die dazugehören Änderungen aufgelistet.

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = '2016-12-31';;
SELECT CustomerId, CustomerName, CreditLimit, ValidFrom, ValidTo
FROM Sales.Customers
    FOR SYSTEM_TIME BETWEEN @StartDate AND @EndDate
WHERE CustomerID = 978
ORDER BY ValidFrom;
Abb.9: Temporal Table mit Historie. © William Durkin
Abb.9: Temporal Table mit Historie. © William Durkin

In Abb.9 sehen wir, dass am 01.07.2015 um 16:00 Uhr der CreditLimit von 3300 auf 3465 erhöht wurde. Dabei wurde der nun ungültige Datensatz in die Archivtabelle kopiert und ValidTo zu dem Zeitpunkt aktualisiert. Der neuen Datensatz mit CreditLimit 3465 wurde in die Grundtabelle mit einer Gültigkeit von 9999-12-31 23:59:59.9999999 gespeichert.

Das nächste, sehr starke Feature bei Temporal Tables gegenüber der herkömmlichen Trigger-Lösung ist die automatische DDL-Anpassung von der Archivtabelle, wenn die Grundtabelle geändert wird. Wird ein ALTER TABLE-Befehl ausgeführt, so wird die Änderung an die Archivtabelle automatisch weitergereicht.

Folgender Befehl fügt eine neue Spalte zu der Grundtabelle hinzu.

ALTER TABLE Sales.Customers ADD TestCol INT NULL

Anschließend rufen wir 0 Datensätze aus der Grundtabelle und der Archivtabelle ab.

SELECT TOP 0
    C.CustomerID,
    C.CustomerName,
    C.CreditLimit,
    C.ValidFrom,
    C.ValidTo,
    C.TestCol
FROM Sales.Customers AS C;

SELECT TOP 0
    CA.CustomerID,
    CA.CustomerName,
    CA.CreditLimit,
    CA.ValidFrom,
    CA.ValidTo,
    CA.TestCol
FROM Sales.Customers_Archive AS CA;

Nun sehen wir, dass die neue Spalte in beiden Tabellen existiert, obwohl nur die Grundtabelle geändert wurde.

Abb.10: Neue Spalte in beiden Tabellen. © William Durkin
Abb.10: Neue Spalte in beiden Tabellen. © William Durkin

Temporal Tables erleichtern das Leben von DBAs und Entwicklern zugleich. Handgemachte Trigger-Lösungen können nun abgeschafft werden. Das macht die Administration – besonders im Release-Management bzw. Change-Management – bei archivierten Tabellen deutlich einfacher. Wer solche Daten archivieren und abfragen möchte, sollte sich Temporal Tables auf jeden Fall anschauen.

Stretch Database

Heutzutage ist es nicht selten, Datenbanken in Terrabyte-Größe zu finden. Das tritt im OLAP-Bereich häufiger auf, dennoch gibt es einen deutlichen Zuwachs im OLTP-Bereich. Die Gründe für den Zuwachs sind vielfältig. Im OLTP-Bereich hat es oft damit zu tun, dass man die operativen Daten aus mehreren Jahren, z. B. wegen der Aufbewahrungspflicht, im "Online"-System bereithalten muss. Da viele Systeme von Drittanbietern stammen und diese selten Archivierungskonzepte liefern, werden die Systeme bzw. Datenbanken über die Zeit unhandlich groß.

Stretch Database bietet die Möglichkeit, genau solche Datenbanken transparent aufzuteilen, um die Ressourcenlast auf das eigentliche Produktivsystem zu reduzieren. Man definiert eine oder mehrere Filterkriterien, um die Daten in Hot- bzw. Cold-Daten aufzuteilen. Anhand dieser Aufteilung werden die Cold-Daten in eine Azure-gehosteten Datenbank transparent migriert. Dabei wird das Produktivsystem entlastet: Backups werden nur von den heißen Daten gemacht, die Datenbankdateien werden kleiner und somit der lokale Speicherbedarf reduziert. Abfragen, die nur die lokalen Daten berühren, müssen weniger Daten durchsuchen, um Ergebnisse zu liefern. Sollte aber eine Abfrage gestartet werden, der die kalten Daten ebenfalls benötigt, agiert SQL-Server völlig selbstständig im Hintergrund und holt die Daten aus der Cloud, um sie dann als Teil des Ergebnisses darzustellen.

Abb.11: Applikation schickt eine Anfrage an die Datenbank. © William Durkin
Abb.11: Applikation schickt eine Anfrage an die Datenbank. © William Durkin

In Abb.11 zu sehen: Eine Applikation schickt eine Anfrage an die Datenbank. Die Abfrage benötigt Daten aus den "gestreckten" Daten, die in die Cloud verschoben wurden. Der On-Premises-SQL-Server holt im Hintergrund die benötigten Daten, ohne dass die Applikation überhaupt davon erfahren hat und liefert die Ergebnismenge an die Applikation zurück.

Database Scoped Configurations

Viele von uns halten mehrere Datenbanken zusammen auf einem SQL Server. Das ist eine übliche Vorgehensweise, denn SQL Server-Lizenzen sind teuer und wir wollen die Lizenzen soweit wie möglich ausnutzen. Leider gibt es aber viele Applikationen, die bestimmte Servereinstellungen voraussetzen, um nachher einen Supportanspruch wahrnehmen zu können. Microsoft Sharepoint ist ein sehr gutes Beispiel für solche Vorgaben. Bei Sharepoint wird die parallele Abfrageausführung (kurz MAXDOP) untersagt. Sollten wir aber zusammen mit den Sharepoint-Datenbanken andere Datenbanken betreiben wollen, wären wir gezwungen, alle Datenbankabfragen – ob Sharepoint oder nicht – mit seriellen Abfrageausführungen zu betreiben. Das kann unter Umständen Performanceeinbußen verursachen.

Mit SQL Server 2016 hat Microsoft es endlich ermöglicht, einige dieser Einstellungen von der Serverebene auf die Datenbankebene zu verschieben. Das heißt, wir sind nun in der Lage, die Sharepoint-Datenbanken mit MAXDOP = 1 zu betreiben, während wir alle anderen Datenbanken mit einem anderen MAXDOP-Wert betreiben können.

So können wir mehrere Datenbanken auf einem Server betreiben, wo wir früher dazu gezwungen waren, die Datenbanken auf getrennten Instanzen/Servern zu halten. Diese Tatsache erspart uns nicht nur Verwaltungsaufwand, sondern auch reichlich Lizenzkosten, denn nun können Datenbanken zusammengelegt werden, die sonst getrennt werden mussten.

Fazit

Microsoft hat mit SQL Server 2016 eine ganze Reihe an Neuerungen, sowohl für DBAs als auch für Entwickler geliefert. Diese neuen Möglichkeiten räumen viele Gründe gegen ein Upgrade aus. Besonders interessant sind die Features, die eine Konsolidierung sowie einen gesicherten Migrationspfad anbieten (Scoped Configuration und Query Store). Die Gewissheit, ob eine bestehende Datenbank auf eine neuere Version störungsfrei migriert werden kann, war bei den letzten Versionen von SQL Server nicht unbedingt gegeben. Diese Bedenken sind dank dieser neuen Version deutlich geringer geworden und aufgrund dessen kann ich meinen Kunden getrost den SQL Server 2016 empfehlen.

Wichtige Information: Kurz vor der Veröffentlichung dieses Artikels hat Microsoft mitgeteilt, dass viele neue Features in der teuren Enterprise Edition erscheinen werden und so war es bereits mit vielen Features, die bisher in diese Artikelserie gezeigt wurden. Microsoft hat jedoch im November 2016 mit dem Release von Service Pack 1 für SQL Server 2016 eine riesige Überraschung serviert. Die Unterschiede an Features und Funktionalität zwischen Enterprise Edition und Standard Edition sind weitestgehend entfernt worden! Es ist nun möglich, viele Enterprise Edition-Features ebenfalls in der Standard Edition zu nutzen. Dafür muss lediglich der Service Pack installiert werden. Einzelheiten dazu findet man bei Microsoft [4].

Dies ist der zweite von vier Artikeln von William Durkin über die Neuheiten von SQL Server 2016. Die weiteren Artikel der Serie (Engine Features, Programmierung und Hochverfügbarkeit) erscheinen in den nächsten Wochen.

Teil I: SQL Server 2016: Was ist neu? – Security

nach Oben
Autor

William Durkin

William Durkin ist unabhängiger SQL Server-Consultant und auf SQL Server-Performance Tuning, Hochverfügbarkeit und Systemmigrationen/-upgrades spezialisiert. Geboren in England und seit 2001 im niedersächsichen Emsland zuhause,...
>> Weiterlesen
botMessage_toctoc_comments_929