Bernd Jungbluth: Seminar - SQL Server Integration Services

Nach langer Zeit findet das Seminar SQL Server Integration Services wieder statt:

26. + 27. April 2016 im Hotel Ebertor in Boppard am Rhein

SQL Server Integration Services - kurz SSIS - ist das ETL-Tool von Microsoft zur Integration von Daten unterschiedlicher Quellen in ein Datawarehouse-System. Das Füllen und Aktualisieren von Datawarehouse-Systemen ist jedoch nicht die einzige Einsatzmöglichkeit von SSIS.

SSIS lässt sich immer dann einsetzen, wenn es darum geht, Daten zu importieren, exportieren, transformieren, aufzubereiten, migrieren, konsolidieren oder zu integrieren - ob nun als einfache Datentransfers, Import-/Export-Routinen, ETL-Lösungen oder als komplexe Datenintegrationslösungen.

In diesem 2-tägigen Seminar wird die Realisierung von SSIS-Projekten behandelt.
Dabei steht die Entwicklung dieser Projekte im Mittelpunkt, ergänzt mit deren Bereitstellung und Betrieb.

Das Seminar basiert auf SQL Server Integration Services in den Versionen 2012 und 2014.

Preis: 700 Euro inkl. Mittagessen und Getränke zzgl. MwSt.

Die Teilnehmerzahl ist auf 8 Personen begrenzt.

Weitere Informationen zum Seminar gibt es unter www.berndjungbluth.de/ssis.pdf.

Uwe Ricken: Zusammenhang zwischen dynamischem SQL und veralteten Statistiken

In einem aktuellen Projekt bin ich auf eine Technik gestoßen, die – LEIDER – noch viel zu häufig von Programmierern im Umfeld von Microsoft SQL Server angewendet wird; Konkatenation von Texten zu vollständigen SQL-Befehlen und deren Ausführung mittels EXEC(). Dieser Artikel beschreibt einen – von vielen – Nachteil, der sich aus dieser Technik ergibt und zeigt einen Lösungsweg, die in den nachfolgenden Beispielen gezeigten Nachteile zu umgehen.

Dynamisches SQL

Unter “dynamischem SQL“ versteht man eine Technik, mit der man SQL-Fragmente mit variablen Werten (meistens aus zuvor deklarierten Variablen) zur Laufzeit zusammensetzt so dass sich aus den Einzelteilen am Ende ein vollständiges SQL Statement gebildet hat. Dieser “SQL-Text“ wird entweder mit EXEC() oder mit sp_executesql ausgeführt. Das nachfolgende Code-Beispiel zeigt die – generelle – Vorgehensweise:

-- Erstellen einer Demotabelle mit verschiedenen Attributen
CREATE TABLE dbo.demo_table
(
    Id        INT          NOT NULL    IDENTITY (1, 1),
    KundenNo  CHAR(5)      NOT NULL,
    Vorname   VARCHAR(20)  NOT NULL,
    Nachname  VARCHAR(20)  NOT NULL,
    Strasse   VARCHAR(20)  NOT NULL,
    PLZ       VARCHAR(10)  NOT NULL,
    Ort       VARCHAR(20)  NOT NULL
);
GO
 
/* Eintragen von 5 Beispieldatensätzen */
SET NOCOUNT ON;
GO
 
INSERT INTO dbo.demo_table (KundenNo, Vorname, Nachname, Strasse, PLZ, Ort)
VALUES
('00001', 'Uwe', 'Ricken', 'Musterweg 10', '12345', 'Musterhausen'),
('00002', 'Berthold', 'Meyer', 'Parkstrasse 5', '98765', 'Musterburg'),
('00003', 'Beate', 'Ricken', 'Badstrasse 15', '87654', 'Monopoly'),
('00004', 'Emma', 'Ricken', 'Badstrasse 15', '87654', 'Monopoly'),
('00005', 'Udo', 'Lohmeyer', 'Brühlgasse 57', '01234', 'Irgendwo');
GO

Der Code erstellt eine Beispieltabelle und füllt sie mit 5 Datensätzen. Dynamisches SQL wird anschließend wie folgt angewendet:

DECLARE @stmt  NVARCHAR(4000);
DECLARE @col   NVARCHAR(100);
DECLARE @Value NVARCHAR(100);
 
SET @Col   = N'Nachname';
SET @Value = N'Ricken';
 
