Christoph Muthmann: SSMS 2016 September 2016

Die neue Version (16.4) des SQL Server Management Studios für 2016 ist verfügbar, jetzt auch 16.4.1!

Ganze Geschichte »

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

Hallo zusammen,

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

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

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

Download SQL Server Management Studio (SSMS)

/EDIT 22.09.2016

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

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

/EDIT 26.09.2016

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

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

 

 

 


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

(EN)

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

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

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

So, what makes a conference successful?

(DE)

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

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

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

Was macht also eine erfolgreiche Konferenz aus?

 Successful_Tech_Conferences_PASS_Summit_2013_Exhibition

Exhibition at PASS Summit USA 2013

“It is big”

Probably the most common answer will be:

1)

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

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

Die wahrscheinlich gängigste Antwort wird sein:

1)

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

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

 Successful_Tech_Conferences_PASS_Summit_2013_Party

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

“it has more attendees than last year.”

2)

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

2)

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

 Successful_Tech_Conferences_SQLKonferenz2014.

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

"Your sponsors are your partners."

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

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

Your sponsors are your partners.

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

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

Eure Sponsoren sind Eure Partner.

 Successful_Tech_Conferences_SQLKonferenz2016_SarpedonQualityLab

Sarpedon Quality Lab® Sponsor booth at SQL Konferenz 2016

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

3)

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

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

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

For commercial conferences the reasons may be a bit different.

That brings me to the other options:

3)

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

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

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

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

Das führt mich zu den anderen Optionen:

 Successful_Tech_Conferences_PASS_Summit2014_MCMs

The MCMs at the PASS Summit 2014

“famousness leads to more publicity

4)

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

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

A conference can be known for:

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

4)

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

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

Eine Konferenz kann bekannt sein für:

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

 Successful_Tech_Conferences_SQLBits2016_Party

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

 Successful_Tech_Conferences_SQLKonferenz2016_Channel9Video

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

volunteers are key

5)

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

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

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

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

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

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

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

5)

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

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

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

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

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

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

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

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

Successful_Tech_Conferences_SQLServerGeeksConference2016

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

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

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

 Successful_Tech_Conferences_SQLGulf3_GroupPic

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

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

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

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

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

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

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

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

 Successful_Tech_Conferences_SQLSaturday_Singapore2016

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

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

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

Successful_Tech_Conferences_SQLSaturday_Malaysia2015

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

 

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

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

 Successful_Tech_Conferences_SQLGulf3_University

Alfaisal University in Riadh, the Location of SQL Gulf 2016

 

Question to my readers:

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

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

Frage an meine Leser:

Was denkst DU?

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

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

 Successful_Tech_Conferences_MCSHA2016

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

Cu next conference

Andreas

Successful_Tech_Conferences_PASS_Summit2015_MCMs

The MCMs at the PASS Summit 2015

Philipp Lenz: PowerPivot: Calculate Subtotals with Earlier

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

Christoph Muthmann: Verbindungstyp Oracle in Reporting Services 2016

Im Forum gab es vor kurzem die Frage, wie man den Reporting Services 2016 beibringt, wieder mit Oracle Datenquellen arbeiten zu können. Hier ist die kurze Antwort.

Ganze Geschichte »

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

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

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

sql-server-performanceanalyse_2400x_rgb-220x352

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

Alternativ auch als eBook erhältlich:

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

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

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

 


Dirk Hondong: Den PlanExplorer gibt es nun komplett gratis

Hallo liebe Community,

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

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

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

Hier geht es zur Produktseite:

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

 

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

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


Christoph Muthmann: Ersatz für den SQL Server Upgrade Advisor

Lange wurde über die Veröffentlichung des SQL Server Upgrade Advisors für SQL Server 2016 gerätselt. Jetzt wissen wir, dass es ein neues Tool gibt.

Ganze Geschichte »

Christoph Muthmann: Update für SQL Server 2014 SP2 (Juli 2016)

Ganz frisch erschienen ist das erste CU für SQL Server 2014 SP2.

Ganze Geschichte »

Christoph Muthmann: Kostenloses Backup-Tool von Key Metric Software

Von Key Metric Software gibt es ein kostenloses Backup Tool für lokale und remote SQL Server mit vielen verschiedenen Optionen, wo das erstellte Backup abgelegt werden soll.

Ganze Geschichte »

Robert Panther: Ankündigung: BASTA! 2016

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

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

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

 


Bernd Jungbluth: Neue SQL Server-Seminare im November

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

08.11.2016 - Migration Access nach SQL Server

09.11.2016 - Datenbankentwicklung mit SQL Server

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

15. und 16.11.2016 - SQL Server Integration Services

17.11.2016 - SQL Server Reporting Services

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

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

Die Teilnehmerzahl ist auf 8 Personen begrenzt.

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

Uwe Ricken: HEAPS in Verbindung mit DELETE-Operationen

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

Einleitung

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

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

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

Testumgebung

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

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

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

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

DM_OS_BUFFER_DECRIPTORS_01

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

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

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

DM_OS_BUFFER_DECRIPTORS_02

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

Lesen von Datenseiten im Heap

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

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

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

Execution_Plan_01

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

HEAP Structure

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

Löschen von Datensätzen in Heap

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

Transactions-Concurrency

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

Deallokieren von leeren Datenseiten

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

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

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

Löschen von Datensätzen mit exklusiver Tabellensperre

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

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

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

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

DM_DB_INDEX_PHYSICAL_STATS_01

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

Transactions-Concurrency-X-Lock

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

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

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

LOCKS_01

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

Lock Escalation beim Löschen von großen Datensatzmengen

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

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

DM_DB_INDEX_PHYSICAL_STATS_02

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

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

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

DM_DB_INDEX_PHYSICAL_STATS_03

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

DM_TRAN_LOCKS_01

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

DM_TRAN_LOCKS_02

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

DM_TRAN_LOCKS_03

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

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

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

Zusammenfassung

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

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

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

Herzlichen Dank fürs Lesen!

Christoph Muthmann: Update für SQL Server 2016 (Juli 2016)

Ganz frisch erschienen und im SQL Server Release Services Blog veröffentlicht wurden das erste CU für SQL Server 2016 RTM.

Ganze Geschichte »

Christoph Muthmann: SQL Server 2014 Service Pack 2

Ganz frisch erschienen und im SQL Server Release Services Blog veröffentlicht wurde das SP2 für SQL Server 2014.

Ganze Geschichte »

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

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

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

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

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

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

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

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

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

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

 Index Cheat-Sheet_preview

Download

Herunterladen kann man es hier bei Technet:

Download

You can download it here at Technet:

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

 

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

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

In total, there are now 4 different basic structures:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Folgende Artikel gab es zu diesen Themen bereits von mir:

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

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

The following articles on these topics I have published before:

 

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

 

Happy Indexing

Andreas

Robert Panther: „Fun with Spellchecking“

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

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

Doch zum Schluß meine beiden Favoriten:

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


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

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

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

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

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

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

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

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

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

Gehen wir ein paar Beispiele einfach mal durch.

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

image

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

image

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

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

Und schon funktioniert unsere einfache Abfrage.

image

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

image

Probieren wir mal ein einfaches UPDATE.

image

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

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

image

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

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

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

So, kommen wir zu einem INSERT.

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

image

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

image

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

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

image

Nein, das schlägt auch gleich fehl.

Wie schaut es noch generell mit DELETES aus?

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

image

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

image

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

image

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

image

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

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

image

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

image

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

image

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

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

image

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

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

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

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

image

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

image

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

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

image

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

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

image

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

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

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

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

