SQL Server & BI Blog by Andreas Wolter

(en)
Finally I get to write about my conference-talks in 2015.

The year started really great with the German SQL Server Konferenz in Darmstadt with 2 sessions on In-Memory OLTP in SQL Server 2014 – one of them being even a full-day PreCon, which I co-presented with Niko Neugebauer,  who was talking about Clustered ColumnStore Indexes.

(de)
Endlich komme ich dazu, auf meine Konferenz-Vorträge dieses Jahr einzugehen.

Das Jahr begann gleich großartig mit der Deutschen SQL Server Konferenz in Darmstadt mit 2 Sessions zu In-Memory OLTP in SQL Server 2014 – eine davon sogar eine ganztägige PreCon, welche ich mit Niko Neugebauer co-präsentierte, der über Clustered ColumnStore Indexe sprach.

In March I was happy to have been speaker for the second time at the SQLBits in London -
probably THE event outside of PASS in Europe if not worldwide.
There I presented one of my favorite topics on Performancetools: Extended Events

Im März war ich glücklich zum zweiten Mal auf den SQLBits in London als Sprecher zu sein – wahrscheinlich DEM Event außerhalb der PASS in Europe wenn nicht weltweit.
Dort präsentierte ich zu einem meiner Lieblingsthemen zu Performancetools: Extended Events

In April I joined the SQLDay Poland for the first time, giving a session on Locking & Blocking and a second session on Security, namely SQL Injection:

Im April war ich das erste Mal auf dem SQLDay Poland  , mit einer Session zu Sperren & Blockaden und einer zweiten Sessio zu Sicherheit, genauer SQL Injection:

For June I am happy to be able to announce that I will again present on Extended Events at the SQL Saturday #409 Rheinland/Germany. -
In fact I have already presented at the first German SQL Saturday in 2012 on Extended Events (“Tracing with SQL Server 2012 Extended Events”) – I hope this year it will be the final round to get even the last one off from the old & dusty SQL Profiler.

Added to that I am honored to give one of the 2 full day and free of cost PreCons: Together with Patrick Heyde, Microsoft (Technet-Blog), I will talk on SQL Server in Azure Environments. Specifically on how to optimally configure such a deployments to performance and cost-efficiency at the same time. (Also see my blog article “SQL Server in Microsoft Azure: How to gain performance by flexibility and save costs at the same time”)

Besides giving a free PreCon and regular session, my Company Sarpedon Quality Lab is again officially sponsoring this home-event. This is part of my commitment to the German PASS Community – especially since my own family starts taking more time, this is a bit of what I can do to support the PASS Deutschland e.V..

Für den Juni darf ich mich glücklich schätzen, bekanntzugeben, dass ich auf dem SQL Saturday #409 Rheinland wieder zu Extended Events präsentieren werde. – Tatsächlich hatte ich bereits auf dem ersten Deutschen SQL Saturday 2012 zu Extended Eventzs präsentiert
(“Tracing with SQL Server 2012 Extended Events”) – Ich hoffe dieses Jahr wird die letzte Runde sein, um auch den Letzten von dem alten & verstaubten SQL Profiler abzuholen.

Zusätzlich dazu fühle ich mich geehrt, eine der 2 ganztägigen und kostenlosen PreCons zu geben: Zusammen mit Patrick Heyde, Microsoft (Technet-Blog), werde ich über SQL Server in Azure Umgebungen sprechen. Und zwar, wie man solche Deployments optimal konfiguriert um sowohl Performance- als auch Kosteneffizient zugleich zu sein. (Siehe auch meinen Blog-Artikel “SQL Server in Microsoft Azure: Wie man durch Flexibilität Leistung gewinnt und zugleich Kosten spart”)

Abgesehen von der kostenblosen PreCon und regulärem Vortrag, ist meine Firma Sarpedon Quality Lab wieder offizieller Sponsor dieses Events. Das ist Teil meines Engagements für die deutsche PASS Community – speziell seit meine eigene Familie mehr Zeit beansprucht, ist das ein wenig dessen, was ich tun kann, den PASS Deutschland e.V. zu unterstützen.

In September I will be at the SQLSaturday #413 Denmark in Copenhagen, organized among other by Regis Baccaro. I will be giving yet another PreCon on In-Memory OLTP and Clustered ColumnStore and a regular session on SQL Server storage.

Im September werde ich auf dem SQLSaturday #413 Denmark in Kopenhagen, organisiert neben anderen von Regis Baccaro, sein. HIer gebe ich wieder eine PreCon zu In-Memory OLTP und Clustered ColumnStore und eine reguläre Session zu SQL Server Speicher.

(DE)
Diesmal widme ich mich erstmalig in einem Artikel den Thema Microsoft Azure.
(Microsoft.com: Was ist Microsoft Azure?)
Unter Microsoft Azure werden mittlerweile eine Unmenge an Diensten bereitgestellt, und einer davon ist das Hosting von virtuellen Machinen, in denen ein SQL Server Dienst läuft. Hierbei sprechen wir also von IaaS (Infrastructure as a Service).

- Hier wird dieses Service-Modell nähergehend erläutert und unter anderem auch dem PaaS-Ansatz gegenübergestellt:
Which Windows Azure Cloud Architecture? PaaS or IaaS?

