Andreas Wolter: Free Deadlock-Collector & -Parser, based on Extended Events session system_health / freier Deadlock-Sammler & -Parser basierend auf Erweiterten Ereignissen

Bernd Jungbluth: 4. SQL Server und .NET-Entwicklerkonferenz

snek4_klein.png

Auch dieses Jahr gibt es wieder interessante Vorträge zum SQL Server und zu .NET:

Connection – Session – Request: Ohne teure Tools den SQL Server überwachen
Statistiken: Wie plant SQL Server seine Ausführungspläne
beide Vorträge von Uwe Ricken

Datenunsicherheit
Vortrag von Elmar Bergmann

Data Tools in der Praxis
Vortrag von Bernd Jungbluth

Aktueller Web-Baukasten
Professionelles ALM mit Visual Studio Online
beide Vorträge von Rainer Stropek

Automatisierte Tests in .NET
Vortrag von Paul Rohorzka

plus ein weiterer Vortrag zum Thema .NET

Eine Besonderheit der SNEK liegt in den praxisorientierten Vorträgen.
Produktschwächen dürfen und sollen erwähnt wie auch diskutiert werden.

Eine weitere Besonderheit ist der Preis:
2 Tage Konferenz für 340 Euro inklusive MwSt.

Die Konferenz findet wie immer im Hotel Arvena Park in Nürnberg statt.
Mehr Informationen gibt es unter http://www.donkarl.com/snek/.

Ich freue mich jetzt schon auf die Vorträge und vor allem auf das Wiedersehen mit den vielen Stammgästen.

Christoph Muthmann: Rückblick auf die SQL Konferenz

Es ist zwar schon wieder ein paar Tage her, aber zwischendurch war einfach zu viel zu tun, um hier etwas zu posten. Kurz gefasst: Es hat wirklich Spaß gemacht und war toll organisiert!

Ganze Geschichte »

Bernd Jungbluth: SQLCMD und BCP liefern invalide XML-Dokumente

In einem Projekt sammele ich per SQLCMD Daten und speichere diese in XML-Dateien. Mit SQL Server 2005, 2008 und 2008 R2 funktioniert dies einwandfrei.
Ab SQL Server 2012 jedoch speichert SQLCMD nicht immer ein valides XML-Dokument.

Der Grund sind Zeilenumbrüche. Die XML-Datei enthält nach jedem 2034. Zeichen einen Zeilenumbruch.
Fällt auch nur einer der Zeilenumbrüche in einen Wert oder einen Tag, ist das XML-Dokument nicht mehr valide.

Um dieses Dilemma nachvollziehen zu können, beginnen wir mit einem Skript, dass eine Datenbank wie auch eine Beispieltabelle namens xmlKunden anlegt und diese mit 25.000 Datensätzen füllt.

– Datenbank anlegen
CREATE DATABASE xmlTest;
GO
USE
xmlTest;
GO
— Tabelle und Daten anlegen
SET NOCOUNT ON;
— Variablen zum Erstellen von Daten
DECLARE @intZaehler int = 0, @strZaehler nvarchar(19)
;
— Bereits bestehende Tabelle löschen
IF Object_ID(‘xmlKunden’) Is Not Null
BEGIN
       DROP TABLE dbo.xmlKunden;
END
– Tabelle erstellen
CREATE TABLE dbo.xmlKunden
(
       ID int IDENTITY(1,1),
       Matchcode nvarchar(8),
       Firma nvarchar(255),
       Strasse nvarchar(256),
       Plz nvarchar(8),
       Ort nvarchar(256)
);
– Tabelle mit 25.000 Datensätzen füllen
WHILE @intZaehler < 25000
BEGIN
       SET @intZaehler = @intZaehler + 1;
       SET @strZaehler = CAST(@intZaehler As nvarchar(19));
       INSERT INTO dbo.xmlKunden (Matchcode, Firma, Strasse, Plz, Ort)
            VALUES (‘KD’ + @strZaehler, ‘Firma’ + @strZaehler,
                   ‘Strasse’ + @strZaehler, ‘P-’ + @strZaehler, ‘Ort’ + @strZaehler);
END
GO

Der Inhalt der Beispieltabelle soll per SQLCMD als XML-Dokument exportiert werden. Hierzu benötigen wir folgendes Skript:

SET NOCOUNT ON;
:
XML ON
SELECT
CONVERT(nvarchar(23),Getdate(), 127) As Datum,
       (SELECT      ID, Matchcode, Firma, Strasse, Plz, Ort
        FROM         xmlTest.dbo.xmlKunden Kunde
        FOR XML AUTO, TYPE)
FOR XML RAW (‘Kunden’);

Die Datenermittlung per FOR XML liefert ein XML-Dokument, dass durch den Zusatz :XML ON als XML-Datenstrom ausgegeben wird.
Der Zusatz :XML ON funktioniert nur mit SQLCMD. Das SQL Server Management Studio quittiert die Verwendung von :XML ON mit einer Fehlermeldung.

Die Skriptdatei speichern wir als xmlSelectKunden.sql und starten dann in der Eingabeaufforderung den Datenexport mit folgendem Befehl:

SQLCMD -S <SqlServer> -i “D:\xmlSelectKunden.sql” -o “D:\Kunden.xml”

Das Ergebnis ist ein XML-Dokument mit Zeilenumbrüchen. Die Statuszeile zeigt den Zeilenumbruch an der 2034. Stelle.

Dieses Verhalten ist netterweise bei Microsoft Connect unter dem Eintrag http://connect.microsoft.com/SQLServer/feedback/details/786004/sqlcmd-with-xml-on-break-lines-on-large-output beschrieben.
Der Eintrag bezieht sich zwar nur auf den Export per SQLCMD mit der Anweisung :XML ON, das Verhalten gilt jedoch ebenso für den Export eines XML-Dokuments per BCP. Mehr zu BCP folgt weiter unten.

Microsoft Connect hat den Eintrag bereits geschlossen und als nicht lösbar gekennzeichnet.
Es wird lediglich auf die dort aufgeführten Workarounds hingewiesen. Zum heutigen Zeitpunkt sind dies nur zwei.

Ein Workaround empfiehlt die Installation von SQLCMD in der Version vom SQL Server 2008 R2 auf SQL Server ab der Version 2012.
Dies hätte ein inhomogenes System zur Folge, was wohl in den meisten Fällen vom Administrator nicht toleriert wird.

Der zweite Workaround empfiehlt, das Ergebnis der Datenermittlung zunächst in einer Variablen vom Datentyp nvarchar(max) zu speichern und anschließend den Wert der Variablen per SELECT auszugeben.

Ok, dann ändern wir halt das Skript …

SET NOCOUNT ON;
DECLARE @xml As nvarchar(max);
SET @xml = (SELECT CONVERT(nvarchar(23),Getdate(), 127) As Datum,
                 (SELECT  ID, Matchcode, Firma, Strasse, Plz, Ort
                    FROM   xmlTest.dbo.xmlKunden Kunde
                  FOR XML AUTO, TYPE)
           FOR XML RAW (‘Kunden’));
:
XML ON
SELECT
@Xml As Ausgabe;

… und führen es erneut aus.

SQLCMD -S <SqlServer> -i “D:\xmlSelectKunden.sql” -o “D:\Kunden.xml”

Und siehe da – die XML-Datei enthält nun keine unerwünschten Zeilenumbrüche.
Dafür ist sie nun auf 2.049 Zeichen begrenzt. Es bleibt also bei einem nicht validen XML-Dokument.

Vielleicht hilft ja eine Anpassung des Workarounds. Wie sieht das Ergebnis aus, wenn der Export ohne den Zusatz :XML ON erfolgt?
Schließlich geben wir aktuell per SELECT lediglich eine Zeichenfolge aus.

Das lässt sich schnell herausfinden: Die Zeile mit :XML ON auskommentieren, das Skript speichern und per SQLCMD ein weiteres Mal ausführen.

SET NOCOUNT ON;
DECLARE @xml As nvarchar(max);
SET @xml = (SELECT CONVERT(nvarchar(23),Getdate(), 127) As Datum,
                 (SELECT  ID, Matchcode, Firma, Strasse, Plz, Ort
                    FROM   xmlTest.dbo.xmlKunden Kunde
                  FOR XML AUTO, TYPE)
           FOR XML RAW (‘Kunden’));
–:XML ON
SELECT @Xml As Ausgabe;

SQLCMD -S <SqlServer> -i “D:\xmlSelectKunden.sql” -o “D:\Kunden.xml”

Mit diesem Skript liefert SQLCMD eine einfache Zeichenfolge. Das Ergebnis ist somit keine XML-Datei mehr, sondern eine Textdatei.
Mit einigen Einschränkungen, denn zum einen enthält die Ausgabe eine Kopfzeile und zum anderen ist die Breite der jeweiligen Spalten auf 256 Zeichen begrenzt.

Die Textdatei kann auch eine Informationsmeldung über einen Datenbankwechsel enthalten, wenn dieser im Skript mit dem USE-Befehl erfolgt.
Dies lässt sich vermeiden, wenn die Tabelle mit dem vollqualifizierten Namen bestehend aus Datenbank, Schema und Tabellenname angesprochen wird.

Die Ausgabe der Kopfzeile wie auch die Begrenzung der Ausgabe auf 256 Zeichen kann ebenfalls vermieden werden.
Hierfür gibt es die beiden folgenden Parameter:

-h-1     vermeidet die Ausgabe der Kopfzeile
-y0     erweitert die maximale Größe einer Spalte auf 1 MB

Die Grenze von 1 MB ist nicht fix. Sie lässt sich mit einer Zahl beliebig festlegen. Der Wert 0 steht hier für den maximalen Wert – und der liegt nun mal bei 1 MB.

Speichern wir dieses Ergebnis nun in einer Datei mit der Erweiterung xml, erhalten wir ein valides XML-Dokument – vorausgesetzt der Inhalt ist nicht größer als 1 MB.
Um dies zu testen, schränken wir die Datenermittlung per TOP-Klausel auf 10 Datensätze ein.

SET NOCOUNT ON;
DECLARE @xml As nvarchar(max);
SET @xml = (SELECT CONVERT(nvarchar(23),Getdate(), 127) As Datum,
                 (SELECT  TOP 10 ID, Matchcode, Firma, Strasse, Plz, Ort
                    FROM   xmlTest.dbo.xmlKunden Kunde
                  FOR XML AUTO, TYPE)
           FOR XML RAW (‘Kunden’));
–:XML ON
SELECT @Xml As Ausgabe;

Nachdem wir das Skript gespeichert haben, ergänzen wir den Aufruf von SQLCMD mit den Parametern und starten den Datenexport.

SQLCMD -S <SqlServer> -i “D:\xmlSelectKunden.sql” -o “D:\Kunden.xml” -h-1 -y0

Das Ergebnis ist ein valides XML-Dokument.

Entfernen wir im Skript die TOP-Klausel, erhalten wir ein Ergebnis von mehr als 1 MB.
Dann wird die Ausgabe einfach abgeschnitten – und schon haben wir wieder kein valides XML-Dokument.

Vielleicht liegt es ja am Datentyp der Variablen, die das Ergebnis der Datenermittlung speichert. Denn wir wollten ja eigentlich ein XML-Dokument und keine Zeichenfolge.
Ändern wir doch einfach mal den Datentyp der Variablen von nachvar(max) auf xml.

Das Ergebnis ist das gleiche. Die Ausgabe ist auf 1 MB begrenzt. Daran ändert sich auch nichts, wenn das Skript wieder mit dem Zusatz :XML ON ausgeführt wird.
Der Grund liegt in der maximal möglichen Breite einer Spalte.

Als Alternative zu SQLCMD ist vielleicht BCP eine Hilfe. Da sich mit BCP jedoch keine Skripte ausführen lassen, brauchen wir für die Datenermittlung eine Sicht, eine Gespeicherte Prozedur oder eine Tabellenwertfunktion. Versuchen wir es mit einer Gespeicherten Prozedur.

USE xmlTest;
GO
CREATE
PROC dbo.pExportKundenXml
AS
SET
NOCOUNT ON;
SELECT CONVERT(nvarchar(23),Getdate(), 127) As Datum,
       (SELECT      ID, Matchcode, Firma, Strasse, Plz, Ort
        FROM         xmlTest.dbo.xmlKunden Kunde
        FOR XML AUTO, TYPE)
FOR XML RAW (‘Kunden’);

In der Eingabeaufforderung starten wir dann den Datenexport per BCP mit diesen Parametern:

BCP xmlTest.dbo.pExportKundenXml queryout “D:\Kunden.xml” -S <SqlServer> -T -x –c

Das Ergebnis ist ernüchternd. Die XML-Datei enthält wieder die unerwünschten Zeilenumbrüche.

Naja, dann nutzen wir doch den Workaround vom SQLCMD und schreiben das Ergebnis zunächst in eine Variable vom Datentyp nvarchar(max), um diese per SELECT auszugeben.

USE xmlTest;
GO
ALTER
PROC dbo.pExportKundenXml
AS
SET
NOCOUNT ON;
DECLARE @xml As nvarchar(max);
SET @xml = (SELECT ‘Datum = ‘ + CONVERT(nvarchar(23),Getdate(), 127) As Datum,
                 (SELECT  ID, Matchcode, Firma, Strasse, Plz, Ort
                    FROM   xmlTest.dbo.xmlKunden Kunde
                  FOR XML AUTO, TYPE)
           FOR XML RAW (‘Kunden’));
SELECT @Xml As Ausgabe;

Starten wir also den Datenexport per BCP ein weiteres Mal.

BCP xmlTest.dbo.pExportKundenXml queryout “D:\Kunden.xml” -S <SqlServer> -T -x –c

Fantastisch – endlich haben wir eine XML-Datei ohne die lästigen Zeilenumbrüche und somit ein valides XML-Dokument.
Sogar die Ausgabe ist nicht auf 1 MB begrenzt.

Der für SQLCMD beschriebene Workaround liefert beim Datenexport per BCP das erwartete Ergebnis.
Dabei ist es gleich, ob die Variable vom Datentyp xml oder nvarchar(max) ist.

Der Nachteil dieser Variante liegt jedoch darin, dass wir an einer Gespeicherten Prozedur oder einer Tabellenwertfunktion mit mehreren Anweisungen nicht vorbeikommen. Nun kann es ja durchaus sein, dass das Erstellen eigener Datenbankobjekte nicht erwünscht ist. In diesem Fall ist der Datenexport per BCP nicht möglich. Also schauen wir nochmal nach einer Lösung per SQLCMD.

Bei den bisherigen Datenexporten per SQLCMD wird das XML-Dokument entweder als Datenstrom oder als Zeichenfolge in einer einzelnen Spalte ausgegeben. Beim Datenstrom per :XML ON erhalten wir die Zeilenumbrüche und bei der Ausgabe in einer Spalte ist diese auf 1 MB begrenzt.

Die Begrenzung der Ausgabe auf 1 MB gilt jedoch nur für eine Spalte einer Zeile.
Warum also das Ergebnis nicht auf mehrere Zeilen verteilen?

Schreiben wir doch einfach die einzelnen Datensätze vom Ergebnis nicht per FOR XML in ein XML-Dokument, sondern zeilenweise als Zeichenfolgen in eine Tabelle. Diese Datensätze ergänzen wir mit weiteren Datensätzen, die das Start- und Ende-Tag des XML-Dokuments enthalten. Somit ergibt der Inhalt der Tabelle das eigentliche XML-Dokument.

Das Ergebnis könnte jedoch Zeichen enthalten, die für ein XML-Dokument reserviert sind. Hierzu gehören neben dem Hochkomma und dem Anführungszeichen auch die Zeichen &, < und >. Um nicht mühsam diese Zeichen per REPLACE mit den Maskierungen wie ‘”‘ ersetzen zu müssen, nutzen wir einfach wieder FOR XML für die Datenermittlung. Auf diese Weise erhalten wir ein valides XML-Dokument.

Das XML-Dokument speichern wir in einer Variable vom Datentyp xml und lesen dann die einzelnen Zeilen per XQuery aus. Dabei konvertieren wir jede einzelne Zeile in eine Zeichenfolge. Hinzu kommt eine weitere Spalte namens Satzart, die den Wert ‘D’ für “Daten” erhält.

Die so aufbereiteten Zeilen schreiben wir per SELECT INTO in eine Tabelle.
SELECT INTO wird an dieser Stelle nicht ohne Grund verwendet: Es ist am schnellsten.

Anschließend ergänzen wir die erstellte Tabelle mit einer weiteren Spalte namens “EintragID”. Diese Spalte erhält die IDENTITY-Funktion und vergibt somit den einzelnen Datensätzen eine fortlaufende Nummer.

Nun fehlen noch die Einträge für die Start- und Ende-Tags des XML-Dokuments.
Das Start-Tag schreiben wir mit der Satzart “A” für “Anfang” in die Tabelle und das Ende-Tag mit der Satzart “E” für “Ende”.

Die Satzart dient lediglich zur Sortierung der Ausgabe, denn die folgt per ORDER BY nach den Spalten “Satzart” und “EintragID”.
Durch diese Sortierung sind die Start-Tags am Anfang (Satzart “A”), gefolgt von den Daten (Satzart “D”) und dem Ende-Tag (Satzart “E”).

SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

– XML-Variable
DECLARE @xmlErgebnis xml;

– Ergebnis als XML aufbereiten
SET @xmlErgebnis = (SELECT ID, Matchcode, Firma, Strasse, Plz, Ort
                                    FROM xmlTest.dbo.xmlKunden Kunde
                                    FOR XML AUTO, TYPE);

– Ergebnis Zeile für Zeile ergänzt mit Satzart in Tabelle speichern
SELECT CAST(‘D’ As char(1)) As Satzart,
              CAST(Zeile.Wert.query(‘.’) As nvarchar(max)) As Zeile
INTO
     #tabXML
FROM    @xmlErgebnis.nodes(‘/Kunde’) Zeile(Wert);

– Tabelle mit laufender Nummer erweitern
ALTER TABLE #tabXML ADD ZeilenNr int IDENTITY(1,1);

– Start-Tag einfügen
INSERT INTO #tabXml (Satzart, Zeile)
VALUES (‘A’, ‘<Kunden Datum=”‘ + CONVERT(nvarchar(19), GETDATE(), 127) + ‘”>’);

– Ende-Tag einfügen
INSERT INTO #tabXml (Satzart, Zeile) VALUES (‘E’, ‘</Kunden>’);

– Ausgabe der Daten - sortiert nach Satzart (A, D, E) und Zeilennummer
SELECT Zeile FROM #tabXML ORDER BY Satzart, ZeilenNr;

– Tabelle wieder löschen
DROP
TABLE #tabXML

Dieses Skript speichern wir unter der Bezeichnung xmlSelectKunden.sql.

Dann exportieren wir die Daten der Beispieltabelle per SQLCMD in eine Datei mit der Erweiterung xml.
Wobei wir hier wieder die Parameter -h-1 und -y0 verwenden, um die Ausgabe der Kopfzeile zu vermeiden und die maximale Spaltenbreite auf 1 MB festzulegen. Zusätzlich kommt nun noch der Parameter -u hinzu. Hiermit erhalten wir eine Unicode-Datei.

SQLCMD -S <SqlServer> -i “D:\xmlSelectKunden.sql” -o “D:\Kunden.xml” -h-1 -y0 -u

Die so erstellte Textdatei beinhaltet ein valides XML-Dokument und lässt sich durch die Erweiterung xml auch als solches verwenden.

Falk Krahl: Neues Servicepack für SQL Server 2014

Heute ist für den SQL Server 2014 ein neues Update erschienen. Es handelt sich dabei um das kumulative Update 06. Es kann unter folgendem Link angefordert werden.
Kumulatives Update 06 für SQL Server 2014

Marcel Franke: Recap SQL Server Conference 2015 – A Kickoff to new era

After 3 fantastic days at the SQL Server Conference 2015 in Darmstadt I would like to take the chance to take a short recap. First of all I would like to say thank you to our German PASS Community for organizing this great conference and to all the volunteers, sponsors and speakers that helped to make this happen. In 4 tracks and together with 42 international speakers we delivered for our conference visitors a great selection of sessions, half of them in English and with lot’s of MVPs on board.

Especially this year I had the feeling that there is a shift that is happing in the market. People are very interested in new technologies especially in the area of Cloud Computing and Data Analytics.  I personally believe that this year we will see a big movement in the BI business: http://nexxtjump.com/2014/07/22/a-call-to-all-data-engineers-and-bi-workers-2/

I was also happy to give a talk for our great community and visitors about “SAP HANA, Power Pivot, SQL Server – In Memory-Technologien im Vergleich”. You can find my slides on slideshare: http://www.slideshare.net/marcelfranke/in-memorytechnologien-im-vergleich-sql-server-konferenz-2015

 

So I personally believe that this year will be a great one for all of us with lot’s of new stuff to learn and great projects to come. In the meantime we can all remember some very special moments of this conference. Some of mine are shared below, the rest you can find on Flickr: https://www.flickr.com/photos/dirkhondong/collections/72157650641795156/. Thanks to our Paparazzi Dirk to keep and save these moments for us.
 

image

 

image

 

WP_20150203_18_51_15_Pro

 

image

 

image

 

image

 

Eingebetteter Bild-Link


Filed under: Conferences

Marcel Franke: Big Data = Big Fun – The worldwide first Microsoft carnival session

