Andreas Wolter: My conference-sessions in 2015: from Extended Events over In-Memory to Security

Christoph Muthmann: Mehrfache Leerzeichen einfach entfernen

Kurz vor dem langen Wochenende nur ein kleines Skript mit einem tollen Trick, wie man mehrfache Leerzeichen in einem String einfach entfernen kann.

Ganze Geschichte »

Falk Krahl: Ankündigung SQL Server 2016

Bereits Anfang des Monates hat Microsoft die nächste Version des SQL Server angekündigt. Es handelt sich dabei um den SQL Server 2016 der Anfang des nächsten Jahes erscheinen soll (Vielleicht wieder am ersten April ????).
Eine Vorabversion wird es bereits im Sommer geben.
Einige Neuheiten werden die Integration der Programmiersprache R sein sowie der Umgang mit Tabellen die teilweise in der Cloud und teilweise auf der eigenen Hardware liegen.
Weitere Neuerung die mit SQL Server 2016 kommen werden, finden Sie unter folgendem Link:
SQL Server 2016 Neuereungen

Falk Krahl: Neue Updates für SQL Server 2012

Auch für den SQL Server 2012 sind zwei neue Updates erschienen. Zum einen handelt es sich um das kumulative Update 06 für den SQL Server 2012 mit Servicepack 2 und zum anderen um das kumulative Update 16 für den SQL Server 2012 mit Servicepack 1. Diese können unter den folgenden Links angefordert werden:
Kumulatives Update 16 für SQL Server 2012 SP1
Kumulatives Update 06 für SQL Server 2012 SP2

Falk Krahl: SQL Server 2014 SP 1

Ffür den SQL Server 2014 RTM ist nun erneut das Servicepack 1 erschienen. Nachdem dies bereits vor einem Monat erschien und wieder zurück gezogen werden musste, jetzt also der 2. Versuch. Allerdings beinhaltet dieses nur die kumulativen Updates 01 bis 05. Wer also bereits auf CU 06 oder sogar CU 07 ist, sollte wohl noch auf das Update SQL Server 2014 SP1 CU01 warten.
Für alle anderen kann dies unter folgendem Link angefordert werden:
SQL Server 2014 SP1

Christoph Muthmann: SQL Server 2014 Service Pack 1 has released again

Bereits am Freitag hat das SQL Server Release Team in seinem blog die Verfügbarkeit des SP1 bekannt gegeben.

Ganze Geschichte »

Christoph Muthmann: Aktuelles Statement ausführen

Es gibt diverse Tools (z. b. für Oracle), welche nur das Statement ausführen, in welchem aktuell der Cursor steht. Für das Management Studio ist so etwas leider nicht offiziell vorgesehen, aber es gibt Add Ins.

Ganze Geschichte »

Christoph Muthmann: SQL Server 2016 public preview

Micorosft hat einige Informationen über das nächste Release des SQL Servers bekannt gegeben, von dem wir im Sommer eine public preview erwarten.

Ganze Geschichte »

Christoph Muthmann: Office 2016 Public Preview now available

Microsoft has just released the public preview of Office 2016.

Ganze Geschichte »

Christoph Muthmann: Greg Linwood's Nepal story

Der frühere SQL Server MVP Greg Linwood war zum Zeitpunkt des Erdbebens in Nepal unterwegs, wo er unter anderem ein Heim für blinde Waisenkinder besucht hat, welches seine Organisation unterstützt. In seinem blog kann man seinen Bericht über das Erdbeben lesen, wobei er noch Glück gehabt hat.

Ganze Geschichte »

Christoph Muthmann: The Power of Community

The MVP Virtual Conference (MVP V-Conf) is a new, virtual, 2-day event that showcases how the best and brightest independent technology experts are using Microsoft technologies today. Tune in and see what the community of power users are saying about the mobile-first, cloud-first world of possibility with Microsoft re-imagined.

Ganze Geschichte »

Andreas Wolter: SQL Server in Microsoft Azure: How to gain performance by flexibility and save costs at the same time

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

Ganz frisch erschienen und im SQL Server Releaseblog veröffentlicht wurde ein CU für SQL Server 2014.

Ganze Geschichte »

Falk Krahl: Neues Update für SQL Server 2014

Bereits letzte Woche ist für den SQL Server 2014 RTM ein neues Update erschienen. Es handelt sich dabei um das kumulative Update 07. Ich hoffe das dieses besser funktioniert als das bereits am 15.04.2015 bereit gestellte Servicepack 01 für den SQL-Server 2014, was wieder zurück gezogen wurde.
Es kann unter folgendem Link angefordert werden.
Kumulatives Update 07 für SQL Server 2014

Marcel Franke: PASS Business Analytics Conference 2015 – Day 2

Good morning. So we here go with the second day of the PASS BA Conference. After a really good first day todays keynote started with a Lady I have never heard before. Her name is Mico Yuk, CEO of BI Brainz and she is a great lady. And although it was very hard to have a keynote after Day 1 keynote of Carlo Ratti, who showed us more the visionary future of Analytics, she really made it very well and grounded the people with the day to day challenges.

WP_20150422_08_08_02_Pro

Keynote – Visual Storyboarding

Her talk was about Visual Storyboarding and there where plenty other talks about that subject today.  So let me try to get that story first for you. She started with the general disappointments companies have with Business Intelligence (BI) projects. We hear that a lot these days, normally addressed by some software vendors mostly out of the Hadoop world, but she made it very clear. Let’s start with 2 facts she presented:

  • $97 Billion annual BI spend by 2016
  • But the average Business Intelligence user adoption rate is only 21%

P1030409

Lot’s of BI users still seem to use a lot of “export to Excel” functionality and do the Analytics work on their own. That must make us think. So why is the user adoption rate of BI and Data Warehouse projects so low? It reminds me of a quote of John Naisbitt:

“We are drowning in Information but starved for knowledge”.

But what are the reasons for? BI projects in general are still to technical driven by the IT and we still have lot’s of silos in organizations, like a BI Team, Analysts, Data Science Team, etc. But these teams have to work together, because a lot of Data Scientists spent a long time at school and are smart, but with not much experience in the field and not enough business understanding. That is a gap that business departments and analysts can fill. In the world of BI the main focus is about explaining the things that happened (the past). A lot of Data Scientists can ask question on things, nobody thought before and can give you insides in your data and your business people never noticed before. So again, these teams have to work together and we can’t give it just to the IT.

