Christoph Muthmann: SSMS 2016 Dezember 2016

Passend zu Nikolaus gibt es eine neue Version des SQL Server Management Studios 2016!

Ganze Geschichte »

Christoph Muthmann: Schreib für Freiheit: Der Amnesty-Briefmarathon 2016

Es ist besser, eine Kerze anzuzünden, als die Dunkelheit zu verfluchen.

Ganze Geschichte »

Christoph Müller-Spengler: 0x851c0001 – the credentials you provided for the ‘SQLSERVERAGENT’ service is invalid.

We always install SQL Server with configuration.ini files to be sure that each and every server looks like the other. Today we received the error message:

(01) 2016-11-29 14:23:47 Slp: Exception type: Microsoft.SqlServer.Chainer.Infrastructure.InputSettingValidationException
(01) 2016-11-29 14:23:47 Slp: Message: 
(01) 2016-11-29 14:23:47 Slp: The credentials you provided for the 'SQLSERVERAGENT' service is invalid.
(01) 2016-11-29 14:23:47 Slp: HResult : 0x851c0001
(01) 2016-11-29 14:23:47 Slp: FacilityCode : 1308 (51c)
(01) 2016-11-29 14:23:47 Slp: ErrorCode : 1 (0001)

Sometimes it’s just this simple:
The password generator added a “&” somewhere in the password for the Service Account:

a1B2c3D&4e5F6gH

Following the Password Policy in MSDN, you will find that “&” is not on the whitelist, just

  • ! – exclamation point
  • $ – dollar sign
  • # – number sign
  • % – percent

is allowed.

Happy pa$$w0rding to all of you🙂


Christoph Muthmann: Anzahl von VLFs schätzen

Wer überlegt, wie man seine LDFs richtig dimensioniert, um nach beendeten Wachstum eine überschaubare Anzahl von VLFs zu erhalten, kann das Verhalten relativ exakt vorausberechnen.

Ganze Geschichte »

Christoph Muthmann: SSMS vertikale Scrollleiste ändern

Wer die neueren Versionen des SSMS (SQL Server Management Studio) ab SQL Server 2016 verwendet, wird sich vielleicht schon mal gefragt haben, wo denn aktuell der Schieber zum Scrollen steht. Insbesondere bei längeren Texten kann dies schon mal sehr unübersichtlich werden, vor allem wenn auch noch einige Fehler in rot, Änderungen in gelb und gespeicherte Zeilen in grün markiert sind.

Ganze Geschichte »

Christoph Muthmann: Announcing SQL Server on Linux public preview

Neben der Ankündigung, die Andreas Wolter bereits veröffentlicht hat, gibt es auch Neuigkeiten vom SQL Server auf Linux.

Ganze Geschichte »

Dirk Hondong: CREATE OR ALTER mit SQL 2016 SP1

Wie sich die Zeiten doch ändern. Vor einem Jahr habe ich kurz über das Thema  DROP…CREATE geschrieben, bzw. das Thema DROP..IF EXISTS angeschnitten, als es gerade neu war in der CTP des 2016ers.

Und jetzt, mit dem SP1, kommt tatsächlich die CREATE OR ALTER Syntax ins Haus geflattert.

Für mich als DBA ist das natürlich großartig zu wissen. Wenn man Datenbanken unter sich hat mit einer granularen Sicherheit, dann ist dieser Befehl nun echt Gold wert. Etwaige Applikationsupdates, welche auch die DB im Hintergrund anpacken, „zerschießen“ einem so nicht mehr die Berechtigungen, welche evtl. auf einzelnen Objekten vorhanden sind.

Also, schaut in Euren Skriptsammlungen und Quellcodeverwaltungen nach und überarbeitet Eure Skripte, wenn Ihr schon für den 2016er entwickelt.

Natürlich gibt es noch viele andere Neuerungen die mit dem SP1 kommen (neue query hint Optionen, Partitionierung und In-Memory Funktionalitäten in allen Editionen und und und…). Aber hier waren schon Andere schneller, was das bloggen angeht und mitunter auch schon richtig ausführlich. Hier nenne ich nur mal schnell den Andreas Wolter und Niko Neugebauer.

Und nun geht’s an Testen:

2016-11-17-10_07_42-sql-server-2008-r2-diagnostic-information-queries-cy-2016-sql-readonlysql

 

 


Andreas Wolter: SQL Server 2016 SP1 removes the editions’ programming feature limits! Everything from Always Encrypted to XTP/In-memory for everybody!

Christoph Muthmann: Microsoft SQL Server team hosts Ask Me Anything session

Am kommenden Freitag (18.11.2016) findet eine AMA-Session vom SQL Server Team statt.

Ganze Geschichte »

Dirk Hondong: Sicherheitsupdates (MS16-136) für SQL Server 2012, 2014, 2016 veröffentlicht

Hallo zusammen,

gestern frisch von Microsoft veröffentlicht:

Microsoft-Sicherheitsbulletin MS16-136 – Hoch

Dabei handelt es sich um Sicherheitslücken, wo der potentielle Angreifer erweiterte Berechtigungen am SQL Server erlangen kann.

Für den SQL Server 2016 bedeutet es, dass es das CU3 gibt.

Happy patching and testing…

 


Dirk Hondong: 100% SQLKonferenz 2017, 25% SQLPaparazzo

Hallo zusammen,

im Februar 2017 ist es soweit, die SQLKonferenz findet wieder statt. Um genau zu sein: vom 14. bis 16. Februar an gewohnter Stelle, dem Darmstadtium.

Es ist inzwischen schon das 4. Mal, dass dieses großartige Event von der PASS Deutschland in Zusammenarbeit mit dem e-Team abgehalten wird. Viel braucht man eigentlich schon nicht mehr sagen. Es wird wieder losgehen mit einem Tag voller PreCons gefolgt von 2 Tagen voll mit Vorträgen aus insgesamt 6 verschiedenen Themengebieten. Noch ist Nichts publik, aber ich hab schon ein paar Insider Infos (als SQLPaparazzo schon fast selbstverständlich). Ich kann nur sagen: wie geil wird das denn?

Doch 2017 wird dennoch etwas anders sein. Der SQLPaparazzo wird nur auf “Sparflamme” arbeiten. Es ist das erste Mal, dass ich nicht privat als ein sogenannter Volunteer bei der SQLKonferenz aufschlage. Tatsächlich ist es 2017 so, dass mein Arbeitgeber mich auf die Konferenz schickt, was mich natürlich riesig freut. Aber genau aus dem Grund wird es mir 2017 nicht möglich sein in jede Session reinzuhüpfen, um ein paar Aufnahmen von den Sprechern und dem Publikum zu machen. Stattdessen wird es von meiner Seite aus dieses Mal nur Aufnahmen geben von Vorträgen, die ich von Anfang bis Ende besuchen werde plus das Ganze “Drumherum”.

Um jetzt eventuell die Veranstaltung Fototechnisch doch etwas mehr abdecken zu können frage ich einfach mal in die Community-Runde: Wer würde denn vielleicht noch seine Kamera mitbringen und mitmachen? Die- oder derjenige kann sich gerne bei Tillmann Eitelberg (t) oder mir melden.


Christoph Muthmann: MVP Reconnect

Microsoft möchte zukünftig die Zusammenarbeit mit ehemaligen MVPs verbessern und sieht hierfür eine Art MVP-Alumni-Netzwerk vor.

Ganze Geschichte »

Dirk Hondong: SQL Server Management Studio 16.5 verfügbar

Der Titel sagt schon Alles. Eine neue Version des SSMS ist da.

Wie beim letzten Mal auch schon ist der deutsche Link noch nicht aktuell. Daher einfach auf https://msdn.microsoft.com/en-us/library/mt238290.aspx gehen und von dort die Version ziehen bzw. direkt diesen Link nehmen, wenn man eh mit der englischsprachigen Version arbeitet:

https://download.microsoft.com/download/C/B/C/CBCFAAD1-2348-4119-B093-199EE7AADCBC/SSMS-Setup-ENU.exe

Wie es scheint, sind wieder einige Bugs gefixt worden wie z.B. der Application Crash beim Schließen von SSMS (kenn ich auch schon) oder dass das „Manage Compression“ Menü deaktiviert ist bei Benutzertabellen im Objektbaum.

 


Bernd Jungbluth: Zusatztermine für die SQL Server-Seminare im Herbst

Die Seminare “Datenbankentwicklung mit SQL Server” am 9.11.2016 und “SQL Server Data Tools” am 10.11.2016 sind ausverkauft.
Aufgrund der großen Nachfrage gibt es für die beiden Seminare zusätzliche Termine:

29.11.2016 - Datenbankentwicklung mit SQL Server - PDF
30.11.2016 - SQL Server Data Tools - PDF - PDF

Auch die anderen SQL Server-Seminare sind so gut wie ausgebucht.
Für diese Termine gibt es derzeit nur noch wenige freie Plätze:

08.11.2016 - Migration Access nach SQL Server - PDF
15.+16.11.2016 - SQL Server Integration Services - PDF
17.11.2016 - SQL Server Reporting Services - PDF

Die Seminare finden im Hotel Ebertor in Boppard am Rhein statt.

Die Teilnahme an einem Seminar kostet 375 Euro pro Tag und Person zzgl. MwSt.
Speisen und Getränke sind im Preis enthalten.

Die Teilnehmerzahl ist auf 8 Personen begrenzt.

Zur Anmeldung reicht eine kurze E-Mail an workshop@berndjungbluth.de.

Christoph Muthmann: PASS Essential zum Thema Biml

Im kommenden Jahr gibt es ein PASS Essential zum Thema Biml in Essen.

Ganze Geschichte »

Christoph Müller-Spengler: Log chain broken? How to deal with backups from the future.

Today (2015-10-05) we had to deal with an interesting phenomenon. Backups from the future breaking the log chain!

Problem

Our third party software solution that is responsible for backup and restore tried to take a transaction log backup of a database but always converted the transaction log backup into a full backup as it detected the backup chain being broken.

You might also notice this informational message somewhere in your backup software:

Broken chain detected for database [<PlaceYourDatabaseNameHere>]. Automatically converting the backup to Full. This could happen due to a) Performing backups outside our software. b) Running log backups without running full after database restore of previous cycle. c) May have failed to record previous backup information of the database in <PlaceYourBackupSoftwareNameHere>.

The Backup chain is very important when your database is in recovery model FULL, because you would have lost the ability to restore to a certain point-in-time. The software detects this and automatically tries to fix this by taking a full backup. (A differential would have done the job, says Paul Randal: A SQL Server DBA myth a day: (20/30) restarting a log backup chain requires a full database backup)

So what exactly happend to our SQL Server Instance?

Analysis

Let’s have a look at the backup history for the database:

;WITH backup_cte
 AS ( SELECT database_name ,
 backup_type = CASE type
 WHEN 'D' THEN 'database'
 WHEN 'L' THEN 'log'
 WHEN 'I' THEN 'differential'
 ELSE 'other'
 END ,
 backup_finish_date ,
 rownum = ROW_NUMBER() OVER ( PARTITION BY database_name,
 type ORDER BY backup_finish_date DESC )
 FROM msdb.dbo.backupset
 )
SELECT database_name ,
 backup_type ,
 backup_finish_date
FROM backup_cte
WHERE database_name = DB_NAME(DB_ID())
 AND rownum = 1
ORDER BY database_name;

This results in:

backupdatefromthefuture

Wow – a transaction log backup from the future!

That of course could be the problem for the backup software.

But how would that effect the software to think that the backup chain is broken? The backup chain is intact when the last_lsn of the previous transaction log backup always is the first_lsn of the following transaction log backup. This is how the transaction logs are “chained”.

So let’s take a look at the LSNs:

-- ##################
SELECT bs.database_name
 , bs.backup_start_date
 , bs.backup_finish_date
 , CASE bs.type
 WHEN 'D' THEN 'DATABASE'
 WHEN 'I' THEN 'DIFFERENTIAL'
 WHEN 'L' THEN 'LOG'
 END AS backup_type
 , first_lsn
 , last_lsn
FROM msdb.dbo.backupset bs
WHERE database_name = DB_NAME(DB_ID())
ORDER BY bs.backup_start_date DESC

This results to:

lsns

where you can see that the first_lsn from the transaction log backup having finished at 2016-10-01 03:48:49.000 has a LSN in between the first_lsn and last_lsn of the full backup.

The following transaction log backups now fulfills the chain. Each last_lsn is the new first_lsn.

SQL Server has another source where to find the relevant last_lsn for a given database, the DMV “database_recovery_status“:

select last_log_backup_lsn
from sys.database_recovery_status
where database_id = db_id()

For our special database the last_log_backup_lsn resulted as:

last_log_backup_lsn

but wait, this is exactly the last_lsn from the screenshot above, so everything should be fine, right?

Now this is a shot in the dark, but i assume that the backup software also consideres the date to order the backups, not only the ascending LSNs.

Executing the following T-SQL statement:

SELECT bs.database_name
 , bs.backup_start_date
 , bs.backup_finish_date
 , CASE bs.type
 WHEN 'D' THEN 'DATABASE'
 WHEN 'I' THEN 'DIFFERENTIAL'
 WHEN 'L' THEN 'LOG'
 END AS backup_type
 , first_lsn
 , last_lsn
 , srs.last_log_backup_lsn AS [database_recovery_status.last_log_backup_lsn]
FROM msdb.dbo.backupset bs
 INNER JOIN sys.database_recovery_status srs ON srs.database_guid = bs.database_guid
WHERE database_id = DB_ID()
ORDER BY bs.backup_start_date DESC

results to:

lsns_differ

Here you can see that the LSN from the DMV sys.database_recovery_status differs to the one from msdb.dbo.backupset. This is just because of the column backup_start_date being taken into consideration.

… and in case you want a simple query that tells you, if you have a broken log chain, you can use this:

SELECT  	TOP 1 last_lsn as [msdb.dbo.backupset.last_lsn]
			, srs.last_log_backup_lsn AS [sys.database_recovery_status.last_log_backup_lsn]
			, CASE WHEN bs.last_lsn = srs.last_log_backup_lsn THEN 'log chain intact' ELSE 'LOG CHAIN BROKEN !!!' END AS [Log Chain Status]
FROM		msdb.dbo.backupset bs
			INNER JOIN sys.database_recovery_status srs ON srs.database_guid = bs.database_guid
WHERE		srs.database_id = DB_ID()
AND bs.type = 'L'
ORDER BY	bs.backup_start_date DESC

This results as:

logchainbroken

Solution

Just delete the row from the future from the msdb.

I used the code, that is implemented in the system stored procedure msdb.dbo.sp_delete_backuphistory and slightly changed just two things:

as i had to delete records from the future, i had to change

WHERE backup_finish_date < @oldest_date

into

WHERE backup_finish_date > GETDATE()

So the T-SQL to delete information about backups from the future looks like this:

 DECLARE @backup_set_id TABLE (backup_set_id INT)
DECLARE @media_set_id TABLE (media_set_id INT)
DECLARE @restore_history_id TABLE (restore_history_id INT)

INSERT INTO @backup_set_id (backup_set_id)
SELECT DISTINCT backup_set_id
FROM msdb.dbo.backupset
WHERE backup_finish_date > GETDATE() @oldest_date

INSERT INTO @media_set_id (media_set_id)
SELECT DISTINCT media_set_id
FROM msdb.dbo.backupset
WHERE backup_finish_date > GETDATE() @oldest_date

INSERT INTO @restore_history_id (restore_history_id)
SELECT DISTINCT restore_history_id
FROM msdb.dbo.restorehistory
WHERE backup_set_id IN (SELECT backup_set_id
FROM @backup_set_id)

BEGIN TRANSACTION

DELETE FROM msdb.dbo.backupfile
WHERE backup_set_id IN (SELECT backup_set_id
FROM @backup_set_id)
IF (@@error > 0)
GOTO Quit

DELETE FROM msdb.dbo.backupfilegroup
WHERE backup_set_id IN (SELECT backup_set_id
FROM @backup_set_id)
IF (@@error > 0)
GOTO Quit

DELETE FROM msdb.dbo.restorefile
WHERE restore_history_id IN (SELECT restore_history_id
FROM @restore_history_id)
IF (@@error > 0)
GOTO Quit

DELETE FROM msdb.dbo.restorefilegroup
WHERE restore_history_id IN (SELECT restore_history_id
FROM @restore_history_id)
IF (@@error > 0)
GOTO Quit

DELETE FROM msdb.dbo.restorehistory
WHERE restore_history_id IN (SELECT restore_history_id
FROM @restore_history_id)
IF (@@error > 0)
GOTO Quit

DELETE FROM msdb.dbo.backupset
WHERE backup_set_id IN (SELECT backup_set_id
FROM @backup_set_id)
IF (@@error > 0)
GOTO Quit

DELETE msdb.dbo.backupmediafamily
FROM msdb.dbo.backupmediafamily bmf
WHERE bmf.media_set_id IN (SELECT media_set_id
FROM @media_set_id)
AND ((SELECT COUNT(*)
FROM msdb.dbo.backupset
WHERE media_set_id = bmf.media_set_id) = 0)
IF (@@error > 0)
GOTO Quit

DELETE msdb.dbo.backupmediaset
FROM msdb.dbo.backupmediaset bms
WHERE bms.media_set_id IN (SELECT media_set_id
FROM @media_set_id)
AND ((SELECT COUNT(*)
FROM msdb.dbo.backupset
WHERE media_set_id = bms.media_set_id) = 0)
IF (@@error > 0)
GOTO Quit

COMMIT TRANSACTION
RETURN

Quit:
ROLLBACK TRANSACTION

 

Thank you for reading,

Christoph

 


Uwe Ricken: Parameter Sniffing – Lösungsansätze

Wer täglich mit Microsoft SQL Server arbeitet – sei es als DBA oder als Entwickler – wird sich schon mal mit dem Problem von Parameter Sniffing auseinandergesetzt haben. “Parameter Sniffing” bedeutet, dass Microsoft SQL Server beim Ausführen einer Stored Procedure/parametrisierten Abfrage den Übergabeparameter verwendet, um die Kardinalität des Wertes zu bestimmen und zukünftige Ausführungen der Abfrage auf Basis des ERSTEN Übergabeparameters durchführt. Die Kardinalität des Parameters fließt in die Bestimmung der Abfragestrategie mit ein. Die Abfragestrategie wird als Ausführungsplan im Plancache abgelegt. Dieser Artikel beschäftigt sich mit den Problemen, die sich aus dieser Arbeitsweise ergeben und zeigt mögliche Lösungsansätze.

Testumgebung

Um das Problem von Parameter Sniffing darzustellen, wird eine Testtabelle mit ca. 10.000 Datensätzen (je nach Version von Microsoft SQL Server) benötigt. Ebenfalls wird eine Stored Procedure für die Ausführung der standardisierten Abfragen verwendet.

Tabelle

Als Datengrundlage dient eine Tabelle mit dem Namen [dbo].[Mitarbeiter]. Aus Gründen der Vereinfachung wird ein Attribut mit einer Datenlänge von 1.000 Bytes verwendet um „Volumen“ zu generieren.

-- Erstellen der Demotabelle
CREATE TABLE dbo.Mitarbeiter
(
    Id         INT        NOT NULL IDENTITY(1,1),
    Name       CHAR(1000) NOT NULL,
    CostCenter CHAR(7)    NOT NULL
);
GO

-- Befüllen der Tabelle mit ca. 10.000 Datensätzen
INSERT INTO dbo.Mitarbeiter WITH (TABLOCK) (Name, CostCenter)
SELECT CAST(text AS CHAR(1000)),
       'C' + RIGHT(REPLICATE('0', 6) + CAST(severity AS VARCHAR(4)), 6)
FROM   sys.messages
WHERE  language_id = 1033;
GO

-- Erstellung der benötigten Indexe
CREATE UNIQUE CLUSTERED INDEX cuix_Mitarbeiter_Id ON dbo.Mitarbeiter (Id);
CREATE NONCLUSTERED INDEX nix_Mitarbeiter_CostCenter ON dbo.Mitarbeiter (CostCenter);
GO

Die Verteilung der Schlüsselwerte des Indexes [nix_Mitarbeiter_CostCenter] kann aus dem Histogramm der gespeicherten Statistiken entnommen werden:

-- Verteilung der Kostenstellen
DBCC SHOW_STATISTICS (N'dbo.Mitarbeiter', N'nix_Mitarbeiter_CostCenter') WITH HISTOGRAM;

histogramm_01
Die Analyse der Verteilung von Kostenstellen zeigt, dass es nur einen Datensatz gibt, der die Kostenstelle „C000013“ als Eintrag gespeichert hat während über 6.300 Einträge zur Kostenstelle „C000016“ vorhanden sind.

Prozedur

Für den Zugriff auf die Daten wird eine Stored Procedure erstellt, die als Parameter die gewünschte Kostenstelle bereitstellt.

CREATE PROCEDURE dbo.Mitarbeiter_pro_Kostenstelle
    @CostCenter	CHAR(7)
AS
    SET NOCOUNT ON;
    SELECT Id, Name, CostCenter FROM dbo.Mitarbeiter
    WHERE CostCenter = @CostCenter;
    SET NOCOUNT OFF;
GO

Problemstellung

Sobald Microsoft SQL Server ein SQL-Statement ausführt, wird der generierte Ausführungsplan im Plan Cache gespeichert, um beim nächsten Aufruf des SQL Statements wiederverwendet zu werden. Dieses Verfahren ist bei komplexen Abfragen ein gewünschter Effekt. Bei der ersten Ausführung der Prozedur mit verschiedenen Parametern werden unterschiedliche Ausführungspläne generiert.

EXEC dbo.Mitarbeiter_pro_Kostenstelle @CostCenter = 'C000016';

executionplan_ci_scan_01

EXEC dbo.Mitarbeiter_pro_Kostenstelle @CostCenter = 'C000013';

executionplan_key_lookup_01

Die unterschiedlichen Ausführungspläne kommen zustande, da Microsoft SQL Server unter anderem das zu generierende I/O berücksichtigt. Ein INDEX SEEK (wie z. B. mit Kostenstelle C000013) verursacht zusätzliche 3 I/O pro gefundenen Datensatz. Würde für die Suche nach der Kostenstelle C000016 dieser Ausführungsplan verwendet, so würden insgsamt > 19.000 I/O nur für die Key Lookups produziert werden. Ein TABLE SCAN verursacht lediglich 1.300 I/O.
Entscheidend für die Performance der Stored Procedure ist – und genau das ist das Problem von Parameter Sniffing – die initiale Ausführung der Prozedur. Das folgende Beispiel zeigt die beiden Ausführungspläne, wenn zu Beginn (Plan Cache ist leer!) die Abfrage auf die Kostenstelle „C000013“ ausgeführt wird. Anschließend (der Ausführungsplan ist nun im Plan Cache) wird die Stored Procedure mit dem Wert „C000016“ für die Kostenstelle ausgeführt:

EXEC dbo.Mitarbeiter_pro_Kostenstelle @CostCenter = 'C000013';
GO
EXEC dbo.Mitarbeiter_pro_Kostenstelle @CostCenter = 'C000016';
GO

executionplan_02

Bei der ersten Ausführung wird der Parameterwert „C000013“ von Microsoft SQL Server verwendet, um einen Ausführungsplan zu erzeugen. Dieser Ausführunsplan wird im Plan Cache gespeichert. Bei der zweiten Ausführung erkennt Microsoft SQL Server, dass der Ausführungsplan bereits vorhanden ist und verwendet ihn erneut. Hierbei kommt es dann jedoch zu einem gravierenden Nachteil; die Prozedur wurde unter der Annahme zwischengespeichert, dass nur 1 (!!!) Datensatz zurückgeliefert wird. Tatsächlich werden aber bei der zweiten Ausführung > 6.000 Datensätze geliefert. Ein Blick auf die Eigenschaften des gespeicherten Ausführungsplans verdeutlicht das Problem:

plan_parameters_01

Die Strategie des gespeicherten Ausführungsplans basiert auf dem „Parameter Compiled Value“ während die Ausführung durch den „Parameter Runtime Value“ geprägt ist. Ist der Ausführungsplan für die initiale Kostenstelle ideal gewesen, dreht er für die erneute Verwendung mit anderen Parameterwerten ins Gegenteil. Dieses Problem wird Parameter Sniffing genannt. Die Frage, die jeden Programmierer in einem solchen Fall umtreibt: Wie kann man das Problem minimieren/verhindern?

Lösungsansätze

Das Speichern eines Ausführungsplans ist ein essentieller Bestandteil in der Optimierungsphase von Microsoft SQL Server. Wird ein Ausführungsplan im Plan Cache gespeichert, kann Microsoft SQL Server bei der Ausführung der Prozedur sowohl den Kompiliervorgang als auch den Optimierungsvorgang überspringen und die Prozedur unmittelbar ausführen. Für die Prozedur im Beispiel mag ein RECOMPILE nicht ins Gewicht fallen; aber man sollte nicht nur die absolute Zeit berücksichtigen sondern auch im Fokus behalten, wie stark die Prozedur innerhalb der Applikation genutzt wird. Sind es nur ein paar hundert Aufrufe am Tag oder millionenfache Aufrufe – das kann einen nicht unerheblichen Einfluss auf die Performance der Anwendung haben. Aus diesem Grund müssen unterschiedliche Optimierungsmöglichkeiten in Betracht gezogen werden.

Neukompilierung

Das generelle Problem von Parameter Sniffing ist der gespeicherter Ausführungsplan. Ziel des ersten Lösungsansatzes ist es, das Speichern von Ausführungsplänen zu vermeiden oder aber – abhängig vom Parameterwert – einen idealen Plan zu finden.

Neukompilierung durch Applikation

Sofern eine direkte Einflussnahme in den Applikationscode möglich ist, gibt es zwei Ansätze, um eine Neukompilierung zu erzwingen.

Manipulation von Planattributen

Ein Ausführungsplan speichert viele Informationen. Unter den vielen Informationen (z. B. Ausführungstext, Strategie, etc…) gibt es sogenannte Planattribute. Bei diesen Planattributen unterscheidet man zwischen cacherelevanten Einstellungen und nichtrelevanten Einstellungen.

SELECT A.*
FROM    sys.dm_exec_query_stats AS S
        CROSS APPLY sys.dm_exec_sql_text (S.sql_handle) AS T
        CROSS APPLY
        (
           SELECT *
           FROM   sys.dm_exec_plan_attributes(S.plan_handle)
                  WHERE is_cache_key = 1
         ) AS A
WHERE    T.text LIKE '%dbo.Mitarbeiter_pro_Kostenstelle%'
         AND T.text NOT LIKE '%sys.dm_exec_sql_text%';

Der obige Code liefert alle cacherelevante Einstellungen, die Bestandtei des Ausführungsplans sind. Werden Einstelungen VOR der Ausführung der Prozedur geändert, wird ein neuer Ausführungsplan generiert, sofern nicht bereits ein Ausführungsplan vorhanden ist.

plan_attributes_01

-- Ändern der Verbindungseinstellungen
SET DATEFORMAT ymd;
EXEC dbo.Mitarbeiter_pro_Kostenstelle @CostCenter = 'C000016';
SET DATEFORMAT dmy;

Manipulation des Aufrufs der Prozedur

Die Idee, Sessionkonfigurationen zur Laufzeit anzupassen, ist keine gute Lösung. Das obige Beispiel könnte sogar dazu führen, dass die Applikation nicht mehr korrekt läuft, wenn das Datum nicht in einem vorher bestimmten Format verwendet wird. Oft reicht es aus, den Aufruf unmittelbar anzupassen, damit eine Neukompilierung durchgeführt wird. Ebenfalls wird diese Lösung nur bedingt helfen, da der Ausführungsplan für alle Anwender gilt – und somit bei erneuter Ausführung auf die gleichen Parameter und Cachesettings trifft!
Mit dem folgenden T-SQL-Aufruf (gestartet innerhalb der Applikation) kann eine Neukompilierung der Stored Procedure erzwungen werden:

EXEC dbo.Mitarbeiter_pro_Kostenstelle @CostCenter = 'C000013' WITH RECOMPILE;
GO
EXEC dbo.Mitarbeiter_pro_Kostenstelle @CostCenter = 'C000016' WITH RECOMPILE;
GO

Durch die Option „WITH RECOMPILE“ auf Ebene der Prozedur wird die vollständige Prozedur neu kompiliert. Diese Technik ist nicht zu empfehlen, wenn die Prozedur sehr umfangreich ist und innerhalb der Prozedur komplexe Abfragen mit einer langen Optimierungsphase verwendet werden.

Neukompilierung in Prozedur

Wenn es an den Möglichkeiten mangelt, den Applikationscode zu manipulieren, bleibt als Alternativ die Optimierung IN der Prozedur. In diesem Fall muss die Neukompilierung durch die Prozedur selbst bestimmt werden. Hierzu gibt es zwei Lösungsansätze:

  • Neukompilierung der vollständigen Prozedur
  • Neukompilierung des betroffenen Statements

Bis einschließlich Microsoft SQL Server 2000 war eine Neukompilierung nur auf Ebene der Prozedur möglich; seit Microsoft SQL Server 2005 gibt es die Möglichkeit der Neukompilierung auf „Statementlevel“. Um eine Neukompilierung der vollständigen Prozedur zu erzwingen, ist der Hinweis unmittelbar im Prozedurkopf zu platzieren:

CREATE PROCEDURE dbo.Mitarbeiter_pro_Kostenstelle
	@CostCenter	CHAR(7)
	WITH RECOMPILE
AS
	SET NOCOUNT ON;
	SELECT Id, Name, CostCenter FROM dbo.Mitarbeiter WHERE CostCenter = @CostCenter;
	SET NOCOUNT OFF;
GO

Durch die Verwendung von „WITH RECOMPILE“ im Prozedurkopf wird die vollständige Prozedur bei jedem Aufruf neu kompiliert. Bei dieser Technik gilt es zu beachten, wie komplex die Prozedur ist und ob das Verhindern von Parameter Sniffing nicht durch lange Kompilier- und Optimierungszeiten erkauft wird.
Ist eine Prozedur sehr komplex und es lassen sich Statements isolieren, die Opfer von Parameter Sniffing sind, kann ein RECOMPILE seit Microsoft SQL Server 2005 auf einzelne Statements angewendet werden.

CREATE PROCEDURE dbo.Mitarbeiter_pro_Kostenstelle
	@CostCenter	CHAR(7)
AS
	SET NOCOUNT ON;
	SELECT Id, Name, CostCenter FROM dbo.Mitarbeiter WHERE CostCenter = @CostCenter
	OPTION (RECOMPILE);
	SET NOCOUNT OFF;
GO

Verwendung des Density Vectors

Wenn Microsoft SQL Server einen Ausführungsplan erstellt, gibt es zwei Möglichkeiten zur Bestimmung der geschätzten Anzahl von Datensätzen:

  • Verwendung des Histogramms
  • Verwendung des Density Vectors