SET @stmt = N'SELECT * FROM dbo.demo_table ' +
CASE WHEN @Col IS NOT NULL
     THEN N'WHERE ' + @Col + N' = ' + QUOTENAME(@Value, '''') + N';'
     ELSE N''
END
 
SELECT @stmt;
EXEC sp_executesql @stmt;
 
SET @Col = N'PLZ';
SET @Value = N'87654';
 
SET @stmt = N'SELECT * FROM dbo.demo_table ' +
CASE WHEN @Col IS NOT NULL
     THEN N'WHERE ' + @Col + N' = ' + QUOTENAME(@Value, '''') + N';'
     ELSE N''
END
 
SELECT @stmt;
EXEC sp_executesql @stmt;

Den deklarierten Variablen werden Parameterwerte zugewiesen und anschließend wird aus diesen Parametern ein SQL-Statement generiert. Dieses SQL-Statement wird im Anschluss ausgeführt und das Ergebnis ausgegeben.

Dynamic_Results_01

Statistiken

Basierend auf Statistiken generiert Microsoft SQL Server einen Ausführungsplan für die Durchführung einer Abfrage. Wenn Statistiken nicht akkurat/aktuell sind, kann im Ergebnis die Abfrage unperformant sein, da Microsoft SQL Server zum Beispiel zu wenig Speicher für die Durchführung reserviert hat. Wie unterschiedlich Ausführungspläne sein können, wenn Microsoft SQL Server weiß, wie viele Datensätze zu erwarten sind, zeigt das nächste Beispiel:

CREATE TABLE dbo.Addresses
(
    Id       INT          NOT NULL IDENTITY (1, 1),
    Strasse  CHAR(500)    NOT NULL DEFAULT ('Einfach nur ein Füller'),
    PLZ      CHAR(5)      NOT NULL,
    Ort      VARCHAR(100) NOT NULL
);
GO
 
CREATE UNIQUE CLUSTERED INDEX cix_Addresses_Id ON dbo.Addresses (Id);
GO
 
/* 5000 Adressen aus Frankfurt */
INSERT INTO dbo.Addresses (PLZ, Ort) VALUES ('60313', 'Frankfurt am Main');
GO 5000
 
/* 1000 Adressen aus Darmstadt */
INSERT INTO dbo.Addresses (PLZ, Ort) VALUES ('64283', 'Darmstadt');
GO 1000
 
/* 100 Adressen aus Hamburg */
INSERT INTO dbo.Addresses (PLZ, Ort) VALUES ('20095', 'Hamburg');
GO 100
 
/* 100 Adressen aus Erzhausen */
INSERT INTO dbo.Addresses (PLZ, Ort) VALUES ('64390', 'Erzhausen');
GO 100
 
/* Erstellung eines Index auf ZIP*/
CREATE NONCLUSTERED INDEX ix_Addresses_ZIP ON dbo.Addresses (PLZ);
GO

Der Code erstellt eine Tabelle mit dem Namen [dbo].[Addresses] und füllt sie mit unterschiedlichen Mengen verschiedener Adressen. Während für eine Großstadt wie Frankfurt am Main sehr viele Adressen in der Tabelle vorhanden sind, sind das für ein Dorf nur wenige Datensätze. Sobald alle Datensätze in die Tabelle eingetragen wurden, wird zu Guter Letzt auf dem Attribut [ZIP] ein Index erstellt, um effizient nach der PLZ zu suchen.

/* Beispiel für viele Datensätze */
SELECT * FROM dbo.Addresses WHERE PLZ = '60313';
GO
 
/* Beispiel für wenige Datensätze */
SELECT * FROM dbo.Addresses WHERE PLZ = '64390';
GO

Die beiden Abfragen erzeugen unterschiedliche Abfragepläne, da – je nach Datenmenge – die Suche durch die gesamte Tabelle effizienter sein kann, als jeden Datensatz einzeln zu suchen.

Execution_Plan_01

Die Abbildung zeigt, dass für eine große Datenmenge (5.000 Datensätze) ein Suchmuster über die komplette Tabelle für den Query Optimizer die schnellste Möglichkeit ist, die gewünschten Daten zu liefern. Bei einer – deutlich – kleineren Datenmenge entscheidet sich der Query Optimizer für eine Strategie, die den Index auf [ix_Adresses_ZIP] berücksichtigt aber dafür in Kauf nimmt, dass fehlende Informationen aus der Tabelle entnommen werden müssen (Schlüsselsuche/Key Lookup).
Die entsprechende Abfragestrategie wird unter Zuhilfenahme von Statistiken realisiert. Microsoft SQL Server überprüft die Verteilung der Daten im Index [ix_Addresses_ZIP] und entscheidet sich – basierend auf dem Ergebnis – anschließend für eine geeignete Abfragestrategie.

DBCC SHOW_STATISTICS ('dbo.Addresses', 'ix_Adresses_ZIP') WITH HISTOGRAM;

DBCC_STATISTICS_01

Testumfeld

Im der Testumgebung wird eine Tabelle mit dem Namen [dbo].[Orders] angelegt. Diese Tabelle besitzt 10.000.000 Datensätze, die pro Handelstag die Orders aus einem Internetportal speichert. Dazu werden die Käufe jede Nacht von der Produktionsdatenbank in die Reporting-Datenbank übertragen. Insgesamt sind Bestellungen vom 01.01.2015 bis zum 10.01.2016 in der [dbo].[Orders] gespeichert. Pro Tag kommen 25.000 – 30.000 Bestellungen dazu. Die Tabelle hat folgende Struktur:

CREATE TABLE dbo.Orders
(
    Order_Id      INT     NOT NULL   IDENTITY (1, 1),
    Customer_No   CHAR(5) NOT NULL,
    OrderDate     DATE    NOT NULL,
    ShippingDate  DATE    NULL,
    Cancelled     BIT     NOT NULL   DEFAULT (0)
);
GO
 
CREATE UNIQUE CLUSTERED INDEX cix_Orders_Order_Id ON dbo.Orders(Order_ID);
CREATE NONCLUSTERED INDEX ix_Orders_Customer_No ON dbo.Orders (Customer_No);
CREATE NONCLUSTERED INDEX ix_Orders_OrderDate ON dbo.Orders (OrderDate);
GO

Die – aktuellen – Statistiken für das Bestelldatum (OrderDate) sind bis zum 10.01.2016 gepflegt!

DBCC_STATISTICS_02

Für die Abfrage(n) aus dieser Tabelle wird eine Stored Procedure mit dem folgenden Code programmiert:

CREATE PROC dbo.proc_SearchOrders
    @Search_Shipping    BIT,
    @Search_Date        DATE,
    @Additional_Column  NVARCHAR(64),
    @Additional_Value   VARCHAR(64)
AS
BEGIN
    SET NOCOUNT ON
 
    DECLARE @stmt NVARCHAR(4000) = N'SELECT * FROM dbo.Orders'
    IF @Search_Shipping = 1
        SET @stmt = @stmt + N' WHERE ShippingDate = ''' + CONVERT(CHAR(8), @Search_Date, 112) + N''''
    ELSE
        SET @stmt = @stmt + N' WHERE OrderDate = ''' + CONVERT(CHAR(8), @Search_Date, 112) + N''''
 
    IF @Additional_Column IS NOT NULL
        SET @stmt = @stmt + N' AND ' + @Additional_Column + N' = ''' + @Additional_Value + ''''
 
    EXEC sp_executesql @stmt;
 
    SET NOCOUNT OFF;
END
GO

Der Code verwendet dynamisches SQL, um einen ausführbaren Abfragebefehl zu konkatenieren. Dabei werden nicht nur die zu verwendenden Spalten konkateniert sondern auch die abzufragenden Werte werden dynamisch dem SQL-String hinzugefügt. Somit ergibt sich bei der Ausführung der Prozedur je nach Parameter immer ein unterschiedlicher Abfragebefehl wie die folgenden Beispiele zeigen:

EXEC dbo.proc_SearchOrders
    @Search_Shipping = 0,
    @Search_Date = '20160101',
    @Additional_Column = NULL,
    @Additional_Value = NULL;
GO
 
EXEC dbo.proc_SearchOrders
    @Search_Shipping = 0,
    @Search_Date = '20160101',
    @Additional_Column = 'Customer_No',
    @Additional_Value = '44196';

Dynamic_Results_02

Problem

Abhängig von den Parametern werden unterschiedliche Abfragebefehle konkateniert. Microsoft SQL Server kann – bedingt durch die unterschiedlichen Kombinationen aus abzufragenden Spalten und abzufragenden Werten – keinen einheitlichen Ausführungsplan für die Abfrage erstellen. Sobald ein ausführbarer SQL Code geringster Abweichungen (Kommentare, Leerzeichen, Werte) besitzt, behandelt Microsoft SQL Server den Ausführungstext wie eine NEUE Abfrage und erstellt für die auszuführende Abfrage einen neuen Ausführungsplan.

EXEC dbo.proc_SearchOrders
    @Search_Shipping = 0,
    @Search_Date = '20160105',
    @Additional_Column = NULL,
    @Additional_Value = NULL;
GO

Die Prozedur erzeugt den folgenden Ausführungsplan:

Dynamic_Results_03

Microsoft SQL Server prüft die Daten für den 05.01.2016 im Histogramm und schätzt, dass ca. 26.600 Datensätze zurückgeliefert werden. Diese “Schätzung“ ist sehr nah an den realen Daten und die Abfrage wird mittels INDEX SCAN durchgeführt. Je nach Datum werden immer wieder NEUE Ausführungspläne generiert und im Prozedur Cache abgelegt.
Statistiken werden von Microsoft SQL Server automatisch aktualisiert, wenn mindestens 20% der Daten in einem Index geändert wurden. Sind sehr viele Datensätze in einem Index, dann kann diese Aktualisierung recht lange auf sich warten lassen.
Wenn Microsoft SQL Server mit einem Abfragewert konfrontiert wird, der NICHT in den Statistiken vorhanden ist, dann „schätzt“ Microsoft SQL Server immer, dass sich 1 Datensatz in der Tabelle befindet. Dieses Problem kommt im obigen Beispiel zum tragen. Jeden Tag werden die aktuellsten Orders in die Tabelle eingetragen. In der Tabelle befinden sich 10.000.000 Datensätze. Insgesamt müssten nun 2.000.000 Datenänderungen durchgeführt werden, um die Statistiken automatisch zu aktualisieren.
Die Stored Procedure wird im nächsten Beispiel für den 11.01.2016 aufgerufen. Wie aus der Abbildung erkennbar ist, sind Werte nach dem 10.01.2016 noch nicht in der Statistik vorhanden. Sollten also Werte in der Tabelle sein, dann verarbeitet Microsoft SQL Server die Anfrage wie folgt:

  • Da der Parameter für das OrderDate im Abfragetext konkateniert wird, wird ein NEUER Abfrageplan erstellt
  • Bei der Erstellung des Plans schaut Microsoft SQL Server in die Statistiken zum OrderDate und stellt fest, dass der letzte Eintrag vom 10.01.2016 ist
  • Microsoft SQL Server geht davon aus, dass für den 11.01.2016 lediglich 1 Datensatz in der Datenbank vorhanden ist
  • Der Ausführungsplan wird für 1 Datensatz geplant und gespeichert

Dynamic_Results_04

Die Abbildung zeigt den Ausführungsplan in Microsoft SQL Server, wie er für den 11.01.2016 geplant wurde. Es ist erkennbar, dass die geschätzte Anzahl von Datensätzen DEUTLICH unter dem tatsächlichen Ergebnis liegt. Solche Fehleinschätzungen haben in einem Ausführungsplan Seiteneffekte:

  • Der geplante Speicher für die Ausführung der Abfrage wird niedriger berechnet als er tatsächlich benötigt wird. Da nachträglich kein Speicher mehr allokiert werden kann, werden einige Operatoren die Daten in TEMPDB zwischenspeichern (SORT / HASH Spills)
  • Nested Loops sind ideal für wenige Datensätze. Ein Nested Loop geht für jeden Datensatz aus der “OUTER TABLE“ in die “INNER TABLE“ und fragt dort über das Schlüsselattribut Informationen ab. Im obigen Beispiel hat sich Microsoft SQL Server für einen Nested Loop entschieden, da das geschätzte IO für einen Datensatz deutlich unter einem INDEX SCAN liegt. Tatsächlich müssen aber nicht 1 Datensatz aus dem Clustered Index gesucht werden sondern 2.701 Datensätze!

Konkatenierte SQL Strings verhalten sich wie Ad Hoc Abfragen. Sie führen dazu, dass der Prozedur/Plan Cache übermäßig gefüllt wird. Ebenfalls geht wertvolle Zeit verloren, da für jede neue Konkatentation ein neuer Plan berechnet und gespeichert werden muss!
Die nächste Abfrage ermittelt – aus den obigen Beispielen – die gespeicherten Ausführungspläne.

SELECT DEST.text,
DECP.usecounts,
DECP.size_in_bytes,
DECP.cacheobjtype
FROM sys.dm_exec_cached_plans AS DECP
CROSS APPLY sys.dm_exec_sql_text (DECP.plan_handle) AS DEST
WHERE DEST.text LIKE ‚%Orders%‘ AND
DEST.text NOT LIKE ‚%dm_exec_sql_text%‘;

RESULTSET_01

Das Ergebnis zeigt, dass die Prozedur zwei Mal aufgerufen wurde. Dennoch musste für JEDEN konkatenierten Ausführungstext ein eigener Plan erstellt und gespeichert werden.

Lösung

Nicht immer kann man auf dynamisches SQL verzichten. Es wird immer Situationen geben, in denen man mit den Herausforderungen von dynamischen SQL konfrontiert wird. In diesen Situationen ist es wichtig, zu verstehen, welchen Einfluss solche Entscheidungen auf die Performance der Abfragen haben. Im gezeigten Fall sind – unter anderem – Statistiken ein Problem. Es muss also eine Lösung geschaffen werden, die darauf baut, dass Pläne wiederverwendet werden können – Parameter! Es muss eine Lösung gefunden werden, bei der zwei wichtige Voraussetzungen erfüllt werden:

  • Der auszuführende Befehl darf sich nicht mehr verändern
  • Ein einmal generierter Plan muss wiederverwendbar sein

Beide Voraussetzungen kann man mit leichten Modifikationen innerhalb der Prozedur schnell und einfach erfüllen.

ALTER PROC dbo.proc_SearchOrders
    @Search_Shipping    BIT,
    @Search_Date        DATE,
    @Additional_Column  NVARCHAR(64),
    @Additional_Value   VARCHAR(64)
AS
BEGIN
    SET NOCOUNT ON
 
    DECLARE @stmt NVARCHAR(4000) = N'SELECT * FROM dbo.Orders';
    DECLARE @vars NVARCHAR(1000) = N'@Search_Date DATE, @Additional_Value VARCHAR(64)';
 
    IF @Search_Shipping = 1
        SET @stmt = @stmt + N' WHERE ShippingDate = @Search_Date';
    ELSE
        SET @stmt = @stmt + N' WHERE OrderDate = @Search_Date';
 
    IF @Additional_Column IS NOT NULL
        SET @stmt = @stmt + N' AND ' + QUOTENAME(@Additional_Column) + N' = @Additional_Value';
 
    SET @stmt = @stmt + N';'
    SELECT    @stmt;
    EXEC sp_executesql @stmt, @vars, @Search_Date, @Additional_Value;
 
    SET NOCOUNT OFF;
END
GO

Nachdem der Prozedur Cache gelöscht wurde, wird die Prozedur erneut in verschiedenen Varianten ausgeführt und die Abfragepläne werden analysiert:

EXEC dbo.proc_SearchOrders
    @Search_Shipping = 0,
    @Search_Date = '20160105',
    @Additional_Column = NULL,
    @Additional_Value = NULL;
GO

Dynamic_Results_05

EXEC dbo.proc_SearchOrders
    @Search_Shipping = 0,
    @Search_Date = '20160111',
    @Additional_Column = NULL,
    @Additional_Value = NULL;
GO

Dynamic_Results_06

Obwohl nun ein Datum verwendet wird, dass nachweislich noch nicht in den Statistiken erfasst ist, wird dennoch ein identischer Plan verwendet. Ursächlich für dieses Verhalten ist, dass Microsoft SQL Server für beide Ausführungen auf ein identisches Statement verweisen kann – somit kann ein bereits im ersten Durchlauf verwendeter Ausführungsplan angewendet werden. Dieses “Phänomen“ wird Parameter Sniffing genannt. Auch dieses Verfahren hat seine Vor- und Nachteile, die ich im nächsten Artikel beschreiben werde.

EXEC dbo.proc_SearchOrders
    @Search_Shipping = 0,
    @Search_Date = '20160105',
    @Additional_Column = 'Customer_No',
    @Additional_Value = '94485';
GO

Dynamic_Results_07

EXEC dbo.proc_SearchOrders
    @Search_Shipping = 0,
    @Search_Date = '20160105',
    @Additional_Column = 'Cancelled',
    @Additional_Value = '1';
GO

Dynamic_Results_08

Die nächsten zwei Beispiele zeigen die Ausführungspläne mit jeweils unterschiedlichen – zusätzlichen – Kriterien. Es ist erkennbar, dass Microsoft SQL Server nun für beide Ausführungen jeweils unterschiedliche Ausführungspläne verwendet. Unabhängig von dieser Tatsache werden nun aber nicht mehr für jeden unterschiedlichen Kunden EINZELNE Pläne gespeichert sondern der bei der ersten Ausführung gespeicherte Ausführungsplan wiederverwendet. Die nachfolgende Abfrage zeigt im Ergebnis die gespeicherten Ausführungspläne, die für 10 weitere – unterschiedliche – Kundennummern verwendet wurden:

SELECT DEST.text,
       DECP.usecounts,
       DECP.size_in_bytes,
       DECP.cacheobjtype
FROM   sys.dm_exec_cached_plans AS DECP
       CROSS APPLY sys.dm_exec_sql_text (DECP.plan_handle) AS DEST
WHERE  DEST.text LIKE '%Orders%' AND
       DEST.text NOT LIKE '%dm_exec_sql_text%';
GO

RESULTSET_02

Statt – wie bisher – für jede Ausführung mit unterschiedlichen Kundennummern einen eigenen Plan zu speichern, kann der einmal generierte Plan verwendet werden.

Zusammenfassung

Dynamisches SQL wird recht häufig verwendet, um mit möglichst einer – zentralen – Prozedur mehrere Möglichkeiten abzudecken. So legitim dieser Ansatz ist, so gefährlich ist er aber, wenn man dynamisches SQL und Konkatenation wie “gewöhnlichen“ Code in einer Hochsprache verwendet. Microsoft SQL Server muss dann für JEDE Abfrage einen Ausführungsplan generieren. Dieser Ausführungsplan beruht auf Statistiken, die für einen idealen Plan benötigt werden. Sind die Statistiken veraltet, werden unter Umständen schlechte Pläne generiert. Sind die Daten regelmäßig verteilt, bietet es sich an, mit Parametern statt konkatenierten SQL Statements zu arbeiten. Durch die Verwendung von Parametern wird einerseits der Plan Cache entlastet und andererseits muss Microsoft SQL Server bei wiederholter Ausführung mit anderen Werten nicht erneut einen Ausführungsplan erstellen und eine Prüfung der Statistiken entfällt.
Wo Licht ist, ist natürlich auch Schatten! Die oben beschriebene Methode bietet sich nur dann an, wenn die Daten regelmäßig verteilt sind. Wenn die abzufragenden Daten in der Anzahl ihrer Schlüsselattribute zu stark variieren, ist auch diese Lösung mangelhaft! Für das Projekt konnten wir mit dieser Methode sicherstellen, dass NEUE Daten geladen werden konnten und Statistiken nicht notwendiger Weise aktualisiert sein mussten. Die Verteilung der Daten ist für jeden Tag nahezu identisch!
Herzlichen Dank fürs Lesen!

Christoph Muthmann: Microsoft ändert Bewertung von CUs

Beginnend mit der aktuellen Version der CUs (cumulative updates) für SQL Server 2012 ändert Microsoft seine Politik in Bezug auf diese Updates.

Ganze Geschichte »

Constantin Klein: Kumulative Updates für SQL Server 2012 SP2 und SQL Server 2012 SP3 veröffentlicht

Mit guten Vorsätzen ins neue Jahr. Nachdem es hier lange Zeit sehr ruhig war, möchte ich Euch in diesem Jahr wieder häufiger mit neuen Artikeln beglücken. Los geht es mit neuen kumulativen Updates für SQL Server 2012. Microsoft hat zwei neue kumulative Updates für SQL Server veröffentlicht: Update KB Artikel Build Nr. Anzahl Fixes SQL […]

Uwe Ricken: Änderung der Sortierung und Einfluss auf das Transaktionsprotokoll

Ein von mir sehr geschätzter Kollege – der SQL Paparazzi der PASS Deutschland – Dirk Hondong (t | w) – hat mich während meines Vortrags bei der PASS Usergroup in Köln gefragt, inwieweit man belegen kann, ob die Änderung der Sortierung einer Spalte eine reine Metadaten-Operation ist oder ob eine solche Änderung auch die Änderung von Daten nach sich zieht. Die Frage fand ich so interessant, dass ich mich gleich an die Untersuchung gemacht hatte, um selbst festzustellen, welche Auswirkungen die Änderung der Sortierung auf die entstehenden Transaktionen haben.

Warum eine Datenbank oder ein Objekt eine Sortierung braucht.

Sobald mit einem Datenbanksystem gearbeitet wird, werden Tabellen angelegt, Daten gespeichert und mit Hilfe von Indexen organisiert. Für die Reihenfolge der Daten in einem Index gibt es zwei grobe Richtungen. Handelt es sich um numerische Werte, ist die Sortierung einer Datenbank oder einer Spalte irrelevant. Sobald jedoch ein Index auf ein nicht numerisches Feld angewendet wird, muss Microsoft SQL Server für die richtige Sortierung der Werte im Index die Datenbank- oder Spaltensortierung berücksichtigen. Das nachfolgende Beispiel zeigt eine Tabelle mit zwei Textspalten. Beide Textspalten haben unterschiedliche Sortierungen und werden mit einem dedizierten Index pro Spalte versehen.

/* Create the demo table with different collations */
CREATE TABLE dbo.demo_table
(
    Id    INT      NOT NULL,
    c1    CHAR(1)  COLLATE Latin1_General_CI_AS    NOT NULL,
    c2    CHAR(1)  COLLATE Latin1_General_BIN      NOT NULL
);
 
/* Create an index on each different column */
CREATE INDEX ix_demo_table_c1 ON dbo.demo_table (c1);
CREATE INDEX ix_demo_table_c2 ON dbo.demo_table (c2);
GO
 
/* fill the table with A-Z and a-z */
DECLARE @i INT = 65;
WHILE @i <= 90
BEGIN
    INSERT INTO dbo.demo_table (Id, C1, C2)
    SELECT @i, CHAR(@i), CHAR(@i)
    UNION ALL
    SELECT @i + 32, CHAR(@i + 32), CHAR(@i + 32)
 
    SET @i += 1;
END
GO
 
/* Select the different columns by usage of it's index */
SELECT c1 FROM dbo.demo_table;
SELECT c2 FROM dbo.demo_table;
GO

Für die beiden Abfragen werden die auf den Spalten befindlichen Indexe verwendet und man kann im Ergebnis deutlich erkennen, dass beide Spalten nach unterschiedlichen Mustern sortiert werden.

Rowsets_01

Die nebenstehende Abbildung zeigt auf der linken Seite die Spalte [c1]. Diese Spalte verwendet eine Sortierung, die Groß- und Kleinschreibung nicht unterscheidet (“_CI_” = Case Insensitive) während die Spalte [c2] eine binäre Sortierung anwendet. Bei der binären Sortierung werden die Werte in der Spalte nach ihrem Binärwert sortiert. Da die Kleinbuchstaben einen höheren Binärwert haben (Großbuchstaben beginnen bei 0x41 und Kleinbuchstaben beginnen bei 0x5B) werden sie erst NACH den Großbuchstaben einsortiert.

Die einstellte Sortierung für eine Spalte, die alphanumerische Werte enthält ist beim Einsatz eines Indexes (egal ob Clustered Index oder Nonclustered Index) ein ausschlaggebendes Moment für die Einordnung der Werte, die in diese Spalte gespeichert werden sollen.

Ändern einer Sortierung

Man kann jederzeit für einzelne Attribute, eine Datenbank oder für den Server die Sortierung ändern. Während die Änderung für Datenbanken und Tabellen nachträglich mit ertragbarem Aufwand verbunden ist, ist die Änderung der Sortierung für den Server mit deutlich mehr Aufwand verbunden. Weitere Informationen zum Ändern der Sortierungen finden sich hier:

Sobald eine Datenbank Daten enthält und es sollen nachträglich Änderungen an der Sortierung vorgenommen werden, dann ist es nur recht, dass man sich Gedanken darüber macht, wie hoch wohl das Transaktionsvolumen dieser Transaktion ist. Sofern es sich nur um Änderungen am Schema handelt (Schemaänderungen), wird das Transaktionsvolumen in einem verträglichen Rahmen bleiben; sollten jedoch die gespeicherten Daten betroffen sein, muss man sich eine entsprechende Strategie zurechtlegen, um die Änderungen sorgfältig zu planen.

Beispielszenario

Um zu prüfen, welche Ressourcen bei der Änderung der Sortierung an der Spalte einer Tabelle beteiligt sind, wird zunächst eine Beispieltabelle mit 1.000 Datensätzen erstellt. Diese Tabelle besitzt – im ersten Beispiel – keine Indexe; es handelt sich also um einen HEAP.

/* Create a HEAP with a few demo data and default collation */
CREATE TABLE dbo.demo_table
(
    Id    INT        NOT NULL    IDENTITY(1, 1),
    C1    CHAR(3)    NOT NULL    DEFAULT ('DE'),
    C2    CHAR(5)    NOT NULL    DEFAULT ('12345')
);
GO
 
/* what is the collation of the string attributes? */
SELECT  C.name            AS column_name,
        S.name            AS type_name,
        C.max_length      AS data_length,
        C.collation_name  AS collation_name
FROM    sys.tables AS T INNER JOIN sys.columns AS C
        ON (T.object_id = C.object_Id) INNER JOIN sys.types AS S
        ON  (
               C.user_type_id = S.user_type_id AND
               C.system_type_id = S.system_type_id
            )
WHERE   T.name = 'demo_table'
ORDER BY
        C.column_id;
GO

Zunächst wird die Tabelle mit der Sortierung der Datenbank angelegt. Die Abfrage zeigt, welche Sortierung für die einzelnen Spalten verwendet werden (in meinem Beispiel ist es Latin1_General_CI_AS).

Rowsets_02

Anschließend wird diese Tabelle mit 1.000 Datensätzen gefüllt und die Test können beginnen.

Änderung der Sortierung in HEAP

Die Tabelle besitzt keine Indexe – sie ist ein HEAP. Um für eine Spalte eine Eigenschaft zu ändern, muss mit Hilfe von ALTER TABLE … ALTER COLUMN die Eigenschaft angepasst werden. Das nachfolgende Beispiel verwendet eine explizite Transaktion für diese Anpassungen. Diese explizite Transaktion muss verwendet werden, um nach der Aktion festzustellen, welche Ressourcen durch die Aktion gesperrt/verwendet werden. Gleichfalls kann mit Hilfe einer benannten Transaktion der entsprechende Eintrag im Transaktionsprotokoll gefunden werden (siehe Code).

/* to monitor the behavior of the transaction we wrap it in a named transaction */
BEGIN TRANSACTION ChangeCollation;
GO
    ALTER TABLE demo_table ALTER COLUMN C1 CHAR(3) COLLATE Latin1_General_BIN NOT NULL;
    GO
 
-- what resources are blocked by the transaction?
SELECT  DTL.resource_type,
        DTL.resource_description,
        DTL.request_mode,
        DTL.request_type,
        DTL.request_status,
        CASE WHEN DTL.resource_type = N'OBJECT'
             THEN OBJECT_NAME(DTL.resource_associated_entity_id)
             ELSE NULL
        END        AS resource_Object_Name
FROM    sys.dm_tran_locks AS DTL
WHERE   DTL.request_session_id = @@SPID AND
        DTL.resource_type != N'DATABASE';

-- what has happend in the transaction log?
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'ChangeCollation'
) AND
Context != N'LCX_NULL'
ORDER BY
        [Current LSN],
    [Transaction ID];
 
COMMIT TRANSACTION ChangeCollation;
GO

Zunächst wird die Sortierung der Spalte [C1] umgestellt. Um festzustellen, welche Ressourcen dabei von Microsoft SQL Server verwendet werden, hilft die Funktion [sys].[dm_tran_locks]. Sie zeigt, welche Ressourcen aktuell von offenen Transaktionen verwendet werden.

Rowsets_03

Die obige Abbildung zeigt, welche Objekte durch die Transaktion gesperrt sind. Die Beispieltabelle [dbo].[demo_table] wird mit einer SCH-M-Sperre versehen. Hierbei handelt es sich um eine Sperre, die gesetzt werden muss, um Änderungen an den Objekten (Schemata) vornehmen zu können. Ebenfalls ist zu erkennen, dass X-Sperren (Exklusivsperren) auf Datensätzen (KEY) liegen. Da es sich nicht um die Benutzertabelle handelt (die ist mit einer SCH-M-Sperre versehen), kann es sich nur um die drei Systemtabellen handeln, die mit einer IX-Sperre versehen wurden.

Ein Blick in das aktive Transaktionsprotokoll bestätigt diesen Verdacht. Tatsächlich befindet sich im Transaktionsprotokoll lediglich EIN Transaktionseintrag, der im Zusammenhang mit der Änderung der Sortierung steht!

Rowsets_04

Änderung der Sortierung in einem Clustered Index

Wie sieht es mit der Änderung der Sortierung aus, wenn die Tabelle ein Clustered Index ist und der Schlüssel selbst eine Textspalte ist? Das nachfolgende Skript erstellt eine Tabelle mit einem Clustered Index auf der Spalte [Id]. In diese Tabelle werden ein paar Datensätze eingetragen um anschließend die Sortierung anzupassen.

CREATE TABLE dbo.demo_table
(
    Id    CHAR(4) COLLATE Latin1_General_CS_AI NOT NULL PRIMARY KEY CLUSTERED,
    C1    CHAR(300)    NOT NULL,
    C2    CHAR(500)    NOT NULL
);
GO
 
/* Fill the table with a few values */
DECLARE @i INT = 65;
WHILE @i <= 90
BEGIN
    INSERT INTO dbo.demo_table (Id, C1, C2)
    SELECT CHAR(@i), 'Das ist ein Test', 'Ja, das ist ein Test'
    UNION ALL
    SELECT CHAR(@i + 32), 'Das ist ein Test', 'Ja, das ist ein Test';
 
    SET @i += 1;
END
GO

Versucht man, nachträglich die Sortierung der Spalte [Id] zu ändern, erhält man einen “klassischen” Fehler, der eindeutig darauf hinweist, dass Sortierungen auf indexierte Spalten nicht anwendbar sind.

ALTER_COLUMN_FAILURE_5074

Diese Fehlermeldung macht im Zusammenhang mit der Effizienz einer DDL-Operation Sinn. Würde Microsoft SQL Server erlauben, dass Sortierungen in Spalten geändert werden, die von einem Index berücksichtigt werden, dann müsste Microsoft SQL Server die Schemasperre auf dem Tabellenobjekt so lange aufrecht erhalten, bis die geänderte Sortierung in jedem betroffenen Index berücksichtigt wurde. Das bedeutet für die Indexe jedoch eine vollständige Neusortierung, da sich – bedingt durch Groß-/Kleinschreibung, Akzente, etc – die Sortierung der Einträge ändert.

Damit die Schemasperre so schnell wie möglich wieder aufgehoben werden kann, sind solche lang laufenden Transaktionen in Microsoft SQL Server nicht erlaubt! Andere Prozesse können auf die Tabelle nicht zugreifen und die Applikationen müssten warten, bis der Sortiervorgang und Neuaufbau der Indexe abgeschlossen ist.

Zusammenfassung

Die Änderung der Sortierung ist ein DDL-Befehl und setzt voraus, dass die betroffenen Spalten einer Tabelle nicht von Indexen verwendet werden. Die eigentliche Operation der Änderung der Sortierung geht mit minimalem Aufwand, da ausschließlich Metadaten geändert werden.

Herzlichen Dank fürs Lesen!

Uwe Ricken: Don’t trust sys.dm_db_database_page_allocations

Seit Microsoft SQL Server 2012 gibt es eine neue Möglichkeit, die allokierten Datenseiten eines Objekts mit Hilfe der Systemfunktion [sys].[dm_db_database_page_allocations] zu ermitteln. Über die Verwendung habe ich bereits im Artikel “Neue DMF für Aufteilung von Datenseiten” geschrieben. Diese Systemfunktion ist keine offiziell dokumentierte Funktion. Ich benutze diese Funktion sehr gern, da sie – anders als DBCC IND die Daten als Tabelle zurück liefert, dessen Ergebnis durch Prädikate eingegrenzt werden können. Eher durch Zufall ist aufgefallen, dass diese Funktion nicht immer zuverlässig arbeitet.

Verwendung von sys.dm_db_database_page_allocations

sys.dm_db_database_page_allocation wird verwendet, um sich einen Überblick über die durch ein Objekt belegten Datenseiten zu verschaffen. Hierbei handelt es sich um eine Funktion; die grundsätzliche Filterung findet bereits durch die übergebenen Parameter statt. Der grundsätzliche Aufruf der Funktion sieht wie folgt aus:

SELECT *
FROM sys.dm_db_database_page_allocations
     (
        @DatabaseId   SMALLINT,
        @TableId      INT          = NULL,
        @IndexId      INT          = NULL,
        @PartitionId  BIGINT       = NULL,
        @Mode         NVARCHAR(64) = 'LIMITED'
     );

Das Ergebnis dieser Funktion ist eine Tabelle mit allen durch ein Objekt belegten Datenseiten; oder sollte es sein. Der “Fehler” ist mir in einer Demo-Datenbank aufgefallen, die ich als Grundlage eines zu produzierenden Video-Workshops verwende. Diese Datenbank – mit den Fehlern – kann hier heruntergeladen werden: http://1drv.ms/1ZNxEXH. Die betroffene Tabelle ist [dbo].[Customers]. Diese Tabelle – wie alle anderen auch – ist ein HEAP und besitzt 75.000 Datensätze.

Demonstration

Mit den nachfolgenden Skripten wird zunächst ermittelt, wie viele Datenseiten durch die Tabelle [dbo].[Customers] belegt sind. Die einfachste Art der Feststellung ist das gemessene IO für einen TABLE SCAN.

SET STATISTICS IO ON

USE CustomerOrders;
GO
 
SET STATISTICS IO ON;
GO
 
SELECT * FROM dbo.Customers AS C;
GO
 
SET STATISTICS IO OFF;
GO

STATISTICS_IO_01

Ein direkter Vergleich mit den allokierten Datenseiten bestätigt, dass diese Tabelle 682 Datenseiten belegt.

sys.system_internals_allocation_units

Die Systemsicht sys.system_internals_allocation_units ist nur für die interne Verwendung durch Microsoft SQL Server reserviert. Jede Partition einer Tabelle, eines Indexes oder einer indizierten Sicht hat eine Zeile in sys.system_internals_allocation_units, die eindeutig durch eine Container-ID (container_id) identifiziert ist. Die Container-ID besitzt eine 1:1-Zuordnung zur [partition_id] in der Katalogsicht [sys].[partitions], mit der die Beziehung zwischen den in einer Partition gespeicherten Daten der Tabelle, des Indexes oder der indizierten Sicht und den Zuordnungseinheiten bestimmt wird, die zum Verwalten der Daten in der Partition verwendet werden.

SELECT IAU.total_pages,
       IAU.used_pages,
       IAU.data_pages
FROM   sys.partitions AS P INNER JOIN sys.system_internals_allocation_units AS IAU
       ON (P.partition_id = IAU.container_id)
WHERE  P.object_id = OBJECT_ID(N'dbo.Customers', N'U');

USED_DATA_PAGES_01

Insgesamt belegt die Tabelle 689 Datenseiten; davon sind 688 REINE Datenseiten und eine IAM-Datenseite belegt. Dass Datenseiten belegt sind, obwohl sie nicht verwendet werden, liegt an dem Umstand, dass Microsoft SQL Server automatisch vollständige Extents belegt, wenn eine Tabelle mehr als 8 Datenseiten belegt! Von den 688 Datenseiten sind 682 Datenseiten mit Daten belegt. Somit stimmt die Ausgabe des SELECT-Befehls. Insgesamt müssen 682 Datenseiten ausgegeben werden.

sys.dm_db_database_page_allocations

Eine Abfrage auf die Systemfunktion sys.dm_db_database_page_allocations zeigt jedoch ein anderes Ergebnis:

SELECT DDDPA.allocation_unit_type_desc,
       DDDPA.allocated_page_page_id,
       DDDPA.page_free_space_percent 
FROM   sys.dm_db_database_page_allocations
       (
          DB_ID(),
          OBJECT_ID(N'dbo.Customers', N'U'),
          NULL,
          NULL,
          N'DETAILED'
       ) AS DDDPA 
WHERE  DDDPA.is_allocated = 1 AND 
       DDDPA.page_type = 1 
ORDER BY 
       DDDPA.page_type DESC, 
       DDDPA.allocated_page_page_id ASC; 
GO

USED_DATA_PAGES_02

Wie man an der obigen Abbildung deutlich erkennen kann, werden NICHT die erwarteten 682 Datenseiten angezeigt sondern es fehlt offensichtlich eine Datenseite. Wird jedoch die Zuordnung der Datenseiten mit DBCC IND überprüft, stimmt die Zuordnung wieder:

DBCC IND(CustomerOrders, 'dbo.Customers', 0);

USED_DATA_PAGES_03

Die Differenz von +1 hängt damit zusammen, dass DBCC IND nicht nur die reinen Datenseiten ausgibt sondern zusätzlich die IAM-Datenseiten im Resultat auswirft. Zieht man die IAM-Datenseite vom Ergebnis ab, so verbleiben 682 Datenseiten für die Ausgabe der Datensätze von [dbo].{Customers]. Bei genauerer Betrachtung war im Anschluss erkennbar, dass die allokierte Datenseite 40447 nicht von sys.dm_db_database_page_allocation berücksichtigt wurde.

USED_DATA_PAGES_DIFFERENCE

Zusammenfassung

Die seit Microsoft SQL Server 2012 zur Verfügung gestellte Funktion ist eine große Hilfe für DBA, wenn es darum geht, gezielt eine Liste der allokierten Datenseiten mit Hilfe von Filtern und Sortierungen ausgeben zu lassen. Ist man auf die exakte Anzal der allokierten Datenseiten angewiesen, sollte besser weiterhin mit DBCC IND die Ausgabe gesteuert werden. Im konkreten Fall half der Neuaufbau der Tabelle mit Hilfe eines REBUILDs.

Herzlichen Dank fürs Lesen!

Uwe Ricken: Wann Löschvorgänge in HEAPS den allokierten Speicher nicht freigeben

Eine Anfrage im MSDN-Forum von Microsoft mit dem Titel “Issue in shrinking data file” ist der Grund für diesen Artikel. In der Anfrage ging es darum, dass der Fragesteller aus einer sehr großen Datenbank Unmengen von Datensätzen aus diversen Tabellen gelöscht hatte. Anschließend wollte er die Datenbankdatei verkleinern um so mehr Platz auf dem Storage zu schaffen. Jedoch ergaben Überprüfungen des konsumierten / allokierten Speichers, dass trotz des Löschens mehrerer Millionen Datensätze der Speicher nicht als “frei” gekennzeichnet wurde. Beim genaueren Lesen der Fragestellung kam einem Satz besondere Aufmerksamkeit zu Teil: “Also, I noticed that there is three huge tables in the db and these are non-clustered index.”. Damit war eigentlich schon klar, was das Problem des Fragestellers war. Dieser Artikel beschreibt die technischen Hintergründe, warum ein DELETE-Befehl nicht automatisch den allokierten Speicher freigibt.

Was ist ein HEAP?

Als HEAP wird eine Tabelle bezeichnet, die nicht nach Ordnungskriterien sortiert wird. Das bedeutet, dass ein HEAP die Datensätze immer da abspeichert, wo ausreichend Platz in einer Datenseite ist. Ein HEAP genießt den großen Vorteil, dass er – anders als ein Clustered Index oder Nonclustered Index – keine B-Tree-Struktur zur Verwaltung benötigt. Ein HEAP wird ausschließlich durch Datenseiten (Leafs) repräsentiert, die durch eine oder mehrere IAM-Datenseiten (Index Allocation Map) verwaltet werden.

SQLGuru_01

Die obige Abbildung zeigt die Organisationsstruktur eines HEAPS. Die Datenseiten (110 – 152) haben keinen direkten Bezug zueinander und die übergeordnete IAM-Datenseite verwaltet die der Tabelle zugehörigen Datenseiten. Eine IAM-Datenseite kann immer nur EIN Datenbankobjekt (Tabelle, Indexed View) verwalten!

Testumgebung

Um die Fragestellung / Problemstellung des Autors der obigen Anfrage zu reproduzieren, wird in einer Testdatenbank eine Tabelle [dbo].[demo_table] angelegt. Diese Tabelle kann pro Datenseite maximal einen Datensatz speichern, da die Datensatzlänge 8.004 Bytes beträgt. Anschließend werden 10.000 Datensätze in die zuvor angelegte Tabelle eingetragen um die zugewiesene Speicherzuordnung auszuwerten.

-- Create the demo table
CREATE TABLE dbo.demo_table
(
    Id    INT        NOT NULL    IDENTITY (1, 1),
    C1    CHAR(8000) NOT NULL    DEFAULT ('only a filler')
);
GO

-- and insert 10.000 records into this table
SET NOCOUNT ON;
GO

INSERT INTO dbo.demo_table DEFAULT VALUES;
GO 10000

-- show the number of allocated data pages after the insert
SELECT  DDPS.index_id,
        DDPS.in_row_data_page_count,
        DDPS.in_row_used_page_count,
        DDPS.in_row_reserved_page_count,
        DDPS.row_count
FROM    sys.dm_db_partition_stats AS DDPS
WHERE   DDPS.object_id = OBJECT_ID(N'dbo.demo_table', N'U');
GO

SQLGuru_02

Wie die Abbildung zeigt, wurden 10.000 Datensätze ([row_count]) in die Tabelle eingetragen. Diese 10.000 Datensätze belegen insgesamt 10.000 Datenseiten ([in_row_data_page_count]).

Löschen von Datensätzen

Nachdem die Datensätze eingetragen wurden, werden im nächsten Schritt 1.000 Datensätze aus der Tabelle gelöscht und erneut der belegte Speicher überprüft. Zum Löschen von Datensätzen wird der DELETE-Befehl im “klassischen” Stil ohne weitere Hints verwendet

-- Delete the last 1,000 records...
DELETE dbo.demo_table WHERE Id >= 9001;
GO
 
-- and check the allocated space again!
SELECT  DDPS.index_id,
        DDPS.in_row_data_page_count,
        DDPS.in_row_used_page_count,
        DDPS.in_row_reserved_page_count,
        DDPS.row_count
FROM    sys.dm_db_partition_stats AS DDPS
WHERE   DDPS.object_id = OBJECT_ID(N'dbo.demo_table', N'U');
GO

Das Ergebnis ist “überraschend”; obwohl 1.000 Datensätze gelöscht wurden, ist der allokierte Speicher nicht freigegeben worden.
(c) by db Berater GmbH

SELECT in HEAP

Die Ursache für dieses Verhalten liegt in der Art und Weise, wie Microsoft SQL Server beim Lesen von Daten aus einem Heap vorgeht. Da ein HEAP kein Ordnungskriterium besitzt, wird auch mit eingesetztem Prädikat immer ein Table Scan ausgeführt; es muss also immer die komplette Tabelle gelesen werden.

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

(c) by db Berater GmbH

Der Lesevorgang in einem HEAP liest zunächst die IAM-Datenseite des betroffenen Objekts. Die IAM-Datenseite muss gelesen werden, da ansonsten Microsoft SQL Server nicht weiß, welche Datenseiten zum Objekt gehören. In einem Clustered Index / Nonclustered Index ist das nicht notwendig, da die Datenseiten Verknüpfungen zu den nachfolgenden / vorherigen Datenseiten besitzen! Hat Microsoft SQL Server die IAM-Datenseite gelesen, kann mit dem Einlesen der allgemeinen Datenseiten begonnen werden. Genau hier liegt aber der “Fehler des Designs”; da der DELETE-Vorgang nicht mit einer Tabellensperre einhergeht, muss Microsoft SQL Server alle Datenseiten auch weiterhin bereitstellen, da ansonsten ein zweiter Vorgang, der einen SELECT auf die Tabelle durchführt, die IAM-Datenseite bereits gelesen haben könnte und somit die zu lesenden Datenseiten bereits kennt. Würde Microsoft SQL Server nun bei einem DELETE-Vorgang diese Datenseiten aus der Zuordnung entfernen, würde der zweite – Lese – Vorgang eine Datenseite anfordern, die nicht mehr existiert.

Dieses „Problem“ ist auch offiziell bei Microsoft bekannt und kann hier nachgelesen werden: https://support.microsoft.com/en-us/kb/913399. Entgegen der Auffassung von Microsoft, dass es sich um einen „bekannten Bug“ handelt, stellt sich dieses Verhalten eher als „Feature“ dar!

Lösung

Um das Problem der Freigabe von allokiertem Datenspeicher zu lösen, gibt es zwei Alternativen:

Neuaufbau der Tabelle

Die erste Möglichkeit besteht darin, die Tabelle selbst mittels REBUILD neu aufzubauen. Hierzu benötigt Microsoft SQL Server – kurzfristig – eine exklusive Sperre auf die Tabelle, um die “alte” Tabelle durch die neu aufgebaute Tabelle zu ersetzen. Zuvor werden die Datenbestände in die “neue” Tabelle transferiert und somit eine neue Struktur geschaffen.

-- Rebuild the table
ALTER TABLE dbo.demo_table REBUILD;
GO

-- and check the allocated space again!
SELECT  DDPS.index_id,
        DDPS.in_row_data_page_count,
        DDPS.in_row_used_page_count,
        DDPS.in_row_reserved_page_count,
        DDPS.row_count
FROM    sys.dm_db_partition_stats AS DDPS
WHERE   DDPS.object_id = OBJECT_ID(N'dbo.demo_table', N'U');
GO

SQLGuru_04

Nach dem REBUILD sind die “leeren” Datenseiten gelöscht worden und nur noch Datenseiten mit Datensätzen sind vorhanden.

Löschen von Datensätzen mit TABLOCK-Hinweis

Eine andere – elegantere – Möglichkeit besteht darin, den Löschvorgang mit einer Tabellensperre zu verbinden. Da ein möglicher SELECT-Vorgang die IAM-Datenseite lesen muss, kann durch das Sperren der Tabelle dieser Zugriff ausgeschlossen werden. Ein möglicher SELECT-Befehl kann keine IS-Sperre (Intent Shared)  auf das Tabellenobjekt legen, da während des Löschvorgangs eine X-Sperre (Exklusiv) auf dem Tabellenobjekt liegt. Somit wird der SELECT-Befehl solange gesperrt, bis der Löschvorgang abgeschlossen ist. Da in dieser Situation sichergestellt ist, dass niemand lesend auf die Tabelle zugreift, kann Microsoft SQL Server gefahrlos die leeren Datenseiten entfernen!

-- Delete 2,000 records with a TABLOCK hint
DELETE dbo.demo_table WITH (TABLOCK) WHERE Id >= 7001;
GO
 
-- and check the allocated space again!
SELECT  DDPS.index_id,
        DDPS.in_row_data_page_count,
        DDPS.in_row_used_page_count,
        DDPS.in_row_reserved_page_count,
        DDPS.row_count
FROM    sys.dm_db_partition_stats AS DDPS
WHERE   DDPS.object_id = OBJECT_ID(N'dbo.demo_table', N'U');
GO

SQLGuru_05

Zusammenfassung

HEAPS haben eine andere interne Struktur, die viele Vor- aber auch Nachteile besitzt. Viele Aktionen, die als “selbstverständlich” gelten, bergen Tücken. Wenn man aber die Konzepte hinter den Objekten versteht, tappt man nicht in diese Fallen.

Herzlichen Dank fürs Lesen und allen Lesern ein frohes neues Jahr!

Robert Panther: Ankündigung: SQL Server Konferenz 2016 in Darmstadt

Ich freue mich, im kommenden Jahr auf der deutschen SQL Server Konferenz, die vom 23.-25. Februar 2016 in Darmstadt stattfindet, als Sprecher dabei zu sein.

Die Konferenz selbst beginnt mit einem Workshoptag, dem zwei Tage mit parallel stattfindenden Tracks zu verschiedenen Themen aus den Bereichen Administration, Entwicklung, Business Intelligence, Azure Data Platform und Information Management folgen. Als Referenten sind ca. 50 Speaker aus dem In- und Ausland im Einsatz. Workshoptag und Hauptkonferenz sind auch separat buchbar.

Bei meinem eigenen Vortrag wird es um das Thema Datenqualität gehen. Dabei werden verschiedene Ansätze aufgezeigt, wie man mit den Mitteln, die SQL Server in den verschiedenen Versionen (bis hin zu SQL Server 2016) zur Verfügung stellt, eine möglichst gute Datenqualität erreicht. Dabei wird stets die Praxistauglichkeit im Vordergrund stehen. Es geht also weniger darum, was alles theoretisch machbar ist, sondern eher, wie man mit vertretbarem Aufwand die bestmögliche Datenqualität erzielt.

Weitere Informationen zur Konferenz gibt es auf der offiziellen Veranstaltungswebsite: http://sqlkonferenz.de

PASS2016_300x100

 


Christoph Muthmann: PASS Rheinland 01/2016

Das neue Jahr startet für mich direkt mit einem Vortrag in der Regionalgruppe Rheinland zusammen mit Uwe Ricken.

Ganze Geschichte »

Robert Panther: Index-Vortrag bei den Frankfurter IT-Tagen 2015

Am 14.12.2015 werde ich bei den IT-Tagen in Frankfurt mit einem Vortrag zum Thema SQL Server Indizes vertreten sein. Dabei werde ich einen Überblick über die verschiedenen Indextypen geben, die SQL Server zur Verfügung stellt und werde auch Kriterien aufzeigen, anhand derer man entscheiden kann, für welchen Einsatzweck welche Indexform am besten geeignet ist.

Details zum Vortrag: http://www.informatik-aktuell.de/seminare/it-tage-datenbanken-2015/sql-server-indizes-verschiedene-varianten-im-ueberblick.html

Die IT-Tage mit Schwerpunkt Datenbanken in Frankfurt finden vom 14.-18.12.2015 im Maritim Hotel an der Frankfurter Messe statt. Nähere Infos zur Veranstaltung sind auf der Website des Veranstalters zu finden: http://www.informatik-aktuell.de/seminare/it-tage-datenbanken-2015.html

Ergänzung vom 23.12.2015:

Mittlerweile sind auf der Website des Veranstalters auch die Slides zu den einzelnen Vorträgen zu finden: http://www.informatik-aktuell.de/seminare/it-tage-datenbanken-2015/download-praesentationen-it-tage-2015.html

 


Uwe Ricken: 22 Konferenzen – 40.000 Flugkilometer – 1.000.000 mal Danke

Das Jahr geht nun zu Ende und aktuell stehen noch zwei interessante Konferenzen zum Thema SQL Server auf meiner Agenda. Zum einen der SQL Saturday in Slovenien (nächste Woche) und die IT Tage Frankfurt mit Schwerpunkt Datenbanken. Insgesamt war das Jahr 2015 durch sehr viele Konferenzen geprägt, von denen ich nicht eine einzige missen wollte.

Deutsche SQL Konferenz

Im Februar ging es mit der “Deutsche SQL Konferenz” in Darmstadt los. Es freut mich wirklich sehr, dass diese Konferenz – in 2015 zum zweiten Mal ausgetragen – gut etabliert hat und auch im Jahr 2016 wieder in Darmstadt interessante Themen zum Microsoft SQL Server bietet. Da Darmstadt für mich quasi ein Heimspiel ist, ging es gemütlich mit dem Auto in weniger als 15 Minuten zum Veranstaltungsort.

SQLRally Nordic

Die SQLRally war auch dieses Jahr sicherlich wieder ein Highlight. Ich erwähne diese Konferenz insbesondere, weil die PASS leider dieses Format eingestellt hat. Ich weiß nicht, was dahinter steckt – aber ich bin davon überzeugt, dass dieses Format in Europa gut angekommen ist. Das beweisen immer wieder die hohen Besucherzahlen. Sehr schade!

SNEK (SQL Server + .NET-Entwicklerkonferenz)

Die SNEK ist eine von Karl Donaubauer initiierte Konferenz, die – wie es der Name schon verrät – sowohl SQL Server als auch .NET-Themen behandelt. Ich kenne Karl schon seit mehr als 20 Jahren. Karl ist seit vielen Jahren MVP für Microsoft Access und seine AEK gehen nun bereits in das 19. Jahr. Die AEK als auch die SEK/SNEK besuche ich schon seit Beginn als Sprecher. Das Format ist einzigartig; Karl veranstaltet die komplette Konferenz auf eigenes Risiko – es sind KEINE Sponsoren vor Ort. Das Konzept ist an Entwickler gerichtet und soll nicht für Marketingveranstaltungen missbraucht werden. Bei Karl habe ich – wenn man es genau nimmt – meine Karriere als Sprecher begonnen.

SQL Saturday(s)

Dieses Jahr war es ein neuer persönlicher Rekord – insgesamt bin ich als Sprecher auf mehr als 15 SQL Saturdays in Europa und USA mit einer eingereichten Session ausgewählt worden. ALLE SQL Saturdays waren außergewöhnlich. Ich kann wirklich jedem SQL Experten empfehlen, diese – für die Besucher – kostenlosen Veranstaltungen zu besuchen. Tolle Lokationen, tolle Sprecher und super nette SQL Experten all around. Einige SQL Saturdays werden mir auch noch in vielen Jahren in Erinnerung bleiben.

Cambridge

IMG_3496

WOW – Was Mark Broadbent (w | t) da mit seinem Team in Cambridge organisiert hat. ist einfach der Hammer. Mich hat nachhaltig das Speaker Dinner beeindruckt. Wir hatten die einmalige Gelegenheit, in einem der vielen Colleges in einem Saal zu essen, der selbst Harry Potter hätte erblassen lassen. Dir, lieber Mark und Deinem Team, ein großes Danke für drei unvergleichliche Tage in Cambridge!

Lissabon

IMG_3933

Niko Neugebauer hat in Lissabon schon vor vielen Jahren zum ersten Mal überhaupt einen SQL Saturday in Europa veranstaltet. Sein Hang zur Perfektion ist allgemein bekannt. Ich habe mich noch nie so gut “betreut” gefühlt, wie auf den SQL Saturdays, die von Niko Neugebauer veranstaltet wurden. Niko hat es dieses Jahr geschafft, neben Dejan Sarka (w | t) auch Paul White (w | t)aus Neuseeland als PreCon-Speaker zu gewinnen. Die gemeinsame Sonntagsveranstaltung wird mir immer im Gedächtnis bleiben – dort wurde der Twitter-Account-Name @sqlbambi für mich geboren :)

Portland (OR)

Vielen SQL Server Experten ist der SQL Saturday in Portland als “Vorglühen” zum SQL PASS SUMMIT sicherlich wohl bekannt; wird er doch genau eine Woche vor dem Pass Summit ausgetragen. Für mich war der SQL Saturday in Portland aus drei Gründen ein tolles Event:

  • Es war meine ERSTE Konferenz als Sprecher in den USA
  • Das Who is Who der europäischen Sprecherelite war vor Ort
  • Es war eine super tolle Zeit mit den deutschen und österreichischen Kollegen

SQL in the City – Redgate

Ich bin seit einigen Jahren sehr engagiert mit einem fantastischen Team von Redgate in Cambridge verbunden. Für mich persönlich ist aus dieser “Projekt-Verbindung” eine wunderbare persönliche Verbindung zu den Menschen bei Redgate geworden. Ich mag die Leute. Man trifft sie immer wieder auf den vielen SQL Server Konferenzen. Es ist für mich einfach nur schön, meine Zeit mit diesen Leuten zu verbringen. Von diesem Team kam die Anfrage, ob ich nicht bei “SQL in the City” in London und Seattle mit einem Beitrag über Wait Stats dabei sein wolle. Da mussten sie nicht lange fragen :)

  • In London war ich mit meinem eigenen Beitrag als auch als Ersatz für einen ausgefallenen Kollegen vertreten: Meine Vorträge wurden mit Platz 1 und Platz 2 der Konferenzbeiträge gewertet. WOW!
  • In Seattle ging es dann mit meinem ursprünglichen Beitrag in die zweite Runde. Auch dieser Vortrag wurde von den Teilnehmern mit Platz 1 in der Wertung belohnt.

Ein großes DANKE an das Team von Redgate dafür, dass sie so großes Vertrauen in mich haben; ein super großes DANKE geht natürlich an “the audience”. War eine tolle Erfahrung für mich und wird auch nie vergessen.

PASS Summit 2015

IMG_4487

Na ja – was soll man da sagen! Ich war das zweite Mal nach 2014 auf dem PASS Summit und das ERSTE Mal als Sprecher vertreten. Man kann sich sicherlich vorstellen, dass ich ganz schön aufgeregt gewesen war. Ein großes Danke geht an meine “internationalen SQL Friends”, die mir – quasi – das Händchen während meines Vortrags gehalten haben. Mark Broadbent, Regis Baccaro, Oliver Engels, Tillmann Eitelberg, Frank Geisler, … – euch gilt mein großes DANKE. Ihr seid in meiner Session gewesen und wir hatten zwei tolle Wochen miteinander verbracht.

PASS Camp 2015

Das PASS Camp wird jedes Jahr im Lufthansa Training und Conference Center in Seeheim-Jugenheim von der deutschen PASS e.V. veranstaltet. Ich war mit einem neuen Feature von SQL Server 2016 vertreten zu dem ich auch auf der SQL Konferenz 2016 eine Menge zu sagen habe. Das PASS Camp ist aus meiner Sicht EINMALIG. Es verbindet die klassische Konferenz mit Labs, in denen jeder Teilnehmer die vorher besprochenen Topics in einem eigenen Lab ausprobieren kann. Schade, dass ich nur an dem Tag in Seeheim war, an dem mein Vortrag war – aber ich habe derzeit einfach zu viel zu tun!

IT Tage Frankfurt 2015

Bevor für das Konferenzjahr 2015 der Vorhang fällt, werde ich noch zwei Mal in Frankfurt auf den IT-Tagen mit ein paar Sessions vertreten sein. Unter anderem gibt es eine – bereits lange vorher ausverkaufte – ganztägige Veranstaltung zur Analyse von SQL Server Problemen. Veranstaltet werden die IT-Tage Frankfurt von Andrea Held, die eine leidenschaftliche ORACLE-Expertin ist. Da soll man noch mal sagen, dass sich ORACLE nicht mit MS SQL Server verträgt.

Insgesamt bin ich im Jahr 2015 mehr als 40.000 km mit dem Flugzeug, 5.000 km mit der Bahn und 20.000 km mit dem Auto unterwegs gewesen, um auf unzähligen SQL Server Konferenzen und Usergroup-Treffen zu sprechen. Das Jahr geht nun bald zu Ende; die Arbeit wird – leider – noch nicht weniger aber es ist absehbar, dass 2015 wohl zu einem der intensivsten Jahre in und für die SQL Server Community geworden ist.

Euch allen ein schönes Weihnachtsfest und ein erfolgreiches Jahr 2016.
Herzlichen Dank fürs Lesen!

Shares

Christoph Muthmann: SQL Server 2012 Service Pack 3

Heute hat das SQL Server Release Team in seinem blog die Verfügbarkeit des SP3 bekannt gegeben.

Ganze Geschichte »

Uwe Ricken: 2,97 auf der Richterskala von 1–3 für PASS Summit Speaker

Ich mag es eigentlich nicht, den eigenen Erfolg so demonstrativ in den Vordergrund zu stellen (Ausnahme war die MCM Zertifizierung und der MVP Award). Aber mit dem folgenden Artikel möchte ich nicht verhehlen, dass ein gewisser Stolz beim Schreiben “mitschwingt”. Dieser Artikel soll aber auch dem Einen oder Anderen etwas Mut machen, vielleicht selbst einmal als Sprecher vor einem interessierten Publikum zu stehen. Vielleicht macht dieser Artikel ja Lust darauf.

PASS SUMMIT 2015

Der eine oder andere hatte es sicherlich mitbekommen; ich durfte auf dem PASS Summit 2015 in Seattle (WA) zum ersten Mal mit einer Session dabei sein.  Der erste Versuch im Jahre 2014 war nicht vom Erfolg gekrönt. Das es diesmal geklappt hat, lag sicherlich am Thema (ich war wohl der einzige mit diesem Topic) aber auch am Review meiner Abstracts! Hier gilt mein Dank vor allem Brent Ozar (t | w). Brent hat sich meine “Submission Abstracts” angeschaut und mir wertvolle Tipps bezüglich der Formulierung, etc. gegeben. Nun ja – es hat geklappt und das Review Board hat meine Session “Change Data Capture Case Study and Checklist” für den diesjährigen Summit ausgewählt. Am 30.10.2015 um 14:00 (PST) ging es dann auch pünktlich in “Ballroom 6A” los.

I'm Speaking_e-signature

Da es sich um den letzten Tag der Konferenz handelte und die Mittagszeit schon rum war, habe ich nicht mit zu vielen Zuhörern gerechnet. Insgesamt haben sich 123 SQL-Experten eingefunden, um meinen Ausführungen zu CDC zuzuhören. Nach 75 Minuten war der “Spuk” vorbei und ich muss eingestehen, dass ich noch nie so viel Spaß auf einer Session hatte, wie diese. Ich zitiere mal aus einem deutschen Film mit Til Schweiger: “Wenn du mit dem Schlitten durch die Stadt fährst, die Fenster auf und die Anlage voll aufgedreht bis zum Anschlag, das ist mehr als nur Auto fahren, das ist ein Gefühl von Freiheit, das ist total geil!”. So ähnlich kam es mir auch vor, nachdem die ersten zwei Minuten etwas holprig auf Grund der Aufregung vergangen waren. Der deutsche Fanclub war ebenso vertreten, wie der Fanclub aus Österreich, Dänemark und England! Ein dickes DANKE an meine FREUNDE aus der SQL Community!

Evaluation

Heute kam dann die Auswertung der Teilnehmer – und die war einfach nur überwältigend. Auf einer Skala von 1 (schlecht) – 3 (sehr gut) habe ich im Durchschnitt bei einer Beteiligung von 23 Teilnehmern eine 2,97 erreicht. Für das erste Mal vor so einer großen Kulisse als “non-native” Speaker finde ich diese Bewertung wirklich super! Ein anderes Highlight im “Bewertungsmarathon” ist diese Passage: “A minimum of twenty attendance and evaluation submissions must be reached to be considered for the top ten sessions.”. Da meine Session von 23 Personen bewertet wurden, nimmt meine Evaluation auf jeden Fall an der “Competition for the TOP 10 Sessions” teil. Insgesamt war für mich weniger die Bewertung “gut” oder “schlecht” interessant als vielmehr die Kommentare der Teilnehmer. Sie geben ein viel detaillierteres Bild von meiner Session als die Auswahl zwischen drei Werten. Folgende Kommentare (Nur ein Auszug) habe ich erhalten:

A very engaging speaker. Enjoyed thr session throughly in addition to learning CDC.

Excellent, fun session. I was stunned that CDC could be made fun.

Was a great overview and came away feeling like I can set it up right away. Did a good job showing examples before and after.

Great introduction to CDC. The examples were spot on and I feel comfortable now implementing it in my organization. Since I was not aware of this feature at all I am glad I found something useful to bring home from the conference that will make our lives easy

So – genug Lobhuddelei. Mich hat an den Kommentaren im Tenor gefreut, dass die Teilnehmer neben wichtigen Informationen zum Thema auch SPASS an der Session hatten. Sie haben sich “unterhalten” gefühlt und gemerkt, dass ich die Session nicht einfach “runterspule” sondern mit Engagement und Leidenschaft auf der Bühne stehe! Genau das war mein Gedanke, während ich die Session geplant und immer wieder korrigiert hatte; wenn die Leute lachen, bemerken sie Deine Fehler nicht! :) Ich habe mich über die Bewertungen natürlich gefreut – sie haben gezeigt, dass die vielen Stunden der Vorbereitung lohnenswert waren.

An diesem Punkt möchte ich sehr gerne noch einmal den Aufruf von Andreas Wolter (t |w) ins Gedächtnis bringen: “Die SQL PASS Deutschland sucht Sprecher – Aufruf an alle SQL Server Fachleute”. Ich teile seine Auffassung, dass es schön wäre, wenn sich mehr Sprecher für die örtlichen Usergroups / lokalen Konferenzen / SQL Saturdays / SQL Rally / PASS Summit aus Deutschland finden würden. Ich kenne so viele deutsche SQL Experten von den verschiedenen Usergroups, die das Zeug zu einem genialen Sprecher haben. Leider trauen sich einige nicht oder aber sie haben zu wenig Zeit. Beides ist absolut nachvollziehbar; aber es wäre doch sehr schön, wenn man sich mal einen Ruck gibt und dann – vielleicht – auch mal in Seattle in einem der großen Konferenzräume steht und über ein SQL Thema spricht, das mehr als 100 Leute interessiert.

Herzlichen Dank fürs Lesen!

Christoph Muthmann: Azure SQL Database Query Store verfügbar

Das Microsoft Azure Team hat in seinem blog die generelle Verfügbarkeit des Query Stores für die Azure Datenbank bekannt gegeben.

Ganze Geschichte »

Philipp Lenz: SSRS: PREVIOUS Funktion in einer Matrix

In diesem Beitrag beschreibe ich das Problem wenn man den Vorherigen Wert in einer Matrix in Reporting Services verwenden möchte: Reporting Services – PREVIOUS in a Matrix

Dirk Hondong: Es muss nicht immer DROP…CREATE sein

Hallo zusammen,

dieser kleine Beitrag schwirrte schon lange in meinem Kopf herum und nun mache ich mich endlich mal daran, diesen auch umzusetzen. Anlass ist der letzte Blog Post von einem unserer #sqlpass_de MVPs und Leiter der Regionalgruppe Ruhrgebiet: Frank Geisler (B|T). Frank hatte das, in der CTP 3 des SQL Servers 2016, neue DROP…IF EXISTS kurz vorgestellt.

Und dann ist mir wieder eingefallen, wie oft ich schon sogenannte Update Skripte gesehen habe, wo Funktionen, Prozeduren oder Views “überarbeitet” wurden. Die fingen dann nämlich so an:

   1: DROP PROCEDURE blabla
   2:
   3: CREATE PROCEDURE blabla
   4: AS
   5: ....
(das blabla stand natürlich nicht so in den Skripten)

An und für sich mag der Ansatz ja ok sein, auch wenn der schon nicht so richtig elegant ist. Was ist, wenn das entsprechende Objekt gar nicht vorhanden ist? Dann kommt sofort

Msg 3701, Level 11, State 5, Line 1
Cannot drop the procedure 'blabla', because it does not exist or you do not have permission.

Wie man es richtig macht, hat Frank in seinem BlogPost ja aufgezeigt, nämlich:

   1: IF OBJECT_ID('blabla','P') IS NOT NULL
   2:     DROP PROCEDURE blabla

oder dann demnächst in SQL Server 2016:

   1: DROP PROCEDURE IF EXISTS dbo.blabla
   2: GO
   3: CREATE PROCEDURE dbo.blabla
   4: AS

Nun aber Folgendes: Was ist denn, wenn man tatsächlich eine Datenbank hat, wo granular Berechtigungen vergeben worden sind und es ggf. kein dediziertes Schema gibt, auf dem ich die entsprechenden Berechtigungen vergeben kann? Wo also zum Beispiel ein bestimmtes Servicekonto nur eine Prozedur ausführen oder eine View abrufen soll? Mit dem DROP ist das Objekt weg und damit auch die granulare Berechtigung auf eben dieses.

Von daher hab ich nun schon einige Male folgende Idee mit auf den Weg gegeben (und es ist nichts Neues): Prüft, ob das entsprechende Objekt da ist oder nicht. Wenn nicht, dann einach einen Dummy anlegen und nachgelagert dann das neue Objekt mit ALTER begegnen. Wenn es schon da ist, dann greift direkt Euer ALTER Befehl. Also quasi so:

   1: IF OBJECT_ID('dbo.blabla','P') IS  NULL
   2:     EXEC ('CREATE PROCEDURE blabla
   3:     AS
   4:     SET NOCOUNT ON;')
   5: GO
   6: ALTER PROCEDURE blabla
   7: AS
   8: BEGIN
   9: PRINT 'So ist es doch besser....'
  10: END

Dies hat den Charme, dass Ihr nicht bereits gesetzte Berechtigungen verliert. Erspart in mancher heißen Testphase das eine oder andere Telefonat mit dem DBA eures Vertrauens.


Sascha Lorenz: SQL Server 2016 "R" in Reporting Services zur Implementierung eines IBCS Charts

Heute möchte ich kurz auf einen Post meines PSG Kollegen Thomas Martens (kurz Tom) hinweisen:

Er ist u.a. Experte für die Sprache “R” und beschäftigt sich schon einige Jahre mit diversen Implementierungen von fortgeschrittenen Algorithmen. Um so erfreuter war er, als bekannt wurde, dass Microsoft in dem kommenden SQL Server 2016 “R” integrieren wird.

Neben der Nutzung von “R” für komplexe finanzmathematische und statistische Herausforderungen ist ein weiteres Steckenpferd vom Tom die Entwicklung von individuellen Visualisierungen mittels des “R”-Paketes GGPLOT2.

Daher hat er es sich nicht nehmen lassen quasi Stunden nach der Verfügbarkeit des CTP 3 ein erstes Beispiel in seinem Blog zu posten.

https://minceddata.wordpress.com/2015/11/03/they-walk-in-line-sql-server-2016-reporting-services-and-r-charting-using-ggplot2-2/

Dabei handelt es sich einfach gesagt um einen SSRS Report, welcher mittels eines “R”-Skriptes Daten aus einer SQL Server Tabelle und SSRS Parameter nutzt, um dann mit GGPLOT2 eine Umsetzung eines IBCS Charts live zu rendern.


Das eröffnet der Nutzung des SQL Servers und der Reporting Services ganz neue Dimensionen!


Tom scharrt schon mit den Füssen, weil “R” auch für seinen Liebling Power BI angekündigt wurde. Ich bin gespannt.

Dirk Hondong: PASS Camp 2015–und ich als “first timer” mit dabei

Jupps, Ihr habt richtig gelesen. Der @SQLPaparazzo wird dieses Jahr tatsächlich beim PASS Camp mit am Start sein. Lange musste ich mich gedulden, doch jetzt hat es geklappt.

PassCamp

Für diejenigen, die davon noch nichts gehört haben sollten (was eigentlich sehr unwahrscheinlich ist): es ist DAS Ereignis, was die PASS Deutschland zu bieten hat. Drei Tage geballter Wissenstransfer. Und das nicht einfach in Form von Präsentationen und kleinen Demos des Sprechers.  Das Zauberwort heißt “Hands on”. Und dazu in einer richtig tollen Location, dem Lufthansa Training & Conference Center und mit noch tolleren Sprechern. Guckt Euch einfach mal die Liste an.

Glücklicherweise konnte ich noch einen Platz im DBA Track ergattern. Denn hier gilt, wie auch für den BI und den SQL Information Services Track: nur 20 Teilnehmer für den Track.  Es ist also schon eine exklusive Veranstaltung und der Schwerpunkt wird wohl der SQL Server 2016 sein, dessen CTP 3.0 seit kurzem draußen ist.

Und dabei fällt mir gerade ein: wie bereite ich mich eigentlich auf das PASS Camp vor? Schließlich war ich ja noch nie mit dabei. Vielleicht hat ja der eine oder andere einen Tipp, was man auf keinen Fall vergessen sollte. Ein Notebook einpacken wäre nicht verkehrt. Schon mal den SQL Server 2016 installieren? Macht bestimmt Sinn….  Smiley

Jedenfalls freue ich mich schon richtig, die “Familie” wieder zu treffen.


Christoph Muthmann: Transactional Replication to Azure SQL DB is now in public preview

Das SQL Server Tiger Team hat in seinem blog eine interessante Neuigkeit für die Replikation zu einer Azure Datenbank angekündigt.

Ganze Geschichte »

Christoph Muthmann: Microsoft Business Intelligence - Die Reporting Roadmap

Microsoft hat jetzt die Roadmap für seine BI Produkte veröffentlicht. Nachdem Gartner Microsoft in acht aufeinander folgenden Jahren als "Leader" positioniert hat, gibt es weiterhin ambitionierte Pläne.

Ganze Geschichte »

SQLPodcast: #012 – tSQLt

In dieser Folge vom PASS Summit 2015 aus Seattle habe ich mich endlich mal wieder mit meinem MVP und PASS Kollegen Frank Geisler unterhalten. Unser Thema dieses mal war tSQLt - Unit Testing für T-SQL. Frank hat einen groben Überblick gegeben was mit tSQLt machbar ist, wie Unit Tests funktionieren und wie man diese in seine Datenbank-Projekte integrieren kann.

Andreas Wolter: Conferences in the second half of 2015: From Asia to America to Europe // Konferenzen im 2. Halbjahr 2015: Von Asien über Amerika bis Europa

Christoph Muthmann: Änderungen bei @@Version

Wie bereits gestern angedeutet, gibt es eine Änderung bei der Ausgabe der Version. Mittlerweile ist der Workaround auch öffentlich dokumentiert. Es gibt ein paar neue Optionen für SERVERPROPERTY.

Ganze Geschichte »

Christoph Muthmann: Update für SQL Server 2014 (Oktober 2015)

Ganz frisch erschienen und im SQL Sentry Blog veröffentlicht wurden ein CU für SQL Server 2014 RTM und eins für SQL Server 2014 SP1.

Ganze Geschichte »

Robert Panther: BASTA! Nachlese

BASTA! 2015

Die diesjährige Herbst-BASTA! ist vorüber. 3 Tage Konferenz + 2 Workshoptage, wobei von den Konferenztagen an einem Tag einer der insgesamt 9 Vortragssääle von Morgens bis Abends SQL Server-Themen gewidmet war.

Darunter auch mein Vortrag zum Thema SQL Server Indizes, an dem immerhin über 70 Leute teilgenommen haben. Die Präsentation dazu kann hier heruntergeladen werden: SQL Server Indizes gestern und heute

Weitere Infos zur Veranstaltung gibt es auf der offiziellen Veranstaltungs-Website: http://www.basta.net

Die Vorbereitungen für die nächsten Konferenzvorträge laufen bereits …


Andreas Wolter: Tales from “certification-hell”: What an MCM, MCA, MCSM, MCSM Charter Member, MCSE or MVP really is / Geschichten aus der Zertifizierungs-Hölle: Was ein MCA, MCSM, MCM, MCSM Charter Member, MCSE oder MVP wirklich ist

 

(DE)
2 Jahre ist es her, dass Microsoft das Ende des Master-Programms angekündigt hat. (Microsoft Certified Master & Architect (MCM & MCA) – The End of Advanced Certification. – And a planned new beginning?)

Aber damit ist die Zertifizierung nicht „tot“ oder gar „wertlos“, wie oft falsch zitiert.
Ganz im Gegenteil: sie ist fast noch wertvoller, denn nur, wer es bis zum 31. Dezember 2013 durch alle Prüfungen schaffte, kann sich heute MCM oder gar MCSM nennen.
Was „tot“ ist, ist das „Programm“, der Zertifizierungspfad mit den Prüfungen und eben der Möglichkeit das Zertifikat zu erhalten.

Da ich über die letzten Jahre immer wieder gefragt wurde, was eigentlich ein MCSM ist oder auch ein „Charter Member“ und wie der MVP da mitspielt, möchte ich dies hier einmal versuchen zu erklären. Für die unlogischen Hintergründe bin ich allerdings nicht verantwortlich – das lag ganz in der Hand von Microsoft Learning.

(EN)
It has been two years since Microsoft announced the end of the Master program.  (Microsoft Certified Master & Architect (MCM & MCA) – The End of Advanced Certification. – And a planned new beginning?)

But that does not mean that the certification is “dead” or even “valueless,” as has often been mistakenly referred to.
Quite the contrary: it is almost more valuable because only those who made it through all the exams by 31 December 2013 may call themselves MCM or even MCSM today.
What is “dead,” then, is the “program” – the certification track with the exams, and thus the possibility to obtain the certificate.

As I have been repeatedly asked over the course of the last years what an MCSM actually is, or a “Charter Member,” and how the MVP fits in there, I would like to try to elaborate on it here. Only I am not responsible for the illogical backgrounds – this was all Microsoft Learning.

Werfen wir einen Blick auf die Zertifizierungspyramide, wie sie noch lange Zeit bei Microsoft Learning online zu finden war:

Let’s take a look at the certification pyramid as it had been available at Microsoft Learning online for a long time:

 

 Microsoft-Certification-MCSA-MCSE-MCM-MCSM

Das Schöne an dieser Übersicht, ist, dass es auch noch den „alten“ Pfad „MCTS-MCITP-MCM“ zeigt.
Diese hier erste Zertifizierungsreihe wurde aufgrund der Entwicklung zur Cloud hin mit Erscheinen von Windows Vista, Windows Server 2008 und für SQL Server Version 2012 durch den neuen Pfad „MCSA-MCSE-MCSM“ abgelöst.

Very conveniently, it still includes the “old” track “MCTS-MCITP-MCM.
This first certification series was replaced by the new track “MCSA-MCSE-MCSM” due to the development towards the cloud and the emergence of Windows Vista, Windows Server 2008, and for SQL Server Version 2012.

 

MCM_SQL_Server_MCSM_Data_Platform

 

Damit ist also der Unterschied vom MCM zum MCSM gelöst:

Der MCSM ist ebenso wie der MCM die höchste technische Zertifizierung von Microsoft, aber für die neueren Server-Systeme.

Für SQL Server nennt sich der MCSM, der auf SQL Server 2012 erworben werden konnte damit auch: Microsoft Certified Solutions Master: Data Platform (hier kann man das offizielle Data Sheet dazu einsehen)

Den MCM, Microsoft Certified Master SQL Server gab es für SQL Server 2005 und SQL Server 2008.
(hier kann man das offizielle Data Sheet dazu einsehen)

Hence this explains the difference between MCM and MCSM:

The MCSM is, just like the MCM, the highest technical certification of Microsoft, but for the recent server systems.

For SQL Server, the MCSM that could be obtained on SQL Server 2012 is thus also called: Microsoft Certified Solutions Master: Data Platform (see the official data sheet here).

The MCM, i.e. Microsoft Certified Master SQL Server was available for SQL Server 2005 and SQL Server 2008 (see the official data sheet here).

Was ist bei den Prüfungen zum MCM & MCSM anders als zum MCSE und MCSA?

Für die Standard-Zertifizierungen legt man eine Reihe an Multiple-Choice-Examen ab. Die genauen Prüfungen kann man hier nachlesen: MCSE: Data Platform

Für den MCM und auch den MCSM war eine weitere („weitere“, da eine bestehende MCITP-Zertifizierung Voraussetzung war) sogenannte „Wissens-Prüfung“ erforderlich, die ebenfalls im Multiple-Choice-Format war. Allerdings waren die Fragen hier deutlich komplexer und praxisnäher.
Der entscheidende Unterschied aber ist, dass für den Master eine praktische Prüfung, das „Lab-Exam“ erforderlich war. Diese wurde an einem Terminal mit Zugriff auf eine echte SQL Server Umgebung durchgeführt, auf der dann diverse Probleme innerhalb einer vorgegebenen Zeit zu lösen waren.

Für den MCM SQL Server 2008 waren 5 Stunden und 25 Minuten angesetzt.
Für den MCSM auf Basis SQL Server 2012 gab es mehr Aufgaben in 7 Stunden zu lösen.
- genaueres darf ich leider nicht schreiben.

Leider konnte man die Prüfung zum MCSM: Data Platform erst ab September 2013 ablegen.
Insgesamt 7 Experten weltweit haben diese Möglichkeit erfolgreich genutzt.

How do the exams for MCM & MCSM differ from those for MCSE and MCSA?

The standard certifications are preceded by a series of multiple-choice exams. You can read  the exact exams here: MCSE: Data Platform

The MCM as well as MCSM required an additional (“additional” because an existing MCITP certification was prerequisite), so-called “Knowledge-Exam,” equally in multiple-choice format. However, the questions here were significantly more complex and practical.
The decisive difference, though, is that the Master required a practical exam, i.e. the “Lab-Exam.” This exam was carried out at a terminal with access to a real SQL Server environment at which various problems were to be solved within a specified time period.

For the MCM SQL Server 2008, the allotted time was 5 hours and 25 minutes. For the MCSM on the basis of SQL Server 2012, tasks had to be solved within 7 hours. – I am not allowed to be more specific here.

Unfortunately, it was only from September 2013 onwards that it was possible to pass the exam for MCSM: Data Platform.
In total, seven experts world-wide have successfully taken this opportunity.

Und damit hoffe ich auch dargelegt zu haben, warum die MCM und erst recht die MCSM Zertifizierung nicht wertlos ist: Eine der ursprünglichen Gedanken für das Master-Programm war es, so zu prüfen, dass man sichergehen konnte, dass derjenige, der besteht, damit echte praktische Erfahrung beim Lösen komplexer Probleme mit SQL Server nachgewiesen hat. Und diese geht ja nicht verloren. Wer Erfahrung mit SQL Server 2005 Datenbanktuning, Desaster Recovery etc. hat, kann auf diese auch unter neueren Versionen zurückgreifen.
Was natürlich für alle Produktzertifizierungen gilt, ist, dass die neuen Features damit nicht unbedingt auf dem Level bekannt sein müssen.

That said, I hope this explains why the MCM certification is not valueless, and certainly not the MCSM certification: One of the original ideas for the Master program was to examine in such a way that one could be sure that the examinee who passed thus proved true practical experience in solving complex problems with SQL Server. And this experience does not get lost. Once you have experience in SQL Server 2005 Database tuning, Desaster Recovery etc. you can also rely on it in more recent versions as well.
What applies to all product certifications is that the new features do not necessarily have to be known at this level.

(Das war für mich persönlich der Grund, auch noch den MCSM hinterherzulegen: um meine Kenntnisse auf SQL Server 2012 mit den damals neuen Features AlwaysOn und ColumnStore damit belegen zu können.

- Und natürlich, um die „Schmach“ der im ersten Anlauf verbockten Lab-Prüfung mit 82% der benötigten Punktzahl (weil ich die falsche Zeitzone im Blick hatte?) wieder gut zu machen.
– Ohne Chance auf Wiederholung am letzten Tag der Prüfungsverfügbarkeit ist mir das ja glücklicherweise auch gelungen :-): MCSM (Microsoft Certified Solutions Master) Data Platform on SQL Server 2012)

(For me personally, this was the reason to also add the MCSM exam right behind: so I could prove my knowledge in SQL Server 2012 with the then new features AlwaysOn and ColumnStore.

– And of course to make up for the “disgrace” of having screwed up the Lab-Exam on the first go with 82% of the required score (because I erred in the time zone?). – And, quite luckily, I was  successful at it – and this with no chance at a retry, as I passed it on the last day the exam was available :-):  MCSM (Microsoft Certified Solutions Master) Data Platform on SQL Server 2012

 

Für den nächsten Punkt muss ich gestehen, dass die obige Pyramide eigentlich nicht ganz vollständig war. Ursprünglich sah sie so aus:

Regarding the next point, I admit that the pyramid shown above was actually not quite complete. Originally, it looked like this:

 
 MCA-MCSM-MCM-MCSE-MCA_Pyramid

 

Was aber ist denn ein MCA?

MCA steht für Microsoft Certified Architect. Dieser Zertifizierung, die an der Spitze der Pyramide stand, war nicht so sehr eine technische, d.h. es wurden keine weiteren Praxis-Prüfungen oder ähnliches durchgeführt. Vielmehr musste der- oder diejenige gegenüber einem Gremium, dem „MCA-Board“, Rede und Antwort zu der Durchführung von realen Projekten mit SQL Server stehen musste. Hierbei ging es nicht so sehr um Technik, dafür war man ja bereits MCM, sondern mehr um die anderen Fähigkeiten wie Teamführung und Konzeption, die man als Architekt eben haben sollte.

Hier schreibt ein MCA dazu: What is a Microsoft Certified Architect?

Deshalb wird der MCM auch oft als die höchste „technische“ Zertifizierung angegeben. Der MCA ist jedoch die uneingeschränkt höchste Zertifizierung von Microsoft.

So what then is an MCA?

MCA stands for Microsoft Certified Architect. This certification, which was at the top of the pyramid, was not so much a technical one – i.e. no further practical exams or the like were carried out. Rather, the examinee was required to answer questions from a committee, the “MCA Board,” on the implementation of real projects with SQL Server. Here, it was not so much about technique – the MCM already covered that part – but rather about other skills such as team leadership and concept competencies.

Read here what an MCA says: What is a Microsoft Certified Architect?

This is why the MCM is oftentimes referred to as the highest “technical” certification. Yet it is in fact the MCA which is the highest Microsoft certification by far and without restrictions.

   MCA_SQL_Server_2005_2008

 

Trivia: Die allererste Runde an MCA’s hatte übrigens noch keine SQL Server Version im Titel, sondern hieß schlicht „MCA: Database“. Davon gibt es insgesamt 26 weltweit. (Und diese Zahl ist wirklich fix, auch wenn nicht alle Namen veröffentlicht wurden.) 2007 wurde aus diesem Titel dann der MCA: SQL Server 2005.

Trivia² In der Zeit des „MCA: Database" gab es noch einen „MCA apprentice“, der den ersten Experten verliehen wurde, die durch das „Ranger“-Programm (der Vorläufer des MCM-Programms) gegangen sind, aber aufgrund ihrer Rolle nicht dem MCA-Board gegenübersitzen konnten – diese wurden anschließend zu MCMs. (Danke an boB Taylor und Assaf Fraenkel für die Internas).

Trivia: The very first round of MCAs did not have any SQL Server Version in the title and was simply called „MCA: Database.“ There are 26 worldwide. (This number is definitely fixed, even if not all names were published.) In 2007, this title became the MCA: SQL Server 2005.

Trivia²: In the “MCA: Database“ era there was an “MCA apprentice” which was awarded to the first experts who made it through the “Ranger” program (the forerunner of the MCM program), but who due to their role could not sit across from the MCA Board – they subsequently became MCMs. (Thanks to  boB Taylor and Assaf Fraenkel for this in-house information.)

5 MCAs für SQL Server halten den MCA sowohl unter 2005 als auch 2008.

Auf SQL Server 2012 gab es keinen MCA mehr zu erwerben.

5 MCAs for SQL Server hold the MCA both for 2005 and 2008.

For SQL Server 2012, there was no more MCA to be obtained.

  MCA_Microsoft Certified_Architect

 

Die letzte, finale und öffentliche Liste der MCAs, MCSMs und MCMs weltweit kann man immer noch hier finden: www.microsoft.com/en-us/learning/mcsm-certification.aspx

Es gilt jedoch weiterhin, dass die Liste und damit Anzahl nicht ganz vollständig ist, denn nicht jeder möchte seinen Namen veröffentlich sehen.
Für SQL Server gibt es demnach weltweit die folgende Anzahl „Mastern“:

The last, final and public list of the MCAs, MCSMs and MCMs worldwide can still be found here: www.microsoft.com/en-us/learning/mcsm-certification.aspx

Note that the list, and hence the number, is still not entirely complete as not everyone wants his name published.
For SQL Server, there is thus the following number of “Masters” worldwide:

 

MCM SQL Server 2005

MCA SQL Server 2005

MCM SQL Server 2008

MCA SQL Server 2008

MCSM Data Platform

33

23 (+3)

172

7 (+1)

7

 

Und da ich diesen Spruch von Brent Ozar, einem MCM-Kollegen aus den USA, so leger fand, hier eine aktualisierte Version davon:

Because I thought this comment by Brent Ozar, an MCM colleague from the US was so casual, here is an updated version:

„Es gibt mehr Menschen, die den Mond betreten haben (12) als es MCSMs für SQL Server 2012 gibt (7).“ :-D

“There are more people who set foot on the moon (12) than there are MCSMs for SQL Server 2012 (7).“ :-D

 

Ja, und dann wäre da noch das „Ding“ da:

Well, and then there is still this „thing“ here:

 

MCSM_Data-Platform-Charter-Member

 

Was ist ein MCSM: Charter Member (Microsoft Certified Solutions Master Charter – Data Platform)?

Diese „Zertifizierung“, wurde allen MCMs zusätzlich zum MCM gegeben. Ohne irgendeine weitere Prüfung oder Voraussetzung. Also letztlich, dem Worte nach, zertifiziert sie: Nichts.


Hart aber wahr: Hier die offizielle Verlautbarung dazu: Existing Microsoft Certified Masters To Receive Microsoft Certified Solutions Master Charter Certification.

Warum macht man so etwas?
Vermutlich war es eine Art „Trostpflaster“ für die Abkündigung der Zertifizierungsreihe.
Ganz ursprünglich war es nur für eine Übergangsphase gedacht, aber nachdem klar war, dass das es den MCSM nicht lange geben würde, wurde der Charter Member auf Dauer vergeben.

(Zusätzlich wurde übrigens auch der MCSE: Data Platform allen MCMs „geschenkt“. - Zu dem Zeitpunkt hatte ich diesen jedoch bereits auf regulärem Wege in der Tasche, so dass ich gar nicht erst in die Versuchung kam, Prüfungen zu skippen :-))

An dieser Stelle kann ich kaum verbergen, das mich jegliches Verbiegen der Nachweiskraft der Microsoft-Zertifizierungen wenig freut.

Leider verwirren diese Aspekte letztlich nur noch mehr in dem Zertifizierungs-Dschungel.

Normalerweise wurde ein „Charter Member“ bis dato für das Erreichen einer Zertifizierung innerhalb der ersten 6 Monate nach dem erstmaligen Erscheinen einer Zertifizierung vergeben. (Quellen: What’s the deal with Charter Member certificates?, https://www.microsoft.com/en-us/learning/program-membership.aspx )
Als Bonus für diejenigen, die sich als Erste an die Prüfungen gewagt und sie bestanden haben. – Diese „First-Achievers“ konnten nämlich keinesfalls bereits auf irgendwelche Braindumps zurückgreifen – und wir wissen ja alle, dass die normalen Prüfungen zu leicht mit allen möglichen Tricks erreichbar sind.
- Ich selber habe bisher 6 Zertifizierungen mit dem Charter-Member Status erreicht (diesen unsinnigen MCSM zähle ich da nicht mit). (andreas-wolter.com/zertifizierungen)

What is an MCSM: Charter Member (Microsoft Certified Solutions Master Charter – Data Platform)?

This “certification” was awarded to all MCMs in addition to the MCM. Without any further exam or prerequisite. Ultimately, accordingly, it certifies: nothing.


Hard but true: Here you can read the official statement: Existing Microsoft Certified Masters To Receive Microsoft Certified Solutions Master Charter Certification.

Why would they do this?

Presumably this was a kind of “consolation” for the discontinuation of the certification series. Initially, it was only intended for a transition period, but after it became clear that the MCSM would not exist for much time the Charter Member was awarded permanently.

(By the way, in addition to that the MCSE: Data Platform was “given” to all MCMs. – At that time, though, I had already obtained this title the standard way so I was not even tempted to skip exams :-)).

At this point, I can hardly disguise the fact that I am not particularly happy about any bending of value of the Microsoft certifications.

Unfortunately, these aspects are ultimately only confusing in the certification jungle. Previously, a “Charter Member” was usually awarded for the achievement of a certification within the first six months after the first appearance of a certification. (Sources: What’s the deal with Charter Member certificates?, https://www.microsoft.com/en-us/learning/program-membership.aspx )
As a bonus for those who were the first to venture on these exams and passed them. – These “First Achievers” were definitely not able to rely on any braindumps – and we are all aware that the normal exams are easily achieved with all sorts of tricks.
– I myself have achieved 6 certifications with the Charter-Member status up to now (and I am not counting the senseless MCSM). (andreas-wolter.com/zertifizierungen)

Im Endeffekt hat das Charter Member-Logo also keinen wertigen Hintergrund. Deshalb verwende ich es auch kaum je irgendwo – wer erklärt schon gerne auf Nachfrage, dass eine Zertifizierung nichts Reelles bedeutet.

At the end of the day, the Charter Member logo does not have a significant background. Therefore, I hardly use it anywhere – for, who would like to explain, when asked, that a certification does not really mean anything.

 

Kommen wir zu einem ganz andern Zertifikat: SSAS Maestro

Let’s turn to an entirely different certificate: SSAS Maestro

Zu dieser seltenen Zertifizierung kann ich weder ein Logo noch eine genaue Zahl an Individuen nennen, die diese halten.
Diese Zertifizierung hatte eine noch viel kürzere Lebensspanne, als der Master. Der Maestro wurde eingeführt, um auch für die Business Intelligence –Seite des SQL Server, speziell den Analysis Services eine entsprechende Premium-Zertifizierung anbieten zu können. Hier die offizielle Ankündigung von damals: What is the SSAS Maestros?
Eine Abkündigung gab es nicht mal.

For this rare certification I can neither give a logo nor an exact number of individuals who hold this certification.
This certification had an even shorter lifespan than the Master. The Maestro was introduced in order to be able to offer a corresponding premium certification for the Business Intelligence part of the SQL Server, particularly the Analysis Services. Here you can read the official announcement of that time:
What is the SSAS Maestros?
There was not even an announcement of discontinuation.

 

Bleibt ein Thema: Der MVP Award

We have one topic left: The MVP Award

 

MVP_Microsoft_Most_Valuable_Professional 

Da kann man berechtigt fragen: „Was hat das hier zu suchen? – Das ist doch gar keine Zertifizierung.
Dem gebe ich Recht. Das ist jedoch nicht jedem bekannt und vor allem ist es dennoch ein gern gesehener Titel auf Konferenzen oder in Projekten, und darum erläutere ich hier kurz, was ein MVP eigentlich ist oder sein sollte.

One is entitled to the question: “What’s this got to do with it? – It is not even a certification!”
I agree. However, not everyone is aware of this, and it is still a welcome title at conferences or in projects. That’s why I will briefly explain what an MVP actually is or is supposed to be.

An dieser Stelle möchte ich gern aus dem Blog eines ehemaligen MVP, Mitch Garvis zitieren:

“Der Microsoft MVP Award ist nicht für Leute, die in ihrer Technologie kompetent sind; er ist für Leute, die ihre Kompetenz mit der Arbeit in der Community teilen, beispielsweise in Blog-Artikeln, als Referent bei Events und Vorträgen, in Tweets, Foren und dergleichen.“
(Quelle: My parting words as a Microsoft MVP)

At this point, I would like to quote from the blog of a former MVP, Mitch Garvis:

“The Microsoft MVP Award is not for people proficient in their technology; it is for people who share their proficiency with community work, such as blog articles, speaking events & presentations, tweets, forums, and such.“
(Source: My parting words as a Microsoft MVP)

Bei Microsoft selber liest es sich wie folgt:

Wer sind MVPs?:

Microsoft Most Valuable Professionals, oder MVPs, sind Führungspersönlichkeiten, die ein vorbildliches Engagement gezeigt haben, anderen durch ihre Erfahrung mit Microsoft-Technologien zu helfen um diese optimal zu nutzen. Sie teilen ihre außergewöhnliche Leidenschaft, Ihr Praxiswissen und Ihr technisches Know-how mit der Community und Microsoft.“

„Wie wird man ein MVP?:

Es gibt keinen Maßstab wie man ein MVP werden kann, weil dies durch die jeweiligen Technologien und ihre Lebensdauer variiert. Folgende Beiträge beurteilen wir während der Bewertungsphase: Beiträge in online-Foren wie Microsoft Answers, TechNet und MSDN; Wikis und online-Inhalte; Konferenzen und Benutzergruppen; Podcasts, Websites, Blogs und social Media; und Artikel und Bücher. Jeder Beitrag eines Kandidaten wird jedes Jahr mit den Beiträgen anderer Kandidaten verglichen, der sich in der gleichen Expertise befindet.“
(Quelle: Microsoft: Most Valuable Professional)

At Microsoft itself it reads as follows:

Who are MVPs?

Microsoft Most Valuable Professionals, or MVPs, are community leaders who’ve demonstrated an exemplary commitment to helping others get the most out of their experience with Microsoft technologies. They share their exceptional passion, real-world knowledge, and technical expertise with the community and with Microsoft.“

“How to become an MVP?

While there is no benchmark for becoming an MVP, in part because it varies by technology and its life-cycle, some of the criteria we evaluate include the impact of a nominee’s activities in online forums such as Microsoft Answers, TechNet and MSDN; wikis and online content; conferences and user groups; podcasts, Web sites, blogs and social media; and articles and books. Each nominee's activities are compared to those of other candidates, and active MVPs receive the same level of analysis as new candidates each year.”
(Source: Microsoft: Most Valuable Professional)

Das bedeutet, es gibt weder ein einheitliches Verfahren, wer diesen Award = „Auszeichnung“ verdient, noch gar eine Prüfung.
Auch fachliche Kompetenz steht hier nicht im Vordergrund und ist niemals der Grund. Für fachliches Wissen gibt und gab es Prüfungen. Auch wenn es seit dem Wegfall des MCM-Programms leider keine Praxisprüfungen mehr gibt – der MVP ist kein Ersatz dafür.

That means there is no standardized procedure as to who deserves this award, nor is there an exam.
Neither are technical skills in the foreground here, and nor are they ever the reason for such an award. For technical knowledge, there are and were exams.
Even if unfortunately there are no longer any practical exams since the discontinuation of the MCM program, the MVP is no replacement.

Zitate wie „Von Microsoft ist er aufgrund seines Fachwissens ausgezeichnet mit dem Titel ‘Microsoft Valuable Professional‘ “ sind daher leider irreführend.

Auch wenn sich unter den MVPs eine Vielzahl an echten Experten findet, ist das nicht das Kriterium. Es gibt viele zertifizierte Master, die nicht die Zeit oder Gelegenheit haben, ihr Wissen kostenlos (auch das ist ein Aspekt) der Öffentlichkeit zugänglich zu machen – oder die tun es, aber die entscheidenden Personen erfahren nicht davon, oder es gibt eine Länderspezifische Quote an maximalen MVPs je Thema, und so weiter, und ergo werden sie nicht mit dem MVP belohnt.

Dann gibt es immer auch die, die seit Jahren schon massiv viel in der Community aktiv sind, auf dutzenden internationalen Konferenzen aufgetreten sind, sogar bereits nominiert worden sind, und dennoch keinen Award erhalten. (Siehe Fälle wie Mark Broadbent)
Und auf der anderen Seite gibt es auch solche, von denen noch fast niemand je gehört hat, und die erst mit dem MVP wirklich bekannt und damit auch gefragt und aktiv werden.  So ähnlich ist es letztlich bei vielen Auszeichnungen. Absolute Fairness wird es nie geben. - Ein Award ist nunmal keine Zertifizierung, und vielleicht sollte man einen solchen manchmal auch nicht so ernstnehmen.
Insofern kann ich mich letztlich glücklich schätzen, dass ich 2014 MVP wurde, nachdem ich bereits seit 2009 auf Konferenzen von Deutschland bis in die USA aktiv bin. (2012 6 Konferenzen, 2013 11, 2014 wurde ich dann MVP)

Quotes such as “He has been awarded the title ‘Microsoft Valuable Professional’ by Microsoft based on his technical knowledge” are thus misleading.

It is true that among the MVPs there are many real experts, but it is not the criterion. There are many certified Masters who do not have the time or opportunity to make their knowledge available to the public for free (this, too, is an aspect) – or they do make it available, but the deciding people do not hear about it, or there is a country-specific quota of a maximum of MVPs per topic, and so on, and therefore they are not awarded the MVP.

On the one hand, there are those who have been extremely active in the community for years, have been presenting at dozens of international conferences, have even been nominated, and still do not receive any award. (See cases like Mark Broadbent)
On the other hand, there are also those of whom almost no one has ever heard, and who only with the MVP actually become known and as a result are in demand and become active. In the end, this is how it goes with many awards. There will never be an absolute fairness. – An award is no certification after all, and sometimes maybe it’s better to not take such all too serious.

Therefore, I can consider myself lucky that I became MVP in 2014 after having been active on conferences in Germany and as far as in the USA since 2009. (6 conferences in 2012, 11 in 2013, and then followed the MVP award in 2014)

Therefore, I can consider myself lucky that I became MVP in 2014 after having been active on conferences in Germany and as far as in the USA since 2009. (6 conferences in 2012, 11 in 2013, and then followed the MVP award in 2014)

MVP, ganz allgemein, kann man werden, wenn man sich sehr für die Community eines Produktes engagiert. Sei es in Online-Foren durch Hilfestellungen, dem Halten von Vorträgen ohne Entgelt auf diversen Konferenzen, umfangreichen Blog-Artikeln oder anderweitigem Engagement, das in irgendeiner Weise der Produktsparte und anderen Kunden hilft. Ein bestimmtes Level an technischer Komplexität ist hier nicht gefordert, mehr die Kontinuität – und die ist, das kann ich aus persönlicher Erfahrung sagen, durchaus zeitaufwändig. Deshalb freue ich mich auch für jeden, der von Microsoft mit dieser Auszeichnung Anerkennung erhält.
Eine Garantie, bei einem bestimmten Umfang so gewürdigt zu werden, gibt es aber nicht, und sich „MVP werden“ als Ziel zu setzen, würde dem ursprünglichen Gedanken bei der Schaffung dieses Awards auch nicht gerecht.

In general, one can become an MVP if you are very active in the community of a product. Be it through assistance in online forums, giving presentations without fee at various conferences, writing comprehensive blog articles, or other forms of commitment that are helpful in some way to the product line or to other clients. This does not require a specific level of technical complexity but rather the continuity – which is, from my personal experience, quite time-consuming. This is why I am happy for anyone who is acknowledged by Microsoft with this award.
However, there is no guarantee to be recognized at reaching a specific amount of commitment. And to set as one’s goal to “become MVP” would not do justice to the original idea behind the award.

Nominierung

Wer jemanden kennt, von dem er meint er hat die Auszeichnung „MVP“ durch seine Aktivitäten in den letzten 12 Monaten (noch ein Kriterium) verdient haben kann ihn hier direkt nominieren: Nominierung als MVP

Nomination

Who knows someone, which he believes he has the deserved to be awarded with the "MVP" by its activities in the last 12 months (another criterion) may nominate him directly here: Nomination as MVP

Expertise

Was ich aus eigener Kenntnis der Master- als auch MVP-Community sagen kann, ist, dass Master in aller Regel eher breiter aufgestellt sind und, wie ja auch in den Prüfungen verlangt, in mehreren Bereichen der Datenbankengine (Beispielsweise Backup & Restore + Indexing + Volltextsuche) sehr versiert sind. Das  Bei MVPs scheint es eine höhere Spezialisierung für genau ein Thema zu geben (Beispielsweise Indexing oder Hochverfügbarkeit). Das ist mein persönlicher Eindruck und durch keinerlei Umfragen oder gar Prüfungen fundiert und soll auch nicht negativ wirken. :-)

Expertise

What I can say from my own knowledge of the Master and MVP community is that Masters usually possess a broader spectrum of technical knowledge and, as required in the exams, they are very well-versed in several areas of database engine (for example Backup & Restore + Indexing + Fulltextsearch). As for MVPs, there seems to be a higher specialization in exactly one topic (For example Indexing or High Availability). This is really my personal impression and not evidenced by any surveys or exams, and it is not meant to sound negative. :-)

Vorteile, des MVP?

Für mich sind das der frühzeitige Einblick in zukünftige Entwicklungen von SQL Server noch lange vor Veröffentlichung und der direkte Kontakt zum Product-Team. Unter NDA, mit relativ geringen Beschränkungen offene Gespräche mit Microsoft-Entwicklern, für mich auch speziell dem Security-Team halten zu können, ist ein unschätzbarer Bonus.

What are the advantages of the MVP?

To me, it is the insight at an early stage into future developments of SQL Server long before its release, and the direct contact to the product team. To be able to have open discussions with Microsoft developers, and in my case especially also the Security team – under NDA and with relatively few restrictions – is an invaluable bonus. 

 

Ich hoffe, ich konnte ein wenig Licht in den Dschungel der Premium-Zertifizierungen von Microsoft bringen. :-)

I hope I was able to shed some light on the jungle of Microsoft’s premium certifications. :-)

 

Happy learning & sharing

Andreas

Torsten Schuessler: Cumulative Update 8 for SQL Server 2012 SP2

The Cumulative update package 8 for Microsoft SQL Server 2012 Service Pack 2 (Build 11.00.5634) is available:

Cumulative update package 8 for SQL Server 2012 SP2

And very important if you are using FileTable:
The COD hotfix resolves an issue where you may see access violations occur when using FileTable. More details in KB #3087872

I wish you a nice day,
tosc

 

Torsten Schuessler

Uwe Ricken: Forwarded Records in non-clustered Indexen

Während der Erstellung eines Artikels für SIMPLE-TALK kam ein sehr interessanter Aspekt in Bezug auf HEAPS in Verbindung mit non-clustered Indexen in den Fokus: „Wird beim Neuaufbau eines non-clustered Index die Position des Forwarded Record im non-clustered Index gespeichert oder die ursprüngliche Adresse?. Was ein Forwarded Record ist und welchen Einfluss ein Forwarded Record auf die Performance haben kann, beschreibt der Artikel “Forwarded Records Intern”. Der nachfolgende Artikel geht auf die Verwaltung von non-clustered Indexen in einem Heap ein.

Testumgebung

Für den Artikel wird eine Tabelle [dbo].[Customer] erstellt, die mit 1.000 Datensätzen* gefüllt wird. Die Tabelle hat folgenden – einfachen – Aufbau:

CREATE TABLE dbo.Company
(
    Id     INT           NOT NULL     IDENTITY (1, 1),
    Name   VARCHAR(200)  NOT NULL,
    Street VARCHAR(100)  NOT NULL,
    ZIP    VARCHAR(10)   NOT NULL,
    City   VARCHAR(100)  NOT NULL
);
GO

CREATE NONCLUSTERED INDEX ix_Company_ZIP ON dbo.Company (ZIP);
GO

Non-Clustered Index in einem Heap

Ein Non-Clustered Index in einem Heap folgt einer anderen Vorgehensweise als in einem Clustered Index. Während ein non-clustered Index in einem Clustered Index nur den Clusterschlüssel speichert, muss der non-clustered Index für einen Heap immer die absolute Position des Datensatzes speichern, da eine Suche über einen Schlüssel nicht möglich ist. Die nachfolgende Abbildung zeigt die unterschiedliche Indexstruktur für einen Heap und für einen Clustered Index.

INDEX_02_HEAP Die linke Abbildung zeigt den Index [ix_company_zip], wie er in einem Heap gespeichert ist. Der ZIP-Code ist der Indexschlüssel und im Attribut [HEAP RID (key)] wird der Verweis zum Datensatz im Heap gespeichert. Da ein Heap keine Ordnungsschlüssel besitzt, kann der Datensatz nur über die absolute Position angesprochen werden. Die RID ist ein Binärwert mit einer Länge von 8 Bytes, in dem die Datenseite, Dateinummer und Slot ID gespeichert sind. Die ersten vier Bytes speichern die Datenseite während die restlichen vier Bytes für Dateinummer und Slot ID verwendet werden:

0xB4 00 00 00 = Datenseite 180
0x01 00       = Dateinummer 1
0x13 00       = Slot ID 19
Vollkommen anders sieht es jedoch aus, wenn der non-clustered Index [ix_company_zip] in einem clustered Index implementiert wird. Statt einer Referenz zu einer absoluten Position in der Tabelle wird ein Verweis auf den Ordnungsschlüssel gespeichert. Kann der Datensatz [ZIP = 00142] in einem Heap direkt angewählt werden, so muss er in einem Clustered Index über den B-Tree ermittelt werden.

Ein großer Vorteil jedoch ist die Größe des Indexes. Er ist 4 Bytes kleiner, da der Datentyp INT lediglich 4 Bytes konsumiert.

INDEX_02_CLUSTER

Non-Clustered Index und Forwarded Records

Im Zusammenhang mit Forwarded Records kam die Frage auf, ob bei dem Neuaufbau eines non-clustered Index Microsoft SQL Server die “neue” Adresse des Datensatzes im non-clustered Index speichert. Diese Frage kann man ganz klar mit NEIN beantworten. Mit dem nachfolgenden Code wird zunächst die Datenseite ermittelt, auf dem sich der Datensatz befindet.

-- Auf welcher Datenseite liegt der Datensatz mit ZIP = 00142
SELECT FPLC.*,
       C.*
FROM   dbo.Company AS C
       CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS FPLC
ORDER BY ZIP;
GO

RECORDSET_06

Ein Blick auf die Datenseite 145 zeigt, dass kein weiterer Platz mehr zur Verfügung steht.

DBCC TRACEON (3604);
DBCC PAGE (demo_db, 1, 145, 3);

DBCC_PAGE_145

Wird der Datensatz mit der ID = 626 erweitert, so muss es unweigerlich zu einem Forwarded Record kommen. Im Beispiel Fall wird auf der Seite 145 ein FORWARDED_STUB generiert, der auf die neue Datenseite verweist, auf der sich der Datensatz als Forwarded Record befindet.

—Aktualisierung des Datensatzes mit der ID 626
UPDATE dbo.Company
SET    Street = 'Das ist eine Strasse mit einem gaaaaaaanz langen Namen'
WHERE  ID = 626;

DBCC_PAGE_145_02

Wie die Abbildung zeigt, wurde der Datensatz mit der ID = 626 auf die Datenseite 337 in Datendatei 1 in Slot 31 verschoben. Diese Position entspricht – in Binärcode umgewandelt – der RID-Position 0x51 01 00 00 01 00 1f 00. Im nächsten Schritt wird der non-clustered Index neu aufgebaut und anschließend die Indexseiten überprüft.

-- Bestehenden Index neu aufbauen
ALTER INDEX ix_Company_ZIP ON dbo.Company REBUILD;
GO

-- Auf welcher Datenseite ist der Datensatz mit ID = 00142 gespeichert?
SELECT *
FROM   dbo.Company WITH (INDEX(2))
       CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS FPLC

-- Ein Blick auf die Datenseite...
DBCC TRACEON (3604);
DBCC PAGE (demo_db, 1, 1920, 3);
GO

INDEX_02_HEAP

Die Abbildung zeigt, dass trotz Neuaufbau des Index die Position des Datensatzes im Heap nicht geändert wurde. Dieses Ergebnis ist verständlich, wenn man versteht, wie ein Forwarded Record funktioniert.

Ein Forwarded Record gibt den allokierten Speicherplatz nicht auf sondern hinterlässt lediglich einen Verweis auf die neue Position. Tatsächlich wird der Datensatz selbst immer noch auf der Originalseite verwaltet. Ein non-clustered Index in einem Heap speichert – wie oben beschrieben – die exakte Position des Datensatzes und nicht, wie in einem Clustered Index, einen Schlüsselwert. Somit versteht sich das Ergebnis als Konsequenz daraus, dass bei einem Rebuild des Index die Position des Datensatzes im Heap zu keiner Zeit neu positioniert wurde.

Herzlichen Dank fürs Lesen!

*Achtung: Werbung

Für das Befüllen von Testtabellen verwende ich sehr gern den SQL Data Generator von RedGate. Hiermit lassen sich in wenigen Sekunden komplexe Datenmodelle mit Testdaten füllen.

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

Uwe Ricken: Forwarded Records intern

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

Interner Aufbau eines HEAPS

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

PFS – Datenseite

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

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

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

IAM – Datenseite

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

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

RECORDSET_03

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

FORWARDED RECORDS?

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

Testumgebung

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

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

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

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

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

RECORDSET_01

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

RECORDSET_02

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

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

RESULT_01

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

FORWARDED RECORDS generieren

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

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

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

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

RECORDSET_04

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

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

FORWARDED RECORS erkennen

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

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

PHYSICAL_STATS_01

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

FORWARDED RECORDS lesen

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

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

RESULT_02

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

PHYSICAL_READS

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

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

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

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

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

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

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

Warum vollständig qualifizierte Objektverweise?

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

Testumgebung

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

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

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

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

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

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

    CONSTRAINT pk_foo PRIMARY KEY CLUSTERED (Id)
)
GO

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

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

Abfragen ohne qualifizierte Objektnamen

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

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

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

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

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

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

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

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

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

RECORDSET_01

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

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

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

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

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

ResultSet_03 ResultSet_04

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

Abfragen mit qualifizierten Objektnamen

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

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

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

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

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

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

Zusammenfassung

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

Verweise

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

Herzlichen Dank fürs Lesen!

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

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

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

sqlserverperformanceratgeber

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


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

Liebe PASS'ler und SQL Server Enthusiasten in Bayern,

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

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

Philipp Lenz: SSRS: Inhaltsverzeichnis / Document Map

(english Version below)

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

26-07-_2015_16-10-102

 

 

 

 

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

Philipp Lenz: SSRS: Summen aus anderen Gruppen erhalten

(see english Version below)

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

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

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

 

 

 

 

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

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

tablix1

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

 

English Version

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

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

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

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

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

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

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

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

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

Der Security Bulletin vom Juli 2015:

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

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

Beschrieben werden 3 Sicherheitslecks:

Welche SQL Server Versionen sind betroffen?

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

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

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

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

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

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

Securing SQL Server

SQL Server Security Blog

SQL Server Best Practices – Implementation of Database Object Schemas

SQL Server 2012 Security Best Practices – Operational and Administrative Tasks 

SQL Server 2012 Label Security Toolkit and white paper

SQL Server Database Ownership: recommendations

Guest account in User Databases

 

Happy patching

Andreas

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

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

IoT Camp/Hackathon: Das Internet der Dinge live erleben

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

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

Mehr dazu gibt es auf Olivia's Blog.

 

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

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

Abstract

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

Slides



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

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

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

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

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

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

Liebe PASS'ler und SQL Server Enthusiasten in Bayern,

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

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

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

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

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

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

Liebe PASS’ler in Bayern,

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

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

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

Uwe Ricken: Temporäre Tabellen vs. Tabellenvariablen

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

Temporäre Tabellen

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

Temporäre Tabellen befinden sich in der TEMPDB

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

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

Lokale temporäre Tabellen

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

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

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

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

     INSERT INTO #User1_Table DEFAULT VALUES
     GO 10

     SELECT * FROM #User1_Table
REVERT
GO

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

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

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

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

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

Globale temporäre Tabellen

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

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

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

Temporäre Tabellen und Indexierung

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

USE tempdb;
GO

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    SET NOCOUNT OFF;
GO

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

Temporäre Tabelle und Statistiken

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

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

SET NOCOUNT ON;
GO

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

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

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

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

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

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

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

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

Temporäre Tabellen und Transaktionen

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

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

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

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

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

Temporäre Tabellen und Collation

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Tabellenvariablen

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

Gültigkeit von Tabellenvariablen

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

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

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

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

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

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

Tabellenvariablen und Indexierung

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

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

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

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

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

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

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

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

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

Tabellenvariablen und Statistiken

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

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

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

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

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

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

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

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

SET NOCOUNT ON;
GO

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

    PRIMARY KEY CLUSTERED (Id)
);

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


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

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

Tabellenvariablen und Transaktionen

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

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

    PRIMARY KEY CLUSTERED (Id)
);

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

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

SELECT * FROM @t;
GO

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

Verwendung von Tabellenvariablen

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

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

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

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

    SET NOCOUNT OFF;
GO

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

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

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

Tabellenvariablen sind nur im RAM persistent?

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

SET NOCOUNT ON;
GO

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

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

    PRIMARY KEY CLUSTERED (Id)
);

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

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

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

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

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

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

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

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

Wann mit Tabellenvariablen oder temporärer Tabelle arbeiten?

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

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

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

Herzlichen Dank für’s Lesen.

Weiterführende Weblinks

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

Andre Essing: Ich spreche auf dem SQL Saturday Rheinland

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

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

Sascha Dittmann: Performance Optimierung von SQL Server Azure VMs

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

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

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

Verschieben der tempdb-Datenbank

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

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

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

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

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

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

Buffer Pool Extensions

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

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

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

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

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

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

Vorsicht beim Starten / Neustart der VM

Einen kleinen Wermutstropfen haben diese Änderungen allerdings.

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

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

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

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

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

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

Start-Typ des SQL Servers aus Manuell umstellen

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

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

Startup-Skript hinzufügen (1)

Startup-Skript hinzufügen (2)

 



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

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

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

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

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

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

Virtuelle Maschine Erstellen

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

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

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

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

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

Aktualisieren des Betriebssystems

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

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

PuTTY

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

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

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

Passwortloses SSH

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

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

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

SSH Keys erzeugen

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

Passwortloses SSH

Network Time Protocol (NTP)

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

yum install ntp
service ntpd start
chkconfig ntpd on

NTP Dienst installieren

SELinux Deaktivieren

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

setenforce 0

SELinux abschalten (1)

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

vi /etc/selinux/config

SELinux abschalten (2)

PackageKit Deaktivieren

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

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

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

PackageKit abschalten

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

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

Transparent Huge Pages (THP) Deaktivieren

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

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

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

vi /etc/grub.conf

Transparent Huge Pages (THP) abschalten

Netzwerk Konfiguration

Bei der Netzwerk Konfiguration habe ich zwei Änderungen vorgenommen.

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

vi /etc/sysconfig/network

oder

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

IPv6 aktivieren

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

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

vi /etc/hosts

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

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

 

Festplatte hinzufügen

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

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

Neue VHD hinzufügen

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

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

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

Neue VHD in Linux bereitstellen

 

VM in ein Image umwandeln

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

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

waagent -deprovision
shutdown -h 0

Deprovision Linux VM

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

VM Herunterfahren

… und in ein Image umwandeln.

Image Erzeugen (1)

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

Wie geht’s weiter?

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

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

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

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

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

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

Storage Account & Cloud Service

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

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

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

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

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

Virtuelles Netzwerk

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

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

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

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

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

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

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

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

Wie geht’s weiter?

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

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

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

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