Eine schöne gegenüberstellende Grafik findet sich in diesem Blog-Artikel:
Windows Azure IaaS vs. PaaS vs. SaaS

Nachdem man sich einmal entschieden hat, dass das Konzept IaaS für einen Teil der eigenen Umgebung Sinn macht, steht die Frage der Konfiguration der SQL Server Systeme an.
Hier werden in dem Azure Portal fertige Images angeboten, die gerade Einsteigern den Zugang erleichtern sollen.
- Tatsächlich kann man mit nur 7 Klicks einen Windows Server inklusive lizensierten SQL Server einrichten, wenn man eine Vorlage aus der Galerie verwendet.

(EN)
This is the first time that I am tackling Microsoft Azure in an article. (Microsoft.com: What is Microsoft Azure?)

A plethora of services is provided by Microsoft Azure by now, of which the hosting of virtual machines running on an SQL Server service is one. This is what we call IaaS (Infrastructure as a Service).

- Here, this service model will be explained in more detail and, among others, compared to the PaaS approach: Which Windows Azure Cloud Architecture? PaaS or IaaS?

- A nice comparative graph is available in this blog article:
Windows Azure IaaS vs. PaaS vs. SaaS

After determining that the concept IaaS makes sense for part of one’s own environment, the issue of the SQL Server Systems configuration will be next. In the Azure Portal ready-made images are available that will facilitate access especially for starters.
- In fact, with only 7 clicks it is possible to set up a Windows Server including a licensed SQL Server when using a template from the gallery

Für den produktiven Einsatz von SQL Server muss man sich jedoch etwas mehr Mühe geben, denn die Standard-Vorlagen enthalten nur eine Daten-Disk, und auf der liegt das Betriebssystem. – Dort möchte man seinen SQL Server aus mehreren Gründen nicht betreiben.
Neben Datenintegrität ist das die IO-Performance.

Microsoft stellt daher auch sogenannte „optimized“ Images -für entweder OLTP- oder OLAP-Szenarien zur Verfügung (Im Screenshot rot umrahmt), welche direkt mit 15 weiteren Data Disks kommen, was dann insgesamt 16 macht.

However, for the productive application of SQL Server some more efforts are required, as the standard templates merely contain a data disc in which the operating systems is located. – Yet there are several reasons as to why one should not run one’s SQL Server here: data integrity and IO performance.

Therefore, Microsoft also provides so-called “optimized” images – for OLTP or OLAP scenarios – (highlighted by red frame in the screenshot) that immediately come with 15 more data discs making a total of 16.

1) Variante 1, der „traditionelle Ansatz“ ist also: mehrere Data Disks und eine Maschine mit entsprechender Unterstützung/CPU-Power.

Die in diesem Fall 15 Daten Disks (neben der OS Disk) werden in 2 Storage Pools zu je 12 bzw. 3 Disks für SQL-Daten und SQL-Logs vorkonfiguriert.

Die maximalen IOPS hängen auch von den zur Verfügung stehenden CPU-Cores ab. Lineare Performance-Steigerungen sollte man auch nicht erwarten.

1) Option 1, the “traditional approach” hence is: several data discs and a machine with corresponding support/CPU power.

The 15 data discs (additionally to the OS disc) in this case are pre-configured for SQL data and SQL logs in 2 storage pools of 12 and 3 discs each.

The maximum IOPS also depend on the available CPU cores. One should not expect linear performance increases anyway.

Das Problem hierbei: Man verliert dabei fast jegliche Flexibilität.
Und zwar hinsichtlich der Ausbaustufe (Performance) und damit letztlich auch in der Preisgestaltung.
Denn um die insgesamt 16 Data Disks zu verwenden, muss man mindestens eine der 8-Core VM-Größen betreiben (A4, A7, A8, A9, D4, D13, D14, G3, G4 und G5), durchgängig.

Lediglich nach oben kann man Skalieren.
- Wer mehr als 16 zusätzliche Datenträger benötigt, muss eine VM mit 16 bzw. 32 Cores (G-Serie) einsetzen und erhält dann die Möglichkeit bis zu 32 bzw. 64 Data Disks neben der OS Disk einzusetzen.
Nach unten ist man dann auf jeden Fall preislich festgelegt.

The problem here is: You lose almost any flexibility, i.e. in terms of configuration level (performance) and ultimately also in terms of pricing.

Because in order to use the total of 16 data discs it is necessary to consistently operate one of the 8-core VM sizes (A4, A7, A8, A9, D4, D13, D14, G3, G4 and G5). It is only possible to scale upwards.

- If you need more than 16 additional data carriers you will have to apply a VM with 16 or 32 cores (G-series), which will enable you to apply up to 32 or 64 data discs besides the OS disc.
In terms of pricing, this will definitely set the limit downwards.

Damit dürfte das Ziel, Kosten durch Cloud-basierte Systeme zu sparen, jedoch schwieriger zu erreichen sein.
Die große Stärke des Cloud-basierten Ansatzes ist es aber, möglichst genau nur dann, wenn man eine bestimmte Leistung (oder Dienst) benötigt, diese anzufordern & zu erhalten, und wenn man sie nicht benötigt, diese auch nicht nutzlos „aktiviert“ zu belassen. Denn man bezahlt ja, was man „abonniert“, was hier nicht unbedingt auch das ist, was man wirklich nutzt.