Uwe Ricken: Temporal Tables – Verwendung von Triggern

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

Testumgebung

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

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

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

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

Szenario

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

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

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

Blocked resources 03

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

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

TRAN_LOCKS_02

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

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

RECORDSOURCE_01

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

Lösungen?

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

Verwendung von INSTEAD OF Trigger

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

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

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

ERRORMESSAGE_01

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

Verwendung von Stored Procedures

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

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

Zusammenfassung

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

Bisher veröffentlichte Artikel zu System Versioned Temporal Tables

Herzlichen Dank fürs Lesen!

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

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

Temporal Tables mit Hilfe einer Eigenlösung

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

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

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

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

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

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

Szenario für Trigger

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

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

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

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

    CONSTRAINT pk_Customers_ID PRIMARY KEY CLUSTERED (Id)
);
GO

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

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

UPDATE-Trigger

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

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

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

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

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

    SET NOCOUNT OFF;
GO

DELETE-Trigger

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

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

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

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

    SET NOCOUNT OFF;
GO

Welche Ressourcen werden bei einem Update blockiert?

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

Coding - Transactional Process

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

In der Praxis sieht der Prozess wie folgt aus:

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

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

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

Blocked resources 01

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

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

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

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

FN_DBLOG_01

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

Trigger = Deadlock

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

DEADLOCK-Situation-01

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

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

SET TRAN ISOLATION LEVEL SERIALIZABLE;
GO

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

In einem zweiten Abfragefenster wird die folgende Transaktion gestartet:

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

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

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

TRAN_LOCKS_01

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

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

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

DEADLOCK-Situation-02

Abfragen auf Basis von Zeitstempeln

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

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

    UNION ALL

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

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

Szenario für “System Versioned Temporal Tables”

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

Welche Ressourcen werden bei einem Update blockiert?

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

Blocked resources 02

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

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

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

Locked resources 02

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

Deadlock-Szenarien

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

Abfragen auf Basis von Zeitstempeln

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

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

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

Bisher veröffentlichte Artikel zu System Versioned Temporal Tables

Vielen Dank fürs Lesen!

Christoph Muthmann: SSMS 2016 Juli 2016

Die neue Version des SQL Server Management Studios für 2016 ist verfügbar.

Ganze Geschichte »

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

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

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

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

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

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

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

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

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

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

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

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

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

Uwe Ricken: Temporal Tables – Umbenennen von Metadaten

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

Hinweis

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

Umbenennung von Metadaten

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

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

Wie funktioniert sp_rename?

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

Umbenennen von Tabellennamen

Interne Verwaltung von Tabellenobjekten

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

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

USE [temporal];
   GO

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

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

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

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

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

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

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

Dependency between System Versioned Table and History Table

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

Codeexcerpt - sp_rename

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

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

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

Dependency between System Versioned Table and History Table - 02

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

Umbenennung von Spaltennamen

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

Umbenennen von Attributen bei aktivierter “System Versioned Temporal Table”

Umbenennen von Attribut aus “System Versioned Temporal Table”

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

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

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

Umbenennen von Attribut aus “History Table”

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

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

sp_rename_error_output

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

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

Umbenennen von Attributen bei deaktivierter “System Versioned Temporal Table”

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

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

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

reactivating_versioning_error_output

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

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

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

Zusammenfassung

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

Bisher erschienen Artikel zu System Versioned Temporal Tables

Herzlichen Dank fürs Lesen!

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

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

Hinweis

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

NULL oder NOT NULL

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

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

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

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

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

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

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

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

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

Testszenarien

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

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

NULL wird zu NOT NULL in leerer Tabelle

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

BEGIN TRANSACTION;
GO

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

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

COMMIT TRANSACTION;
GO

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

Locked resources 01

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

Vorher Nachher
TableScreenShot 01 TableScreenShot 02

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

NULL wird zu NOT NULL in einer gefüllten Tabelle

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

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

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

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

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

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

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

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

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

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

NOT NULL wird zu NULL in einer gefüllten Tabelle

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

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

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

Ausblick

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

Herzlichen Dank fürs Lesen!

Sascha Lorenz: SQL Server 2016 - in-Memory OLTP

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

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

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

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

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

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

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

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

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

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

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

Sascha Lorenz: SQL Server Missing Indexes Feature – Vorsicht!

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

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

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

Dieser Vorschlag wird auch prominent im Management Studio dargestellt.

image

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

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

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

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

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

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

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

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

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

Hallo liebe PASS Gemeinde,

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

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

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

 

Gruß

“SQLPaparazzo”


Sascha Lorenz: SQL Server 2016 – Business Intelligence Edition?

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

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

For SQL Server Business Intelligence Edition Licenses with Software Assurance

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

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

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

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

Denke mal, dass das fair klingt, oder?

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

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

SQL Server 2016 ist verfügbar. *jubel*

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

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

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

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

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

image

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

image

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

Abschnitt “Cross-Box Scale Limits”

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

Nun wird es aber gleich richtig spannend:

image

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

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

Abschnitt “RDBMS High Availability”

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

Abschnitt “RDBMS Scalability and Performance”

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

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

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

Abschnitt “RDBMS Security”

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

Abschnitt “Replication”

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

Abschnitt “Management Tools”

Da ist mir Nichts aufgefallen.

Abschnitt “Manageability”

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

Abschnitt “Development Tools”

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

Abschnitt “Programmability”

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

image

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

JSON, yeah…

Query Store, wow…

Temporal, Hammer… (und dann noch Strech…)

Microsoft, da habt ihr ja mal alles richtig gemacht!

Abschnitt “Integration Services”

image

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

Abschnitt “Integration Services – Advanced Adapters”

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

Abschnitt “Integration Services – Advanced Transformations”

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

Abschnitt “Master Data Services”

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

Abschnitt “Data Warehouse”

Hat sich Nichts geändert.

Abschnitt “Analysis Services”

Nichts Neues.

Abschnitt “BI Semantic Model (Multidimensional)”

Auch hier nichts Neues.

Abschnitt “BI Semantic Model (Tabular)”

Jetzt aber, Tabular im Standard. Aber…

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

Abschnitt “Power Pivot für SharePoint”

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

Abschnitt “Data Mining”

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

Abschnitt “Reporting Services”

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

Abschnitt “Bussines Intelligence Clients”

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

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

Abschnitt “Spatial und Location Services”

Alles bleibt wie es war.

Abschnitt “Additional Database Services”

Nichts Neues.

Abschnitt “Other Components”

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

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

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

Robert Panther: SQL Server 2016 RTM jetzt verfügbar

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

Verfügbare Editionen

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

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

Verwaltungstools

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

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

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

Weitere Informationen

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

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

 


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

 

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

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

The Built-Number is 12.0.1601.5.

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

Download-Link:

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

Download-Link:

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

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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Andreas Wolter

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

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


مرحبا


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

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

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

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

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

Dieses Jahr bringt mich an eine weitere Gegend dieser Welt:

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

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

 

 

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

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

 

SQL Server 2016 – the evolution of In-Memory technologies

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

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

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

 

SQL Server 2016 – the Security Release

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

 

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

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

 

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

Andreas

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

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

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

Report-Typen

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

 Report types

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

 

 Reporting_Services_2016_Mobile_Report_Publisher

 

Reporting Services Web Portal

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

 Reporting Services Web Portal

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

 Reporting_Services_2016_Web_Portal

 

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

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

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

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

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

 Reporting_Services_2016_Custom_Branding

 

Das Ergebnis im Vergleich zum Original-Design oben:

The result in comparison to the original design above:

 Reporting_Services_2016_Custom_Brand_SarpedonQualityLab

 

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

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

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

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

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

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

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

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

 

 Reporting_Services_2016_Print_to_pdf

 

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

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

 

Neuerungen für seitenbasierte Berichte

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

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

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

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

Nothing much has happened in the rdl.

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

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

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

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

 Reporting_Services_2016_Parameter_Grid

 

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

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

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

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

 Reporting_Services_2016_TreeMap_Chart

 

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

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

 

 Reporting_Services_2016_Sunburst_Chart_psych

 

Kleiner Spaß…

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

Just kidding…

Its application is suitable for hierarchies, and, in particular, also for „ragged“ hierarchies. Below, a standard example of a ragged hierarchy is illustrated with a sunburst-diagram:

 

 Reporting_Services_2016_Sunburst_Chart_ragged

 

Das war’s zu den Neuerungen auch fast schon.

Eines bleibt noch zu erwähnen: Report-Elemente wie Diagramme, Tachos, Karten oder Bilder lassen sich nun auch in Power BI Dashboards integrieren.

Well, that is about all there is on innovations.

One more thing: Report elements such as diagrams, speedometers, maps or images can now also be integrated in Power BI Dashboards.

 

Zum Abschluss noch einige Links zum Weiterlesen:

In closing, here are a couple of links for further reference:

 

 

Happy Reporting – finally :-)

Andreas

Christoph Müller-Spengler: Create failed for Availability Group Listener

Problem

Today I tried to create an AlwaysOn Availability Group. Everything went fine until I configured the Availability Group Listener.

It failed with the error message:

Create failed for Availability Group Listener <ListenerName>.
Microsoft SQL Server, Error 19471.

AvailabilityGroups_CreateListenerError

The Windows Application Event Log is a little bit more helpful on that:

Cluster network name resource <ClusterName> failed to create its
associated computer object in domain. [...]
verify that the Cluster Identity <ClusterName> has 'Full Control' permission
to that computer object using the Active Directory Users and Computers tool.

AvailabilityGroups_CreateListener_EventLog

There we go.

Solution

If you have the rights in your domain to do so, just help yourself out of this, otherwise you have to contact your Domain Administrator to help you.

On your Domain Controller:

  • Open “Active Directory Users and Computers”
  • In menu “View” check “Advanced Features” to be able to find the OU where your Cluster object is located in.
  • On the root of your domain right click and choose “Find…”
  • In drop down meny “Find” select “Computers”
  • In the text box for “Computer name” type your Cluster name and click button [ Find Now ]
  • View the “Properties” of your Cluster object that was found.
  • On tab “Object” you will find the location (OU) where your Cluster object is located in.
  • Close all popups.
  • Navigate to the location you just figured out, perform a right click and choose “Properties”.
  • In tab “Security” click the button [ Add… ]
  • Click the button [ Object Types… ] , mark the checkbox next to “Computers” and leave the popup with a click on [ OK ].
  • In the textbox enter the name of your Cluster, check the name and leave the popup with a click on button [ OK ].
  • In the field for the permissions mark the check box for “Full control” and click Apply.
  • Leave the Dialog open.

 

Go to SQL Server Management Studio and repeat the attempt to create the Availability Group Listener.

Go back to the Domain Controller (or ask your Domain Admin to do so) and remove the “Full control” permission for the Cluster.

 

Happy listening to your Availability Groups:-)

Christoph


Robert Panther: Neue Features für künftige Versionen von SQL Server

SQL Server 2016 steht vor der Tür und wird voraussichtlich noch in diesem Quartal veröffentlicht. Die Release Candidates sind schon verfügbar und auch die Features sind mittlerweile weitgehend bekannt. Allerdings wird bereits an der darauf folgenden Version gearbeitet, zu der nun die ersten Infos zu möglichen Features durchgesickert sind:

Gefilterte gruppierte Indizes

Die Daten der Tabelle werden in zwei verschiedenen Arten vorgehalten. Die Zeilen, die der WHERE-Klausel entsprechen werden in Form eines gruppierten Indexes abgelegt, während die restlichen Zeilen als Heap gespeichert werden. Somit können bei passenden Abfragen die Vorteile eines gruppierten Indexes mit denen eines gefilterten Indexes kombiniert genutzt werden. Im Notfall ist aber auch noch die Gesamtheit der Daten abfragbar, ohne die indizierten Daten doppelt speichern zu müssen.

Beispiel:

CREATE CLUSTERED INDEX CX_PersonPhone_BusinessEntityID_CellPhoneNumber
ON Person.PersonPhone(PhoneNumber)
WHERE PhoneNumberTypeID=1

Der neue DISLIKE-Operator

Mit dem neuen DISLIKE Vergleichsoperator steht bald eine kompaktere Form des NOT LIKE zur Verfügung.
Während die alte Variante aber eine Kombination aus zwei Operatoren ist (LIKE-Operator mit anschließender Negierung durch NOT), wird der neue DISLIKE-Operator in einem Schritt ausgeführt und arbeitet daher auch deutlich performanter.

Beispiel:

SELECT * FROM Person.Person
WHERE LastName DISLIKE ‚Brown%‘

Da es sich bei diesem neuen Operator um eine T-SQL Erweiterung handelt, dürfte dieses Feature (auch wenn es ein Performance-Feature ist) nicht nur der Enterprise Edition vorbehalten sein, sondern stattdessen auch für die kleineren Editionen von SQL Server (bis hin zu Express) zur Verfügung stehen.

Neue Datentypen: CHAR(MAX), NCHAR(MAX), BINARY(MAX)

Beim Einfügen eines Datensatzes mit einem der so deklarierten Felder, wird nach dem Speichern der übrigen Spalten der maximal verfügbare Platz auf der jeweiligen 8 KB-Speicherseite für dieses Feld belegt und mit Leerzeichen (bzw. 0-Bytes bei BINARY(MAX)) aufgefüllt. Daraus ergibt sich die Einschränkung, dass nur ein Feld pro Tabelle einen der drei genannten Datentypen verwenden kann, da sonst nicht eindeutig geregelt ist, welches Feld den verfügbaren Platz belegen kann.

Das hat zur Folge, dass ein Datensatz, der einen der genannten Datentypen verwendet, genau eine Speicherseite belegt, wodurch Themen wie Füllfaktor und Index-Padding für diese Tabellen dann nicht mehr relevant sind. Durch die daraus resultierende direkte Beziehung (Anzahl Datensätze = Anzahl Speicherseiten) kann SQL Server deutlich genauere Vorhersagen treffen, wie viele Speicherseiten für die Ausführung einer Abfrage zu lesen sind, was wiederum bessere Ausführungspläne zur Folge haben kann.

Sinnvoll einzusetzen ist dieses Feature aber sicherlich nur dann, wenn die Tabelle ohnehin so breit ist, dass nicht allzu viele Zeilen auf eine Speicherseite passen würden, da ansonsten der Vorteil der insgesamt weniger zu lesenden Speicherseiten überwiegt.

Unklar ist zum jetzigen Zeitpunkt noch, wann diese Features in SQL Server Berücksichtigung finden. Nach den bisherigen Releasezyklen wäre etwa 2018 mit der nächsten Version von SQL Server zu rechnen, wobei es bei Microsoft auch im Bereich der Serverprodukte Ansätze gibt, häufigere aber dafür kleinere Releases zu veröffentlichen.

 


Christoph Müller-Spengler: redgate SQL Monitor – The RPC Server is unavailable