Microsoft SQL Server kann das Histogramm eines Statistikobjekts nur verwenden, wenn zur Kompilierzeit des Befehls der Wert des Prädikats bekannt ist. Ist das nicht der Fall, muss Microsoft SQL Server vom Histogramm einer Statistik auf den Density Vektor ausweichen.

-- Density Vector der Statistiken
DBCC SHOW_STATISTICS (N'dbo.Mitarbeiter', N'nix_Mitarbeiter_CostCenter')
WITH STAT_HEADER;
DBCC SHOW_STATISTICS (N'dbo.Mitarbeiter', N'nix_Mitarbeiter_CostCenter')
WITH DENSITY_VECTOR;

density_vector_01

Die Abbildung zeigt sowohl den Header als auch den Density Vektor der Statistiken des Index „nix_Mitarbeiter_CostCenter“. Zur Berechnung der geschätzten Anzahl von Datensätzen wird der Wert aus [All Density] im Density Vector mit dem [Rows] aus dem Header multipliziert.
0,0625 * 8932 = 558,25
Ein Problem bei der Verwendung des Density Vektors ist der generische Ansatz, der mit diesen Informationen verfolgt wird. Der Density Vektor kann nur einen Mittelwert bilden. Wenn die Datengrundlage eine gleichmäßige Verteilung der Daten gewährleistet, ist der Density Vektor ein probates Hilfsmittel zur Bestimmung der geschätzten Rückgabemenge; aber dann wäre Parameter Sniffing kein Problem.
Die Demodaten haben einen heterogenen Verteilungsschlüssel; mal sind es extrem viele Datensätze („C000016“) während auf der anderen Seite deutlich weniger Datensätze vorhanden sind („C000013“). Um Microsoft SQL Server zu zwingen, den Density Vektor statt das Histogramm zu verwenden, gibt es zwei programmatische Möglichkeiten:

  • Verwendung einer neuen Variablen im Code der Prozedur
  • Verwendung von OPTION (OPTIMIZE FOR UNKNOWN)

Verwendung von Variablen

Die Kompilierung erfolgt auf Statement-Level. Die Verwendung einer zusätzlichen Variablen innerhalb der Prozedur führt dazu, dass Microsoft SQL Server nicht bestimmen kann, welchen Wert die Variable hat, wenn sie im SELECT-Statement angewendet wird.
Da ein Wert beim Kompilieren des SELECT-Statements nicht deterministisch ist, kann Microsoft SQL Server kein Histogramm verwenden sondern muss auf den Density Vektor ausweichen. Das führt dazu, dass Microsoft SQL Server für JEDE Ausführung den Mittelwert von 558,25 Datensätzen annimmt. Dieser Wert ist für die Kostenstelle C000013 deutlich zu hoch während der Wert für die Kostenstelle C000016 klar zu niedrig ist.

CREATE PROCEDURE dbo.Mitarbeiter_pro_Kostenstelle
    @CostCenter	CHAR(7)
AS
    SET NOCOUNT ON;

    -- Deklaration einer neuen Variablen!
    DECLARE @myCostCenter CHAR(7) = @CostCenter;

    SELECT Id, Name, CostCenter FROM dbo.Mitarbeiter
    WHERE CostCenter = @myCostCenter;

    SET NOCOUNT OFF;
GO

Die Abbildung zeigt die gespeicherten Ausführungspläne für die verwendeten Kostenstellen. In beiden Ausführungsplänen wird vom generischen Wert des Density Vektors als „geschätzte Zeilen“ ausgegangen.

executionplan_03

Verwendung von OPTION (OPTIMIZE FOR UNKNOWN)

Statt mit einer neuen Variablen in der Stored Procedure zu arbeiten, kann im Statement selbst die Option „OPTIMIZE FOR UNKNOWN“ verwendet werden. Wie es der Befehl bereits suggeriert, verhält sich die Anweisung so, als ob ihr der Wert der Prozedurvariablen zum Zeitpunkt der Kompilierung nicht bekannt ist. Diese Option verhält sich exakt wie die zuvor beschriebene Verwendung von neuen Variablen in der Stored Procedure; und erbt damit alle Vor- und Nachteilen dieser Technik.

CREATE PROCEDURE dbo.Mitarbeiter_pro_Kostenstelle
    @CostCenter	CHAR(7)
AS
    SET NOCOUNT ON;

    SELECT Id, Name, CostCenter FROM dbo.Mitarbeiter
    WHERE CostCenter = @CostCenter OPTION (OPTIMIZE FOR UNKNOWN);

    SET NOCOUNT OFF;
GO

Verwendung von Plan Guides

Mit Planhinweislisten kann die Leistung von Abfragen optimiert werden, wenn die Abfrage nicht unmittelbar angepasst werden kann. Planhinweislisten beeinflussen die Abfrageoptimierung, indem Abfragehinweise oder ein fester Abfrageplan für die Abfragen verfügbar gemacht werden. Um einen Abfrageplan zu erstellen, wird die Prozedur sys.sp_create_plan_guide verwendet. Wenn z. B. für die Beispielprozedur angegeben werden soll, dass der Ausführungsplan jedes Mal neu berechnet werden soll (RECOMPILE), müsste ein Planhinweis für die Prozedur folgendermassen implementiert werden:

EXEC sp_create_plan_guide
     @name = N'PG_Employee_By_CostCenter',
     @stmt = N'SELECT Id, Name, CostCenter FROM dbo.Mitarbeiter WHERE CostCenter = @CostCenter;',
     @type = N'OBJECT',
     @module_or_batch = N'dbo.Mitarbeiter_pro_Kostenstelle',
     @params = NULL,
     @hints =  N'OPTION (RECOMPILE)';
GO

Die Verwendung von Planhinweislisten hat den Vorteil, dass Applikationselemente nicht geändert werden müssen. Es gibt viele Softwarehersteller, die – zu Recht – darauf verweisen, dass Anpassungen nicht erlaubt sind und somit ein Garantieanspruch verfällt. Nachteil der Planhinweislisten ist, dass diese Technik nicht in den Express-Editionen von Microsoft SQL Server zur Verfügung stehen.
Ob Microsoft SQL Server eine Planhinweisliste verwendet, kann ebenfalls in den Eigenschaften des Ausführungsplans überprüft werden:

usage_of_planguide

Verwendung von dynamischem Code

Dynamischer Code lässt sich in nicht immer verhindern; für die Lösung des Problems von Parameter Sniffing sollte die nachfolgende Lösung jedoch die letzte Alternative sein. Dynamischer Code bedeutet, dass die auszuführende Abfrage zunächst – dynamisch – in einer Variablen gespeichert wird und anschließend mit EXEC() ausgeführt wird. Die umgeschriebene Prozedur sieht anschließend wie folgt aus:

CREATE PROCEDURE dbo.Mitarbeiter_pro_Kostenstelle
    @CostCenter	CHAR(7)
AS
    SET NOCOUNT ON;

    DECLARE @sql_cmd NVARCHAR(256) = N'SELECT Id, Name, CostCenter FROM dbo.Mitarbeiter