imageI think today was a very historic day. Ralph, de Bräd,  Kemperdick (Microsoft Germany), organized the worldwide first carnival session at Microsoft in Cologne. Together with some other Jecks (Hilmar, Christos, Tillman and me) we presented some funny sessions about Big Data and we had a lot of fun together with our guests and visitors. Thanks to Ralph and Microsoft for organizing this and to all the guests who joined. We all agreed that this year was only the small start of a tradition that will hopefully fill pavilions and arenas in the next years. And for the rest of you who couldn’t join here are some impressions.

In this sense…Kölle Alaaf!

WP_20150212_10_05_21_Pro

WP_20150212_14_57_52_Pro

WP_20150212_12_21_44_Pro

WP_20150212_12_28_59_Pro

WP_20150212_12_29_29_ProWP_20150212_18_38_09_ProWP_20150212_14_29_38_Selfie

WP_20150212_15_03_59_Pro


Filed under: Big Data, Conferences

Torsten Schuessler: Microsoft Cloud Platform Roadmap Site

Microsoft realized the need to provide better transparency with their new release of the Cloud Platform Roadmap Site.

There are roadmaps given for ...

  • Cloud Infrastructure: 
    • Microsoft Cloud Platform System Azure G-Series VM Sizes
    • Azure ExpressRoute Enhancements
    • Azure Multi-NIC Virtual Machines
    • Azure Network Security Groups
    • Azure Site Recovery Standalone
  • Enterprise mobility: 
    • Azure RemoteApp
    • Azure Active Directory Application Proxy
    • Microsoft Intune: November Service Update
    • Microsoft Intune: December Service Update
    • Microsoft Intune: February Service Update
  • Data management and analytics: 
    • Azure SQL Database Latest Update
    • Azure SQL Database New Service Tiers
    • Azure SQL Database Auditing
    • Azure Machine Learning Marketplace
    • HBase for Azure HDInsight
    • Cluster Customization for Azure HDInsight to include Spark, R, Solr, Giraph
    • Analytics Platform System Appliance Update 3
  • Application development:
    • Visual Studio Community 2013
    • Live Channels for Azure Media Services
    • Content Protection
    • Visual Studio Online: Multi Data Center Support
    • Visual Studio Online: Extensibility
  • Internet of Things: 
    • Microsoft Azure IoT Services

Enough stuff for us all :-)
I prefer - at this time - Azure SQL Database Auditing.

I wish you a nice day,
tosc

Torsten Schuessler

Dirk Hondong: Der kleine SQLKonferenz 2015 Rückblick

Wie doch die Zeit vergeht. Gerade erst noch in Darmstadt gewesen und nun ist es doch schon wieder eine Woche her, dass die SQLKonferenz zu Ende gegangen ist.

Ich war mal wieder der SQLPaparazzo (t), der sich hat in jeder Session kurz blicken lassen, um einige Momente der SQLKonferenz festzuhalten.

Auch wenn ich die Sessions nicht von Anfang bis Ende mitbekommen habe, so denke ich doch, dass wieder klasse Inhalte vermittelt wurden und die zahlreichen Besucher zufrieden waren.

Interessant fand ich die PreCon “Designing For Performance: Myths and Misunderstandings” von Karen Lopez und Thomas LaRock. Diese hatte tatsächlich Workshop-Charakter. Das Publikum wurde in “selbst-organisierende” Gruppen unterteilt und durfte sich im Laufe des Tages mit 3 “Labs” auseinandersetzten. Das war mal was Anderes. Die PreCons, die ich bisher schon so besucht habe in meinem DBA Leben, z.B. auf der SQLBits von Klaus Aschenbrenner, Adam Machanic oder Kalen Delaney oder die PreCon mit Bob Ward während der EuroPASS, waren ja “doch nur” Vorträge. Man hatte da irgendwie nichts in der Hand, außer Zettel und Stift. Das soll jetzt nicht heißen, dass diese PreCons schlecht waren. Aber wenn es zwischendurch die Möglichkeit der “hands-on” Erfahrung gibt, dann ist es doch irgendwie eine andere Nummer.

Allein schon, dass sich “bunt zusammengewürfelte” Arbeitsgruppen arrangieren mussten, war irgendwie spannend anzuschauen.

 

Da ich immer ein wenig über die Schultern der Anderen geschaut habe, kann ich für mich sagen, dass das Konzept aufgegangen ist. Zuerst war ich ein wenig skeptisch, gerade bei mehr als 50 Teilnehmern. Aber die Ergebnisse wurden anschließend als lockere Frage & Antwort Runde durchgegangen und von Karen und Thomas gab es dann Erklärungen dazu.

Hierzu kann ich nur sagen: Bitte mehr davon Smiley

Zu den übrigen Sessions muss ich eigentlich nicht viel sagen. Hochkarätige Sprecher und facettenreiche Themen. Dieser Mix muss auf jeden Fall beibehalten werden.

 

 

 

Besonders ist mir die Session von Cathrine Wilhelmsen (b|t) über das automatische Generieren von SSIS Paketen mit BIML und BIMLScript in Erinnerung geblieben. Ich denke, dass muss ich mir echt mal genauer anschauen.

 

 

 

Ansonsten kann ich nur noch sagen, dass es wieder eine rundum gelungene Veranstaltung war, wo man auch viele Bekannte wieder getroffen hat und gerade am Abend auf der kleinen Party noch ein wenig zusammen sitzen konnte.

 

Danke an die Organisatoren für ein weiteres, tolles #sqlpass_de Event.

 

Sämtliche Bilder der Konferenz finden sich im Übrigen hier:

SQLKonferenz 2015 Darmstadt


Uwe Ricken: Initialgröße von Protokolldatei in Verbindung mit SHRINKFILE

Einen Tag nach der SQL Konferenz in Darmstadt habe ich Zeit gehabt, meine Emails zu bearbeiten. Unter den vielen Emails war eine interessante Frage eines Teilnehmers des SQLSaturday in Slowenien, die sich mit der Größe der Protokolldatei einer Datenbank beschäftigte.

Eine Datenbank (einfache Wiederherstellung) besitzt eine Protokolldatei mit einer Größe von 76 GB. Der DBA möchte das Protokollmedium auf eine moderate Größe von 100 MB verkleinern. Trotz mehrerer Versuche lies sich die Protokolldatei auf lediglich 1.250 MB verkleinern. Warum das Protokollmedium sich nicht weiter verkleinern lies, beschreibt dieser Artikel.

Hinweis

Dieser Artikel beschreibt nicht die generelle Funktionsweise einer Transaktionsprotokolldatei sondern setzt voraus, dass der Leser die Grundlagen bereits kennt. Ein sehr gutes Dokument für den Einstieg stellt TRIVADIS bereit: “Handling der Transaction Log Files im MS SQL Server

Transaktionsprotokoll

Die Transaktionsprotokolldatei einer Datenbank ist immer wieder ein Problem im täglichen Umfeld eines DBA. BITTE DIE EINZAHL BEACHTEN – es sollte immer nur eine Protokolldatei vorhanden sein; Microsoft SQL Server verarbeitet Transaktionen immer seriell!

Wenn ein Workload zu groß wird oder aber eine Transaktion nicht in Zwischenschritten abgearbeitet werden kann (z. B. bei ETL-Prozessen), dann wird die Protokolldatei schnell sehr groß und konsumiert einen nicht erheblichen Anteil des Storages. Selbst bei einer Datenbank im Wiederherstellungsmodus SIMPLE kann das Protokoll sehr stark wachsen, wenn auch nur eine einzelne Transaktion ein hohes Transaktionsvolumen produziert.

Da viele erfahrene DBA in der Regel bereits im Vorfeld wissen, dass – insbesondere bei der Erstinstallation – für die Importe große Transaktionsvolumen generiert werden, legt man die Datenbanken mit sehr großen Transaktionsprotokolldateien an. Dieser Umstand kann aber bei “Normalbetrieb” zu einem Problem werden wie das folgende Beispiel verdeutlicht.

Zunächst wird eine Datenbank mit einer Initialgröße von 10 GB für das Protokoll erstellt.

Der nachfolgende Code erstellt eine einfache Datenbank, dessen Transaktionsprotokoll initial sehr groß gewählt wird. Je nach Initialgröße / Wachstumsgröße werden zwischen 4 und 16 sogenannter VLF (Virtual Log Files) innerhalb einer Transaktionsprotokolldatei erstellt (siehe Kimberly Tripp):

  • Größe / Vergrößerung <= 64 MB: 4 VLF
  • Größe / Vergrößerung >64 MB <= 1024 MB: 8 VLF
  • Größe / Vergrößerung > 1.024 MB: 16 VLF

Hinweis: Dieser Algorithmus hat sich mit Microsoft SQL Server 2014 geändert (siehe Paul Randal)!

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

Sobald die Datenbank angelegt wurde, werden die Informationen über die erstellten VLF mit dem folgenden Befehl angezeigt:



USE demo_db;
GO
 
DBCC LOGINFO();

Das Ergebnis präsentiert sich wie erwartet – es sind insgesamt 16 VLF, die sich über die Transaktionsprotokolldatei erstrecken.


ResultSet_01


Wie man sehr schön erkennen kann, erstreckt sich die Verteilung der VLF sehr gleichmäßig auf insgesamt 16 Einheiten. Jede Einheit ist ~671.023.104 Bytes (0,625 GB) groß. Soll das Transaktionsprotokoll verkleinert werden, wird mit dem nachfolgenden Befehl, versucht, eine Initialgröße von 100 MB zu erreichen:



USE demo_db;
GO
 
DBCC SHRINKFILE ('demo_log', 100);
GO

Das Ergebnis der Aktion sieht nicht sehr erfolgversprechend aus, da – augenscheinlich – die Größe nicht auf 1 MB reduziert wurde sondern auf 1,25 GB!


ResultSet_02


Die obigen Angaben stammen aus der Aktion DBCC SHRINKFILE. Die Größenangabe bezieht sich immer auf Datenseiten (1 Datenseite = 8.192 Bytes). 163.825 Datenseiten entsprechen 1,250 GB.


Die Ursache für dieses Verhalten versteckt sich in der Größe der VLF. Auf Grund der immensen Startgröße des Transaktionsprotokolls wurde der Initialwert gleichmäßig auf 16 VLF aufgeteilt. Beim Verkleinern verbleiben aber IMMER mindestens zwei VLF in der Protokolldatei. Schaut man sich den Inhalt des Transaktionsprotokolls nach der Verkleinerung wieder an, kann man das sehr deutlich erkennen:



DBCC LOGINFO();
GO
ResultSet_03

Zusammenfassung


Die Initialgröße des Transaktionsprotokolls sollte immer sehr gut überlegt sein. Abhängig vom Workload der Applikation kann es sicherlich sinnvoll sein, dass das Transaktionsprotokoll bereits vor der Verwendung ausreichend dimensioniert ist/wird. Wird jedoch ausschließlich für das Beladen einer neuen Datenbank das Transaktionsprotokoll initial mit großen Startwerten initialisiert, ist unter Umständen eine ausreichende Verkleinerung nicht mehr möglich. In diesem Fall bleibt nur die Möglichkeit, die Datenbank abzuhängen, die Transaktionsprotokolldatei zu löschen und die Datenbank erneut OHNE Transaktionsprotokoll wieder anzuhängen. Dabei wird dann eine neue – kleinere - Datei für das Transaktionsprotokoll erzeugt.


Links


Das Thema “Transaktionsprotokoll” ist immer wieder eine Herausforderung für DBA. Nachfolgend ein paar – aus meiner Sicht – sehr interessante Links zu diesem komplexen Thema:



Herzlichen Dank fürs Lesen

Sascha Lorenz: Data Vault in der Praxis Vortrag von der SQL Server Konferenz 2015 in Darmstadt

Hier meine Folien zum Data Vault Vortrag.

Vielen Dank an die vielen Teilnehmer!

Bernd Jungbluth: Seminar - Datenbankentwicklung mit SQL Server

Heute möchte mein neues Seminar “Datenbankentwicklung mit SQL Server” vorstellen.

Inhalt dieses Seminars ist die Datenbankmodellierung wie auch das Erstellen und Verwenden von Gespeicherten Prozeduren, Sichten, Funktionen und Triggern.

Zur Datenbankentwicklung gehört neben dem Modellieren der Tabellen auch das Abbilden von Geschäftslogik. Zum Teil erfolgt dies bereits mit den Tabellen und deren Regeln zur Datenintegrität. Komplexere Logik lässt sich im SQL Server mit T-SQL in Gespeicherten Prozeduren, Sichten, Funktionen und Triggern realisieren.

Das Seminar findet am 24. März 2015 im Hotel „Best Western Premier Bellevue Rheinhotel“ in Boppard am Rhein statt.

Preis: 375 Euro zzgl. MwSt., inklusive Mittagessen und Getränke

Die Teilnehmerzahl ist auf 8 Personen begrenzt. Aktuell gibt es noch 4 freie Plätze.
Der Anmeldeschluss ist am 20. Februar 2015.

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

Falk Krahl: Neues Update für SQL Server 2012 SP1

Auch für den SQL Server 2012 mit Servicepack 1 ist ein neues Update erschienen. Es handelt sich um das kumulative Update 14. Es kann unter folgendem Link angefordert werden:
SQL Server 2012 SP1 CU14

Torsten Schuessler: #CU package 4 for SQL Server 2012 SP2

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

Cumulative update package 4 for SQL Server 2012 SP2

I wish you a nice day,
tosc

 

Torsten Schuessler

Falk Krahl: Neues Update für SQL Server 2012 SP2

Soeben ist für den SQL Server 2012 mit Servicepack 2 ein neues Update erschienen. Es handelt sich um das kumulative Update 04. Es kann unter folgendem Link angefordert werden:
SQL Server 2012 SP2 CU04

Christoph Muthmann: Henry Habermacher

Leider haben wir wieder ein hochgeschätztes Mitglied der SQL Server Community verloren. Vor einem Monat ist der Access-MVP Henry Habermacher verstorben.

Ganze Geschichte »

Christoph Muthmann: SP4 auf SQL Server 2005 Express anwenden

Warum ich noch über solche alten Versionen schreibe, wird sich der ein oder andere fragen. Ganz einfach, dies ist eine Voraussetzung für den Upgrade auf SQL Server 2014 Express.

Ganze Geschichte »

Uwe Ricken: SQL Server Konferenz in Darmstadt

Liebe SQL Gemeinde; es ist mal wieder soweit. Auch 2015 wird die im letzten Jahr ins Leben gerufene Konferenz “SQL Server Konferenz” wieder in Darmstadt ihre Tore öffnen. Ähnlich wie in meinem Artikel über den PASS Summit 2014 möchte ich mit diesem Artikel die Vor- und Nachteile eines Besuchs der SQL Server Konferenz aus meiner persönlichen Sicht beschreiben.

Was ist die SQL Server Konferenz?

Die SQL Server Konferenz fand im Jahr 2014 zum ersten Mal im darmstadtium mitten im Herzen von Darmstadt statt. Die SQL Server Konferenz 2014 war ein so großer Erfolg, dass sich die PASS Deutschland e. V. in Zusammenarbeit mit Microsoft Deutschland als Organisatoren erneut für die Durchführung der SQL Konferenz für das Jahr 2015 entschlossen hat.

SQLKonferenz

Deutsche und internationale Experten präsentieren, diskutieren und erläutern Themen wie SQL Server vNext, Self-Service BI, Hochverfügbarkeit, Big Data, Continuous Integration, In-Memory Computing, Data Stewardship, Data Vault und Modern Data Warehouse, Machine Learning, das Internet der Dinge, Enterprise Information Management und viele weitere spannende Themen. In vier parallelen Tracks werden alle Neuheiten rund um den Microsoft SQL Server und die Microsoft Datenplattform von erfahrenen und bekannten Sprechern aus dem In- und Ausland angeboten.

Die Location

Die SQL Server Konferenz findet auf mehr als 18.000 qm im darmstadtium im Herzen von Darmstadt statt und ist sowohl mit öffentlichen als auch individuellen Verkehrsmitteln schnell und bequem zu erreichen. Ressourcenschonende Nachhaltigkeit und eine in Deutschland einmalige IT Infrastruktur sind neben der fantastischen Lage mitten im Rhein-Main-Gebiet Merkmale, die für das darmstadtium zählen.

Die Sprecher

So wichtig für jede Konferenz die Besucher, deren Interessen und die Themen sind; es müssen Leute da sein, die den kompliziertesten Sachverhalt in einfache und verständliche Sprache übersetzen – die Sprecher. Jede Konferenz lebt von ihren Sprechern. Ich kenne zwar nicht alle Sprecher persönlich ABER ... wenn man sich mal die Liste der Sprecher hier anschaut, wird man schnell erkennen, dass man es mit hochgradig spezialisierten Experten zu tun hat, die ihr Metier in der Tiefe verstehen. Neben nationalen Sprechern sind dieses Mal sehr viele internationale Sprecher auf der SQL Server Konferenz vertreten, die auf der ganzen Welt für ihre Kenntnisse und ihr Engagement für die Community bekannt sind. Bevor ich auf einige Sprecher im Detail eingehe, möchte ich ausdrücklich darauf hinweisen, dass die Sprecher der Hauptkonferenz sich alle KOSTENLOS engagieren. Anfahrt, Hotel, Vorbereitungen – es gibt keinen Cent für die Tätigkeiten. Die Sprecher machen es aus Passion! Ich möchte hier nur einige wenige hervorheben, die ich sehr interessant finde (das soll aber keine Wertung darstellen!).

Kalen Delaney

Kalen Delaney dürfte allen DBAs durch ihre “Bibel” SQL Server Internals bekannt sein.  Ich hatte das Vergüngen, Kalen im Jahre 2013 auf der SQLRally in Stockholm persönlich kennen zu lernen. Ich kann jedem nur empfehlen, ruhig mal ein paar Worte mit ihr zu wechseln. Dass Ihr – neben Paul Randal – die Storage Engine bestens bekannt ist, dürfte jeder Leser ihrer Bücher wissen. Ich habe mich mit ihren Büchern auf meine MCM Prüfungen vorbereitet.

Thomas LaRock

Thomas war bereits letztes Jahr auf der SQL Server Konferenz und hat wohl eindrucksvoll bewiesen, dass ihm Jägermeister nicht unbekannt ist. Scherz beiseite; Thomas ist der Präsident der PASS und ebenfalls einer von ca. 200 MCM weltweit. Ich habe ihn persönlich auf der SQL Server Konferenz im letzten Jahr kennengelernt; wie Kalen Delaney ein sehr erfahrener SQL Server Experte, der es mag und versteht, sein Wissen über Microsoft SQL Server in die Community zu tragen.

Steve Jones

Wer kennt nicht DIE Anlaufstelle für SQL Server Themen im Internet: http://www.sqlservercentral.com ist für jeden DBA / BI Experten immer wieder eine Anlaufstelle, wenn es darum geht, Probleme zu lösen. Es gibt eigentlich nichts, was dort nicht zu finden ist. Steve Jones ist der Betreiber der Webseite, die mittlerweile zu Red Gate gehört. Steve habe ich bei einem Vortrag in Seattle auf dem PASS Summit 2014 gehört und gesehen. Fantastisch!

Régis Baccaro

Régis Baccaro kenne ich von vielen SQL Saturday Events in Europa, die ich in 2014 als Sprecher besucht habe. Es gibt eigentlich keinen SQL Saturday, auf dem Régis nicht schon eindrucksvoll sein Wissen in Bezug auf BI-Themen von Microsoft SQL Server unter Beweis gestellt hat. Ein Franzose, der in Dänemark lebt und mit mir eine Passion teilt – gutes Essen.

Boris Hristov

Boris Hristov ist für mich das Paradebeispiel für Engagement für die Community. Boris kommt aus Bulgarien. Über seine Hangouts bin ich mit Boris zum ersten Mal in Kontakt gekommen; seitdem verbindet uns – wie übrigens alle Mitglieder von #sqlcommunity – eine Freundschaft, die – wie bei Régis auch, immer wieder “refreshed” wird, wenn wir uns auf den zahlreichen SQL Saturday Veranstaltungen treffen. Boris Hristov scheut keine Kosten, um sein exzellentes Wissen in Europa in die Community zu tragen.

Oliver Engels / Tillmann Eitelberg

Jedes PASS Mitglied in Deutschland kennt natürlich unseren Vorstand. Mich verbindet mit Oliver und Tillmann eine Freundschaft, die ich sehr zu schätzen weis. Den beiden macht so leicht niemand was in Sachen BI und SSIS vor. Obwohl ich nur einmal in einer Session der Beiden gesessen bin, war ich dennoch begeistert von den Möglichkeiten von Power BI – und das. obwohl ich kein Freund von “Clicky Bunti” (Insider) bin.

Marcel Franke

Marcel Franke habe ich das erste Mal in Stockholm auf der SQL Rally kennen gelernt. Echt beeindruckend, was Marcel in Sachen BI, “Big Data” und “Data Analytics” drauf hat – sicherlich ein sehenswerter Vortrag für alle Besucher, die viel mit diesen Themen in Berührung kommen.

Patrick Heyde

Seit dem SQL Server Bootcamp im Dezember 2014 ist Patrick für mich nur noch “Mr. Azure”. Patrick kennt als Mitarbeiter von Microsoft die Azure Welt, wie kein Zweiter. Seine Vorträge gefallen mir vor allen Dingen deswegen, da Patrick es in seiner ruhigen Art sehr gut versteht, die Komplexität von Azure für alle Zuhörer verständlich zu vermitteln.