Lucky me, I am setting up redgate SQL Monitor to monitor SQL Server Instances. First to say – as ever – redgate is developing ingeniously simple tools. So the installation just took minutes, the services on one VM, the repository database on another.

After having this successfully finished i logged into the Web GUI and configured my first target Host where the SQL Server Instance is running that i want to monitor.

As expected everything went fine and the windows metrics appeared like “Disk avg. write time”.

Problem

But i was not able to have a look at the SQL Server metrics. So something must have gone wrong. I had a look at Configuration -> Monitoring: “Monitored servers” and found an unconnected machine.

But wait – the first few seconds there was a “Monitoring connected” message, so something must have gone wrong a couple of seconds later on. In column “Actions” i chose the link “Show log”.

redgate_SQLMonitor_Errorlog

Of course i googled “The RPC server is unavailable” along with “0x800706BA”. As I am currently dealing with a Windows Server Failover Cluster Instance I tried the  workaround mentioned in Warning Event ID 5605 is Logged in Application log when querying MSCluster namespace through WMI and edited the ClusWMI.mof adding the value FALSE to [RequiresEncryption(FALSE)]. But that did not help.

I also followed the advice from redgate support to check all methods that SQL Monitor uses to connect to the target Host and also the SQL Server Instance.

I also checked the firewall settings allowing tcp communication on port 135 and > 1024. Everything open.

When it came to WMI test using WbemTest I tried to connect to my target Host, but also received the well known error:

redgate_SQLMonitor_Wbem

So I consulted a colleague of mine to double check if my target Host was available from another VM.

Guess what: It was. So it must have had something to do with the configuration of the two VMs we were trying to connect to the target Host.

She mentioned that she had configured the DNS suffixes in the local search list.

That did the trick. With all my unsuccessful attempts to connect to my target Host I always defined it fqdn in SQL Monitor. But i guess that the WMI security settings on my target Host prevented me from getting the right response.

Solution

As the target Host is not in the same domain that my monitoring VM is in, i had to put the IP Address of the very target Host into the hosts file on the monitoring VM in location “C:\Windows\System32\drivers\etc\hosts”


123.45.67.89    SERVERNAME

Please note that I just put in the NetBIOS Name of the Server.

That did the trick.

Going back to SQL Monitor, deleting my unsuccessfully configured taret Host and configuring it once again, but this time not fqdn but only with the SERVERNAME it all worked well and finally i got everything up and running.

Happy monitoring to all of you:-)

Thank you for reading,

Christoph

 

 

 


Robert Panther: SQL Server 2016 RC1 verfügbar

Die Schlagzahl erhöht sich spürbar. Nachdem am 9. März der erste Release Candidate (RC0) von SQL Server erschienen ist, legte Microsoft lediglich 9 Tage später bereits nach und hat den Release Candidate 1 zum Download bereitgestellt:

https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

Wie beim ersten Release Candidate handelt es sich um eine 180 Tage-Testversion (bis dahin dürfte die finale Version ja auf jeden Fall verfügbar sein).

Die wesentlichen Änderungen zum RC0 scheinen im Reporting Services Bereich zu liegen, wobei man bei den Release Candidates davon ausgehen sollte, dass sich an den Features ohnehin nicht mehr viel ändert, sondern vorrangig an der Beseitigung von Fehlern gearbeitet wird.

 


Robert Panther: Teil 2 der Artikelserie zum Thema SQL Server Indizes in der Windows Developer

In der aktuellen Ausgabe (4.16) der Zeitschrift Windows Developer ist inzwischen der zweite Teil meiner vierteiligen Artikelserie zum Thema SQL Server Indizes erschienen. Darin geht es um zusätzliche Indexoptionen sowie die Wartung von Indizes (und Indexstatistiken).

Nähere Infos dazu auf der Verlags-Website:
https://entwickler.de/windows-developer-magazin/windows-developer-4-16-210713.html

 


Robert Panther: Nachlese zur SQL Konferenz 2016 in Darmstadt

Die SQL Server Konferenz 2016 in Darmstadt ist zu Ende. 3 Tage Konferenz (davon ein PreCon Workshop Tag), ca. 50 Speaker (darunter auch viele MVPs aus dem In- und Ausland) und etwa 500 Teilnehmer machten das darmstadtium für eine kurze Zeit zum Zentrum der deutschen SQL Server Szene.

Die (nachträglich noch etwas ergänzten) Slides zu meinem Vortrag zum Thema Datenqualität können hier heruntergeladen werden: Datenqualität_Panther

SQLKonferenz2016_Panther


Robert Panther: Virtuelles SQL Server Launch Event

In Kürze (10.03.2016, 10:00 Eastern Standard Time = 16:00 deutscher Zeit) beginnt das Data Driven SQL Event in New York, bei dem Satya Nadella und Scott Guthrie den neuen SQL Server 2016 launchen. Das Event kann über folgenden Link per Live-Stream mitverfolgt werden: msft.it/6010Bw5Tk

Dies impliziert allerdings noch nicht zwingend die Verfügbarkeit der RTM-Version von SQL Server 2016, die für das zweite Quartal (Gerüchten zufolge im Mai) erwartet wird.

Ergänzung (vom 15.03.2016):

Inzwischen ist über denselben Link auch die Aufzeichnung der Veranstaltung abrufbar (aufgeteilt in mehrere kleine Videos). Dazu ist seit dem 09.03.2016 auch der erste Release Candidate (RC0) von SQL Server 2016 verfügbar. Ein Link zum Download der 180 Tage Testversion ist ebenfalls auf der genannten Seite zu finden.

 


Andreas Wolter: Sessions submitted for major conferences 2016. Topics: Security – Performance – In-Memory

Vorträge für die großen Konferenzen 2016 eingereicht. Themen: Sicherheit - Performance - In-Memory

(DE)
Nach dem tollen Verlauf der deutschen SQLKonferenz im Februar, wo ich die Ehre hatte, zusammen mit Joachim Hammer, dem Program Manager der Security-Teams für die relationalen SQL Engines bei Microsoft in Redmond die neuen Sicherheitsfeatures des SQL Server 2016 vorzustellen (mehr Infos), habe ich nun endlich Zeit gefunden, die nächsten großen Konferenzen dieses Jahres anzugehen.

(EN)
After the great success of the German SQLKonferenz in February, where I had the honor of presenting the new security features of SQL Server 2016 together with Joachim Hammer, the Program Manager of the security teams of the relational SQL Engines at Microsoft in Redmond (more info), I finally found time to go about the next big conferences this year.

Für den PASS Summit 2016, der wieder in Seattle/USA stattfindet, und auch für den SQLServerGeeks Annual Summit 2016, der in Bangalore/Indien stattfindet habe ich insgesamt 6 Sessions aus den Themengebieten „Sicherheit“, „Performance Analyse“ und „In-Memory“ ausgearbeitet und eingereicht. Dazu kommen 2 ganztägige PreCons zum Thema „Sicherheit“ und „In-Memory“.
Wen es interessiert, zu sehen, was ich diesmal „in Petto“ habe, kann die Abstracts hier einsehen.

For the PASS Summit 2016 which is again taking place in Seattle/USA as well as for the SQLServerGeeks Annual Summit 2016 which is taking place in Bangalore/India, I worked out and submitted 6 sessions altogether from the subject areas “Security,” “Performance Analysis” and “In-Memory.” Added to that 2 full-day PreCons with the topics “Security” and “In-Memory.”
For whoever is interested to see what I have “up my sleeve” this time, can review the abstracts here.

 

Pre-Conferences:

SQL Server Security black belt – attack, protect and keep secure

