Christoph Muthmann: SQL Server 2016 kommt am 1. Juni

So lesen sich zumindest die Hinweise im SQL Server Blog von Microsoft.

Ganze Geschichte »

Christoph Muthmann: Refugee Hackathon

Folgende Info hat mich von Andreas Jakl erreicht: „Wir werden am 21. / 22. Mai gemeinsam mit Microsoft Österreich einen großen Refugee Hackathon in Wien veranstalten!

Ganze Geschichte »

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

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

Ganze Geschichte »

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

Christoph Muthmann: Wichtige Änderung bei der Anlage von VLFs

Durch Benjamin Hoch wurde ich auf eine wichtige Änderung für die Erzeugung von VLFs (Virtual Log Files) ab SQL Server 2014 aufmerksam gemacht, die mir bislang entgangen war.

Ganze Geschichte »

Christoph Muthmann: Cloud and Datacenter Conference Germany

Am 12. Mai findet in Düsseldorf die "Cloud and Datacenter Conference Germany" statt.

Ganze Geschichte »

Christoph Muthmann: SQL Client Tools update for SQL Server 2016

Heute ein kurzer Hinweis auf einen aktuellen Artikel aus dem SQL Server Blog zu dem ich auch einen bescheidenen Beitrag leisten durfte.

Ganze Geschichte »

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

 

 

 


Christoph Muthmann: Update für SQL Server 2012 (März 2016)

Ganz frisch erschienen und im SQL Server Release Services Blog veröffentlicht wurden ein CU für SQL Server 2012 SP2 und eins für SQL Server 2012 SP3.

Ganze Geschichte »

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


Christoph Muthmann: New TPC-H Benchmarks Comparing SQL Server 2016 to SQL Server 2014

This shows a 38.4% score increase on identical hardware, which is quite impressive.

Ganze Geschichte »

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;

Andreas Wolter: Schema-design for SQL Server: recommendations for Schema-design with security in mind

Andreas Wolter: SQLKonferenz in Darmstadt: Vorstellung der Security Features von SQL Server 2016 mit dem Leiter des Security-Teams aus Redmond

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


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

In der aktuellen Ausgabe (3.16) der Zeitschrift Windows Developer startet meine vierteilige Artikelserie zum Thema SQL Server Indizes.
Im ersten Teil geht es vor allem um Index-Grundlagen der klassischen gruppierten und nicht-gruppierten Indizes. In den folgenden Teilen werden die Themen Indexoptionen, Wartung von Indizes, spezielle Indexformen bis hin zu Columnstore Indizes behandelt, so dass alle wesentlichen Indexthemen abgedeckt sind.

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


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!

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

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

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

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

Warum eine Datenbank oder ein Objekt eine Sortierung braucht.

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

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

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

Rowsets_01

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

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

Ändern einer Sortierung

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

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

Beispielszenario

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

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

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

Rowsets_02

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

Änderung der Sortierung in HEAP

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

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

-- what has happend in the transaction log?
SELECT  Operation,
        Context,
        AllocUnitName,
        [Page ID],
        [Slot ID]
FROM    sys.fn_dblog(NULL, NULL)
WHERE   [TRANSACTION ID] IN
        (
            SELECT [Transaction ID]
            FROM sys.fn_dblog(NULL, NULL)
            WHERE [Transaction Name] = N'ChangeCollation'
) AND
Context != N'LCX_NULL'
ORDER BY
        [Current LSN],
    [Transaction ID];
 
COMMIT TRANSACTION ChangeCollation;
GO

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

Rowsets_03

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

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

Rowsets_04

Änderung der Sortierung in einem Clustered Index

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

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

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

ALTER_COLUMN_FAILURE_5074

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

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

Zusammenfassung

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

Herzlichen Dank fürs Lesen!

Uwe Ricken: Don’t trust sys.dm_db_database_page_allocations

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

Verwendung von sys.dm_db_database_page_allocations

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

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

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

Demonstration

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

SET STATISTICS IO ON

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

STATISTICS_IO_01

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

sys.system_internals_allocation_units

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

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

USED_DATA_PAGES_01

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

sys.dm_db_database_page_allocations

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

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

USED_DATA_PAGES_02

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

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

USED_DATA_PAGES_03

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

USED_DATA_PAGES_DIFFERENCE

Zusammenfassung

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

Herzlichen Dank fürs Lesen!

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

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

Was ist ein HEAP?

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

SQLGuru_01

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

Testumgebung

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

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

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

INSERT INTO dbo.demo_table DEFAULT VALUES;
GO 10000

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

SQLGuru_02

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

Löschen von Datensätzen

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

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

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

SELECT in HEAP

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

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

(c) by db Berater GmbH

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

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

Lösung

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

Neuaufbau der Tabelle

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

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

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

SQLGuru_04

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

Löschen von Datensätzen mit TABLOCK-Hinweis

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

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

SQLGuru_05

Zusammenfassung

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

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

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

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

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

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

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

PASS2016_300x100

 


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

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

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

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

Ergänzung vom 23.12.2015:

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

 


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

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