Niko Neugebauer

Niko Neugebauer ist für mich DER Experte für Themen rund um Columnstore Indexes. Es gibt eigentlich nichts, was Niko zu diesem Thema nicht kennt. Sein Wissen ist phänomenal und mehr als 40 Blogeinträge auf seinem Blog beschäftigen sich ausschließlich mit dem Thema Columnstore Indexe; zu lesen unter http://www.nikoport.com. Ein Besuch einer Session von Niko kann ich nur empfehlen.

Natürlich sind noch viele viele andere Sprecher “am Start”, die sich in Europa für die Community engagieren. Sei es Allen Mitchel (Einer der vielen Organisatoren von http://www.sqlbits.com), Milos Radivojevic, Cathrine Wilhelmsen, ...). Ein Besuch der SQL Server Konferenz ist sicherlich lohnenswert.

Die Themen

Bei der Auswahl der Themen haben sich die Organisatoren es sich sicherlich nicht leicht gemacht. Ich weiß nicht, wie viele Sessions eingereicht wurden – aber ich bin davon überzeugt, dass es weitaus mehr waren, als in den drei Tagen vorgetragen werden können. Ich bin sicher, dass unter den vielen Themen zu

  • Administration (DBA),
  • Business Intelligence (BI),
  • Applikationsentwicklung (DEV)
  • Big Data & Information Management (BD)

für jedes Interessensgebiet was dabei ist. Ich bin gespannt auf die Neuigkeiten im Bereich BI und freue mich auf die interessanten Sessions.

Warum zur SQL Server Konferenz?

Die SQL Server Konferenz ist eine Veranstaltung in Deutschland, die es in dieser Form so nicht gibt. In drei Tagen (am ersten Tag werden PreCons veranstaltet) werden viele Themen rund um den Microsoft SQL Server behandelt. Wer täglich mit Microsoft SQL Server in Themen rund um DBA, DEV, BI involviert ist, sollte sich die Zeit nehmen, die SQL Server Konferenz im darmstadtium in Darmstadt zu besuchen.

Ein – aus meiner Sicht – ganz wichtiges Thema JEDER Konferenz ist das Networking. Als ich im Jahre 2013 mit dem Besuch von Konferenzen begonnen habe, war ich natürlich noch etwas “schüchtern” in Bezug auf “große Namen”. Nie habe ich so falsch gelegen, wie mit der These der arroganten “Superstars”. Alles sehr nette Leute, die es sehr freut, wenn man sich mit Ihnen über UNSER Lieblingsthema Microsoft SQL Server austauscht. Aber man darf sich auch sehr gerne über andere Dinge unterhalten.

Ich habe über die Konferenzen viele neue Menschen getroffen; einige davon bezeichne ich als Freunde. Das Networking gibt die Möglichkeit, auch abseits von besuchten Sessions mit den Teilnehmern über das eine oder andere Problem zu sprechen – erstaunlich, wie schnell man manchmal eine Lösung zu einem Problem findet, über das man schon mehrere Tage gebrütet hat.

Kosten

Bei “Kosten” hört der Spaß in der Regel auf. Die Kosten für die SQL Server Konferenz halte ich persönlich für angemessen. Für die Teilnahme an der SQL Server Konferenz gibt es drei Kostenmodelle (Preise EXKLUSIVE MwSt.).

  • Besuch der PreCon: 349,00 €
  • Hauptkonferenz vom 04.02.2015 bis 05.02.2015: 699,00 €
  • Komplettpaket (PreCon und Hauptkonferenz): 949,00 €

Hört sich erst mal teuer an; ist es aber aus meiner Sicht für die Leistungen nicht. Als Mitglied in der PASS e.V. (kostenlos) gibt es noch einen Rabatt. Einen gesonderten Rabatt gibt es, wenn mehr als 2 Personen eines Unternehmens an der SQL Server Konferenz teilnehmen möchten. Weiter Informationen zu den Rabatten sind über die Emailadresse info@event-team.com zu erfahren.

Aussteller

Die IT ist im Wandel und das eigentlich stetig. Es ist auch für den SQL Experten wichtig, dass er “auf der Höhe der Zeit” bleibt. Nicht nur die neuen Versionen von Microsoft SQL Server sollten im Fokus eines jeden Besuchers stehen sondern auch, was sich auf dem Markt der Tools und der Hardware tut. Allein die Änderungen im Bereich Storage in den letzten Jahren sind gewaltig. Wie monitore ich meine SQL Server? Welche Hardware ist für meine SQL Server Anwendungen die richtige Wahl? All diese Fragen können in persönlichen Gesprächen mit den Ausstellern in Ruhe besprochen werden. Ein Besuch auf den Ausstellungsflächen der Sponsoren ist immer sinnvoll – viel Spaß beim “Stöbern”.

Was man verbessern könnte

Tatsächlich gefällt mir die SQL Server Konferenz so, wie sie ist, SEHR GUT. Lediglich ein Punkt ist aus meiner Sicht ein “must have” – Videoaufnahmen aller Sessions! Mir persönlich fällt es schwer, in manchen Slots einen “Favoriten” auszuwählen. Es sind alles exzellente Themen, die in den einzelnen Sessions behandelt werden aber ich kann mich nur für eine entscheiden.

Wie gerne würde ich mir die Session von Andreas Wolter über “In Memory OLTP” anhören; schade nur, dass zeitgleich meine eigene Session läuft. Ich glaube, dass es vielen Besuchern so geht, wenn mehrere – für den Einzelnen – interessante Themen zeitgleich behandelt werden.

Wenn es sehr komplexe Themen sind (z. B. der Vortrag von Niko Neugebauer), dann freut man sich, dass man die Session nachträglich noch einmal in Ruhe vor dem eigenen Computer anschauen kann und die Ruhe und die Zeit hat, die gezeigten Beispiele am heimischen Computer noch einmal nachzuspielen. Ein nicht zu unterschätzender Vorteil!

Ich habe auf dem PASS Summit 2014 gesehen, wie professionell dort JEDE Session aufgenommen wurde. Jeder Teilnehmer des PASS Summit hat so die Möglichkeit, nachträglich die für ihn interessanten Sessions noch einmal anzuschauen. Sollte jemand Interesse an dem Material haben, der nicht auf dem SUMMIT war, so hat er die Möglichkeit, gegen einen kleinen Obolus einen USB-Stick mit dem ganzen Material zu erwerben. Ich glaube, das wäre auch ein sehr gutes Modell für die SQL Server Konferenz – aber das ist Jammern auf sehr hohem Niveau. Es ist ja auch erst die zweite Konferenz dieser Art – es muss jedes Jahr eine kleine Verbesserung geben...

Mein Thema

Ich selbst bin ebenfalls mit einer Session auf der SQL Server Konferenz vertreten. Ich arbeite als SQL Server Architekt und SQL Server Berater in vielen Unternehmen in Deutschland und Europa. Bei den vielen hundert Einzeleinsätzen habe ich es recht häufig mit Performance-Problemen zu tun. Nicht selten sind es Architekturprobleme aber über 40% meiner Einsätze resultieren in dem Ergebnis, dass Indexe nicht richtig genutzt werden oder aber Indexe grundsätzlich fehlen. Mein Thema behandelt genau diese Szenarien aus den vielen Kundeneinsätzen, über die ich auch teilweise schon geblogged habe. “Indexing - alltägliche Performanceprobleme und Lösungen” wird mein Thema sein. Ich werde in 60 Minuten 6 – 10 gängige Probleme und deren Lösungen aufzeigen. Ich verspreche, dass es nicht langweilig wird, da die Session zu fast 90% mit Demos belegt ist. Es versteht sich von selbst, dass ich für jeden Interessierten sowohl vor als auch nach meiner Session sehr gerne für weitere Fragen rund um Microsoft SQL Server zur Verfügung stehe.

Ich freue mich auf einen regen Gedanken- und Wissensaustausch mit allen Besuchern der SQL Server Konferenz im darmstadtium in Darmstadt und wünsche allen Lesern und Besuchern viel Spaß!

Nachtrag

Mit “unserem” Vorsitzenden der PASS e. V. – Oliver Engels - habe ich auf dem SQL Boot Camp 2014 in Seeheim-Jugenheim ein kurzes Gespräch über die SQL Server Konferenz generell und im Speziellen zu meinem Thema geführt. Vielleicht macht das Gespräch ja Lust auf mehr.

Ich freue mich auf die SQL Server Konferenz! Ich freue mich sehr auf die Sprecher und ihre Themen aber am meisten freue ich mich auf die Fachbesucher und SQL Server Experten, mit denen ich über mein Lieblingsprodukt fachsimpeln kann. Es ist schön, Teil einer so interessanten und vielfältigen Community zu sein. Jede Konferenz zu Microsoft SQL Server in Deutschland ist ein Gewinn – ich werde alles dazu beitragen, dass die SQL Server Konferenz im Februar ein Gewinn für die Community wird. Ich hoffe sehr, dass auch die Besucher der Konferenz die SQL Server Konferenz wert schätzen und eine rege Teilnahme zu verzeichnen ist.

Bis zum 03.02.2015 in Darmstadt im darmstadtium auf der SQL Server Konferenz.

Herzlichen Dank fürs Lesen!

Uwe Ricken: Clustered Key – variable oder feste Länge

In LinkedIn wurde eine interessante Frage diskutiert, die in ihrem Ursprung zwar nicht unmittelbar mit dem Titel dieses Artikels zusammenhängt, mich aber auf Grund der abgebildeten Datenstruktur zu der Fragestellung dieses Artikels anregte. Dieser Artikel beschreibt, warum ein Clustered Index nach Möglichkeit immer einen Datentypen mit fester Länge besitzen sollte.

Der Clustered Key ist das Ordnungskriterium für einen Clustered Index. Der Schlüssel kann aus einem oder mehreren Attributen einer Tabelle bestehen. Bei der Definition des Index können bis zu 16 Spalten in einem einzigen zusammengesetzten Indexschlüssel kombiniert werden. Die maximal zulässige Größe der Werte des zusammengesetzten Index beträgt 900 Byte. Die Datentypen ntext, text, varchar(max), nvarchar(max), varbinary(max), xml oder image dürfen nicht als Schlüsselspalten für einen Index angegeben werden.

Struktur eines Datensatzes

Um zu verstehen, welche Nachteile ein Index mit variabler Datenlänge besitzt, muss man wissen, wie ein Datensatz in Microsoft SQL Server “strukturiert” ist. Der nachfolgende Code erstellt eine Tabelle mit einem Clustered Index auf “Customer_No”.

IF OBJECT_ID('dbo.Customer', 'U') IS NOT NULL
    DROP TABLE dbo.Customer;
    GO
 
CREATE TABLE dbo.Customer
(
    Customer_No      CHAR(10)    NOT NULL,
    Customer_Name    CHAR(1000)  NOT NULL,
 
    CONSTRAINT pk_Customer_No PRIMARY KEY CLUSTERED (Customer_No)
);
GO

Die Tabelle wird anschließend mit 1.000 Testdatensätzen für weitere Analysen der Datenstruktur befüllt. Um die Struktur eines Datensatzes zu analysieren, werden Informationen zur gespeicherten Position benötigt.



SELECT sys.fn_PhysLocFormatter(%%physloc%%), * FROM dbo.Customer;
ROWSET_01

Auf Datenseite 156 werden sieben Datensätze gespeichert. Um die Datenseite – und damit die Struktur der Datensätze – einzusehen, wird mit DBCC PAGE der Inhalt der Datenseite untersucht. Wichtig bei der Verwendung von DBCC PAGE ist die vorherige Aktivierung des Traceflags 3604, um die Ausgabe vom Fehlerprotokoll in SQL Server Management Studio umzuleiten.



DBCC TRACEON (3604);
DBCC PAGE ('demo_db', 1, 156, 3);
DBCC_PAGE_01

In der Abbildung wird der erste Datensatz der Datenseite angezeigt. Der blau gerahmte Bereich repräsentiert die tatsächlich auf der Datenseite gespeicherten Informationen. Es ist erkennbar, dass der Datensatz eine Länge von 1.017 Bytes besitzt; berechnet man jedoch die Länge des Datensatzes basierend auf der Tabellendefinition, wird man bemerken, dass die Länge eigentlich nur 1.010 Bytes sein müssten. Die zusätzlichen Bytes werden für die Definition der Metadaten eines Datensatzes benötigt. Die Anzahl der zusätzlichen Informationen (Overhead) ist von den Datentypen einer Tabelle abhängig.


Datensatz-Metadaten


Die ersten zwei Bytes (0x10 00) speichern Informationen über die Eigenschaften eines Datensatzes. Da es sich um einen “gewöhnlichen” Datensatz handelt, spricht man von einem “PRIMARY RECORD”. Die nächsten zwei Bytes (0xf6 03) bestimmen das Offset zur Information über die Anzahl der Spalten in einem Datensatz. Zwischen dem Header (4 Bytes) und dem Offset befinden sich die Daten aller Attribute mit festen Datentypen.


Fixed Length Bereich


Der Hexadezimalwert 0xf6 03 ergibt umgerechnet den Dezimalwert 1.014. 4 Bytes für den Header + 10 Bytes für das Attribut [Customer_No] + 1.000 Bytes für das Attribut [Customer_Name] ergeben den Wert von 1.014 Bytes.


DBCC_PAGE_02


Die obige Abbildung zeigt den Datenbereich ab dem Byte 1.014 (rot).


Anzahl der Attribute (Spalten)


Die nächsten zwei Bytes (0x02 00) geben Aufschluss über die Anzahl der Spalten.


NULL-Bitmap


Nach den Informationen über die Struktur des Datensatzes folgen die Informationen über über Attribute, die NULL Werte enthalten dürfen (NULL Bitmap). Hierbei gilt, dass pro 8 Attribute einer Tabelle jeweils 1 Byte belegt wird. Erlaubt eine Spalte einen NULL Wert so wird das entsprechende Bit gesetzt. Da für das obige Beispiel kein Attribut einen NULL Wert zulässt, ist dieser Wert 0 (0x00).


Variable Length Bereich


Das Ende der Datensatzstruktur ist der Datenbereich für Attribute mit variabler Länge. Spalten mit variabler Länge sind mit einem deutlich höheren Aufwand von Microsoft SQL Server zu verwalten; ein Datensatz wird – bedingt durch die zusätzlichen Informationen – deutlich größer (siehe nächstes Beispiel). Für JEDES Attribut mit einem variablen Datentypen werden zwei Bytes für das Offset gespeichert, an dem der Wert für das Attribut endet. Erst zum Schluss werden die Daten selbst gespeichert. Das obige Beispiel besitzt KEINE Attribute mit variablen Datentypen; Informationen über variable Datentypen müssen nicht gespeichert werden.


Clustered Index mit variablem Datentyp


Um die Datenstruktur eines Clustered Index mit variabler Datenmenge zu untersuchen, werden die Daten der bestehenden Tabelle [dbo].[Customer] in eine neue Tabelle übertragen.



IF OBJECT_ID('dbo.Customer_Variable', 'U') IS NOT NULL
    DROP TABLE dbo.Customer_Variable;
    GO
 
CREATE TABLE dbo.Customer_Variable
(
    Customer_No    VARCHAR(10)  NOT NULL,
    Customer_Name  CHAR(1000)   NOT NULL,
 
    CONSTRAINT pk_Customer_Variable_Customer_No PRIMARY KEY CLUSTERED (Customer_No)
);
GO
 
-- Übertragung aller Daten aus dbo.Customer
INSERT INTO dbo.Customer_Variable WITH (TABLOCK)
SELECT * FROM dbo.Customer;
GO

Mit der folgenden Abfrage werden aus jeder Tabelle der jeweils erste Datensatz ausgegeben. Zusätzlich werden Informationen zur Datenseite ausgegeben, auf der die Datensätze gespeichert wurden.



SELECT TOP 1 'Fixed length' AS ClusterType, sys.fn_PhysLocFormatter(%%physloc%%), * FROM dbo.Customer
UNION ALL
SELECT TOP 1 'Variable length' AS ClusterType, sys.fn_PhysLocFormatter(%%physloc%%), * FROM dbo.Customer_Variable;
GO
ROWSET_02

Der zu untersuchende Datensatz befindet sich auf Datenseite 1.144 und hat folgende Struktur:


DBCC_PAGE_03


Der “identische” Datensatz ist um 4 Bytes “gewachsen”. Statt 1.017 Bytes benötigt der Datensatz 4 weitere Bytes. Diese vier weiteren Bytes resultieren aus der geänderten Datensatzstruktur. Die nächste Abbildung zeigt den betroffenen Ausschnitt vergrößert an.


DBCC_PAGE_04


Die Abbildung zeigt das Ende der Datensatzstruktur. Die ersten zwei markierten Bytes repräsentieren die Anzahl der Spalten des Datensatzes. Das nächste Byte repräsentiert das NULL Bitmap. Der anschließende Bereich (2 Bytes) ist eine der Ursachen für das Wachstum der Datensatzgröße.


Der Hexadezimalwert 0x01 00 bestimmt die Anzahl der Spalten mit variablen Datensätzen. Die nächsten zwei Bytes (0xfd 03) bestimmen das Offset für das Ende des Datenbereichs für die erste Spalte mit variabler Datenlänge. Da keine weiteren Spalten vorhanden sind, beginnen unmittelbar im Anschluss die Daten des Clustered Keys.


Wer mehr Informationen zu den Strukturen eines Datensatzes wünscht, dem sei natürlich das Buch “Microsoft SQL Server 2012 Internals” von Kalen Delaney (b | t) empfohlen. Alternativ gibt es zu diesem Thema auch einen sehr guten Artikel von Paul Randal (b | t) unter dem Titel “Inside the storage engine: Structure of a record” .


Overhead


Bedingt durch die Struktur eines Datensatzes wächst die Datenlänge um maximal vier weitere Bytes. Hat die Tabelle bereits Attribute mit variabler Datenlänge gehabt, sind es lediglich zwei weitere Bytes für das Offset.


Neben der Vergrößerung des Datensatzes haben variable Datentypen noch andere Nachteile als Clustered Index:



  • Der Overhead von maximal 4 Bytes ist – auf den einzelnen Datensatz reduziert – unerheblich. Die zusätzlichen 4 Bytes können aber dazu führen, dass weniger Datensätze auf einer Datenseite gespeichert werden können.
  • CPU Zeit für die Evaluierung des Datenwertes, da erst das Offset für die variablen Bereiche und anschließend das Offset für den Datenbereich errechnet werden muss. Es ist sicherlich kein nennenswerter Zeitverlust aber – wie in vielen Dingen – ist die Menge der Datensätze für den Overhead entscheidend.
  • Das Schlüsselattribut eines Clustered Index wird in jedem non clustered Index gespeichert. Somit verteilt sich der Overhead auch auf non clustered Indexe.

Herzlichen Dank fürs Lesen!


Hinweis


Wer Kalen Delaney live erleben möchte, dem sei die SQL Konferenz in Darmstadt vom 03.02.2015 – 05.02.2015 sehr ans Herz gelegt. Die Agenda der SQL Konferenz liest sich wie das Who ist Who der nationalen und internationalen SQL Community.


Ich bin ebenfalls als Sprecher mit einem interessanten Thema “Indexing - alltägliche Performanceprobleme und Lösungenvertreten.


SQLKonferenz


Anmerkung


Dankenswerter Weise wurde ich durch einen Kommentar des von mir sehr geschätzten Kollegen Torsten Strauss darauf aufmerksam gemacht, dass der Artikel einige Voraussetzungen “unterschlagen” hat und somit zu Fehlinterpretationen führen kann.


Die Grundvoraussetzung zur Idee zu diesem Artikel ist, dass immer mit “gleichen” Karten gespielt wird – soll heißen, dass unabhängig vom Datentypen IMMER der gleiche Datenbestand verwendet wird. Ein fairer Vergleich wäre sonst nicht möglich! Spreche ich im ersten Beispiel vom Datentypen CHAR mit einer Länge von 10 Bytes, setzt das Beispiel mit dem Datentypen VARCHAR und einer maximalen Zeichenlänge von 10 Bytes voraus, dass ebenfalls 10 Zeichen pro Datensatz im Clustered Index gespeichert werden!

Christoph Müller-Spengler: How to tune ISV apps – Capture the workload

This is the first article of my blog post series How to tune ISV apps
In today’s blog post i will describe the steps that you have to do in order to capture a typical workload during business hours.

Importance of the baseline.

Why is it important to capture a workload?
Because you need something that you can compare your improvements against. If you have no baseline, you cannot guarantee that the improvements you have performed against your database are real improvements or just drawbacks.

The tools you need to capture the workload are:

  • SQL Server Profiler
  • SQL Server Management Studio

Don’t worry – SQL Server Profiler will not be used to capture the workload on the production server, it is just the tool to create the right “setup”.

Create the Tracefile

Start SQL Server Profiler, whether from the production server or another server does not matter, because we will not bring the production server to it’s knees ;-)
Choose “File -> New Trace…”
CW_Profiler1

Connect to your SQL Server instance using the credentials that you have. (You need at least the “ALTER TRACE” permission for you SQL Server Login.)
CW_Profiler2

In the next wizard the only important thing is the template that you choose, this has to be “TSQL_Replay”. With that template you are able to really replay one and the same workload against many different versions of our optimized database. Everytime you do that, you can check wether your changes have been good ones or bad ones.
CW_Profiler3