Security Hardening is a subject which, sooner or later, every DBA will face. Microsoft SQL Server, according to the NIST vulnerability database the most secure RDBMS for years, contains many features that help keep the data secure on different layers. At the same time, ever-new applications which use databases on your servers, support-personnel, deployment-processes, auditors, and other processes and real people are constantly demanding access to your Server.

At this full-day pre-conference you will see how external and internal attackers can gain access to sensitive data. You will then learn how to secure the different attack surfaces of a typical SQL Server, and protect not only Data at Rest but also Data in Use and Data in Transit and learn best practices to prevent common vulnerabilities.

In the second part you will get to know fundamental security principles such as

  • Least Privilege;
  • Segregation of Duties;
  • Reconstruction of Events;
  • Delegation of Authority;

and you will learn how to use built-in functionalities of SQL Server (some limited to v2016) to build your own security frameworks to secure Deployment and Monitoring, separate Job-permissions; how to implement time-based permissions and which techniques can help reconstruct security-relevant events.

If you are in charge of creating or implementing security concepts or need a full picture of attack surface protection and concepts, this session is exactly right for you.

 

In-Memory in SQL Server 2016 – from 0 to Operational Analytics Hero

The Columnstore Index technology came with SQL Server 2012 in the form of Nonclustered Columnstore, and SQL Server 2014 brought us updatable Clustered Columnstore Indexes and a completely new In-Memory Engine for memory optimized table & indexes.

SQL Server 2016 is adding the updatable Nonclustered Columnstore Indexes that can both operate on row store as well as on memory-optimized tables, called In-Memory Operational Analytics. With the In-Memory engine being extensively improved in terms of both scalability and T-SQL language support, In-Memory will become a viable option in many projects.

On this training day, attendees will be given a complete picture on the current state of technology and how and where to use either In-Memory OLTP or ColumnStore or both for efficient queries and data store.

 

General sessions:

Extended Events – The Top Features for efficient Traces

Extended Events, which entered the product in SQL Server 2008, are replacing the old SQL Trace & Profiler - and there are many good reasons for that. In this session you will see a selection of the most fascinating possibilities using this Tracing Framework. If you want to find out how to trace in a flexible and lightweight way, how to do advanced analysis directly inside the GUI, how to audit Database and Table-access without Auditing, how to analyze deadlocks without old-fashioned TraceFlags based on the built-in system_health session, this session is just for you. You will also learn how to use the GUI in an effective way for top-down-analysis and what is possible with some XQuery scripting.

 

Performance Analyzing SQL Server workloads with DMVs and XEvents

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

 

SQL Server 2016 – the evolution of In-Memory technologies

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

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

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

 

 

SQL Server Security black belt series: Securing Data

You have installed SQL Server and have heard about several “best practices,” maybe renamed the sa account, but now what?

In this session you will see demos of several methods how an attacker can get access to data in Use & in Transit and see which available built-in technologies provide help in mitigating such attacks. You will be given guidance on how to systematically identify possible threats and ne given best practices at hand.

Among the technologies that can be seen are Network sniffing, a Threat Modeling Tool, TDE and the new Always Encrypted technology of SQL Server 2016. This session is mainly targeting Administrators but many concepts and samples should be valuable knowledge for developers as well.

 

SQL Server Security black belt series: Securing Operations

You got SQL Server up and running and thought you could easily secure it by completely denying all access to everybody else except you and your co-admin, but you realize that there are many more individuals demanding access for daily or weekly operations. You have heard about “Segregation of Duties” and “Least Privilege” and are looking into how you can properly implement it with SQL Server.

In this session you will learn about techniques and approaches on how to implement secure processes in order to ensure both “Least Privilege” and “Segregation of Duties” and at the same time “Reconstruction of Events.” Among the techniques shown are “time based-permissions” and custom server roles for performance analysis and job-monitoring.

 

“SQL Attack…ed” – SQL Server under attack via SQL Injection

One of the most frequently attacked targets is the data that resides in a database server. SQL Server is considered “secure by default,” but this is only relevant until the first databases and configurations have been changed. This is why most of the exploited weaknesses are due to misconfiguration or weak coding practices as opposed to security bugs in SQL Server itself, of which we had only a few in the last 10 years.

In this purely demo-based session you will see samples of several real-life attacks, from mere reading up to disrupting service availability via various types of manual and automated SQL Injection, including a broadly unknown elevation of privileges attack for a non-sa account.

If you have a database-server which is accessible by processes beyond your direct control or which even can be reached by some kind of frontend applications, and you are unsure what the possible security implications to watch out for, this session is meant for you.

 

Ich werde natürlich posten, wenn meine Vorträge für 2016 feststehen. Vielleicht sieht man sich ja auf der einen oder anderen Konferenz. :-)

Of course I will post when my presentations for 2016 are fixed. Maybe you can meet me at one or another conference. :-)

 

Andreas

Philipp Lenz: SYNOPTIC DESIGNER FOR POWER BI – CUSTOM VISUALS