Unser ideales System sollte also maximal skalierbar sein, und zwar sowohl nach oben und nach unten.

This, however, makes it more difficult to reach the goal of saving costs through cloud-based systems.

Yet the great strength of the cloud-based approach is ideally only when requiring a specific performance (or service) to request and receive it, and when not needed, to not leave it idly “activated.” For you only pay for what you “subscribe” to, which in this case is not necessarily what you actually use.

Our ideal system, thus, should be maximally scalable, both upwards and downwards.

This will probably become clearer if you look at the charts with the current (status: 30 April 2015) virtual machines and their performance core sizes.

Zu sehen sind die Anzahl der dedizierten CPU-Cores, die Größe des Arbeitsspeichers, die Größe der Temp-Disk, und, ganz wichtig für die Skalierbarkeit: die Anzahl der maximal erlaubten Datenträger neben der OS-Disk selber.

Pro Datendisk erhält man bis zu 500 IOPS. Um mehr IOPS zu erhalten, benötigt man also mehr Data Disks – aber auch mehr CPU Cores.
Wenn man jedoch eine Maschine mit 16 Data Disks verwendet, kann man in Zeiten geringerer Auslastung kaum herunter-skalieren. Eine A2 beispielsweise für eine Art Minimal-Betrieb ist damit unerreichbar. Und wenn man noch mehr Data Disks benötigt um IO-Peaks abfangen zu können, schränkt man sich noch mehr ein und muss dauerhaft die teuersten Maschinen bezahlen.

You can see the number of dedicated CPU cores, the size of the working memory, the size of the temp disc, and, very important for the scalability: the number of the maximally permitted data carriers besides the OS disc itself.

Per data disc you get up to 500 IOPS. To receive more IOPS, thus, you need more data discs – but also more CPU cores. However, when using a machine with 16 data discs it will hardly be possible to scale downwards in times of low utilization rates. An A2, for example, will thus be unreachable for some kind of minimum operation. If you need more data discs to be able to accommodate IO peaks you will restrict yourself further and will have to continually pay for the most expensive machines.

Welche Alternativen gibt es? Wie kann man flexibel sein, um Kosten zu sparen, und gleichzeitig je nach Bedarf auf höhere Maschinen wechseln („Scale-Up“), und auf der anderen Seite nachts oder an Wochenenden seine Maschinen auf minimale CPU’s beschränkt („Scale-Down“).

Are there any alternatives? How to be flexible in order to save costs and at the same time switch to higher machines (“scale-up”) if necessary, and on the other hand restrict your machines to minimal CPUs during nighttime or on weekends (“scale-down”)?

2) Speichern der Data-Files direkt auf Azure Blob-Store

Der offensichtliche Vorteil ist hierbei, dass man keine Data-Disks benötigt – und die sind es, die die Skalierungsmöglichkeiten wesentlich beschränken. Anstelle der Data Disks werden die SQL Server Datenbankdateien direkt im Blob-Store gespeichert.

Die Datenbank-Erstellung kann dann so aussehen:

1) Saving data files directly on the Azure Blob Storage

The obvious advantage here is that data discs are not required – and these are what significantly limit scaling options. Instead of the data discs, the SQL Server database files are stored directly in the Blob storage.

The database creation can look something like this:

Diese Möglichkeit wird seit SQL Server 2014 unterstützt.
Hierbei erhält man ebenfalls pro File 500 IOPS, mit einem Limit von 20.000 je Storage Account, welches generell gilt.

Hier findet sich ein ausführliches Beispiel zur Einrichtung samt Code:
Create a SQL Server 2014 Database directly on Azure Blob storage with SQLXI

Der Nachteil bei dieser Variante ist in meinen Augen die Komplexität. Das Vorgehen mit Einrichtung der Shared Access Signature, die für den Zugriff auf den Blob-Container benötigt wird, ist nicht direkt trivial.

This option is supported since SQL Server 2014.
Here, too, you get 500 IOPS per file, with a general limit of 20.000 per storage account.

Here you can find a detailed example of the setup including a code:
Create a SQL Server 2014 Database directly on Azure Blob storage with SQLXI

The disadvantage of this option is, in my view, the complexity. The approach to creating the Shared Access Signature required for accessing the Blob container is not really trivial.

3) Speichern der Data-Files auf einer Azure Datei-Freigabe

Seit Mai letzten Jahres ist der Azure File-Service (Introducing Microsoft Azure File Service) als Vorschaufeature verfügbar.

Neben „echten“ Verzeichnissen unterstützt dieser Dienst auch Freigaben auf Basis von SMB 2.1.
Hier gibt es ein Maximum an 1000 IOPS pro Share. Das heißt ich benötige für dieselbe Menge an IOPS nur halb so viele Dateien wie für den Direkt-Zugriff auf Azure Blobs.
Wichtig ist, dass man zunächst das Preview-Feature für einen Storage-Account aktiviert hat.