Just click “Run”. The grid with the incoming requests appears.
Click immediately on the red square button “Stop” in order to stop the trace.
CW_Profiler41

Now you have to export this new “Script Trace Definition” to a T-SQL file. Just save it anywhere, you can later copy’n’paste it to your production server, if you did not already start the SQL Server Profiler on that very server.
CW_Profiler5

Alter the Trace Definition File

Now you have to open the file and make some settings and tweak it a little bit.

  • Set an appropriate file size. Once you run this Trace Definition File in order to create the real trace files on file system, the trace will generate a new file each time it hits the threshold defined at the parameter “@maxfilesize”. The default for this value is 5 what means 5 MB. Personally i set it to 50 MB:
    set @maxfilesize = 50
    
  • Specify the location where the trace files should be written.
    !Important!: Please make sure that the amount of disk space you need is in place, because the amount of data you collect depends on the workload that you really have got on the production server and also on the duration that you want to capture.
    I captured a workload on a OLTP Helpdesk Trouble Ticket System between 1:30pm and 15:00pm and collected 4 GB of data.
    So please edit the following line, so that it fits your needs.
    You have to change two things in this line:

    • Change the value of the second parameter from “0” to “2” in order to activate the rollover option.
      Otherwise you will find the following error in SQL Server Errorlog:
      Trace ID ‘2’ was stopped because of an error. Cause: Stopping the trace because the current trace file is full and the rollover option is not specified.. Restart the trace after correcting the problem.

    • At the third parameter specify a proper path and filename.
    exec @rc = sp_trace_create @TraceID output, 2, N'D:\CapturedTraces\Trace', @maxfilesize, NULL, 200
    

    As mentioned in the notes above the line, the “.trc” extension will be appended to the filename automatically.

  • At the end of the file you will find a line with
    exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - d7cd774c-ec84-488e-afab-2211df8a6ead'
    

    If the SQL Server Instance you want to trace, hosts more than one database, you have to apply another filter like this, where you have to replace “YourDatabaseNameHere”:

    exec sp_trace_setfilter @TraceID, 35, 0, 6, N'YourDatabaseNameHere'
    
  • Now back to the very top of the file you have to add TSQL code to backup you database before executing the capturing trace like this:
    BACKUP DATABASE [YourDatabaseNameHere]
    TO DISK = N'E:\MSSQL10_50.MSSQLSERVER\MSSQL\YourDatabaseNameHere_4_Replay.bak'
    WITH
    	COPY_ONLY
    	, NOFORMAT
    	, NOINIT
    	, NAME = N'YourDatabaseNameHere-Full Database Backup'
    	, SKIP
    	, COMPRESSION
    	, STATS = 1
    GO
    

    Once again, just replace “YourDatabaseNameHere” with your real database name.
    !Important!: Please make sure that you have set the “COPY_ONLY” option in order to leave the backup chain unbroken. If your version of SQL Server supports backup compression, please also add “COMPRESSION”.

    Execute the Trace

    Once you have finished tweaking your “Trace Definition File”, open your SQL Server Management Studio, connect to your SQL Server instance, open a “New Query” and paste the code or just open the Trace Definition File.

    Now: execute the script, pressing “F5″ or clicking “! Execute”.

    In the result grid, you will see
    CW_Profiler6
    This is the ID of your very trace that you started. Keep this ID in mind or elsewhere for later use.

    If you have a look at the file system, you will see the created file(s) in your specified folder. Once the 50 MB have been reached, SQL Server rolls over to the next file, named YourFileNameHere_1.trc
    CW_Profiler7

    Stop the Trace

    After your defined period of time that you wanted to capture, you have to stop the SQL Trace. Here comes the ID of the trace into play as you have to pass it to the stored procedure that you execute as the first parameter.

    exec sp_trace_setstatus 2, 0
    

    After that, just delete the Trace so that there are no more processes living in SQL Server.

    exec sp_trace_setstatus 2, 2
    

    Save the data

    Now that you successfully captured the workload, just move the backup and all of your trace files away from your production server to a location that is safe.

    Summary

    In today’s blog post you have learned how to make use of SQL Server Profiler and SQL Server Management Studio to capture a workload on your production server in order to be able to replay it whenever you want against a specified backup that you made beforehand.

    I hope you enjoyed reading this blog post and will return for the next one when i will cover the topic “Diagnosis and Doing with “sp_BlitzIndex™””

    Thanks,
    Christoph


    Christoph Muthmann: Early Bird weiterhin für PASS Mitglieder verfügbar

    Deutsche SQL Server Konferenz 2015 - Early Bird Tickets weiterhin für PASS e.V. Mitglieder verfügbar!

    Ganze Geschichte »

    Marcel Franke: 2014 in review

    The WordPress.com stats helper prepared a 2014 annual report for my blog.

    Here’s an excerpt:

    The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 91,000 times in 2014. If it were an exhibit at the Louvre Museum, it would take about 4 days for that many people to see it.

    Click here to see the complete report.


    Filed under: Uncategorized

    Uwe Ricken: INTERN: Änderung der Größe eines Datenfeldes

    Auf Grund zahlreicher Emails unter MVP-Kollegen bin ich der Frage nachgegangen, wie sich die Änderung der Größe eines Datenfeldes mit fester Größe auf den Datenkonsum auswirkt. Ein weiterer sehr interessanter Aspekt war der Frage, wie sich die Änderung der Datengröße auf das Transaktionsvolumen auswirkt. Der nachfolgende Artikel geht diesen Fragen nach und beleuchtet die internen Prozesse des Vorgangs.

    Wie häufig kommt es vor, dass das ursprünglich entwickelte Design der Metadaten durch die aufkommenden Datenmengen obsolet wird? Letztendlich bleibt dem DBA die Möglichkeit, die bestehende Metadatenstruktur mittels ALTER TABLE ... ALTER COLUMN schnell und einfach zu ändern. Bei wenigen Datenzeilen gestaltet sich die Operation – in der Regel – recht einfach und schnell; deutlich mehr Überlegungen sind ratsam, wenn es um mehrere Millionen Datensätze geht. In diesem Fall sollte der komplette Vorgang zunächst in einer Testumgebung durchspielt werden und die Messwerte für Zeit und Volumen sorgfältig mit einer sinnvollen Strategie abgewogen werden!

    Testumgebung

    Für die Testumgebung wird eine Tabelle mit 10.000 Datensätzen gefüllt. Pro Datenseite werden – basierend auf einer Datensatzlänge von 2.015 Bytes – 4 Datensätze gespeichert.

    -- Falls die Tabelle bereits existiert, wird sie gelöscht
    IF OBJECT_ID('dbo.demo_table', 'U') IS NOT NULL
        DROP TABLE dbo.demo_table;
        GO
     
    -- Erstellen der Testtabelle
    CREATE TABLE dbo.demo_table
    (
        Id    INT         NOT NULL,
        c1    CHAR(2001)  NOT NULL    DEFAULT ('A'),
        c2    DATE        NOT NULL
    );
    GO
     
    -- Clustered Index liegt auf Attribut c2
    CREATE UNIQUE CLUSTERED INDEX ix_demo_table_c2 ON dbo.demo_table (c2)
    GO

    Die Tabelle besitzt einen Clustered Key auf [c2]. Jeder Datensatz hat eine feste Datenlänge von 2.015 Bytes. Mit insgesamt 10.000 Datensätzen belegt die Tabelle in der Datenbank 2.502 Datenseiten im Leaf-Level (http://msdn.microsoft.com/de-de/library/ms190457.aspx).



    -- Anzahl belegter Datenseiten in Tabelle
    SELECT  OBJECT_NAME(p.object_id)    AS    table_name,
            au.type_desc,
            au.total_pages,
            au.used_pages,
            au.data_pages
    FROM    sys.partitions AS P INNER JOIN sys.allocation_units AS AU
            ON (p.partition_id = au.container_id)
    WHERE   p.object_id = OBJECT_ID('dbo.demo_table', 'U');

    RECORDSET_03


    Basierend auf der Definition des Clustered Keys wird ein Datensatz auf einer Datenseite in der folgenden Reihenfolge gespeichert:



    -- Interne Struktur eines Datensatzes auf einer Datenseite
    SELECT  c.name                AS column_name,
            c.column_id,
            pc.max_inrow_length,
            pc.system_type_id,
            t.name                AS    type_name,
            pc.leaf_offset
    FROM    sys.system_internals_partition_columns pc INNER JOIN sys.partitions p
            ON (p.partition_id = pc.partition_id) LEFT  JOIN sys.columns c
            ON (
                  column_id = partition_column_id AND
                  c.object_id = p.object_id
               ) INNER JOIN sys.types AS T
            ON (pc.system_type_id = t.system_type_id)
    WHERE   p.object_id = OBJECT_ID('dbo.demo_table');

    RECORDSET_01


    Alle Datentypen sind Datentypen mit fester Länge. Die Spalte [leaf_offset] speichert die Offsets der einzelnen Spaltenwerte eines Datensatzes. Ein Datensatz beginnt immer bei Offset 0x04 da der Datensatzheader eine feste Länge von 4 Bytes besitzt (0x00 – 0x03). Nach der Speicherung des Datums (3 Bytes) werden die Daten des Attributs [Id] (4 Bytes) an der Position 0x07 gespeichert und der Inhalt des letzten Attributs [c1] wird ab Position 0x0B gespeichert.


    Unabhängig von der Struktur der Metadaten werden die Clustered Key(s) einer Tabelle am Beginn eines Datensatzes gespeichert wenn es sich beim Datentypen des Clustered Keys um einen Datentypen mit fester Länge handelt.


    ALTER TABLE ... ALTER COLUMN


    Die bestehende Datenstruktur soll im Beispiel angepasst werden. Der Datentyp des Attributs [Id] soll von einem INT-Datentypen (4 Bytes) zu einem BIGINT-Datentypen (8 Bytes) umgewandelt werden. Durch die Konvertierung verdoppelt sich das benötigte Speichervolumen für [Id]. Auf einer Datenseite sind maximal 8.060 Bytes für Daten reserviert. Wird die Datenlänge vergrößert, passen die Daten nicht mehr vollständig auf eine Datenseite und es erfolgt ein – kostenintensiver – Page Split!



    BEGIN TRANSACTION;
        -- Änderung des Datentypen INT zu BIGINT
        ALTER TABLE dbo.demo_table ALTER COLUMN [Id] BIGINT NOT NULL;
     
        -- Wie häufig wurden Page Splits durchgeführt?
        SELECT  Operation, COUNT_BIG(*)
        FROM    sys.fn_dblog(NULL, NULL) AS FD
        WHERE   Operation IN
                (
                   'LOP_DELETE_SPLIT',
                   'LOP_MODIFY_COLUMNS'
                )
        GROUP BY
                Operation;
    COMMIT TRANSACTION;

    RECORDSET_02


    Das Ergebnis zeigt die Modifikation von 10.000 Datensätzen innerhalb der Transaktion. Da mit jeweils 4 Datensätzen die Datenseiten vollständig belegt waren, mussten die Daten JEDER Datenseite auf weitere Datenseiten verteilt werden – eine sehr kostenintensive Operation.


    Interne Verwaltung


    Die Operation als solche ist – bedingt durch die auftretenden Page Splits – sehr kostenintensiv und würde deutlich kostspieliger werden, wenn beim Erweitern des benötigten Speichers Bestandsdaten überschrieben würden. Microsoft SQL Server verfährt bei der Erweiterung anders als man vermutet.



    Slot 0 Offset 0x60 Length 2015
    Record Type = PRIMARY_RECORD   Record Attributes =  NULL_BITMAP  Record Size = 2015
    Memory Dump @0x0000000015E9A060
     
    Slot 0 Column 3 Offset 0x4 Length 3 Length (physical) 3
    c2 = 1953-01-01
     
    Slot 0 Column 1 Offset 0x7 Length 4 Length (physical) 4
    Id = 2447
     
    Slot 0 Column 2 Offset 0xb Length 2001 Length (physical) 2001
    c1 = P46Q7LE7JCKDLJPWX5JRZYKO0297SN5A8TTI3F9SWZTK1JGL66CNZN1R...
    <... SNIP ...>
     
    Slot 0 Offset 0x0 Length 0 Length (physical) 0
    KeyHashValue = (0950748c2d13)

    Der obige Code zeigt den Auszug einer Datenseite vor der Änderung des Datentypen. Der Auszug der Datenseite entspricht einem Datensatz in der Tabelle. Sehr gut zu erkennen ist, dass das Schlüsselattribut [c2] an erster Position gespeichert wird. Anschließend folgen [Id] und [c1].


    Nachdem der Datentyp für das Attribut [Id] von INT auf BIGINT geändert wurde, sieht die Datenseite wie folgt aus:



    Slot 0 Column 3 Offset 0x4 Length 3 Length (physical) 3
    c2 = 1953-01-01                     
     
    Slot 0 Column 67108865 Offset 0x7 Length 0 Length (physical) 4
    DROPPED = NULL                      
     
    Slot 0 Column 2 Offset 0xb Length 2001 Length (physical) 2001
    c1 = P46Q7LE7JCKDLJPWX5JRZYKO0297SN5A8TTI3F9SWZTK1JGL66CNZN1R...
     
    Slot 0 Column 1 Offset 0x7dc Length 8 Length (physical) 8
    Id = 2447                           
     
    Slot 0 Offset 0x0 Length 0 Length (physical) 0
    KeyHashValue = (0950748c2d13)

    Der obige Auszug aus der gleichen Datenseite zeigt, wie Microsoft SQL Server vorgeht, wenn der feste Datentyp eines Attributs vergrößert wird. Aus transaktioneller Sicht wäre das “verschieben” des Offsets eine Katastrophe. In diesem Fall müsste Microsoft SQL Server jeweils die ganze Datenseite verschieben. Die nachfolgenden Abbildungen zeigen, welchen Aufwand Microsoft SQL Server bewältigen müsste, wenn der Prozess so umständlich ablaufen würde.


    PPT_01


    Die obige Abbildung zeigt vier Datensätze, die auf einer Datenseite gespeichert sind. Die Offsets mit dem * sollen die Attribute repräsentieren, die durch einen neuen – größeren – Datentypen ersetzt werden sollen. Würde nun tatsächlich der Speicherbereich an “Ort und Stelle” erweitert werden, müssten alle nachfolgenden Speicherbereiche verschoben werden.


    PPT_02


    Würde Microsoft SQL Server tatsächlich dieses Verfahren verwenden, müssten für die Erweiterung von Datensatz “A” 38 “Einheiten” verschoben werden (Vorgang 1); um Datensatz “B” zu verschieben, müssten weitere 28 “Einheiten” verschoben werden. Für Datensatz “C” wären es immerhin noch 18 “Einheiten und für den letzten Datensatz weitere 8 “Einheiten – somit insgesamt 92 “Einheiten”. Es scheint logisch, dass für diesen Vorgang nicht nur erhebliche Daten im Transaktionsprotokoll entstehen sondern dass auch der Vorgang selbst sehr zeitintensiv ist. Aus diesem Grund geht Microsoft SQL Server wie folgt vor, wenn ein größerer Datentyp verwendet wird.


    PPT_03


    Statt die ganze Datenseite zu verschieben, wird die ursprüngliche Spalte als [DROPPED] gekennzeichnet und am Ende des Datenbereichs mit fester Länge wird das Attribut neu angelegt. Durch dieses Verfahren reduziert sich der Overhead für das Verschieben von Daten erheblich.


    Ein Blick auf die Datenseite selbst zeigt, wie Microsoft SQL Server die Attribute neu ausgerichtet hat:



    Slot 0 Offset 0x60 Length 2023
    Record Type = PRIMARY_RECORD  Record Attributes =  NULL_BITMAP  Record Size = 2023
    Memory Dump @0x0000000015E9A060
     
    Slot 0 Column 3 Offset 0x4 Length 3 Length (physical) 3
    c2 = 1953-01-01                     
     
    Slot 0 Column 67108865 Offset 0x7 Length 0 Length (physical) 4
    DROPPED = NULL                      
     
    Slot 0 Column 2 Offset 0xb Length 2001 Length (physical) 2001
    c1 = 7O9E4PMJ0SEX8N6PN2GPC54XBNY65LOQG1TU4LXJKIKPLYY4JAB...
    <;... SNIP...>
     
    Slot 0 Column 1 Offset 0x7dc Length 8 Length (physical) 8
    Id = 2447
     
    Slot 0 Offset 0x0 Length 0 Length (physical) 0
    KeyHashValue = (0950748c2d13)

    Der Auszug gibt eine Vielzahl von Informationen über den Vorgang preis. Auffällig ist, dass der Datensatz nicht um 4 Bytes gewachsen ist sondern um 8 Bytes! Dieses Ergebnis ist dem Umstand geschuldet, dass das ursprüngliche Attribut nicht physikalisch aus dem Datensatz entfernt wurde sondern weiterhin 4 Bytes konsumiert (rot gekennzeichnet). Zum bestehenden Datensatz ist ein *neues* Attribut hinzugekommen, dass weitere 8 Bytes konsumiert. Dieses Attribut wird im Datensatz als [Column 1] deklariert. Sehr gut erkennbar ist, dass sich die Anordnung der Attribute im Datensatz geändert hat. Die ursprüngliche physikalische Reihenfolge war [c2], [Id], [c1]. Durch die Erweiterung sieht die physikalische Reihenfolge nun wie folgt aus:



    -- Interne Struktur eines Datensatzes auf einer Datenseite
    SELECT  c.name                AS column_name,
            c.column_id,
            pc.max_inrow_length,
            pc.system_type_id,
            t.name                AS    type_name,
            pc.leaf_offset
    FROM    sys.system_internals_partition_columns pc INNER JOIN sys.partitions p
            ON (p.partition_id = pc.partition_id) LEFT  JOIN sys.columns c
            ON (
                  column_id = partition_column_id AND
                  c.object_id = p.object_id
               ) INNER JOIN sys.types AS T
            ON (pc.system_type_id = t.system_type_id)
    WHERE   p.object_id=object_id('dbo.demo_table');
    GO
    RECORDSET_04

    Das zuvor an Position 2 geführte Attribut [Id] ist logisch nicht mehr vorhanden. Es konsumiert weiterhin 4 Bytes und beginnt an Offset 0x07. Das Attribut wurde “an anderer Stelle” im Datensatz neu implementiert. Das Attribut [Id] ist nun an das Ende des Bereichs für feste Datenlänge “gerutscht” und konsumiert dort 8 Bytes.


    Lösung


    Um den überflüssigen Speicherverbrauch wieder aus den Datenseiten zu eliminieren, reicht es aus, den Clustered Index neu aufzubauen. Durch den REBUILD wird der Speicherbereich wieder freigegeben und die Attribute wieder in der korrekten physikalischen Reihenfolge angeordnet.



    ALTER INDEX ix_demo_table_c2 ON dbo.demo_table REBUILD;

    RECORDSET_05


    Zusammenfassung


    Das Erweitern von festen Datentypen erfordert eine sorgfältige Planung, wenn es sich um sehr große Tabellen handelt. Bedingt durch den gewaltigen Overhead sind bei solchen Metadaten-Änderungen folgende Aspekte zu berücksichtigen und nach Möglichkeit in einer Testumgebung sorgfältig zu prüfen:


    Vollständig protokollierte Transaktion


    Änderungen von Metadaten sind immer vollständig protokollierte Transaktionen. Dieser Umstand bedeutet in der Umsetzung, dass bei sehr hoher Datenmenge ein nicht zu unterschätzendes Transaktionsvolumen generiert wird. Aus diesem Grund sollte bereits vor der Umsetzung das Transaktionsprotokoll ausreichend dimensioniert sein, um Wartezeiten durch AUTOGROWTH zu vermeiden. Dateien eines Transaktionsprotokolls partizipieren nicht von Instant File Initialization und somit ist der zeitliche Overhead für Vergrößerungsvorgänge der Protokolldatei ein Faktor bei der Berechnung des Zeitaufwands.


    Fragmentierung von Indexen


    Durch die Vergrößerung der Datensatzlänge muss dieser Platz im Datensatz reserviert werden. Wird die Vergrößerung auf einen frisch aufgebauten Index angewendet, ist davon auszugehen, dass bereits durch die Page Splits ca. 4 – 6 KByte zusätzliches Transaktionsvolumen pro Datenseite generiert werden.


    Man kann diese Overhead weitestgehend vermeiden, indem man folgende Punkte VOR der Vergrößerung beachtet und umsetzt:


    • Analyse der durch den Index belegten Datenseiten (siehe Abfrage oben)
    • Überprüfung der durchschnittlichen Anzahl von Datensätzen / Datenseite
    • Neuaufbau des Index (im geeigneten Servicefenster) mit einem FILLFACTOR, der so viel Platz auf der Datenseite belässt, dass eine Erweiterung ohne Page Splits durchgeführt werden kann.


    Beispiel für die Berechnung des FILLFACTOR für den Neuaufbau des Index:


    Ein Index belegt 100.000 Datenseiten. Ein Datensatz hat eine Länge von ~400 Bytes. Somit passen auf eine Datenseite 20 Datensätze. Ein Attribut vom Datentypen [INT] soll zu einem [BIGINT] geändert werden. Somit müssen auf einer Datenseite für den Erweiterungsvorhang 20 * 8 Bytes = 160 Bytes zusätzlich zur Verfügung stehen. Ein FILLFACTOR von 5% ist für den zu bewältigenden Änderungsvorgang ein guter Ausgangswert für den Index. Der gesamte Vorgang der Vergrößerung unterteilt sich somit in drei Phasen:



    • Neuaufbau des Index mit einem FILLFACTOR von 5%
    • Anpassung des Datentypen (INT –> BIGINT)

    Herzlichen Dank fürs Lesen!


    PS: Ich wünsche allen Lesern meines Blogs einen guten Start, Glück und Gesundheit für 2015. Ich hoffe, dass die von mir gewählten Themen interessant und informativ sind. Ich freue mich auf 2015 mit vielen Konferenzen und neuen Kontakten.


    Es würde mich sehr freuen, die Leser meines Blogs persönlich kennenzulernen – also einfach mal ansprechen, wenn wir uns auf einer Konferenz begegnen sollten.


    Die erste Möglichkeit in diesem Jahr wäre z. B. die SQL Konferenz (http://www.sqlkonferenz.de). Dort spreche ich am 05.02.2015 über tägliche Indexprobleme, wie man sie erkennt und wie man sie lösen kann!

    Tillmann Eitelberg: SQL Konferenz 2015

      SQL Konferenz 2015 Bald ist es wieder so weit, die SQL Konferenz für das Jahr 2015 startet vom 3. Februar bis 5. Februar im Darmstadtmium in Darmstadt. Im Folgenden möchte ich als einer der Verantwortlichen der Konferenz einen etwas detaillierteren Einblick in die Agenda geben. Auswahl der Sessions und Kommentare stellen meine persönliche Selektion […]

    Uwe Ricken: Kombinierte Indexe richtig definieren

    Ganz aktuell habe ich bei einem Kunden ein Problem in einer Abfrage entdeckt, die trotz “korrektem” Index nicht optimal ausgeführt wird. Das Problem war relativ schnell gefunden; die Reihenfolge der Attribute im Index waren nicht korrekt implementiert. Der Artikel beschreibt, wie wichtig bei der Erstellung von Indexen die Berücksichtigung von Abfragemustern ist.

    Szenario

    Der Kunde setzt ein Online-Bestellsystem ein, das neben einer Kundennummer ([Customer_Id]) das Datum der Bestellung ([OrderDate]) speichert. Für die nachfolgenden Beispiele wird eine einfache Tabellenstruktur mit 100.000 Datensätzen erstellt. Alle 100.000 Aufträge datieren aus dem Jahr 2014. Für die Erstellung der Testdaten habe ich den SQL Data Generator von Red Gate verwendet!

    -- Erstellung der Demotabelle
    CREATE TABLE dbo.Orders
    (
        Id           INT     NOT NULL    IDENTITY (1, 1),
        OrderNo      CHAR(5) NOT NULL,
        Customer_Id  INT     NOT NULL,
        OrderDate    DATE    NOT NULL,
     
        CONSTRAINT pk_Orders_ID PRIMARY KEY CLUSTERED (Id)
    );
    GO
     
    -- Zusammengesetzter Index nach Datum und Kunden-Id
    CREATE INDEX ix_Orders_OrderDate ON dbo.Orders
    (
        OrderDate,
        Customer_Id
    );
    GO

    Abfrage(n)


    Die Tabelle enthält 100.000 Datensätze. Von diesen 100.000 Datensätzen sind ca. 9.000 Datensätze aus dem Januar 2014. Die Abfrage sucht alle Aufträge, die im Januar 2014 getätigt wurden:



    SELECT Id, Customer_Id, OrderDate
    FROM   dbo.Orders
    WHERE  OrderDate BETWEEN '20140101' AND '20140131';

    Der zugehörige Ausführungsplan sieht ideal aus und zeigt, dass der zuvor erstellte Index [ix_Orders_OrderDate] verwendet wird um einen INDEX SEEK auf [OrderDate] auszuführen.


    EXECUTION_PLAN_01 


    Der Ausführungsplan zeigt 8.426 gefundene Datensätze. Das Ergebnis der Operation war vorhersehbar, da [OrderDate] im Index das primäre Indexattribut repräsentiert. In diesem Fall ist es unerheblich, dass [Customer_ID] als Prädikat nicht verwendet wurde.


    Diese Abfrage wird vom Benutzer jedoch nicht nur zur Eingrenzung des Bestelldatums angewendet sondern ausschließlich in Verbindung mit einer gültigen Kundennummer ([Customer_Id]). Die Abfrage muss um die Eingrenzung nach der Kundennummer erweitert werden:



    SELECT Id, Customer_Id, OrderDate
    FROM   dbo.Orders
    WHERE  OrderDate BETWEEN '20140101' AND '20140131' AND
           Customer_Id = CAST(5 AS INT);

    Der Ausführungsplan der obigen Abfrage lässt erahnen, dass erneut ein INDEX SEEK verwendet wird. Schaut man jedoch auf die Eigenschaften des Abfrageoperators, kann man Anomalien erkennen, die darauf hinweisen, dass der INDEX SEEK nicht “ideal” verwendet wird.


    EXECUTION_PLAN_02


    Die Abbildung zeigt, dass für das Datum erneut ein SEEK verwendet werden konnte während jedoch die Suche nach der Kundennummer ([Customer_Id]) mit Hilfe eines Filters durchgeführt wird. In diesem Fall spricht man von einem Index Range Scan! Um die Filteroperation sichtbar zu machen, wird Traceflag 9130 als Option verwendet.



    SELECT Id, Customer_Id, OrderDate
    FROM   dbo.Orders
    WHERE  OrderDate BETWEEN '20140101' AND '20140131' AND
           Customer_Id = CAST(5 AS INT)
    OPTION (QUERYTRACEON 9130);

    EXECUTION_PLAN_03


    Wie der Ausführungsplan zeigt, müssen zunächst erneut 8.426 Datensätze aus dem Datumszeitraum gesucht werden. Diese Datenmenge wird evaluiert um sie anschließend mit Hilfe eines Filters auf die – in diesem Beispiel – verbliebenen 20 Datensätze zu reduzieren. Microsoft SQL Server muss also 8.426 Datensätze überprüfen, um alle Datensätze mit der Kundennummer 5 zu filtern! Microsoft SQL Server geht bei der Suche nach den Datensätzen wie folgt vor:



    |--Index Seek(OBJECT:([demo_db].[dbo].[Orders].[ix_Orders_OrderDate]),
    SEEK:
    (
       ([demo_db].[dbo].[Orders].[OrderDate], [demo_db].[dbo].[Orders].[Customer_Id]) >= (CONVERT_IMPLICIT(date,[@1], 0), [@3]) AND
       ([demo_db].[dbo].[Orders].[OrderDate], [demo_db].[dbo].[Orders].[Customer_Id]) <= (CONVERT_IMPLICIT(date,[@2], 0), [@3])
    ),
    WHERE:([demo_db].[dbo].[Orders].[Customer_Id]=[@3]) ORDERED FORWARD)

    Es ist zu erkennen, dass für die Suche nach den Datumswerten ein – optimaler – INDEX SEEK verwendet wird. Jedoch muss für die Suche nach der Kundennummer eine WHERE-Klausel verwendet werden, die im Ausführungsplan als FILTER-Operator gekennzeichnet ist. Ursächlich für dieses Verhalten ist die Verwendung von >=, <=, BETWEEN.


    Microsoft SQL Server kann für die Kombination [OrderDate] und [Customer_Id] nicht auf Statistiken zurück greifen. Microsoft SQL Server speichert im Histogramm ausschließlich die Werte des ersten Attributs eines Index.



    DBCC SHOW_STATISTICS('dbo.Orders', 'ix_Orders_OrderDate') WITH HISTOGRAM;
    DBCC SHOW_STATISTICS_HISTOGRAM_01

    Dadurch bedingt werden zunächst alle Datumswerte aus der Datenmenge extrahiert die nachträglich auf der Kundennummer ([Customer_Id]) gefiltert werden.


    Warum INDEX SEEK + FILTER?


    Warum kann Microsoft SQL Server nicht idealer Weise bereits beim INDEX SEEK über [OrderDate] das Attribut [Customer_Id] berücksichtigen? Dieser Umstand ist der generellen Struktur eines Index geschuldet. Ein Index ist in einer B-TREE Struktur organisiert. Hierbei wird der Indexbaum immer von einem Root-Knoten bis zu einem Leaf-Knoten durchsucht, um die angeforderten Daten zu finden. Um die Indexstruktur abzubilden, wird die DMF [sys].[dm_db_database_page_allocation] verwendet:



    -- Show the index structure of the index ix_Orders_OrderDate
    SELECT  page_type_desc,
            page_level,
            allocated_page_page_id,
            previous_page_page_id,
            next_page_page_id
    FROM    sys.dm_db_database_page_allocations
    (
        DB_ID(),
        OBJECT_ID('dbo.Orders', 'U'),
        3,
        NULL,
        'DETAILED'
    ) AS DDDPA
    WHERE   is_allocated = 1
    ORDER BY
        page_type DESC,
        page_level DESC,
        previous_page_page_id ASC;

    Die Abbildung zeigt, wie von der Root Page (11.208) abwärts die Leaf-Ebene für alle Daten des Januar 2014 durchsucht wird.


    INDEX_RANGE_SCAN_01


    Entscheidend für die Arbeit der Query Engine ist, dass der Index erst nach [OrderDate] und anschließend nach [Customer_Id] sortiert ist. Microsoft SQL Server kann nicht in einem Prozessschritt sowohl Datum als auch Kundennummer mit einem effektiven SEEK erkennen. Vielmehr müssen erst alle Bestellungen von Januar 2014 ermittelt werden und anschließend kann die Ergebnismenge nach der entsprechenden Kundennummer “gefiltert” werden!


    Reihenfolge der Attribute in Index


    Immer wieder wird empfohlen, einen Index nach der Kardinalität seiner Attribute zu erstellen; diese Aussage ist nur bedingt richtig – wie das obige Bespiel beeindruckend zeigt. Kardinalität allein kann nicht das Kriterium sein, nach dem ein Index erstellt wird; es gilt auch die Abfragen selbst zu analysieren. Wenn – wie im vorliegenden Beispiel – ein Index sehr häufig für Mengenoperationen verwendet wird, kann die gewählte Indexstrategie schnell zu einem Bumerang werden. Für das aktuelle Szenario wurde der Index wie folgt umgebaut:



    CREATE INDEX ix_Orders_OrderDate ON dbo.Orders (Customer_Id, OrderDate) WITH DROP_EXISTING;

    Wir die gleiche Abfrage erneut ausgeführt, reduziert sich das IO um über 90%, da der Index ix_Orders_OrderDate wesentlich effektiver verwendet werden kann:



    SELECT Id, Customer_Id, OrderDate
    FROM   dbo.Orders
    WHERE  OrderDate BETWEEN '20140101' AND '20140131' AND
           Customer_Id = CAST(5 AS INT)
    OPTION (QUERYTRACEON 9130);
    EXECUTION_PLAN_04

    Die Details des Ausführungsplans offenbaren, dass sowohl nach der Kundennummer als auch nach dem Auftragsdatum mit Hilfe eines INDEX SEEK gesucht werden kann. Die Ursache hierfür liegt in der – neuen – Struktur des Indexes.


    INDEX_RANGE_SCAN_02


    Die neue Struktur des Indexes wird durch die Kundennummer ([Customer_Id]) kontrolliert. Da der Index aus [Customer_Id] und [OrderDate] besteht, wird das zweite Attribut ([OrderDate]) ebenfalls im Index sortiert. Wird – wie in der Beispielabfrage – nach einem Kunden mit der Customer_Id = 5 gesucht, kann der Query Optimizer einen optimalen INDEX SEEK verwenden, da er lediglich einen Indexwert betrifft. Die gleiche Operation kann nun auch für das Auftragsdatum verwendet werden, da dieses Attribut im Index vorhanden und sortiert ist. Die Query Engine KANN beim INDEX SEEK das Datum während des Zugriffs prüfen. Ist die obere Grenze des zu filternden Auftragsdatums erreicht, kann die Query Engine die Arbeit beenden; es kann keine weiteren Aufträge mit einem Bestelldatum im Januar geben, die nach dem 31.01.2014 getätigt wurden!


    Zusammenfassung


    Die richte Zusammenstellung / Komposition eines Index hängt nicht immer von starren Regeln ab. Vielmehr muss immer wieder der eigentliche Workload im Fokus stehen. Wenn – wie das obige Beispiel recht deutlich zeigt – stur nach “Vorgabe” implementiert wird, ergeben sich daraus hervorragende Optimierungsmöglichkeiten, die für Überraschungen sorgen können.


    Herzlichen Dank fürs Lesen!


    PS: Dieses Problem wie auch viele andere werde ich auf der SQL Konferenz 2015 in Darmstadt demonstrieren, analysieren und mit Lösungswegen präsentieren. Die SQL Konferenz findet in 2015 das zweite Mal mit einer beeindruckenden Themen- und Sprechervielfalt rund um SQL Server statt nachdem bereits die Premiere in 2014 ein voller Erfolg war.


    Seien es Fragen zu diesem Artikel oder aber auch generelle Fragen zur Indexierung oder zur Database Storage – bitte nicht zögern, mich einfach anzusprechen. Ich freue mich über jeden Gedankenaustausch!

    SQLPodcast: #009 – Windows PowerShell

    Im Gespräch mit Sascha Lorenz, Microsoft SQL Server MVP, rund um das Thema Microsoft PowerShell.

    Marcel Franke: Conference time – SQL Server Conference 2015

    728x90_ImSpeaking

    Although everybody prepares for Christmas and the holidays, some people already prepare the first conferences starting early next year. And so I’m very happy that the SQL Server Conference 2015 will take place in Darmstadt again. We will have 2 days full of great sessions with international speakers. So also for English speaking people there are lot’s of sessions to follow. And there is a full day only for PreCon Trainings.

    I’m also very happy to say that I have the chance to support the community with a session about “SAP Hana, Power Pivot and SQL Server – In-Memory technologies compared”.

    So register yourself, take a seat and enjoy the sessions.


    Filed under: Conferences

    Falk Krahl: Neues Servicepack für SQL Server 2014

    Heute ist für den SQL Server 2014 ein neues Update erschienen. Es handelt sich dabei um das kumulative Update 05. Es kann unter folgendem Link angefordert werden.
    Kumulatives Update 05 für SQL Server 2014

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

    Ganz frisch erschienen und im SQL Server Releaseblog veröffentlicht wurde ein CU.

    Ganze Geschichte »

    Christoph Muthmann: Die Reinheit der Daten

    Das ist mal wieder ein Artikel, der auch mit "Es war einmal" anfangen könnte. Es war einmal vor dem Jahre 2005...

    Ganze Geschichte »

    Christoph Muthmann: Passwort gleich Login

    Heute nur ein kurzer Tipp dazu, wie man die SQL Logins findet, deren Passwort identisch mit dem Namen ist.

    Ganze Geschichte »

    Christoph Müller-Spengler: Last “Touch” on each Database per Instance

    cms4j:

    Enhanced the T-SQL Script by adding last_user_scan, last_user_seek and last_user_lookup and compared the values against each other to get the most recent access to the very table.

    Originally posted on cms4j:

    Today i was asked if some of the databases on a specific instance were in use or not.
    As this is an interesting question that could be easily answered by the SQL Server DMVs i wrote a little script to answer this question.

    It creates a temporary table with five colummns named DatabaseName, TableName, IndexType, LastTouch and ActualDate (this is just for comparison).
    It then executes sp_MSforeachdb and inserts a record for each database within the instance except for the system databases.
    It then tells us which table was recently used and when.

    1
    /*
    @cms4j 2014-01-13
    Script to get the latest "touch" on each database
    to get to know wheather the database is in use or not.
    */

    CREATE TABLE #LastTouchOnDatabase
    (
    [DatabaseName] nvarchar(200) NULL
    , [TableName] nvarchar(200) NULL
    , [IndexType] nvarchar(200) NULL
    , [LastTouch] datetime NULL
    , [ActualDate] datetime NULL
    )

    EXEC dbo.sp_MSforeachdb 'USE [?];
    INSERT INTO #LastTouchOnDatabase
    (DatabaseName…

    View original 148 more words


    Christoph Muthmann: PASS KBD 10 Jahre

    Am Montag hatten wir Gelegenheit eine tolle Veranstaltung mit großer Tombola in Köln auszurichten. Hier einige Eindrücke.

    Ganze Geschichte »

    Christoph Müller-Spengler: Blog Post Series „How to tune ISV apps“

    Inspired by Brent Ozars Blog post http://www.brentozar.com/archive/2014/12/whos-allowed-add-indexes-isv-apps/ I will start this series.

    It will consist of:

    1. Capture the workload
      Capturing a typical workload during business hours.
    2. Diagnosis and Doing with “sp_BlitzIndex™”
      Diagnose and Delete/Alter/Add Indexes with the help of Brent Ozars “sp_BlitzIndex™”.
    3. Diagnosis and Doing with “Find Key Lookups in Cached Plans”
      Diagnose and Alter/Add Indexes with the help of Kendal van Dykes “Find Key Lookups in Cached Plans”.
    4. Setup Distributed Replay
      Setup a SQL Server Instance with Distributed Replay.
    5. Replay the workload
      Replay the captured workload against a backup first taken before capturing the first workload.
    6. Analysis of Performance Improvements
      Analyze two captured workloads with the help of Qure Analyzer.
    7. Analysis with Chrome Developer Tools.
      Load the ISV app in Chrome and make use of the Developer Tools to measure the response times of the Database Server.
    8. Subjective measurements
      Let human beings test the improved app.

    Hope to keep you as readers,
    Christoph Müller-Spengler


    Sascha Lorenz: 10 Jahre SQL Server Usergroup in Hamburg – PASS Deutschland e.V. feiert !

    Hallo PASS’ler im Norden,

    hiermit möchten wir zu einem besonderen Treffen der Regionalgruppe Hamburg einladen!

    Es gibt etwas zu feiern! Die PASS Deutschland besteht seit 10 Jahren, im Oktober haben wir uns in der Regionalgruppe zum 100. Mal getroffen und wir wollen mit Euch gemeinsam das Jahr gemütlich ausklingen lassen. Deswegen gibt es leckeres Essen und eine Verlosung von Überraschungspreisen.

    Bitte bringt gern Kollegen und Freunde mit, die sich auch mit dem SQL Server beschäftigen. Wir freuen uns auf Euch!

    Donnerstag, 11. Dezember 2014,
    18:00 Uhr bis ca. 20:30 Uhr,
    (Vortrag beginnt ab 18:30 und geht bis ca. 20:30 Uhr, davor sorgen wir dafür, dass ihr nicht verhungert…)

    in der MICROSOFT Niederlassung Hamburg (Adresse unten)

    Thema: SQL Server Data Tools für Datenbankentwickler von Peter Horst
    ”Wir werden das Entwickeln von Datenbankanwendungen mit den Microsoft SQL Server Data Tools kennenlernen. Themen wie Delta-Deployment auf unterschiedliche Datenbankschemaversionen, Refactoring, Code-Analyse & Datenbank Unit Test werden nicht fehlen.”

    Wir treffen uns bei:
    Microsoft Deutschland GmbH
    Geschäftsstelle Hamburg
    Gasstraße 6a
    22761 Hamburg

    Kostenlose Parkplätze befinden sich hinter dem Gebäude. Der Parkplatz ist
    über die Rampe mit dem Schild "Microsoft Kunden" erreichbar.

    Nur wenige Minuten zu Fuß ist der S-Bahnhof Bahrenfeld entfernt (S1/S11).

    Ansprechpartner vor Ort: MS Empfangs-Team, Cornelia Matthesius und Sascha Lorenz.

    Wir bitten um eine vorherige Anmeldung per Email an: slo@sqlpass.de

    Wichtig: Wir benötigen die Anmeldungen 2 Tage vor dem Treffen, da wir uns
    2014 bei Microsoft treffen können und dort Besucherausweise ausgestellt
    werden! Spontane Teilnehmer sind dennoch willkommen. ;-)

    Sascha Lorenz: Power BI - SQL Server Konferenz 2015

    120x600_SQL_Server_Konferenz7[4][4]Wie ich auch schon in diesem Blogpost angemerkt habe, ist die kommende SQL Server Konferenz in Darmstadt ein ganz besonderes Event. Daher möchte ich in den kommenden Tagen einige Sessions im Detail vorstellen.

    Microsoft BI vNext (Neues aus der Welt von Power BI)
    ”In den letzten hat Microsoft stark im Bereich Self Service BI investiert und die diversen Produkte unter dem Label PowerBI zusammengefasst. In diesem einführenden Vortrag stellt Frank Geisler die Produkte PowerQuery, PowerPivot, PowerView und PowerMap vor. Es wird gezeigt wie man über PowerQuery Daten in eine PowerBI Lösung importieren kann, wie man dann mit Hilfe von PowerPivot ein Datenmodell aufbaut um dieses dann mit PowerView und PowerMap zu visualisieren. Zum Schluss des Vortrages wird noch gezeigt wie man die fertige Lösung auf der PowerBI Website bereitstellen und mit Q&A abfragen kann.”

    Von:

    Frank Geisler ist geschäftsführender Gesellschafter der GDS Business Intelligence GmbH und beschäftigt sich in seinem Unternehmen mit dem Microsoft BI Stack und SharePoint Lösungen. Dabei legt er als MCSE – Business Intelligence und MCSE – Data Plattform sowohl Wert auf die Administration als auch die Entwicklung von BI Systemen und kennt den SQL-Server seit der Version 6.5 und SharePoint seit dem Projekt „Tahoe“. Frank hält des Öfteren Vorträge auf Konferenzen, an Universitären oder Usergroup-Treffen und schreibt regelmäßig Artikel für verschiedene deutsche Fachzeitschriften. Außerdem hat er schon einige Bücher veröffentlicht, unter anderem hat er ein grundlegendes Buch zum Thema „Datenbanken“ geschrieben und ist Mitautor der „SharePoint für Dummies“-Bücher. Er gehört zu den Gründungsmitgliedern der PASS Deutschland e.V. und ist zusammen mit Klaus Höltgen Chapter Leader der Regionalgruppe Ruhrgebiet. Für seine Community Arbeit ist Frank im Juli 2014 zum MVP SQL Server ernannt worden.

    &

    Volker Hinz ist führender Lösungsberater für Business Intelligence im Großkundenvertrieb der Microsoft Deutschland GmbH. Architekturberatung rund um SQL, BI und Office-Themen sowie deren Integration in heutige Kundenumgebungen gehört zu seinen Schwerpunkten.

    Christoph Müller-Spengler: Is SQL Server under high Memory Pressure?

    A few weeks ago, i started a blog post series that covers the finding of performance bottlenecks in SQL Server. In the first blog post in the series https://cms4j.wordpress.com/2014/09/30/is-sql-server-under-high-cpu-pressure/
    i focused on CPU Pressure.

    In this blog post i will explain how to decide from just a few queries to SQL Server DMVs if SQL Server is under high Memory pressure.

    We all know there are far more reasons why SQL Server could be slow, but let’s stick with Memory for now.

    Page Life Expectancy

    A first and very good indicator is the so called Page Life Expectancy (PLE) that can be queried by the DMV sys.dm_os_performance_counters. The Page Life Expectancy tells us how long a data page that was read from disk subsystem will stay in SQL Server’s Buffer Pool.
    In former times when SQL Server just had about 4 GB of RAM a value of 300 (seconds) was recommended by Microsoft. But times are changing, so this is no more longer the amount of RAM that you will find in nowadays SQL Servers.

    Jonathan Kehayias has a very good blog post about this:
    http://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/ -> What’s Wrong about Page Life Expectancy >= 300?

    So depending on your Servers RAM you have to define a different value than 300.
    Jonathan calculates
    Recommended PageLifeExpectancy = Physical RAM / 4 * 300

    Top Wait Type

    SQL Server always provides you with statistics what he is waiting on for the most of the time. This can be queried from the DMV sys.dm_os_wait_stats.
    With high memory pressure i often experienced the Wait Type PAGEIOLATCH_XX as the top wait type.
    This is because not all data pages that SQL Server wants to hold in the Buffer Pool fit into it – so SQL Server has to drop some pages from the Buffer Pool in order to read some new fresh pages from the disk subsystem.
    Please note that this Wait Type must not always mean that you have Memory Pressure. It could also be a slow disk subsystem or a poor indexing strategy, because there are too many table scans instead of index seeks.
    Please take a look at Paul Randals blog post http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/.
    I added two more wait types to the “benign” waits: “BACKUPBUFFER” and “BACKUPBIO” as the backup process does not make use of the Buffer Pool.

    Buffer Pool Rate

    Joining the discussion of the right value for Page Life Expectancy Thomas LaRock brought up another calculation for his so called Buffer Pool Rate. In his blog post
    http://thomaslarock.com/2012/05/are-you-using-the-right-sql-server-performance-metrics/ -> 5. BUFFER POOL I/O RATE
    he develops this value as
    MAX MEMORY / PLE = xx.x MB/sec
    He likes to have it below 20 MB/sec.

    Building the T-SQL Script

    So with these three criteria i built a script.

    /*
    	CMS, 05.12.2014:
    	Is SQL Server under Memory Pressure?
        
        This check consists of 3 Steps:
        1) Check Actual Page Life Expectancy
        2) Check Top Wait Stat                  - with the help of Paul Randal's Wait Stats CHECK
    
    http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
    
        3) Check Buffer Pool Rate               - With the help of SQL Rockstars Buffer Pool Rate Calculation
                                                  http://thomaslarock.com/2012/05/are-you-using-the-right-sql-server-performance-metrics/ -> Buffer Pool I/O Rate
        
    */
    
    SET NOCOUNT ON
    
    DECLARE @MaxServerMemory FLOAT
    DECLARE @ActualPageLifeExpectancy FLOAT
    DECLARE @RecommendedPageLifeExpectancy FLOAT
    DECLARE @RecommendedMemory FLOAT
    DECLARE @TopWaitType sysname
    DECLARE @BufferPoolRate FLOAT
    
    -- ####################################################################################################
    SELECT  @MaxServerMemory = (1.0 * cntr_value / 1024 / 1024)
    		, @RecommendedPageLifeExpectancy = Convert(INT ,(1.0 * cntr_value) / 1024 / 1024 / 4.0 * 300)
    FROM    sys.dm_os_performance_counters
    WHERE   counter_name = 'Target Server Memory (KB)'
    
    SELECT  @ActualPageLifeExpectancy = 1.0 * cntr_value
    FROM    sys.dm_os_performance_counters
    WHERE   object_name LIKE '%Buffer Manager%'
    AND		LOWER(counter_name) = 'page life expectancy'
    
    -- ####################################################################################################
    /*
    Check TOP Wait Type.
    If the TOP 1 Wait Type is PAGEIOLATCH_SH it indicates that SQL Server is waiting on datapages to be read 
    into the Buffer Pool.
    Memory Pressure might not be the root cause, it can be a poor indexing strategy
    because way too many pages have to be read into the buffer pool and they are deleted from the buffer pool too early.
    */
    ;WITH [Waits] AS
        (SELECT
            [wait_type],
            [wait_time_ms] / 1000.0 AS [WaitS],
            ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
            [signal_wait_time_ms] / 1000.0 AS [SignalS],
            [waiting_tasks_count] AS [WaitCount],
            100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
            ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
        FROM sys.dm_os_wait_stats
        WHERE [wait_type] NOT IN (
            N'BROKER_EVENTHANDLER',             N'BROKER_RECEIVE_WAITFOR',
            N'BROKER_TASK_STOP',                N'BROKER_TO_FLUSH',
            N'BROKER_TRANSMITTER',              N'CHECKPOINT_QUEUE',
            N'CHKPT',                           N'CLR_AUTO_EVENT',
            N'CLR_MANUAL_EVENT',                N'CLR_SEMAPHORE',
            N'DBMIRROR_DBM_EVENT',              N'DBMIRROR_EVENTS_QUEUE',
            N'DBMIRROR_WORKER_QUEUE',           N'DBMIRRORING_CMD',
            N'DIRTY_PAGE_POLL',                 N'DISPATCHER_QUEUE_SEMAPHORE',
            N'EXECSYNC',                        N'FSAGENT',
            N'FT_IFTS_SCHEDULER_IDLE_WAIT',     N'FT_IFTSHC_MUTEX',
            N'HADR_CLUSAPI_CALL',               N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
            N'HADR_LOGCAPTURE_WAIT',            N'HADR_NOTIFICATION_DEQUEUE',
            N'HADR_TIMER_TASK',                 N'HADR_WORK_QUEUE',
            N'KSOURCE_WAKEUP',                  N'LAZYWRITER_SLEEP',
            N'LOGMGR_QUEUE',                    N'ONDEMAND_TASK_QUEUE',
            N'PWAIT_ALL_COMPONENTS_INITIALIZED',
            N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
            N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
            N'REQUEST_FOR_DEADLOCK_SEARCH',     N'RESOURCE_QUEUE',
            N'SERVER_IDLE_CHECK',               N'SLEEP_BPOOL_FLUSH',
            N'SLEEP_DBSTARTUP',                 N'SLEEP_DCOMSTARTUP',
            N'SLEEP_MASTERDBREADY',             N'SLEEP_MASTERMDREADY',
            N'SLEEP_MASTERUPGRADED',            N'SLEEP_MSDBSTARTUP',
            N'SLEEP_SYSTEMTASK',                N'SLEEP_TASK',
            N'SLEEP_TEMPDBSTARTUP',             N'SNI_HTTP_ACCEPT',
            N'SP_SERVER_DIAGNOSTICS_SLEEP',     N'SQLTRACE_BUFFER_FLUSH',
            N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
            N'SQLTRACE_WAIT_ENTRIES',           N'WAIT_FOR_RESULTS',
            N'WAITFOR',                         N'WAITFOR_TASKSHUTDOWN',
            N'WAIT_XTP_HOST_WAIT',              N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
            N'WAIT_XTP_CKPT_CLOSE',             N'XE_DISPATCHER_JOIN',
            N'XE_DISPATCHER_WAIT',              N'XE_TIMER_EVENT')
        -- @cms4j: do not take BACKUP into Account, as it does not use the Buffer Cache
    	AND  [wait_type] NOT IN (
            N'BACKUPBUFFER', N'BACKUPIO')
        AND [waiting_tasks_count] > 0
     )
    SELECT @TopWaitType = (SELECT top 1
        MAX ([W1].[wait_type])
        FROM [Waits] AS [W1]
    INNER JOIN [Waits] AS [W2]
        ON [W2].[RowNum] <= [W1].[RowNum]
    GROUP BY [W1].[RowNum]
    HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95); -- percentage threshold
    
    
    -- ####################################################################################################
    -- SQL Rockstar comes up with BUFFER POOL RATE. But that depends on SUPER DUPER STORAGE. If that is low, the BufferPoolRate will also be slow.
    SELECT  @BufferPoolRate = ( 1.0 * cntr_value / 128.0 )
            / ( SELECT  1.0 * cntr_value
                FROM    sys.dm_os_performance_counters
                WHERE   object_name LIKE '%Buffer Manager%'
                        AND LOWER(counter_name) = 'page life expectancy' )
    		FROM    sys.dm_os_performance_counters
    WHERE   object_name LIKE '%Buffer Manager%'
            AND counter_name = 'Database pages'
    
    -- ####################################################################################################
    -- Calculate Recommended Max Memory
    SELECT @RecommendedMemory = Convert(INT, @RecommendedPageLifeExpectancy / @ActualPageLifeExpectancy * @MaxServerMemory)
    
    -- ####################################################################################################
    -- Put all the things together...
    SELECT	CASE
    			WHEN @RecommendedMemory > @MaxServerMemory AND @TopWaitType LIKE 'PAGEIOLATCH_%' AND @BufferPoolRate > 20.0 THEN '3 x YES'
                WHEN
    				(@RecommendedMemory > @MaxServerMemory AND @TopWaitType LIKE 'PAGEIOLATCH_%')
    				OR
    				(@RecommendedMemory > @MaxServerMemory AND @BufferPoolRate > 20.0) THEN '2 x YES'
                WHEN @RecommendedMemory > @MaxServerMemory AND @TopWaitType NOT LIKE 'PAGEIOLATCH_%' THEN '1 x YES, TOP Wait: ' + @TopWaitType
                WHEN @RecommendedMemory < @MaxServerMemory  THEN 'no'
            END AS [Is SQL Server under Memory Pressure?]
            , @MaxServerMemory AS [Max Server Memory (GB)]
            , @ActualPageLifeExpectancy AS [Actual PLE]
            , @RecommendedPageLifeExpectancy AS [Recommended PLE]
            , @RecommendedMemory AS [Recommended Memory (GB)]
            , @TopWaitType AS [Top Wait Type]
            , @BufferPoolRate AS [BufferPool Rate, you want < 20]
    
    

    So if all of the three criteria equal to “true” the script will show ‘3 x YES’.
    If it’s Page Life Expectancy and PAGEIOLATCH_XX or Page Life Expectancy and Buffer Pool Rate, you will get ‘2 x YES’.
    Otherwise you will get other results as shown in the script.

    Please keep in mind that this is just the starting point where you have to investigate further and proove your first results with more detailed reasons why SQL Server is under high Memory Pressure.

    Thank you for reading,
    Christoph Müller-Spengler

    Other blog posts in that series:


    Sascha Lorenz: Azure ML (Machine Learning) – SQL Server Konferenz 2015

    120x600_SQL_Server_Konferenz7[4]Wie ich auch schon in diesem Blogpost angemerkt habe, ist die kommende SQL Server Konferenz in Darmstadt ein ganz besonderes Event. Daher möchte ich in den kommenden Tagen einige Sessions im Detail vorstellen.

    ”How to do Analytics with Azure ML
    Diese Session zeigt End-to-End wie man große Datenmengen mit Azure ML analysiert, Vorhersagen generiert und diese in Power BI visualisieren und aus Geschäftsanwendungen nutzen kann. Demo-lastig. “

    Von:

    Artus Krohn-Grimberghe
    Dr. Artus Krohn-Grimberghe has spent the past 6 years immersed in advanced data mining topics. He currently works as assistant professor for Analytic Information Systems and Business Intelligence in Germany. His research focuses on data mining algorithms for e-commerce and mobile business. When he isn’t researching new algorithms or teaching, he is consulting for predictive analytics clients.

    Martin Vach
    Martin Vach ist führender Lösungsberater für Business Intelligence im Großkundenvertrieb der Microsoft Deutschland GmbH. Architekturberatung rund um SQL, BI und Office-Themen sowie deren Integration in heutige Kundenumgebungen gehört zu seinen Schwerpunkten.

    Sascha Lorenz: Azure Search – SQL Server Konferenz 2015

    120x600_SQL_Server_Konferenz7Wie ich auch schon in diesem Blogpost angemerkt habe, ist die kommende SQL Server Konferenz in Darmstadt ein ganz besonderes Event. Daher möchte ich in den kommenden Tagen einige Sessions im Detail vorstellen.

    “Looking for Answers with Azure Search
    Want to learn how to search your massive data repositories in Azure?
    Want to learn how to find value from your data quickly?
    Azure search is a new offering from Microsoft. Being able to search your data repositories at scale is something Azure cloud users have been screaming for. Well now it is here so come to this session and learn how to use Azure search.”

    Von Allan Mitchell

    “I work with companies helping them to decide how best to store their data
    Key/Value (Redis)
    Document (DocumentDB, Mongo)
    Relational (SQL Server)
    NoSQL (Hadoop/HBase)
    I also help customers to decide on whether a move to cloud based offerings makes sense. The Azure platform from Microsoft has a lot to offer and it isn't necessarily a Cloud/On Premise binary choice. Hybrid scenarios make a lot of sense too. I also work with customers who have large scale or interesting data movement requirements. This could be batch processes which are complex or real-time sensor and telemetry data.”

    Sascha Lorenz: Azure Data Factory und SSIS – SQL Server Konferenz 2015

    120x600_SQL_Server_Konferenz

    Wie ich auch schon in diesem Blogpost angemerkt habe, ist die kommende SQL Server Konferenz in Darmstadt ein ganz besonderes Event. Daher möchte ich in den kommenden Tagen einige Sessions im Detail vorstellen.

    Azure Data Factory und SSIS: Neue Wege zur Ochestrierung von Cloud und On Prem

    Mit der Azure Data Factory (ADF) hat Microsoft einen neuen Managed Service in Azure herausgebracht, welcher für das Laden und Aufbereiten von Datenströmen aus der Cloud und on Prem neue Wege geht. In dieser Session werden wir einen tieferen Einblick in die Technologie erlauben, schauen uns LIVE an wie man mit JSON und PowerShell eine "Fabrik" erstellt, Pipelines baut und den Betrieb monitored. Natürlich blicken wir auch in Richtung Zukunft und auf das was ADF im Unterschied zu unserem "Liebling" SSIS bietet.

    Von Tillmann Eitelberg & Oliver Engels :

    Tillmann Eitelberg ist Geschäftsführer der oh22information services GmbH und hat sich mit seinem Unternehmen auf die Themengebiete Datenqualität, Datenintegration und Master Data spezialisiert. Er schreibt regelmäßig über diese Themen sowie seine kleine Leidenschaft Spatial Data in seinem Blog unter www.ssis-components.net und hat verschiedene SSIS Komponenten bei Codeplex veröffentlicht. Tillmann ist SQL Server MVP. Den PASS Deutschland unterstützt er als 2. Vorsitzender, als Chapter Leader für die Region Köln/Bonn/Düsseldorf sowie als Regional Mentor Germany für PASS Global.

    Oliver Engels ist Vorstandsvorsitzender der oh22data AG, Bad Camberg im Taunus. Er gehört zur Spezies der Betriebswirte, die es nicht lassen können, sich mit IT Themen auseinander zu setzen. Das macht er jetzt bereits seit über 20 Jahren mit dem Schwerpunkt auf Marketing- und Vertriebssystemen.
    Er beschäftigt sich intensiv mit CRM, Datenqualität, Business Intelligence, Enterprise Reporting und der Analyse von Marketing- und Vertriebsdaten. Den MS SQL Server beschäftigt er seit der Version 4. Er gehört zu den Gründungsmitgliedern der PASS Deutschland, ist 1. Vorsitzender, Chapter Leader Rhein/Main sowie Regional Mentor Germany für PASS Global. Oliver ist SQL Server MVP und im Microsoft Advisory Board für SQL Information Services. Wenn er sich mal nicht mit Daten beschäftigt, dann ist er draußen in der Natur, sei es mit seinen Golden Retrievern im Wald oder mit den Wanderstiefeln auf matschigen Trails.

    Sascha Lorenz: SQL Server Konferenz 2015 – I’m speaking

    SQL_Server_Konferenz_ImSpeaking

    Die Zeit rennt mal wieder. Die nächste SQL Server Konferenz ist schon wieder komplett geplant. Am 03. – 05.02.2015 in Darmstadt im darmstadtium gibt es wieder zahlreiche Sessions zum Thema Microsoft SQL Server und Data Plattform.

    Die SQL Server Konferenz in Zahlen zusammengefasst: 40 Sessions, 4 Tracks, 38 Sprecher, 20 MVPs, 17 internationale Sprecher und damit ist die SQL Konferenz ein internationale Format geworden und für unsere Region einmalig. Und das Ganze zu einem Hammerpreis!! 

    Ich bin wieder als Sprecher für die Konferenz ausgewählt worden, was mich sehr freut. Es wird um das sehr spannende Thema Data Vault gehen. Den Vortrag werde ich gemeinsam mit meinem PSG Kollegen Jan Streblow halten, welcher auch Erfahrung in Projekten mit diesem Data Warehouse Ansatz sammeln konnte.

    “Data Vault in der Praxis mit dem SQL Server

    Data Vault ist als alternativer Modellierungsansatz für Data Warehouse Umgebungen mittlerweile sehr bekannt geworden. Dennoch fehlt vielen Teams noch die Praxis zur Bewertung der Methode, um in der Architekturphase eines Data Warehouse eine Entscheidung begründen zu können.

    Diese Session gibt Entscheidern und Entwicklern einen Überblick über die in der Methode vorgeschlagenen Artefakte, skizziert einen Architekturansatz für das sogenannte Core Data Warehouse und zeigt die Vorteile auf gegenüber der klassischen Denormalisierung im Data Warehouse in der Realisierung mit dem SQL Server und den SQL Server Integrationen Services.

    Gezeigt werden Beispiele aus den Bereichen der privaten Wirtschaft und der öffentlicher Verwaltung.”

    Dirk Hondong: SQLKonferenz 2015: der SQLPaparazzo sucht Paparazzi

    Hallo liebe PASS Deutschland Community,

    sicherlich habt Ihr es schon mitbekommen: die nächste SQLKonferenz ist in Sicht mit einer PreCon am 3. Februar und 2 darauf folgenden Konferenztagen. Veranstaltungsort ist, wie auch 2014, das Darmstadtium

    SQLKonferenz_2014_Montag_043

    und die Agenda ist echt der Wahnsinn.

    Nachdem ich auf der SQLKonferenz 2014 und auch dem SQLSaturday #313 an der Hochschule Bonn-Rhein-Sieg als quasi als “Einzelkämpfer” unterwegs war, um die Events ein wenig in Bildern festzuhalten, suche ich für das Event 2015 wieder ein wenig Unterstützung. Wer schon 2014 auf der Konferenz dabei war, hat mich ja ständig durch das Darmstadtium wuseln sehen. Ich bin daher wohl auch der einzige Teilnehmer, der von sich behaupten kann, alle Sessions besucht zu haben. Jedoch ist das auf Dauer schon ein wenig anstrengend und einer einzelnen Session von A bis Z folgen klappt dann leider nicht.

    Von daher frage ich einfach mal in die Community Runde, wer Interesse hat mich auf der SQLKonferenz 2015 zu unterstützen. Auf dem SQLSaturday #230 hatte das ja auch sehr gut geklappt.

    Rückmeldungen einfach via Kommentar, Twitter, Facebook, Email oder mir persönlich geben bei einem der nächsten SQLPass Events.

     

     


    Steffen Krause [MS]: Wieder da bei Microsoft

    Nur kurz: Ich bin wieder da - als Cloud Solution Architect für Microsoft Azure. More to come... Gruß, Steffen...(read more)

    Falk Krahl: Neues Update für SQL Server 2012 SP1

    Ebenfalls heute ist für den SQL Server 2012 mit Servicepack 1 ein neues Update erschienen. Es handelt sich um das kumulative Update 13. Es kann unter folgendem Link angefordert werden:
    SQL Server 2012 SP1 CU13

    Falk Krahl: Neues Update für SQL Server 2012 SP2

    Heute ist für den SQL Server 2012 mit Servicepack 2 ein neues Update erschienen. Es handelt sich um das kumulative Update 03. Es kann unter folgendem Link angefordert werden:
    SQL Server 2012 SP2 CU03

    Uwe Ricken: Logins und Serverrollen ermitteln

    Grundlage aller Serveranalysen im Rahmen von Sicherheitsüberprüfungen ist eine Übersicht über die verfügbaren Logins (Server Prinzipale) und ihre Gruppenzugehörigkeit. War es seit der Version 2005 bis zur Version 2008 R2 noch möglich, mit Hilfe einer PIVOT-Abfrage die Zugehörigkeiten zu festen Serverrollen abzufragen, wurde mit Version 2012 die erstmals die Möglichkeit gegeben, eigene Serverrollen zu definieren. Wie dennoch eine Übersicht aller Berechtigungen ausgegeben werden kann, zeigen die folgenden Skripte im Zusammenhang mit einer umfangreichen Erläuterung zu den festen Serverrollen in Microsoft SQL Server.

    Sicherheitsinformationen < 2012

    Die Rollen von Microsoft SQL Server bis zur Version 2008 (R2) waren vordefiniert und konnten nicht individuell erweitert werden. Von daher war eine Analyse der Rollenzugehörigkeit für die Versionen 2005 – 2008 R2 durch eine PIVOT-Abfragen möglich, wie das nachfolgende Skript zeigt:

    SELECT  login_name,
            login_type,
            [public],
            [sysadmin],
            [securityadmin],
            [serveradmin],
            [setupadmin],
            [processadmin],
            [diskadmin],
            [dbcreator],
            [bulkadmin]
    FROM
    (
        SELECT  SP.name         AS  login_name,
                SP.type         AS  login_type,
                'public'        AS  server_role,
                sp.principal_id
        FROM    sys.server_principals AS SP
        WHERE   type != 'R'
     
        UNION
     
        SELECT  SP.name         AS  login_name,
                SP.type         AS  login_type,
                role_list.name  AS  server_role,
                SP.principal_Id
        FROM    sys.server_principals AS SP LEFT JOIN
                (
                    SELECT  SRM.member_principal_id,
                            SR.name
                    FROM    sys.server_role_members AS SRM INNER JOIN sys.server_principals AS SR
                            ON (SRM.role_principal_id = SR.principal_id)
                ) AS role_list ON
                (sp.principal_id = role_list.member_principal_id)
        WHERE    SP.type != 'R'
    ) AS SecList
    PIVOT
    (
        COUNT(principal_id)
        FOR  server_role
        IN
        (
            [public],
            [sysadmin],
            [securityadmin],
            [serveradmin],
            [setupadmin],
            [processadmin],
            [diskadmin],
            [dbcreator],
            [bulkadmin]    
        )
    ) AS pvt
    ORDER BY
        login_type,
        login_name;

    Das Skript sieht auf dem ersten Blick umständlich aus da es durch die Verwendung einer PIVOT-Abfrage recht umfangreich und kompliziert erscheint. Prinzipiell gilt jedoch die besondere Beachtung den fest definierten Serverrollen. Diese “festen Serverrollen” sind in Microsoft SQL Server Standard und konnten bis zur Version Microsoft SQL Server 2012 nicht erweitert werden.


    Sicherheitsinformationen >= 2012


    Mit Microsoft SQL Server 2012 war es erstmals möglich, eigene Serverrollen zu definieren. Diese Möglichkeit wurde schon seit vielen Jahren gefordert, um in Enterprise-Umgebungen Möglichkeiten zu haben, granulare Berechtigungskonzepte für Administratoren festzulegen. Bisher war es immer notwendig, DBAs mit [sysadmin]-Berechtigungen auszustatten. Mit Microsoft SQL Server 2012 könnten erstmals dedizierte Berechtigungen erteilt werden, die sich nicht nur auf vordefinierte Bereiche erstrecken.


    Auf Grund der Variabilität der Serverrollen kann das zuvor verwendete Skript nicht mehr angewendet werden. Man kann nicht mehr von den fest definierten Serverrollen ausgehen sondern muss ebenfalls einkalkulieren, dass individuelle Serverrollen implementiert wurden. Das nachfolgende Skript reagiert auf diese Variabilität, indem eine Ergebnistabelle dynamisch erstellt wird.



    /* wenn die Ausgabetabelle bereits existiert, muss sie erst gelöscht werden */
    IF OBJECT_ID('tempdb..##Result') IS NOT NULL
        DROP TABLE ##Result;
        GO
     
    /*
        Erstellung der Ausgabetabelle basierend auf den vorhandenen Serverrollen
    */
    DECLARE    @stmt        NVARCHAR(4000);
    DECLARE    @col_enum    NVARCHAR(1000) = N'';
    SET        @stmt  =    'CREATE TABLE ##Result([PrincipalName] nvarchar(128) NOT NULL PRIMARY KEY CLUSTERED, login_type CHAR(5) NOT NULL $Column_Names);';
     
    SELECT  @col_enum = @col_enum + ', ' + QUOTENAME(name) + ' TINYINT NOT NULL DEFAULT (0)'
    FROM    sys.server_principals AS SP
    WHERE   type_desc = 'SERVER_ROLE'
    ORDER BY
            principal_id;
     
    /* Ersetzen der Textkomponente durch die tatsächlich zu erstellenden Attribute */
    SET   @stmt =  REPLACE(@stmt, '$Column_Names', @col_enum);
    EXEC  sp_executeSQL @stmt;
     
    /* Eintragen aller vorhandenen Logins des Servers */
    INSERT INTO ##Result ([PrincipalName], [login_type], [public])
    SELECT  name, type, ISNULL(IS_SRVROLEMEMBER('public', name), 0)
    FROM    sys.server_principals
    WHERE   type_desc != 'SERVER_ROLE';
     
    /* Ermittlung aller verfügbaren Serverrollen für jedes Login */
    DECLARE c CURSOR
    FOR
        SELECT  N'UPDATE ##Result SET ' + QUOTENAME (R.name) + ' = 1 WHERE [PrincipalName] = '+ QUOTENAME(L.name, '''') + ';'
        FROM    sys.server_principals AS L INNER JOIN sys.server_role_members AS SRM
                ON (l.principal_id = SRM.member_principal_id) INNER JOIN sys.server_principals AS R
                ON (SRM.role_principal_id = R.principal_id);
     
    OPEN c;
     
    FETCH NEXT FROM c INTO @stmt;
    WHILE @@FETCH_STATUS != -1
    BEGIN
        EXEC sp_executeSQL @stmt;
        FETCH NEXT FROM c INTO @stmt;
    END
     
    CLOSE c;
    DEALLOCATE c;
     
    /* Ausgabe des Ergebnisses */
    SELECT * FROM ##Result
    ORDER BY
        [login_type],
        [PrincipalName];

    Das Skript wirkt ebenfalls recht umfangreich, ist aber auf Grund seiner modularen Schritte recht einfach zu lesen. Im ersten Schritt wird eine globale temporäre Tabelle erstellt, die später für die Ausgabe vorgesehen ist. Die Besonderheit dieser Ausgabetabelle besteht aus drei Dingen:



    • es handelt sich um eine globale temporäre Tabelle, da später mit sp_executeSQL befüllt und aktualisiert wird. Da sp_executeSQL in einem eigenen Kontext ausgeführt wird, kann die Ausführung nur auf globale Objekte zugreifen, die vorhanden sind.
    • Im Ausführungsstring für die Erstellung der Tabelle [##Result] befindet sich ein Textelement “$Column_Names”. Dieses Textelement wird vor der Ausführung des Codes durch die Namen der vorhandenen Serverrollen ersetzt. Dadurch wird in der Tabelle für jede Serverrolle ein eigenes Attribut erzeugt.
    • SQL Injection kann mit großer Wahrscheinlichkeit ausgeschlossen werden, da die Attribute mit QUOTENAME() in eckige Klammern eingeschlossen werden und damit nicht als Codestrukturen erkannt werden.

    Ist die temporäre Tabelle ##Result erstellt, werden alle vorhandenen Logins in die Tabelle eingetragen. Da für alle Attribute, die Serverrollen repräsentieren der Standardwert 0 gilt, müssen abschließend die vorhandenen Zuordnungen von Serverlogins und Serverrollen ausgewertet und aktualisiert werden. Die Ermittlung dieser Informationen wird mit einem Cursor durchgeführt. Sobald die Evaluierungsphase abgeschlossen ist, werden die Informationen ausgegeben.


    Feste Serverrollen


    Für die Bewertung der Sicherheit eines vorhandenen Microsoft SQL Server ist diese Auswertung ein erster wichtiger Schritt, der jedoch noch weitere Prüfungen nach sich zieht. Hierzu zählen insbesondere die dedizierten Serverberechtigungen für jeden einzelnen Login. Microsoft SQL Server verfügt über Standardrollen für die Administration des Servers, die über unterschiedliche Berechtigungen verfügen. Die nachfolgende Liste beschreibt die festen Serverrollen in einem Microsoft SQL Server.


    sysadmin


    Die Mitglieder der festen Serverrolle [sysadmin] verfügen über ALLE Berechtigungen. Rechte von Mitgliedern der Serverrolle [sysadmin] können nicht eingeschränkt werden. Grundsätzlich sollten weder Applikationskonten noch Logins von Standardbenutzern über diese Rechte verfügen! Leider kommt es immer wieder vor (<=90%), dass Applikationen, die durch Softwarehersteller auf den Kundenservern implementiert werden, Logins verwenden, die Mitglied der festen Serverrolle [sysadmin] sind. Wenn ein Softwarehersteller nach diesen Rechten für seine Applikation fragt, sollte Vorsicht angebracht sein und in einer Gegenfrage die speziellen Berechtigungen für die Anwendung angefordert werden, die eine Mitgliedschaft in der Serverrolle [sysadmin] berechtigen. Ebenfalls impliziert die Mitgliedschaft in der festen Serverrolle [sysadmin] den Zugang zu allen Datenbanken und ihre Inhalte (Daten)!


    serveradmin


    Die Mitglieder dieser festen Serverrolle können – wie sich aus dem Namen der Serverrolle bereits ableiten lässt – die Konfiguration eines Servers ändern. Weiterhin besteht für Mitglieder der festen Serverrolle [serveradmin] die Möglichkeit, die Dienste des Microsoft SQL Server neu zu starten. Die Mitglieder der festen Serverrolle [serveradmin] sind trotz des Namens SEHR eingeschränkt. Ihre Berechtigungen erstrecken sich ausschließlich auf die Konfiguration von Einstellungen, die mittels sp_configure abgerufen werden können. Ein Mitglied der festen Serverrolle [serveradmin] kann weder Einstellungen für den SQL Agent konfigurieren noch Datenbanken oder neue Logins / Benutzer anlegen. Außerdem können keine globalen oder temporären Traceflags gesetzt werden.


    securityadmin


    Mitglieder der festen Serverrolle [securityadmin] können die Eigenschaften von Logins modifizieren. Jedoch können Mitglieder der festen Serverrolle [securityadmin] ein Login nicht einer Serverrollen zuweisen,  der er nicht selbst als Mitglied zugeordnet ist.


    Dennoch ist vor der Vergabe dieser Privilegien sorgfältig abzuwägen! Als Mitglied der festen Serverrolle [securityadmin] besteht die Möglichkeit, weitreichende Berechtigungen “durch die Hintertür” an andere Logins zu vergeben. Das nachfolgende Skript – ausgeführt als ein Mitglied der festen Serverrolle [securityadmin] – verdeutlicht die Problematik.



    -- Erstellen eines neuen Logins
    CREATE LOGIN TEST WITH PASSWORD = 'abc$123', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;
    GO
     
    -- Hinzufügen zur Serverrolle [securityadmin] funktioniert
    ALTER SERVER ROLE [securityadmin] ADD MEMBER [TEST];
     
    -- Hinzufügen zu Serverrolle [processadmin schlägt fehl,
    -- da nicht selbst Mitglied
    ALTER SERVER ROLE [processadmin] ADD MEMBER [TEST];
    GO
     
    -- Zuweisung von Berechtigungen auf Serverebene, die weit
    -- über die Berechtigungen der eigenen Serverrolle hinausgehen
    GRANT ALTER ANY DATABASE TO [TEST];
    GRANT ADMINISTER BULK OPERATIONS TO [TEST];
    GRANT CONTROL SERVER TO [TEST];
    GO

    Das Skript erstellt ein neues Login mit dem Namen [Test]. Anschließend sollen diesem neuen Login zwei Serverrollen zugewiesen werden. Die Zuweisung zur ersten Gruppe funktioniert einwandfrei, da der ausführende Benutzer selbst Mitglied der Serverrolle [securityadmin] ist. Der zweite Aufruf schlägt fehl, da der ausführende Benutzer nicht selbst über die notwendigen Berechtigungen verfügt.


    Problematisch wird die Zuweisung von serverweiten Berechtigungen. Die letzten drei Befehle können von einem Mitglied der festen Serverrolle [securityadmin] ausgeführt und umgesetzt werden. Somit verfügt der neue Login z. B. über die Möglichkeit der Serverkontrolle und somit über die Möglichkeit der Verwendung von “Dynamic Management Objects”, die einem Mitglied der festen Serverrolle [securityadmin] versagt sind. Da ein Mitglied der festen Serverrolle [securityadmin] das Login selbst erstellt hat, kann er sich nun mit diesem Konto anmelden und anschließend die Serverinformationen abfragen oder aber – wie das obige Beispiel zeigt – mit diesem Konto den SQL Server kontrollieren. Microsoft verweist ausdrücklich auf die erweiterten Sicherheitsbedenken in der OH:


    Durch die Möglichkeit, Zugriff auf Database Engine (Datenbankmodul) zu gewähren und Benutzerberechtigungen zu konfigurieren, kann der Sicherheitsadministrator die meisten Serverberechtigungen zuweisen.Die Rolle securityadmin muss als Entsprechung der Rolle sysadmin behandelt werden.


    processadmin


    Den Mitgliedern der festen Serverrolle [processadmin] ist es erlaubt, aktive Prozesse (Threads) zu beenden indem Sie mit KILL die entsprechende Session beenden. Diese Berechtigung hört sich zunächst recht harmlos an, hat aber – aus meiner Sicht – erhebliche Mankos in Bezug auf die Datensicherheit. Mit der Mitgliedschaft in der festen Serverrolle [processadmin] geht einher, dass das Login nicht nur Prozesse beenden kann sondern mittels “Dynamic Management Objects” ebenfalls Möglichkeiten besitzt, bestehende Sessions zu überwachten und zu analysieren! Das nachfolgende Skript kann ein Mitglied der festen Serverrolle [processadmin] ohne Probleme ausführen:



    SELECT  DES.session_id,
            DEC.last_read,
            DEC.last_write,
            DEST.text            AS last_command,
            req_command.text     AS current_command,
            DEQP.query_plan      AS current_plan,
            DER.last_wait_type
    FROM    sys.dm_exec_connections AS DEC INNER JOIN sys.dm_exec_sessions DES
            ON (DEC.session_id = DES.session_id) LEFT JOIN sys.dm_exec_requests AS DER
            ON (
                 DEC.connection_id = DER.connection_id AND
                 DEC.most_recent_session_id = DER.session_id
               )
            CROSS APPLY sys.dm_exec_sql_text(DEC.most_recent_sql_handle) AS DEST
            OUTER APPLY sys.dm_exec_sql_text(DER.sql_handle) AS req_command
            OUTER APPLY sys.dm_exec_query_plan(DER.plan_handle) AS DEQP
    WHERE   DES.is_user_process = 1;

    Das ein Mitglied der festen Serverrolle [processadmin] überprüfen muss, wann eine Session zum letzten Mal aktiv war oder worauf eine aktive Sitzung zuletzt gewartet hat, ist unbestreitbar eine Grundvoraussetzung für ein verantwortliches Handeln. Problematisch wird es – aus meiner Sicht – jedoch, wenn es darum geht, auch die Ausführungsbefehle sichtbar zu machen. Hier geht die Berechtigung eines Mitglieds der festen Serverrolle [processadmin] zu weit, da durch die Auswertung von SQL Befehlen Informationen preis gegeben werden, die u. U. nicht für Dritte zugänglich sein sollten (z. B. ein UPDATE einer Mitarbeitertabelle, in der Gehälter gespeichert werden).


    setupadmin


    Die Mitglieder der festen Serverrollen [setupadmin] haben die Möglichkeit, Verbindungsserver (“Linked Server”) hinzuzufügen.


    bulkadmin


    Auch hier ist der Name selbst erklärend. Mitglieder dies festen Serverrolle [bulkadmin] verfügen über die Berechtigung, BULK INSERT Operationen ausführen zu können. In Seminaren taucht immer wieder die Frage auf, warum es diese Rolle überhaupt gibt. Die Antwort ist recht einfach: Mit BULK INSERT werden externe Datendateien in Datenbanken importiert. Ein Mitglied der festen Serverrolle [bulkadmin] erhält – indirekt – einen Zugriff auf Ressourcen, die sich außerhalb von Microsoft SQL Server befinden.


    diskadmin


    Mitglieder der festen Serverrolle [diskadmin] verfügen über die Berechtigung zur Verwaltung von Datenträgerdateien. Diese Serverrolle ist aus meiner Sicht wertlos, da sie einzig die Berechtigung zur Verwaltung von BACKUP-Medien erteilt. Sollte man Mitglied der festen Serverrolle [diskadmin] sein, so sollte man bereits einige Erfahrung mit T-SQL haben; die Verwendung von SQL Server Management Studio (SSMS) ist trotz Mitgliedschaft in der festen Serverrolle [diskadmin] nicht möglich! Das SSMS in Bezug auf Verwaltung seine Tücken hat, habe ich bereits im Artikel “GUI vs. T-SQL – trotz dbo / db_owner keine Berechtigungen?” beschrieben.


    Versucht man, als Mitglied der festen Serverrolle [diskadmin] über SSMS ein neues BACKUP Medium anzulegen, erhält man die folgende Fehlermeldung:


    CREATE NEW BACKUP MEDIA


    Auch die Verwaltung bereits bestehender Backupmedien gestaltet sich schwierig, wenn nicht zusätzlich zur bestehenden Mitgliedschaft in der festen Serverrolle [diskadmin] die Mitgliedschaft in der festen Serverrolle [dbcreator] besteht!


    CHECK BACKUP MEDIA


    Um ein Backupmedium zu erstellen, muss also mit T-SQL gearbeitet werden



    EXEC sp_addumpdevice @devtype = 'disk', @logicalname = 'DB_BACKUP', @physicalname = 'S:\backup\db_backup.med';

    Mit sp_addumpdevice ist es möglich, ein BACKUP-Medium zu erstellen; auf Grund der Voraussetzung weiterer Berechtigungen sind weitere Operationen wie BACKUP und RESTORE nicht möglich! Hierzu bedarf es mindestens der Zugehörigkeit zur festen Serverrolle [dbcreator].


    dbcreator


    Mitglieder der festen Serverrolle [dbcreator] können beliebige Datenbanken erstellen, ändern, löschen und wiederherstellen. Diese – eher unscheinbare – Berechtigung sollte mit Sorgfalt zugeteilt werden. Ein Mitglied der festen Datenbankrolle [dbcreator] kann von JEDER Datenbank eine Datensicherung machen. Insbesondere bei Datenbanken, die im vollständigen Wiederherstellungsmodus laufen, sollte ein Mitglied der festen Datenbankrolle [dbcreator] darauf aufmerksam gemacht werden, dass BACKUP-Befehle immer mit der Option COPY_ONLY ausgeführt werden sollten um die Sicherungskette der Protokollsicherungen nicht zu unterbrechen.


    Ein zusätzliches Manko der festen Serverrolle [dbcreator] ist die fehlende Möglichkeit, den Eigentümer einer neu angelegten Datenbank nachträglich zu ändern. Diese Berechtigung fehlt einem Mitglied der festen Datenbankrolle [dbcreator] nämlich trotz “... beliebige Datenbanken erstellen, ändern, löschen ...”.


    Einen Vorteil hat die feste Serverrolle [dbcreator] im Zusammenhang mit der Wiederherstellung von Datenbanken jedoch... führt ein Mitglied der festen Serverrolle [sysadmin] eine Wiederherstellung einer Datenbank von einer Datensicherung aus, so wird – automatisch – der ausführende Benutzer zum Eigentümer der Datenbank. Führt ein Mitglied der festen Datenbankrolle [dbcreator] eine Wiederherstellung einer Datenbank aus einer Datensicherung aus, so bleibt der ursprüngliche Eigentümer der Datenbank erhalten. Sehr bedauerlich, dass diese Einstellung nicht grundsätzlich die Standardeinstellung ist. Unabhängig davon, ob ein Mitglied der festen Serverrolle [sysadmin] oder ein Mitglied der festen Serverrolle [dbcreator] die Wiederherstellung initiiert sollte der ursprüngliche Eigentümer einer Datenbank bei einer Wiederherstellung erhalten bleiben.


    Herzlichen Dank fürs Lesen!

    Marcel Franke: What’s your passion in life?

    This morning I have seen an interesting interview with Microsoft’s CEO Satya Nadella at the University of Berlin (Satya Nadella as guest at TU Berlin) and he was talking about the new generation, technologies, students, changing business models and passion. So it made me think about my passion and what makes me happy every day, even if I need to stand up at 5 in the morning (of course Smile)?

    20140221_pmONE_Scharmuetzelsee_Veranstaltung-53

    So here are my 4 major passions:

    • It’s about my customers: I really like the time in which we live now. We can see a big paradigm shift in the area of Digital Enterprises, Big Data, Data Analytics and the Internet of things. So a lot of fascinating projects happens these days and I feel very lucky to be part of some of them.
    • It’s about my team: Because this new world is so fast changing and challenging it’s so important to have a great team in order to be successful. And what I like in my team is, that we share the same passion. And if you share the same passion, you can achieve fantastic things and have a lot of fun and success.
    • It’s about technologies: I have a good technical background and focused my research in the last month on Data Analytics, Big Data, Machine Learning and new technologies that came up with the evolution of the cloud technologies. And there is a great video I always remember from Lars Thomsen about “520 weeks of future”: 520 Wochen Zukunft — die zweite Dekade der grossen Chancen. Unfortunately this video is in German, but it makes me think.
    • It’s about education: I started to learn to self educate myself at my first days at university. All of my colleagues were familiar with the Internet, websites, mp3 and all the great things in the year of 2000. So I had to catch up. And after some years in consulting with lot’s of experiences I got in so many projects, I felt that it’s now time to share these experiences with others and help educating our new generation. That’s why I started to give trainings, talks on conferences, published whitepapers and started to write a blog.

    So what are yours? What makes you excited in your day to day job?

    Have fun…


    Filed under: This & that

    Uwe Ricken: Rangfolge von Datentypen – Auswirkung auf Abfragen

    In dem zuvor geschriebenen Artikel “Warum korrekte Datentypen für WHERE-Klauseln wichtig sind” wurde beschrieben, wie wichtig es ist, dass in den WHERE-Klauseln immer die richtigen Datentypen verwendet werden. Dieser Artikel zeigt, dass neben der Wahl der korrekten Datentypen bei Abfragen auch die Datentypen selbst eine wichtige Rolle für eine optimale Ausführung spielen.

    Wer bei der Abfrage von Daten nicht den richtigen Datentypen beachtet, kann recht schnell in die Verlegenheit kommen, dass ein performanter INDEX SEEK zu einem INDEX SCAN werden kann. Das Beispiel zeigt, wie die Rangfolge der verwendeten Datentypen die Ausführung von Abfragen in Verbindung mit falschen Datentypen für den Abfragewert beeinflusst.

    Testumgebung

    Für die Demonstration des oben beschriebenen Themas wird eine Tabelle mit 1.000 Datensätzen erstellt. Anschließend wird ein non clustered Index auf dem Attribut [c2] erstellt, um die unterschiedlichen Ausführungsszenarien zu untersuchen.

    -- Testtabelle
    CREATE TABLE dbo.foo
    (
        Id INT          NOT NULL    IDENTITY (1, 1),
        c1 MONEY        NOT NULL    DEFAULT (100),
        c2 VARCHAR(20)  NOT NULL
    );
    GO
     
    -- Eintragen von 1.000 Datensätzen
    DECLARE @i INT = 1;
    DECLARE @sn INT;
    WHILE @i <= 1000
    BEGIN
        SET @sn = RAND() * 1000
        INSERT INTO dbo.foo (c2) VALUES (CAST(@sn AS VARCHAR(20)));
        SET @i += 1;
    END
    GO
     
    -- Erstellung von Index für Datenzugriff
    CREATE INDEX ix_foo_c2 ON dbo.foo (c2) INCLUDE (c1);
    GO

    Sobald die Tabelle erstellt und die Daten eingetragen sind, werden die beiden folgenden Abfragen ausgeführt...



    SELECT c1, c2 FROM dbo.foo WHERE c2 = 790;
    SELECT c1, c2 FROM dbo.foo WHERE c2 = '790';
    GO

    Es wird der folgende Ausführungsplan für die Abfragen generiert:


    EXECUTIONPLAN_01


    Scheinbar gleiche Abfragen erzeugen unterschiedliche Ausführungspläne, dessen ursprünglicher Grund bereits in dem zuvor angegebenen Artikel beschrieben wurde. Die erste Abfrage muss einen – kostspieligen – INDEX SCAN durchführen, da jeder Wert im Attribut [c2] explizit in den Datentypen [INT] umgewandelt werden muss; somit ist eine gezielte Suche im Index nicht möglich. In der zweiten Abfrage wurde der korrekte Datentyp verwendet und somit kann ein effizienter INDEX SEEK angewendet werden. Wie schaut es aber aus, wenn der korrekte Datentyp kein String sondern ein numerischer Wert ist?


    Um diese Frage zu beantworten, wird der Datentyp von Attribut [c2] in einen “INT” umgewandelt. Damit der Datentyp geändert werden kann, dürfen keine Objekte auf das Attribut verweisen. Aus diesem Grund muss der Index [ix_foo_c2] gelöscht werden. Nach der Änderung des Datentypen kann der Index erneut erstellt werden.



    -- Löschen des Index auf Attribut [c2]
    DROP INDEX ix_foo_c2 ON dbo.foo;
    GO
     
    -- Änderung des Datentypen von varchar zu int
    ALTER TABLE dbo.foo ALTER COLUMN c2 INT NOT NULL;
     
    -- Erstellung des Index ix_foo_c2
    CREATE INDEX ix_foo_c2 ON dbo.foo (c2) INCLUDE (c1);
    GO

    Sobald die Änderungen abgeschlossen sind, können die Abfragen erneut ausgeführt werden. Tatsächlich hat sich auf Grund der Änderung der Datentypen der Ausführungsplan für den “falschen” Datentypen geändert; er führt TROTZ falschen Datentypen einen performanten INDEX SEEK aus.


    EXECUTIONPLAN_02


    Verrückte Welt, oder? Warum wird (entgegen der Erwartungen) trotz eines falschen Datentypen ein INDEX SEEK ausgeführt. Die Antwort liegt in den Details des Ausführungsplans versteckt.


    EXECUTION_PROPERTIES_01


    Interessant ist die Analyse des SEEK-Prädikats. Es ist erkennbar, dass im SEEK-Prädikat der Datentyp des zu suchenden Wertes konvertiert wird und nicht die Werte im Attribut [c2].


    Dieser Umstand führt dazu, dass Microsoft SQL Server einen performanten INDEX SEEK durchführen kann. Es muss nicht jeder Wert in [c2] konvertiert werden sondern der “Suchwert” wird zum Datentypen konvertiert, der dem Datentypen von [c2] entspricht. Dieses Verhalten ist der Rangfolge der Datentypen geschuldet, die von Microsoft SQL Server unterstützt werden.


    Allgemein werden Textdatentypen immer nachrangig zu numerischen Datentypen behandelt; somit müssen sich diese Datentypen einer Konvertierung unterwerfen, wenn sie mit Werten zusammenarbeiten müssen, die in der Rangfolge über dem eigenen Datentypen stehen. Die von Microsoft SQL Server verwendete Rangfolge stellt sich wie folgt dar:


    1.  user-defined data types (highest)
    ...
    5.  datetime2
    ...
    10. float
    ...
    15. int
    ...
    19. ntext
    ...
    23. uniqueidentifier
    24. nvarchar (including nvarchar(max) )
    26. varchar (including varchar(max) )
    ...
    29. binary (lowest)


    Die vollständige Liste der Rangfolge für Datentypen findet sich hier “Data Type Precedence (Transact-SQL)”. Für das angeführte Beispiel ist erkennbar, dass der Datentyp [varchar] nachrangig zum Datentypen [int] ist; er muss sich “unterordnen” und wird – sofern möglich – zu einem numerischen Datentypen konvertiert.


    Herzlichen Dank fürs Lesen!

    Falk Krahl: Neues Servicepack für SQL Server 2014

    Bereits am 21.10.2014 ist für den SQL Server 2014 ein neues Update erschienen. Es handelt sich dabei um das kumulative Update 04. Es kann unter folgendem Link angefordert werden.
    Kumulatives Update 04 für SQL Server 2014

    Andreas Wolter: Maximum number of rows per data page and minimal record size (SQL Server storage internals)

    Maximale Anzahl Zeilen je Datenseite und minimale Datensatzgröße

    (DE)
    In einer meiner letzten Master-Classes tauchte die nicht ganz ernste aber dennoch interessante Fragestellung auf:

    Wie viele Zeilen passen eigentlich maximal auf eine Datenseite? – wenn die Datensätze/Records so klein wie möglich sind.

    Zunächst, Part 1, was ist der kleinstmögliche Datensatz in SQL Server?

    Um das zu beurteilen, ist es gut, die Datentypen sowie die Struktur eines Datensatzes genau zu kennen.
    Man könnte versucht sein, anzunehmen, dass eine Spalte vom Datentyp bit der kleinstmögliche Datensatz ist. Der erfahrene SQL‘er wird wissen, dass ein bit allein auch immer mindestens 1 byte in einem Record benötigt – erst bei mehreren Spalten dieses Datentyps, kommt ein Platzersparnis ins Spiel (bit (Transact-SQL)).

    Der nächste Kandidat sind Datentypen, die laut der Liste in Books Online nur 1 byte Speicherplatz benötigen. Das wären folgende:

    (EN)
    In one of my last Master classes, a not quite so serious but nevertheless interesting question was brought up:

    How many rows maximally do actually fit on a data page? – if the data sets/records are as small as possible.

    First of all, part 1, what is the smallest possible data set in SQL Server?

    In order to assess this, it is commendable to know exactly the data types as well as the structure of a data set. 

    One could be tempted to assume that a column of the data type bit is the smallest possible data set. The experienced SQL people will know that a bit alone always also requires at least 1 byte in one record – only with several columns of this data type, the place-saving aspect comes into play (bit (Transact-SQL)).

    The next candidate are data types which according to the list in Books Online use only 1 byte of storage. These would be the following:

     

    • bit
    • char(1)
    • binary(1)
    • tinyint

     

    Tatsächlich benötigen in diesem besonderen Fall, einer einzigen Spalte je Datensatz, auch Records mit Nettowert von 2 Bytes, 9 Bytes auf der Datenseite:

    As a matter of fact, in this particular case of a single column per record, also records with a net value of 2 bytes use 9 bytes on the data page:

     

    • char(2)
    • nchar(1)
    • binary(2)
    • smallint

    Wie kommt das?
    Das liegt an der Struktur der Datensätze. Diese ist hinlänglich in diversen Blogs dokumentiert (z.B. hier Inside the Storage Engine: Anatomy of a record und hier: SQL Server Storage Internals 101 ), jedoch eher für allgemeine Zwecke und dieser Sonderfall (eine 1-byte-Spalte) ist dabei weniger im Fokus.

    Theoretisch würden für einen Datensatz 8 Bytes ausreichen:

    4 Bytes: Datensatzkopf (Typ + Zeiger auf Null-Bitmap)
    1 Byte: Daten
    2 Bytes: Anzahl der Spalten im Record
    1 Byte: NULL Bitmap

    Dazu kommt noch der 2 Bytes große Zeiger im Page-Offset, was dann 10 Bytes ergeben würde.
    Dennoch belegt ein solcher Datensatz 9 Bytes auf der Seite/Page + Offset.

    Woran das liegt, sehen wir uns an.

    How come?
    This has to do with the structure of the data sets, which is sufficiently documented in various blogs (e.g. here: Inside the Storage Engine: Anatomy of a record and here: SQL Server Storage Internals 101 ), but rather for general purposes, and this special case (a 1-byte-column) is less focused on here.

    Theoretically, 8 bytes would be sufficient for a data set:

    4 bytes: data set head (type + pointer to NULL-bitmap)
    1 byte: data
    2 bytes: number of columns in record
    1 byte: NULL bitmap

    Add to that the 2-bytes-pointer in the page offset, which would then result in 10 bytes.
    Despite this, such a data set uses 9 bytes on the page/page + offset.

    We will now look at the reason for this.

    Im Folgenden definiere ich 2 Tabellen mit jeweils einer bzw. 2 Spalten von Datentyp tinyint, der ja bekanntlich einen Byte benötigt:

    In the following, I am defining 2 tables with one and 2 columns each of the data type tinyint, which is known to use 1 byte:

     

    CREATE TABLE T1col
    (col1 tinyint null)
    GO
    CREATE TABLE T2col
    (col1 tinyint null, col2 tinyint null)

     

    Danach füge ich zuerst Datenätze mit dem Wert „255“ bzw „255, 255“ ein, und danach einige mit Wert „NULL“.

    Mit DBCC Page kann man sich den Header der Datenseiten beider Tabellen ansehen, und findet eine kleine Überraschung (Ergebnis reduziert):

    Next, I am inserting, first, data set of the value “255” or “255, 255” and then a few of the value “NULL.”

    With the DBCC page, one can look at the header of the data pages of both tables, and one will find a small surprise (reduced result). 

     

     DBCCPage_1vs2cols

     

    Obwohl die Größe der Daten fixer Länge (pminlen) mit 5 bzw. 6 unterschiedlich angegeben wird, ist der freie Speicherplatz auf der Seite (m_freeCnt) identisch! (rot)

    Der Datensatz belegt in beiden Fällen jedoch 9 Bytes im Page-body (blau).

    So sieht die Tabelle, bestehend aus einer Spalte, mit einigen Datensätzen gefüllt, auf der Festplatte aus:

    Even though the size of the fixed-length data (pminlen) is specified differently, with 5 and 6 respectively, the free storage on the page (m_freeCnt) is identical! (red)

    The record, however, uses in both cases 9 bytes in the page body. (blue)

    This is what the table, consisting of one column, filled up with a few records, looks like on the hard drive:

     

      Page_Record_1col9byte_hex

     

    Man sieht, dass 9 Bytes belegt sind, jedoch nur, da nach der NULL Bitmap noch ein Byte jedem Datensatz anhängig ist (gelb markiert).

    Hier die Tabelle mit 2 Spalten:

    One can see that 9 bytes are used, but only because after the NULL bitmap, one extra byte is attached to each data set (marked in yellow).

    Below, see the table with 2 columns:

     

     1410_Page_Record_2cols9byte_hex.png

     

    Auch hier also 9 Bytes, mit dem Unterschied, wie das letzte Byte verwendet wird.

    Noch etwas deutlicher wird es im Binärformat. Das ist die Tabelle mit 2 Spalten – auch diese benötigt 9 Bytes, und man sieht unten, wie die NULL Bitmap zu ihrem Namen kommt:

    Here, too, it is 9 bytes, with the difference being how the last byte is used.

    It becomes a bit clearer in the binary format.
    This is the table with 2 columns –this one uses 9 bytes as well, and you can see below how the NULL bitmap gets its name:

     

    Page_Record_2cols9byte_binary

     

    Dieser eine Byte, der für mich keinen klaren Nutzen hat, führt also zu dem Ergebnis, das beide Tabellen letztlich 9 Bytes je Record auf der Festplatte benötigen.

    This one byte, which to me does not have any clear purpose, is what leads to the result that both tables ultimately use 9 bytes per record on the hard drive.

     

    Die minimale Datensatzgröße ist daher in der Tat 9 Bytes. Der zweitgrößte Datensatz ist jedoch auch 9 Bytes. :-D

    Dabei darf beliebig gewählt werden zwischen 2 Spalten à 1 Byte oder 1 Spalte à 2 Bytes :-)
    Daher die Liste:

    The minimal record size is thus in fact 9 bytes. The second biggest record, however, is also 9 bytes. :-D

    Here, one may freely choose between 2 columns à 1 byte or 1 column à 2 bytes :-).
    Hence the list:

     

    • bit
    • char(1)
    • binary(1)
    • tinyint
    • char(2)
    • nchar(1)
    • binary(2)
    • smallint

     

    Kommen wir nun zu Part 2:

    Wie viele Datensätze passen maximal auf eine Datenseite, wenn wir jetzt wissen, dass jeder Datensatz mindestens 9 Bytes + Offset benötigt?

    Let us now look at Part 2:

    How many records fit maximally on a data page if we now know that every data set requires a minimum of 9 bytes + offset?

    Wir testen mit einer Tabelle, bestehend aus einer Spalte mit Datentyp tinyint – wohlwissend, dass es dasselbe Ergebnis bringen würde, wenn wir smallint oder etwas anderes aus der Liste oben nehmen würden.

    We are testing with one table of one column with datatype tinyint – knowing full well that it would have the same outcome if we were to take smallint or something else from the list above.

     

    CREATE TABLE T3_Tinyint
    (col tinyint NOT NULL)
    GO
    …Insert 700 Rows…

    Sehen wir uns an, wie voll die Datenseite geworden ist, und welche Page_ID diese hat:

    Then, we will check again how full the data page has become, and which Page_ID it has:

     

     Row_Per_Page_Stage1_700

     

    Ausgehend davon, dass eine Datenseite 8192 Bytes groß ist und abzüglich Header 8096 Bytes für Daten zur Verfügung stehen, bedeutet ein Füllgrad von ~95,107%, das noch gut ~396 Bytes zur Verfügung stehen. Durch 11 ergibt das 36. - 36 Datensätze haben also noch Platz!

    Was sagt DBCC Page dazu?

    Based on the fact that a data page is 8192 bytes in size and that, less the header, 8096 bytes are available for data, a fill degree of ~95,107% means that some ~396 are still available. Divided by 11 this equals 36 – there is still room for 36 records!

    What does DBCC Page have to say to this?

     

     DBCCPage_Row_Per_Page_Stage1_700_Page_Header

     

    Auch hier: 396 Bytes frei – na dann war unsere Überschlagsrechnung gar nicht so schlecht. :-)

    Das heißt rein rechnerisch müssten weitere 36 Datensätze auf die Seite passen.
    Also „rauf damit“.

    396 bytes free – well, then, our back-of-the-envelope calculation wasn’t so bad at all. :-)

    That is to say that in purely arithmetical terms, a further 36 records should fit on the page.
    So “let’s put them on.”

     

    …Insert 36 Rows…

     

    Row_Per_Page_Stage2_2Pages

     

    2 Seiten, direkt ab dem 701. Datensatz.
    Was ist da los?
    Im Hex Editor betrachten wir „das Grauen“:

    2 pages, directly from the 701st data set.
    What is going on there?
    In the hex editor, we are looking at “the horror”:

     

     Tab_Footer_Offset_FreeSpace_hex

     

    Freier Platz! – Fragmentierung, „Igitt“ ;-)
    Was machen wir da?
    Ein Rebuild der Tabelle, was sonst.

    Free space! – Fragmentation, „yuck“ ;-)
    What to do?
    A rebuild of the table, what else.

     

    Row_Per_Page_Stage3_736

     

    Und schon ist die Seite zu glatten 100% gefüllt: 736 Datensätze.
    Und das sagt der Header:

    And just like that, the page is filled with a sleek 100%: 736 records.
    And this is what the header says:

     

     DBCCPage_Row_Per_Page_Stage3_736_Page_Header

     

    Ja, die Page_ID ist eine andere – aber nicht, weil ich gemogelt hätte, sondern weil die Storage Engine für den Rebuild neuen Platz reserviert, und den alten nach getaner Arbeit freigegeben hat.

    Und auch auf Platte sieht es jetzt so aus, wie es sein sollte – bis zum Ende aufgefüllt:

    Yes, the Page_ID is a different one – but not because I might have cheated, but because the storage engine allocated new space for the rebuild, and released the old one after the done work.

    On the drive, too, it now looks exactly how it’s supposed to – filled up to the end:

     

     Tab_Footer_Offset_Full_hex

     

    Übrigens: Wenn ich anstelle der Heap-Speicherung der Tabelle einen Clustered Index als Struktur gewählt hätte, wären die Daten in den allermeisten Fällen sofort auf der einen Seite zu 100% abgelegt worden
    – aber was tut man nicht alles für die Spannungskurve :-)

    Ansonsten gilt auch hier die Regel: „Niemals auf etwas verlassen, was man nicht selbst getestet hat“ ;-)

    By the way, had I chosen a Clustered Index as structure instead of the Heap-storage, in most cases, the data would have been stored immediately to a 100% on the one page
    – but what’s the fun in that! :-)

    Otherwise, the rule also applies here: “Never rely on something you haven’t tested yourself” ;-)

     

    Ergebnis:
    Die Antwort auf die Frage lautet daher nicht 700, nicht 732, oder gar 809, sondern: 736 Datensätze passen maximal auf eine Seite.

    - und dabei macht es noch nicht einmal einen Unterschied, ob man eine Spalte mit 1 oder 2 Bytes Größe, oder gar 2 Spalten mit je einem Byte Größe verwendet.

    Da jeder Datensatz 9 Bytes zzgl. 2 Bytes Record Offset benötigt, haben wir damit die maximale Kapazität einer SQL Server Datenseite mit 8096 Bytes exakt ausgereizt! :-)

    - Nicht zu verwechseln mit der maximalen Zeilenlänge von 8060 Bytes.

    Result:
    The answer to this question, therefore, is not 700, not 732, or even 809, but: a maximum of 736 data records fits on one page.

    - and it does not even make a difference, if one uses one column with 1 or 2 bytes in size, or even 2 columns with one byte in size.

    Since every data record uses 9 bytes plus 2 bytes record offset we will have exactly exhausted the maximum capacity of an SQL Server data page with 8096 bytes! :-)

    - Not to be confused with the maximal row length of 8060 bytes.

     

    my nine bytes

    Andreas

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