As Mico said a lot of BI projects stop on just presenting numbers, but as a decision maker your are more focused on actions. So the last step of creating the right actions out of my numbers & trends is often the missing part. But it is also not that easy to achieve, because in order to get it right, you first need the trust of the users and this has a lot to do with communication. So what has this to do with Visual Storyboarding?

Visual Storytelling is the common language that can break silos and bring people together to talk in the same language (http://visual.ly/science-storytelling) and focus on the important things. Mico presented some nice samples on slide 20 that you also find here:

Something that I also recognized in the keynote of day 1 was that a lot of great visualizations are still hand made by graphic designers and developers. So there is still a larger gap in the ease of use of tools and the quality of presenting data. This has also to do with a new kind of user experience that people also expect in business application that is more driven by consumer products and apps. 

Summary

For me it was great to had the chance to visit the PASS Business Analytics conference this year. California in general is a great place for that kind of conference. As already said it is a different kind of community. I learned a lot of new interesting thoughts on data analytics and get to know new interesting people. For me I take 3 main things away from this conference:

  • Data Analytics is still hard work. You have to leverage so many skills, also non-technical skills, to get it really right.
  • Visualization and Storytelling is something we will see more in the future and is for me definitely a way to go to close the last mile to the user.
  • I see many people catching up with R. R is more and more used in the community and also in business departments, so I think Microsoft made a big step in the right direction by acquiring Revolution Analytics.

Sources


Filed under: Uncategorized

Marcel Franke: PASS Business Analytics Conference 2015 – Day 1

Pic1This is my first time to visit the PASS Business Analytics Conference and so I want to take the chance to give a wrap up of my impressions of the first day. The conference takes place in Santa Clara and I personally think that California as a great place to be. The sunny weather makes it very easy to combine interesting and challenging sessions and an open mood of people for networking and get to know each other. I really like the very friendly and open nature of the people here.

Maybe I give you some more details on the conference itself. The clear focus of that conference is Data Analytics. It’s a very good mixture of technical, business and strategic sessions. The audience is very different to the typical and more technical PASS Conference in Seattle. Altogether there are 600 participants out of 24 countries and more than 60 hours of sessions with great speakers. Most of them I never heard before, but they really give you some new ideas or food for thought that you never be thought of before.

Pic2

Keynote

The keynote started with the PASS president Thomas LaRock, followed by a great talk of Carlo Ratti, the Director of the MIT Senseable City Lab. The MIT Senseable City Lab is focused on who sensors are changing cities into real-time cities with lot’s of new opportunities. Today cities are already covered with digital sensors, so that people can react on it. There is an estimation that by 2020  about 50 billion connections need to handled in the world. Most of these connections will be sensors and devices than mobile phones. He showed several examples how cities are covered with digital sensors, react on it and become more smart cities these days. There are several research projects that the MIT is doing in that area like:

Mobility and Smart Cities

The Hub Cab is a project started on analyze Taxi Peaks in New York and try to bring in shared mobility. Beside the MIT also Audi and GE are part of that project, which makes me, as a German, also proud of that we are part of that innovation.  He presented some interesting numbers that although safe driving cars are on the way only 20% of the cars are need today for our mobility. An average car is only used 5% of the time so there is a huge opportunity to make mobility more efficient, especially in large city, also in order enhance the quality of live. So sharing is much more intelligent and what if we take the self-driving experience and the the sharing concepts together and even think about how traffic is organized. Do we still then need traffic lights? There are some nice concepts and researches on self-driving intersection that organizes traffic much more intelligent and efficient as traffic lights did for the last 100 years. This is a big opportunity for all of us.

The Copenhagen wheel

Another interesting and fascinating thing that came out of a MIT research project is the Copenhagen Wheel. This is a complete new way of bicycle mobility and a great example how sensors and technology can help us to innovate new experiences. And the best way to understand it, is to watch the video: https://www.youtube.com/watch?v=S10GMfG2NMY


Follow the trash

One last interesting project to me was the “moma-followup”. The idea is to follow obsolete electronics, that is normally just thrown away, across the world for recycle or reuse. In order to do this the MIT developed two different types of self-reporting technologies that are coupled with the obsolete electronics and than see what happened: https://www.youtube.com/watch?v=5fHZYyOZU4c


Summary

For me the first day really gave me some new ideas what is going on in Data Analytics. I think it is an amazing time to see, what can be achieved with technologies in these days and this is not always just to earn more money. It is interesting to see how fast the technology adoption happens and what role these great Universities like the MIT, Boston, Harvard, Stanford and many others play in that area. So a really great day one and I will follow up on day 2.

Sources:


Filed under: Big Data, Conferences, Data Analytics, Data Science

Andreas Wolter: Received 2nd MVP Award for SQL Server - Zum 2. Mal ausgezeichnet mit dem MVP-Award für SQL Server

Philipp Lenz: Excel 2013 and the Flash Fill Function (ETL for Beginners ;-) )

Off topic but really good to combine with PowerPivot or PowerQuery: The flash fill function.
With this feature in Excel 2013 you can automatically extract text into columns. It works better than the text in columns feature or Left, Right or Find functions in Excel or PowerPivot for me.

Example:

This column contains surname, middle names and last names into one column. First you must convert this data into a table:

10-04-_2015_15-01-42

Next add a column “surname” – please do not add the other columns. Sometimes Excel is confused by the other columns…
Then type in the new column the first surname “Franz”. In the next row start typing the next surname “Karl”. Excel writes automatically the other names into the cells. In this case you can also use the Flash Fill Function in the Ribbon “Data” – but this function will not work by the middle names.

10-04-_2015_15-04-55

 

 

 

 

 

 

 

Now add a new column middle name and type in the first data cell the first middle name “Dieter”. If your data have no double name in the first rows, sometimes you must type much more names if Excel use the Flash Fill Function.

10-04-_2015_15-05-40

 

 

 

 

 

 

 

 

In the last row you can see the surname in the middle name column. If you have middle names or data with more text parts it is sometimes not perfect! The last step is to add the last name column. The data has in the first 2 rows some different data – a single last name and double name. So you must type 2 rows till the Flash Fill Function takes effect:

10-04-_2015_15-06-08

Now you can add this table into your PowerPivot or PowerView model. Enjoy this feature!

More information: http://blogs.office.com/2012/08/09/flash-fill/

SQLPodcast: #011 – T-SQL on Steroids

In dieser Folge habe ich mich mit Frank Geisler allgemein zum Thema T-SQL Entwicklung und den möglichen Entwicklungsumgebungen unterhalten.