WHERE CostCenter = ' + QUOTENAME(@CostCenter, '''');
    EXEC (@sql_cmd);

    SET NOCOUNT OFF;
GO

Mit der Verwendung von dynamischem SQL geht – neben Sicherheitsbedenken – ein weiterer nicht unerheblicher Nachteil einher, der von vielen Programmierern unterschätzt wird: Plan Cache Bloating!
Bei der Verwendung von dynamischem SQL werden Textfragmente miteinander kombiniert und ergeben so einen – neuen – auszuführenden SQL Befehl. Dieser SQL-Befehl wird dann mittels EXEC in einem eigenen Prozess ausgeführt. Durch die Konkatenation werden keine Variablen innerhalb des SQL-Befehls verwendet sondern ein „fertig designeder Code“ an die Engine gesendet; JEDER unterschiedliche SQL-Befehl wird mit einem eigenen Ausführungsplan im Plan Cache gespeichert!
Die Beispielprozedur wird mit drei unterschiedlichen Kostenstellen ausgeführt. Bei der Analyse der Ausführungspläne ist offensichtlich, dass Microsoft SQL Server für jedes Statement einen „individuellen“ Ausführungsplan erstellt.

EXEC dbo.Mitarbeiter_pro_Kostenstelle @CostCenter = 'C000013';
GO
EXEC dbo.Mitarbeiter_pro_Kostenstelle @CostCenter = 'C000016';
GO
EXEC dbo.Mitarbeiter_pro_Kostenstelle @CostCenter = 'C000019';
GO
SELECT  T.Text,
        QP.usecounts,
        QP.size_in_bytes,
        QP.objtype
FROM    sys.dm_exec_cached_plans AS QP
        CROSS APPLY sys.dm_exec_sql_text (QP.plan_handle) AS T
WHERE   T.text LIKE '%FROM dbo.Mitarbeiter%'
        AND T.text NOT LIKE '%sys.dm_exec_sql_text%';

Die Prozedur wurde insgesamt drei Mal ausgeführt und statt eines einzigen Ausführungsplans wurden drei Pläne erstellt.

plan_cache_bloating_01

Zusammenfassung

Parameter Sniffing ist bei ungleicher Verteilung von Daten eine Herausforderung für jeden SQL Entwickler. Es gibt keine allgemeine Lösung, die als „Silver Bullet“ alle Probleme löst. Sind – wie im verwendeten Beispiel – nur wenige Schlüsselwerte mit unterschiedicher Kardinalität vorhanden, kann die Verwendung von dynamischem Code eine gute Idee sein. Hierbeit gilt es jedoch, den Zugriff auf die Daten durch Views einzuschränken und keinen unmittelbaren Zugriff auf die Tabellen zu gewähren.
Sind die Datenmengen für die einzelnen Werte zu unterschiedlich und Sicherheit ist ein wichtiges Thema, dann scheidet eine Lösung mit dynamischem SQL aus.
Es verbleiben abschließend nur zwei Alternativen. Der Zugriff auf den Density Vekor wird für viele Situationen eine gute Alternative sein; es gibt jedoch Situationen, in denen diese Alternative nicht wirkt, da die verschiedenen Datenwerte zu große Abstände in der Datenmenge haben und somit den Density Vector als ungeeignetes Mittel erscheinen lassen.
Letzte Alternative ist – und bleibt – das Vermeiden einer Speicherung des Ausführungsplans. Nur so ist gewährleistet, dass für jeden einzelnen Wert ein geeigneter Ausführungsplan gewählt wird. Diese Lösung ist jedoch nur so lange ein adäquates Mittel, so lange die Kompilier- und Optimierungsphasen schnell und effizient sind. Wird für den Optimierungsvorgang mehr Zeit aufgewendet als für einen „schlechten“ Plan, sollte man eher den schlechten Plan in Kauf nehmen.

Lesenswert!

Herzlichen Dank furs Lesen!

Dirk Hondong: SQL Server Management Studio September Release (16.4.x)

Hallo zusammen,

nur eine kleine, schnelle Info für diejenigen, die es noch nicht mitbekommen haben: Das Management Studio 2016 liegt seit kurzem in einer neuen Version vor.

Ganz wichtig für mich ist vor Allem das Beheben der out of Memory exception, wozu es auch ein Connect Item gegeben hat.

Die aktuelle Version des Management Studios kann über folgenden Link bezogen werden:

Download SQL Server Management Studio (SSMS)

/EDIT 22.09.2016

Der Download wurde erst einmal von Microsoft wieder zurückgezogen:

2016-09-22-08_44_46-download-sql-server-management-studio-ssms-%e2%80%8e-microsoft-edge

/EDIT 26.09.2016

Nun gibt es das SSMS in der 16.4.1er Version zum Download.

2016-09-26-07_55_03-microsoft-sql-server-management-studio

 

 

 


Andreas Wolter: What makes a successful Tech Conference? // Was macht eine erfolgreiche technische Konferenz aus?

(EN)

After my Asia-tour this summer I feel it is about time for this article.
At the time of this article (09-2016) I have presented at more than 50 technical conferences around the world (Europe, Middle East, Asia, North America), from small with about 150 attendees to huge with around 5000 attendees. With that came a lot of good contact with many of the organizers.

Recently again I have been asked by a good friend and organizer how I felt about their conference (remember, a conference is never run by a single person) and what could be improved.
Even though I did give an answer, I realize there is more to it and I promised to think about it a bit more. I decided to put this into an article and maybe some other organizers and attendees find it interesting.

Note: this article is based on the experience of my own and unlike a technical article there will hardly be one version of the truth.

So, what makes a conference successful?

(DE)

Nach meiner Asien-Tour in diesem Sommer ist nun dieser Artikel an der Reihe.
Zum Zeitpunkt dieses Artikels (09-2016) habe ich auf über 50 technischen Konferenzen weltweit (Europa, Naher Osten, Asien, Nordamerika) präsentiert. Von kleinen Konferenzen mit etwa 150 Teilnehmern bis zu großen Konferenzen mit um die 5000 Teilnehmern. Dabei entstanden viele gute Kontakte mit vielen der Organisatoren.

Erst vor kurzem wurde ich wieder von einem guten Freund und Organisator gefragt, was ich von ihrer Konferenz hielte (eine Konferenz wird ja nie von einer einzelnen Person durchgeführt) und was noch verbessert werden könnte.
Obwohl ich eine Antwort gab, ist mir klar, dass da mehr dahintersteckt, und ich versprach, noch ein bisschen weiter darüber nachzudenken. So entschied ich mich, einen Artikel daraus zu machen, und vielleicht ist er auch für einige andere Organisatoren und Teilnehmer interessant.

Hinweis: dieser Artikel beruht auf meiner eigenen Erfahrung und im Gegensatz zu einem technischen Artikel, wird es kaum eine Version der Wahrheit geben.

Was macht also eine erfolgreiche Konferenz aus?

 Successful_Tech_Conferences_PASS_Summit_2013_Exhibition

Exhibition at PASS Summit USA 2013

“It is big”

Probably the most common answer will be:

1)

It is big”, or even “the biggest” – both in terms of number of attendees and offered parallel sessions, tracks and topics.

A good example would be the PASS Summit in the US: the biggest SQL Server conference worldwide. But of course there are even much bigger events outside of the pure SQL Server world…

Die wahrscheinlich gängigste Antwort wird sein:

1)

Sie ist groß“ oder gar „die größte“ Konferenz – sowohl von der Zahl der Teilnehmer her als auch von den angebotenen Sessions, Tracks und Themen.

Ein gutes Beispiel wäre der PASS Summit in den USA: die weltweit größte SQL-Server-Konferenz. Aber außerhalb der reinen SQL-Server-Welt gibt es natürlich noch viel größere Events…

 Successful_Tech_Conferences_PASS_Summit_2013_Party

Attendees Party at PASS Summit 2013 in Charlotte NASCAR Hall of Fame

“it has more attendees than last year.”

2)

Almost no conference starts “big”, let alone the “biggest”, so in the beginning, when establishing a conference, it will often be: “it has more attendees than last year.” - This is the first and most obvious sign of a successful progression.
- side note: can a progression be positive without an increase of attendees?
– Yes it can:
For example the same number of attendees can be a different mixture in terms of job-background which may fit better to the topic of the conference and hence will be happier than last year’s attendees and hopefully spread the word for the follow-up conference which then finally will have more attendees.

2)

Fast keine Konferenz fängt “groß” an, geschweige denn als „die größte“, so dass es am Anfang, wenn eine Konferenz gerade etabliert wird, oft heißen wird: „es gibt mehr Teilnehmer als letztes Jahr.“ – Das ist das erste und offensichtlichste Zeichen einer erfolgreichen Entwicklung.
- Nebenbemerkung: kann eine Entwicklung positiv sein ohne einen Anstieg an Teilnehmern? – Ja, kann sie:
Zum Beispiel kann die gleiche Zahl an Teilnehmern hinsichtlich Job-Hintergrund anders zusammengesetzt sein und vielleicht besser zum Thema der Konferenz passen und damit auch zufriedener als die Teilnehmer vom Vorjahr sein, und die Konferenz hoffentlich weiterempfehlen, so dass die nächste Konferenz schließlich noch mehr Teilnehmer hat.

 Successful_Tech_Conferences_SQLKonferenz2014.

Thomas La Rock, the PASS President at that time and Oliver Engels, PASS Chapter Leader at that time of German PASS at SQL Konferenz 2014 – the 10th anniversary of PASS Germany

"Your sponsors are your partners."

Also, do not forget the sponsors who are highly dependent on the right mix of people – and not the pure numbers.

Having the right sponsors (by making them happy) will give you more flexibility in the whole setup from location, catering, speaker-invites and “goodies”. If you are reading this in preparation of a conference, bear this in mind.

Your sponsors are your partners.

Auch darf man nicht die Sponsoren vergessen, welche stark von der richtigen Mischung an Leuten abhängig sind – und nicht von den reinen Zahlen.

Die richtigen Sponsoren zu haben (indem man sie glücklich macht) gibt einem mehr Flexibilität beim ganzen Aufbau, von der Location über Catering, Sprecher-Einladungen bis hin zu „Goodies“. Wenn ihr gerade bei der Vorbereitung einer Konferenz seid, während ihr dies lest, behaltet das im Auge.

Eure Sponsoren sind Eure Partner.

 Successful_Tech_Conferences_SQLKonferenz2016_SarpedonQualityLab

Sarpedon Quality Lab® Sponsor booth at SQL Konferenz 2016

“community conferences are often very happy with a break even.”

3)

It can also be: “it generates a good profit.
Actually very often it will be: “it does generate profit.” – Rather than a loss. And this happens more often than you may think.

Why would a conference make a loss and still be repeated you would ask yourself?

In fact community conferences are often very happy with a break even as the main interest is to just serve the community and not the profit.

For commercial conferences the reasons may be a bit different.

That brings me to the other options:

3)

Es kann auch heißen: “Sie wirft einen guten Gewinn ab. Oftmals wird es tatsächlich heißen: „Sie erzielt Gewinn.“ – Mehr als einen Verlust. Und das passiert häufiger als man meinen mag.

Warum würde eine Konferenz Verluste machen und trotzdem wiederholt werden, könnte man sich fragen?

Tatsächlich sind Community Konferenzen oft sehr zufrieden mit einer runden Null, da das Hauptinteresse der Community und nicht dem Profit gilt.

Bei kommerziellen Konferenzen mögen die Gründe etwas anders sein.

Das führt mich zu den anderen Optionen:

 Successful_Tech_Conferences_PASS_Summit2014_MCMs

The MCMs at the PASS Summit 2014

“famousness leads to more publicity

4)

The conference may not create profit, but, simply put “famousness”. Famousness that leads to more publicity. And this alone can be worth the effort for both a commercial as well as a non-profit driven conference.

What kind of fame could that be other than being “the greatest”?

A conference can be known for:

  • The best party” – take undefeated SQLBits in the UK with its ever changing theme-parties.
  • The cool(est) location
    If the host-city is already famous, it helps a lot – if you pick a location that is somewhat typical or representative for the city.
    Some examples would be: SQL Saturday Cambridge, SQL Saturday Malaysia/Kuala Lumpur – in the famous Petronas towers, SQL Saturday Singapore – overlooking the famous Marina Bay and many others.
    Oh, and SQLBits again changes the host-city every year, making it a principle to show the variety of the country to its attendees and speakers.
  • The most famous speakers
    - here the simple rule is (usually): the bigger the conference, the more attracting to world-renowned speakers. Commercially-driven conferences can gain an advantage over other similarly sized ones here though, as they can provide a fee for speakers.
    If you are unsure start by looking for speakers that have been awarded MVP (usually a sign of a lot of interaction with public, which is exactly what a conference is about) and MCM or MCSM certified professionals (a good indicator of practical experience).
  • Big(gest) choice of sessions
    - the greater the audience you want to attract, the broader the range of topics should be. And nowadays everything is somewhat connected anyways, so even an administrator can be interested in certain BI or even Data Science topics.
    Besides topics also the level of the sessions is important for a bigger audience – from beginner level to advanced level.
  • Cool prices and goodies
    - At most conferences attendees have a chance to collect some goodies or even win high-valued prices.
    I personally have never consciously seen this being advertised specifically though. To what extent this influences attendees to come back I have no clue.
  • This one is more speaker-centric: a conference can be known among speakers for “being a great host” – by providing a special program for speakers. For example SQLSaturday Portugal (the first SQL Saturday outside US btw), is known for a superb crew of volunteers that even gave us a special tour to some famous locations around Lisbon – “private sightseeing tour”. At SQL Gulf we were invited to a traditional Saudi Arabian Dinner – before the self-made “sightseeing-tour”. Others bring you to traditional restaurants and so on and so on. In this area one can be very creative. But locality certainly does help.
  • Having programmers and even managers from Microsoft/another vendor at location.” This slightly clumsy point is referring to the extra value that a conference can provide, when the actual programmers, program managers etc. from the targeted software are right at the conference. For most attendees this is the only opportunity to ever meet the people behind the product.
    The PASS Summit in the US is known for that. The close proximity to Redmond makes it possible. For other conferences it is much harder, but still quite a few times you can see some famous people coming from the headquarters. While size of the conference helps, having a strong connection with Microsoft definitely does help a lot if you are an organizer.

4)

Die Konferenz mag zwar keinen Gewinn erzielen, aber, einfach ausgedrückt, „Bekanntheit“. Bekanntheit, die zu mehr Öffentlichkeit führt. Und das allein kann die Anstrengung wert sein sowohl für eine kommerzielle als auch eine nichtkommerziell geführte Konferenz.

Welche Art von Bekanntheit könnte das sein, außer „die größte“ zu sein?

Eine Konferenz kann bekannt sein für:

  • Die beste Party“ – nehmt die ungeschlagenen SQLBits in Großbritannien mit seinen immer wechselnden Themen-Partys.
  • Die cool(st)e Location
    Wenn sie in einer bekannten Gastgeberstadt stattfindet, hilft es sehr – solange man eine Location auswählt, die zumindest typisch oder repräsentativ für die Stadt ist.
    Einige Beispiele wären: SQL Saturday Cambridge, SQL Saturday Malaysia/Kuala Lumpur – in den berühmten Petronas-Towers, SQL Saturday Singapore – mit Ausblick auf die berühmte Marina Bay – und viele andere. Oh, und SQLBits wiederum wechselt jedes Jahr die Gastgeberstadt und macht es sich zum Prinzip, seinen Teilnehmern und Sprechern die Vielfalt des Landes zu zeigen.
  • Die bekanntesten Sprecher“ – hier ist die einfache Regel (normalerweise): je größer die Konferenz, desto attraktiver für weltbekannte Sprecher. Kommerziell geführte Konferenzen können hierbei allerdings einen Vorteil gegenüber ähnlich großen Konferenzen haben, da sie den Sprechern ein Honorar bieten können.
    Wenn ihr unsicher seid, könnt ihr zunächst nach Sprechern Ausschau halten, die mit dem MVP Award ausgezeichnet worden sind (meistens ein Zeichen von viel öffentlicher Interaktion, also genau das, was eine Konferenz ausmacht) und MCM oder MCSM zertifizierten Profis (ein gutes Zeichen für praktische Erfahrung).
  • Große/größte Auswahl an Sessions“ – je größer das Publikum, das man anziehen möchte, je größer sollte die Auswahl an Themen sein. Und heutzutage ist sowieso alles irgendwie miteinander verbunden, so dass sogar ein Administrator an bestimmten BI oder selbst Data Science-Themen interessiert sein kann. Neben den Themen ist auch die Könnerstufe der Sessions wichtig für ein größeres Publikum – von der Anfängerstufe bis zur Fortgeschrittenen-Stufe.
  • Coole Preise und Goodies
    - Bei den meisten Konferenzen haben die Teilnehmer die Gelegenheit, einige Goodies einzusammeln oder sogar wertvolle Preise zu gewinnen.
    Ich persönlich habe es allerdings noch nie als speziell beworben gesehen. Inwieweit dies die Teilnehmer beeinflusst wiederzukommen, weiß ich nicht.
  • Das hier ist mehr Sprecher-zentriert: eine Konferenz kann unter Sprechern als „ein toller Gastgeber“ bekannt sein– indem ihnen ein spezielles Programm bietet. Zum Beispiel ist die SQLSaturday Portugal (übrigens die erste SQLSaturday außerhalb der USA) für seine ausgezeichnete Mannschaft an ehrenamtlichen Helfern bekannt, die uns sogar eine besondere Tour zu einigen berühmten Orten in Lissabon gegeben hatten – eine „private Sightseeing-Tour“. Bei SQL Gulf wurden wir zu einem traditionellen saudi-arabischen Abendessen eingeladen – vor der von ihnen selbst organisierten Sightseeing-Tour. Andere führen einen in traditionelle Restaurants und so weiter und so fort. In diesem Bereich kann man sehr kreativ sein. Aber die Lokalität hilft auf jeden Fall.
  • „Programmierer und sogar Manager von Microsoft/anderem Anbieter vor Ort haben“. Dieser etwas umständliche Punkt bezieht sich auf den zusätzlichen Wert, den eine Konferenz schaffen kann, wenn die tatsächlichen Programmierer, Programmmanager etc. der angezielten Software auf der Konferenz mit dabei sind. Für die meisten Teilnehmer ist dies die einzige Gelegenheit, die Leute hinter dem Produkt überhaupt je zu treffen.
    Der PASS Summit in den USA ist dafür bekannt. Die Nähe zu Redmond macht dies möglich. Für andere Konferenzen ist es viel schwieriger, aber dennoch kann man ab und an einige berühmte Leute von den Headquartern sehen. Die Größe der Konferenz allein ist zwar hilfreich, doch es hilft auf jeden Fall, als Organisator eine starke Verbindung zu Microsoft zu haben.

 Successful_Tech_Conferences_SQLBits2016_Party

„Space Party“ at SQL Bits 2016 - check out the dancing Darth Vader in the background :-D

 Successful_Tech_Conferences_SQLKonferenz2016_Channel9Video

Video shooting at SQL Konferenz 2016 with Joachim Hammer, Head of the Security-Team for relational Engines from Redmond (right), Tobiasz Koprowski from UK (middle) and myself – Video available at Channel 9: “Let's Talk SQL Server 2016 Security

volunteers are key

5)

And yet there is more. It is something that is not measurable in numbers or can be put in simple words: I will call it “atmosphere” in an attempt to give it a simple descriptive name. It consists of the certain spirit that you feel at the conference. How the people interact, how attendees approach speakers and vice versa. It is hard to describe, but when you have been at several conferences you will sense the difference.
Consequently this is also the aspect that can be influenced the least by an organizer. At least this is my feeling. By my observation the relative amount of volunteers are an indicator of a highly positive atmosphere. So maybe I should say: “volunteers are key” to a successful conference.

An ambitious crew of positive-thinking crew that puts its heart in the work for the conference can make a huge difference and give the conference the right impulse towards a successful conference with attendees and speakers wanting to come back.

And until someone proves me wrong I will leave this as a thesis :-)

- How do you find volunteers? Well, hopefully they will find you. ;-)

Besides obviously your local user groups it can help to talk to your local university’s technology/IT information department.

Small side note: As a university dropout (Japanese & British studies, for those who are curious – computer science, at least in terms of substance, was no good back then…) I would have never imagined to ever be giving presentations in a university auditorium. Yet this is exactly what I did a couple of years ago at SQLSaturday Rheinland/Germany at the Bonn-Rhein-Sieg University and, only recently, in the framework of SQLGulf, at the Alfaisal University in Riadh.

Unfortunately that is as much as I can say about finding volunteers. During my travels I did realize that there can be huge differences between countries in terms of volunteers. Why that is, is hard to tell and certainly a complex matter. I did find the volunteers outstanding at the SQL Server Geeks Conference in India and also in Portugal in terms of sheer number and positive energy. If you ever make it there, you will know what I mean.

5)

Und es gibt trotzdem noch etwas. Es ist etwas, das sich nicht in Zahlen messen oder einfachen Worten ausdrücken lässt: Ich nenne es mal „Atmosphäre“ im Versuch, ihm einen einfachen, anschaulichen Namen zu geben. Es besteht aus der bestimmten Stimmung, Atmosphäre, die man auf der Konferenz spürt. Wie die Leute miteinander interagieren, wie die Teilnehmer die Sprecher ansprechen und umgekehrt. Es ist schwer zu beschreiben, aber wenn man einmal auf mehreren Konferenzen gewesen ist, wird man den Unterschied spüren.

Demnach ist dies auch der Aspekt, der am wenigstens vom Organisator beeinflusst werden kann. Das ist jedenfalls mein Empfinden. Nach meiner Beobachtung ist die relative Zahl an ehrenamtlichen Helfern ein Indiz für eine äußerst positive Atmosphäre. Also sollte ich vielleicht sagen: „Ehrenamtliche Helfer sind wesentlich“ für eine erfolgreiche Konferenz.

Eine ambitionierte, positiv-denkende Mannschaft, die ihr Herz in die Arbeit für die Konferenz steckt, kann einen großen Unterschied machen und der Konferenz den richtigen Impuls hin zu einer erfolgreichen Konferenz geben, zu der die Teilnehmer und Sprecher zurückkommen wollen.

Und bis jemand beweist, dass ich falsch liege, lasse ich es als meine These stehen :-)

-Wie findet man ehrenamtliche Helfer? Nun, hoffentlich finden sie Euch. ;-)

Neben euren örtlichen Nutzergruppen (naheliegend) kann es hilfreich sein, mit der IT-Fakultät eurer örtlichen Universität zu sprechen.

Kleine Randnotiz: Als Studienabbrecher (Japanologie & Anglistik für wen es interessiert – Informatik taugte inhaltlich so rein gar nichts damals…) hätte ich nie gedacht, jemals im Auditorium einer Hochschule Vorträge zu halten. Und genau das habe ich dann vor einigen Jahren auf dem SQLSaturday Rheinland/Germany in der Hochschule Rhein-Sieg und kürzlich, im Rahmen von SQLGulf sogar in der Alfaisal Universität in Riad.

Leider ist das schon alles, was ich zum Thema Freiwillige finden sagen kann. Während meiner Reisen habe ich festgestellt, dass es große Unterschiede zwischen Ländern geben kann, was ehrenamtliche Helfer angeht. Warum das so ist, ist schwer zu sagen und sicherlich eine komplexe Materie. Ich fand die ehrenamtlichen Helfer auf der SQL Server Geeks-Konferenz in Indien und auch die in Portugal einfach umwerfend, nicht nur von der reinen Zahl her, sondern auch von der positiven Energie. Wenn Sie es einmal dahin schaffen, dann werden Sie wissen, was ich meine.

Successful_Tech_Conferences_SQLServerGeeksConference2016

Attendees, Speakers, organizers and volunteers at SQL Server Geeks Conference 2016 in Bangalore, India

Note that I do not include the point: being “the best conference”. As there are so many possibilities to host a great conference, accomplishing all at the same time seems rather impossible.

Beachtet, dass ich hier nicht den Punkt, „die beste Konferenz“ zu sein, aufführe. Da es so viele Möglichkeiten gibt, eine tolle Konferenz zu halten, scheint es eher unmöglich, alles auf einmal zu vollbringen.

 Successful_Tech_Conferences_SQLGulf3_GroupPic

Speakers and Organizers at SQL Gulf 2016 in Riadh, Saudi Arabia

“the most precious thing we all have to offer is time”

Finally: One may ask: “What do I look for as a speaker?”

Now, I cannot deny that being at conferences is also “work”. Even if I love my job and look forward to presenting, I do have to justify it from business aspects as well. Luckily, being the owner of Sarpedon Quality Lab, I allow myself a few exceptions and a relaxed attitude towards conference-attendance.
“You gotta love what you do, so why not work also just for the joy of it. At least sometimes…”
Therefore, at most community-driven events the reason why I just like to be there is because I love the community, the people around me: organizers, volunteers, co-speakers and attendees.
What’s more, there is little that gives such positive energy and boost like an actively interested audience does.

So the answer to the question as to which conferences I prefer would be: the ones with the best atmosphere und the most interactivity with the attendees. After all, the most precious thing we all have to offer is time. And a conference is a great opportunity to spend it with many people with similar interests.

Und schließlich: Man mag fragen: “Wonach suche ich als Sprecher?

Jetzt kann ich nicht bestreiten, dass auf Konferenzen zu sein auch „Arbeit“ ist. Obwohl ich meine Arbeit liebe und ich mich aufs Präsentieren freue, muss ich es auch aus geschäftlichen Aspekten rechtfertigen. Zum Glück kann ich mir, als Inhaber von Sarpedon Quality Lab, einige Ausnahmen und eine entspannte Einstellung gegenüber Konferenz-Teilnahmen erlauben.
„Man sollte seine Arbeit schon lieben, also warum nicht, wenigstens hin und wieder, einfach aus Spaß daran arbeiten...“
Was mir über die Jahre klargeworden ist, ist, dass ich einfach gern auf einigen Konferenzen bin, weil ich die Community: die Leute um mich herum, die Organisatoren, ehrenamtliche Helfer, Co-Sprecher und Teilnehmer dort sehr mag.
Außerdem gibt es wenig, das einem solch positive Energie und Schub gibt wie ein aktiv interessiertes Publikum.

Daher wäre die Antwort, welche Konferenzen ich vorziehe: diejenigen mit der besten Atmosphäre und der besten Interaktivität mit Teilnehmern.
Denn Zeit ist das wertvollste, das wir alle anzubieten haben. Und eine Konferenz ist eine tolle Gelegenheit, sie mit vielen Leuten mit ähnlichen Interessen zu teilen.

 Successful_Tech_Conferences_SQLSaturday_Singapore2016

View from the Microsoft office Singapore, the Location of SQLSaturday Singapore 2016

From the an attendee's point of view things like “many sessions to choose”, “well known top experts to meet”, “being able to interact with speakers and get answers to technical questions”, “connecting with fellow professionals” and “getting in touch with interesting companies (potentially new employers)” will probably be among the most important reasons.

Aus Sicht eines Teilnehmers sind vermutlich Dinge wie „viele Sessions zur Auswahl, „bekannte Top Experten treffen können“, „mit Sprechern in Kontakt kommen und Antworten auf technische Fragen zu erhalten“, „sich mit anderen Profis zu vernetzen“ und „mit interessanten Firmen (potentiellen neuen Arbeitgebern) in Kontakt zu kommen“ unter den wichtigsten Gründen sein.

Successful_Tech_Conferences_SQLSaturday_Malaysia2015

View from the Microsoft office Kuala Lumpur, the Location of SQLSaturday Malaysia 2015

 

Disclaimer: If I have not mentioned your conference, do not take it as a negative. I have simply been at too many (over 50 conferences in more than 12 countries) and on the other hand certainly not enough to make a scientific statement. I tried to give only a few examples for certain criteria. I am not doing an overall “rating of conferences”.

Disclaimer: Wenn ich Eure Konferenz nicht erwähnt habe, fasst es nicht negativ auf. Ich bin einfach auf zu vielen gewesen (über 50 Konferenzen in mehr als 12 Ländern) und andererseits sicherlich nicht genügend, um eine wissenschaftliche Aussage fällen zu können. Ich habe nur einige Beispiele für bestimmte Kriterien zu geben versucht und mache hier keine Gesamt-„Bewertung von Konferenzen“.

 Successful_Tech_Conferences_SQLGulf3_University

Alfaisal University in Riadh, the Location of SQL Gulf 2016

 

Question to my readers:

What do YOU think?
Why do you go to conferences, why do you speak at conferences, why do you organize conferences?

I am curious to hear your answers which may be slightly different for each of you.

Frage an meine Leser:

Was denkst DU?

Warum gehst Du auf Konferenzen, warum sprichst Du auf Konferenzen oder warum organisierst Du Konferenzen?

Ich bin neugierig auf Eure Antworten, die für jeden leicht unterschiedlich sein mögen.

 Successful_Tech_Conferences_MCSHA2016

Attendees at German SQL Server Master-Class “High Availability” 2015

Cu next conference

Andreas

Successful_Tech_Conferences_PASS_Summit2015_MCMs

The MCMs at the PASS Summit 2015

Philipp Lenz: PowerPivot: Calculate Subtotals with Earlier

Anbei eine kurze Erklärung wie man mit PowerPivot/DAX Zwischensummen erstellt um u.a. Trends zu erzeugen: Beschreibung
Beispieldatei: Daten

Robert Panther: Neues Buch zur Performance-Analyse von SQL Servern

Vor wenigen Tagen ist bei entwickler.press mein neues Buch zur Analyse von SQL Server Performanceproblemen erschienen. Darin wird für alle Versionen bis einschließlich SQL Server 2016 in kompakter Form dargestellt, was zu prüfen ist, und wo mit möglichst geringem Aufwand schnell eine Verbesserung der Performance erzielt werden kann. In separaten Abschnitten des Textes wird ebenso darauf eingegangen, wie eine ausführliche Performanceanalyse aussehen und welche langfristigen Wartungsmaßnahmen man ergreifen kann, um die Datenbankperformance auch dauerhaft auf einem guten Level zu halten.

Das Buch ist bewusst kompakt gehalten und bildet eine auf Performance-Analyse konzentrierte (aber auch aktualisierte und um neue Inhalte ergänzte) Teilausgabe meines – ebenfalls bei entwickler.press erschienenen – SQL Performance-Ratgebers.

sql-server-performanceanalyse_2400x_rgb-220x352

Robert Panther
SQL Server
Performanceprobleme analysieren und beheben
entwickler.press
132 Seiten (Softcover)
ISBN: 978-3-86802-162-2
Preis: € 12,90 inkl. MwSt.

Alternativ auch als eBook erhältlich:

PDF-ISBN:  978-3-86802-349-7
PDF-Preis:  9,99 €

EPUB-ISBN:  978-3-86802-686-3
EPUB-Preis:  9,99 €

Weitere Infos:
Buchinfo bei entwickler.press
Infos & Bestellmöglichkeit bei Amazon.de

 


Dirk Hondong: Den PlanExplorer gibt es nun komplett gratis

Hallo liebe Community,

genau, Ihr habt richtig gelesen. Ich bin gerade darüber gestolpert.

Der Plan Explorer von SQL Sentry ist gestern in der Version 3.0 erschienen und beinhaltet nun auch die „Pro“-Features wie zum Beispiel mehrere Tabs an Abfrageplänen zu öffnen, Deadlocks visualisieren und und und….

Jeder, der irgendwie mit Query Tuning zu tun hat, kommt jetzt um dieses Werkzeug bestimmt nicht mehr herum.

Hier geht es zur Produktseite:

http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view

 

/EDIT: An der Stelle muss ich auch noch mal ein kleines Sorry in Richtung SQLSentry aussprechen, da ich vor dem „big announcment“ schon über PE 3.0 gebloggt habe.

/EDIT zum 2: Hier ist nun auch das Announcment von Greg Gonzales, CEO von SQLSentry: http://blogs.sqlsentry.com/greggonzalez/plan-explorer-pro-goes-free/


Robert Panther: Ankündigung: BASTA! 2016

BASTA_2016_Speakerbutton_ContendAd_36103_v1 (1)Auch auf der diesjährigen BASTA! in Mainz bin ich wieder mit einem eigenen Vortrag aus dem SQL Server-Umfeld dabei.
Und zwar werde ich am Donnerstag, dem 22.09.2016 von 11:30 bis 12:45 die performance-relevanten Features von des neuen SQL Server 2016 näher vorstellen.
Dabei werden neben den Neuerungen im Bereich Columnstore Indizes und In-Memory-Storage auch der neue Query Store und die Live Query Statistics sowie zahlreiche kleinere Neuerungen behandelt.

Details zum Vortrag: https://basta.net/session/sql-server-2016-performance-features

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

 


Bernd Jungbluth: Neue SQL Server-Seminare im November

Im November biete ich wieder meine Seminare rund um das Thema SQL Server an.

08.11.2016 - Migration Access nach SQL Server

09.11.2016 - Datenbankentwicklung mit SQL Server

10.11.2016 - SQL Server Data Tools  *** Neues Seminar ***

15. und 16.11.2016 - SQL Server Integration Services

17.11.2016 - SQL Server Reporting Services

Die Seminare finden im Hotel Ebertor in Boppard am Rhein statt.

Die Teilnahme an einem Seminar kostet 375 Euro pro Tag und Person zzgl. MwSt.
Speisen und Getränke sind im Preis enthalten.

Die Teilnehmerzahl ist auf 8 Personen begrenzt.

Für eine Anmeldung reicht eine kurze E-Mail an workshop@berndjungbluth.de

Uwe Ricken: HEAPS in Verbindung mit DELETE-Operationen

In einem Projekt wurde den Entwicklern gesagt, dass man grundsätzlich mit Heaps arbeiten solle, da durch die Verwendung eines Clustered Index viele Deadlocks verursacht worden sein. Daraufhin hat man für fast alle Tabellen in der Datenbank die geclusterten Tabellen wieder zu Heaps konvertiert. Die Deadlocks sind laut Aussage vollkommen verschwunden – jedoch hat man ein paar Dinge nicht beachtet, die sich nun nachteilig auf die Performance auswirken; und es sind nicht fehlende Indexe gemeint!

Einleitung

Ich persönlich favorisiere Heaps sofern es möglich ist; insbesondere DWH-Szenarien sind prädestiniert für Heaps. Meine Meinung über Heaps habe ich grundsätzlich überdacht, nachdem ich die Artikel “Unsinnige Defaults: Primärschlüssel als Clusterschlüssel” von Markus Wienand und Thomas Kejser “Clustered Index vs. Heap” gelesen habe. Grundsätzlich bieten Clustered Indexe in SQL Server sehr viele Vorteile (insbesondere in der Maintenance); jedoch sollte man nicht immer den “pauschalen” Empfehlungen folgen. Von daher empfehle ich meinen Kunden immer wieder mal, auch über die Alternative HEAP nachzudenken. Die Alternative muss aber – wie im vorliegenden Fall – gründlich überlegt sein und immer von drei Seiten betrachtet werden:

  • Analyse der Workloads
  • Analyse der SELECT-Statements
  • Analyse der Maintenance (Indexfragmentierung, Forwarded Records, DELETE und UPDATE-Verhalten)

Wenn alle Bedingungen “passen”, steht der Verwendung von Heaps nichts mehr im Wege. Im Vorliegenden Fall hat man leider nur die Option 2 im Blick gehabt und dabei vollkommen ausgeblendet, dass in der betroffenen Tabelle sehr viele Aktualisierungs- und Löschvorgänge stattfinden.

Testumgebung

Für die aus dem obigen Fall entstandenen Probleme wird eine Tabelle verwendet, die pro Datenseite einen Datensatz speichert. Diese Tabelle wird mit 20.000 Datensätzen gefüllt.

-- Create a HEAP for the demo
CREATE TABLE dbo.demo_table
(
    Id    INT        NOT NULL    IDENTITY (1, 1),
    C1    CHAR(8000) NOT NULL    DEFAULT ('Das ist nur ein Test')
);
GO
 
-- Now we fill the table with 20,000 records
SET NOCOUNT ON;
GO
 
INSERT INTO dbo.demo_table WITH (TABLOCK) (C1)
SELECT  TOP 20000
        text
FROM    sys.messages;

Sobald die Tabelle befüllt ist, sind insgesamt (inklusive IAM-Seite) 20.001 Datenseiten im Buffer Pool des SQL Servers.

-- what resource of the table dbo.demo_table are in the buffer pool now!
;WITH db_pages
AS
(
    SELECT  DDDPA.page_type,
            DDDPA.allocated_page_file_id,
            DDDPA.allocated_page_page_id,
            DDDPA.page_level,
            DDDPA.page_free_space_percent,
            DDDPA.is_allocated
            sys.dm_db_database_page_allocations
            (
                DB_ID(),
                OBJECT_ID(N'dbo.demo_table', N'U'),
                NULL,
                NULL,
                'DETAILED'
            ) AS DDDPA
)
SELECT  DOBD.file_id,
        DOBD.page_id,
        DOBD.page_level,
        DOBD.page_type,
        DOBD.row_count,
        DOBD.free_space_in_bytes,
        DP.page_free_space_percent,
        DP.is_allocated
FROM    sys.dm_os_buffer_descriptors AS DOBD
        INNER JOIN db_pages AS DP ON
        (
            DOBD.file_id = DP.allocated_page_file_id
            AND DOBD.page_id = DP.allocated_page_page_id
            AND DOBD.page_level = DP.page_level
        )
WHERE   DOBD.database_id = DB_ID()
ORDER BY
        DP.page_type DESC,
        DP.page_level DESC,
        DOBD.page_id,
        DOBD.file_id;

DM_OS_BUFFER_DECRIPTORS_01

Jede Datenseite des Heaps ist nahezu vollständig gefüllt. Nun werden 1.000 Datensätze aus der Tabelle mittels DELETE gelöscht.

-- Now we delete half of the records
SET ROWCOUNT 2000;
DELETE  dbo.demo_table
WHERE   Id % 2 = 0;

Die Analyse des Bufferpools zeigt das Dilemma beim Löschen von Datenseiten aus einem Heap…

DM_OS_BUFFER_DECRIPTORS_02

Entgegen der Erwartung, dass leere Datenseiten automatisch wieder an die Datenbank zurückgegeben (deallokiert) werden, stellt sich die Situation so dar, dass die leeren Datenseiten weiterhin von der Tabelle allokiert sind und im Buffer Pool vorhanden sind. Jede zweite Seite aus dem gezeigten Beispiel ist leer und beim Laden der Daten aus der Tabelle werden diese leeren Datenseiten mit in den Buffer Pool geladen. bei 1.000 Datensätzen macht das immerhin 8 MByte aus.

Lesen von Datenseiten im Heap

Wird ein Heap verwendet, so können Daten nur mit einem Table Scan ermittelt werden. Ein Table Scan bedeutet, dass immer die vollständige Tabelle gelesen werden muss und anschließend die gewünschten Datensätze aus der Ergebnismenge gefiltert werden müssen.

SELECT * FROM dbo.demo_table WHERE Id = 10 OPTION (QUERYTRACEON 9130);

Der Beispielcode generiert folgenden Ausführungsplan. Das TF 9130 wird verwendet, um den FILTER-Operator im Ausführungsplan sichtbar zu machen.

Execution_Plan_01

Faktisch ist ein Heap auf Grund seiner Definition nicht mit einem Ordnungskriterium versehen. Aus diesem Grund verhält sich der Heap wie ein Puzzle und jeder einzelne Stein muss verglichen werden, bis die gewünschten Steine gefunden werden. Ein Heap hat eine weitere Besonderheit, die es in einem Index nicht gibt; die einzelnen Datenseiten haben keine unmittelbare Verbindung zueinander.

HEAP Structure

Eine Datenseite in einem Heap ist isoliert und nur durch die IAM-Seite (Index Allocation Map) werden die vielen autarken Datenseiten miteinander in Verbindung gebracht. Da Microsoft SQL Server beim Lesen von Seite 110 z. B. nicht weiß, welche Seiten außerdem zur Tabelle gehören, muss – im Vorfeld – die IAM Datenseite gelesen werden. Auf dieser Seite stehen alle Datenseiten (max. 64.000), die zur Tabelle gehören. Mit dieser Information kann Microsoft SQL Server anschließend die einzelnen Datenseiten lesen. Diese Technik nennt sich Allocation Scan.

Löschen von Datensätzen in Heap

Wenn man versteht, dass ein Heap kein Ordnungskriterium besitzt, kann man sich vorstellen, was passieren würde, wenn während eines Löschvorgang von Transaktion 1 ein weiterer Vorgang Daten der Tabelle lesen möchte.

Transactions-Concurrency

In der Abbildung laufen zwei Transaktionen zeitversetzt. Transaktion 1 (T1) führt einen Löschvorgang aus während Transaktion 2 (T2) mittels SELECT die Daten für einen anderen Prozess liest. Auf Grund der Strukturen eines Heap müssen beide Prozesse zunächst die IAM Seite lesen. Sobald die IAM Seite gelesen wurde, können beide Prozesse mit dem sequentiellen Durchlaufen der Tabelle beginnen. Würde T1 die Daten und die Datenseite 36 löschen, würde T2 in einen Fehler laufen. Da T2 bereits die IAM Seite gelesen hat, weiß der Prozess, dass er die Datenseite 36 lesen muss. Die wäre aber bei einer Deallokation nicht mehr vorhanden! Aus diesem Grund belässt Microsoft SQL Server die Datenseite als allokierte (aber leere) Datenseite in der Struktur der Tabelle. Unabhängig davon, ob ein weiterer Prozess auf die Tabelle zugreift, ist dieses Verhalten das Standardverhalten von Microsoft SQL Server.

Deallokieren von leeren Datenseiten

Um leere Datenseiten wieder an die Datenbank zurückzugeben, gibt es vier Möglichkeiten:

  • Verwendung von Clustered Index statt Heap
  • Neuaufbau des HEAP mit ALTER TABLE
  • Löschen von Datensätzen mit einer exklusiven Tabellensperre
  • Lock Escalation beim Löschen von großen Datensatzmengen

Während Option 1 und 2 selbsterklärend sind, zeigen die nachfolgenden Beispiele, wie es sich mit exklusiven Sperren verhält:

Löschen von Datensätzen mit exklusiver Tabellensperre

Die einfachste Methode, Datensätze aus einem Heap zu löschen und den allokierten Platz wieder freizugeben, besteht darin, die Tabelle während des Löschvorgangs exklusiv zu sperren.  Der Nachteil dieses Verfahrens liegt jedoch darin, dass ein solches System nicht mehr skalieren kann. Solange ein Löschvorgang durchgeführt wird, können anderen Prozesse nicht auf die Tabelle zugreifen (Lesend und/oder Schreibend). Einzig mit Hilfe der optimistischen Isolationsstufe “READ COMMITTED SNAPSHOT ISOLATION” lässt sich der Lesevorgang unter intensiver Verwendung von Systemressourcen bewerkstelligen; dieses Verfahren soll jedoch in diesem Artikel nicht näher beleuchtet werden.

-- Now we delete 2000 records
BEGIN TRANSACTION;
GO
    DELETE dbo.demo_table WITH (TABLOCK)
    WHERE  Id <= 2000;

Sobald der Code durchgelaufen ist, sind von den ursprünglichen 20.000 Datenseiten nur noch 18.000 Datenseiten vorhanden.

-- what pages have been allocated by the table
SELECT  DDIPS.index_id,
        DDIPS.index_type_desc,
        DDIPS.page_count,
        DDIPS.record_count
FROM    sys.dm_db_index_physical_stats
(
    DB_ID(),
    OBJECT_ID(N'dbo.demo_table', N'U'),
    0,
    NULL,
    N'DETAILED'
) AS DDIPS

DM_DB_INDEX_PHYSICAL_STATS_01

Microsoft SQL Server kann mit Hilfe einer exklusiven Sperre auf der Tabelle gefahrlos die Datenseiten deallokieren, da während des Löschvorgangs kein Prozess auf die IAM-Seite zugreifen kann.

Transactions-Concurrency-X-Lock

Transaktion 1 (T1) beginnt mit dem Löschvorgang und setzt zunächst eine exklusive Sperre auf die Tabelle (in diesem Fall auf die IAM-Datenseite). Solange der Löschvorgang andauert, wird diese exklusive Sperre auf der Tabelle belassen. Während dieser Zeit muss Transaktion 2 (T2) warten. Sobald der Löschvorgang beendet ist, wird die Sperre auf der Tabelle aufgehoben und T2 kann (die nun aktualisierte IAM-Seite) lesen. Das Sperren der kompletten Tabelle hat zur Folge, dass Prozesse, in die die Tabelle involviert ist, nicht mehr skalieren können; die Prozesse müssen seriell bearbeitet werden.

-- output of aquired / released locks
DBCC TRACEON (3604, 1200, -1);
GO
 
-- delete 1,000 records
SET ROWCOUNT 2000;
DELETE dbo.demo_table WITH (TABLOCK)
WHERE  Id % 2 = 0;
GO
 
-- deactivate the output of locks
DBCC TRACEOFF (3604, 1200, -1);
GO

Das Codebeispiel macht die Sperren für die Transaktion sichtbar. Die nachfolgende Abbildung zeigt die gesetzten Sperren.

LOCKS_01

Der Löschvorgang fordert zunächst einen X-Lock auf die Tabelle (OBJECT: 8:245575913). Sobald die Tabellen erfolgreich gesperrt wurde, können Datenseiten und Datensätze gesperrt werden, um sie zu löschen. Durch die exklusive Sperre auf die Tabelle können keine weiteren Prozesse auf das Objekt zugreifen; die IAM ist “gesichert” und kann ohne Gefahren modifiziert werden.

Lock Escalation beim Löschen von großen Datensatzmengen

Das obige Beispiel hat gezeigt, dass man gezielt steuern kann, ob Datenseiten in einem Heap bei einem Löschvorgang wieder freigegeben werden sollen. Jedoch werden Datenseiten auch dann freigegeben, wenn die Tabelle durch den Prozess exklusiv blockiert wird. Als Lock Escalation wird der Vorgang beschrieben, bei dem viele Einzelsperren zu einer Gesamtsperre konvertiert werden. Diese Technik verwendet Microsoft SQL Server, um Ressourcen zu sparen. Der – grobe – Schwellwert für eine Sperrausweitung liegt bei 5.000 Einzelsperren. Wenn z. B. 10.000 Datensätze gelöscht werden sollen, muss Microsoft SQL Server 10.000 exklusive Sperren während der Transaktion halten. Jede Sperre konsumiert 96 Bytes. Bei 10.000 Datensätzen würden das 960.000 Bytes (~1 MB) an RAM während der Transaktion belegt werden. Aus diesem Grund wird ab einem bestimmten Schwellwert die EInzelsperre zu einer Komplettsperre (Partition oder Tabelle) eskaliert.

Das folgende Beispiel zeigt, wie sich Lock Escalation auf das Löschen von großen Datenmengen in einem HEAP auswirkt. Gegeben ist wieder die obige Tabelle mit 20.000 Datensätzen. Aus dieser Tabelle sollen – ohne weitere Tabellenhinweise – 10.000 Datensätze gelöscht werden.

DM_DB_INDEX_PHYSICAL_STATS_02

Da ein Datensatz 8 KBytes konsumiert, hat die Tabelle 20.000 Datenseiten allokiert. Um die Besonderheiten im Transaktionsprotokoll besser lokalisieren zu können, wird eine benannte Transaktion verwendet.

-- Now we delete half of the records
BEGIN TRANSACTION demo;
GO
 
DELETE dbo.demo_table
WHERE  Id % 2 = 0;
GO
 
COMMIT TRANSACTION demo;
GO

Nachdem 10.000 Datensätze gelöscht worden sind (OHNE Tabellenhinweise), stellt sich das Ergebnis der verbliebenen Datenseiten wie folgt dar.

DM_DB_INDEX_PHYSICAL_STATS_03

Die Zahl der verbliebenen Datenseiten ergibt – auf dem ersten Blick – überhaupt keinen Sinn. Die Erwartungshaltung bei diesem Löschvorgang wäre entweder alle 20.000 Datenseiten verbleiben im allokierten Zustand oder aber nur noch 10.000 Datenseiten. Die während des Löschvorgangs gesetzten Sperren stellen sich wie folgt dar:

DM_TRAN_LOCKS_01

20.000 allokierte Datenseiten – 6.876 gesperrte Datenseiten = verbliebene 13.124 Datenseiten. Die Frage, die sich in diesem Zusammenhang stellt: Warum werden 6.876 Datenseiten exklusiv gesperrt und nicht alle 10.000 Datenseiten. Ein Blick in das Transaktionsprotokoll liefert die Antworten zu diesem Verhalten.

DM_TRAN_LOCKS_02

Die erste Abbildung zeigt den Inhalt des Transaktionsprotokolls zu Beginn der Transaktion. Es ist gut zu erkennen, dass einzelne Datensätze gelöscht werden (AQUIRE_LOCK_X_RID). Da zu diesem Zeitpunkt keine exklusive Sperre auf der Tabelle liegt, verbleiben die Datenseiten weiterhin in der Tabelle.

DM_TRAN_LOCKS_03

Ab Zeile 3.126 im Transaktionsprotokoll wird es interessant. Insgesamt wurden – bis zu diesem Punkt – 3.124 Datensätze gelöscht, OHNE die Datenseiten wieder zu deallokieren! Ab Datensatz 3.125 findet eine Lock Escalation statt (Zeile 3126). Nun wird nicht mehr jede einzelne Datenzeile (RID) blockiert sondern die vollständige Tabelle (OBJECT). Wird bei gesperrter Tabelle ein Datensatz gelöscht, ist die Aktion “aufwändiger”:

  • Der Datensatz wird gelöscht (LOP_DELETE_ROWS)
  • Aktualisierung des Headers der betroffenen Datenseite (LOP_MODIFY_HEADER)
  • Freigabe der Datenseite in PFS (LOP_MODIFY_ROW | LCK_PFS)
  • Löschen der Zuordnung aus IAM (LOB_HOBT_DELTA)

Insgesamt werden die ersten Datensätze – bis zur Lock Escalation – aus den Datenseiten gelöscht ohne die Datenseiten zu deallokieren. Bei einer Lock Escalation (Tabelle wird gesperrt) werden ab diesem Moment die Datenseiten im dem Heap deallokiert.

Zusammenfassung

Heaps bieten viele Vor- und Nachteile in einem Datenbanksystem; man sollte vor der Implementierung von Heaps berücksichtigen, dass sie “pflegebedürftiger” sind als Clustered Indexe. Ein Heap reagiert in DML-Operationen komplett anders als ein Clustered Index. Ein Heap sollte daher nur verwendet werden, wenn:

  • Die Tabelle hauptsächlich Daten aufnimmt (INSERT)
    z. B. Protokoll-Tabellen
  • Die Tabelle autark ist und keine Referenz zu anderen Tabellen besitzt
  • Die Attribute des Heaps ausschließlich “Fixed Length” Attribute sind
    (Forwarded Records)

Wenn Daten aus einem Heap gelöscht werden müssen, dann muss man berücksichtigen, dass Microsoft SQL Server Datenseiten in einem Heap nicht automatisch wieder freigibt. Datenseiten werden nur deallokiert, wenn sichergestellt ist, dass die Tabelle nicht durch andere Prozesse gelesen werden kann; die Tabelle muss aslo exklusiv gesperrt sein! Durch die Bearbeitung von Tabellen mit exklusiven Sperren wird ein großer Vorteil von Microsoft SQL Server – Granularität und Concurrency – gewaltsam blockiert. Diese Besonderheiten gilt es bei der Arbeit mit Heaps zu beachten.

Herzlichen Dank fürs Lesen!

Andreas Wolter: Index-Cheat-Sheet. The complete Index-compatibility Matrix for SQL Server

(DE)
Mit dem Aufkommen der Columnstore Technologie und auch der In-Memory Technologie in SQL Server 2014 sind insgesamt 4 neue Indextypen zu den althergebrachten page-basierten hinzugekommen.

Wo es früher nur die Auswahl an „Clustered oder Non-Clustered“ (Deutsch auch „gruppiert oder nicht-gruppiert“) gab, gibt es mittlerweile auch Clustered Columnstore, Non-Clustered Columnstore, Nonclustered Hash und Nonclustered Range-Indexe.

Und seit SQL Server 2016 sind diese Indextypen untereinander noch weiter kombinierbar.
Vereinfacht ausgedrückt lassen sich nun sowohl die Page-basierten Indexe als auch die Memory-optimierten Tabellen mit Columnstore Indexen kombinieren.
Page(„Disk“)-basiert + Memory-optimiert funktioniert jedoch nicht.

(EN)
With the emergence of the Columnstore technology and the In-Memory technology in SQL Server 2014, a total of 4 new index types have been added to the conventional page-based types.

When previously there had only been the choice of “Clustered or Non-Clustered,” now there are also Clustered Columnstore, Non-Clustered Columnstore, Nonclustered Hash und Nonclustered Range-Indexes.

And since SQL Server 2016, these index types can be further combined. Simply put, now it is possible to combine both page-based indexes and memory-optimized tables with Columnstore indexes.
However, Page (“Disk”)-based + Memory-optimized does not work.

Um auf schnelle Weise sehen zu können, welche Kombination an Indexen möglich ist, habe ich eine Matrix erstellt, welche die Kombinationsmöglichkeiten darstellt.
Angereichert mit Zusatzinformationen kann man den „Index-Spickzettel“ in Form einer pdf herunterladen. Und so sieht er dann aus:

In order to quickly determine which combination in indexes is possible, I created a matrix that illustrates the combination options.

Completed with additional information, you can download the “index cheat sheet” as a PDF document. This is how it looks:

 Index Cheat-Sheet_preview

Download

Herunterladen kann man es hier bei Technet:

Download

You can download it here at Technet:

gallery.technet.microsoft.com/Index-Cheat-Sheet-The-8378ac1b

 

Insgesamt sind gibt es also mittlerweile 4 verschiedene Basis-Strukturen:

  1. Heaps
  2. Clustered Indexe page-basiert
  3. Clustered Columnstore Indexe und
  4. sogenannte varHeaps, die Struktur der memory-optimierten Tabellen.

In total, there are now 4 different basic structures:

  1. Heaps
  2. Clustered page-based indexes
  3. Clustered Columnstore indexes and
  4. so-called varHeaps, the structure of memory-optimized tables

- Nicht enthalten sind: Indexed Views, XML-Indexe und Räumliche (Spatial) Indexe, sowie die Implikationen bei Sparse-Columns.
Prinzipiell basieren auch diese alle noch auf den page-basierten b-Tree Indexen, sind durch die teilweise auf der CLR basierenden Datentypen jedoch wesentlich eingeschränkter.

- Unique Indexe habe ich nicht gesondert betrachtet, da sich diese für meine Betrachtung nicht anders als ihre Nicht-Unique Pedanten verhalten.

- Not included are: Indexed Views, XML-Indexes and spatial indexes, as well as the implications in Sparse-Columns.

Generally, the latter are all still based on the page-based b-Tree indexes, but are significantly more restricted due to the data types partially based on CLR.

- I did not consider Unique Indexes separately as for the purpose of my consideration they do not behave differently to the way their non-unique counterparts do.

Zusätzlich habe ich noch einige Grundregeln und Höchstgrenzen mit aufgenommen.

Dem aufmerksamen Leser wird dabei vielleicht auffallen, dass die maximale Anzahl der Index-Spalten in SQL Server 2016 von 16 auf 32 angehoben worden ist. – Bitte seht das aber nicht als Einladung an, diese Limit auch nur Ansatzweise auszunutzen!

In addition, I have included a few basic rules and maximum limits.

The attentive reader may notice that the maximum number of index columns in SQL Server 2016 has increased from 16 to 32. – But please do not regard it as an invitation to even attempt to exploit these limits!

Ich hoffe diese Übersichten sind eine hilfreiche Erinnerungsstütze nicht  nur für Einsteiger, sondern auch für solche, die sich nicht immer an jede Regel erinnern können. Zum Weiterlesen sind auch einige Links auf BOL hinterlegt.

I hope these overviews are a helpful mnemonic device not only for beginners but also for those who don’t always remember every single rule. Links for further readings at BOL are also listed.

Hinweis: Die Übersicht basiert auf dem aktuellen Stand der Technik: SQL Server 2016.

Bereits gegenüber SQL Server 2014 gibt es wesentliche Unterschiede, und viel mehr noch zu 2012.
Kurz zusammengefasst gab es in der Version 2012 noch keine Memory-optimierten Tabellen und Clustered Columnstore Indexe. Diese kamen erst 2014 in den SQL Server. Und erst im 2016er gibt es die Kombinationsmöglichkeiten.

Folgende Artikel gab es zu diesen Themen bereits von mir:

Note: This overview is based on the current technical state: SQL Server 2016.

There are already significant differences to SQL Server 2014, and even more to 2012.
In a nutshell, in the 2012 version, there had not been any memory-optimized tables or Clustered Columnstore indexes. They only made it into the SQL Server in 2014. And only with the 2016 SQL Server there are the combination possibilities.

The following articles on these topics I have published before:

 

  1. The SQL Server 2016 In-Memory Evolution – from row-store to columnstore to in-memory operational analytics
  2. SQL Server 2016 – the Security & Performance Release / ein Sicherheits- und Performance-Release
  3. Artikel „SQL Server 2014 – Neues Fundament“ in iX Ausgabe 5/2014, Richtigstellungen zu In-Memory OLTP und ColumnStore Indexes und warum AlwaysOn nicht gleich Always On ist / SQL Server 2014 – New Fundament” in iX Issue 5/2014, corrections in In-Memory OLTP and ColumnStore Indexes, and why AlwaysOn is not the same as Always On
  4. The Columnstore Indexes & Batch Mode Processing (R)Evolution in SQL Server 2014

 

Happy Indexing

Andreas

Robert Panther: „Fun with Spellchecking“

Gerade habe ich den Text für mein neues Buch „SQL Server – Performanceprobleme analysieren und beheben“ fertig gestellt, das Anfang September bei entwickler.press erscheinen wird. Beim finalen Check mit der Rechtschreibkorrektur meiner favorisierten Textverarbeitung sind wieder ein paar echte Stilblüten bei den Korrekturvorschlägen dabei gewesen:

Autoshrink -> Autorin (wenn schon, dann bitte doch Autor, oder?!)
Workload -> Wroclaw (vielleicht ein versteckter Hinweis auf polnische Gastarbeiter aus Breslau?)
Join -> Joint (je nachdem, wierum man ihn dreht, wird daraus ein LEFT JOINT oder ein RIGHT JOINT)
Extent -> Externat (ich kannte bisher nur den Begriff Internat, aber man lernt ja nie aus)
Schema_Id -> Schamade (man wird doch nicht gleich aufgeben wollen, oder?!)
Latch -> latsch (beides steht irgendwie für eine gewisse Form der Langsamkeit)
Hallengren -> Challenger (so schnell wird der Autor der bekannten Wartungsskripte zum Herausforderer der Datenbank)

Doch zum Schluß meine beiden Favoriten:

Rebuild -> Raubwild (so gefährlich habe ich einen Indexneuaufbau gar nicht eingeschätzt)
Filestream -> Filetsteak (na dann Mahlzeit!)


Sascha Lorenz: SQL Server In-Memory OLTP – Isolation Level Beispiele

Wie schon mal von mir erwähnt, erlebe ich bei den Teilnehmern meiner SQL Server In-Memory OLTP Workshops die meisten Schwierigkeiten bei der Verwendung der “neuen” Isolation Level. Zwar klingen für viele Entwickler die Level SNAPSHOT, REPEATABLE READ und SERIALIZABLE vertraut, nur schon bei den SQL Server “disk-based” Tabellen haben die wenigsten diese aktiv genutzt. Standard ist häufig READ COMMITED und das ist auch gut so. Die Tatsache, dass In-Memory OLTP aber ohne Sperren auskommt, fühlt sich für die meisten Entwickler erstmal befremdlich an.

Zwar haben wohl die meisten Interessierten schon mal die Microsoft PowerPoints mit einer Erklärung des Version Stores gesehen und sich gefreut, wie schön da Versionen von Zeilen nebeneinander koexistieren können. Nur wie sieht das in der Praxis für den Entwickler aus?

Der deutsche Begriff für die Technologie In-Memory OLTP “Arbeitsspeicheroptimierung” trifft es irgendwie nicht ganz. Zwar wird der Arbeitsspeicher wesentlich optimierter genutzt, dennoch klingt das im Deutschen schon unspektakulär, oder?

In-Memory OLTP wurde entwickelt, um äußerst schnelle Lösungen zu ermöglichen. Auch wenn man mittels altbekanntem T-SQL mit dieser Engine arbeitet und diese auch hybride Lösungen mit den nun  “disk-based” Tabellen (also den guten alten 8kb-lastigen Strukturen) ermöglicht, so ist es doch eine durch und durch neue Engine. Und vieles was die gute alte Engine an Schlamperei bei der Programmierung einfach mal geschluckt hat, funktioniert nun nicht mehr. Dafür ist In-Memory OLTP einfach zu spezialisiert auf Geschwindigkeit. Spontane Flexibilität wird da schwierig.

Zur Erinnerung: Isolation Level bestimmen in der Welt der pessimistischen Level wie lange ein Shared Lock auf einem Datensatz oder gar auf einen Bereich aufrecht erhalten wird. Wenn ein Datensatz exklusiv gesperrt ist, dann kann er nicht gelesen und erst recht nicht geändert werden. Also, es kommt in einer Transaktion ein SELECT daher und verhindert durch den verwendeten Isolation Level, dass andere Transaktionen Daten ändern oder ergänzen können. Die Transaktion mit der Änderung (UPDATE, DELETE usw.) bleibt einfach hängen (Blocking genannt…), da ja kein rankommen an die Daten ist. Ist ja ein Shared Lock drauf. 

In der optimistischen Welt der In-Memory OLTP Engine ist das alles anders. Und ja, auch schon früher konnte man mit optimistischer Isolation arbeiten, nur haben das die wenigsten Entwickler genutzt. Die Wahl hat man nicht mehr, wenn man wenn es nun “ein wenig” schneller haben möchte.

Durch den Version Store von In-Memory OLTP können nun mehrere Versionen einer Datenzeilen parallel existieren und jede Transaktion sieht die für sich gültigen Zeilen. Liest sich doch ganz einfach, oder? Wie ist das jetzt aber bei Änderungen? Und wenn jeder seine “Wahrheit” sieht, wer hat dann am Ende recht?

Abgesehen von zwei Ausnahmen (Wobei es sich eigentlich nur um eine handelt…), lässt der SQL Server erstmal alle Änderungen zu. Erst beim COMMIT einer Transaktion wird geprüft, ob es Abhängigkeiten gibt, welche einen Fehler auslösen müssen. Und, um es nun noch ein wenig interessanter zu gestalten, im Gegensatz zum pessimistischen Modell, können nun SELECTs im Nachhinein ungültig sein.

Gehen wir ein paar Beispiele einfach mal durch.

Hier sehen wir, dass ein einfaches SELECT problemlos läuft. Nichts anderes haben wir erwartet, oder?

image

Was ist das? Sobald wir eine explizite Transaktion aufmachen und ein SELECT ausführen, gibt es einen Fehler.

image

Meldung 41368, Ebene 16, Status 0, Zeile 5
Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT).

Wir lernen aus der Fehlermeldung, dass wir einen Isolation Level benötigen, wenn wir mit transaktionalen Klammern arbeiten. In den folgenden Beispielen setze ich daher einen Table Hint (WITH SNAPSHOT).

Und schon funktioniert unsere einfache Abfrage.

image

Kommen wir zu spannenderen Dingen. Wir öffnen eine weitere Session und führen ebenfalls ein SELECT in einer Transaktion aus. Wichtig hierbei, wir schließen diese noch nicht.

image

Probieren wir mal ein einfaches UPDATE.

image

Ups, ja klar. Ein UPDATE muss ja auch lesen was es so löschen möchte. Also brauchen wir auch hier einen Isolation Level.

Und schon funktioniert das UPDATE in Session 63. Als wir versuchen die selbe Zeile zu ändern, laufen wir in einen Fehler. In der pessimistischen Welt wären wir hier hängen geblieben.

image

Meldung 41302, Ebene 16, Status 110, Zeile 34
The current transaction attempted to update a record that has been updated since this transaction started. The transaction was aborted.

Wir lernen daraus, dass die SQL Server In-Memory OLTP Engine aufpasst, dass wir nichts überschreiben, was in einer anderen Session bereits geändert wurde.

Bei einem DELETE hätten wir übrigens den gleichen Fehler erhalten.

So, kommen wir zu einem INSERT.

Erst machen wir in 63 ein INSERT mit der ID 4 und dann ein INSERT in 62 mit der ID 4. Und schon haben wir zwei Zeilen mit der identischen ID. Autsch.

image

Nun kommt es darauf an, wer zuerst ein COMMIT ausführt.

image

Und wir sehen, dass es nur einen geben konnte (Sorry…). Dennoch gab es einen Moment in der Vergangenheit, in dem mindestens zwei Wahrheiten existierten. Damit sollte der Entwickler umgehen lernen.

Und wo wir gerade bei zwei Wahrheiten sind. Können wir unsere zwei Versionen einer Zeile jeweils auch ändern?

image

Nein, das schlägt auch gleich fehl.

Wie schaut es noch generell mit DELETES aus?

Wir haben wieder zwei Sessions. Beide zeigen das gleiche Resultset.

image

Nach einem DELETE in 63 verschwindet dort unsere ID 4. In Session 62 ist diese aber noch dabei, da diese auf eine alte Version der Zeile zeigt.

image

Das bleibt so lange, wie die Session 62 ihre Transaktion nicht schließt. Ein COMMIT in 63 hilft da nicht weiter.

image

Erst das Beenden der Transaktion in 62 zeigt auch hier die letzte wahrhaftige Wahrheit. Auch dessen sollte sich ein Entwickler bewusst sein.

image

Nun kommen wir zu den wirklich interessanten Dingen. Steigern wir den Isolation Level und gehen auf REPEATABLEREAD. Bitte dran denken, dass die Verwendung der höheren Level unter Last auch messbar wird!

Wir starten wieder von vorne. In 63 öffnen wir eine Transaktion und lesen unsere Tabelle. In 62 führen wir ein UPDATE durch. Die Transaktion sparen wir uns, damit entfällt auch die Notwendigkeit für einen Table Hint.

image

Nun haben wir in 63 die alte Version und in 62 sehen wir schon die neue Version.

image

Unsere Transaktion liest im Step 21 aber fleißig wiederholt unsere Tabelle. Und bekommt den alten Stand geliefert.

image

Und nun wird es unangenehm. Ein COMMIT in der Session 63 führt zu einem Fehler. Aber wir haben doch nur gelesen?! Warum?

Weil die SQL Server In-Memory OLTP Engine ERST beim COMMIT eine Überprüfung durchführt, ob die ISOLATION LEVEL denn auch eingehalten worden sind. Wie schon erwähnt, kann also ein SELECT im Nachhinein fehlschlagen. Der Entwickler hat auf Basis der Daten gearbeitet, muss sich aber bewusst sein, dass er evtl. schon in einer alten Realität agierte und ihm sein Code um die Ohren fliegt.

image

Meldung 41305, Ebene 16, Status 0, Zeile 96
The current transaction failed to commit due to a repeatable read validation failure.

Wie schaut es mit INSERTs und REPEATABLEREAD aus? Probieren wir das einfach mal aus.

In 63 öffnen wir die Transaktion und schauen uns die Daten an. Dann schreiben wir in 62 eine neue Zeile, welche dort auch sichtbar ist.

In Step 25 sehen wir, dass uns in 63 weiterhin die alte Version gezeigt wird.

image

Erst ein Schließen der Transaktion führt dazu, dass wir den aktuellen Stand sehen. INSERTs führen also NICHT zu Fehlern bei REPEATABLEREAD. Dafür können neue Zeilen unvermittelt erscheinen.

image

Auf zum nächsten Level. Alles noch mal, nun mit SERIALIZABLE.

In 63 sehen wir unsere Daten und in 62 fügen wir eine Zeile hinzu. In Step 29 schauen wir mal wieder nach unseren Daten und sehen die Zeile nicht.

image

Erst das COMMIT in 63 zeigt, was dieser Isolation Level bewirkt. Er verhindert, dass im Lesebereich, der war hier jetzt ganz grob die ganze Tabelle, alte Versionen gelesen werden können und führt nach Ende der Transaktion zu einem Fehler.

Wichtig, nicht das INSERT schlägt fehl, sondern das SELECT, welches in der Vergangenheit einen ungültigen Stand der Daten las. Nochmals, In-Memory OLTP prüft erst beim COMMIT, ob die Isolation Level eingehalten wurden!!!

image

Meldung 41325, Ebene 16, Status 0, Zeile 128
The current transaction failed to commit due to a serializable validation failure.

Diese noch recht einfachen Beispiele zeigen schon sehr deutlich, dass existierender T-SQL Code in aller Regel gründlich überarbeitet werden muss für die Nutzung von In-Memory OLTP. Ja, das bedeutet Aufwand. Nur dieser ist meiner Erfahrung nach mehr als gerechtfertigt, wenn man den deutlichen Performance Gewinn für seine Lösung sieht.

Ich habe bewusst unterwegs ein paar technische Details übersprungen, damit sich das Thema halbwegs zügig liest. Dieser Post ist nicht als Schulungsersatz für die Verwendung von In-Memory OLTP gedacht, sondern um bewusst zu machen, dass es da Arbeit gibt.

Für viele Entwickler (und Entwicklerinnen…) mag sich nach dem Lesen dieses Posts In-Memory OLTP nun noch ein wenig schräger anfühlen als schon vorher, nur gerade aus den gezeigten Mechaniken zieht diese Engine ihre unglaubliche Geschwindigkeit. Probiert sie einfach mal aus!

Uwe Ricken: Temporal Tables – Verwendung von Triggern

Trigger sind eine beliebte Technologie, um Geschäftsregeln auf Ebene von Tabellen zu implementieren. Durch die Verwendung von Triggern kann z. B. für die bearbeiteten Datensätze immer der Name und das Datum des letzten Anwenders gespeichert werden, der den Datensatz manipuliert hat. Von relativ einfachen bis zu komplexen Regelwerken sind Trigger in Datenbanken von vielen Entwicklern eine gerne adaptierte Technologie. So “elegant” die Verwendung von Triggern für viele Entwickler sein mag  –  im Zusammenhang mit “System Versioned Temporal Tables” sollten sie auf keinen Fall verwendet werden. Der folgende Artikel zeigt einen klassischen Anwendungsfall, der bei Implementierung von “System Versioned Temporal Tables” eklatante Nachteile in sich birgt.

Testumgebung

Für die nächsten Beispiele wird erneut die Tabelle [dbo].[Customer] in leicht abgewandelter Form verwendet. Die Tabelle besitzt ein Attribut mit dem Namen [UpdateUser]. Dieses Attribut soll bei jeder Aktualisierung automatisch mit dem Namen des Bearbeiters aktualisiert werden.

-- Create a dedicated schema for the history data
IF SCHEMA_ID(N'history') IS NULL
    EXEC sp_executesql N'CREATE SCHEMA [history] AUTHORIZATION dbo;';
    GO
 
IF OBJECT_ID(N'dbo.Customers', N'U') IS NOT NULL
    DROP TABLE dbo.Customers;
    GO
 
CREATE TABLE dbo.Customers
(
    Id         INT          NOT NULL    IDENTITY (1, 1),
    Name       VARCHAR(100) NOT NULL,
    Street     VARCHAR(100) NOT NULL,
    ZIP        CHAR(5)      NOT NULL,
    City       VARCHAR(100) NOT NULL,
    UpdateUser SYSNAME      NOT NULL    DEFAULT (ORIGINAL_LOGIN()),
    ValidFrom  DATETIME2(0) GENERATED ALWAYS AS ROW START NOT NULL DEFAULT ('2016-01-01T00:00:00'),
    ValidTo    DATETIME2(0) GENERATED ALWAYS AS ROW END   NOT NULL DEFAULT ('9999-12-31T23:59:59'),
 
    CONSTRAINT pk_Customers_ID PRIMARY KEY CLUSTERED (Id),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
);
GO
 
CREATE TABLE history.Customers
(
    Id         INT          NOT NULL,
    Name       VARCHAR(100) NOT NULL,
    Street     VARCHAR(100) NOT NULL,
    ZIP        CHAR(5)      NOT NULL,
    City       VARCHAR(100) NOT NULL,
    UpdateUser SYSNAME      NOT NULL,
    ValidFrom  DATETIME2(0) NOT NULL,
    ValidTo    DATETIME2(0) NOT NULL
);
GO
 
ALTER TABLE dbo.Customers
SET
    (
        SYSTEM_VERSIONING = ON
        (HISTORY_TABLE = History.Customers)
    );
GO

Für die Speicherung der historischen Daten wird die Tabelle [history].[Customers] bereitgestellt. Zusätzlich wird für die Tabelle [dbo].[Customers] ein Trigger entwickelt, der nach der Aktualisierung die betroffenen Datensätze mit dem Namen des Bearbeiters kennzeichnet/aktualisiert.

CREATE TRIGGER dbo.trg_Customers_Update
ON dbo.Customers
FOR UPDATE
AS
    SET NOCOUNT ON;
 
    -- Update the [UpdateUser] with information about
    -- the user!
    UPDATE c
    SET    UpdateUser = 'Donald Duck'
    FROM   dbo.Customers AS C INNER JOIN inserted AS I
           ON (C.Id = I.Id)
 
    SET NOCOUNT OFF;
GO

Szenario

Das folgende Beispiel zeigt, wie Microsoft SQL Server den implementierten Trigger auf [dbo].[Customer] verarbeitet. Dazu wird in einer expliziten Transaktion zunächst der Datensatz mit der ID = 10 aktualisiert.

-- now the first record will be updated
BEGIN TRANSACTION;
GO
 
    UPDATE dbo.Customers
    SET    Name = 'db Berater GmbH'
    WHERE  Id = 10;
    GO
 
    SELECT DTL.resource_type,
           T.object_name,
           DTL.resource_description,
           DTL.request_type,
           DTL.request_mode,
           DTL.request_status
    FROM   sys.dm_tran_locks AS DTL
           OUTER APPLY
           (
               SELECT s.name + N'.' + t.name    AS object_name
               FROM   sys.schemas AS S INNER JOIN sys.tables AS T
                      ON (S.schema_id = T.schema_id)
               WHERE  T.object_id = DTL.resource_associated_entity_id
           ) AS T
    WHERE  DTL.request_session_id = @@SPID
           AND DTL.resource_type != N'DATABASE';
    GO
 
COMMIT TRANSACTION;
GO

Der zweite Teil der obigen Abfrage ermittelt die innerhalb der Transaktion gesetzten Sperren. Dadurch wird erkennbar, welche Objekte durch die Transaktion verwendet/blockiert werden.

Blocked resources 03

In Zeile 9 ist eine exklusive Schlüsselsperre erkennbar. Hierbei handelt es sich um den Datensatz in [dbo].[Customers] der für die Aktualisierung gesperrt wurde. Wesentlich interessanter jedoch ist die RID-Sperre (Rowlocator ID in einem HEAP) für zwei (!) Datenzeilen. Die exklusiven Sperren auf die Ressource 1:2264:2 und 1:2264:3 weisen darauf hin, dass ein Datensatz auf Datenseite 2264 in Slot 2 und ebenfalls in Slot 3 gesperrt wurden. Die Datenseite gehört zur Tabelle [history].[Customers]. Zwei Slots = zwei Datensätze. Microsoft SQL Server verarbeitet in der History Tabelle also zwei Datensätze! Einen noch genaueren Einblick in die Transaktion offenbart ein Blick in das Transaktionsprotokoll:

SELECT  Operation,
        Context,
        AllocUnitName,
        [Page ID],
        [Slot ID]
FROM    sys.fn_dblog(NULL, NULL)
WHERE   [Transaction ID] IN
        (
            SELECT  [Transaction ID]
            FROM    sys.fn_dblog(NULL, NULL)
            WHERE   [Transaction Name] = N'user_transaction'
        )
ORDER BY
        [Current LSN] ASC;
GO

TRAN_LOCKS_02

Die Transaktionsschritte sind chronologisch aufgeführt. Nachdem in Zeile 1 die Transaktion beginnt, wird zunächst ein Datensatz in [dbo].[Customers] geändert [LOP_MODIFY_ROW]. Diese Änderung führt zu einem neuen Eintrag in die Tabelle [history].[Customers] und wird durch die Operation [LOP_INSERT_ROWS] gekennzeichnet. Dieser Prozess wird automatisch durch die implementierte Technologie von “System Versioned Temporal Tables” initiiert. Nachdem der ALTER Datensatz in die History Tabelle eingetragen wurde, wird der benutzerdefinierte Trigger für UPDATE-Ereignisse gestartet und modifiziert den bereits geänderten Datensatz erneut [LOP_MODIFYING_COLUMNS] und erneut schlägt die Technologie von “System Versioned Temporal Tables” zu und trägt den vorherigen Datensatz in die History Tabelle ein. Zum Schluss wird die Transaktion geschlossen [LOP_COMMIT_XACT]. Ein Blick auf die gespeicherten Daten aus beiden Tabellen visualisiert die zuvor beschriebenen Prozessschritte:

;WITH T
AS
(
    SELECT  C.*
    FROM    dbo.Customers AS C
    WHERE   Id = 10
 
    UNION ALL
 
    SELECT  C.*
    FROM    history.Customers AS C
    WHERE   C.Id = 10
)
SELECT * FROM T
ORDER BY ValidFrom DESC;
GO

RECORDSOURCE_01

In Zeile 3 befindet sich der ursprüngliche Datensatz, dessen Name zunächst geändert wurde. Durch die Aktualisierung jedoch wurde dieser Datensatz erneut in die History Tabelle gespeichert und in Zeile 1 steht der letztendlich in [dbo].[Customers] gespeicherte Datensatz!

Lösungen?

Viele Datenbankanwendungen verwenden Trigger und die Entwickler haben viel Businesslogik in diese Trigger implementiert. Eine “einfache” Portierung der Triggerlogik in andere Prozesskanäle ist nicht schnell realisierbar. Welche Möglichkeiten bestehen also, dieses Dilemma zu lösen?

Verwendung von INSTEAD OF Trigger

Eine Idee wäre, den UPDATE-Prozess im Vorfeld abzufangen, die Daten zu manipulieren und dann in einem Update-Statement einzutragen. Während das oben beschriebene Problemszenario mit AFTER-Triggern arbeitet, sollte eine INSTEAD OF-Lösung den gewünschten Erfolg bringen. AFTER-Trigger werden abgefeuert, wenn der Datensatz bereits aktualisiert wurde (innerhalb der Transaktion) und somit die Änderungen bereits in das Transaktionsprotokoll eingetragen wurden. Ein INSTEAD OF Trigger wird ausgeführt, BEVOR die Datenmanipulation stattfindet. Für das Eintragen/Aktualisieren von Daten ist dann der Trigger selbst verantwortlich.

 CREATE TRIGGER dbo.trg_Customers_Update
ON dbo.Customers
INSTEAD OF UPDATE
AS
    SET NOCOUNT ON;
 
    -- Update the [UpdateUser] with information about
    -- the user!
    UPDATE C
    SET    C.Name = I.Name,
           C.Street = I.Street,
           C.ZIP = I.ZIP,
           C.City = I.City,
           C.UpdateUser = 'Donald Duck'
    FROM   dbo.Customers AS C INNER JOIN inserted AS I
           ON (C.Id = I.Id)
 
    SET NOCOUNT OFF;
GO

Das obige Codebeispiel zeigt, dass der vollständige UPDATE-Prozess durch den Trigger verwaltet wird. Versucht man jedoch, den Trigger zu implementieren, stößt man unweigerlich an die Einschränkungen von “System Versioned Temporal Tables”.

ERRORMESSAGE_01

System Versioned Temporal Tables erlauben keine INSTEAD OF Trigger. Diese Restriktion ist zwingend, da Temporal Tables gemäß ANSI 2011 Standard implementiert wurden. Würde ein INSTEAD OF Trigger zugelassen werden, bestünde die Möglichkeit, die Daten in der Tabelle [deleted] zu manipulieren und anschließend diese Daten in die History Tabelle zu leiten.

Verwendung von Stored Procedures

Aus mehreren Gründen empfiehlt sich die Verwendung von Stored Procedures. Sie fordert jedoch ein Umdenken bei den Entwicklern. Statt adhoc-Abfragen in der Applikation zu generieren, die DML-Operationen an die Datenbank senden, wäre die Verwendung von Stored Procedures von mehreren Vorteilen geprägt:

  • Abstraktionsschicht zwischen Anwendung und Daten
  • Implementierung von Geschäftslogik als Business-Schicht
  • Restriktion des Zugriffs auf die Daten
-- Implementation of logic as stored procedure
CREATE PROC dbo.proc_update_customer
    @Id     INT,
    @Name   VARCHAR(100),
    @Street VARCHAR(100),
    @ZIP    CHAR(5),
    @City   VARCHAR(100)
AS
    SET NOCOUNT ON;
 
    -- now the record will be updated with all
    -- information
    UPDATE  dbo.Customers
    SET     Name       = @Name,
            Street     = @Street,
            ZIP        = @ZIP,
            City       = @City,
            -- implementation of UpdateUser
            UpdateUser = ORIGINAL_LOGIN()
    WHERE   Id = @Id;
 
    SET NOCOUNT OFF;
GO

Zusammenfassung

Die Verwendung von Triggern in System Versioned Temporal Tables sollte auf jeden Fall vermieden werden. Durch Trigger, die nachträglich betroffene Datensätze manipulieren, wird ein nicht unerheblicher Overhead an Daten in der History Tabelle generiert. Statt auf die Implementierung von Triggern zu setzen, sollte die Verwendung von Stored Procedures in Betracht gezogen werden. Sie bietet neben der Eliminierung der zuvor genannten Nachteile von Triggern auch Sicherheitsaspekte, um die Daten der Tabellen nicht unmittelbar zu manipulieren.

Bisher veröffentlichte Artikel zu System Versioned Temporal Tables

Herzlichen Dank fürs Lesen!

Uwe Ricken: Temporal Tables – Programmierung vs. Systemlösung

Im Kommentar zu meinem Artikel “Temporal Tables – Umbenennen von Metadaten” hat ein von mir sehr geschätzter Kollege aus meiner Access-Zeit – Philipp Stiefel (w) – angemerkt, dass eine Gegenüberstellung von Programmierung und Systemlösung interessant wäre. Das finde ich auch – also wurde der Urlaub dazu genutzt, sich mit den unterschiedlichen Lösungsansätzen zu beschäftigen.

Temporal Tables mit Hilfe einer Eigenlösung

Wer noch keine Möglichkeit hat, mit Microsoft SQL Server 2016 zu arbeiten, wird nicht umhin kommen, eine Implementierung von “Temporal Tables” in Eigenregie zu realisieren. Hierzu gibt es drei mögliche Lösungsansätze:

  1. Implementierung in der Clientsoftware
  2. Implementierung durch Stored Procedures
  3. Implementierung durch Trigger

Die Optionen 1 und 2 fallen in diesem Artikel aus der Betrachtung heraus, da diese Lösungen eine Protokollierung verhindern, wenn unmittelbar oder durch Drittsoftware (z. B. Access) Zugang zu den Tabellen zu erwarten ist. Meine Erfahrung hat gezeigt, dass bisher KEINE Software konsequent eine Abstraktionsschicht verwendet (Views / Stored Procedures), um den unmittelbaren Zugang zu den Tabellen zu verhindern. Aus diesem Grund betrachte ich in diesem Artikel ausschließlich die Implementierung durch Trigger.

Grundsätzliche Einschränkungen bei einer programmierten Lösung

Unabhängig von der gewählten Lösung gibt es in einer programmierten Lösung generelle Probleme, die nicht zu lösen sind:

  • Die Tabelle für die Historisierung besitzt KEINE unmittelbare Abhängigkeit zur “Temporal Table”!
  • Die Tabelle für die Historisierung kann ohne Berücksichtigung der “Temporal Table” in ihrer Struktur manipuliert werden und vice versa!
  • Die Tabelle für die Historisierung ist eine gewöhnliche Tabelle. Somit besteht Raum für direkte Manipulationen des Dateninhalts!
  • Möglichkeiten des “Verbergens” von Attributen für die Versionierung bestehen nicht – es muss über Views eine Möglichkeit geschaffen werden.
  • Sofern nicht mit der Enterprise-Edition gearbeitet wird, ist PAGE-Compression (Standard für die History Table) nicht möglich!

Szenario für Trigger

Wie schon in den bisher erstellten Artikeln wird eine Tabelle [dbo].[Customer] für die Beispiele verwendet. Für die Historisierung wird die Tabelle [history].[Customers] verwendet.

IF SCHEMA_ID(N'history') IS NULL
    EXEC sp_executesql N'CREATE SCHEMA [history] AUTHORIZATION dbo;';
    GO

IF OBJECT_ID(N'dbo.Customers', N'U') IS NOT NULL
    DROP TABLE dbo.Customers;
    GO

CREATE TABLE dbo.Customers
(
    Id         INT          NOT NULL    IDENTITY (1, 1),
    Name       VARCHAR(100) NOT NULL,
    Street     VARCHAR(100) NOT NULL,
    ZIP        CHAR(5)      NOT NULL,
    City       VARCHAR(100) NOT NULL,
    ValidFrom  DATETIME2(0) NOT NULL    DEFAULT ('2016-01-01T00:00:00'),
    ValidTo    DATETIME2(0) NOT NULL    DEFAULT ('9999-12-31T23:59:59'),

    CONSTRAINT pk_Customers_ID PRIMARY KEY CLUSTERED (Id)
);
GO

CREATE TABLE history.Customers
(
    Id         INT          NOT NULL,
    Name       VARCHAR(100) NOT NULL,
    Street     VARCHAR(100) NOT NULL,
    ZIP        CHAR(5)      NOT NULL,
    City       VARCHAR(100) NOT NULL,
    ValidFrom  DATETIME2(0) NOT NULL,
    ValidTo    DATETIME2(0) NOT NULL
);
GO

Für die Implementierung von “Temporal Tables” mit Triggern müssen zwei Situationen/Ereignisse in der Tabelle berücksichtigt werden.

UPDATE-Trigger

Der UPDATE-Trigger wird ausgeführt, sobald ein bestehender Datensatz manipuliert wird. Der Trigger besitzt eine simple Struktur. Zunächst muss ein Zeitstempel für die Manipulation generiert werden um ihn anschließend in beiden Tabellen für die Gültigkeit zu verwenden. Während der Zeitstempel in der “Temporal Table” für den Beginn der Validierung verwendet wird, muss der in die “History Table” einzufügende Datensatz diesen Zeitstempel für das Ende der Gültigkeit erhalten.

CREATE TRIGGER dbo.trg_Customers_Update
ON dbo.Customers
FOR UPDATE
AS
    SET NOCOUNT ON;

    DECLARE @Timestamp  DATETIME2(0) = GETDATE();

    -- in the first step we insert the "old" record with a validation stamp
    -- into the history tables
    INSERT INTO history.Customers (Id, Name, Street, ZIP, City, ValidFrom, ValidTo)
    SELECT Id, Name, Street, ZIP, City, ValidFrom, @TimeStamp
    FROM   deleted;

    -- now we have to update the original row in the ValidFrom attribute
    UPDATE C
    SET    C.ValidFrom = @TimeStamp
    FROM   dbo.Customers AS C INNER JOIN inserted AS I
           ON (C.Id = I.Id);

    SET NOCOUNT OFF;
GO

DELETE-Trigger

Wird ein Datensatz aus der Tabelle gelöscht, muss der Datensatz in die “History Table” übertragen werden. Ebenfalls muss protokolliert werden, bis wann dieser Datensatz im der “Temporal Table” vorhanden war.

CREATE TRIGGER dbo.trg_Customers_Delete
ON dbo.Customers
FOR DELETE
AS
    SET NOCOUNT ON;

    DECLARE @TimeStamp DATETIME2(0) = GETDATE();

    INSERT INTO history.Customers
    SELECT Id, Name, Street, ZIP, City, ValidFrom, @TimeStamp
    FROM   deleted;

    SET NOCOUNT OFF;
GO

Welche Ressourcen werden bei einem Update blockiert?

Die Verwendung von Triggern ist mit Vorsicht zu genießen – insbesondere in Umgebungen mit hohem Transaktionsvolumen. Die folgende Abbildung zeigt den Prozessaufruf für ein UPDATE.

Coding - Transactional Process

Insgesamt unterteilt sich die Aktualisierung in drei Phasen. In Phase 1 wird der Wert für das Attribut [Street] geändert und der Trigger aktiviert. In der zweiten Phase wird der ursprüngliche Datensatz in der Tabelle [history].[Customers] gespeichert. In diesem Abschnitt wird der zuvor ermittelte Zeitstempel verwendet, um das Gültigkeitsende des Datensatzes zu bestimmen. In der letzten Phase muss erneut der geänderte Datensatz aktualisiert werden, da der ermittelte Zeitstempel nun als neues Startdatum für die Gültigkeit des Datensatzes verwendet wird.

In der Praxis sieht der Prozess wie folgt aus:

-- Activation of output of the locked resources to the client
DBCC TRACEON (3604, 1200, -1);
GO

Die Traceflags sorgen dafür, dass die Sperren, die während der Aktualisierung gesetzt werden, am Client sichtbar gemacht werden. Anschließend wird in einer expliziten Transaktion der Datensatz geändert. Die Transaktion bleibt für weitere Untersuchungen geöffnet!

BEGIN TRANSACTION;
GO
    UPDATE dbo.Customers
    SET    Street = 'Musterweg 1'
    WHERE  Id = 33906;

Blocked resources 01

Die Ausgabe der Ressourcen zeigt, in welcher Reihenfolge die Tabellen/Datensätze blockiert werden. Im ersten Abschnitt wird in der Tabelle [dbo].[Customers] (OBJECT: 6:565577053:0) der Datensatz mit der Id = 33906 exklusiv blockiert. Hierzu wird hierarchisch zunächst die Tabelle und dann die Datenseite mit einem “Intent Exclusive Lock” blockiert. Anschließend wird der Datensatz selbst mit einem eXclusive-Lock blockiert.

Der nächste Abschnitt ist aus Performance-Sicht sehr interessant. Wie man erkennen kann, wird die Datenbank 2 in der Transaktion benutzt. Die Datenbank mit der ID = 2 ist TEMPDB! Bei der Verwendung von Triggern werden zwei Systemtabellen innerhalb eines Triggers benötigt. In einem UPDATE-Prozess sind es die Tabellen [inserted] und [deleted]. Diese Objekte werden in TEMPDB angelegt und verwaltet. Nachdem die Aktualisierung des Datensatzes abgeschlossen wurde, wird der Datensatz wieder freigegeben. Anschließend muss die Tabelle [history].[Customers] (OBJECT: 6:645577338:0) verwendet werden, da der Trigger den vorherigen Datensatz in diese Tabelle einträgt. Abschließend wird erneut eine Aktualisierung (U-Lock –> X-Lock) der Tabelle [dbo].[Customers] durchgeführt, um das Attribut [ValidFrom] neu zu setzen. Neben den eXklusiven Sperren der beiden Tabellen führt insbesondere der Zugriff auf TEMPDB zu einem nicht unerheblichen Einfluss auf die Performance, wenn TEMPDB nicht richtig konfiguriert ist!

Ein Blick in das Transaktionsprotokoll zeigt die Aufrufreihenfolge aus Sicht der durchgeführten Transaktion

SELECT [Current LSN],
       [Operation],
       [Log Record Length] + [Log Reserve] AS LogVolume,
       AllocUnitName,
       [Page ID],
       [Slot ID]
FROM   sys.fn_dblog(NULL, NULL);

FN_DBLOG_01

Sobald die Transaktion beginnt, wird eine Aktualisierung ([LOP_MODIFY_ROW]) auf die Tabelle [dbo].[Customers] durchgeführt. Anschließend wird der “alte” Datensatz in die Tabelle [history].[Customers] eingetragen ([LOP_INSERT_ROWS]). Da der Trigger jedoch erneut die Tabelle [dbo].[Customers] aktualisieren muss, wird diese Tabelle erneut in der Transaktion bearbeitet ([LOP_MODIFY_ROW]).

Trigger = Deadlock

Wie die Aufrufkette in der obigen Prozessbeschreibung zeigt, werden zwei Ressourcen in einer Wechselwirkung zueinander blockiert. Dieses Verhalten kann dazu führen, dass es vermehrt zu Deadlock-Problemen kommt. Die Situation wird durch den Trigger initiiert. Das folgende Beispiel zeigt eine Situation, in der ein Deadlock eine Transaktion beendet.

DEADLOCK-Situation-01

In Transaktion 1 (T1) wird ein SELECT auf [history].[Customers] ausgeführt, dem unmittelbar danach ein SELECT auf [dbo].[Customers] folgt. Wenn zwischen beiden Aufrufen eine Aktualisierung auf [dbo].[Customers] ausgeführt wird, versucht der Trigger eine X-Sperre auf [history].[Customers]. Diese X-Sperre kann nicht gesetzt werden, da  T1 die Tabelle noch im Zugriff hat. Gleichwohl kann T1 nicht auf [dbo].[Customers] zugreifen, da sie von T2 exklusiv blockiert ist.

Das nachfolgende Script wird in einem neuen Abfragefenster gestartet. Um einen Deadlock zu erzwingen wurde eine restriktive Isolationsstufe gewählt: (SERIALIZABLE).

SET TRAN ISOLATION LEVEL SERIALIZABLE;
GO

BEGIN TRANSACTION;
GO
    SELECT * FROM History.Customers AS H
    WHERE  H.Id = 10;
    GO

In einem zweiten Abfragefenster wird die folgende Transaktion gestartet:

UPDATE dbo.Customers
SET    Name = 'db Berater GmbH'
WHERE  Id = 10;

Diese Transaktion wird begonnen aber nicht beendet. Grund dafür sind Blockaden auf der Ressource [history].[Customers].

SELECT DTL.request_session_id,
       CASE WHEN DTL.resource_type = N'OBJECT'
            THEN SCHEMA_NAME(T.schema_id) + N'.' + OBJECT_NAME(DTL.resource_associated_entity_id)
            ELSE DTL.resource_description
       END AS object_resource,
       DTL.request_mode,
       DTL.request_type,
       DTL.request_status
FROM   sys.dm_tran_locks AS DTL LEFT JOIN sys.tables AS T
       ON (DTL.resource_associated_entity_id = T.object_id)
WHERE  DTL.resource_database_id = DB_ID()
       AND DTL.resource_type != N'DATABASE'
ORDER BY
       DTL.request_session_id;

TRAN_LOCKS_01

Die Abbildung zeigt, dass der Prozess 57 eine IX-Sperre auf die Tabelle [history].[Customers] setzen möchte aber nicht erhält, weil Prozess 54 bereits eine S-Sperre auf die Ressource gesetzt hat. Prozess 57 hat aber bereits eine IX-Sperre auf [dbo].[Customers] gesetzt. Nun versucht Prozess 54 ein SELECT auf [dbo].[Customers]:

SELECT * FROM dbo.Customers AS C
WHERE C.Id = 10;

Nach einigen Sekunden wird der Prozess als DEADLOCK-Opfer beendet!

DEADLOCK-Situation-02

Abfragen auf Basis von Zeitstempeln

Mit “System Versioned Temporal Tables” ist es innerhalb einer Abfrage möglich, für einen bestimmten Zeitpunkt den Status des gewünschten Datensatzes zu ermitteln. Diese Möglichkeit besteht für eine “Eigenlösung” natürlich nicht, da die Syntax ohne “System Versioned Temporal Tables” nicht funktioniert. In diesem Fall bleibt nur die Möglichkeit einer programmierten Lösung, die – basierend auf der Annahme, dass ein bestimmter Zeitpunkt angegeben wird – mit Hilfe einer INLINE-Funktion implementiert wird.

CREATE FUNCTION dbo.if_Customers
(
    @Id        int,
    @TimeStamp datetime2(0)
)
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM   dbo.Customers
    WHERE  (
            @Id = 0 OR
            Id = @Id
           ) AND
           ValidFrom <= @TimeStamp AND
           ValidTo >= @TimeStamp

    UNION ALL

    SELECT *
    FROM   history.Customers
    WHERE  (
            @Id = 0 OR
            Id = @Id
           ) AND
           ValidFrom <= @TimeStamp AND
           ValidTo >= @TimeStamp
);
GO

Die Funktion muss beide Tabellen abfragen und den jeweiligen Zeitraum berücksichtigen. Mit “System Versioned Temporal Tables” gibt es weitere Abfragemöglichkeiten, die in einer programmierten Version selbst erstellt werden müssten. Bei der Erstellung der Funktionen ist zu berücksichtigen, dass es sich immer um Inline-Funktionen handelt. Ansonsten könnte es Probleme bei Abfragen geben, die diese Funktion mittels JOIN oder CROSS APPLY verwenden, da in solchen Fällen immer von einer Rückgabemenge von 1 Datensatz ausgegangen wird!

Szenario für “System Versioned Temporal Tables”

Das gleiche Szenario wird nun für “System Versioned Temporal Tables” durchgeführt. Hierbei interessiert primär, welche Ressourcen belegt/blockiert werden und wie sich das Transaktionsvolumen im Verhältnis zur Triggerlösung verhält.

Welche Ressourcen werden bei einem Update blockiert?

Erneut wird ein Update auf einen Datensatz in der “System Versioned Temporal Table” durchgeführt, um zu prüfen, welche Ressourcen gesperrt werden.

Blocked resources 02

Die Ausgabe der blockierten Ressourcen zeigt die Reihenfolge, in der die Tabellen/Datensätze blockiert werden. Hervorzuheben sind die ersten beiden Sperren. Anders als bei der “programmierten” Version sperrt Microsoft SQL Server bereits zu Beginn der Transaktion BEIDE Tabellen! Zu Beginn wird ein Intent Exclusive Lock auf die Tabelle [dbo].[Customers] (OBJECT: 6:565577053:0) gesetzt um unmittelbar im Anschluss die Tabelle [history].[Customers] (OBJECT: 6:629577281:0) zu sperren. Durch die IX-Sperren wird signalisiert, dass in tieferen Hierarchien eine X-Sperre gesetzt werden soll. Sobald ein IX-Sperre auf die Datenseite (PAGE: 6:1:818) gesetzt wurde, kann eine X-Sperre auf den Datensatz in [dbo].[Customers] angewendet werden.

Erst, wenn die exklusive Sperre auf de Datensatz angewendet werden kann, wird in der Tabelle für den neu hinzuzufügenden Datensatz eine entsprechende Datenseite nebst Slot gesperrt. Dieser Teil der Transaktion beschreibt das Hinzufügen des ursprünglichen Datensatzes in die Tabelle [history].[Customers].

Ein Blick in das Transaktionsprotokoll zeigt die Aufrufreihenfolge aus Sicht der durchgeführten Transaktion.

Locked resources 02

Zu Beginn wird die Zeile in [dbo].[Customers] aktualisiert um anschließend in [history].[Customers] den vorherigen Datensatz einzutragen. Die Zeilen 6 – 10 sind für die Bewertung der Transaktion irrelevant; sie zeigen lediglich, dass in [history].[Customers] vor dem Eintragen eines neuen Datensatzes eine neue Datenseite allokiert wurde.

Deadlock-Szenarien

Sicherlich sind auch in diesem Szenario DEADLOCK-Gefahren vorhanden. Sie entsprechen aber anderer Natur und liegen eher im Design der Indexe. Ist es in der programmierten Version notwendig, die Objekte “sequentiell” und getrennt voneinander zu sperren, so lässt eine Systemlösung von “Temporal Tables” dieses Szenario nicht zu. Microsoft SQL Server sperrt immer BEIDE Tabellen vor einer Manipulation von Datensätzen. Damit kann sich kein weiterer Prozess zwischen die Transaktion schieben; ein Deadlock ist – bezüglich der beschriebenen Version – nicht mehr möglich!

Abfragen auf Basis von Zeitstempeln

Ganz klar liegt hier der große Vorteil von “System Versioned Temporal Tables”. Statt – wie in der programmierten Version – mit eigenen Funktionen die Funktionalität von “Temporal Tables” nachzubauen, bedient man sich im neuen Feature von Microsoft SQL Server 2016 lediglich der erweiterten Syntax von System Versioned Temporal Tables.

SELECT * FROM dbo.Customers
FOR SYSTEM_TIME AS OF '2016-07-05T16:00:00' AS C
WHERE  Id = 33906;

Statt komplizierter Abfragen reicht der Hinweis “FOR SYSTEM_TIME…” um verschiedene Abfragemöglichkeiten basierend auf Zeitstempeln zu generieren. Ob diese Abfragen performant sind oder Verbesserungspotential haben, soll in einem anderen Artikel detailliert untersucht werden.

Bisher veröffentlichte Artikel zu System Versioned Temporal Tables

Vielen Dank fürs Lesen!

Andreas Wolter: The SQL Server 2016 In-Memory Evolution – from row-store to columnstore to in-memory operational analytics

 

(DE)
Der SQL Server 2016 ist da, und damit nun auch die „Version 2“ der In-Memory OLTP Engine, wenn man so will.

-           Was hinter der im SQL Server 2014 eingeführten In-Memory Engine von SQL Server steckt, habe ich auf diversen internationalen Konferenzen und in der IX bereits 2014 vorgestellt. Zu einem Blog-Artikel bin ich leider bisher nicht gekommen.

In diesem Artikel möchte ich die Neuerungen und Verbesserungen beleuchten, an denen Microsoft die letzten 2 Jahre gearbeitet hat, und die viel auf Kundenfeedback zurückzuführen sind. Feedback, welches in einem großen Maße noch aus „nicht umsetzbar, weil dies und dies fehlt“ bestand.

Und eines kann ich vorwegnehmen: in meinen Augen hat Microsoft die überwiegende Anzahl an Blockern adressieren können.

(EN)
SQL Server 2016 is finally available and, by extension, the “version 2” of the In-Memory OLTP Engine, if you will.

-           At various international conferences and already at the IX in 2014 I have presented what is behind the In-Memory Engine of SQL Server introduced in SQL Server 2014. Only I had not yet found the time to put it into a blog article so far.

In this article I will illuminate the innovations and improvements Microsoft has been working on for the past 2 years, and which can be attributed much to customer feedback. In fact, feedback that to a great extent consisted of notes like “not practicable because this and this is missing.”

And let me say one thing before I start: in my view, Microsoft has been able to address the majority of blockers.

Das heißt, In-Memory ist für jeden mindestens eine Evaluierung Wert, und in fast allen Datenbank-Projekten finden sich Strukturen, sie man In-Memory eleganter lösen kann. – Ok, nicht für ganz jeden, denn dieses Feature ist leider der Enterprise-Edition vorbehalten.

That means, everybody should at least consider evaluating In-Memory, and in almost all database projects there are structures that can be solved more elegantly In-Memory. – Ok, maybe not for everybody, because this feature is unfortunately limited to the enterprise edition.

 

Die wichtigsten Neuerungen für memory optimierte Tabellen sind:

Man kann nun sowohl Unique Indexe als auch Fremdschlüssel-Constraints definieren. Letztere sind nur zwischen memory-optimierten Tabellen möglich (und nicht zwischen Disk-/Page-basierten und memory-optimierten Tabellen), und müssen sich immer auf den Primärschlüssel beziehen – der Verweis auf Unique Indexe ist nicht möglich.

Auch sind nun NULL-Werte in Nicht-Unique Indexen zulässig (Anders als bei Disk-basierten Tabellen nicht in Unique-Indexen!).

Ebenfalls sehr wichtig ist die Unterstützung aller Codepages und von Nicht-Unicode-Daten sowie die Verschlüsselung der memory-optimierten Daten mit TDE (ergo nicht im Arbeitsspeicher selber sondern der Daten, die auf der Festplatte abgelegt werden). *1

Das waren in meinen Augen die häufigsten Blocker in Projekten, in denen In-Memory evaluiert wurde, da es dafür kaum praktikable Workarounds gab.

*1 Daten-Verschlüsselung mit den ENCRYPTION-Funktionen in SQL Server wird nicht unterstützt – das gilt auch für die neue Always Encrypted Technologie und Dynamic Data Masking.

Row-Level Security von SQL Server 2016 wird aber unterstützt. Die Prädikate und Funktionen, müssen dann nativ kompiliert werden. Sehr cool, wenn ihr mich fragt.

The most important innovations for memory-optimized tables are:

It is now possible to define Unique Indexes as well as foreign key constraints. The latter are only possible between memory-optimized tables (and not between disk-/page-based and memory-optimized tables), and must always refer to the primary key – referring to Unique Indexes is not possible.

Moreover, NULL-values in Non-Unique Indexes are now allowed (as opposed to disk-based tables not in Unique Indexes!).

Equally very important is the support of all code pages and of non-Unicode data as well as the encryption of memory-optimized data with TDE (hence not in the main memory itself but of the data that stored on disk). *1

In my view, these were the most frequent blockers in projects in which In-Memory was evaluated, as there were hardly any practicable workarounds for this issue.

*1 Data encryption with the ENCRYPTION functions in SQL Server is not supported – this is also true for the new Always Encrypted Technology and Dynamic Data Masking.

Row-Level Security of SQL Server 2016 yet is supported. The predicates and functions must consequently be compiled natively.

Very cool, if you ask me.

Eine weitere Einschränkung ist mit der Möglichkeit, Memory-optimierte Tabellen im Nachhinein zu ändern, entfallen.
Unterstützt ist das Hinzufügen, Entfernen, und Ändern von Spalten und Indexen im Nachhinein. Anstelle CREATE/ALTER/DROP Index muss hier nun ALTER TABLE verwendet werden, da bei Memory optimierten Tabellen Indexe Teil der Tabellen-Definition sind (und in  Gesamtheit kompiliert werden).
Besonders wichtig hierbei ist, dass man darüber nun auch den Bucket-Count von Hash-Indexen ändern kann, der sich naturgemäß ja im Betrieb sehr stark ändern kann mit der Zeit.

So sieht das im Code an einem Beispiel aus:

A further limitation has been eliminated with the possibility of altering Memory-optimized tables afterwards.
Adding, dropping and altering columns and indexes afterwards is supported. Instead of CREATE/ALTER/DROP index it must now be used ALTER TABLE, since in Memory-optimized tables indexes are part of the table definition (and are being compiled in its entirety).
It is particularly important here that it is now also possible to change the bucket count of Hash-indexes which during operation may naturally change considerably over time. 

This is how it looks in a sample code:

Add_Alter_Drop_Hash_Index 

Statistiken können auch mit SAMPLE anstelle FULLSCAN aktualisiert werden, und vor allem auch automatisch.

Datentypen: LOB-Datentypen wie varchar/varbinary(max) werden unterstützt, und werden „off-row“ gespeichert.

Statistics can also be updated with SAMPLE instead of FULLSCAN, and, above all, automatically as well.

Data type: LOB datatypes such as varchar/varbinary(max) are supported and stored “off-row.”

Wichtige, neu unterstützte T-SQL Funktionalitäten innerhalb von Natively compiled Stored Procedures und, ganz neu, Functions sind: die OUTPUT-Klausel, UNION und UNION ALL, DISTINCT, OUTER JOINs, Unterabfragen.

Außerdem können nativ kompilierte Prozeduren nun auch mit ALTER PROCEDURE verändert werden. Dadurch werden sie naturgemäß im letzten Schritt in der neuen Form kompiliert abgelegt.
Um für den Fall von geränderten Statistiken einen neuen Ausführungsplan zu ermöglichen, kann man nun auch sp_recompile gegen nativ kompilierte Prozeduren (und Funktionen) ausführen.

Important, newly supported T-SQL functionalities within Natively compiled Stored Procedures and, brand new, Functions, are: the OUTPUT clause, UNION and UNION ALL, DISTINCT, OUTER JOINs, subqueries.

Moreover, natively compiled procedures can now also be changed with ALTER PROCEDURE. In this way, they will naturally be stored compiled in the new shape in the last step.
In order to facilitate a new implementation plan in the case of changed statistics one can now also executive sp_recompile against natively compiled procedures (and functions).

 

Auch an der Performance wurde weiter geschraubt. So können memory-optimierte Tabellen und Hash-Indexe jetzt (im InterOP Mode) parallel gescannt werden. Im IO Bereich wurde der gesamte Checkpoint-Prozess überarbeitet und die Datenfiles können nun mit multiplen Threads geschrieben und gelesen werden, wodurch sich der Durchsatz fast auf ein zehnfaches erhöhen kann (wenn das IO-Subsystem da mithält).

Performance, too, was further tweaked. As a result, memory-optimized tables and Hash-indexes can now (in InterOP mode) be scanned simultaneously. In the IO area, the entire checkpoint process was reviewed and the data files can now be read and written with multiple threads, which may result in an almost tenfold increase of the throughput (if the IO-subsystem keeps up with it).

 Parallel_Scan_HashIndex_In-MemoryOLTP

 

Columnstore-Technologie

Was hat sich eigentlich in der anderen, seit 2012 im SQL Server integrierten Storage-Engine „Vertipaq“ mit den Columnstored Indexen getan? Diese sind ja ebenfalls Main-memory optimiert, jedoch mit ganz anderem Ziel: Speicherplatzoptimierung und effiziente OLAP-Style-Queries.

Die Neuerungen hier sind sehr essentiell:

Beide Columnstore Index Typen, Clustered und Nonclustered, sind nun aktualisierbar!
Außerdem lassen sich Columnstore Indexe nun mit weiteren traditionellen btree-Indexen ergänzen. Das ist wichtig, weil ja nicht jede Abfrage wirklich von der Columnstore-Speicherform profitiert. Dieser Zugewinn an Flexibilität ist ein entscheidender Vorteil gegenüber den bisherigen Releases und kann gar nicht genug betont werden.

Und noch etwas ist nun möglich: Nonclustered Columnstore können mit einem Filter erstellt werden.

Columnstore Technology

What has been going on in the other Storage-Engine “Vertipaq”, integrated in SQL Server since 2012, with the Columnstored Indexes? These are also Main-memory optimized, but with an entirely different objective:
Storage space optimization and efficient OLAP-style queries.

The innovations here are very essential:

Both Columnstore Index Types, Clustered and Nonclustered, can now be updated!
Additionally, Columnstore Indexes can now be extended with further traditional btree-indexes. This is important, as not every query really profits from the Columnstore storage form. This gain in flexibility is a decisive advantage over the previous releases and cannot be emphasized enough.

And something else is now possible: Nonclustered Columnstore can be created with a filter.

Mit diesen neuen Techniken lässt sich zum Beispiel folgendes Problem lösen:

Eine Tabelle mit Verkaufstransaktionen wird im Sekundentakt durch kleine Inserts gefüllt.
Nebenbei möchte man aber auch diverse Berichte mit Tages- und Tageszeitaggregationen bereitstellen. So aktuell wie möglich natürlich.
Das Problem ist hierbei typischerweise, dass man sich hier entscheiden muss zwischen Indexen für alle Berichtsabfragen und denen, die minimal notwendig sind für etwaige Updates. Inserts benötigen ja für sich gesehen keine Indexe.
Durch diese Kombination entstehen die mit vielen Indexen überfrachteten OLTP-Tabellen, die ich bei meinen Einsätzen oft entdecke und die es dann gilt, „wegzuoptimieren“.

Mit der Möglichkeit einen Nonclustered Columnstore Index zusätzlich zu dem Clustered Index anzulegen, spart man nicht nur Indexe, (denn der Columnstore-Index kann ja jede nötige Spalte abdecken) sondern mit einem geschickt gesetzten Filter kann man auch den Index-Overhead vermeiden, der sonst die eigentlich wichtigeren Inserts treffen würde.

Die Vermischung aus OLTP- und OLAP-Abfragen sind eines der typischsten Probleme von Datenbanken, und diese neuen Möglichkeiten sind daher einfach ein Traum für Datenbank-Architekten.
So sieht das im Code aus:

By means of new techniques the following problem can be solved, for example:

A table with sales transactions is filled by small inserts at intervals of seconds.
At the same, one would also like to provide various reports on day and daytime aggregations. Maximally up to date of course.
The problem typically lies in the fact that one has to decide between indexes for all report queries and those that are minimally required for possible updates. Inserts viewed in isolation do not require any indexes.
This combination results in the OLTP tables overloaded with many indexes, which I frequently discover during my work and that then need to be “optimized” (removed).

The possibility to create a Nonclustered Columnstore Index in addition to the Clustered Index does not only save Indexes (because the Columnstore Index can cover every necessary column), but with a smartly applied filter the Index-Overhead can also be avoided that would otherwise affect the actually more important inserts.

The mixing of OLTP and OLAP queries are one of the most typical problems in databases, and these new possibilities are thus simply a dream for database architects.

This is how it will look in code:

 Filtered_NonclusteredColumnstore_Index_SQLCode

40% Performance-Verbessrung im TPC-H Benchmark

Diese Verbesserungen haben den SQL Server 2016 im TPC-H Benchmark performance-technisch mit fast 40% mehr QphH (Query-per-Hour Performance Metric) am SQL Server 2014 vorbeiziehen lassen. Auf dem Screenshot kann man sehen, dass der Benchmark am 9.3.2016 eingesendet wurde, und auch wirklich auf derselben Hardware wie am 1.5.2015 unter SQL Server 2014 erzielt wurde.

40% Performance-Improvement in TPC-H Benchmark

In terms of performance, these improvements have made the SQL Server 2016 pull ahead of SQL Server 2014 by close to 40% more QphH (Query-per-Hour Performance Metric) in the TPC-H Benchmark. You can see in the screenshot that the Benchmark was sent in on 9 March 2016 and really was achieved on the same hardware as under SQL Server 2014 on 1 May 2015.

 TPC-H_3000GB

Weitere wichtige Verbesserungen für Columnstore sind die Unterstützung des SNAPSHOT Isolation Level (und RCSI), was besonders für Read-Only Replicas  von Availability Groups wichtig ist, sowie Online-Defragmentierung und diverse Analyse-Verbesserungen.

Further important improvements for Columnstore include the support of the SNAPSHOT Isolation Level (and RCSI), which is especially important to Read-Only Replicas of Availability Groups, as well as online-defragmentation and various analysis enhancements

 

Das Highlight: Real-time Operational Analytics & In-Memory OLTP

Das Highlight Schlechthin aber ist sicherlich die Kombination aus memory-optimierten Tabellen und ColumnStore Indexen.

Damit werden zwei Features, die für völlig gegensätzliche Abfrage-Typen, OLTP und OLAP, optimiert sind, verschmolzen.

The highlight: Real-time Operational Analytics & In-Memory OLTP

The absolute highlight however is surely the combination of memory-optimized tables and ColumnStore Indexes.

As a result, two features that are optimized for entirely opposing query types – OLTP and OLAP – are merged.

Technik

Technisch kommt ein Clustered Columnstore Index zum Einsatz. Dieser lässt, wie man in dem Bild sehen kann, den „hot-Bereich“ der Daten aus, um den Overhead durch die doppelte Datenhaltung bei Änderungen bzw. den potentiell schnell aufeinander folgenden Inserts in diesem Bereich zu vermeiden. Zusätzlich zu der implizierten Delta Rowgroup (im Bild: Tail), die durch den memory-optimierten Index abgedeckt wird, gibt es eine „deleted rows table“  für gelöschte Daten. Beide Bereiche werden nach dem für Columnstore Indexe standardmäßigen Schwellwert von 1 Million Zeilen asynchron komprimiert/dem CCI hinzugefügt.

An dieser Stelle noch ein Hinweis: auch die maximale Datenmenge, die pro Datenbank in (durable) memory-optimierten Tabellen gehalten werden kann, ist nun entfernt worden!
Nach aktuellem Stand der Technik sind unter Windows Server 2016 damit theoretisch bis zu 12 TB (abzgl. einem Maintenance-overhead) in XTP-memory speicherbar!

Von der Struktur her gestaltet sich das so:

Technical facts

In technical terms, a Clustered Columnstore Index is applied. As can be seen in the image, it omits the “hot-spot” of the data in order to prevent the overhead through the double data storage in case of alterations and the potentially quick succession of inserts in this area. In addition to the implied Delta Rowgroup (in the image: Tail) that is covered by the memory-optimized index, there is a “deleted rows table” for deleted data. Both areas are asynchronously compressed/added to the CCI according to the Columnstore Index standard threshold value of 1 million cells.

At this point, let me add another note: the maximum data amount that can be stored per database in (durable) memory-optimized tables has now been eliminated, too!
As a result, according to the current technical state, in theory up to 12 TB (less a maintenance overhead) can be stored in XTP-memory under Windows Server 2016!

In terms of structure, it would look like this:

 SQL2016_Real_time_operational_analytics

Und die Umsetzung in Code sieht so aus:

And applied in code, it would look like this:

 MemoryOptimizedTable_ClusteredColumnstore_Index_SQLCode

Im Ergebnis hat man nun das Beste aus beiden Welten: hochperformante Inserts/Updates/Deletes und Singleton-Abfragen, und zugleich hochperformante analytische Abfragen, die mit vielen Millionen Zeilen auf einmal hantieren – und zwar zeitgleich auf derselben Tabelle!

The outcome now offers the best from both worlds: high performing inserts/updates/deletes and singleton-queries, and at the same time high performing analytic queries that handle many millions of cells at once – and in fact at the same time in the same table!

 MemoryOptimizedTable_ClusteredColumnstore_Index

Eine Einschränkung beim Abfragen ist, dass der Columnstore Index auf memory-optimierten Tabellen nur im InterOP-Modus funktioniert – also nicht in nativ kompilierten Prozeduren.

Und da wären wir beim letzten Thema:

One restriction in querying is that the Columnstore Index in memory-optimized tables only works in the InterOP mode – thus not in natively compiled procedures.

Which leads us to the last topic:

 

Offene Punkte, fehlende Feature-Unterstützung
Es gibt natürlich auch jetzt noch eine ganze Reihe an Features, die durch die über jahrzehntelange andauernden Entwicklung der SQL-Sprache in den SQL Server übernommen wurde, aber eben noch nicht in die neue XTP-Engine gelangt sind. Das liegt nicht nur daran, dass diese „einfach neu“ ist, sondern auch, dass durch die völlig andere Architektur dieser radikal auf In-Memory getrimmten Engine einige wesentliche Unterschiede zu den althergebrachten Datenbank-Engines bestehen.

Folgende Features vermisse ich persönlich noch am meisten:

  1. DBCC CHECKDB/CHECKTABLE für memory optimierte Tabellen
  2. CASE-Statement
  3. Filtered Indexe
  4. CTEs
  5. Replikation
  6. OFFSET-Operator
  7. Ranking Funktionen
  8. DDL Trigger für CREATE/DROP TABLE und CREATE/DROP PROCEDURE
  9. TRUNCATE TABLE
  10. DATA_COMPRESSION
  11. Datentyp datetimeoffset

Die vollständige Liste befindet sich hier:

Open points, missing feature support

There are of course still a number of features that have been taken over into SQL Server due to the decade-long development of the SQL language, but which have not made it into the new XTP Engine yet. This is not just because the latter is “simply new” but also because due to the completely different architecture of this engine, which is radically tailored to In-Memory, there are several significant differences vis-à-vis the traditional database engines.

Personally, I miss the following features the most:

  1. DBCC CHECKDB/CHECKTABLE for memory-optimized tables
  2. CASE-Statement
  3. Filtered Indexes
  4. CTEs
  5. Replication
  6. OFFSET-Operator
  7. Ranking Functions
  8. DDL Trigger for CREATE/DROP TABLE and CREATE/DROP PROCEDURE
  9. TRUNCATE TABLE
  10. DATA_COMPRESSION
  11. Data type datetimeoffset

For the complete list, visit:

 

Transact-SQL Constructs Not Supported by In-Memory OLTP

Call to Action

Auch wenn die Liste an fehlendem Feature/Funktionssupport immer noch recht lang ist – die wenigsten nutzen wirklich all diese Features voll aus. Und für die meisten dieser übriggebliebenen „Blocker“ gibt es eigentlich recht gute Workarounds, sei es in Form einer anderen Architektur oder Code-technisch. Man muss auch bedenken, dass die In-Memory Tabellen nicht für alle Szenarien überhaupt Sinn machen, sondern eher für die Top-belasteten Tabellen Sinn machen. Und da sollte man sich ohnehin bereits etwas Mühe beim Design gemacht haben.

Generell bin ich der festen Meinung, dass sich in fast jedem Datenbankprojekt einige Stellen finden werden, die von In-Memory Funktionen profitieren können.

Warum kann ich das so sicher sagen?

Bereits seit SQL 2014 gibt es die Möglichkeit neben Memory-optimierten Tabellen auch Memory- optimierten Tabellen Variablen zu verwenden. Und mit diesen lassen sich wiederum viele Temptable-Konstrukte ablösen.

Nun werden dadurch nicht unbedingt gleich ganze Applikationen performanter, aber es ist ein Anfang, sich mit In-Memory Codetechnisch auseinanderzusetzen und langsam aber sicher damit zu programmieren. Ein weiterer „Quick-Win“ ist oft in Datawarehouse-Architekturen im sogenannten „Staging-Bereich“ zu finden, wie er gerade in traditionellen DW-Systemen häufig zum Einsatz kommt.

Und über diese „Einfallstore“ ist man ganz schnell in der „In-Memory-Welt“ angekommen.

Call to Action

Even though the list of missing feature/function support is still quite long, only few really make full use of these features. And for most of the remaining “blockers” there are actually quite good workarounds, be it in the form of a different architecture or in code terms. One has to bear in mind that the In-Memory tables do not necessarily make sense for all scenarios, but rather for the top-affected tables. And as for the latter, one should already have put some effort into the design anyway.

In general, I firmly believe that in almost every database project there are some instances that may profit from In-Memory functions.

Why can I be so sure?

Already since SQL 2014 it has been possible to use memory-optimized table variables aside from memory-optimized tables. And using these, in turn, many temptable-constructs can be replaced. Now that does not necessarily result in higher performing applications right away, but it is a good way to start dealing with In-Memory in terms of code and to slowly but surely start programming with it. A further “Quick-Win” can often be found in data warehouse architectures in the so-called “staging area,” as it is frequently being applied in traditional DW-systems at the moment.

And it is via these “gateways” that you have will have ended up in the “In-Memory world” before you know it.

 

Cu In-Memory

Andreas

PS:

If you are in India in August and want to advance your skills in those new technologies there is still a chance to get a seat in the Precon "Present and Future: In-Memory in SQL Server – from 0 to Operational Analytics Master" at SQL Server Geeks Summit in Bangalore on August 10.

SQL Server Geeks Conference

Sascha Lorenz: SQL Server in-Memory OLTP – Ein Plädoyer für Lasttest Werkzeuge und End-to-End Monitoring

Im Rahmen einiger Proof-of-Concepts und Workshops für die in-Memory OLTP Engine des SQL Servers habe ich die Erfahrung gemacht, dass selbst komplexe Eigenentwicklungen von Unternehmen häufig ohne Werkzeuge für realistische Lasttests auskommen müssen.

Ziel der PoCs ist es zu evaluieren, ob das Investment in die Integration von SQL Server in-Memory OLTP überhaupt einen wirtschaftlich gerechtfertigten Vorteil mit sich bringt. Die einfache Frage im Raum ist häufig: Wird unsere Anwendung mittels in-Memory OLTP tatsächlich deutlich schneller? Die Voraussetzung für eine solche Überlegung ist natürlich, dass man entweder zurzeit mit der bestehenden Last zu langsam unterwegs ist oder für die Zukunft mit wesentlich mehr Last rechnet und davon ausgeht, dass diese zu Problemen führen wird.

Die wenigsten Kunden nutzen zurzeit die in-Memory OLTP Engine des SQL Servers für eine komplette Neuentwicklung auf der grünen Wiese. Wobei, wer als ISV oder Entwicklungsleiter schon länger eine Neuentwicklung im Auge hatte, dann ist der SQL Server 2016 ein guter Grund nun endlich damit zu beginnen. Die Implementierung von in-Memory OLTP in eine bereits produktive Anwendung ist immer mit Aufwand verbunden. Dieser besteht i.d.R. aus Lizenzkosten (Enterprise Edition, sofern nicht eh schon gesetzt) und natürlich Aufwand für die Anpassung der Anwendung. In-Memory OLTP basiert ja u.a. auf einem Versionstore und damit einem optimistischen Konzept hinsichtlich von Änderungen an den Daten. Daher müssen die Entwickler häufig diesbezüglich erst geschult und gecoacht werden.

Nur lohnt sich dieser Aufwand auch wirklich? Dann kommt oft noch die Definitionsrunde, was denn nun eigentlich mit schneller gemeint ist. Nur oft kann diese Frage gar nicht in der ersten Runde seriös und abschließend geklärt werden, da Werkzeuge fehlen, um eine realistische Last auf einem Testsystem zu erzeugen.

Die Funktionsweise von in-Memory OLTP selbst kann ich mittlerweile sehr gut demonstrieren mit Open Source Werkzeugen und meinen eigenen Beispielszenarien. Nur hat sowas ja nichts zu tun mit der Last eines selbst entwickelten Webshops oder einer angepassten ERP Umgebung. Ohne Tests sind Aussagen über mögliche Verbesserungen reine Kaffeesatzleserei.

Der Verzicht auf die Entwicklung von Lasttest Werkzeugen und ein End-to-End Monitoring sind aus meiner Sicht eindeutig technische Schulden. Natürlich kann ich nachvollziehen, dass das unglaublich unattraktive Stories sind und gerade hier gerne am Ende eines Projektes Budget gespart wird.

Der Lasttest wird ersetzt durch ein generisches Monitoring des SQL Servers (wenn überhaupt…) und das End-to-End Monitoring ergibt sich durch den Durchschnitt der Tickets mit dem Label “Performance”.

Mir ist es ein Rätsel, wie ohne Lasttests eine Anwendung überhaupt hinsichtlich irgendwelcher Seiteneffekte durch mehrere Anwender getestet werden konnte. Wobei damit dann häufig auch deutlich wird, warum man überhaupt vor Ort ist, um diese zu tunen bzw. warum sich da jemand Vorteile durch die in-Memory Engine verspricht.

Ähnliches gilt für das End-to-End Monitoring. Die vorhandenen SLAs beschränken sich zumeist auf RTO & RPO. Kennzahlen darüber hinaus sind nicht definiert und “Effekte” im Frontenderlebnis nicht nachvollziehbar.

Daher meine Bitte an Alle: Implementiert Lasttests, welche auch weiter entwickelt werden mit der Anwendung, und gleich dazu noch ein End-to-End Monitoring drauf, damit evtl. Effekte frühzeitig bemerkt werden können.

Natürlich wäre dann auch der Beweis, dass in-Memory OLTP genau diesen Workload beschleunigt wesentlich einfacher zu erbringen und mit weniger Aufwand möglich. So verbringe ich nicht unwesentlich viel Zeit in den PoCs und entwickle rudimentäre Lasttests, um den Budgetverantwortlichen zumindest eine grobe Einschätzung der Möglichkeiten liefern zu können.

Uwe Ricken: Temporal Tables – Umbenennen von Metadaten

Während meines Vortrags über “Temporal Tables” auf dem SQL Saturday Rheinland 2016 wurden einige Fragen gestellt, die ich nicht “ad hoc” beantworten konnte, da ich zu den Fragen noch keine ausreichenden Tests gemacht hatte. Dieser Artikel ist der zweite Artikel in einer Artikelreihe über “System versioned Temporal Tables” Dieser Artikel beschäftigt sich mit der Frage, ob man mit [sp_rename] Tabellen / Spalten von System Versioned Temporal Tables umbenennen kann.

Hinweis

Diese Artikelreihe befasst sich nicht mit den Grundlagen von “System Versioned Temporal Tables”! Die grundsätzliche Funktionsweise über “System Versioned Temporal Tables” kann im Artikel “Temporal Tables” (englisch) bei Microsoft nachgelesen werden.

Umbenennung von Metadaten

Frage: “…funktioniert sp_rename und wird die Umbenennung durchgereicht?” Eigentlich besteht diese Frage aus zwei Elementen. Die Antwort ist “Ja” und “Nein”.

  • JA – Objekte können mit der Systemprozedur sp_rename jederzeit umbenannt werden. Das Umbenennen von Objekten wird nicht dadurch blockiert, dass eine Tabelle als “System Versioned Temporal Table” gekennzeichnet und eingebunden ist.
  • NEIN – wenn eine Tabelle, die als System Versioned Temporal Table dient, umbenannt wird, wird nicht automatisch die History-Tabelle mit umbenannt. Jedoch muss man beim Umbenennen zwei wichtige Aspekte beachten; mit [sp_rename] können nicht nur Tabellen umbenannt werden sondern auch Spaltennamen! Wie unterschiedlich [sp_rename] auf beide Objekttypen reagiert, zeigen die die folgenden Beispiele und Erklärungen.

Wie funktioniert sp_rename?

Bei [sp_rename] handelt es sich um eine Systemprozedur, die von Microsoft bereitgestellt wird. Mit [sp_rename] kann der Name eines benutzerdefinierten Objekts in der aktuellen Datenbank geändert werden. Bei diesem Objekt kann es sich um eine Tabelle, einen Index, eine Spalte, einen Aliasdatentyp oder einen CLR-benutzerdefinierten Typ (Common Language Runtime) von Microsoft .NET Framework handeln.

Umbenennen von Tabellennamen

Interne Verwaltung von Tabellenobjekten

Obwohl ein Objekt immer eindeutig benannt werden muss, verwaltet Microsoft SQL Server alle Objekte intern mittels einer OBJECT_ID. Dieses Verfahren gilt sowohl für Tabellen als auch für Indexe, Einschränkungen, usw. Wir lesen und adressieren Objekte nach ihren Namen aber intern verwenden viele Funktionen und Prozeduren für den Zugriff die interne OBJECT_ID.

Mit dem folgenden Code wird eine Tabelle [dbo].[Customers] erzeugt. Ebenfalls wird für die Speicherung der historischen Daten eine entsprechende Tabelle mit gleichen Schemaeigenschaften erstellt.

USE [temporal];
   GO

-- Create a dedicated schema for the history data
IF SCHEMA_ID(N'history') IS NULL
   EXEC sp_executesql N'CREATE SCHEMA [history] AUTHORIZATION dbo;';
   GO

IF OBJECT_ID(N'dbo.Customers', N'U') IS NOT NULL
   DROP TABLE dbo.Customers;
   GO

CREATE TABLE dbo.Customers
(
   Id        INT          NOT NULL IDENTITY (1, 1),
   Name      VARCHAR(100) NOT NULL,
   Street    VARCHAR(100) NOT NULL,
   ZIP       CHAR(5)      NOT NULL,
   City      VARCHAR(100) NOT NULL,
   Phone     VARCHAR(20)  NULL,
   Fax       VARCHAR(20)  NULL,
   EMail     VARCHAR(255) NULL,
   ValidFrom DATETIME2(0) GENERATED ALWAYS AS ROW START NOT NULL DEFAULT ('2016-01-01T00:00:00'),
   ValidTo   DATETIME2(0) GENERATED ALWAYS AS ROW END NOT NULL DEFAULT ('9999-12-31T23:59:59'),

   CONSTRAINT pk_Customers_ID PRIMARY KEY CLUSTERED (Id),
   PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
);
GO

ALTER TABLE dbo.Customers SET(SYSTEM_VERSIONING = ON); GO

Ein Blick hinter die Kulissen zeigt, wie die beiden Objekte in der Datenbank verwaltet werden:

SELECT object_id,
       QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(name) AS TableName,
       temporal_type,
       temporal_type_desc,
       history_table_id
FROM   sys.tables
WHERE  object_id = OBJECT_ID(N'dbo.Customers', N'U')
 
UNION ALL
 
SELECT object_id,
       QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(name) AS TableName,
       temporal_type,
       temporal_type_desc,
       history_table_id
FROM   sys.tables
WHERE  object_id = OBJECT_ID(N'history.Customers', N'U');
GO

Dependency between System Versioned Table and History Table

In der Abbildung sind die Abhängigkeiten der beiden Tabellen untereinander gut zu erkennen. Wichtig für die Fragestellung ist, dass beide Tabellen unterschiedliche [object_id] besitzen! Den für die Umbenennung von “Objekten” verantwortliche Code aus [sp_rename] zeigt die folgende Abbildung.

Codeexcerpt - sp_rename

Der Codeausschnitt zeigt, dass die Umbenennung einer Tabelle (%%object) auf der korrespondierenden object_id (@objid) basiert. Das Objekt mit der ID = @objid erhält den Namen @newname. Sollte die Umbenennung einen Fehler verursachen, wird als Fehlermeldung ausgegeben, dass ein Objekt mit gleichem Namen bereits in der Datenbank existiert! Unabhängig von Art der Programmierung sollte jedoch klar sein, dass [sp_rename] für Objekte nur auf die ObjektId verweist und keine weiteren Tabellenabhängigkeiten überprüft und/oder anpasst.

EXEC sp_rename
   @objname = N'dbo.Customers',
   @newname = N'NewCustomers';
GO

Für das Beispiel wird die Tabelle [dbo].[Customers] in [dbo].[NewCustomers] umbenannt. Diese Operation wird ohne Fehlermeldungen durchgeführt und die Überprüfung der Tabellennamen zeigt, dass ausschließlich die „System Versioned Temporal Table“ umbenannt wurde; die „History Table“ blieb unberührt!

Dependency between System Versioned Table and History Table - 02

Der Name hat sich geändert aber die [object_id] bleibt von einer Neubenennung unberührt. Der Name einer Tabelle ist für eine “System Versioned Temporal Table” nicht wichtig. Die Verwaltung erfolgt über die [object_id].

Umbenennung von Spaltennamen

Die Systemprozedur [sp_rename] wird nicht nur für die Umbenennung von Tabellen verwendet sondern kann auch verwendet werden, um Attribute einer Tabelle umzubenennen. Bei den Tests müssen zwei Situationen berücksichtigt werden:

Umbenennen von Attributen bei aktivierter “System Versioned Temporal Table”

Umbenennen von Attribut aus “System Versioned Temporal Table”

Im ersten Beispiel wird versucht, das Attribut [Name] aus der Tabelle [dbo].[Customers] neu zu benennen. Dabei bleibt die Systemversionierung aktiviert. Das Ergebnis sollte nicht überraschen – die Umbenennung funktioniert einwandfrei und ohne Fehler.

EXEC sp_rename
   @objname = N'dbo.Customers.Name',
   @newname = N'CustomerName',
   @objtype = N'COLUMN';
GO

Microsoft SQL Server kann das Attribut in der „System Versioned Temporal Table“ ohne Probleme ändern und wendet die Anpassungen automatisch auf die „History Table“ an.

Umbenennen von Attribut aus “History Table”

Im nächsten Beispiel soll versucht werden, das geänderte Attribut [CustomerName] wieder in [Name] umzubenennen. Diesmal wird die Prozedur auf die “History Table” angewendet. Auch dieses Ergebnis sollte nicht überraschen; der Versuch schlägt fehl mit dem Hinweis, dass Änderungen an der “History Table” nicht erlaubt sind!

EXEC sp_rename
   @objname = N'history.Customers.Name',
   @newname = N'Name',
   @objtype = N'COLUMN';
GO

sp_rename_error_output

Die Fehlermeldung zeigt, dass eine Änderung an der aktivierten “History Table” nicht erlaubt ist. Die Fehlermeldung ist korrekt, wenn man in die Sicherheitsprinzipien von “System Versioned Temporal Tables” kennt. Hier heißt es: “When SYSTEM_VERSIONING is ON users cannot alter history data regardless of their actual permissions on current or the history table. This includes both data and schema modifications.”

Somit ist klar, warum weder Datensätze noch Schemamodifikationen möglich sind – die Sicherheitsrichtlinien von Microsoft SQL Server lassen Manipulationen an der “History Table” nicht zu, so lange die die Systemversionierung aktiviert ist.

Umbenennen von Attributen bei deaktivierter “System Versioned Temporal Table”

Mit dem letzten Beispiel wird geprüft, ob eine Änderung der Attribute ohne aktivierter Systemversionierung möglich ist. Hierzu wird der folgende Code ausgeführt:

-- Deactivate System Versioning and change the attributes
ALTER TABLE dbo.Customers
SET (SYSTEM_VERSIONING = OFF);
GO
 
-- Now we can change the attributes in the history table
EXEC sp_rename
   @objname = N'history.Customers.Name',
   @newname = N'CustomerName',
   @objtype = N'COLUMN';
GO
 
-- can we now activate System Versioning?
ALTER TABLE dbo.Customers
SET
    (
        SYSTEM_VERSIONING = ON
        (HISTORY_TABLE = History.Customers)
    );
GO

Zunächst wird die Systemversionierung deaktiviert und anschließend das Attribut [Name] umbenannt. Dieser Schritt ist nur möglich, da keine Systemversionierung mehr aktiviert ist. Versucht man anschließend, die Systemversionierung wieder zu aktivieren, erhält man den folgenden Fehler:

reactivating_versioning_error_output

Die Systemversionierung kann nicht mehr aktiviert werden, da nach der Änderung des Attributs die Metadaten beider Tabellen unterschiedlich sind. Um die Systemversionierung wieder erfolgreich aktivieren zu können, muss das geänderte Attribut auch in der “System Versioned History Table” geändert werden.

EXEC sp_rename
   @objname = N'dbo.Customers.CustomerName',
   @newname = N'Name',
   @objtype = N'COLUMN';
GO

Nachdem die Namen der Attribute in beiden Tabellen wieder identische Namen besitzen, lässt sich die Tabelle erneut als “System Versioned Temporal Table” konfigurieren.

Zusammenfassung

Das Umbenennen von “System Versioned Temporal Tables” sowie deren “History Tables” ist mit [sp_rename] möglich, da für die interne Verwaltung nicht der Name der Tabelle entscheidend ist sondern die ObjektId. Sofern Attribute in einer Relation umbenannt werden sollen, können nur Attribute der “System Versioned Temporal Table” ohne Deaktivierung umbenannt werden. Eine Neubenennung von Attributen in der „History Table“ kann nur durchgeführt werden, wenn die Systemversionierung aufgelöst wird.

Bisher erschienen Artikel zu System Versioned Temporal Tables

Herzlichen Dank fürs Lesen!

Uwe Ricken: Temporal Tables – Behandlung von NULL-Einschränkungen

Während meines Vortrags über “Temporal Tables” auf dem SQL Saturday Rheinland 2016 wurden einige Fragen gestellt, die ich nicht “ad hoc” beantworten konnte, da ich zu den Fragen noch keine ausreichenden Tests gemacht hatte. Mit diesem Artikel beginne ich eine Artikelreihe über “System versioned Temporal Tables”, zu der mich insbesondere Thomas Franz inspiriert hat. Ihm danke ich sehr herzlich für die vielen Fragen, die er mir per Email zugesendet hat.

Hinweis

Diese Artikelreihe befasst sich nicht mit den Grundlagen von “System Versioned Temporal Tables”! Die grundsätzliche Funktionsweise über “System versioned Temporal Tables”  kann im Artikel “Temporal Tables” (english) bei Microsoft nachgelesen werden.

NULL oder NOT NULL

Frage: “… kann ein Attribut mit einer NULL-Einschränkung nachträglich eine NOT NULL-Einschränkung erhalten?”
Diese Frage kann man ganz eindeutig beantworten: “It depends!”
Wenn Attribute in einer Tabelle NULL-Werte zulassen, dann muss die abhängige “System Versioned Temporal Table” die gleichen Einschränkungen besitzen. Sollte diese Einschränkung in beiden Tabellen unterschiedlich sein, können Informationen eventuell nicht abgespeichert werden und die Historisierung ist unvollständig. Das folgende Skript erstellt eine Tabelle [dbo].[Customers] sowie die korrespondierende Tabelle für die Speicherung der historischen Daten mit gleichem Namen im Schema [history].

-- Create a dedicated schema for the history data
IF SCHEMA_ID(N'history') IS NULL
EXEC sp_executesql N'CREATE SCHEMA [history] AUTHORIZATION dbo;';
GO

-- Create the base table for the application data
IF OBJECT_ID(N'dbo.Customers', N'U') IS NOT NULL
    DROP TABLE dbo.Customers;
    GO

CREATE TABLE dbo.Customers
(
    Id         INT             NOT NULL    IDENTITY (1, 1),
    Name       VARCHAR(100)    NOT NULL,
    Street     VARCHAR(100)    NOT NULL,
    ZIP        CHAR(5)         NOT NULL,
    City       VARCHAR(100)    NOT NULL,
    Phone      VARCHAR(20)     NULL,
    Fax        VARCHAR(20)     NULL,
    EMail      VARCHAR(255)    NULL,
    ValidFrom  DATETIME2(0)    GENERATED ALWAYS AS ROW START  NOT NULL  DEFAULT ('2016-01-01T00:00:00'),
    ValidTo    DATETIME2(0)    GENERATED ALWAYS AS ROW END    NOT NULL  DEFAULT ('9999-12-31T23:59:59'),

    CONSTRAINT pk_Customers_ID PRIMARY KEY CLUSTERED (Id),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
);
GO

-- Create the System Versioned Temporal Table for history data
CREATE TABLE history.Customers
(
    Id         INT             NOT NULL,
    Name       VARCHAR(100)    NOT NULL,
    Street     VARCHAR(100)    NOT NULL,
    ZIP        CHAR(5)         NOT NULL,
    City       VARCHAR(100)    NOT NULL,
    Phone      VARCHAR(20)     NULL,
    Fax        VARCHAR(20)     NULL,
    EMail      VARCHAR(255)    NULL,
    ValidFrom  DATETIME2(0)    NOT NULL,
    ValidTo    DATETIME2(0)    NOT NULL
);
GO

-- Activate System Versioning
ALTER TABLE dbo.Customers
SET
(
    SYSTEM_VERSIONING = ON
    (HISTORY_TABLE = History.Customers)
);
GO

In der Tabelle können die Attribute [Phone], [Fax] und [Email] leer sein (NULL). Anschließend wird ein Datensatz in die Tabelle [dbo].[Customers] eingetragen. Der einzutragende Datensatz besitzt eine Emailadresse aber Phone und Fax bleiben leer!

INSERT INTO [dbo].[Customers]
(Name, Street, ZIP, City, Phone, Fax, EMail)
VALUES
('db Berater GmbH', 'Bahnstrasse 33', '64390', 'Erzhausen', NULL, NULL, 'info@db-berater.de');
GO

Testszenarien

Die Tabellen- und Datenstruktur lässt unterschiedliche Testszenarien zu, mit denen die Fragestellung analysiert werden kann. Für die Fragestellung werden vier verschiedene Szenarien untersucht:

  • NULL wird zu NOT NULL in einer leeren Tabelle
  • NULL wird zu NOT NULL in einer gefüllten Tabelle
  • NOT NULL wird zu NULL in einer gefüllten Tabelle
  • NOT NULL wird zu NULL

NULL wird zu NOT NULL in leerer Tabelle

Im ersten Beispiel wird die Einschränkung ohne Inhalt in der Tabelle geändert

BEGIN TRANSACTION;
GO

    ALTER TABLE dbo.Customers
    ALTER COLUMN [Phone] VARCHAR(20) NOT NULL;
    GO

    SELECT  SCHEMA_NAME(o.schema_id) + N'.' + O.name,
            DTL.request_mode,
            DTL.request_type,
            DTL.request_status
    FROM    sys.dm_tran_locks AS DTL
            INNER JOIN sys.objects AS O
            ON (DTL.resource_associated_entity_id = O.object_id)
    WHERE   DTL.request_session_id = @@SPID
            AND DTL.resource_type = N'OBJECT'
    GO

COMMIT TRANSACTION;
GO

Der Code wickelt die Änderung innerhalb einer expliziten Transaktion ab, um so die gesetzten Sperren nach der Änderung nachverfolgen zu können. Tatsächlich ist eine Änderung von Einschränkungen in der Tabelle ohne Weiteres möglich, wenn sich noch keine Daten in der “System versioned Temporal Table” befinden.

Locked resources 01

Die Abbildung zeigt, dass für die Anpassungen in Systemtabellen Änderungen vorgenommen werden müssen. Die beiden Benutzertabellen werden mit einer LCK_M_SCH_M-Sperre versehen. Hierbei handelt es sich um Schemasperren, die verhindern, dass während eines DDL-Prozesses auf die betroffenen Objekte zugegriffen wird. Ohne Fehler wird die Änderung implementiert. Für eine Anpassung muss “System Versioning” nicht deaktiviert werden!

Vorher Nachher
TableScreenShot 01 TableScreenShot 02

Die Abbildung zeigt, dass die Einschränkungen nicht nur auf [dbo].[Customers] angewendet wurde sondern ebenfalls auf die Tabelle [history].[Customers] übertragen wurde. Identische Metadaten sind essentiell für “System Versioned Temporal Tables”! Dieser Test lässt sich auch in die andere Richtung wiederholen. Solange noch KEINE DATEN in der “System Versioned Temporal Table” vorhanden sind, können NULL-Einschränkungen ohne Probleme auf die Objekte angewendet werden.

NULL wird zu NOT NULL in einer gefüllten Tabelle

Das nächste Szenario muss differenziert betrachtet werden. Für das Beispiel soll das Attribut [Email] verwendet werden. Zunächst wird ein Datensatz in die Tabelle [dbo].[Customers] eingetragen, der eine EMail-Adresse besitzt.

INSERT INTO [dbo].[Customers]
(Name, Street, ZIP, City, Phone, Fax, EMail)
VALUES
('db Berater GmbH', 'Bahnstrasse 33', '64390', 'Erzhausen', NULL, NULL, 'info@db-berater.de');
GO

Anschließend wird versucht, die NULL-Einschränkung für das Attribut [Email] zu ändern:

ALTER TABLE dbo.Customers
ALTER COLUMN [Email] VARCHAR(255) NOT NULL;
GO

Tatsächlich läuft die Änderung ohne Probleme durch und für das Attribut [Email] sind keine NULL-Werte mehr erlaubt. Nachdem für das Attribut [Email] die NULL-Einschränkung erneut geändert wurde (NULL-Werte erlaubt) , wird ein weiterer Datensatz eingetragen, der keine Email-Adresse besitzt:

ALTER TABLE dbo.Customers
ALTER COLUMN [Email] VARCHAR(255) NULL;
GO

INSERT INTO [dbo].[Customers]
(Name, Street, ZIP, City, Phone, Fax, Email)
VALUES
('Microsoft GmbH', 'Musterstrasse 33', '12345', 'Musterhausen', NULL, NULL, NULL);
GO

Wird nun erneut versucht, für das Attribut [Email] die NULL-Einschränkung zu ändern, schlägt die Änderung fehl. Die Fehlermeldung lässt sehr schnell erkennen, worin die Ursache dafür liegt:

Meldung 515, Ebene 16, Status 2, Zeile 155
Der Wert NULL kann in die Email-Spalte, temporal.dbo.Customers-Tabelle nicht eingefügt werden. Die Spalte lässt NULL-Werte nicht zu. Fehler bei UPDATE.

Microsoft SQL Server überprüft vor der Konfiguration der NULL-Einschränkung zunächst das vorhandene Datenmaterial. Befinden sich Datensätze in der Tabelle, die einen NULL-Wert im Attribut besitzen, dann können diese Datensätze bei einer Änderung die vorherige Version nicht abspeichern. Für das Beispiel des zweiten Datensatzes würde eine Änderung an der Adresse dazu führen, dass der ursprüngliche Datensatz (mit einen NULL-Wert in [Email]) nicht in der “System Versioned Temporal Table” eingetragen werden kann, da bei erfolgreicher Anpassung dieses Attribut keine NULL-Werte zulassen würde!

NOT NULL wird zu NULL in einer gefüllten Tabelle

Wie sieht es aber aus, wenn ein Attribut bereits bei der Erstellung eine NOT NULL-Einschränkung besitzt die nachträglich geändert werden soll? Diese Frage wird mit dem folgenden Szenario untersucht und beantwortet:

ALTER TABLE dbo.Customers
ALTER COLUMN [Name] VARCHAR(100) NULL;
GO

Nicht ganz so überraschend ist das Ergebnis – es funktioniert einwandfrei ohne dabei “System Versioning” zu unterbrechen. Die Erklärung für dieses Verhalten liegt im gleichen Verhalten wie bereits im vorherigen Test. In diesem Szenario muss Microsoft SQL Server keine Validierungen durchführen, da die Restriktion NOT NULL zu einem NULL wird. Somit sind leere Werte erlaubt. Unabhängig davon, ob bereits Werte im Attribut stehen, verletzen sie keine NULL-Einschränkung.

Ausblick

Dieser Artikel ist der erste Artikel in einer Reihe von unterschiedlichen Artikeln zum Thema “System Versioned Temporal Tables”. Ich bin sehr an Fragen zu diesem Thema interessiert und sofern ein Leser Fragen zu diesem Thema hat, möchte ich sie sehr gerne aufgreifen und darüber bloggen. Fragen können jederzeit über das Kontakt-Formular gesendet werden.

Herzlichen Dank fürs Lesen!

Sascha Lorenz: SQL Server 2016 - in-Memory OLTP

Die letzten Monate habe ich recht viel Zeit damit verbraucht Kunden im Rahmen von Workshops, PoCs und Projekten das Thema in-Memory OLTP und unsere Erfahrungen damit im SQL Server 2014 und SQL Server 2016 näher zu bringen. Bekannt wurde das Feature ja vor einiger Zeit als Projekt “Hekaton” für den SQL Server 2012.

Seinerzeit war das Interesse bei den Kunden sehr verhalten. Sowohl Endkunden als auch ISVs haben das Thema zwar wahrgenommen, aber da es ja erst mit 2014 realisierbar war, führte dieses bei den meisten zu einer “Das ist etwas für später…”-Meinung. Wenige haben für sich das Potenzial von in-Memory OLTP erkannt.

Das letzte halbe Jahr hat sich dieses geändert. Mit der Sichtbarkeit des Commitments von Microsoft in diese Technologie, von einem Feature mag ich da gar nicht mehr sprechen wollen, wurde vielen Nutzern des SQL Servers klar, dass sich das Spiel ändern wird bzw. eigentlich schon geändert hatte.

Nun, im SQL Server 2016, durch die Kombination von Technologien (Columnstore Index und in-Memory OLTP) zu Operational Analytics ist der Umfang der Änderungen bzw. Erweiterungen an der Architektur der SQL Server Engine deutlich sichtbarer geworden. Marketing ist halt doch alles. Hier mal ein gutes Beispiel.

Und für viele ist damit nun ein Wettbewerbsvorteil möglich, welchen man nicht mehr ignorieren kann.

Was machen wir da draußen schon mit in-Memory OLTP?

In den Workshops und PoCs erfahren und erleben die Kunden, wie leicht es geworden ist eine für sie bisher unvorstellbare Geschwindigkeit ihres Workloads auf ihrer bestehenden Hardware zu realisieren. Und ja, es ist kein “ein-und-läuft”-Feature. Viele Kunden entwickeln mittlerweile ihre LoB Systeme zum Teil selber und sind sehr darauf bedacht sich am Ende keinen Flaschenhals bei der Datenbank einzubauen.

In Projekten werden meist partiell Tabellen vom sogenannten “disk-based” Modell übertragen in die in-Memory Welt. Dabei sind häufig gar nicht die eigentliche Tabelle und die neuen Formen der Indizierung die Herausforderung, sondern ist eher die intellektuelle Hürde, dass nun mit einem Versionstore gearbeitet wird und sich die Isolationslevel ein wenig anders anfühlen. Hilft ja alles nichts. Da kommt ja auch ein wesentlicher Teil der Geschwindigkeit her.

Des Weiteren leiten wir Teams an sich dieser Technologie kreativ zu nähern. Zwar steht ja OLTP drauf, aber das heißt ja nichts. Gerade mit der Möglichkeit auf die “Durability” bewusst zu verzichten, können ganz besondere Number Cruncher in T-SQL entstehen. Besonders, wenn der T-SQL Code dann auch kompiliert werden konnte.

Und wir bringen in-Memory OLTP in Produktion. Das ist nicht immer ganz einfach, da bei dieser spezialisierten Engine ein wenig von der Flexibilität des SQL Servers abhanden kam. Dieses ist aber meist verkraftbar, wenn man sich die Vorteile vor Augen führt. Speziell Deployment Szenarien beim rund-um-die-Uhr-Betrieb können dabei Herausforderungen sein, welchen man bereits in der Designphase einer Lösung begegnen muss. Wobei, DevOps lässt hier grüßen. Und wer schon mal mit unseren Entwicklern in Berührung gekommen ist, kennt unsere repository-driven Ansätze für größere Umgebungen.

Resümee von mir: in-Memory OLTP lebt!

Sascha Lorenz: SQL Server Missing Indexes Feature – Vorsicht!

Dieser kurze Post richtet sich primär an DBAs und Entwickler, welche vielleicht gerade damit begonnen haben sich mit dem Themen SQL Server, Performance Optimierung usw. zu beschäftigen.

Vielen dürfte das Missing Index Feature bekannt sein. Der Query Plan wird bei der Kompilierung durch den Query Optimizer um einen Index Vorschlag angereichert.

https://technet.microsoft.com/en-us/library/ms345417(v=sql.105).aspx 

Dieser Vorschlag wird auch prominent im Management Studio dargestellt.

image

Diese “Empfehlungen” entstehen im Bruchteil einer Sekunde beim Kompilieren eines SQL Statements. Und beziehen sich einzig allein das eine Statement bzw. die eine Prozedur. Schauen also nicht nach links und rechts.

Ich sehe in ihnen eher den plakativen Hinweis, dass selbst dem Query Optimizer in der Kürze der Zeit aufgefallen ist, dass es deutliches Potenzial gibt. Mehr sollte es nicht sein.

Also nicht jeden Vorschlag unreflektiert umsetzen! Ist ja eigentlich klar, oder?

Nun durfte ich bei einem Kunden die Erfahrung machen, dass im Internet (es ist doch böse, wusste es immer…) Skripte zu finden gibt, welche den Plancache des SQL Server für eine komplette Instanz auslesen, die Missing Indexes Informationen extrahieren und automatisch ALLE Empfehlungen umsetzen. Es werden also in allen Datenbanken einer Instanz ungeprüft neue Indexe erzeugt.

Das mag auf den aller ersten Blick ja eine super Idee sein, nur so hat selbst Microsoft dieses Feature nicht gemeint. Viel hilft nicht immer viel…

Indexe, speziell sogenannte abdeckende Indexe, sind eine gewollte Form der Redundanz von Daten. Es wird also Speicher belegt und Indexe müssen auch bei Schreibvorgängen gepflegt werden. Speziell komplexere Systeme wie zum Beispiel ein SharePoint mögen so etwas gar nicht.

Bei diesem Kunden führte eine solche “Maßnahme” dazu, dass quasi alle Anwendungen danach deutlich langsamer liefen als vorher. Von dem plötzlichen Wachstum um 2/3 auf dem Storage ganz zu schweigen.

Ich verzichte bewusst auf eine Verlinkung zu den Skripten, um diese nicht noch relevanter zum Thema erscheinen zu lassen.

Dirk Hondong: SQLSaturday #525 Rheinland–leider ohne den Paparazzo

Hallo liebe PASS Gemeinde,

der nächste SQLSaturday Rheinland steht vor der Tür.  Ich beneide die Besucher, die dabei sein werden. Bei den Sessions, die dieses Jahr anstehen, ist das auch kein Wunder. Auch dieses mal wird es wieder einen tollen Mix aus nationalen und internationalen Sprechern geben.

Ich schaffe es jedoch dieses Jahr aus privaten Gründen leider nicht an der Fachhochschule Bonn-Rhein-Sieg vorbeizuschauen um ein paar Fotos zu schießen, wofür ich ja sonst bekannt bin. Aber seid gewarnt: Fotos werden dennoch gemacht…  Smiley

Ich wünsche allen Teilnehmern ganz viel Spaß. Ich denke bei dem #555 bin ich dann wieder dabei.

 

Gruß

“SQLPaparazzo”


Sascha Lorenz: SQL Server 2016 – Business Intelligence Edition?

Nachdem nun der SQL Server 2016 verfügbar ist, schauen Kunden der SQL Server 2014 Business Intelligence Edition ein wenig ungläubig drein. Die BI Edition gibt es nicht mehr. Habe mir dazu die Hinweise auf der Lizenzierungsseite von Microsoft angeschaut.

Hier der O-Ton aus dem Licensing Guide für SQL 2016:

For SQL Server Business Intelligence Edition Licenses with Software Assurance

SQL Server 2014 was the last version of the SQL Server Business Intelligence Edition. Customers with active SA coverage on qualifying Business Intelligence Edition server licenses on June 1, 2016 are eligible to upgrade to and use SQL Server 2016 Enterprise (Server+CAL) software with those licenses.

During the current term of SA coverage (effective on or before June 1, 2016), customers who are licensing SQL Server 2014 Business Intelligence Edition can, for a given deployment, upgrade to and use the SQL Server 2016 Enterprise Edition (Server+CAL) software in place of the licensed SQL Server 2014 edition. Note: Customers who upgrade to SQL Server 2016 software are subject to current version Enterprise Edition server license product terms. 

Customers with Enterprise Agreements effective on or before June 1, 2016 can continue to acquire additional SQL Server 2014 Business Intelligence server licenses—and upgrade those licenses to SQL Server 2016—through the end of their current enrollment term, as long as the number of new licenses acquired does not exceed more than 25% of the number of qualifying licenses acquired as of May 1, 2016. 

After their current term expires, SA coverage can be renewed and maintained on SQL Server Enterprise Edition server licenses to provide continued access to SA benefits, including License Mobility rights, Failover Server rights, and access to future releases.

Denke mal, dass das fair klingt, oder?

Wie immer sind alle Lizenzierungshinweise hier von mir ohne jegliche Gewähr!

Sascha Lorenz: SQL Server 2016 ist verfügbar – Was ist in welcher Edition?

SQL Server 2016 ist verfügbar. *jubel*

Selten wurde eine Version so von mir intensiv im Vorfeld verfolgt, weil sie gefühlt für jeden etwas spannendes Neues dabei hat.

Kommen wir zur Frage, welche Features haben es ins Release bzw. in welche Edition (Standard oder Enterprise) geschafft?

Zwar habe ich aufgrund meiner Kommunikation mit Microsoft als MVP und durch die weltweite SQL Community vor dem Release Einiges erfahren. Dieser Artikel geht aber bewusst unbefangen an das Thema und nimmt einzig und allein die offizielle Microsoft Seite dazu als Quelle.

https://msdn.microsoft.com/en-us/library/cc645993.aspx (Stand 02.06.2016)

Blättern wir also mal gemeinsam durch die Liste. Was fällt als Erstes auf? Wo ist die BI Edition hin?

image

Ein kurzer Blick auf die 2014 Version des oben genannten Links zeigt diese noch:

image

Das könnte evtl. zu Unruhe im Feld führen.

Abschnitt “Cross-Box Scale Limits”

Ein Blick auf “Maximum Compute Capacity” zeigt, dass die Standard Edition 2016 nun “Limited to lesser of 4 sockets or 24 cores” statt wie bei der 2014 “Limited to lesser of 4 Sockets or 16 cores” nutzen kann. Könnte sich als nützlich erweisen.

Nun wird es aber gleich richtig spannend:

image

Die Standard Edition der SQL Server Analysis Services (SSAS) unterstützt nun auch Tabular als Modell, wenn auch “nur” für 16 GB. Das ist sehr schön und war auch meiner Sicht auch schon ein wenig überfällig. Guter Zug, Microsoft!

Immer wieder schön zu lesen, dass die maximale Datenbank Größe 524 PB ist. Aber das nur am Rande.

Abschnitt “RDBMS High Availability”

Auf den ersten Blick keine Änderungen. Wobei mir mal so war, als sollten die Verfügbarkeitsgruppen in abgespeckter Form auch für die Standard Edition kommen. Das behalte ich mal im Auge und recherchiere dazu ein wenig nach.

Abschnitt “RDBMS Scalability and Performance”

Okay, dass es “Operational analytics” nicht in die Standard schafft, war klar, da sich hier ja sowohl “In-Memory Column Store” als auch “In Memory OLTP” die Hand geben. Dennoch, cooler Kram ist!

Und eine kleine Überraschung, dass es die “Stretch Database” in die Standard geschafft hat. Wobei, kurz drauf rum denken, macht Sinn, da es sich hier um den Einstieg in die Azure Welt für die SQL Server Kunden handelt. Nützliches Feature auf jeden Fall.

Und wo wir hier gerade sind, wem “Delayed Durability” noch Nichts sagt, mal nachlesen. Kann SEHR nützlich sein, wenn man sich bewusst ist, was man da tut.

Abschnitt “RDBMS Security”

Jetzt wird es schon wieder aufregend. Sowohl “Row-level security” als auch “Dynamic data masking” haben es in die Standard Edition geschafft. Wieder ein guter Zug. Dass es “Always Encrypted” nur für Enterprise gibt, nun ja, ist doch eigentlich auch ein Cloud Treiber, oder?

Abschnitt “Replication”

Noch ein Cloud Feature ist “Transactional replication to Azure”. Gibt es auch für Alle.

Abschnitt “Management Tools”

Da ist mir Nichts aufgefallen.

Abschnitt “Manageability”

Wieder Nichts. Vielleicht brauche ich auch nur einen weiteren Kaffee…

Abschnitt “Development Tools”

Bin mal kurz in der Teeküche. Wieder Nichts.

Abschnitt “Programmability”

Jetzt wird es ja richtig cool. Okay, Kaffee hilft halt immer.

image

Denke mal, dass dieses Bild für sich spricht, oder? Was ist denn jetzt eigentlich der Unterschied zwischen Basic und Advanced R ist, werde ich noch mit meinem Kollegen Tom Martens bei Gelegenheit näher erklären.

JSON, yeah…

Query Store, wow…

Temporal, Hammer… (und dann noch Strech…)

Microsoft, da habt ihr ja mal alles richtig gemacht!

Abschnitt “Integration Services”

image

Azure, Hadoop und HDFS. Schön, machst rund.

Abschnitt “Integration Services – Advanced Adapters”

Okay, da ist Name halt Programm. Mach ja auch Sinn.

Abschnitt “Integration Services – Advanced Transformations”

Okay, halt auch was für den größeren Geldbeutel. Wobei, da hätte ich mir auch mal etwas Neues gewünscht.

Abschnitt “Master Data Services”

Gibst weiterhin nur in der Enterprise Edition. Hat ja auch diverse Neuerungen erfahren. Es lebt zumindest.

Abschnitt “Data Warehouse”

Hat sich Nichts geändert.

Abschnitt “Analysis Services”

Nichts Neues.

Abschnitt “BI Semantic Model (Multidimensional)”

Auch hier nichts Neues.

Abschnitt “BI Semantic Model (Tabular)”

Jetzt aber, Tabular im Standard. Aber…

Keine Perspektiven, Partitionen und kein DirectQuery (Mist…). Wobei, dennoch ein guter Schritt.

Abschnitt “Power Pivot für SharePoint”

Alles weiterhin nur für die Enterprise Edition. Interessant wie sich der Teil weiter entwickeln wird, dank PowerBI.

Abschnitt “Data Mining”

Weiterhin gehen die Basics mit der Standard, der Rest mit der Enterprise. Hat da jemand gerade R gesagt?

Abschnitt “Reporting Services”

Nun gut, Mobile Reports, sprich das Datazen Universum, gibt es nur für die Enterprise. Damit war aber auch zu rechnen.

Abschnitt “Bussines Intelligence Clients”

Dieser Abschnitt ist nach wie vor ein wenig verwirrend. Klar, dass die Clients für mobile Reporting auch nur mittels der Enterprise Edition genutzt werden können.

Das mit PowerBI hätte man geschickter formulieren können, sonst denkt noch jemand, dass man dafür zwingend einen SQL Server Enterprise Edition bräuchte. Was natürlich nicht so ist. Das geht auch ganz ohne SQL Server und macht dennoch Spaß. Das sage ich nicht von Vielem!

Abschnitt “Spatial und Location Services”

Alles bleibt wie es war.

Abschnitt “Additional Database Services”

Nichts Neues.

Abschnitt “Other Components”

Schade, dass man hier ganz hinten im Bus “Data Quality Services” und “StreamInsight” findet. Hätte mir für beide Technologien mehr gewünscht. Zumindest sind sie noch dabei. Inwiefern der Einsatz für die breite Masse Sinn macht, sei dahin gestellt.

Und schon sind wir durch. In Summe hat Microsoft die Standard Edition aufgewertet und vieles von dem coolem Kram nicht nur in der Enterprise gelassen.

Natürlich sind alle Angaben in diesem Artikel unverbindlich und ohne Gewähr !

Robert Panther: SQL Server 2016 RTM jetzt verfügbar

Im Laufe des gestrigen Tages ist nun endlich die finale Version des SQL Server 2016 erschienen und über die üblichen Quellen (MSDN etc. verfügbar).

Verfügbare Editionen

An den verfügbaren Editionen hat sich einiges geändert. So gibt es mittlerweile nur noch 64-Bit Versionen. Die Business Intelligence Edition fällt ebenfalls weg und auch die verfügbaren Varianten der Express Edition wurden etwas bereinigt (die mittlere Variante „Express mit Tools“ fällt weg, man muss sich nun nur noch zwischen „Express“ und „Express mit Advanced Services“ entscheiden). Am unteren Ende der „Nahrungskette“ ist auch weiterhin die SQL Server LocaldB verfügbar. Am anderen Ende ist allerdings auch die Developer Edition inzwischen kostenfrei verfügbar (darf natürlich wie bisher nicht produktiv eingesetzt werden).

Als kommerzielle Editionen gibt es wie gewohnt die Standard, Web und Enterprise Edition, die nun noch um eine Enterprise Core Edition ergänzt wurden.

Verwaltungstools

Etwas gewöhnungsbedürftig ist die Tatsache, dass das SQL Server Management Studio nun nicht automatisch mitinstalliert wird, sondern eine separate Installation erfordert. Der Link der in der deutschen Version des SQL Server-Installationscenters angegeben ist, verweist leider noch auf die Januar-Preview des Management Studios. Die entsprechende englischsprachige Seite enthält allerdings den korrekten Link, über den auch die deutsche Variante der finalen Verwaltungstools heruntergeladen werden kann: https://msdn.microsoft.com/en-us/library/mt238290.aspx

Dasselbe Spiel wiederholt sich bei den SQL Server Data Tools. Auch hier verweist die deutschsprachige Seite auf eine Preview vom November 2015, während über die englischsprachige Seite bereits die finale Version (ebenfalls in deutsch) verfügbar ist: https://msdn.microsoft.com/en-us/library/mt204009.aspx

(Die aktuellen SQL Server Data Tools nutzen übrigens noch die Visual Studio 2015 Shell, da Visual Studio 2016 noch nicht final released ist.)

Weitere Informationen

Weitere Informationen zu SQL Server 2016 gibt es auf der offiziellen Produktseite:
https://www.microsoft.com/en-us/server-cloud/products/sql-server
(die deutschsprachige Variante derselben Seite ist noch nicht auf SQL 2016 umgestellt)

Dazu ist wieder ein kostenfreies eBook von Microsoft verfügbar, in dem auf 215 Seiten alle wesentlichen Neuerungen detailliert beschrieben werden:
https://info.microsoft.com/Introducing-SQL-Server-2016-eBook.html

 


Andreas Wolter: SQL Server 2016 general availability and Feature-Support // allgemeine Verfügbarkeit und Feature-Support

 

(DE)
Es ist soweit: Der neue SQL Server, in meinen Augen das beste Release seit vielen, wenn nicht gar überhaupt, ist allgemein verfügbar.
(Blog-Artikel von Joseph Sirosh, Corporate Vice President, Data Group, Microsoft)
Die Built-Nummer ist 12.0.1601.5.

(EN)
Finally: The new SQL Server 2016, in my eyes the best release since many, if not ever, is generally available.
(Blog-Artikel by Joseph Sirosh, Corporate Vice President, Data Group, Microsoft)

The Built-Number is 12.0.1601.5.

Bei MSDN kann man die entsprechende Edition (Enterprise, Standard, Web, Developer, Express – die Business Intelligence Edition fällt weg) herunterladen. Die Developer Edition ist mittlerweile sogar kostenfrei (Blog –Artikel dazu).

Download-Link:

One can download the respective version (Enterprise, Standard, Web, Developer, Express – the Business Intelligence Edition ceases to exist) at MSDN. The Developer Edition is even free of charge by now (Blog-Article on that).

Download-Link:

(DE)
https://msdn.microsoft.com/de-de/subscriptions/downloads/#searchTerm=&ProductFamilyId=650&Languages=de

(EN)
https://msdn.microsoft.com/en-us/subscriptions/downloads/#searchTerm=&ProductFamilyId=650&Languages=en

 

Über die neuen Datenbank-Engine-Features habe ich bereits hier geschrieben:
SQL Server 2016 – ein Sicherheits- und Performance-Release

I wrote about the new Database-Engine-Features before here:
SQL Server 2016 – the Security & Performance Release

Hier ein Blick auf die Von den Editionen von SQLServer 2016 unterstützte Funktionen:

msdn.microsoft.com/en-us/library/cc645993.aspx

Hervorzuheben ist hier, dass die Gerüchte um den Support von Availability Groups in der Standard Edition sich bewahrheitet haben, und sogenannte „Basic Availability Groups“ mit Unterstützung für 2 Knoten und je eine Replika je AG – ähnlich der Datenbankspiegelung – dort einsetzbar sind.

Im Sicherheits-Bereich haben es „Row-level Security“ als auch „Data Masking“ in die Standard Edition geschafft, „Always Encrypted“ jedoch nicht – diese ist nur in der Enterprise-Edition verfügbar.

Die viel-gelobte „Query Store“ ist in allen Editionen verfügbar(!) („Danke Connor“), ebenso wie „Temporal Tables“, und JSON. „R“ ist nur in einer „Basic-Variante“ allgemein verfügbar, „Stretch Database“ wiederum in allen Editionen.

In-Memory OLTP“ und „Operational Analytics“ bleiben leider der Enterprise-Edition vorbehalten.

Bei den BI-Komponenten hervorzuheben ist, dass der Analysis Services Tabular-Mode nun auch in der Standard Edition verfügbar ist. Das ist sehr hilfreich, da diese Variante gerade auch bei kleineren Projekten Sinn macht

Here a look at the Features Supported by the Editions of SQL Server 2016:

msdn.microsoft.com/en-us/library/cc645993.aspx

I want to highlight that the rumors about the support of Availability Groups in Standard Edition have been proven true and so-called „Basic Availability Groups“ with support of 2 nodes and one replica per AG – similar to Database Mirroring – can be used there.

In the Security area „Row-level Security“ and also „Data Masking“ made it into Standard Edition, but „Always Encrypted“ did not – this feature is only available in Enterprise-Edition.

The much-praised „Query Store“ is available in all Editions(!) (“Thank you, Connor”),as are „Temporal Tables“, and JSON. „R“ is available only as a „Basic-variation“ generally available, „Stretch Database“ on the other hand in all in Editions.

In-Memory OLTP“ and „Operational Analytics“ unfortunately remain reserved for Enterprise-Edition.

It should be pointed out that from the BI components the Analysis Services Tabular-Mode is now available in Standard Edition as well. This is very helpful as this type makes a lot of sense especially in small projects.

Die offizielle Seite zu SQL Server 2016 mit weiteren Informationen und Links zu Whitepapers befindet sich hier:
www.microsoft.com/de-de/server-cloud/products/sql-server/

You can find the official Page on SQL Server 2016 with further information and links to Whitepapers here:
www.microsoft.com/en-us/server-cloud/products/sql-server/

Enjoy the next generation of Microsofts Data Platform Flagship: SQL Server 2016

Andreas Wolter

Andreas Wolter: Konferenzen im SQL Server Sommer 2016 – Conferences in SQL Server summer 2016

(DE)
Kaum von den SQLBits XV in Liverpool zurück, auf der ich die Security-Features des SQL Server 2016 vorstellen durfte (SQL Server 2016 - Security Obscurity and Encryption), bin ich im Moment mit der Planung der nächsten SQL Server Master-Class zu Hochverfügbarkeit mit SQL Server (SHA) beschäftigt, die vom 20.-22.6. bei Frankfurt a.M. stattfinden wird.

Hinweis: in den 3 Wochen seit Freischaltung der Anmeldung sind bereits 70% der Plätze vergeben worden. Wer sich noch einen Platz sichern möchte aber nicht weiß, wie schnell seine Einkaufsabteilung es schafft, kann sich gern an mich direkt wenden.
- Mitglieder der PASS Deutschland e.V. erhalten auch diesmal 15% Rabatt.

(EN)
It feels like I have just been back from the SQLBits XV in Liverpool, where I introduced the Security Features of the SQL Server 2016 (SQL Server 2016 - Security Obscurity and Encryption), and next thing I know, I am already in the middle of planning the next SQL Server Master-Class on High Availability with SQL Server (SHA) that is going to take place from 20-22 June near Frankfurt a.M.

Note: in the 2 weeks since activation of registration already 70% of the places have already been filled. If you want to secure a spot but don’t know how you’re your purchase department will make it, you are welcome to contact me directly.
- Members of PASS Deutschland e.V. will once again receive a 15% discount.

 

 

Davor noch aber findet, wie die beiden Jahre zuvor, der deutsche SQLSaturday „Rheinland“ in der Hochschule St. Augustin bei Bonn statt.
Diesen unterstützt meine Firma, Sarpedon Quality Lab®, wie auch das Jahr zuvor als Sponsor.

But before, the German SQLSaturday „Rheinland“ is taking place, like the previous two years.
My company, Sarpedon Quality Lab®, will be a sponsor again like last year.

  

Ich selber werde auch mit einem Vortrag vertreten sein. Diesmal mit einem Beitrag zu ganz klassischer Performance Tracing. Allerdings nicht, wie man immer noch hin und wieder „in the wild“ sieht mit SQL Profiler, sondern natürlich mit aktuellen Technologien wie Extended Events und sogar der neuen Query Store von SQL Server 2016:

I will also be presenting. This time, it is going to be on the entirely classical topic of Performance Tracing. However, not with SQL Profiler as can be seen once in a while “in the wild,” but of course with current technologies such as Extended Events and even the new Query Store by SQL Server 2016:

Analysieren von SQL Server Workloads mit DMVs und XEvents

Diese Session führt durch eine beispielhafte Performance-Analyse unter der Verwendung von DMVs und Extended Events. Wir sehen, wie man eine Top-Down Analyse mit eingebauten Tools durchführen kann und wie man eine feingradige Analyse selbst mittels den „Wait Statistics“ durchführen kann, um Performance-Problemen und Bottlenecks auf die Schliche zu kommen. Zur Identifizierung von Plan-Änderungen wird auch die neue Query Data Store von SQL Server 2016 zum Einsatz kommen. Wer auf dem Feld der Performance-Analysen noch neu ist oder sehen möchte, wie man an die Herausforderung herangehen kann, wird hier praktische Einblicke erhalten, wie man eine Workload analysieren kann. – Die Session ist selbstverständlich „Profiler-free“ ;-)

Performance Analyzing SQL Server workloads with DMVs and XEvents

This session you will be lead you through an example performance-analysis using mainly DMVs and Extended Events. You will see how a top-down analysis using built-in tools can be conducted. This will include wait statistics on different scopes to identify performance problems and bottlenecks up to identifying query plan changes – with & without using the Query Store of SQL Server 2016. If you are new to performance analyzing this session will give you a practical insight into how to methodically approach performance troubleshooting.

 

 

Asien

Im August bin ich wieder in Asien unterwegs und das zweite Mal in Folge auf der größten SQL Server Konferenz Asiens: dem SQL Server Geeks Summit in Bangalore, Indien.

Asia

In August, I will be traveling Asia again and attending the largest SQL Server Conference in Asia for the second time in a row: the SQL Server Geeks Summit in Bangalore, India.

 

Bangalore

Dort werde ich dieses Jahr eine ganztägige Pre-Con geben. Thema: Die In-Memory Storage Engines von SQL Server, die mit dem SQL Server 2016 umfangreich verbessert worden. Das heißt es geht um ColumnStore, Memory Optimized Tabellen, Memory Optimized Indexe, und die Kobinationsmöglichkeiten mit traditioneller Row-Store oder auch ColumnStore für Mixed OLAP als auch OLTP workloads.
Es wird super spannend und sehr technisch. Wer sich damit noch nicht beschäftigt hat: es wird höchste Zeit. Die Art und Weise, wie man Datenbanken konzipiert und designt ändert sich zurzeit rapide. Ich bleibe bei meinen Vorhersagen, dass ColumnStore bald der Standard für Datawarehousing sein wird, und In-Memory OLTP der standard für hochperformante OLTP-Szenarien. Hier zeige ich die technischen Hintergründe und Machbarkeiten.

This time, I will be giving a full-day Pre-Con. Topic: The In-Memory Storage Engine of SQL Server that have been extensively improved with SQL Server 2016. That is, it will be about ColumnStore, Memory Optimized Tables, Memory Optimized Indexes, and the combination possibilities with traditional Row-Store or also ColumnStore for Mixed OLAP as well as OLTP workloads.
It will definitely be super exciting and very technical. If you haven’t dealt with this yet: it is about time! The way of conceptualizing and designing databases is changing rapidly at the moment. I am sticking to my predictions that ColumnStore will soon be the standard for Datawarehousing, and In-Memory OLTP the standard for high performing OLTP scenarios.
At this Pre-Con, I will be demonstrating the technical background and feasibility.

 

Pre-Con Title:

The Present and Future: In-Memory in SQL Server – from 0 to Operational Analytics Master

Track:                DBA/DEV

Pre-Con Abstract:

When the Columnstore Index technology, based on the xVelocity In-Memory engine, came with SQL Server 2012 in the form of Nonclustered Columnstore, and SQL Server 2014 brought us updatable Clustered Columnstore Indexes plus a completely new In-Memory OLTP Engine, “XTP”, for memory optimized table & indexes, those features were still new and because of their limitations used only rarely.

SQL Server 2016 takes both technology onto a whole new level:

Columnstore indexes among other things now support snapshot isolation and hence fully support readable secondaries. Batch execution is not exclusively for parallel threaded queries anymore. They can be combined with other B-tree indexes and even be filtered and support referential integrity with primary and foreign key constraints. Also so-called In-Memory Operational Analytics is supported by the ability to create Columnstore Indexes on memory optimized tables.

On the other hand the In-Memory engine has been extensively improved in terms of both scalability and T-SQL language support, taking away many of the relevant limitations for adaption of version 1 in a similar way than the Columnstore technology. For example altering of pre-compiled objects is now possible, bucket-counts can be adjusted, natively compiled stored procedures can be recompiled and foreign keys are supported as well as encryption with TDE.

All those improvements will make In-Memory technologies a viable option in many projects. For Datawarehouses many (including me) say, that Columnstore will become the default storage type for all objects. And it can be foreseen that over the years the same will happen for OLTP-tables that have to support highly concurrent workloads will be based on memory optimized tables.

It’s time to extend our skills to those technologies to be able implement and support the new types of storage that are coming to our databases to address the fact of ever more data being stored and queried and performance demands and (real time) analytic requirements going up.

At this full-day training day, Microsoft Certified Master for the Data Platform Andreas Wolter, familiar with SQL Servers In-Memory technologies from the early bits on, will give a complete picture on the current state of technology. Attendees will learn how and where to use either In-Memory OLTP or Columnstore or even both for efficient queries and data storing and the important bits both from developers and administrators perspective.

Modules/Topics Include:

1.          Columnstore Storage Engine and compression internals

2.          What is the benefit for OLAP performance

3.          When to use Clustered or Nonclustered Columnstore Indexes

4.          XTP Engine internals for In-Memory OLTP performance benefits

5.          Memory optimized Tables, indexes and Variables

6.          Natively compiled stored procedures & triggers

7.          Combination of Row-Store, Columnstore/xVelocity and XTP engine for operational analytics

Key Takeaways:

1.          How the new storage engines Columnstore & XTP work behind the covers

2.          What are the strengths and weaknesses of these alternate storage engines and how can they be played out best

3.          How to get a quick start with In-Memory optimized objects in almost any environment

4.          What are the typical performance patterns that these technologies address

5.          How to build highly performing Datawarehouse tables

6.          How to improve OLTP hotspot tables with In-Memory technologies

7.          How to enable real-time analytics of operational data

8.          What’s important from file management perspective for administrators

9.          How can Columnstore and In-Memory Hash- & Range-indexes be maintained

10.        What hotspots can you expect for those technologies – or is there any?

Demos:

1.          Performance-Improvements for OLAP workloads with Nonclustered Columnstore indexes …

2.          … Clustered Columnstore indexes

3.          Performance-Improvements for OLTP workloads with memory optimized tables, indexes and code

4.          Operational analytics on row store vs operational analytics on In-Memory

5.          … all under different workload-types

6.          How do Columnstore indexes handle updates to data under the covers

7.          How In-Memory optimized objects look like on disk

Attendee Pre-requisites:

1.          Basic T-SQL knowledge for code-reading

2.          clustered vs nonclustered indexes basics

 

Obendrein werde ich noch zwei normalere Sessions auf der Hauptkonferenz geben. Die Themen stehen noch nicht fest.

Ich freue mich bereits wieder auf das enthusiastische Publikum in Indien!

Added to that I will give two more normal sessions at the main conference. The topics are not final yet.

I am looking forward to the enthusiastic audience in India again!

 

Nach Indien werde ich auf der SQLSaturday Singapore präsentieren. Diese Konferenz wird bei Microsoft Singapore Operations Pte Ltd, One Marina Boulevard stattfinden – inmitten der berühmtesten Sehenswürdigkeiten Singapurs.

Hier gebe ich möglicherweise auch eine PreCon, aber die Planung ist noch nicht abgeschlossen, also mal schauen, was es sein wird. Auch auf dieses Event freue ich mich sehr.

After India I will be presenting at SQLSaturday Singapore. This event will be held at Microsoft Singapore Operations Pte Ltd, One Marina Boulevard – right in the center of the most famous sights of Singapore.

Also here I might give a PreCon, but the planning is not finalized yet, so let’s see what it will be. I am very much looking forward to this event as well.

SingaporeCu in St. Augustin, Bangalore or Singapore – your turn to choose ;-)

Andreas

Andreas Wolter: Sarpedon Quality Lab presenting SQL Server 2016 In-Memory and Security Features in Arabia at SQL Gulf 3


مرحبا


(“MARR-hah-bah”, Arabic: Hello)

This year takes me to yet another part of the world:

I have been invited to speak at THE SQL Server Conference in the Middle East: at SQL Gulf 3 taking place in Riyadh, Saudi Arabia on April 23rd.

I feel very much honored to be among the selected speakers: Denny Cherry, Victor Isakov, Peter Myers, Satya Shyam K and Shehap El-Nagar the organizer himself, who has done a great job pulling this off the third time!
For example about 7 TV stations are expected to cover this event!

(“MARR-hah-bah”, Arabisch: Hallo)

Dieses Jahr bringt mich an eine weitere Gegend dieser Welt:

Ich bin eingeladen worden auf DER SQL Server Konferenz im Nahen Osten: auf der SQL Gulf 3, die am 23. April in Riad Saudi Arabien stattfindet, als Sprecher aufzutreten.

Ich fühle mich sehr geehrt unter den ausgewählten Sprechern zu sein: Denny Cherry, Victor Isakov, Peter Myers, Satya Shyam K und Shehap El-Nagar dem Organisator selbst, der einen tollen Job gemacht hat, das zum dritten Mal zu leisten.
So werden zum Beispiel ca. 7 TV-Sender werden von diesem Event berichten!

 

 

I will be giving two presentations. The first one is on the new In-Memory capabilities of SQL Server 2016, which have been tremendously enhanced, and the second one is on the new Security features which represent one of the pillars of this release:

Ich werde zwei Vorträge halten. Einen über die neuen In-Memory Fähigkeiten von SQL Server 2016, die enorm verbessert worden sind und den zweiten zu den neuen Sicherheitsfeatures, die eine der drei Säulen dieses Releases darstellen: 

 

SQL Server 2016 – the evolution of In-Memory technologies

For SQL Server 2014 a completely new In-Memory Engine for memory optimized table & indexes was integrated into SQL Server with in fact very limited functionality.

For SQL Server 2016 the In-Memory engine is being extensively improved in terms of both scalability and T-SQL language support. Moreover, the ColumnStore index technology has been improved and can now even be combined with memory-optimized tables.

In this session I will provide an overview of the new possibilities and demonstrate where a particular technology may help – or where you cannot expect benefits. If you are planning to go on SQL Server 2016 any time soon, this session shows you two of the most important features that SQL Server 2016 brings.

 

SQL Server 2016 – the Security Release

In this session I will give insights into the most important security features of SQL Server 2016. In fact, this release will introduce completely new features that serve data security on different levels. The top 3 features are: Dynamic Data Masking, Row Level Security, and, as a highlight: Always Encrypted. Also, the new possibilities in Azure will not remain unmentioned either. The session will provide technical insights and demos but also hints to security traps. In the end a system is only as secure as its weakest spot. 
This session aims to assist Administrators as well as Developers in determining the right technologies for their needs.

 

I am looking forward to making many new contacts with people from this region of the world that is striving for modernization in many aspects and already reached the top in several.

Ich freue mich darauf, viele neue Kontakte mit Menschen aus dieser Region, die in vielerlei Hinsicht nach Modernisierung strebt und in einigen bereits Spitzenklasse erreicht hat, zu machen.

 

مع السلامة (Ma’a salama)

Andreas

Andreas Wolter: Reporting Services 2016 – Back in the game: the new capabilities & features // Zurück im Spiel: die neuen Möglichkeiten und Features

(DE)
Meine letzten Veröffentlichungen und Vorträge zu Reporting Services sind schon Jahre her. Kein Wunder, denn seit dem 2008 R2-Release ist dort nichts passiert. Nun mit SQL Server 2016 erfahren die Reporting Services endlich wieder Zuwendung, wie ich im Mai letzten Jahres bereits am Rande vorangekündigt hatte („SQL Server 2016 – ein Sicherheits- und Performance-Release“). Wird es genug sein für ein Revival? Das kann jeder für sich entscheiden. In diesem Artikel stelle ich vor, was es Neues gibt.

 (EN)
It has been years since my last publications and presentations on Reporting Services. This is no surprise, because since the 2008 R2-release nothing has happened there. With SQL Server 2016, the Reporting Services finally get attention again, as I had preannounced in passing already last May („SQL Server 2016 – the Security & Performance Release“). Will it be enough for a revival? You can decide for yourselves. In this article I will introduce what’s new.

Report-Typen

Eine der wichtigsten Neuerungen ist die Integration der DataZen-Technologie. Damit lassen sich für mobile Geräte wie Smartphones und Tablets optimierte Berichte, „Mobile Reports“ entwickeln. Dafür gibt es ein eigenes Entwicklungstool, den „Mobile Report Publisher“, das ähnlich wie der Report Builder, bei dem sich neben der Optik im Wesentlichen nichts geändert hat, aber wesentlich besser als die alte Click-Once Anwendung performt, Berichte lokal als auch auf dem Report Server speichern kann.

 Report types

One of the most important innovations is the integration of the DataZen technology. By means of this technology, optimized reports, i.e. “Mobile Reports,” can be developed for mobile devices such as smartphones and tablets. For this purpose, a particular development tool is available, the “Mobile Report Publisher,” which, similar to the Report Builder (in which, aside from the optic, basically nothing has changed, but it performs much better than the old Click-Once application), can store reports locally as well as on the Report Server.

 

 Reporting_Services_2016_Mobile_Report_Publisher

 

Reporting Services Web Portal

Die offensichtlichste Neuerung bei SSRS ist das neue „Reporting Services Web Portal“, das den alten Report Manager ablöst und auf Html5 basiert. Es unterstützt nun auch Chrome und Firefox vollständig und skaliert automatisch auf allen Bildschirmgrößen. So sieht das neue Portal aus:

 Reporting Services Web Portal

The most obvious innovation in SSRS is the new “Reporting Services Web Portal,” which replaces the old Report Manager and is based on Html5. It now also completely supports Chrome and Firefox and automatically scales to all screen sizes. This is what the new portal looks like:

 Reporting_Services_2016_Web_Portal

 

Hier sieht man auch, dass, zusätzlich zu Ordnern (oben), KPIs und Mobile Reports von den nun sogenannten „Paginated Reports“ (im deutschen „seitenbasierte Berichte) getrennt aufgeführt werden.

Here you can see that in addition to folders (above), KPIs and Mobile Reports are separately listed by the now so-called “Paginated Reports” (i.e. page-based reports).

Das neue Portal unterstützt außerdem nun auch die Möglichkeit, das Design leicht anzupassen. Das nennt sich Custom Branding.
Technisch basiert das Ganze auf einer xml-Datei, die wiederum auf eine json-Datei mit den Farbangaben und optional auf ein logo verweist. Diese 3 Dateien werden als zip-Datei verpackt und dann als „brand package“ im Portal hochgeladen werden. Dabei wird jeweils die vorherige Version einfach ersetzt.

Furthermore, the new portal now also supports the option to slightly adjust the design. This is called Custom Branding.

Technically, it is all based on an xml-file which in turn refers to a json-file containing the color specifications, and, optionally, to a logo. These 3 files are packed as a zip-file and can then be uploaded as a “brand package” in the portal. The corresponding former version is thus simply replaced.

 Reporting_Services_2016_Custom_Branding

 

Das Ergebnis im Vergleich zum Original-Design oben:

The result in comparison to the original design above:

 Reporting_Services_2016_Custom_Brand_SarpedonQualityLab

 

So praktisch das ist, und wenngleich es Reporting Services auch ohne Sharepoint-Integration attraktiver macht, so enttäuschend finde ich persönlich, dass das auch alles ist, was in Sachen Design-Standardisierung möglich ist: Berichte sind davon nämlich nicht betroffen, wie ursprünglich erhofft. Dort ist in der Richtung nichts Neues gekommen. :-(

Vielleicht finde ich also doch noch Gelegenheit, meine damals begonnene Blog-Reihe, wie man Berichts-Layout + Design am besten standardisieren und zentralisieren kann, zu vervollständigen. Die hier (Standardizing and Centralizing Report Design (or: creating style sheets for reports) Part 1: The possibilities) von mir vorgestellte Technik ist also tragischerweise immer noch „State of the Art“ für seitenbasierte Berichte.

Mobile Reports sind hier im Vorteil: diese werden durch dieses Template mit abgedeckt – sie sind ja auch deutlich einfacher und von sich aus nicht so variabel, so dass das einfacher zu implementieren war.

As practical as this may be, and while it makes Reporting Services more attractive even without Sharepoint integration, I personally find it quite disappointing that this is all that’s possible in terms of design standardization: In fact, unlike initially anticipated, reports are not affected by it. This area has not seen anything new. :-(

Perhaps I will get an opportunity after all to complete my blog series on how best to standardize and centralize report layouts and designs, which I had started back then. Tragically, the technique I presented here (Standardizing and Centralizing Report Design (or: creating style sheets for reports) Part 1: The possibilities) is thus still “state of the art” for paginated reports. 

Mobile Reports have an advantage here: they are covered by these templates – they are considerably more simple and not as variable to begin with, which made it easier to implement.

Weitere Neuerungen im Portal sind die Möglichkeit jegliche Form von Bericht als Favorit markieren und in einem gesonderten Ordner „Favoriten“ wiederzufinden, Export nach Powerpoint, sowie die Abkehr von dem problematischen Active-X Control für den Ausdruck hin zu einem Druck nach pdf:

Further innovations in the portal include the possibility to mark any report form as favorite and find them in a specific “Favorites” folder; Export to Powerpoint; and the moving away from the problematic Active-X Control for printing towards Print to PDF:

 

 Reporting_Services_2016_Print_to_pdf

 

Die Abonnements haben einige kleinere Verbesserungen erfahren und lassen sich nun zB. leicht via Knopfdruck deaktivieren. Der Besitzer lässt sich ändern und für die Auslieferung in Dateifreigeben lassen sich nun „Shared Credentials“ verwenden.

The subscriptions have seen some smaller improvements and now can, for example, be easily deactivated at the push of a button. The owner can be changed, and for delivery to fileshares “Shared Credentials” can now be used.

 

Neuerungen für seitenbasierte Berichte

-           An diese Formulierung zur Abgrenzung von Mobile Reports werden wir uns wohl gewöhnen müssen…

Viel Neues hat sich in der rdl nicht getan.
Aber das Thema „Parameter-Bereich“ wurde angegangen und ist nun deutlich flexibler. Genauer gesagt, die Anordnung der Parameter kann nun freier bestimmt werden, indem ein System an Spalten und Zeilen implementiert wurde, in denen man die Parameter frei anordnen kann.

-           Früher waren ja immer 2 Parameter nebeneinander und einer nach dem anderen ohne Lücke automatisch angeordnet und nur die Reihenfolge bestimmbar.

Mit dem neuen System kann man also auch Platz zwischen einzelnen Parametern lassen und in bis zu 8 horizontalen und bis zu 45 vertikalen Spalten anordnen – bzw. eine entsprechende Anzahl an freien Feldern Abstand lassen, wenn man das denn möchte. So sieht das Parameter-Grid aus:

Nothing much has happened in the rdl.

However, the topic “Parameter area” was tackled and is now considerably more flexible.

More precisely, the arrangement of the parameters can now be freely determined. This was made possible by implementing a system of columns and lines in which the parameters can be freely arranged.

-           Previously, 2 parameters were always arranged next to each other and one after the other without a gap, and only the order could be determined.

With the new system, it is now possible to leave space between individual parameters and arrange them in up to 8 horizontal columns and up to 45 vertical columns – or you can leave a gap of a corresponding number of free fields if you wish. This is what the parameter grid looks like:

 Reporting_Services_2016_Parameter_Grid

 

Das Grid ist jedoch nicht flexibel. Die Breite der einzelnen Spalten und Höhe der Zeilen ist fix und wird nur bei Bedarf (zu langem Text) erweitert. Auch die Farbe kann man wie zuvor nicht beeinflussen.

However, the grid is not flexible. The width of the individual columns and the height of the cells is fixed and is only increased if necessary (in case of a too long text). As before, the color cannot be changed either.

Daneben gibt es zwei neue Diagramm-Typen: „Tree Map“ und „Sunburst“. Mit ersterem lassen sich Zahlen gut und nach Hichert-Regeln ins Verhältnis setzen. Auch „Heat Maps“ sollten damit deutlich leichter zu implementieren sein. Bisher hat man sich mit spatial Daten und den entsprechenden Karten-Diagrammen beholfen. So kann eine Tree Map aussehen, die Umsätze je Land nach Kategorien verteilt darstellt:

Besides this, there are two new types of diagrams: “Tree Map” and “Sunburst.” With the former, numbers can be put in relation easily and according to Few’s rules. It should also be much more easy to implement “Heat Maps” with it. Prior to this, one had to make do with spatial data and the corresponding map diagrams. This is what a Tree Map that illustrates sales per country, and placed in categories, can look like:

 Reporting_Services_2016_TreeMap_Chart

 

Und hier ein Beispiel für ein „Sunburst“-Diagramm, mit dem sich besonders schöne psychedelische Effekte erzielen lassen. Man sagt ja, dass Visualisierungen großen Einfluss auf Entscheidungen haben können. Mit etwas Knowhow lässt sich das sicher ausbauen… ;-)

Next follows an example of a „Sunburst“-diagram with which especially beautiful psychedelic effects can be achieved. It is said that visualizations may have a great influence on decisions. With a little knowhow this could surely be enhanced… ;-)

 

 Reporting_Services_2016_Sunburst_Chart_psych

 

Kleiner Spaß…

Der Einsatz ist für Hierarchien geeignet, speziell auch für „unausgeglichene“. Hier ein Standard-Beispiel mit einer unausgeglichenen Hierarchie mit einem Sunburst-Diagramm dargestellt:

Just kidding…

Its application is suitable for hierarchies, and, in particular, also for „ragged“ hierarchies. Below, a standard example of a ragged hierarchy is illustrated with a sunburst-diagram:

 

 Reporting_Services_2016_Sunburst_Chart_ragged

 

Das war’s zu den Neuerungen auch fast schon.

Eines bleibt noch zu erwähnen: Report-Elemente wie Diagramme, Tachos, Karten oder Bilder lassen sich nun auch in Power BI Dashboards integrieren.

Well, that is about all there is on innovations.

One more thing: Report elements such as diagrams, speedometers, maps or images can now also be integrated in Power BI Dashboards.

 

Zum Abschluss noch einige Links zum Weiterlesen:

In closing, here are a couple of links for further reference:

 

 

Happy Reporting – finally :-)

Andreas

Christoph Müller-Spengler: Create failed for Availability Group Listener

Problem

Today I tried to create an AlwaysOn Availability Group. Everything went fine until I configured the Availability Group Listener.

It failed with the error message:

Create failed for Availability Group Listener <ListenerName>.
Microsoft SQL Server, Error 19471.

AvailabilityGroups_CreateListenerError

The Windows Application Event Log is a little bit more helpful on that:

Cluster network name resource <ClusterName> failed to create its
associated computer object in domain. [...]
verify that the Cluster Identity <ClusterName> has 'Full Control' permission
to that computer object using the Active Directory Users and Computers tool.

AvailabilityGroups_CreateListener_EventLog

There we go.

Solution

If you have the rights in your domain to do so, just help yourself out of this, otherwise you have to contact your Domain Administrator to help you.

On your Domain Controller:

  • Open “Active Directory Users and Computers”
  • In menu “View” check “Advanced Features” to be able to find the OU where your Cluster object is located in.
  • On the root of your domain right click and choose “Find…”
  • In drop down meny “Find” select “Computers”
  • In the text box for “Computer name” type your Cluster name and click button [ Find Now ]
  • View the “Properties” of your Cluster object that was found.
  • On tab “Object” you will find the location (OU) where your Cluster object is located in.
  • Close all popups.
  • Navigate to the location you just figured out, perform a right click and choose “Properties”.
  • In tab “Security” click the button [ Add… ]
  • Click the button [ Object Types… ] , mark the checkbox next to “Computers” and leave the popup with a click on [ OK ].
  • In the textbox enter the name of your Cluster, check the name and leave the popup with a click on button [ OK ].
  • In the field for the permissions mark the check box for “Create all child objects” and click Apply.
  • Leave the Dialog open.

 

Go to SQL Server Management Studio and repeat the attempt to create the Availability Group Listener.

Go back to the Domain Controller (or ask your Domain Admin to do so) and remove the “Create all child objects” permission for the Cluster.

 

Happy listening to your Availability Groups🙂

Christoph


Robert Panther: Neue Features für künftige Versionen von SQL Server

SQL Server 2016 steht vor der Tür und wird voraussichtlich noch in diesem Quartal veröffentlicht. Die Release Candidates sind schon verfügbar und auch die Features sind mittlerweile weitgehend bekannt. Allerdings wird bereits an der darauf folgenden Version gearbeitet, zu der nun die ersten Infos zu möglichen Features durchgesickert sind:

Gefilterte gruppierte Indizes

Die Daten der Tabelle werden in zwei verschiedenen Arten vorgehalten. Die Zeilen, die der WHERE-Klausel entsprechen werden in Form eines gruppierten Indexes abgelegt, während die restlichen Zeilen als Heap gespeichert werden. Somit können bei passenden Abfragen die Vorteile eines gruppierten Indexes mit denen eines gefilterten Indexes kombiniert genutzt werden. Im Notfall ist aber auch noch die Gesamtheit der Daten abfragbar, ohne die indizierten Daten doppelt speichern zu müssen.

Beispiel:

CREATE CLUSTERED INDEX CX_PersonPhone_BusinessEntityID_CellPhoneNumber
ON Person.PersonPhone(PhoneNumber)
WHERE PhoneNumberTypeID=1

Der neue DISLIKE-Operator

Mit dem neuen DISLIKE Vergleichsoperator steht bald eine kompaktere Form des NOT LIKE zur Verfügung.
Während die alte Variante aber eine Kombination aus zwei Operatoren ist (LIKE-Operator mit anschließender Negierung durch NOT), wird der neue DISLIKE-Operator in einem Schritt ausgeführt und arbeitet daher auch deutlich performanter.

Beispiel:

SELECT * FROM Person.Person
WHERE LastName DISLIKE ‚Brown%‘

Da es sich bei diesem neuen Operator um eine T-SQL Erweiterung handelt, dürfte dieses Feature (auch wenn es ein Performance-Feature ist) nicht nur der Enterprise Edition vorbehalten sein, sondern stattdessen auch für die kleineren Editionen von SQL Server (bis hin zu Express) zur Verfügung stehen.

Neue Datentypen: CHAR(MAX), NCHAR(MAX), BINARY(MAX)

Beim Einfügen eines Datensatzes mit einem der so deklarierten Felder, wird nach dem Speichern der übrigen Spalten der maximal verfügbare Platz auf der jeweiligen 8 KB-Speicherseite für dieses Feld belegt und mit Leerzeichen (bzw. 0-Bytes bei BINARY(MAX)) aufgefüllt. Daraus ergibt sich die Einschränkung, dass nur ein Feld pro Tabelle einen der drei genannten Datentypen verwenden kann, da sonst nicht eindeutig geregelt ist, welches Feld den verfügbaren Platz belegen kann.

Das hat zur Folge, dass ein Datensatz, der einen der genannten Datentypen verwendet, genau eine Speicherseite belegt, wodurch Themen wie Füllfaktor und Index-Padding für diese Tabellen dann nicht mehr relevant sind. Durch die daraus resultierende direkte Beziehung (Anzahl Datensätze = Anzahl Speicherseiten) kann SQL Server deutlich genauere Vorhersagen treffen, wie viele Speicherseiten für die Ausführung einer Abfrage zu lesen sind, was wiederum bessere Ausführungspläne zur Folge haben kann.

Sinnvoll einzusetzen ist dieses Feature aber sicherlich nur dann, wenn die Tabelle ohnehin so breit ist, dass nicht allzu viele Zeilen auf eine Speicherseite passen würden, da ansonsten der Vorteil der insgesamt weniger zu lesenden Speicherseiten überwiegt.

Unklar ist zum jetzigen Zeitpunkt noch, wann diese Features in SQL Server Berücksichtigung finden. Nach den bisherigen Releasezyklen wäre etwa 2018 mit der nächsten Version von SQL Server zu rechnen, wobei es bei Microsoft auch im Bereich der Serverprodukte Ansätze gibt, häufigere aber dafür kleinere Releases zu veröffentlichen.

 


Christoph Müller-Spengler: redgate SQL Monitor – The RPC Server is unavailable

Lucky me, I am setting up redgate SQL Monitor to monitor SQL Server Instances. First to say – as ever – redgate is developing ingeniously simple tools. So the installation just took minutes, the services on one VM, the repository database on another.

After having this successfully finished i logged into the Web GUI and configured my first target Host where the SQL Server Instance is running that i want to monitor.

As expected everything went fine and the windows metrics appeared like “Disk avg. write time”.

Problem

But i was not able to have a look at the SQL Server metrics. So something must have gone wrong. I had a look at Configuration -> Monitoring: “Monitored servers” and found an unconnected machine.

But wait – the first few seconds there was a “Monitoring connected” message, so something must have gone wrong a couple of seconds later on. In column “Actions” i chose the link “Show log”.

redgate_SQLMonitor_Errorlog

Of course i googled “The RPC server is unavailable” along with “0x800706BA”. As I am currently dealing with a Windows Server Failover Cluster Instance I tried the  workaround mentioned in Warning Event ID 5605 is Logged in Application log when querying MSCluster namespace through WMI and edited the ClusWMI.mof adding the value FALSE to [RequiresEncryption(FALSE)]. But that did not help.

I also followed the advice from redgate support to check all methods that SQL Monitor uses to connect to the target Host and also the SQL Server Instance.

I also checked the firewall settings allowing tcp communication on port 135 and > 1024. Everything open.

When it came to WMI test using WbemTest I tried to connect to my target Host, but also received the well known error:

redgate_SQLMonitor_Wbem

So I consulted a colleague of mine to double check if my target Host was available from another VM.

Guess what: It was. So it must have had something to do with the configuration of the two VMs we were trying to connect to the target Host.

She mentioned that she had configured the DNS suffixes in the local search list.

That did the trick. With all my unsuccessful attempts to connect to my target Host I always defined it fqdn in SQL Monitor. But i guess that the WMI security settings on my target Host prevented me from getting the right response.

Solution

As the target Host is not in the same domain that my monitoring VM is in, i had to put the IP Address of the very target Host into the hosts file on the monitoring VM in location “C:\Windows\System32\drivers\etc\hosts”


123.45.67.89    SERVERNAME

Please note that I just put in the NetBIOS Name of the Server.

That did the trick.

Going back to SQL Monitor, deleting my unsuccessfully configured taret Host and configuring it once again, but this time not fqdn but only with the SERVERNAME it all worked well and finally i got everything up and running.

Happy monitoring to all of you🙂

Thank you for reading,

Christoph

 

 

 


Robert Panther: SQL Server 2016 RC1 verfügbar

Die Schlagzahl erhöht sich spürbar. Nachdem am 9. März der erste Release Candidate (RC0) von SQL Server erschienen ist, legte Microsoft lediglich 9 Tage später bereits nach und hat den Release Candidate 1 zum Download bereitgestellt:

https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

Wie beim ersten Release Candidate handelt es sich um eine 180 Tage-Testversion (bis dahin dürfte die finale Version ja auf jeden Fall verfügbar sein).

Die wesentlichen Änderungen zum RC0 scheinen im Reporting Services Bereich zu liegen, wobei man bei den Release Candidates davon ausgehen sollte, dass sich an den Features ohnehin nicht mehr viel ändert, sondern vorrangig an der Beseitigung von Fehlern gearbeitet wird.

 


Robert Panther: Teil 2 der Artikelserie zum Thema SQL Server Indizes in der Windows Developer

In der aktuellen Ausgabe (4.16) der Zeitschrift Windows Developer ist inzwischen der zweite Teil meiner vierteiligen Artikelserie zum Thema SQL Server Indizes erschienen. Darin geht es um zusätzliche Indexoptionen sowie die Wartung von Indizes (und Indexstatistiken).

Nähere Infos dazu auf der Verlags-Website:
https://entwickler.de/windows-developer-magazin/windows-developer-4-16-210713.html

 


Robert Panther: Nachlese zur SQL Konferenz 2016 in Darmstadt

Die SQL Server Konferenz 2016 in Darmstadt ist zu Ende. 3 Tage Konferenz (davon ein PreCon Workshop Tag), ca. 50 Speaker (darunter auch viele MVPs aus dem In- und Ausland) und etwa 500 Teilnehmer machten das darmstadtium für eine kurze Zeit zum Zentrum der deutschen SQL Server Szene.

Die (nachträglich noch etwas ergänzten) Slides zu meinem Vortrag zum Thema Datenqualität können hier heruntergeladen werden: Datenqualität_Panther

SQLKonferenz2016_Panther


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