Mit dem Synoptic Designer für Power BI können individuelle Grafiken erstellt und integriert werden. Hierfür nutzen ich den Power BI Designer (Desktop Version)  für Windows.
[Download: https://powerbi.microsoft.com/de-de/desktop/]

Aufbau

In diesem Artikel möchte ich demonstrieren wie ein Fußballfeld in einen Bericht integriert wird, um Statistiken aus einem Fußball-Spiel visuell besser darstellen zu können.

Das Spielfeld ist schnell selbst gezeichnet oder aus dem Internet geladen und auf der Synoptic Webseite [https://synoptic.design/ ] wird dies eingefügt und die Bereiche werden markiert:1

Daten

Die korrespondierende Excel Tabelle mit den Daten ist folgendermaßen aufgebaut:

2

Die Spalte „Area“ gibt auch den Namen im Designer, dadurch erfolgt die Zuordnung der Daten auf das Spielfeld, bzw. die Grafik.

Integration

Im Designer exportiert man nun die Grafik mit den Markierungen via „Export to Power BI“ als SVG Datei.

Zuerst wird aber die Excel Tabelle in den Power BI Designer importiert:

3

Anschließend erstellen wir noch folgende Measures zum zählen des Spielstandes:

  1. Tore Team B = CALCULATE(COUNTA(Spieldaten[Aktion]); Spieldaten[Aktion]=“Tor“; Spieldaten[Team] =“B“)
  2. Tore Team A = CALCULATE(COUNTA(Spieldaten[Aktion]); Spieldaten[Aktion]=“Tor“; Spieldaten[Team] =“A“)
  3. Tore Gesamt = [Tore Team A]+[Tore Team B]

 

Der nächste Schritt ist, die sogenannten Custom Visuals in den Designer zu verankern, dafür wählt man auf der Webseite „Get the latest Version“ und lädt die Datei herunter. Anschließend wird diese integriert:

4

Nun kann dieses Panel in den Bericht integriert werden.

Design und Aufbereitung

Nachdem das Panel positioniert wurde, werden die Felder aus den Daten eingesetzt:

Area -> Legend

Aktion -> Values

5

 

 

 

 

 

 

 

 

Anschließend wird via „select map“ die heruntergeladene Karte (SVG Datei) ausgewählt.

Nun werden noch weitere Grafiken positioniert und befüllt:

6

 

 

 

 

 

Okay, anschließend noch das Dashboard auf das kostenlose Power BI Konto hochladen und im Web veröffentlichen:
Link

Philipp Lenz: User defined types in SQL Server

Zuletzt hatte ich das Problem, dass ich temporäre Daten in einer TABLE-Variable hatte und diese in einer Tabellenwert Funktion weiter verarbeiten musste. Problem dabei war, wie gebe ich meine Tabelle an die Funktion weiter?

Die erste Idee war, dass ich eine temporäre Tabelle erstelle und die Daten dort platziere und dann in der Funktion lese – tsja, dass geht leider nicht, bzw. dies lässt der SQL Server nicht zu.

Okay, und nun? Da diese Funktionalität ständig zur Verfügung (zur Verwendung in einem SSRS Bericht) stehen musste, brauchte ich eine stabile und multiuser-fähige Lösung.

Nächste Idee wäre, eine physische Tabelle zu erstellen und darin die Daten zu speichern. Für die multiuser-Fähigkeit könnte ich Prozess- oder die Benutzer ID aufnehmen… ist aber irgendwie umständlich, da einerseits Berechtigungen auf die Tabelle erteilt werden müssen, Indizierung, Speicher etc …

Die Lösung war nun, benutzerdefinierte Typen im SQL Server zu erstellen. Bisher kannte ich das mehr von CLR’s, aber der SQL Server bietet bereits seit Version 2008 die Möglichkeit, eigene Typen für Tabellenwert-Variablen zu erstellen.

Via CREATE TYPE wird der Typ mit der entsprechenden Definition erstellt, bspw.:

CREATE TYPE tmpDataType As Table (
 data VARCHAR(255));
GO

Aus diesem Type kann dann ein entsprechendes Objekt erstellt werden:

DECLARE @tmpDataTable As tmpDataType;

Dieses Objekt kann dann wie eine bisherige Tabellenwert Variable mit Daten befüllt werden. Das Verhalten ist völlig identisch mit den bisherigen Tabellewert Variablen, die Daten können darin manipuliert, ergänzt oder gelöscht werden.  Der eigentliche Vorteil ist nun, dass ich dieses Objekt an eine Funktion weitergeben kann:

CREATE FUNCTION dbo.testFunc(@tmpDataTable tmpDataType READONLY)
RETURNS TABLE
RETURN (
 -- return the data or do somethin else ...
 SELECT data FROM @tmpDataTable
);
GO
DECLARE @tmpDataTable As tmpDataType;
INSERT INTO @tmpDataTable
SELECT data FROM dbo.data;
SELECT * FROM dbo.testFunc(@tmpDataTable);
GO

 

Das war’s schon!

 

 

Vollständiges Demo-Script (Verwendung auf eigene Gefahr!)

-- create a tmp.-DB
CREATE DATABASE TYPETEST;
GO
USE TYPETEST;
GO
-- create a demo table
CREATE TABLE dbo.data (
 id INT not null primary key identity(1,1)
 , data VARCHAR(255));
GO
-- insert demo data
INSERT INTO dbo.data (data) VALUES ('VAL1'), ('VAL2'), ('VAL3');
GO
-- create a temp table
CREATE TABLE ##tmpData (data VARCHAR(255));
GO
-- fill the demo table
INSERT INTO ##tmpData
SELECT data FROM dbo.data;
GO
-- create a function to read the demo data from the temp table
--CREATE FUNCTION dbo.testFunc()
--RETURNS TABLE
--RETURN (
-- SELECT data FROM ##tmpData
--);
--GO
-- Does not work, in functions you do not have the option to read from temporaly tables ...
-- and now?!
-- but how can i move my temporally data to my function?
-- one option is to create a pyhsically table with the process id ... but do you need this for sure?!
-- NO!
-- create a user type table variable in the database (be carefull, this is in the database a global type!!
CREATE TYPE tmpDataType As Table (
 data VARCHAR(255));
GO
-- create a function with a parameter from the user defined typ
CREATE FUNCTION dbo.testFunc(@tmpDataTable tmpDataType READONLY)
RETURNS TABLE
RETURN (
 -- return the data or do somethin else ...
 SELECT data FROM @tmpDataTable
);
GO
-- create a object from our new type
DECLARE @tmpDataTable As tmpDataType;
-- fill our object with demo data
INSERT INTO @tmpDataTable
SELECT data FROM dbo.data;
-- execute our new function
SELECT * FROM dbo.testFunc(@tmpDataTable);
GO
-- clean up
DROP TABLE ##tmpData;
GO
USE tempdb;
GO
DROP DATABASE TYPETEST;

Uwe Ricken: AUTO_UPDATE_STATISTICS wird nicht immer ausgeführt

Der von mir sehr geschätzte Kollege und Kenner der SQL Server Engine Torsten Strauss kam mit einer sehr interessanten Beobachtung auf mich zu. Dabei ging es um die Frage, wann Statistiken aktualisiert werden, wenn für die Datenbank die entsprechende Option aktiviert ist. Dieser Artikel zeigt, dass es bestimmte Situationen gibt, in denen eine automatische Aktualisierung der Statistiken nicht durchgeführt wird.

Statistiken

Der Abfrageoptimierer verwendet Statistiken zum Erstellen von Abfrageplänen, die die Abfrageleistung verbessern. In den meisten Fällen generiert der Abfrageoptimierer automatisch die erforderlichen Statistiken; in anderen Fällen müssen weitere Statistiken erstellen werden, um optimale Ergebnisse zu erzielen. Statistiken können veraltet sein, wenn die Datenverteilung in der Tabelle durch Datenänderungsvorgänge geändert wird.

Wenn die Option „AUTO_UPDATE_STATISTICS“ aktiviert ist, prüft der Abfrageoptimierer, wann Statistiken veraltet sein könnten, und aktualisiert diese Statistiken, sobald sie von einer Abfrage verwendet werden. Der Abfrageoptimierer stellt fest, wann Statistiken veraltet sein könnten, indem er die Anzahl der Datenänderungen seit des letzten Statistikupdates ermittelt und sie mit einem Schwellenwert vergleicht. Der Schwellenwert basiert auf der Anzahl von Zeilen in der Tabelle oder indizierten Sicht. Pauschal gilt eine Statistik als veraltet, wenn mehr als 20% + 500 Datenänderungen durchgeführt wurden. Weitere Informationen zu den Schwellenwerten finden sich hier: https://support.microsoft.com/de-de/kb/195565.

Hinweis

Im nachfolgenden Artikel werden Traceflags verwendet, die nicht von Microsoft dokumentiert sind. Es wird darauf hingewiesen, dass eigene Beispiele nicht in einer Produktionsumgebung ausgeführt werden. Folgende Traceflags werden in den Codes verwendet:

  • 3604: Aktiviert die Ausgabe von Meldungen in den Client statt ins Fehlerprotokoll
  • 9204: Zeigt die für den Abfrageoptimierer „interessanten“ Statistiken, die geladen werden
  • 9292: Zeigt die Statistiken an, die der Abfrageoptimierer in der Kompilephase für „interessant“ hält
  • 8666: Speichert Informationen über verwendete Statistiken im Ausführungsplan

Testumgebung

Das die obige Aussage bezüglich der Aktualisierung von Statistiken nicht pauschal angewendet werden kann, zeigt das nachfolgende Beispiel. Dazu wird eine Tabelle [dbo].[Customer] angelegt und mit ~10.500 Datensätzen gefüllt. Die Tabelle [dbo].[Customer] besitzt zwei Indexe; zum einen wird ein eindeutiger Clustered Index auf dem Attribut [Id] verwendet und zum anderen wird das Attribut [ZIP] mit einem nonclustered Index versehen.

-- Create the demo table 
IF OBJECT_ID(N'dbo.Customer', N'U') IS NOT NULL
   DROP TABLE dbo.Customer;
   GO

CREATE TABLE dbo.Customer  
(
   Id     INT          NOT NULL IDENTITY (1, 1),  
   Name   VARCHAR(100) NOT NULL,  
   Street VARCHAR(100) NOT NULL,  
   ZIP    CHAR(5)      NOT NULL,  
   City   VARCHAR(100) NOT NULL  
);
GO  

-- and fill it with ~10,000 records 
INSERT INTO dbo.Customer WITH (TABLOCK)
(Name, Street, ZIP, CIty)
SELECT 'Customer ' + CAST(message_id AS VARCHAR(10)),  
       'Street ' + CAST(severity AS VARCHAR(10)),  
       severity * 1000,  
       LEFT(text, 100)  
FROM   sys.messages  
WHERE  language_id = 1033;  
GO  

-- than we create two indexes for accurate statistics 
CREATE UNIQUE INDEX ix_Customer_ID ON dbo.Customer (Id);
CREATE NONCLUSTERED INDEX ix_Customer_ZIP ON dbo.Customer (ZIP);
GO 

-- what statistics will be used by different queries
-- result of implemented statistics 
SELECT S.object_id, 
       S.name, 
       DDSP.last_updated, 
       DDSP.rows, 
       DDSP.modification_counter 
FROM   sys.stats AS S 
       CROSS APPLY sys.dm_db_stats_properties(S.object_id, S.stats_id) AS DDSP 
WHERE  S.object_id = OBJECT_ID(N'dbo.Customer', N'U'); 
GO

STATISTICS_DATA_01

Die Abbildung zeigt, dass für die Tabelle zwei Statistik-Objekte existieren. Insgesamt sind 10.557 Datensätze in der Tabelle und es wurden noch keine weiteren Modifikationen an den Daten vorgenommen. Da der zweite Index nicht eindeutig ist, gilt das Augenmerk der Verteilung der Daten in diesem Index. Dazu wird der folgende T-SQL-Befehl ausgeführt:

-- show the distribution of data in the statistics 
DBCC SHOW_STATISTICS ('dbo.Customer', 'ix_Customer_ZIP') WITH HISTOGRAM; 
GO 

DBCC_SHOW_STATISTICS_01

Die Verteilung der Schlüsselwerte ist sehr heterogen. Während für den ZIP-Code „12000“ lediglich ein Eintrag vorhanden ist, sind es für den ZIP-Code „16000“ mehr als 7.500 Datensätze. Abhängig vom zu suchenden ZIP-Code besteht zusätzlich die Gefahr von „Parameter Sniffing“; das soll aber in diesem Beitrag nicht weiter thematisiert werden.

Abfragen

Sobald die Tabelle erstellt wurde, kann mit den Abfragen begonnen werden. Es werden zwei Abfragen auf die Tabelle ausgeführt, die jeweils unterschiedliche Indexe adressieren. Bei den Abfragen gilt die besondere Beachtung dem Umstand, dass sie hoch selektiv sind; sie verwenden einen „=“-Operator für die Suche nach Datensätzen.

DBCC TRACEON (3604, 9204, 9292, 8666);  
GO 

DECLARE @stmt NVARCHAR(1000) = N'SELECT * FROM dbo.Customer WHERE Id = @Id;';
DECLARE @parm NVARCHAR(100) = N'@Id INT';
EXEC sp_executesql @stmt, @parm, 10;
GO 

DECLARE @stmt NVARCHAR(1000) = N'SELECT * FROM dbo.Customer WHERE ZIP = @ZIP;';
DECLARE @parm NVARCHAR(100) = N'@ZIP CHAR(5)';
EXEC sp_executesql @stmt, @parm, '18000';
GO 

Die erste Abfrage verwendet den eindeutigen Index [ix_Customer_Id] während die zweite Abfrage einen performanten INDES SEEK auf den Index [ix_Customer_ZIP] ausführt. Die aus den Abfragen resultierenden Ausführungspläne stellen sich wie folgt dar:

EXECUTION_PLAN_01

Eindeutiger Index

Die Abfrage auf eine bestimmte ID in der Tabelle führt IMMER zu einem INDEX SEEK auf dem Index [ix_Customer_ID]. Durch den „=“-Operator in Verbindung mit dem eindeutigen Index ist gewährleistet, dass immer nur ein Datensatz geliefert werden kann.

Nicht eindeutiger Index

Die Abfrage auf einen bestimmten ZIP-Code kann zu unterschiedlichen Ausführungsplänen führen. Welcher Ausführungsplan verwendet wird, hängt von der Distribution der Kardinalitäten ab. Wenn es sich nur um sehr wenige Datensätze handelt, wird ein INDEX SEEK verwendet; sind jedoch die mit einem INDEX SEEK einhergehenden Lookups zu hoch, wird sich der Abfrageoptimierer für einen TABLE SCAN entscheiden. Man kann also beim ZIP-Code von einem „instabilen“ und „nicht vorhersehbaren“ Ausführungsplan sprechen.

Manipulation der Daten

Basierend auf den Statistiken entscheidet sich der Abfrageoptimierer von Microsoft SQL Server für eine entsprechende Ausführungsstrategie. Werden mehr als 20% der Daten einer Statistik (+500) geändert, so wird eine Statistik invalide.

Das nachfolgende Skript fügt weitere 4.000 Datensätze zur Tabelle hinzu. Bei 10.557 bereits in der Tabelle vorhandenen Datensätzen müssen mindestens 2.612 Datensätze geändert / hinzugefügt werden, damit die Statistiken als veraltet gekennzeichnet werden (10.557 * 20% + 500). Mit den hinzugefügten 4.000 Datensätzen ist dieser Schwellwert auf jeden Fall überschritten.

-- now additional 4,000 records will be filled into the table 
-- to make the stats invalid! 
INSERT INTO dbo.Customer WITH (TABLOCK)  
(Name, Street, ZIP, City)
SELECT TOP 4000 
       'Customer ' + CAST(message_id AS VARCHAR(10)),  
       'Street ' + CAST(severity AS VARCHAR(10)),  
       severity * 1000,  
       LEFT(text, 100)  
FROM   sys.messages  
WHERE  language_id = 1033;  
GO 

STATISTICS_DATA_02

Wie in der Abbildung zu erkennen ist, wurden die 4.000 Datenmanipulationen registriert; diese Aktualisierungen verbleiben so lange in den Statistiken, bis sie erneut abgerufen werden und ggfls. aktualisiert werden.

In der Online-Dokumentation von Microsoft SQL Server heißt es: „Bevor der Abfrageoptimierer eine Abfrage kompiliert und einen zwischengespeicherten Abfrageplan ausführt, sucht er nach veralteten Statistiken. … Vor dem Ausführen eines zwischengespeicherten Abfrageplans überprüft die Database Engine, ob der Abfrageplan auf aktuelle Statistiken verweist.“.

Folgt man der Beschreibung aus der Online-Dokumentation, so müsste bei erneuter Ausführung der zuvor erstellten Abfragen eine Prüfung der Statistiken durchgeführt werden und die Statistiken – auf Grund der Änderungsquote von mehr als 20% – aktualisiert werden.

DBCC TRACEON (3604, 9204, 9292, 8666); 
GO 

DECLARE @stmt NVARCHAR(1000) = N'SELECT * FROM dbo.Customer WHERE Id = @Id;';
DECLARE @parm NVARCHAR(100) = N'@Id INT';
EXEC sp_executesql @stmt, @parm, 10;
GO 

DECLARE @stmt NVARCHAR(1000) = N'SELECT * FROM dbo.Customer WHERE ZIP = @ZIP;';
DECLARE @parm NVARCHAR(100) = N'@ZIP CHAR(5)';
EXEC sp_executesql @stmt, @parm, '18000'; 
GO 

DBCC TRACEOFF (3604, 9204, 9292, 8666); 
GO 

STATISTICS_USAGE_01

Die Abbildung zeigt, dass für die erste Abfrage auf die [ID] die Statistiken nicht erneut überprüft wurden. Für die zweite Abfrage wurden die Statistiken erneut überprüft. Im Ergebnis zeigt dieses Verhalten auch die Abfrage nach den Zuständen der Statistiken der betroffenen Tabelle.

 SELECT S.object_id, 
       S.name, 
       DDSP.last_updated, 
       DDSP.rows, 
       DDSP.modification_counter 
FROM   sys.stats AS S 
       CROSS APPLY sys.dm_db_stats_properties(S.object_id, S.stats_id) AS DDSP 
WHERE  S.object_id = OBJECT_ID(N'dbo.Customer', N'U'); 
GO

STATISTICS_DATA_03

Die Statistiken für den Index [ix_Customer_ZIP] wurden aktualisiert und die 4.000 neuen Datensätze sind in der Statistik enthalten. Für den eindeutigen Index [ix_Customer_ID] wurde diese Aktualisierung jedoch nicht vorgenommen. Der Grund für dieses Verhalten ist relativ einfach zu erklären:

Begründung für das Verhalten

Eindeutiger Index

Wen ein eindeutiger Index auf dem Schlüsselattribut abgefragt wird, muss Microsoft SQL Server keine Statistiken bemühen, da IMMER davon ausgegangen werden kann, dass ein gesuchter Wert nur einmal in im Index erscheint. Bei der ersten Ausführung der Abfrage wurde ein NEUER Ausführungsplan generiert. Insofern stimmt die Aussage aus der Online-Dokumentation. Bevor die Abfrage kompiliert und ein Plan generiert werden kann, müssen die Statistiken überprüft werden. Bei der zweiten Ausführung dieser Abfrage lag der Plan bereits vor; warum sollte Microsoft SQL Server hier die Strategie ändern? Da auf Grund der Eindeutigkeit der Indexwerte niemals mehr als ein Datensatz im Ergebnis erscheinen kann, muss der Plan nicht erneut überprüft werden – er ist „stabil“

Nichteindeutiger Index

Bei der zweiten Abfrage sieht die Stabilität des Plans etwas anders aus. Der Index ist nicht als UNIQUE erstellt worden; es können also pro Schlüsselwert mehrere Daten im Index vorhanden sein. Wenn tatsächlich die Anzahl der Datensätze zu einem Schlüsselattribut variieren, dann ist der Plan „instabil“; er ist abhängig von der Anzahl der vorhandenen Datensätze. In diesem Fall trifft die zweite Aussage aus der Online-Dokumentation zu – der Plan muss auf Validität überprüft werden. Dazu gehört das Überprüfen der veralteten Statistiken. Nun stellt Microsoft SQL Server fest, dass die Statistiken Änderungen erfahren haben, die über dem Schwellwert liegen und somit werden die Statistiken vor der Erstellung des Plans aktualisiert.

Zusammenfassung

Statistiken sind bei Performance-Problemen immer ein Punkt, der überprüft werden sollte. Statistiken werden aber – entgegen der Aussage von Microsoft – nicht grundsätzlich aktualisiert, sobald der definierte Schwellwert überschritten ist. Statistiken werden auch nicht durch einen Background-Task aktualisiert. Die Aktualisierung von Statistiken beruht darauf, wie stabil / instabil ein gespeicherter Plan ist. Wird – auf Grund der Stabilität – bei der Ermittlung der Datensätze erkannt, dass sich die Datenmenge nicht verändern kann, kann es passieren, dass Statistiken so lange nicht aktualisiert werden, bis entweder ein bestehender Plan aus dem Cache gelöscht wird oder aber eine Abfrage mit RECOMPILE dazu gezwungen wird, einen neuen Plan zu verwenden.

Herzlichen Dank fürs Lesen!

Bernd Jungbluth: 5. SQL Server und .NET-Enwickler-Konferenz

Das Programm zur diesjährigen SNEK ist komplett:

Analyse und Bewertung von Wait Stats - Warum muss der SQL Server warten?
Entzauberung von Clustered Indexen
beide von Uwe Ricken

Sicher?
von Thomas Trefz

.NET Core
Git für Softwareentwickler
beide von Rainer Stropek

Backend in der Cloud: API Apps und Logic Apps in Azure
Universal Windows 10 Apps
beide von Thomas Mutzl

In meinem Vortrag zeige ich einige ausgewählte Neuerungen von SQL Server 2016.

Die Konferenz findet wie jedes Jahr in Nürnberg statt.
Weitere Informationen zu den Vorträgen, rund um die Veranstaltung und eine Anmeldemöglichkeit stehen unter http://www.donkarl.com/snek/ bereit.

Aktuell gibt es nur noch 20 freie Plätze!

Wie jedes Jahr freue ich mich auf die Vorträge und ganz besonders auf das Wiedersehen mit den vielen Stammgästen.

Robert Panther: Ankündigung: BASTA! Spring in Darmstadt

Auch auf der diesjährigen Spring-BASTA! werde ich mit einem eigenen Vortrag vertreten sein und zwar am 03. März ab 14:00 Uhr mit dem Thema „Datenqualität konsequent umgesetzt“. Dabei werden verschiedene Ansätze aufgezeigt, wie man mit den Mitteln, die SQL Server in den verschiedenen Versionen (bis hin zu SQL Server 2016) zur Verfügung stellt, eine möglichst gute Datenqualität erreicht. Dabei wird stets die Praxistauglichkeit im Vordergrund stehen. Es geht also weniger darum, was alles theoretisch machbar ist, sondern eher, wie man mit vertretbarem Aufwand die bestmögliche Datenqualität erzielt. Auch der Aspekt, wie man aus Entwicklersicht zu einer möglichst guten Datenqualität beitragen kann, wird dabei eine wichtige Rolle spielen.

Official Speaker at BASTA! 2016 Spring Edition

Die BASTA! Spring mit dem Themenschwerpunkt „The Best of Best Practices!“ findet vom 29. Februar bis 4. März im Maritim Rhein-Main Hotel in Darmstadt statt, wobei der erste und letzte Tag Workshoptage sind, während die anderen drei Tage klassische Konferenztage mit zahlreichen kürzeren Vorträgen sind.

Weitere Infos sind auf der Veranstaltungswebsite zu finden: https://basta.net


Bernd Jungbluth: Seminar - SQL Server Integration Services

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

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

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

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

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

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

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

Die Teilnehmerzahl ist auf 8 Personen begrenzt.

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

Uwe Ricken: Zusammenhang zwischen dynamischem SQL und veralteten Statistiken

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

Dynamisches SQL

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

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

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

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

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

Dynamic_Results_01

Statistiken

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

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

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

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

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

Execution_Plan_01

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

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

DBCC_STATISTICS_01

Testumfeld

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

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

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

DBCC_STATISTICS_02

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

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

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

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

Dynamic_Results_02

Problem

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

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

Die Prozedur erzeugt den folgenden Ausführungsplan:

Dynamic_Results_03

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

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

Dynamic_Results_04

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

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

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

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

RESULTSET_01

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

Lösung

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

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

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

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

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

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

Dynamic_Results_05

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

Dynamic_Results_06

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

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

Dynamic_Results_07

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

Dynamic_Results_08

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

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

RESULTSET_02

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

Zusammenfassung

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

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