Deutsche SQL Konferenz

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

SQLRally Nordic

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

SNEK (SQL Server + .NET-Entwicklerkonferenz)

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

SQL Saturday(s)

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

Cambridge

IMG_3496

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

Lissabon

IMG_3933

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

Portland (OR)

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

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

SQL in the City – Redgate

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

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

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

PASS Summit 2015

IMG_4487

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

PASS Camp 2015

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

IT Tage Frankfurt 2015

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

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

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

Shares

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

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

PASS SUMMIT 2015

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

I'm Speaking_e-signature

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

Evaluation

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

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

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

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

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

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

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

Herzlichen Dank fürs Lesen!

Philipp Lenz: SSRS: PREVIOUS Funktion in einer Matrix

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

Dirk Hondong: Es muss nicht immer DROP…CREATE sein

Hallo zusammen,

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

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

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

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

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

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

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

oder dann demnächst in SQL Server 2016:

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

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

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

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

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


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

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

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

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

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

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

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


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


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

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

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

PassCamp

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

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

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

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


SQLPodcast: #012 – tSQLt

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

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

Robert Panther: BASTA! Nachlese

BASTA! 2015

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

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

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

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


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

 

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

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

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

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

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

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

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

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

 

 Microsoft-Certification-MCSA-MCSE-MCM-MCSM

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

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

 

MCM_SQL_Server_MCSM_Data_Platform

 

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

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

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

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

Hence this explains the difference between MCM and MCSM:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

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

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

 
 MCA-MCSM-MCM-MCSE-MCA_Pyramid

 

Was aber ist denn ein MCA?

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

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

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

So what then is an MCA?

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

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

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

   MCA_SQL_Server_2005_2008

 

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

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

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

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

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

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

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

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

  MCA_Microsoft Certified_Architect

 

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

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

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

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

 

MCM SQL Server 2005

MCA SQL Server 2005

MCM SQL Server 2008

MCA SQL Server 2008

MCSM Data Platform

33

23 (+3)

172

7 (+1)

7

 

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

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

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

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

 

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

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

 

MCSM_Data-Platform-Charter-Member

 

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

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


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

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

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

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

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

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

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

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


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

Why would they do this?

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

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

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

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

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

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

 

Kommen wir zu einem ganz andern Zertifikat: SSAS Maestro

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

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

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

 

Bleibt ein Thema: Der MVP Award

We have one topic left: The MVP Award

 

MVP_Microsoft_Most_Valuable_Professional 

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

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

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

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

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

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

Bei Microsoft selber liest es sich wie folgt:

Wer sind MVPs?:

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

„Wie wird man ein MVP?:

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

At Microsoft itself it reads as follows:

Who are MVPs?

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

“How to become an MVP?

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

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

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

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

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

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

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

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

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

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

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

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

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

Nominierung

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

Nomination

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

Expertise

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

Expertise

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

Vorteile, des MVP?

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

What are the advantages of the MVP?

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

 

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

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

 

Happy learning & sharing

Andreas

Torsten Schuessler: Cumulative Update 8 for SQL Server 2012 SP2

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

Cumulative update package 8 for SQL Server 2012 SP2

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

I wish you a nice day,
tosc

 

Torsten Schuessler

Uwe Ricken: Forwarded Records in non-clustered Indexen

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

Testumgebung

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

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

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

Non-Clustered Index in einem Heap

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

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

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

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

INDEX_02_CLUSTER

Non-Clustered Index und Forwarded Records

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

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

RECORDSET_06

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

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

DBCC_PAGE_145

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

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

DBCC_PAGE_145_02

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

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

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

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

INDEX_02_HEAP

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

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

Herzlichen Dank fürs Lesen!

*Achtung: Werbung

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

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

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

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

Uwe Ricken: Forwarded Records intern

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

Interner Aufbau eines HEAPS

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

PFS – Datenseite

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

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

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

IAM – Datenseite

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

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

RECORDSET_03

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

FORWARDED RECORDS?

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

Testumgebung

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

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

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

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

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

RECORDSET_01

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

RECORDSET_02

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

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

RESULT_01

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

FORWARDED RECORDS generieren

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

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

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

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

RECORDSET_04

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

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

FORWARDED RECORS erkennen

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

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

PHYSICAL_STATS_01

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

FORWARDED RECORDS lesen

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

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

RESULT_02

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

PHYSICAL_READS

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

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

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

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

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

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

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

Warum vollständig qualifizierte Objektverweise?

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

Testumgebung

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

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

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

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

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

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

    CONSTRAINT pk_foo PRIMARY KEY CLUSTERED (Id)
)
GO

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

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

Abfragen ohne qualifizierte Objektnamen

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

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

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

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

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

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

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

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

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

RECORDSET_01

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

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

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

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

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

ResultSet_03 ResultSet_04

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

Abfragen mit qualifizierten Objektnamen

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

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

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

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

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

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

Zusammenfassung

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

Verweise

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

Herzlichen Dank fürs Lesen!

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

Liebe PASS'ler und SQL Server Enthusiasten in Bayern,

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

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

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