3) Storing data files on an Azure File Share

Since May last year, the Azure file service (Introducing Microsoft Azure File Service) has been available as preview feature.

In addition to “real” directories, this service also supports releases on the basis of SMB 2.1.
Here we get a maximum of 1000 IOPS per share. I.e. for the same amount of IOPS only half as many files are required as for the direct access to Azure Blobs.
It is important to start by activating the preview feature for a storage account.

Danach erzeugt man per PowerShell die notwendigen Shares und verteilt seine Datenbank-Dateien darauf.

Next, you create the necessary shares per PowerShell and distribute your database files on them.

Auch hier gilt natürlich die Begrenzung auf die maximalen 20.000 IOPS je Storage Account. Aber der Zugriff ist in meinen Augen wesentlich einfacher.
Ein Nachteil dieser Variante ist, dass man dafür sorgen muss, dass der SQL Server Dienst erst startet, wenn die Freigaben über Netzwerk auch erreichbar sind.
Abseits von manchmal auftretenden Zugriffsproblemen, die sicherlich dem Preview-Stand geschuldet sind, ist diese Kombination in meinen Augen die am Einfachsten zu Administrierende, nachdem man sich einmal an einen automatisch gesteuerten verzögerten Dienst-Start gewöhnt hat.

Der Azure-File-Dienst wird zurzeit mit 50% Rabatt angeboten, und liegt damit rund 20% unter den Azure-Blob-Storage-Preisen.

A disadvantage of this option is that you have to make sure that the SQL Server service starts only when the shares are available via network.
Aside from occasionally occurring access problems that can probably be attributed to the preview status this combination, as I see it, is the easiest one to administrate – once you got used to the automatically controlled delayed service start. 

The Azure File service is currently offered with a 50% discount and is thus at around 20% below the Azure Blob storage prices.

Der Vorteil der beiden letzten Varianten liegt klar auf der Hand: man ist nicht an eine bestimmte Ausbaustufe des Systems gebunden, sondern kann zu bestimmten Zeiten seinen SQL Server auf einer Maschine mit weit mehr oder auch weit weniger CPU-Cores starten.

The advantage of the latter two options is evident: you are not bound to a particular configuration level of the system but you can start your SQL Server on a machine with far more or also far less CPU cores at certain times.

Ein Wort noch zum Transaktionsprotokoll:

Pro Datenbank gibt es nur eine Log-Datei (mehrere Log-Dateien würden sequentiell beschrieben werden und keinerlei Performance-Vorteile bringen). Dort bringen File-Shares direkt den Vorteil, anstelle von 500 IOPS 1000 IOPS zu liefern. Wenn das nicht ausreicht, bleibt leider nur der traditionelle Ansatz im Zusammenspiel mit Windows Server Storage Spaces: ein Striping aus mehreren Data-Disks für das Transaktionsprotokoll mit dem entsprechenden Skalierbarkeitsnachteil.

One final remark on the transaction log:

There is only one log file per database (several log files would be written to sequentially and not bring any performance advantages). There, the immediate benefit is that file shares deliver 1000 IOPS instead of 500 IOPS. If this is not sufficient, only the traditional approach combined with Windows Server Storage Spaces remains, unfortunately: striping of several data discs for the transaction log with the according scalability disadvantage.

Ich hoffe, ich konnte in diesem Artikel den für mich wichtigsten Vorteil des Cloud-basierten Ansatzes am Beispiel SQL Server etwas näherbringen. Sobald man sich einmal an das „Service-Konzept“ der Cloud gewöhnt hat, und traditionelle Denkmuster in der Form von „Ich benötige eine x-Core-Maschine“ hinter sich lassen kann, kann man durch das Kombinieren von verschiedenen Diensten, wie hier Virtuellen Maschinen und Dateidiensten, sehr kosten- und performance-effiziente Systeme bauen.

Und natürlich sind nicht immer IOPS das Maß der Dinge. Ich habe diese nur zur Vereinfachung über MB/sec gewählt und auch ohne auf die Request-Größe Rücksicht zu nehmen. Im Allgemeinen sind die Werte auf Basis von 4-K sequentiellen Lese-Requests zu verstehen. Das gilt aber für alle Speichermechanismen, die hier angesprochen wurden, und sollte daher zum Zwecke der Vergleichbarkeit ausreichen.

I hope this article made somewhat tangible what to me is the biggest advantage of the Cloud-based approach through the example of SQL Server.  As soon as you get used to the Cloud’s “service concept” and leave behind traditional thinking patterns like “I need an x-core machine” you can build very cost- and performance-efficient systems by combining different services, such as, as demonstrated above, virtual machines and file services.

Of course, IOPS are not always the ultimate performance indicator. I have chosen them over MB/sec for simplifying reasons alone and without taking into account the request-size. In general, the values are to be understood based on 4-K sequential reading requests. This applies to all storage mechanisms that have been addressed here and should therefore suffice for the purpose of comparability.