Philipp Lenz: off topic: Office 365 / Office 2015 Click & Install

office365logoorange_webBeim installieren von Office 365 wie auch der Preview von Office 2015 habe ich immense Probleme gehabt. Einerseits konnte ich teilweise das Setup nicht starten, da ich laut Frontend zu wenig Speicherplatz oder keine Interverbindung hätte, oder das Setup brach mittendrin ab, mit der Meldung das eine Datei nicht gefunden werden konnte. Die Logdateien gaben hierzu ebenso wenig Hinweise. Das Problem tritt bei mir mit einem frischen Windows 8.1 Rechner sowie mit einem Windows 10 Preview (March Update) auf. Nachdem ich nun den Windows 10 Rechner neu aufgesetzt habe und die Windows Firewall sowie den Windows Defender deaktiviert habe, läuft nun die Installation. Nebenbei bemerkt, ist auch meine Internetverbindung nicht die schnellste und stabilste.

Update: Das Problem löste sich nur temporär auf. Nach weiterer Recherche liegt es wohl an meinem Speedport W 723 V Typ A. Die Installation wurde weiterhin unterbrochen. Nachdem das Problem mit weiteren Geräten im Netz nachvollziehbar war, und ein Tethering mir zeigte, dass es nur auf Rechnern auftritt die in diesem Netz sind, habe ich kurzerhand den Router gegen eine aktuelle Fritz! Box ausgetauscht – sieht da, es funktioniert. Wenn man den Speedport als Modem nur verwendet und die interne Firewall somit aus dem Spiel nimmt, funktioniert es auch. Das ändern der DNS Adressen hat auch nichts gebracht – Grausam!

Uwe Ricken: GUID vs INT/IDENTITY als Clustered Key

Immer wieder hört oder liest man, dass ein Clustered Index möglichst fortlaufend/aufsteigend organisiert sein soll. Am besten sei immer ein Clustered Index mit möglichst kleinen Datentypen (z. B. INT); außerdem sollte ein Clustered Index nach Möglichkeit nicht aus zusammengesetzten Attributen bestehen. Die mit Abstand größte Abneigung besteht bei vielen Entwicklern gegen den Einsatz von GUID als Clustered Keys. Die generelle “Verteufelung” von GUID ist nicht gerechtfertigt – GUID sind in einigen Workloadmustern performanter als die “Heilige Kuh” IDENTITY (1, 1).
Die Vorbehalte vieler SQL Experten gegen GUID kann man z. B. in den kontroversen Foreneinträgen zu dem Artikel “^Are there that many GUIDs?” von Steve Jones auf sqlservercentral.com nachlesen.
Der nachfolgende Artikel soll zeigen, dass der schlechte Ruf, der einer GUID vorausgeht, nicht immer gerechtfertigt ist sondern seiner Nutzung immer eine sorgfältige Betrachtung des Workloads vorausgehen sollte. Pro und Contra GUID soll der Artikel etwas näher beleuchten.

Testumgebung

Für die nachfolgenden Demonstrationen und Diskussionen wird eine Datenbank mit einer Initialgröße von 1 GB erstellt. In dieser Datenbank befinden sich zwei Tabellen mit identischer Datensatzlänge sowie eine Stored Procedure, die im Beispiel von jeweils 200 Benutzern gleichzeitig ausgeführt werden wird.
CREATE DATABASE [demo_db]

ON  PRIMARY 

(

    NAME = N'demo_db',

    FILENAME = N'F:\DATA\demo_db.mdf',

    SIZE = 1000MB,

    MAXSIZE = 20000MB,

    FILEGROWTH = 1000MB

)

LOG ON 

(

    NAME = N'demo_log',

    FILENAME = N'F:\DATA\demo_db.ldf',

    SIZE = 500MB,

    MAXSIZE = 1000MB,

    FILEGROWTH = 100MB

);

GO

 

ALTER AUTHORIZATION ON DATABASE::demo_db TO sa;

ALTER DATABASE [demo_db] SET RECOVERY SIMPLE;

GO

Die Initialgröße von 1 GB sorgt bei den Tests dafür, dass keine Abweichungen bei der Messung entstehen, weil die Datenbank auf Grund der hohen Datenmenge wachsen muss. Die Größe von 500 MB für die Protokolldatei ist ausreichend, da die Datenbank im Modus “SIMPLE” betrieben wird.


USE demo_db;

GO

 

-- Tabelle mit fortlaufender Nummerierung als Clustered Index

CREATE TABLE dbo.numeric_table

(

    Id    INT          NOT NULL    IDENTITY(1, 1),

    c1    CHAR(400)    NOT NULL    DEFAULT ('just a filler'),

 

    CONSTRAINT pk_numeric_table PRIMARY KEY CLUSTERED (Id)

);

GO

 

-- Tabelle mit zufälligem Clustered Index

CREATE TABLE dbo.guid_table

(

    Id    uniqueidentifier  NOT NULL    DEFAULT(NEWID()),

    c1    CHAR(388)         NOT NULL    DEFAULT ('just a filler'),

 

    CONSTRAINT pk_guid_table PRIMARY KEY CLUSTERED (Id)

);

GO

Beide Tabellen haben eine identische Datensatzlänge um Abweichungen bei den Messungen zu verhindern, weil unterschiedliche Datensatzlängen dazu führen können, dass mehr oder weniger Datensätze auf eine Datenseite passen. Jeder Datensatz hat eine Länge von 411 Bytes. Da ein [uniqueidentifier] eine Länge von 16 Bytes besitzt, muss lediglich der Füllbereich um 12 Bytes reduziert werden.


-- Procedure for insertion of 1,000 records in dedicated table

CREATE PROC dbo.proc_insert_data

    @type varchar(10)

AS

    SET NOCOUNT ON

 

    DECLARE    @i INT = 1;

 

    IF @type = 'numeric'

    BEGIN

        WHILE @i <= 1000

        BEGIN

            INSERT INTO dbo.numeric_table DEFAULT VALUES

            SET @i += 1;

        END

    END

    

    ELSE

    BEGIN

        WHILE @i <= 1000

        BEGIN

            INSERT INTO dbo.guid_table DEFAULT VALUES

            SET @i += 1;

        END

    END

            

    SET NOCOUNT OFF;

GO

Die Prozedur hat eine triviale Aufgabe; sie soll – abhängig vom übergebenen Parameter @type - jeweils 1.000 Datensätze pro Aufruf in eine der beiden Tabellen eintragen. Wird der Prozedur der Parameter @type mit dem Wert 'numeric' übergeben, trägt die Prozedur entsprechende Daten in die Tabelle mit fortlaufendem Clustered Key ein während bei einem anderen Parameterwert Daten in die Tabelle mit zufälligem Clustered Key eingetragen werden.

Vor jeder Ausführung der Prozedur werden die Statistiken in sys.dm_os_wait_stats gelöscht. Dadurch sollen die während der Laufzeit registrierten Wartevorgänge analysiert werden:


DBCC SQLPERF('sys.dm_os_wait_stats', 'CLEAR');

GO

Während die Prozedur ausgeführt wird, wird in einer zweiten Sitzung in SQL Server Management Studio die folgende Abfrage wiederholt ausgeführt:


SELECT  DOWT.session_id,

        DOWT.wait_duration_ms,

        DOWT.wait_type,

        DOWT.resource_description,

        DOWT.blocking_session_id

FROM    sys.dm_exec_sessions AS DES INNER JOIN sys.dm_os_waiting_tasks AS DOWT

        ON (DES.session_id = DOWT.session_id)

WHERE   DES.is_user_process = 1;

Um einen Workload mit mehreren Clients zu simulieren, wird das Tool “ostress.exe” verwendet. Dieses - von Microsoft kostenlos angebotene Tool - ermöglicht die Simulation von Workloads mit mehreren Clients und ist – insbesondere für PoC-Simulationen – sehr zu empfehlen.

Daten in Tabelle mit fortlaufendem Clustered Key


Zunächst wird die Prozedur für die Ausführung in die Tabelle [dbo].[numeric_table] ausgeführt:

OSTRESS_NUMERIC_START

Mit einer vertrauten Verbindung (-E) wird auf den Testserver zugegriffen und die Prozedur [dbo].[proc_insert_data] (-Q) in der Datenbank [demo_db] (-d) für 200 Clients (-n) ausgeführt. Während der Ausführung dieser Prozedur wird mittels sys.dm_os_waiting_tasks überprüft, auf welche Ressourcen Microsoft SQL Server während der Clientzugriffe warten muss. Insgesamt benötigt das Eintragen von 200.000 Datensätzen ~12,500 Sekunden.

OSTRESS_NUMERIC_DURATION

Diese Zeitspanne mag für Datenbanken mit geringem Transaktionsvolumen ausreichend sein; jedoch gibt es Systeme, die noch deutlich mehr Datensätze in kürzerer Zeit verarbeiten müssen. Warum also dauert dieser Prozess so lange?

WAITING_TASKS_NUMERIC_INSERT

Die obige Abbildung zeigt die wartenden Prozesse während der Ausführung der Prozedur. Es ist deutlich zu erkennen, dass die Mehrzahl der Prozesse auf einen PAGELATCH_xx warten müssen.

PAGELATCH_XX


Als “Latch” bezeichnet man interne, nicht konfigurierbare Sperren, die von Microsoft SQL Server benötigt werden, um bei gleichzeitigem Zugriff (Konkurrierend) die im Speicher befindlichen Strukturen zu schützen. Latches werden häufig mit Locks verwechselt, da ihre Zwecke gleich gelagert aber nicht identisch sind. Ein Latch bezeichnet ein Objekt, das die Integrität der Daten anderer Objekte im Speicher von SQL Server gewährleistet. Sie sind ein logisches Konstrukt, das für einen kontrollierten Zugriff auf eine Ressource sorgt. Im Gegensatz zu Locks sind Latches ein interner SQL Server-Mechanismus. Man kann sie grob in zwei Klassen aufteilen - Buffer Latches und Non-Buffer Latches. Für den in diesem Artikel beschriebenen Fall handelt es sich um Sperren auf Datenseiten, die sich bereits im Buffer Pool befinden. Wer sich intensiver mit Latches beschäftigen möchte, dem sei das Dokument “Diagnosing and Resolving Latch Contention on SQL Server” von Ewan Fairweather und Mike Ruthruff für das weitere Studium empfohlen. Es mag zwar schon älter sein aber sein Inhalt ist auch für die neueren Versionen von Microsoft SQL Server noch relevant!

WAITING_TASKS_NUMERIC_INSERT_AGG

Auffällig ist, dass alle Prozesse auf die gleiche Ressource warten müssen. Die Wartevorgänge variieren zwischen SH (Shared) und EX (Exclusive) Ressourcen. Diese Wartevorgänge sind der – in diesem Fall sehr schlechten – Eigenschaft des Clustered Index geschuldet. Da die Daten in sortierter Reihenfolge in den Index eingetragen werden müssen, werden alle neuen Datensätze immer am Ende des Index eingetragen. Der Clustered Index repräsentiert die Tabelle selbst und definiert die Sortierung nach einem vorher festgelegten Schlüsselattribut. Wenn dieses Attribut mit einem fortlaufenden Wert befüllt wird, reduziert sich der Zugriffspunkt für Microsoft SQL Server immer auf die letzte Datenseite des Clustered Index im Leaf-Level.

CLUSTERED_INDEX_NUMERIC

Die Abbildung zeigt den – logischen – Aufbau eines Clustered Index. Aus Darstellungsgründen wurde auf die Beziehung der Datenseiten untereinander verzichtet. Alle Daten werden im Leaf-Level gespeichert. Da das Schlüsselattribut als fortlaufend definiert wurde, müssen ALLE Prozesse auf der letzten Seite des Index ihre Daten eintragen. Wenn – wie in diesem Beispiel gezeigt – mehrere Prozesse gleichzeitig eine INSERT-Operation durchführen, hat das den gleichen Effekt wie das Anstehen an einer Kasse im Supermarkt; man muss warten! Aber ACHTUNG: Das beschriebene Szenario gilt nicht für Workloads mit mehreren Verbindungen!

Fortlaufenden Index-Pages im Clustered Index


Sehr häufig verbindet man mit fortlaufender Nummerierung im Indexschlüssel die Vermeidung von Page Splits. Diese Aussage hat aber nur Bestand, wenn das Eintragen von neuen Datensätzen durch einen einzigen Thread geschieht. Wird jedoch - wie im aktuellen Beispiel - der Einfügevorgang von 200 Benutzern gleichzeitig gestartet, kann eine sequentielle Folge in den Eintragungen nicht mehr gewährleistet werden. Ein Blick in das Transaktionsprotokoll zeigt die Details:


SELECT  [Current LSN],

        Operation,

        Context,

        AllocUnitName,

        [Page ID],

        [Slot ID],

        [RowLog Contents 0]

FROM    sys.fn_dblog(NULL, NULL) AS FD

WHERE   AllocUnitName = N'dbo.numeric_table.pk_numeric_table'

ORDER BY

        [Current LSN];

Die nachfolgende Abbildung zeigt einen Extrakt aus dem Transaktionsprotokoll, der die Einfügevorgänge in chronologischer Reihenfolge ausgibt.

FN_DBLOG_01

Hervorzuheben ist die [SLOT ID]! Aus der Slot Id ist erkennbar, dass die Datensätze nicht hintereinander eingetragen wurden, sondern nach dem 5 Eintrag (Zeile 48) ein neuer Datensatz in Slot 1 eingetragen worden ist. Das lässt nur den Rückschluss zu, dass ein kleinerer Indexwert erst später eingetragen wurde. Mit dem nachfolgenden SELECT-Statement werden die eingetragenen Indexschlüssel “entschlüsselt”:


SELECT CAST(0x14 AS INT)    AS    [Slot_0],

       CAST(0x21 AS INT)    AS    [Slot 1],

       CAST(0x22 AS INT)    AS    [Slot 2],

       CAST(0x23 AS INT)    AS    [Slot 3],

       CAST(0x24 AS INT)    AS    [Slot 4],

       CAST(0x20 AS INT)    AS    [Slot w],

       CAST(0x1F AS INT)    AS    [Slot x],

       CAST(0x15 AS INT)    AS    [Slot y],

       CAST(0x1D AS INT)    AS    [Slot z];

Auf eine vollumfängliche Beschreibung der Strukturen eines Datensatzes kann in diesem Artikel nicht eingegangen werden! Kurz und Knapp gilt für die Berechnung des eingetragenen Wertes für die ID in der Tabelle [dbo].[numeric_table] jedoch folgende Richtlinie:


  • Die ersten vier Bytes in [RowLog Contents 0] sind für den Rowheader reserviert und müssen extrahiert werden
  • Die nächsten 4 Bytes entsprechen dem Wert, der in das Attribut [ID] (INTEGER) eingetragen wird
  • Diese vier Bytes werden “geshifted” und übrig bleibt der Hexadezimalwert für den eingetragenen Dezimalwert
  • Die Berechnung des Dezimalwertes erfolgt mittels CAST-Funktion

Das Ergebnis der obigen Abfrage sieht dann wie folgt aus:

SLOT_ARRAY_01

Die ID-Werte sind in der chronologischen Reihenfolge ihres Einfügeprozesses dargestellt. Zunächst wurden die Werte 20, 33, 34, 35, 36 in die Tabelle eingetragen. Das entspricht exakt dem Auszug aus dem Transaktionsprotokoll (siehe [Slot ID]. Mit der sechsten Transaktion wurde ein neuer Datensatz mit dem Wert 32 in die Tabelle eingetragen. Der Clustered Key muss sortiert werden; also wird der Wert 32 in Slot 1 eingetragen. Anschließend wird der Wert 31 eingetragen und der Kreis schließt sich erneut!

Schaut man sich die Verteilung der eingetragenen Datensätze nach chronologischem Muster an, wird schnell klar, dass Page Splits zwangsläufig auftreten müssen. Der Grund für diese gemischte Verteilung ist schnell erkennbar, wenn man versteht, wie Microsoft SQL Server den Wert für IDENTITY ermittelt. Dieses Thema habe ich bereits ausführlich im Artikel “IDENTITY-Werte…–warum wird der Wert um <increment> erhöht, obwohl die Transaktion nicht beendet werden kann?” beschrieben.

Werden nur von einem Benutzer Daten in eine Tabelle eingetragen, wird der Indexschlüssel um jeweils 1 erhöht und der Datensatz wird eingetragen. Wir jedoch – wie im vorliegenden Beispiel – ein System mit hoher Concurrency betrieben, ist das nicht mehr gewährleistet. Die nachfolgende Abbildung zeigt den internen Sachverhalt:

IDENTITY_ASSIGNMENT_PROCESS

Jeder Thread fordert – sobald er CPU-Zeit erhält – einen IDENTITY-Wert aus dem Pool an. Dieser Prozess ist nicht mehr umkehrbar; einmal einen Wert erhalten, wird dieser Wert von diesem Prozess für den ganzen Einfügezyklus beibehalten! Nachdem der IDENTITY-Wert vergeben wurde, kann der Thread mit diesem erhaltenen Wert den Einfügevorgang beenden. Dazu muss sich jeder Thread (mit dem ihm zugeteilten IDENTITY-Wert in eine Reihe mit anderen Threads anstellen, die ebenfalls auf die Ressource (Datenseite) warten müssen. Hierbei kann es zu Verschiebungen kommen; der Thread mit dem Wert 4 muss warten, weil die Threads mit den Werten 7 und 11 noch vor ihm ausgeführt werden müssen. Wird die Seite vollständig gefüllt, wenn der Thread mit Wert 11 seine Daten gespeichert hat, wird es unweigerlich für den Thread mit Wert 4 zu einem Page Split kommen.

Clustered Index mit zufälligem Schlüssel


Mit dem nachfolgenden Test werden ebenfalls 200.000 Datensätze in die Tabelle [dbo].[guid_table] eingetragen und erneut die Zeiten gemessen.

OSTRESS_GUID_START

Während der Ausführung der Prozedur werden die wartenden Tasks überprüft und eine abschließende Abfrage über die Wartevorgänge ausgeführt. Insgesamt wird der Einfügevorgang in etwa 50% der Zeit ausgeführt. Das  Eintragen in die Tabelle mit fortlaufendem Clustered Key hat: 6,833 Sekunden betragen!

OSTRESS_GUID_DURATION

Während der Ausführung wurden die Wartevorgänge überprüft und das Ergebnis unterscheidet sich deutlich vom vorherigen Prozess.

WAITING_TASKS_GUID_INSERT_AGG

Während beim Eintragen von fortlaufenden Indexwerten PAGELATCH-Wartevorgänge das Bild dominiert haben, ist beim Eintragen von zufälligen Schlüsselwerten das Schreiben in die Protokolldatei der dominierende Wartevorgang – und das zu Recht!

CLUSTERED_INDEX_GUID

Wie die Abbildung zeigt, konzentriert sich der Prozess nicht mehr an das Ende des Indexes sondern verteilt sich gleichmäßig auf alle Datenseiten im Leaf-Level. Somit können mehrere Schreibvorgänge parallel durchgeführt werden. Dieser Umstand wird in den Wartevorgängen abgebildet – fast alle Prozesse warten darauf, dass die Transaktionen in die Protokolldatei geschrieben werden.

numeric_vs_guid

Die Abbildung zeigt einen Mitschnitt im Microsoft WIndows Performance Monitor während der Ausführung der Prozedur für die Tabelle [dbo].[numeric_table] und für die Tabelle [dbo].[guid_table]. Während für das Einfügen von Datensätzen in die Tabelle mit aufsteigendem Indexschlüssel Pagelatches zu beobachten sind, ist das Eintragen der gleichen Datenmenge in die Tabelle mit zufälligem Indexschlüssel nur eine kurze “Episode” da kaum zu messende Pagelatches auftreten.

Vor- und Nachteile der verschiedenen Varianten


Jede der oben vorgestellten Varianten hat seine Vor- und Nachteile. Die Geschwindigkeit beim Eintragen von Daten, die man sich mit einer GUID-Variante erkauft, muss unter Umständen mit teuren Table Scans und mit erhöhtem Aufwand für Index Maintenance bezahlt werden.

Fragmentierung


Wie bereits weiter oben dargestellt, können Page Splits auch bei IDENTITY-Werten für einen Clustered Key vorkommen. Dieser Umstand führt dazu, dass trotz fortlaufendem numerischen Clustered Key die logische Fragmentierung auf den Datenseiten unverhältnismäßig hoch sein kann.


SELECT  OBJECT_NAME(object_id),

        index_type_desc,

        fragment_count,

        page_count,

        record_count,

        avg_fragmentation_in_percent,

        avg_page_space_used_in_percent

FROM    sys.dm_db_index_physical_stats

(

    DB_ID(),

    OBJECT_ID('dbo.numeric_table', 'U'),

    1,

    NULL,

    'DETAILED'

) AS DDIPS;

GO

DM_DB_INDEX_PHYSICAL_STATS_01

Obwohl eine fortlaufende Nummerierung gewählt wurde, sind ca. 88% logische Fragmentierung zu verzeichnen. Insgesamt ist eine Datenseite zu ~65% gefüllt (Page Density). Das bedeutet für die Auslastung des Buffer Pools ca. 5.250 KB statt 8.060 Bytes.

DM_DB_INDEX_PHYSICAL_STATS_02

Ein direkter Vergleich zwischen [dbo].[numeric_table] und [dbo].[guid_table] zeigt, dass es keinen nennenswerten Unterschied im Fragmentierungsgrad gibt. Beide Tabellen sind zwar hochgradig fragmentiert aber es gibt auf den ersten Blick keinen Vorteil, der für die numerische fortlaufende Variante spricht. Jedoch besitzt der fortlaufenden Clustered Keys den Vorteil, dass nach einem INDEX-REBUILD dieser Bereich des Indexes nicht mehr fragmentiert werden kann, da neue Datensätze immer an das Ende des Indexes geschrieben werden! Bei einer GUID besteht auch bei neuen Datensätzen noch das Risiko, dass der Index innerhalb von bereits bestehenden Daten erneut durch Page Splits fragmentiert wird!

Sind gleichzeitige Workloads das bestimmende Bild in Microsoft SQL Server muss sowohl bei fortlaufenden Indexschlüssel als auch bei variablen Indexschlüsseln mit Fragmentierung gerechnet werden. Grundsätzlich kann man aber behaupten, dass bei deutlich weniger Concurrency sowie nach dem Neuaufbau eines Index ein Vorteil für den fortlaufenden Indexschlüssel besteht!

Größere NONCLUSTERED Indexe


Jeder NONCLUSTERED Index muss den Clustered Key eines Clustered Index zusätzlich abspeichern, damit der Index einen Verweis zu den Daten der Datenzeile hat, die nicht im NONCLUSTERED Index hinterlegt sind. Tatsächlich ergibt sich dadurch mathematisch ein Nachteil für die Verwendung einer GUID als Clustered Index. Eine GUID hat eine Datenlänge von 16 Bytes während der Datentyp INT lediglich 4 Bytes an Speichervolumen konsumiert.
STORAGE_VOLUMINA
Das Ergebnis sollte nicht weiter überraschen. Es versteht sich von selbst, dass bei Verwendung eines größeren Schlüssels das Datenvolumen entsprechend wächst. Hier liegt der Vorteil eindeutig bei einem Clustered Key mit einem kleinen Datentypen (4 Bytes vs. 16 Bytes).

Ascending Key Problem


Das Problem von aufsteigenden Schlüsselattributen im Index habe ich im Artikel “Aufsteigende Indexschlüssel – Performancekiller” sehr detailliert beschrieben. Dieses Problem tritt IMMER auf, wenn ein Schlüsselattribut verwendet wird, dass beständig größere Werte in einen Index einträgt. Da die Statistiken eines Index nicht bei jedem Eintrag neu erstellt werden, kann es vorkommen, dass bei veralteten Statistiken ein schlechter Ausführungsplan generiert wird. Selbstverständlich kann auch bei einer GUID ein solches Problem auftreten, wenn die neue GUID tatsächlich am Ende des Index erstellt wird. Mit zunehmender Datenmenge wird dieses Problem jedoch immer unwahrscheinlicher! Hier liegt ein Vorteil in der Verwendung einer GUID!

Dieser Vorteil ist jedoch für einen CLUSTERED INDEX mit fortlaufender Nummerierung eher vernachlässigbar da ein Wert immer nur ein Mal als Schlüsselattribut vorkommt. Somit ist das Problem “Ascending Key” hier eher eine unbedeutende Randerscheinung!

Replikation


Manche Replikationsszenarien (z. B. MERGE) verlangen ein Attribut in jeder Tabelle, die repliziert werden soll, dass von Datentyp [uniqueidentifier] ist und die Eigenschaft ROWGUIDCOL besitzt. Plant man das Datenbanksystem in einem Replikationsumfeld, bietet sich die GUID als Clustered Key an. Will man auf IDENTITY / INT als Clustered Key nicht verzichten, muss jeder Tabelle explizit ein neues Attribut hinzugefügt werden. Das nachfolgende Codebeispiel zeigt für beide Tabellen die jeweiligen “Änderungen/Anpassungen”:


-- table with contigious numbers as clustered key

CREATE TABLE dbo.numeric_table

(

    Id        INT                 NOT NULL    IDENTITY(1, 1),

    c1        CHAR(400)           NOT NULL    DEFAULT ('just a filler'),

    rowguid   UNIQUEIDENTIFIER    NOT NULL    ROWGUIDCOL,

    

    CONSTRAINT pk_numeric_table PRIMARY KEY CLUSTERED (Id),

    CONSTRAINT uq_numeric_table UNIQUE (ROWGUID)

);

GO

 

-- table with random guid as clustered key

CREATE TABLE dbo.guid_table

(

    Id    UNIQUEIDENTIFIER    NOT NULL    ROWGUIDCOL    DEFAULT(NEWID()),

    c1    CHAR(388)           NOT NULL    DEFAULT ('just a filler'),

    

    CONSTRAINT pk_guid_table PRIMARY KEY CLUSTERED (Id)

);

GO

Das Skript zeigt beide Tabellen aus den vorherigen Beispielen, wie sie für eine MERGE oder PEER-TO-PEER Replikation vorbereitet sein müssen. Während es für die Tabelle [dbo].[guid_table] lediglich der zusätzlichen Eigenschaft “ROWGUIDCOL” für den Clustered Key bedarf, ist der Aufwand (und auch die Länge eines Datensatzes) in einer Tabelle mit einem INT deutlich höher. Zunächst muss ein weiteres Attribut vom Datentypen UNIQUEIDENTIFIER angelegt werden, dass ebenfalls die Eigenschaft “ROWGUIDCOL” besitzt. Für eine bessere Performance wird mittels eines UNIQUE CONSTRAINTS ein weiterer Index hinzugefügt, der die Eindeutigkeit sicherstellt.

Hier geht der Punkt eindeutig an die GUID als Clustered Key, da sich die Datenstruktur / Metadaten nicht verändern. Während die Länge eines Datensatzes in der Tabelle [dbo].[numeric_table] um 16 Bytes erweitert werden muss, bleibt die Datensatzlänge in der Tabelle [dbo].[guid_table] unverändert bei 411 Bytes. Ebenfalls wird kein weiterer Index für die Durchsetzung der Eindeutigkeit benötigt!

Zusammenfassung


Die generelle Ablehnung von GUID als Schlüsselattribut in einem Clustered Index wird zu häufig mit pauschalen / schon mal gehörten / Behauptungen untermauert. Sie ist ungerechtfertigt, wenn man nicht den Workload berücksichtigt, der einem DML-Prozess zu Grunde liegt.

Die Verwendung von GUID macht aus der Sicht des Autors dann Sinn, wenn sehr viele Daten von sehr vielen Prozessen eingetragen werden. Handelt es sich eher um ein System, mit dem sehr wenig Benutzer arbeiten oder aber das nur “gelegentlich” neue Datensätze in der Datenbank speichert, so sollte auch weiterhin mit dem favorisierten IDENTITY / INT gearbeitet werden.

GUID sind ideal für parallele ETL-Workloads in Stagingtabellen wenn man unbedingt einen Clustered Index verwenden möchte. Grundsätzlich sollten ETL-Workloads nur in HEAPS ihre Daten speichern. Alle anderen Varianten sind aus Sicht von Durchsatz und Zeit eher eine Bremse im sonst so performanten Ladeprozess! Aber das ist ein ganz anderes Thema, auf das ich im nächsten Artikel detaillierter eingehen werde.

Herzlichen Dank fürs Lesen!

Robert Panther: Neuer Datentyp für SQL Server vNext

Wieder einmal sind aus gut informierten Kreisen ein paar Details zu einem möglichen neuen Feature für die nächste Version von SQL Server bekannt geworden. Während allgemeine Trends wie bessere Performance, höhere Verfügbarkeit und bessere Cloud Integration sicherlich weiter fortgesetzt werden, ist dieses Feature für die meisten eher überraschend:

Neuer Datentyp: FUZZYBIT

Während der altbekannte Datentyp BIT nur eine zwei- (TRUE/FALSE) bzw. dreiwertige (TRUE/FALSE/NULL) Logik abbildet, sind mit dem neuen Datentyp FUZZYBIT auch Zwischenwerte möglich. Der Wert NULL erlaubt es ja heute bereits, einen unbekannten Wert zu repräsentieren. Mit dem FUZZYBIT kommen nun noch die Zwischenstufen LIKELY (wahrscheinlich) und UNLIKELY (unwahrscheinlich) dazu, so dass sich daraus eine sogenannte fünfwertige Logik ergibt. Um das Wording durchgehender zu gestalten, kann man anstelle von NULL auch das neue Schlüsselwort UNKNOWN verwenden, so dass sich damit insgesamt folgende Abstufungen ergeben:

TRUE – LIKELY – UNKNOWN – UNLIKELY – FALSE

Mit diesem neuen Datentyp wird es künftig einfacher möglich sein, Erkenntnisse aus unstrukturierten Big Data Datenquellen in einem relationalen Data Warehouse abzulegen.

Der Name FUZZYBIT leitet sich aus dem Begriff “Fuzzy Logic” ab, der ebenfalls für eine stärkere Differenzierung der zweifachen Booleschen Logik steht. Offen ist bisher noch, ob das Prinzip der Fuzzy Logic auch auf andere Datentypen ausgeweitet wird.


Andreas Wolter: How to import Extended Events session file target and parse deadlock-graph / Import von Extended Events Ereignisdateien und Parsen von Deadlock-Graphen

Andreas Wolter: Die SQL PASS Deutschland sucht Sprecher – Aufruf an alle SQL Server Fachleute

SQLPodcast: #010 – Microsoft PDW & APS

Im Gespräch mit Markus Raatz rund um das Thema Microsoft PDW & APS

Sascha Lorenz: Data Vault in der SQL Server Data Warehouse Welt

Meine Session auf der SQL Server Konferenz 2015 in Darmstadt zeigte mir, dass das Thema Data Vault durchaus polarisiert. Und da ich bei immer mehr Kundensituationen von uns auf Interesse zum Thema Data Vault treffe bzw. auch schon erste Architekturen vorfinde, möchte ich hier mal wieder ein paar Worte zu meiner Sicht der Lage äußern.

Meinem Verständnis nach brachte die Diskussion der letzten Monate um die Vor- bzw. Nachteile des Einsatzes der Data Vault Modellierung einen notwendigen frischen Wind in die Data Warehouse Welt. Und zwar jenseits des allgemeinen Big Data Hypes.

Data Vault kann durchaus als eine besondere Form des Stagings verstanden werden, wobei ich DV eher als einen Ansatz für die Modellierung eines Core Data Warehouses verstanden habe. Des Weiteren stellt Data Vault die rohen, unveränderten Daten in den Vordergrund. Die Idee dahinter ist, dass es keine einheitliche Wahrheit in einem Data Warehouse geben kann. Diese “Wahrheit” ist häufig durch eine Interpretation geprägt. Gerade die Suche nach der vermeintlich vorhanden Wahrheit in den Daten lässt viele Data Warehouse und Business Intelligence Projekte scheitern oder zumindest in Schönheit sterben.

Inwieweit passt nun die Data Vault Modellierung (& Nutzung) in die Microsoft Welt?

Das klassische Data Warehouse basiert auf relationalen Datenbanken. Die Bewirtschaftung mittels ETL- oder ELT-Strecke erfolgt meist mit einem Mix von SSIS und T-SQL Komponenten. Im Übrigen liegen häufig gerade in der Konzeptionierung der Strecke viele zukünftige (und zum Teil versteckte und damit nicht wahrgenommene) technische Schulden.

Eine bekannte Herausforderung bei DWH Projekten ist, dass sich diese häufig nicht wirklich eignen für agile Projektstrukturen. Auf der anderen Seite sterben viele DWH Projekte gerade wegen den sonst häufig starren Steuerungsstrukturen einen grausamen und meist auch teuren Tod. Es gibt mittlerweile eine Reihe von valide Ansätze den agilen Ansatz in die Data Warehouse Welt zu tragen, dennoch empfinde ich viele Ansätze, welche ich so vorfinde, als eher einen agil geprägten Projektverlauf, sprich völliges Chaos.

Data Vault kann tatsächlich mehr Agilität in DWH Projekte bringen. Ein Grundsatz im DV ist, dass es nur zusätzliche Objekte geben kann und keine Änderungen an bestehenden Strukturen durchgeführt werden. Durch diese und andere klare Regeln ist die Modellierung eines Data Vault erstaunlich stringent und passt sich dennoch dem unruhigen Fahrwasser im Datengeschäft an. 

Wichtig ist es dennoch nochmals hervorzuheben, dass ein Data Vault eben gerade nicht die traditionellen Data Warehouse Modellierungsmethoden ablösen will oder sollte. Meinem Verständnis nach ist es eine sinnvolle Ergänzung einer DWH Architektur. Primär wird sie von mir genutzt, um Quellen zu harmonisieren und ihre Strukturen homogen zu gestalten. Das vereinfacht die weitere Nutzung dieser Daten in der DWH Strecke ungemein. Gerade im Kontext Verarbeitung von Daten in Echtzeit kann diese Investition helfen.

Braucht nun wirkliches jedes (neue) Data Warehouse ein Data Vault? Eher nicht. Es kommt wie so häufig mal wieder drauf an.

Für mich ist der Grad an Stabilität eines geplanten Data Warehouses entscheidend. Inwiefern werden die Quellen als auch die Anforderungen stabil bleiben oder ist mit diversen Erweiterungen und Änderungen zu rechnen? Sofern diese Frage nicht beantwortet werden können oder die Antwort nicht belastbar genug ist, empfehle ich den Einsatz eines Data Vaults, um ein wenig Ruhe in die Strukturen zu bekommen.

In Abgrenzung zu klassischen DWH Modellierungen liefert das Data Vault Konzept auch Muster für die Bewirtschaftung mit. Unter anderem ist die Wiederholbarkeit von Bewirtschaftungsjobs möglich ohne sich mit der Herausforderung von möglichen Dubletten beschäftigen zu müssen. Wer sich schon länger mit DWH Umgebungen beschäftigt, weiß diese Eigenschaft durchaus schätzen zu lernen. 

Wenn wir kurz noch gedanklich in der SQL Server Welt bleiben, stellen sich natürlich Fragen, wie denn nun ein Data Vault zum Beispiel mittels der Analysis Services genutzt werden können. Diese erwarten ja einen klassischen Stern als Quelle. Meiner Erfahrung nach nutzen hier viele Anwender zurzeit die Option den Zugriff direkt mittels Views zu realisieren. Mit einer solchen Architektur bin ich nicht immer einverstanden, da ich gerade bei größeren Cubes hier durchaus Probleme bei der Verarbeitung erlebt habe. Häufig wird das Persistieren des Data Mart für einen Würfel als zu umständlich und zeitraubend angesehen. Es gilt noch belastbare Konzepte durch Lab Arbeit und Projekt Erfahrung zu sammeln, um diese Frage zufriedenstellend beantworten zu können.

Falk Krahl: Neue Updates für SQL Server 2012

Bereits gestern sind für den SQL Server 2012 zwei neue Updates erschienen. Zum einen handelt es sich um das kumulative Update 05 für den SQL Server 2012 mit Servicepack 2 und zum anderen um das kumulative Update 15 für den SQL Server 2012 mit Servicepack 1. Diese können unter den folgenden Links angefordert werden:
Kumulatives Update 15 für SQL Server 2012 SP1
Kumulatives Update 05 für SQL Server 2012 SP2

Bernd Jungbluth: Seminar - SQL Server Reporting Services

Dieses Mal findet das Seminar SQL Server Reporting Services am 26. März 2015 statt.

Veranstaltungsort ist wieder das Hotel Best Western Premier Bellevue Rheinhotel in Boppard am Rhein.

Mit den SQL Server Reporting Services bietet Microsoft eine zentrale Informationsplattform für Unternehmen, die aktuelle und applikationsübergreifende Informationen in interaktiven Berichten zur Verfügung stellt.

Die Bereitstellung dieser zentralen Informationsplattform ist Inhalt dieses Seminars.

Es wird das gesamte Spektrum eines Berichts betrachtet – von der Erstellung über die Verwaltung bis hin zur Bereitstellung.
Dabei liegt der Fokus auf der Berichtserstellung mit dem Berichtsdesigner.

In diesem 1-tägigen Seminar lernen Sie
- Reporting Services als zentrale Informationsstelle zu nutzen
- Dynamische Berichte zu erstellen
- Berichte und Berichtsserver zu verwalten
- Berichte im Berichtsmanager bereitzustellen
- Berichte zu abonnieren

Die Teilnehmerzahl ist auf 8 Personen begrenzt.

Preis: 375 Euro pro Person inklusive Mittagessen und Getränke.

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

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.

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 Update 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

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


    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!

    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

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