Wer Interesse hat, sich mit dem Thema noch mehr auseinanderzusetzen ist herzlich willkommen auf dem SQLSaturday Rheinland, einer 1-tägigen kostenfreien Konferenz für SQL Server am 13. Juni in Sankt Augustin.
Und am 12. Juni, also den Tag davor gibt es eine ebenso kostenfreie PreCon, „Hybrid IT Camp: Azure Szenarien & die eigene flexible Infrastruktur für jedermann“. (Short Link: http://bit.ly/sqlsat409hybridit), die ich zusammen mit Patrick Heyde von Microsoft (Blog/Twitter) durchführe.

Those who are interested in further dealing with this topic are welcome to join the free one-day SQL Server conference SQLSaturday Rheinland on 13 June in Sankt Augustin.

On 12 June, the day before, there will also be a free PreCon, Hybrid IT Camp: Azure Szenarien & die eigene flexible Infrastruktur für jedermann (“Azure scenarios & individual, flexible infrastructures for everybody”) (Short Link: http://bit.ly/sqlsat409hybridit), which I will be running with Patrick Heyde (Blog/Twitter) from Microsoft.

Hier noch eine Liste an weiterführenden Links:

• Einschränkungen für Azure-Abonnements und Dienste, Kontingente und Einschränkungen
• Preise für Azure-Speicher
• Create a SQL Server 2014 Database directly on Azure Blob storage with SQLXI
• Introducing Microsoft Azure File Service
• Verwenden des Azure-Dateispeichers
• Microsoft Azure Vorschaufeatures
• Wie skaliert man eine Azure SQL Datenbank on Demand und vollautomatisch
• Best Practices & Disaster Recovery for Storage Spaces and Pools in Azure
• New VM Images Optimized for Transactional and DW workloads in Azure VM Gallery

Below is a list for further reading:

• Azure Subscription and Service Limits, Quotas, and Constraints
• Azure Storage Pricing
• Create a SQL Server 2014 Database directly on Azure Blob storage with SQLXI
• Introducing Microsoft Azure File Service
• How to use Azure File storage with PowerShell and .NET
• Microsoft Azure Preview features
• Best Practices & Disaster Recovery for Storage Spaces and Pools in Azure
• New VM Images Optimized for Transactional and DW workloads in Azure VM Gallery

Am 1. April 2015 bin ich zum 2. Mal mit dem MVP Award (MVP=Most Valuable Professional) von Microsoft ausgezeichnet worden.
Zum MVP-Profil.

1 April 2015 I have been awarded for the second time with the MVP Award by Microsoft (MVP=Most Valuable Professional)
My MVP profile.

2 MVP Awards (2014 und 2015) sowie 2 Certified Master-Titel darf ich mir nun zuschreiben :-)
(MCSM (Microsoft Certified Solutions Master) Data Platform on SQL Server 2012)

I can accredit myself with 2 MVPs (2014 und 2015) as well as 2 Certified Master-Titles :-)
(MCSM (Microsoft Certified Solutions Master) Data Platform on SQL Server 2012)

Ich freue mich sehr über diese erneute Anerkennung meiner Aktivitäten für den SQL Server, speziell im Rahmen der Professional Association for SQL Server (PASS) und insbesondere des PASS Deutschland e.V..

2014 habe ich auf 10 Konferenzen und 6 Regionalen User-Gruppentreffen insgesamt 15 Vorträge sowie 2 PreCons gehalten. Zudem hielt ich 2 PASS Essentials zum Thema Performance Monitoring.

Los ging es gleich mit der SQL Server Konferenz, die die PASS Deutschland e.V. zusammen mit Microsoft Deutschland organisierte, wo ich In-Memory OLTP mit XTP (Hekaton) in SQL Server 2014 präsentierte. (2013 hatte ich die In-Memory Engine des SQL Server bereits erstmalig öffentlich in Deutschland vorgestellt.)

Dazu kamen einige Einsätze auf speziellen Microsoft-Konferenzen, wie dem Microsoft Technical Summit. Vielen Dank für das Vertrauen an dieser Stelle auch an Microsoft, mir immer wieder Gelegenheiten zu geben, mein Wissen auf solchen Events auch speziell an andere Partner weiterzugeben.

-           Insgesamt bin ich so seit 2009 auf insgesamt 39 Konferenzen mit insgesamt 53 Vorträgen als Sprecher aufgetreten – ohne die Usergroup-Vorträge.

Vielen Dank an Anna Feth, Birgit Huebsch, Isabell Fischer und Katrin Letzel, meine MVP Community Program Manager an für die fortwährende Unterstützung und auch allen anderen Kollegen bei Microsoft, mit denen zusammenzuarbeiten oder auch nur sich auszutauschen mir immer eine Freude und Unterstützung ist: Bernhard Frank, Daniel Weinmann, Denis Mrksa, Florian Endres, Frank Seiwerth, Georg Urban,  Guido Jacobs, Hanna Altheimer, Heike Ritter, Lars Schmoldt, Martin Rinas, Martin Vach, Olivia Klose, Patrick Heyde, Ralph Kemperdick, Stephan Pepersack, Thomas Palm, Volker Hinz, Werner Gauer aus Deutschland, und aus den USA vor allem Jack Richins, Jos de Bruijn und Nadav Helfman vom Security bzw. In-Memory-Engine-Team.
Last but not least bedanke ich mich auch bei den Kollegen von der PASS Deutschland für ein weiteres Jahr guter Zusammenarbeit: Oliver Engels, Klaus Höltgen, Kostja Klein, Tillmann Eitelberg vom Vorstand, Christoph Muthmann als meinem Co-Regionalgruppenleiter, Niko Neugebauer sowie allen anderen RGVs, bei welchen ich in den letzten Jahren Gast sein durfte.
Danke Euch allen.

I am very happy about this second recognition of my activities for SQL Server, especially for the  Professional Association for SQL Server (PASS) and particularly of the PASS Deutschland e.V., - the association of German Chapters.

2014 I have been at 10 conferences and 6 regional chapter group-meetings where I held 15 presentations altogether as well as 2 PreCons.
Furthermore I gave 2 PASS Essentials on the topic of Performance Monitoring.

It started with the German SQL Server Konferenz, which PASS Germany organized together with Microsoft Germany, where I presented In-Memory OLTP with XTP (Hekaton) in SQL Server 2014. (2013 I presented the In-Memory Engine of SQL Server already for the first time ever in public in Germany.)

Added to that I have been at several special Microsoft-conferences, like the Microsoft Technical Summit. Many thanks at this point to Microsoft for their confidence, to give me such opportunities to pass on my knowledge at such events also especially to partners.

-           All in all, since 2009 I have been at 39 conferences with altogether 53 presentations – without the Usergroup-presentations

Many thanks to Anna Feth, Birgit Huebsch, Isabell Fischer und Katrin Letzel, my MVP Community Program Managers for their ongoing support and also all other colleagues at Microsoft with who working together or exchanging ideas has always been a joy and support for me:
Bernhard Frank, Daniel Weinmann, Denis Mrksa, Florian Endres, Frank Seiwerth, Georg Urban,  Guido Jacobs, Hanna Altheimer, Heike Ritter, Lars Schmoldt, Martin Rinas, Martin Vach, Olivia Klose, Patrick Heyde, Ralph Kemperdick, Stephan Pepersack, Thomas Palm, Volker Hinz, Werner Gauer from Germany, and from the US above all Jack Richins, Jos de Bruijn and Nadav Helfman from the Security respectively In-Memory-Engine Team.
Last but not least I also want to say thank you to my colleagues from PASS Germany for another year of excellent teamwork:
Oliver Engels, Klaus Höltgen, Kostja Klein, Tillmann Eitelberg from the headquarter, Christoph Muthmann as my Co-Chapterleader, Niko Neugebauer as well as all other chapter leaders where I was welcomed as a guest over the last years.
Thank you everybody.

(DE)
Diesmal soll es um etwas gehen, das Extended Events direkt von Haus aus besser und leichter verfügbar machen, als sämtliche Techniken zuvor:
Speichern von aufgetretenen Deadlock-Ereignissen, und das Analysieren der entsprechenden Deadlock-Graphen.

(EN)
This time it’s about one of the things that Extended Events do better and more easy than all techniques before:
Saving of deadlock-events that occurred and analyzing the respective deadlock-graph.

Deadlocks, die man hier erläutert finden kann, treten zwischen mindestens 2 Arbeits-Threads auf, oft aber sind mehr als 2 beteiligt.
- Ich wähle „Thread“ absichtlich, da Prozess nicht genau genug wäre. Meistens treten Deadlocks zwar zwischen verschiedenen Sessions/Prozessen auf, aber es kann sich auch ein Prozess, der auf parallelen Threads ausgeführt wird, selbst blockieren.
Die Fehlernummer für Deadlocks ist die sicherlich allen SQL Profis bekannte „1205“.

Deadlock, explained here, occur between at least 2 threads but often more than 2 are involved.
- I am using the term “thread” on purpose, since process would not be sufficiently accurate. Mostly Deadlocks in fact do happen between different sessions/processes, but it is also possible that a process running with parallel threads blocks itself.
The error number for deadlocks is “1205” – probably known to all SQL professionals.

Um Deadlocks zu Tracen gab es vor SQL Server 2008 nur die folgenden Möglichkeiten:

1. Eine SQL Trace mit Profiler oder auch ohne GUI mit Protokollierung in trc-Datei oder live in der GUI

2. Trace Flags 1204, 1205 oder 1222, welche in das Errorlog des SQL Servers protokollieren – „wunderbar“ besonders auf „ereignisreichen“ Servern, und denkbar schwierig für die Analyse.

Manch einer mag sich noch an diese grausigen Einträge im Error-Log erinnern:

For tracing deadlocks before SQL Server 2008 there were merely the  following ways:

1. A SQL Trace with Profiler or without GUI with logging in a trace-file or live inside the GUI

2. Trace Flags 1204, 1205 or 1222, which log into SQL Server’s errorlog – „great“ especially  on “eventful” servers.

Some may remember those horrible entries in the error log:

3. Event Notifications, welche die Daten per Service Broker Queue in XML-Format zur Verfügung stellen.
Hier findet sich ein ausführliches Beispiel mit Speicherung in Tabelle und automatischem Mail-Versand mit Info über das Event.

4. Ein WMI-Alert, welcher die Daten ebenfalls per XML bereitstellt und mit einem Auftrag oder einer Benachrichtigung gekoppelt diesen protokollieren bzw. darüber informieren kann.

 3. Event Notifications which provide the data via Service Broker Queue in XML-format.
Here you find an elaborate example with storing in table and automatic email with information about the event.

4. A WMI-Alert which also provides the data via XML und tied to a job can also log respectively inform about it.

All diese Ansätze, so unterschiedliche Vorteile sie auch haben mögen haben eines gemeinsam: Sie müssen erst eingerichtet werden. Im Zweifelsfalle also nachdem die ersten Deadlocks aufgetreten sind.

Seit SQL Server 2008 jedoch werden sämtliche Deadlocks von Hause aus protokolliert.

Das geschieht durch die automatisch laufende system_health Extended Event session.

Diese speichert neben Deadlocks noch weitere wichtige Server-Ereignis- und Fehler-Informationen ab. Hier finden sich Details dazu.

Seit SQL Server 2012 protokolliert die system_health session neben dem Memory-Ziel „Ring_Buffer“ auch in die Ereignisdatei, womit Deadlocks und weitere Fehler auch nach Serverneustart noch eine ganze Weile (bis zu mehreren Tagen) zur Verfügung stehen – bis der Rollover der 5 Dateien stattgefunden hat.

Für SQL Server 2008 Instanzen empfehle ich, das Datei-Ziel hinzuzufügen.

All those approaches, as different as their respective advantages may, have ine thing in common: They have to be set up. In case of doubt after the first deadlocks occurred.

But since SQL Server 2008 all deadlocks are being logged by default.

This is done by the automatically running system_health Extended Event session.

This, alongside deadlocks logs further important server events and error-information. Here you find details on it.

Since SQL Server 2012 the system_health session apart from the memory-target “ring_buffer” also logs into an event-file, so that deadlocks and other error stay available even after a server restart for quite a while (up to a few days) – until the rollover of the 5 files has occurred.

For SQL Server 2008 instances I recommend to add the file-target.

Wie kann man diese Daten auslesen?
Das geht per T-SQL relativ einfach, wenn man weiß, dass diese Daten über die DMV sys. dm_xe_session_targets verfügbar sind.
So liest man das Ring-Buffer-Ziel aus:

 How can you read-out that data?
This works quite easily va T-SQL once you know that this data is available via the DMV sys. dm_xe_session_targets.
This is how you read-out the ring_buffer-Target.

„Target_data“ ist XML-text gespeichert als unicode-text-Blob, welches man bei entsprechender Konvertierung zu XML sogar in SSMS direkt öffnen kann.
Um nur die Deadlock-Events dort heraus zu filtern, muss man die XQuery-Erweiterungen von T-SQL bemühen.

Das sieht dann vom Code her so aus:

 „Target_data“ is XML-text stored as unicode-text-blob which can even be opened direct in SSMS after converting to XML.
To filter out only the deadlocks you need to use the XQuery-extensions of T-SQL.
And this is how that looks like in code plus results:

Wenn man einen solchen Deadlock-Report in XML-Format in SSMS anklickt, bekommt man ihn in einem neuen Fenster als XML-Dokument angezeigt:

When klicking at one of those Deadlock-Report reports in XML-format in SSMS it will be opened in a new window as XML-document:

- im Bild habe ich zur Übersicht einige Knoten zugeklappt.

Wichtig hier sind: die victim-list, welche bei einem Multi-Victim-Deadlock dann je Victim eine Prozess-Id enthält, die man wiederum in der process-list näher untersuchen kann.

Im executionStack findet man dann denn ausgeführten SQL-Befehl und ggfl. sogar direkt den Prozedurnamen.

Darunter folgt in der resource-list eine Liste der beteiligten (gesperrten) Ressourcen.

- in the picture I collapsed some nodes for brevity.

Most important here are: the victim-list, which for a multi-victim-deadlock contains one process-id per victim, which again one can investigate more deeply in the process-list.

In the executionStack one can find the executes SQL-command and if applicable even the procedure name.

Below in the resource-list follows a list of all involved (blocked) resources.

All nodes are peppered with details such as row number, objectname, lock-mode, waiting time, spid and of course timestamps.

Wer hier direkt weiter ansetzen möchte, kann das XML natürlich weiter auseinandernehmen und einzelne Knoten versuchen relational in Spaltenform darzustellen.
Dafür muss man zunächst den Deadlock-Graphen extrahieren:

Who wants to start from here can of course take apart the XML and try to break individual nodes relationally into columns.
For that one has to first extract the deadlock-graph.

Von dort aus kann man leicht die einzelnen Knoten parsen.
An das Transaction Isolation Level kommt man z.B. mit der value-Methode, wenn man einmal weiß, auf welcher Ebene/in welchem Knoten des Graphen sie sich befindet:

From there one can parse the individual nodes easily.
The transaction isolation level for example can be reached with the value-method once you know  on which level/in which node in the graph it resides.

Auf die Weise kann man mit fast allen Attributen und Elementen verfahren.
Bei den Sperr-Informationen stößt man dabei jedoch an die Grenzen der statischen relationalen Darstellung, da dort je nach gesperrter Resource völlig andere Attribute auftauchen.

In this manner one can proceed with almost all attributes and elements.
For locking-information one will get to the limits of the static relational representation since depending on the locked resource there will be completely different attributes.

Das zweite Problem tritt dann auf, wenn man Multi-Victim-Deadlocks vor sich hat.

Potentiell kann es ja eine Unmenge an gesperrten und gekillten Victim-Prozessen geben, so dass man da kaum ein immer ausreichendes Schema aufbauen kann.

Hier ein Beispiel eines Multi-Victim-Deadlocks, dargestellt mit der XEvents GUI (Profiler beherrschte das, wie so vieles, nicht)

The second problem arises once you are dealing with a multi-victim-deadlock.

Potentially there can be a vast number of locked and killed victim-processes, so that one can hardly prepare an ever sufficient schema.

Here an example of a multi-victim-deadlock, displayed with the XEvents GUI (Profiler could not handle that as many other things)

Letzten Monat habe ich meinen SQL Deadlock-Collector & Parser bei codeplex frei downloadbar veröffentlicht.
- hier der direkte Link: SQL Deadlock Collector & Parser

- Hier zum Artikel mit ausführlicher Beschreibung:

freier Deadlock-Sammler & -Parser basierend auf Erweiterten Ereignissen

Dort habe ich mich so entschieden, immer die beiden ersten Prozesse gegenüberzustellen, was bei den meisten Deadlocks schon sehr weiterhilft.

Das sieht dann so aus:

Last month I published my SQL Deadlock-Collector & Parser freely downloadable at codeplex.
- here the direct link: : SQL Deadlock Collector & Parser

- here the article with detailed explanaition:

free Deadlock-Collector & -Parser based on Extended Events

There I decided always contrast the first two processes , which helps for most deadlocks a lot already.

This is how it looks like:

Wie kann man nun die ja ebenfalls von er system_health-session geschriebenen Event-Dateien einlesen?

Dafür gibt es die Systemfunktion sys.fn_xe_file_target_read_file.
Diese benötigt den Pfad zur xel-Datei und den Namen der ersten Datei, welche eingelesen werden soll.
Die Dateien liegen übrigens im Log-Verzeichnis der SQL Server Instanz.
Wenn man den Dateinamen nicht angibt, werden alle Dateien im Verzeichnis eingelesen.
Was die Funktion jedoch wirklich praktisch macht, ist, dass man bereits den Pfad mit einer Wildcard für den Dateinamen versehen kann, dass man den Dateinamen praktisch kaum noch benötigt.

So kann das dann aussehen:

How is it possible to read-in the eventfiles which are written to as well by the system_health-session?

For that the system-function sys.fn_xe_file_target_read_file is a provided.
It needs the path to the xel-file and the name of the first file which is to be read-in.
The files are located in the log-directory of the SQL Server instance by the way.
If the filename is not specified all files in the directory will be read-in.
What makes the function really handy is, that one can already arm the path with a wildcard, so that practically one does not even need the filename any more.

This is how that can look like:

Wer bereits den Deadlock-Collector zum Parsen und Speichern seiner Deadlocks verwendet, kann damit ebenfalls manuell Deadlocks von X-beliebigen Systemen in die SQL_Analysis_Data-Datenbank importieren.
Dafür verwendet man einfach die Prozedur Locking.ins_DeadLock in der Datenbank SQL_Analysis_Code mit dem Parameter @XESource.

Dieser ist standardmäßig auf die Verwendung des Ring_Buffers geschaltet.
Aber man kann auch einfach den Pfad + Dateinamen der zu importierenden xel-Datei übergeben:

Who is already using the Deadlock-Collector to parse and save ones deadlocks can also use it to import deadlocks from any system at will into the SQL_Analysis_Data-database.

For that simply use the procedure Locking.ins_DeadLock in the database SQL_Analysis_Code with the parameter @XESource.

It is set to use the ring_buffer by default.
But one can simply pass in the path + filename of the xel-file to import:

Anschließend kann man die Deadlocks wie hier beschrieben über Sichten oder Ad-Hoc T-SQL bequem halbwegs in relationaler Struktur analysieren.

Afterwards one can comfortably analyse the deadlocks via views or ad-hoc T-SQL in a half-way relational structure as wie described here.

Hinweis:

Note:

It is possible that deadlocks occur which do not appear in the system_health-session targets. Neither in the ring_buffer not in the filetarget.
This happens when the deadlock-graph is too big for the buffer-memory.

In order to make sure that larger deadlock graphs are also captured, I recommend to increase the maximal work memory of the system_health session.

Und wer jetzt immer noch auf Profiler & SQL Trace setzt (seit SQL Server 2012 aus gutem Grunde deprecated), hier noch ein Tipp:
So sieht ein MultiVictim-Deadlock im Profiler aus:

And who now is still using Profiler & SQL trace (deprecated since SQL Server 2012 for good reasons), here a tip:
This is how a multi-victim-deadlock looks in Profiler:

Das sieht eher wie ein „Multi-Winner-Deadlock“ aus ;-)

Und so stellt sich dieser in der XEvents GUI dar:

Looks more like a „multi-winner-deadlock” ;-)

And this is how the XEvents GUI displays it: