Uwe Ricken: Größe und Verwendung aller Datenbanken ermitteln

Mit bestimmter Regelmäßigkeit werde ich beauftragt, vorhandene Microsoft SQL Server zu untersuchen, wenn zum Beispiel eine Performance-Analyse gemacht werden soll oder aber der Microsoft SQL Server einer generellen Untersuchung unterzogen werden soll. Das man dabei schon mal recht interessanteste Analysen vorfindet, habe ich bereits im Artikel “Berater / DBA / DEV – Dokumentation ist eine Hauptleistungspflicht!” behandelt. Mit diesem Artikel möchte ich eine Artikelreihe beginnen, in der ich ein paar meiner im Alltag verwendeten Skripte vorstelle und deren Interpretation beschreibe.

Wenn Datenbanken auf Performanceprobleme untersucht werden müssen, gilt der erste Blick bestimmten Konfigurationsparametern einer Datenbank. Diese Einstellungen einzeln über die GUI des Microsoft SQL Server Management Studio zu ermitteln, kann – insbesondere, wenn es sehr viele Datenbanken sind - sehr mühsam und vor allen Dingen sehr zeitraubend sein. Da die Ergebnisse in die Dokumentation einfließen müssen, ist eine tabellarische Auswertung die bessere Wahl. Aus diesem Grund helfe ich mir – und dem Kunden – mit einem Skript, dass mir in wenigen Augenblicken einen – auf die Performanceanalyse einer Datenbank ausgerichteten – Überblick verschafft.

USE master;
GO
 
-- Dirty reads zulassen, um möglichst keine Ressourcen zu sperren!
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
-- Tabellenvariable für die Speicherung der Ergebnisse
DECLARE    @Result TABLE
(
    Database_Name         sysname        NOT NULL,
    Database_Owner        sysname        NULL,
    compatibility_level   VARCHAR(10)    NOT NULL,
    collation_Name        sysname        NOT NULL,
    snapshot_isolation    VARCHAR(5)     NOT NULL    DEFAULT ('OFF'),
    read_committed_SI     TINYINT        NOT NULL    DEFAULT (0),                
    Logical_Name          sysname        NOT NULL,
    type_desc             VARCHAR(10)    NOT NULL,
    physical_name         VARCHAR(255)   NOT NULL,
    size_MB               DECIMAL(18, 2) NOT NULL    DEFAULT (0),
    growth_MB             DECIMAL(18, 2) NOT NULL    DEFAULT (0),
    used_MB               DECIMAL(18, 2) NOT NULL    DEFAULT (0),
    is_percent_growth     TINYINT        NOT NULL    DEFAULT (0),
    
    PRIMARY KEY CLUSTERED
    (
        Database_Name,
        logical_name
    ),
    
    UNIQUE (physical_name)    
);
 
INSERT INTO @Result
EXEC    sys.sp_MSforeachdb @command1 = N'USE [?];
SELECT  DB_NAME(D.database_id)                            AS [Database Name],
        SP.name                                           AS [Database_Owner],
        D.compatibility_level,
        D.collation_name,
        D.snapshot_isolation_state_desc,
        D.is_read_committed_snapshot_on,
        MF.name,
        MF.type_desc,
        MF.physical_name,
        MF.size / 128.0                                   AS [size_MB],
        CASE WHEN MF.[is_percent_growth] = 1
            THEN MF.[size] * (MF.[growth] / 100.0)
            ELSE MF.[growth]
        END    / 128.0                                    AS [growth_MB],
        FILEPROPERTY(MF.name, ''spaceused'') / 128.0      AS [used_MB],
        MF.[is_percent_growth]
FROM    sys.databases AS D INNER JOIN sys.master_files AS MF
        ON    (D.database_id = MF.database_id) LEFT JOIN sys.server_principals AS SP
        ON    (D.owner_sid = SP.sid)
WHERE    D.database_id = DB_ID();';
 
-- Ausgabe des Ergebnisses für alle Datenbanken
SELECT * FROM @Result AS R;
 
-- Umstellung der Isolationsstufe
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

Dieses Skript erstellt zunächst eine Tabellenvariable, um anschließend mit Hilfe der Systemprozedur sys.sp_MSforeachdb die benötigten Informationen zu jeder Datenbank zu ermitteln. Leider ist dieser Workaround notwendig, da die Funktion FILEPROPERTY nur im Kontext der AKTUELLEN Datenbank Werte liefern kann. Es gilt zu beachten, dass sys.sp_MSforeachdb eine nicht offiziell dokumentierte Systemprozedur von Microsoft ist. Das Ergebnis liefert eine Tabelle, in der für jede Datei einer Datenbank die folgenden Informationen gespeichert werden:


[Database_Name]


Hier bedarf es sicherlich keiner Erklärung; um welche Datenbank handelt es sich?


[Database_Owner]


Dieser Wert sollte aus Sicherheitsgründen sehr genau überprüft werden. Da der Schwerpunkt dieses Artikels auf Performance ausgelegt ist, möchte ich hier auf den lesenswerten Artikel “SQL Server Database Ownership: survey results & recommendations” von Andreas Wolter (b | t) verweisen, der – basierend auf einer Umfrage – Informationen und Empfehlungen zum Eigentümer einer Datenbank gibt.


[Compatibility_Level]


Diese Spalte zeigt, mit welchem Kompatibilitätsmodus die Datenbank betrieben wird. Sofern es sich bei dem zu untersuchenden Microsoft SQL Server um die Version von 7.0 – 2012 handelt, ist diese Option (immer unter Berücksichtigung des Aspekts der Performance) eher zu vernachlässigen. Kommt jedoch der Microsoft SQL Server 2014 ins Spiel, kann diese Option einen wichtigen Hinweis zur Verwendung des neuen “Cardinal Estimator” im Query Optimizer geben. Nur, wenn eine Datenbank im Modus “120” läuft, kann diese neue Möglichkeit genutzt werden.


[Collation_Name]


Die konfigurierte Sortierung für eine Datenbank kann erheblichen Einfluss auf die Performance von Abfragen haben, wenn in der Datenbank Abfragen ausgeführt werden, die datenbankübergreifend arbeiten. Zu diesem Thema habe ich im Artikel “Sortierungskonflikte – Auswirkungen auf Ausführungspläne” die Auswirkungen an Hand einer so tatsächlich vorgefundenen Problemanalyse beschrieben. Wird häufig mit temporären Tabellen gearbeitet, sollte die Sortierung der einzelnen Datenbank mit der Sortierung von TEMPDB verglichen werden.


[SNAPSHOT_ISOLATION] und [READ_COMMITTED_SNAPSHOT]


Snapshot Isolation bietet in Microsoft SQL Server die Möglichkeit, ein pessimistisches Isolationsmodell in ein – bedingt – optimistisches Transaktionsmodell zu  verwandeln. Die Prüfung auf SNAPSHOT_ISOLATION ist insofern für mögliche Performanceprobleme relevant, als das – einfach ausgedrückt – bei einem SELECT die Daten nicht unmittelbar aus der Tabelle gelesen werden, sondern einen Umweg über die TEMPDB machen. SNAPSHOT_ISOLATION beeinflusst also die Performance deutlich, da die TEMPDB bei dieser Isolationsstufe eine wichtige Rolle spielt. Die Thematik zu SNAPSHOT ISOLATION würde diesen Artikel vollständig sprengen. Ein sehr guter Einstieg in die Thematik findet sich hier: “Snapshotisolation in SQL Server”.


[LOGICAL_NAME]


Hinter [LOGICAL_NAME] verbirgt sich – wie es der Name bereits sagt – die logische Bezeichnung einer physikalischen Datei einer Datenbank. Dieser logische Name muss z. B. verwendet werden, um die Funktion FILEPROPERTY zu verwenden oder aber, um mit SHRINKFILE eine Datenbankdatei zu verkleinern. Dieses Attribut hat informellen Charakter in der Ausgabe.


[TYPE_DESC]


Hinter TYPE_DESC verbirgt sich der Typ der Datenbankdatei. Hier unterscheidet man zwischen ROWS (Daten) und LOG (Transaktionsprotokoll). Insbesondere sollte man im Ergebnis darauf achten, ob z. B. mehrere Dateien vom Typ [LOG] bei einer Datenbank definiert wurden. Mehrere Protokolldateien sind nutzlos, da Transaktionsprotokolle IMMER sequentiell geschrieben werden!


[PHYSICAL_NAME]


Die Informationen über die physikalischen Dateinamen sind für Fragen der Performance von großer Bedeutung. Die Informationen beantworten folgende Fragen:



  • Sind Datendateien von Protokolldateien getrennt?
  • Werden mehrere Protokolldateien verwendet?
  • Werden mehrere Datendateien verwendet?

In OLTP-Systemen mit sehr hohen Schreibvorgängen ist eine Trennung von Daten- und Protokolldateien sicherlich sinnvoll. Entgegen einem weit verbreiteten Irrglauben, dass Schreibvorgänge nicht sofort bei Speicherung von Datensätzen durchgeführt werden, wird die Protokolldatei IMMER SOFORT geschrieben. Ist ein System von vielen Schreibvorgängen betroffen, kann eine Aufteilung der Datenbankdateien und der Transaktionsprotokolldatei auf unterschiedliche Laufwerke eine deutliche Entlastung bringen! Die Verwendung mehrerer Protokolldateien ist nicht notwendig. Ein erhoffter Performancegewinn kann nicht realisiert werden, da Transaktionen immer seriell in die Protokolldatei geschrieben werden.


Die Verwendung mehrerer Datendateien kann das Schreiben von Daten beschleunigen, da Microsoft SQL Server das “Round Robin Verfahren” für das Schreiben von Daten verwendet. Hierzu hat Klaus Aschenbrenner (b | t) ein “SQL Quickie” erstellt, in dem er das Verfahren nicht nur sehr gut beschreibt sondern in einer entsprechenden Demo auch die Funktionsweise zeigt.


Ebenfalls einen Blick wert ist, ob die Systemdatenbank [TEMPDB] mehrere Datendateien verwendet. Um TEMPDB ranken sich viele Mythen und Empfehlungen und eine allgemeine Empfehlung ist die Verwendung von mehreren Dateien in TEMPDB, um bei der Erstellung von temporären Tabellen SGAM und GAM-Contention zu vermeiden. Hierzu hat Robert Davis (b | t) eine interessante Präsentation erstellt, in der er auch ein Skript liefert, dass mögliche Engpässe in TEMPDB ermittelt!


Wird ein Multi-Core System untersucht, kann relativ schnell herausgefunden werden, ob TEMPDB noch Anpassungen benötigt (Anzahl Dateien und einheitliche Größe).


[size_MB], [growth_MB], [used_MB], [is_percent_growth]


Die aus meiner Sicht wichtigsten Angaben betreffen die Größeneinstellung der Datenbanken. Die Information [size_MB] beschreibt die physikalische Größe der Datenbankdatei. Sind es mehrere Dateien, die eine Datenbankdatei betreffen, sollte vor allen Dingen darauf geachtet werden, ob sie eine identische Größe besitzen um “Hot Spots” beim “Round Robin Verfahren” zu vermeiden.


Der Spalte [growth_MB] ist aus verschiedenen Gründen erhöhte Aufmerksamkeit zu schenken; handelt es sich um eine LOG-Datei (Transaktionsprotokoll), sollte der Vergrößerungsintervall aus den folgenden Gründen nicht zu groß gewählt sein.



Bei der Wahl der geeigneten Vergrößerung für Datendateien spielen viele Faktoren eine Rolle. Es gibt keine generelle Empfehlung für die Vergrößerung, da sie sehr stark vom Workload der Applikation abhängig ist, die diese Datenbank verwendet. Wenn es sich um eine “Grußkarten-Datenbank” handelt, sind Vergrößerungsintervalle von 100 GB sicherlich Unsinn, aber genau so unsinnig ist ein Vergrößerungsintervall von 1 MB für ein Onlineportal.


Bei den Vergrößerungsintervallen sollte darauf geachtet werden, dass nicht der Standardwert von 1 MB eingerichtet ist. Das führt zu hoher Fragmentierung der Datei auf der Disk und hat somit Einfluss auf die Performance. Im Zusammenhang mit den Größeneinstellungen ist auf jeden Fall “Instant File Initialization” zu beachten. Ist IFI nicht aktiviert, bedeutet Vergrößerung immer STILLSTAND!


Merke: Der beste Vergrößerungsintervall ist der, den man nicht anwenden muss. Bei der Größenbestimmung einer Datenbank sollte möglichst gleich im Vorfeld eine adäquate Größeneinstellung verwendet werden, die eine Vergrößerung sowohl von Daten- als auch von Protokolldatei erst gar nicht erfordert!


Ein Blick auf die Auswertungen in [used_MB] ist hilfreich, um festzustellen, wann die nächste Vergrößerung ansteht. Hier kann man unter Umständen vorbeugen, indem man die Datenbankdateien bereits vorher (in der Nacht) entsprechend vergrößert.


Last but not Least die Prüfung, ob die Datenbank prozentual vergrößert wird! Leider habe ich bei den bisherigen Begutachtungen von Microsoft SQL Server weit über 75% aller angetroffenen Datenbanken mit einem Vergrößerungsintervall von 10% angetroffen. Ursächlich hierfür ist die Systemdatenbank [model], die als Vorlage für neue Datenbanken verwendet wird.


Die “Standardeinstellungen” von [model] sollten möglichst sofort bei Inbetriebnahme des Microsoft SQL Server geändert werden. Sowohl der Vergrößerungsintervall von 1 MB für Datenbankdateien als auch der Wert von 10% für die Protokolldatei sind eher kontraproduktiv für ein schnelles Datenbanksystem!


Herzlichen Dank fürs Lesen!

Christoph Muthmann: Deutsche SQL Server Konferenz 2015

Nach dem großen Erfolg im vergangenen Jahr gibt es ab sofort die Möglichkeit, sich über die Veranstaltungswebseite zur Deutschen SQL Server Konferenz 2015 anzumelden.

Full story »

Christoph Muthmann: Wie lösche ich ein zweites Transaktionslog?

Bei einer routinemäßigen Kontrolle eines Servers fiel mir auf, dass dort jemand ein zweites Transaktionslog auf einer anderen Platte angelegt hatte, da er wohl befürchtete, dass der Plattenplatz beim ersten Transaktionslog für eine größere Transaktion nicht ausreichen würde. Bei der Bereinigung habe ich mir dann selber ein Bein gestellt.

Full story »

Andreas Wolter: Upcoming conferences end of 2014: Microsoft Technical Server Summit, MVP Summit, PASS Summit, Microsoft Technical Summit

 

(DE)
Das Jahresende nähert sich in raschen Schritten. In den nächsten 3 Monaten stehen wieder mehrere Konferenzauftritte an.

(EN)
The end of year is approaching fast. For the next three months, several conferences are scheduled.

Nach der Vorstellung des SQL Server 2014 (SQL Server 2014 - Highlights in der Datenbank-Engine im Überblick) auf der BASTA im September in Mainz, geht es weiter im Oktober auf dem Microsoft Technical Server Summit in Düsseldorf mit einem Vortrag zu Neue Speicherformen in SQL Server 2014:

After the introduction of SQL Server 2014 at the BASTA in September in Mainz/Germany, I am continuing in October with a presentation on New Storage-Types in SQL Server 2014 at the Microsoft Technical Server Summit Düsseldorf/Germany:

 Microsoft_Technical_Server_Summit

 

Clustered Columnstore für DW und In-Memory OLTP - technische Hintergründe und Herausforderungen

Mit dem SQL Server 2014 kommt eine komplett neue Storage-Engine in den SQL Server: XTP mit Memory-optimierten Tabellen & Indexen. Und bereits seit der Version 2012 ist auch das ColumnStore-Format in die Engine integriert, welche in 2014 entscheidende Verbesserungen erfährt. In dieser Session wird der Microsoft Certified Master, Andreas Wolter, die technischen Hintergründe der neuen Speicherformen- & Engines beleuchten und ihre Vorteile demonstrieren. Ebenfalls aufgezeigt werden die technischen Herausforderungen dieser teilweise noch brandneuen Technologien, so dass Sie ein gutes Verständnis für die jeweils optimalen Einsatzszenarien und möglichen Migrationsaufwand mitnehmen können.

 

Anfang November folgt dann das alljährliche Highlight: Nach dem MVP Summit, wo ich hoffe die neuesten Entwicklungen hinsichtlich der nächsten Version des SQL Server zu erfahren, bin ich wie seit 2009 jedes Jahr auf dem PASS Summit in Seattle/USA.
Der Summit ist die erste Anlaufstelle für alle diejenigen, die immer auf dem Neusten Stand sein möchten. Was hier verkündet wird, wird die Inhalte der nächsten 1-2 Jahre auf anderen, kleineren Konferenzen und den Regionalgruppen der PASS weltweit bestimmen.
Dazu kommt der wertvolle direkte Kontakt zu den Entwicklern des SQL Servers vor Ort.
Auch dieses Jahr trage ich wieder selber vor, allerdings nur einen Kurzvortrag, und zwar zu dem Reporting Services Map Reports & Dynamic ZOomiNG:

This is followed by the annual highlight at the beginning of November: After the MVP Summit, at which I’m hoping to learn about the most recent developments in terms of the forthcoming SQL Server, I will be attending the PASS Summit in Seattle/USA, which has become an annual habit since 2009.
The summit is the first point of contact for all those who want to always be up-to-date.  The topics raised here will determine the content of the next one to two years at other, smaller-scale conferences as well at the regional groups of PASS worldwide.

Furthermore, the summit provides the valuable opportunity to connect directly with the developers of SQL Server on site.

This year, too, I will be presenting myself; however, just a short presentation, which will be on Reporting Services Map Reports & Dynamic ZOomiNG:

PASS_Summit_2014

Reporting Services Map Reports & Dynamic ZOomiNG:

With the advent of Power Map, Reporting Services maps seem even more static than they already were. But do maps really have to be that static?

While we will not be able to spin the globe within a report, there are at least a few ways to get some action inside a map.

In this session, we will look at an implementation of how to dynamically zoom in and out of a reporting services map without the use of subreports. Add this to your tool kit to increase the interactive experience of your geospatial reports.

Kaum zurück in Deutschland bin ich in Berlin auf dem Microsoft Technical Summit, wo auch der neue CEO von Microsoft, Satya Nadella eine Keynote halten wird.
Dort werde ich zusammen mit Patrick Heyde, Microsoft (Blog), das neueste zu der nächsten SQL Server Version präsentieren, soweit bis dahin schon für die Öffentlichkeit freigegeben ist. Zusätzlich dazu werde ich einen Deep Dive-Vortrag in In-Memory geben.

Once back in Germany, my next stop will be the Microsoft Technical Summit in Berlin where Microsoft’s new CEO, Satya Nadella, will be giving a keynote speech.

There, I will be presenting the latest on the forthcoming SQL Server version together with Patrick Heyde, Microsoft (Blog) - as far as already released for the public. Additionally I will be giving a Deep Dive-presentation in In-Memory.

 

Die genauen Inhalte der Session werden kurzfristig bekanntgegeben. Soviel sei verraten: Gezeigt werden Neuigkeiten rund um die nächste Version von SQL Server. Die Szenarien reichen von der Datenbank-Engine bis in die Cloud (Microsoft Azure) und decken On-Premise- und Cloud-Umgebungen ab. Seien sie also gespannt auf die kommenden Möglichkeiten mit On-Premise-, Hybrid- und Cloud-Only-Szenarien.

Im Dezember der würdige Abschluss mit dem alljährlichen PASS Camp, ebenfalls zum Thema In-Memory: In-Memory vNext and lessons learned
Hier spreche ich seit 2011 das 4. Mal in Folge.

December will see the worthy finale with the annual PASS Camp, likewise on the topic of In-Memory: In-Memory vNext and lessons learned. Here I am speaking the fourth time in a row since 2011

 PASS_Camp

 I hope to see some of you around somewhere,

Andreas

Bernd Jungbluth: Seminar - SQL Server Reporting Services

Auch für das Seminar SQL Server Reporting Services gibt es dieses Jahr noch einen Termin:

11. Dezember 2014 im Hotel Best Western Premier Bellevue Rheinhotel in Boppard am Rhein

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

Die Bereitstellung dieser zentralen Informationsplattform ist Inhalt dieses Seminars.

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

Nach diesem Seminar sind Sie in der Lage
- Reporting Services als zentrale Informationsstelle zu nutzen
- Dynamische Berichte zu erstellen
- Berichte und Berichtsserver zu verwalten
- Berichte im Berichtsmanager bereitzustellen
- Berichte zu abonnieren

Die Teilnehmerzahl ist auf 8 Personen begrenzt

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

Uwe Ricken: PASS Summit 2014 – muss man da unbedingt hin?

Dieses Jahr ist für mich Premiere – ich werde nach dem MVP Summit im unmittelbaren Anschluss den PASS Summit 2014 besuchen. Der PASS Summit ist das jährliche Highlight für jeden begeisterten SQL Experten – sei es die Arbeit im Rahmen von Administration und  Entwicklung von und mit Microsoft SQL Server oder aber die stark an Funktionalität gewachsene BI-Sparte des Microsoft SQL Server. Sicherlich stellt sich für den einen oder anderen “Unentschlossenen” die Frage, muss man unbedingt da hin? Ich selbst habe mir die Frage auch häufig gestellt und bekenne mich mit einem klaren JEIN.

Was ist die PASS?

PASS steht für “Professional Association for SQL Server” und ist quasi der “Dachverband” für alle regionalen User Groups, die sich mit Microsoft SQL Server beschäftigen (http://www.sqlpass.org). In Deutschland ist die PASS als Verein mit Sitz in Bad Camberg organisiert (http://sqlpass.de/Verein/Verein.aspx). Die Mitgliedschaft in der PASS ist kostenlos und auf jeden Fall empfehlenswert für alle interessierten SQL Server Enthusiasten; egal ob Anfänger, “accidential DBA”, Experten oder die, die es noch werden wollen!

Was ist der PASS Summit?

Die PASS ist Organisator vieler bekannter Veranstaltungen. In Deutschland ist neben der Arbeit der lokalen Chapter vor allen Dingen der SQL Saturday eine beliebte Veranstaltung, die überall auf der Welt von den lokalen PASS Chapters ausgetragen wird. Doch der SQL Saturday (übrigens kostenlos für die Teilnehmer) ist nur eine von vielen weltweit etablierten Veranstaltungsreihen. So werden unter der Schirmherrschaft der PASS folgende Veranstaltungen durchgeführt:

Was für Hollywood die jährliche OSCAR-Verleihung, ist für den SQL Experten der jährliche PASS Summit. Einmal im Jahr trifft das “Who ist Who” der internationalen SQL Server Spezialisten auf mehr als 5.000 Besucher der Veranstaltung die in diesem Jahr vom 04.11.2014 bis zum 07.11.2014 in Seattle (WA) stattfindet. Neben über 100 interessanten Veranstaltungen zu “Database Administration”, “Database Development” und “Business Intelligence” steht bestimmt das Networking als Motivation für den Besuch des PASS Summit im Vordergrund! Die Veranstaltungen sind “Kost für Jedermann” unabhängig vom eigenen Erfahrungsschatz. Alle Vorträge sind in Kategorien eingeteilt, die bei 100 (Anfänger) beginnen und bei 500 (absoluter Experte und Deep Dive) enden. Die Veranstaltungssprache ist Englisch.

Wann sollte man den PASS Summit besuchen?

Unabhängig davon, ob man sich selbst als “Anfänger” bezeichnet oder aber der ultimative “Crack” ist – es gibt immer interessante Themen, die man persönlich stiefmütterlich behandelt und über die man mehr Informationen wünscht. Ich bin beispielsweise kein BI-Experte finde es aber hoch interessant, von den wirklichen Experten mal ein paar Dinge über BI zu hören und zu sehen, die meinen persönlichen Horizont erweitern und meinen Fokus auf das vollständige Produkt Microsoft SQL Server richten. Mein Schwerpunkt ist – und bleibt – die Database Engine aber BI rückt immer mehr in den Fokus. Da ist es wichtig, zumindest die Core Concepts zu verstehen und auch schon mal “in action” gesehen zu haben.

Genau das ist einer der wesentlicher Bestandteile des PASS Summit – die Anzahl von Beispielen durch exzellente Referenten um die behandelte Materie nicht nur in theoretischer Form zu vermitteln sondern auch in der Praxis anschaulich zu demonstrieren.

Bei jedem Experten, der sich mit SQL Server professionell beschäftigt wie auch bei jedem Anfänger, der sich gerade in die Komplexität von Microsoft SQL Server einarbeitet, steht sicherlich die “Bibel” von Kalen Delaney im Schrank! “SQL Server Internals 2xxx”. Dieses Buch hat mich z. B. den vollständigen Weg zu den Master-Prüfungen begleitet und auch heute noch schlage ich bei komplexen Problemen immer wieder nach. Auf dem PASS Summit hat man die Möglichkeit, nicht nur die Theorie aus den Büchern zu erleben sondern man kann mit den Sprechern und Autoren auch selbst sprechen. Ich habe noch keinen der “Superstars” erlebt, der nicht gerne mal für eine Frage nach seiner Session bereit war, ein paar Details zu erklären / erläutern.

Neben Kalen Delaney sind alle bekannten Microsoft SQL Server Experten auf dem PASS Summit vertreten. Wer Paul Randal oder Kimberly Tripp bisher nur in Videos erleben konnte, für den dürfte der PASS Summit bestimmt einen Besuch wert sein. Wer tief in die Produktspezifika von Microsoft SQL Server abtauchen (insbesondere der Query Optimizer) möchte, für den ist Conor Cunningham (Software Architekt for Microsoft SQL Server bei Microsoft) ein Muss. Wer sich mehr für Optimierung von Abfragen und für den Query Optimizer interessiert, ist sicherlich bei Itzik Ben-Gan oder Benjamin Nevarez gut aufgehoben. Die Bücher von Itzik Ben-Gan sollten in keinem Bücherregal eines Datenbankentwicklers fehlen; zeigen sie doch in leicht verständlicher Weise, wie man effiziente und schnelle Abfragen erstellt.

Mal die “Grossen” sehen und hören ist sicherlich ein Aspekt, der für einen Besuch des PASS Summit spricht. Ein anderer wichtiger Posten, den man immer auf dem Radar haben sollte – Networking! Wann hat man schon mal die Möglichkeit, mit Menschen aus allen Kontinenten seine Passion ausgiebig zu diskutieren und vielleicht gemeinsam am Laptop ein Problem zu analysieren und zu lösen? Und wenn es nur das berühmte Bier an der Bar ist; man bekommt so schnell nicht wieder die Möglichkeit, sich mit Menschen aus den verschiedensten Kulturen über seine Passion zu unterhalten!

Und wann nicht?

Der PASS Summit findet jedes Jahr in Seattle (WA) statt. Die Anreise aus Deutschland ist relativ angenehm. Der Flug dauert etwa 10:30 Stunden. Neben der langen Flugdistanz gibt es drei Aspekte, die von einem Besuch des PASS Summit abschrecken könnten:

  • Kosten für Flug, Konferenz und Unterkunft
  • Verdienstausfall für Freiberufler
  • Alle Sessions werden in englischer Sprache gehalten

Die Kosten für den Flug von Frankfurt nach Seattle (Hin- und Rückflug) liegen bei ca. 900,00 €. Für die 3-tägige Konferenz selbst ist man – je nach Buchungsdatum – bei ca. 1.500,00 € angelangt. Hotelkosten liegen bei ca. 200,00 € / Nacht, Seattle ist halt eine amerikanische Großstadt! Insgesamt sind so Basiskosten von 2.500 – 3.000 € fällig, um einmal dabei zu sein und die neuesten Informationen zu Microsoft SQL Server zu erfahren. Ist man dann auch noch Freiberufler, kommen nicht unerhebliche Kosten für den Verdienstausfall hinzu. Geht man von einem Tagessatz von 600,00 € aus, sind erneut 3.000,00 € fällig, die auf Grund des Verdienstausfalls zu Buche geschrieben werden müssen. Diese Kosten müssen auf rationaler Ebene tatsächlich von jedem ins Verhältnis gesetzt werden zum persönlichen Benefit, den man aus dieser Veranstaltung ziehen möchte. Die Entscheidung wird um so schwerer, wenn man sieht, welche fantastischen Angebote es für deutlich weniger Geld bis kostenlos auch hier in Deutschland gibt.

  • So bietet der PASS e. V. sogenannte PASS Essentials an, die sich für einen Tag intensiv mit einem Thema auseinander setzen und – verbunden mit vielen Beispielen – von erfahrenen und bekannten Sprechern aus der deutschen Community gehalten werden.
  • Es gibt das SQL Boot Camp, das jedes Jahr ein fester Bestandteil im Kalender des PASS e. V. ist
  • Und nicht zu vergessen die SQL Konferenz, die im Februar 2014 ein so großer Erfolg war, dass sie auch 2015 wieder ein fester Bestandteil im Programm des PASS e. V. ist.
  • Last but not Least nicht zu vergessen der mittlerweile etablierte SQL Saturday, der sicherlich auch 2015 wieder zahlreichen Zuspruch durch die deutsche SQL Community findet

Ein – zumindest aus DACH-Sicht – nicht zu unterschätzender Punkt ist, dass alle Sessions in englischer Sprache gehalten werden. Obwohl Englisch gerade in der IT die Standardsprache ist, bin ich sicher, dass es für den potentiellen Interessenten eine Herausforderung darstellt! In einem Gespräch mit einem Kollegen auf dem SQL Saturday in Köln / Bonn hat er explizit darauf hingewiesen, dass er zwar englische Fachliteratur liest aber von Konferenzen in anderer als der deutschen Sprache Abstand nimmt; das gilt insbesondere für technisch sehr komplexe Themen.

Aus “unserer” Sicht sicherlich ein berechtigter Einwand gegen den PASS Summit aber – und das ist ja das Besondere des PASS Summit – der Summit ist kein nationales Ereignis sondern ein internationales Ereignis zu dem mehr als 5.000 Teilnehmer aus aller Welt kommen. Wer nicht nur Interesse an rein technischen Veranstaltungen bekundet sondern sich gerne mit Menschen aus anderen Ländern mit andern Kulturen unterhält; für den ist der PPASS Summit sicherlich ein Gewinn.

Meine persönlichen Erwartungen an den PASS Summit.

Microsoft SQL Server ist ein spannendes Produkt zu dem es jeden Tag Neues zu entdecken gibt. Ich schaffe es nicht, mich mit allen Konzepten des Microsoft SQL Server im Detail zu beschäftigen und bin dankbar, dass es Spezialisten gibt, die als Sprecher in 75 Minuten ihr Wissen gespickt mit vielen Beispielen an die Community weiter geben. So kann ich sieben Fliegen mit einer Klappe schlagen:

  • Ich besuche eine atemberaubende Stadt Seattle (WA).
  • Ich treffe viele neue interessante Menschen, die meine Passion für Microsoft SQL Server teilen.
  • Ich lerne viele neue Dinge (und seien es nur Akzente) von Microsoft SQL Server.
  • Die “Superstars” der Community geben sich ein Stelldichein und neben Kalen Delaney kann ich Sessions von Paul Randal, Kimberly Tripp, Itzik Ben-Gan, Adam Machanic, Benjamin Nevarez, Grant Fritchey u.v.m. besuchen.
  • Neben den Veranstaltungen bleibt ausreichend Zeit für Networking und ich bin gespannt, wen ich alles kennen lerne und wie viele bekannte Gesichter aus Europa ich wiedertreffe.
  • Ich treffe viele andere MCM und MVP Kollegen.
  • Ich bekomme vielleicht einen Einblick in die zukünftigen Produktpläne von Microsoft durch die Sprecher der Produktgruppe für Microsoft SQL Server.

Die Aussicht auf viele interessante Leute und spannende Vorträge haben mich dieses Jahr veranlasst, den PASS Summit zu besuchen. Wer weiß, vielleicht sind ja einige Leser meines Blogs ebenfalls vom 04.11.2014 bis 07.11.2014 auf dem PASS Summit in Seattle (WA). Ich freue mich über jeden, der mich mit einem “Hallo” anspricht und sich mit mir gemeinsam über Microsoft SQL Server und andere Dinge austauschen möchte. Genau so dankbar bin ich für diejenigen deutschen Kollegen, die bereits mehrfach auf dem PASS Summit waren und mich “an die Hand nehmen” und mir ein für mich neues Terrain zeigen.

Herzlichen Dank fürs Lesen!

Christoph Muthmann: SQL Server 2008 Service Pack 4 has released

Very fast after SQL Server 2008 R2 SP3, but not unexpected!

Full story »

Christoph Muthmann: SQL Server 2008 R2 Service Pack 3 Now Available

This SP has been released!

Full story »

Bernd Jungbluth: Seminar - Migration Access nach SQL Server

Dieses Jahr gibt es noch einen weiteren Termin für das Seminar Migration Access nach SQL Server:

10. Dezember 2014 im Hotel Best Western Premier Bellevue Rheinhotel in Boppard am Rhein

In diesem Seminar liegt der Fokus auf dem Wort „nach“.
Eine Migration nach SQL Server bedeutet nicht nur die Migration der Access-Tabellen nach SQL Server, sondern vielmehr auch die Migration der Access-Abfragen und VBA-Module zu Gespeicherten Prozeduren und Sichten. Das Ergebnis ist eine zukunftssichere und schnelle Client/Server-Applikation.

Nach diesem Seminar sind Sie in der Lage
- Access-Datenbanken zum SQL Server zu portieren
- Access-Abfragen zu Sichten oder Gespeicherten Prozeduren zu migrieren
- Sichten und Gespeicherte Prozeduren in T-SQL zu programmieren
- Sichten und Gespeicherte Prozeduren in Access und VBA zu verwenden

Agenda
- Migration von Access nach SQL Server
- Analyse der Access/SQL Server-Applikation mit SQL Server Profiler
- Migration der Access-Abfragen nach zu Sichten und Gespeicherten Prozeduren
- Optimierung der Access/SQL Server-Applikation

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

Die Teilnehmerzahl ist auf 8 Personen begrenzt.
Aktuell sind noch 7 Plätze frei.

Der Anmeldeschluss ist am  7. November 2014.

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

Uwe Ricken: Allokation von Datenseiten – Schritt für Schritt

Erneut eine interessante Fragestellung eines von mir sehr geschätzten Kollegen Johannes Curio (e | w). Beim experimentieren wurde eine neue Tabelle mit einem Datensatz erstellt. Bei der Überprüfung der alloziierten Datenseite kam die Frage auf, warum Microsoft SQL Server ausgerechnet Datenseite X und nicht Y für die Allokation verwendet. Ehrlich gesagt habe ich mir darüber noch nie Gedanken gemacht und fand es höchst interessant, dieser Fragestellung einmal nachzugehen.

Ein Blick in die Interna einer Datenbankdatei

Um zu verstehen, wie Microsoft SQL Server die Daten einer Datenbank verwaltet, müssen ein paar Grundlagen zu internen Strukturen bekannt sein. Damit eine Datenbank Tabellen, Indexe, Views, etc. speichern kann, verwendet Microsoft SQL Server Datenseiten (Pages) mit einer festen Größe von 8.192 Bytes. In jeder Datenbankdatei werden diese Pages durchgehend von 0 – x nummeriert. Der Datenspeicher wird in Extents organisiert. Ein Extent besteht immer aus 8 logisch hintereinander folgenden Datenseiten.

Das Speichermodell von Microsoft SQL Server hat sich in den letzten Jahren nicht deutlich weiter entwickelt und so wurde zu Zeiten, als Storage noch teuer war, die Speicherbelegung in einer Datenbank “optimiert”, indem man beim Anlegen von neuen Tabellen nicht sofort 64 KByte (ein Extent) für das anzulegende Objekt verwendete sondern es wurden mehre Objekte in einem Extent gespeichert (http://technet.microsoft.com/de-de/library/ms190969.aspx). Sobald ein Objekt mehr als 8 Datenseiten füllt, wird bei der weiteren Allokation ein vollständiges Extent (64 KByte) für die Tabelle reserviert. Diese beiden “Extent-Typen” unterscheidet man als:

  • uniform extent
  • mixed extent

Ein uniform extent alloziiert immer den Inhalt EINES Objekts während ein mixed extent Daten verschiedener Objekte speichern kann!

Mixed vs uniform extent

Die Abbildung zeigt die Belegung von Datenseiten durch zwei verschiedene Objekte ([dbo].[A] und [dbo].[B]). Microsoft SQL Server alloziiert für ein Objekt zunächst eine Datenseite in einem “Mixed” Extent. Erst, wenn 8 Datenseiten durch ein Objekt belegt sind, alloziiert Microsoft SQL Server ein “Uniform” Extent, das exklusiv für das Objekt [dbo].[A] reserviert ist. Die ersten 8 Datenseiten in jeder Datenbankdatei sind fest reserviert für Systeminformationen zur Datenbank selbst. Von diesen 8 Datenseiten sind 3 Datenseiten ein wichtiger Bestandteil für die Zuweisung von Speicherbereich für Objekte:

PFS = Page Free Space

Die PFS verwaltet 8.088 Datenseiten (64 MB) in einer Datenbankdatei. Ist die Datenbank größer als 64 MB, wird für die nächsten 64 MB erneut eine PFS Seite angelegt. Man spricht in diesem Fall von PFS-Intervallen. Die PFS verwaltet Datenseiten in einer Bytemap, die neben dem verfügbaren Speicherplatz Informationen über die Verwendung der Datenseite bereit hält. Berücksichtigt man den Umstand, dass eine Datenseite 8.192 Bytes groß ist, wird schnell klar, warum die Anzahl der zu verwaltenden Datenseiten pro PFS begrenzt ist.

GAM = Global Allocation Map

Microsoft SQL Server verwaltet den Speicher in 8 zusammenhängenden Datenseiten (Extents). Ob ein Extent belegt ist, wird in der GAM durch ein Bitmap verwaltet. Jedes Bit von insgesamt 8.000 Bytes repräsentiert jeweils 1 Extent. Insgesamt können so über eine GAM-Seite 64.000 Extents verwaltet werden (4 GB). Die erste GAM ist immer die 2. Datenseite in einer Datenbankdatei. Es ist wichtig, zu wissen, dass in der GAM JEDES Extent (unabhängig ob Uniform oder Mixed) verwaltet wird! Je nach Wert eines BIT gilt das Extent als belegt (alloziiert) oder frei:

  • 1 = frei. Das Extent ist für eine Allokierung verfügbar
  • 0 = belegt. Das Extent ist bereits in Benutzung (entweder als Uniform oder Mixed Extent)

SGAM = Shared Global Allocation Map

Die SGAM ist strukturell identisch mit der GAM. Auch die SGAM verwaltet einen Datenraum von 4 GB (64.000 Extents). Die erste SGAM ist immer die 3. Datenseite in einer Datenbankdatei. Das Bitmap der SGAM ist etwas komplizierter als das der GAM, da der Wert nur in Verbindung mit dem Bitmap der GAM auszuwerten ist.

  • 1 = das Extent ist ein “Mixed” Extent mit mindestens einer (von 8) nicht alloziierten Datenseite
  • 0 = das Extent ist entweder ein “uniform” Extent oder aber ein “Mixed” Extent, in dem alle Datenseiten allokiert sind

    Bitmuster GAM SGAM

Die Abbildung zeigt die möglichen Kombinationen von GAM und SGAM. Die Kombination beider Bitmuster geschieht in Microsoft SQL Server sehr effizient und einen Alloziierung von Datenseiten kann schnell durchgeführt werden.

Das Beispiel erzeugt eine leere Datenbank und liest anschließend den Inhalt von GAM und SGAM Datenseiten aus.

CREATE DATABASE demo_db;
GO
 
USE demo_db;
GO
 
-- Ausgabe von Informationen in SSMS
DBCC TRACEON (3604);
 
-- Ausgabe von GAM (immer Seite 2)
DBCC PAGE (demo_db, 1, 2, 3);
 
-- Ausgabe von SGAM (immer Seite 3)
DBCC PAGE (demo_db, 1, 3, 3);

Das Resultat für die GAM-Datenseite stellt sich nach der Erstellung der Datenbank wie folgt dar:



GAM: Extent Alloc Status @0x000000001C2CA0C2
 
(1:0)        - (1:208)      =     ALLOCATED
(1:216)      -              = NOT ALLOCATED
(1:224)      -              =     ALLOCATED
(1:232)      -              = NOT ALLOCATED
(1:240)      -              =     ALLOCATED
(1:248)      - (1:256)      = NOT ALLOCATED
(1:264)      - (1:296)      =     ALLOCATED

Aktuell sind in der – neu erstellten – Datenbank die Datenseiten von 0 – 215 (27 Extents) allokiert. Das bedeutet jedoch nicht, dass diese Datenseiten auch belegt sind. Sie können von Objekten belegt werden. In Verbindung mit dem Auszug aus der SGAM-Seite wird erkennbar, wo Datenseiten zu Verfügung stehen:



SGAM: Extent Alloc Status @0x000000001466A0C2
 
(1:0)        - (1:80)       = NOT ALLOCATED
(1:88)       -              =     ALLOCATED
(1:96)       - (1:104)      = NOT ALLOCATED
(1:112)      - (1:120)      =     ALLOCATED
(1:128)      -              = NOT ALLOCATED
(1:136)      - (1:176)      =     ALLOCATED
(1:184)      - (1:256)      = NOT ALLOCATED

Der Ausdruck “not allocated” ist etwas verwirrend; er bedeutet nicht, dass die Datenseiten als Mixed Extent zur Verfügung stehen! Besinnt man sich auf den Zustand eines Extent im Bitmuster, steht “not allocated” für den Wert 0! Um das Ergebnis zu interpretieren, sollen die erste Zeile (Datenseiten 0 – 208) als Beispiel verwendet werden:


Grundsätzlich sind die Datenseiten von 0 – 215 alloziiert und stehen der Datenbank als Speichermedium zur Verfügung. Innerhalb dieser alloziierten Datenseiten sind die Datenseiten 0 – 87 bereits vollständig belegt (11 Extents). Es ist jedoch auf den ersten Blick nicht erkennbar, ob sie als Uniform Extent oder Mixed Extent allokiert wurden. Der Wert 0 (NOT ALLOCATED) besagt, dass es sich entweder um ein Uniform Extent handelt oder aber um ein Mixed Extent, in dem keine freien Datenseiten mehr zur Verfügung stehen (es ist voll). Das nächste freie Extent beginnt bei Datenseite 88 und erstreckt sich bis Datenseite 95 (exakt 8 Datenseiten). Die Extents von Datenseite 96– 111 wiederum sind vollständig belegt. Ein Auszug der PFS (Page Free Space) zeigt, dass in dem erstgenannten Extent (88 – 95) lediglich eine IAM-Datenseite (Index Allocation Map) zur Verfügung steht.



-- Ausgabe von PFS (immer Seite 1)
DBCC PAGE (demo_db, 1, 1, 3);


(1:88)       -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext
(1:89)       - (1:91)       =     ALLOCATED   0_PCT_FULL                     Mixed Ext
(1:92)       -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext
(1:93)       -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext
(1:94)       -              = NOT ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext
(1:95)       -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext

Das erstgenannte Extent fällt für die Allokation von Datenseiten aus. Der zweite angegebene Datenbereich (Datenseite 112 – 127) jedoch besitzt ausreichend freie Datenseiten, um eine neue Tabelle anlegen zu können:



(1:109)      - (1:113)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext
(1:114)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext
(1:115)      - (1:116)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext
(1:117)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext
(1:118)      -              = NOT ALLOCATED   0_PCT_FULL                     Mixed Ext
(1:119)      -              = NOT ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext
(1:120)      -              = NOT ALLOCATED   0_PCT_FULL                     Mixed Ext
(1:121)      -              = NOT ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext
(1:122)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext
(1:123)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext
(1:124)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext
(1:125)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext
(1:126)      -              = NOT ALLOCATED   0_PCT_FULL                     Mixed Ext
(1:127)      -              = NOT ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext

Analyse der Objektanlage


Nach so viel Theorie die Praxis. Die Tabelle wird mit dem nachfolgenden Script angelegt und mit einem Datensatz befüllt. Es ist wichtig, dass mindestens ein Datensatz in die Tabelle eingetragen wird,da ansonsten die Alloziierung der Datenseiten nicht durchgeführt wird! Genau dieser Umstand muss bei der Analyse des belegten Speichers berücksichtigt werden. Wenn ein Objekt in Microsoft SQL Server neu angelegt wird, werden die Datenseiten für die Speicherung von Datensätzen nicht sofort physikalisch alloziiert! Erst mit der Speicherung des ersten Datensatzes werden die Datenseiten von Microsoft SQL Server alloziiert!


Basierend auf dem Ergebnis der PFS-Analyse sollten für das neu anzulegende Objekt mit einem neuen Datensatz die Datenseiten 118 und 119 verwendet werden können; eine Datenseite muss für die IAM-Seite alloziert werden, während eine weitere Datenseite für die Speicherung der Daten alloziiert wird. Um die Ergebnisse im Anschluss besser erläutern zu können, werden sowohl die Erstellung des Objekts als auch die Speicherung eines Datensatzes in einer separaten Transaktion ausgeführt. Damit lassen sich die Log-Operationen besser herausfiltern!



-- Anlegen der Tabelle [dbo].{tbl_Kunde]
BEGIN TRANSACTION CreateTable;
 
CREATE TABLE [dbo].[tbl_Kunde]
(
    [KundenNr] [char](340) NULL,
    [Vorname]  [char](100) NULL,
    [Nachname] [char](100) NULL,
    [Strasse]  [char](100) NULL,
    [PLZ]      [char](100) NULL,
    [Ort]      [char](100) NULL,
    [Telefon]  [char](100) NULL
);
 
COMMIT TRANSACTION CreateTable;
GO
 
-- Speicherung eines Datensatzes in der Tabelle [dbo].[tbl_Kunde]
BEGIN TRANSACTION InsertData;
 
INSERT INTO tbl_Kunde VALUES
('1001', 'Gabi', 'Schmidt', 'Spielweg 9', '40444', 'Düsseldorf', '0211/968596')
 
COMMIT TRANSACTION InsertData;
GO

Sobald die Tabelle angelegt wurde, kann die Position des neu hinzugefügten Datensatzes mit Hilfe einer Systemfunktion sichtbar gemacht werden:



SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS Location, * FROM dbo.tbl_Kunde;

SELECT_RESULT_01


Obwohl Datenseite 118 ausdrücklich als frei markiert war, wurde diese Datenseite “übersprungen”. Dieses Verhalten legt augenscheinlich die Vermutung nahe, dass Microsoft SQL Server willkürlich die Allokierung von Datenseiten vornimmt; dem ist aber nicht so, wenn man sich die Transaktionsprotokolle beider Aktionen etwas genauer anschaut!


Transaktion – Tabelle erstellen


Wenn ein neues Objekt in der Datenbank erstellt wird, werden dessen Metadaten wie normale Datensätze in den entsprechenden Systemtabellen gespeichert. Diese Systemtabellen sind ebenfalls in Datenseiten organisiert und unterliegen den gleichen Bedingungen für die Allokation wie Benutzerobjekte. Die Transaktion für das Anlegen der Tabelle [dbo].[tbl_Kunden] wurde unter dem Namen “CreateTable” protokolliert.



SELECT  Operation, Context, AllocUnitId, 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] = 'CreateTable'
);

fn_dblog_01


Im Attribut [Page Id] ist gekennzeichnet, welche Datenseite (Hex) bearbeitet wird. Die Datenseite 0x76 ist die Datenseite 118! Das bedeutet für die gesamte Transaktion, dass VOR dem Einfügen eines neuen Datensatzes zunächst die Metadaten des Benutzerobjekts gespeichert werden müssen. Da dieser Speichervorgang den gleichen Bedingungen unterlegt, wie die Speicherung eines Datensatzes in einer Benutzertabelle, wurde die Datenseite 118 bereits durch bei Erstellung des Benutzerobjekts allokiert.


Transaktion – Datensatz eintragen


Wenn eine Tabelle neu erstellt wird, resultiert daraus nicht automatisch die Allokation neuer Datenseiten. Die Datenseiten werden erst in dem Moment alloziiert, wenn ein Datensatz in die Tabelle eingetragen wird – sie wird also “manifestiert” mit dem ersten Datensatz! Das belegt auch das Transaktionsprotokoll des Vorgangs.


fn_dblog_02


Man kann sehr gut erkennen, dass VOR dem Eintragen des Datensatzes (Zeile 104) ein paar “Vorarbeiten” durchgeführt wurden. Zunächst wurde die IAM (Index Allocation Map) erstellt. Die IAM befindet sich auf Datenseite 120 (0x78). Nachdem die IAM alloziiert wurde, konnte die Datenseite 119 (0x77) alloziiert werden, um den Datensatz zu speichern.


Zusammenfassung


Microsoft SQL Server “scannt” die GAM in Verbindung mit der SGAM, um freie Datenseiten für neue Objekte zu allokieren. Dabei geht Microsoft SQL Server sehr effizient vor, da für die Suche nur die beiden Systemseiten verwendet werden müssen.


Wichtig für das Verständnis der Reihenfolge der Allokation von Datenseiten sind zwei Dinge.



  • Metadaten von Benutzerobjekten werden in den gleichen Typen von Datenseiten gespeichert wie gewöhnliche Datenseiten.
  • Wenn eine neue Benutzertabelle erstellt wird, wird der dazu benötigte Speicher erst in dem Moment alloziiert, wenn zum ersten Mal ein Datensatz in die Tabelle eingetragen wird. Aus Effizienzgründen werden neue Tabellen nicht vorher erstellt sondern nur die Metadaten in den Systemtabellen gespeichert.

Hinweis:


Mit einem Kollegen (Herr Maic Beher) habe ich die Fragestellung von Johannes gemeinsam untersucht. Auch er beginnt nun das Bloggen und hat einen ähnlichen Artikel zu “unserer” Arbeit dazu HIER geschrieben!


Herzlichen Dank fürs Lesen!

Tillmann Eitelberg: Technical Summit 2014

Technical Summit 2014: Wir machen Office & Power BI: Das Self-Service BI Werkzeug Vom 11. bis zum 13. November findet im Hotel MOA in Berlin der Microsoft Technical Summit 2014 statt. Das Event ist auf IT-Professionals und Developer fokussiert und Tillmann und ich werden zum Thema Office und BI eine Session haben. Dort werden wir den […]

Torsten Schuessler: Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014

Great KB article, with a lot of helpful guidance and information about configuration settings and valuable hotfixes for SQL Server 2012 and SQL Server 2014 used with high-performance workloads!

Really good stuff! A huge list of categorized advice for trace flags, cumulative updates, MAXDOP settings, auto update statistics threshold, database lock activity increases, ALTER INDEX…ONLINE operation, I/O heavy operations, tempdb, Out-of-memory Improvements for DBCC CHECKDB  and much MORE.

It's a must read, so check it out: KB 2964518 Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 used with high-performance workloads.

I wish you a nice day and happy reading :-),
tosc

 

Torsten Schuessler

Tillmann Eitelberg: Get together! Working with SSIS and HDInsight

In February this year we had our first session on SSIS & HDInsight at our “Deutsche SQL Server Konferenz 2014”. By the way, the next conference will take place on the 3.-5. Februrary 2015, have a look here. The main focus of that session was not from a technical perspective of HDInsight or a Big […]

Christoph Muthmann: Vergessene Jobs: eMails bereinigen

Wer so wie ich regelmäßig eMails über den SQL Server verschickt und da auch immer noch einiges an Daten mit dazu packt, wird bald ein gewisses Wachstum der msdb feststellen. Es gibt mehrere Möglichkeiten, dem entgegen zu wirken.

Full story »

Torsten Schuessler: #CUs for SQL Server 2012 SP1 and SP2

SQL Server 2012 Service Pack 1 Cumulative Update #12

The Cumulative update package 12 for Microsoft SQL Server 2012 Service Pack 1(Build 11.0.3470.0) is available:

Cumulative update package 12 for SQL Server 2012 SP1

SQL Server 2012 Service Pack 2 Cumulative Update #2

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

Cumulative update package 2 for SQL Server 2012 SP2

I wish you a nice day,
tosc

Torsten Schuessler

Andreas Wolter: Performance/ Management Data Warehouse Data Collector & AlwaysOn Availability Groups

Verwaltungs-Data Warehouse Datensammler & AlwaysOn Hochverfügbarkeitsgruppen

(EN)
This time, we are dealing with the „MDW“, short for Management Data Warehouse,(msdn.microsoft.com/en-us/library/bb677306.aspx), which I like to recommend as a minimal performance logging-approach.

From time to time, and most recently in the context of my PASS Essential „SQL Server Analysis tools & Techniques for Performance und general Monitoring“, the question arises as to whether the MDW operates together with the High Availability technologies Database Mirroring and AlwaysOn Availability Groups, and if so, how so.

(DE)
Diesmal geht es um das Management Data Warehouse, kurz „MDW“ (msdn.microsoft.com/de-de/library/bb677306.aspx), welches ich gerne als minimalen Performance-Protokollierungs-Ansatz empfehle.

Hin und wieder, zuletzt im Zusammenhang mit meinem PASS Essential „SQL Server Analysetools & Techniken für Performance und allg. Monitoring“ kommt die Frage auf, ob das MDW mit den Hochverfügbarkeitstechnologien Datenbankspiegelung und AlwaysOn Hochverfügbarkeitsgruppen zusammenspielt, und wenn, wie.

The short answer is: Yes, it does.
The MDW operates both with Database Mirroring as well as with AlwaysOn Availability Groups.

The following graph illustrates a possible setup using the latter:

Die kurze Antwort lautet: Ja.
Das MDW funktioniert sowohl mit Datenbankspiegelung als auch mit den AlwaysOn Hochverfügbarkeitsgruppen.

Das folgende Schaubild zeigt ein mögliches Setup unter Verwendung der letzteren Variante:

 

 MDW_Data_Collector_AlwaysOn

 

The server (0) holding the MDW database is located outside of the high availability nodes. The databases to be monitored are located in the AlwaysOn Availability Groups in the servers 1-3.
Keeping the MDW highly available is not the objective. It is simply about being able to see the performance data of all databases, no matter in which server they are active at the moment.

Der Server (0), der die MDW-Datenbank vorhält, liegt außerhalb der Hochverfügbarkeitsknoten. Die zu überwachenden Datenbanken liegen in AlwaysOn Hochverfügbarkeitsgruppen auf den Servern 1-3.
Das MDW hochverfügbar zu halten ist nicht das Ziel. Es geht nur darum, die Performance-Daten aller Datenbanken einsehen zu können, gleich auf welchem Server sie gerade aktiv sind.

Part 1: Databases in secondary role

If you set up the MDW as standard you will realize that after a failover, the data of the respective databases disappear from the “Disc Usage” reports of the server, while these were previously still present in the primary role.

The background to this is that after a failover, the respective databases now are present in a different server in the primary role, and now are no longer readable in the secondary, in the standard setting.

In this moment, the System Data Collection Set “Disc Usage”, or the underlying job “collection_set_1_noncached_collect_and_upload” cannot collect data for this database. In contrast on the new primary node these database will now reappear as long as they are active in the primary role there. In principle, this behavior is comprehensible: The Data Collector can no longer find any information on this database and assumes that the latter is no longer relevant – as if it was deleted. One may certainly wish for a possibility of intervention here; however, the MDW is currently not flexible in this regard.

The new report “Transaction Performance Analysis Overview” which is enriched through the newly existent “Transaction Performance Collection Set” in SQL Server 2014 also displays data for no longer active databases.

Having clarified this background, the possible solution is self-evident: The databases must remain readable.

With AlwaysOn High Availability Groups, this is in principal easily done:

Part 1: Datenbanken in Secondary-Rolle

Wenn man nun das MDW standardmäßig einrichtet, wird man feststellen, dass nach einem Failover die Daten der jeweiligen Datenbanken aus den „Disk Usage“-Berichten des Servers verschwinden, wo diese bis zuvor noch in der Primary-Role vorlagen.

Hintergrund ist, dass nach einem Failover die jeweiligen Datenbanken nun auf einem anderen Server in der Primary-Role vorliegen, und auf dem nun Secondary, in der Standardeinstellung nicht lesbar sind.

In diesem Moment kann das System Data Collection Set „Disk Usage“, bzw. der dahinterliegende Job „collection_set_1_noncached_collect_and_upload“ zu dieser Datenbank keine Daten auslesen. Auf dem neuen Primary-Knoten hingegen werden diese Datenbanken nun neu erscheinen, solange sie dort in der primären Rolle aktiv sind.

Prinzipiell ist dieses Verhalten nachvollziehbar: Der Data Collector kann keine Informationen zu dieser Datenbank mehr finden und geht davon aus, dass diese nicht mehr relevant ist – als ob sie gelöscht sei. Sicherlich kann man sich hier eine Eingriffsmöglichkeit wünschen, derzeit ist das MDW aber in dieser Hinsicht nicht flexibel.
- Der neue Bericht „Transaction Performance Analysis Overview“, der über das neu im SQL Server 2014 existierende „Transaction Performance Collection Set“ angereichert wird, zeigt auch Daten für bereits nicht mehr aktive Datenbanken an.

Wenn nun dieser Hintergrund klar ist, liegt die mögliche Lösung nahe: Die Datenbanken müssen lesbar bleiben.
Mit AlwaysOn Hochverfügbarkeitsgruppen (Availability Groups) ist das prinzipiell auch leicht gemacht:

 

AlwaysOn_AvailabiltyGroup_Config_ReadableSecondary

 

However, one needs to be aware of the fact that these databases are now released for all reading access – which should be taken into consideration in respect to application architecture, performance as well as in terms of license. Hence, for the purpose of data collection for performance evaluation alone I CANNOT recommend it.

If however the business applications are supposed to maintain reading access to the secondary point anyway, the data collector is covered with this as well.

One more advice: The setting “Read-Intent only” unfortunately does not work with the MDW since one cannot manually adapt the Connection String accordingly.
Database Mirroring does not support this option at all.

Jedoch muss man sich hierüber im Klaren sein, dass diese Datenbanken nun für sämtliche Lesezugriffe freigegeben sind, was sowohl hinsichtlich Applikationsarchitektur, Performance als auch Lizenztechnisch genau bedacht werden sollte. Allein zum Zweck der Datensammlung zu Performance-Auswertung kann ich das also NICHT empfehlen.
Wenn die Geschäftsanwendungen aber ohnehin Lesezugriffe auf den Sekundärknoten erhalten sollen, dann ist damit der Datensammler ebenfalls abgedeckt.
Noch ein Hinweis: Die Einstellung „Read-Intent only“ funktioniert mit dem MDW bisher leider nicht, da man den Connection String nicht entsprechend manuell anpassen kann.
Datenbankspiegelung unterstützt diese Option gar nicht.

Part 2: Configuration of the MDW-Clients

Since the databases run on a different node after a failover, the MDW reports must be set up in all servers in which the Availability Group is running. Here, one needs to ensure that access to the central MDW-Server is possible from all servers.

To do this (before SQL Server 2014) the SQL Server Agent Account of the client-instance must be included in the mdw_writer role on the MDW-Server (mdw_admin is not necessary) when configuring the MDW through the “Configure Management Data Warehouse Wizard:

Part 2: Konfiguration der MDW-Clients

Da die Datenbanken nach einem Failover auf einem anderen Knoten laufen, müssen die MDW-Berichte auf allen Servern, auf denen die Availability Group läuft, eingerichtet werden.

Dabei muss sichergestellt werden, dass von allen Servern auf den zentralen MDW-Server zugegriffen werden kann.

Dazu muss (vor SQL Server 2014) bei der Konfiguration des MDW über den „Configure Management Data Warehouse Wizard“ der SQL Server Agent Account der Client-Instanz auf dem MDW-Server in die mdw_writer-Rolle aufgenommen werden (mdw_admin ist nicht notwendig):

 

MDW_Config_Server

 MDW_Config_Logins_Users_Roles

 

From SQL Server 2014, at the configuration of the data collection in the client, it is possible to provide a SQL Server Agent Proxy of the type “Operating System (CmdExec)” as account for the access to the central MDW-Server:

Ab SQL Server 2014 kann man bei der Konfiguration der Data Collection auf dem Client einen SQL Server Agent Proxy vom Typ „Operating System (CmdExec)“ als Konto für den Zugriff auf den zentralen MDW-Server hinterlegen:

 

MDW_Config_Server_Proxy

 

MDW_Config_Proxy

 

In this case, it is of course required to authorize the underlying Windows account in the server, instead of the agent itself, as „mdw-writer“.

In diesem Fall muss auf dem Server natürlich der dahinterstehende Windows-Account anstelle des Agents selber als „mdw_writer“ berechtigt werden.

As soon as all clients are authorized accordingly, one can read the data of all SQL Server AG nodes in the central management server. Depending on which server a database is currently present in in the primary role, it will then appear in the according subreport.
- This proceeding also works with mirrored databases in Database Mirroring scenarios – there one can only have one “partner instance” though.

Sobald alle Clients entsprechend berechtigt sind, kann man auf dem zentralen Management Server die Daten aller SQL Server Knoten der AG auslesen. Je nachdem auf welchem Server eine Datenbank gerade in der Primary-Rolle vorliegt, erscheint diese dann in dem entsprechenden Subreport.
- Diese Vorgehensweise funktioniert auch mit gespiegelten Datenbanken bei Datenbankspiegelungs-Szenarien – dort gibt es jedoch maximal eine „Partner-Instanz“.

 

 MDW_Report_Server_Selection

 

 

 

 

 

 

 

 

 

 

Happy collecting

 

Andreas

Christoph Muthmann: Vergessene Jobs: msdb sysssislog

Wer sich mit SSIS (SQL Server Integration Services) beschäftigt, kennt die Tabelle dbo.sysssislog. Dort kann man alle möglichen Informationen über den Lauf seiner SSIS-Pakete abspeichern. Die Tabelle dbo.sysssislog ist also kein Job, oder? Warum taucht sie dann in dieser kleinen Serie auf. Zumal in der msdb?

Full story »

Philipp Lenz: Nachtrag zu Visual Studio Online

vs13onlineIn diesem Artikel habe ich beschrieben, wie man das Visual Studio Express 2013 mit den Data Tools installiert um so BI Projekte online verwalten zu können.

Bei der letzten Installation habe ich bemerkt, dass nach dem installieren der Data Tools sowie dem nachträglichen installieren des Visual Studio Express Web Edition, dass beim Starten der Data Tools das Anmeldefenster mit dem Microsoft Account einfach Weiß bleibt. Um dieses Problem zu beheben, startet man das Visual Studio Express Web Edition und installiert das Update für die Data Tools nach. Anschließend ist ein Login mit dem MS Konto und ein verbinden mit dem Visual Studio Online, bzw. dem TFS möglich.

 

English Version:

In this article I have described how Visual Studio Express Web Edition is installed with the Data Tools 2013 by BI projects to be able to manage online.

In the last installation I‘ve noticed that after installing the Data Tools and Visual Studio Express Web Edition, when you start the Data Tools, the login window with the Microsoft account simply remains white. To resolve this problem, open the Visual Studio Express Web Edition and load the update for the Data Tools after. Then you can log in using the MS account and work with the Visual Studio Online / TFS.

Christoph Muthmann: Vergessene Jobs: syspolicy_purge_job

Ich werde in nächster Zeit mal etwas über die Jobs zusammentragen, die entweder keiner richtig kennt, oder die man erst noch selber anlegen muss. Heute starten wir mal mit syspolicy_purge_job. Jeder kennt ihn, keiner hinterfragt die Hintergründe oder wagt etwas an den Einstellungen zu ändern.

Full story »

Bernd Jungbluth: Access und SQL Server

Migration und Erstellung von Mehrbenutzeranwendungen

Access und SQL Server

Access und SQL Server zeigt die Migration einer Access-Anwendung zu einer Anwendung aus Access-Frontend und SQL Server-Backend.

Dabei migrieren Sie nicht einfach nur die Tabellen von Access zum SQL Server und arbeiten dann mit den eingebundenen Tabellen weiter.
Auf diese Weise profitieren Sie selten von den Vorteilen einer Migration zum SQL Server.

Deshalb zeigt das Buch, wie Sie Abfragen, Formulare, Steuerelemente, Berichte und VBA-Prozeduren optimal für den Zugriff auf Ihre Daten im SQL Server vorbereiten. Und nicht nur das: Sie lernen auch die SQL Server-Seite kennen. Sichten, gespeicherte Prozeduren, Funktionen und Trigger sorgen dafür, dass die richtigen Daten in kürzester Zeit beim Benutzer in der Access-Anwendung landen.

Dies alles ist optimiert für Access 2010 und SQL Server 2012, kann aber zum größten Teil auch für Access 2007 und 2013 sowie SQL Server 2008 und 2008 R2 genutzt werden.

Dieses Buch gibt es nur im André Minhorst Verlag zu kaufen.

Umfangreiche Leseprobe (208 Seiten als PDF - 8 MB) 

Bernd Jungbluth: Access-Entwickler-Konferenz - AEK - in Nürnberg und Hannover

Die Access-Entwickler-Konferenz - kurz AEK - findet dieses Jahr zum 17. Mal statt.

Der Termin am 27. und 28. September in Nürnberg ist bereits ausverkauft.
Für den Termin am 11. und 12. Oktober in Hannover gibt es noch freie Plätze.

Nach ein paar Jahren Pause halte ich dieses Jahr wieder mal einen Vortrag.
Mein Thema ist die Migration der Logik von Access nach SQL Server.

Neben den Tabellen einer Access-Datenbank ist es sinnvoll, auch die Logik von Access nach SQL Server zu migrieren. Dabei steht die Verwendung von Gespeicherten Prozeduren im Fokus. Der Vortrag zeigt die Gründe und Methoden einer solchen Migration und veranschaulicht nebenbei, wie ein Access-Frontend auch ohne eingebundene Tabellen mit den Daten einer SQL Server-Datenbank arbeitet.

Infos und Anmeldemöglichkeiten zur AEK gibt es unter  www.donkarl.com/aek

Eine “Generalprobe” meines Vortrags findet bereits am 16. September bei der PASS-Regionalgruppe Mittelrhein in Koblenz statt.
Infos und Anmeldemöglichkeiten zu diesem Termin enthält dieser Link: http://www.sqlpass.de/Regionen/Deutschland/Mittelrhein.aspx

Christoph Muthmann: Easter Egg im Excel-Soccer zur Ligaverwaltung und Buchverlosung

Bei der Erstellung zum Excel-Soccer zur Ligaverwaltung hatte Mouhrad Louha die Idee, ein verstecktes Feature – ok, mehr eine kleine Spielerei als denn ein echtes Feature – einzubauen.

Full story »

SQLPodcast: #008 – Microsoft Azure

008

Diese Woche konnte ich wieder einmal einen Podcast mit meinem MVP Kollegen Sascha Dittmann aufnehmen.
Trotz größerer technischer Probleme bei unserem ersten Versuch vor einigen Tagen, hat sich Sascha die Zeit genommen und noch einmal fast 2,5 Std. mit mir über alles rund um Microsoft Azure gesprochen. Von Cloud Services, Virtual Machines, SQL Database über Mobile Services bis hin zur DocumentDB haben wir eigentlich (fast) keine Dienste ausgelassen. Sascha hat in diesem Rahmen auch kurz etwas zu seinem neuem Projekt Azure Management App erzählt, das aktuell für das iPhone und demnächst auch hoffentlich für das Windows Phone zur Verfügung steht. Wie immer findet ihr unten einige Links zu allem was wir in der aktuellen Folge besprochen haben.

Download: SQLPodcast #008 (113)

Links:

Philipp Lenz: Wasserfall Diagramme mit PowerPivot

PowerPivot_Logo(english version below)
Nachdem ich bisher Wasserfall Diagramme manuell in Excel erstellt habe, möchte ich hier zeigen, wie das auch in Verbindung mit PowerPivot für Excel funktioniert. Die Datengrundlage ist die gute alte Northwind Datenbank. Um ein Wasserfall Diagramm zu erstellen, benötige ich folgende Informationen:

  • Aktueller Umsatz
  • Umsatz des letzten Monats
    • Verringerung und
    • Vergrößerung des Umsatz
  • Zeit Informationen

Mit folgendem Query lese ich die Daten und löse das Pivot auf, damit ich die Daten besser in PowerPivot verarbeiten kann:

SELECT
 YearNumber
 , MonthNumber 
 , Amount
 , AmountType
 , CASE AmountType
  WHEN 'BaseValue' THEN 1
  WHEN 'Increase' THEN 2
  WHEN 'Reduction' THEN 3
 END As CategorySort
FROM ( SELECT 
  CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  < 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth) * -1 
    ELSE 0
   END As Reduction
  , Data.SalesAmountLastMonth - 
   CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  < 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth) * -1 
    ELSE 0
   END As BaseValue
  , CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  > 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth)  
    ELSE 0
   END As Increase
  , Data.MonthNumber
  , Data.YearNumber
 FROM (
  SELECT
   MONTH(Orders.OrderDate) As MonthNumber
   , YEAR(Orders.OrderDate) As YearNumber
   , SUM(Quantity*UnitPrice) As SalesAmount
   , (
    SELECT SUM(od1.Quantity*od1.UnitPrice) FROM [dbo].[Orders] o1
    INNER JOIN [dbo].[Order Details] od1 ON od1.OrderID = o1.OrderID
    WHERE MONTH(o1.OrderDate) = MONTH(DATEADD(mm, -1, MAX(Orders.OrderDate))) 
    AND YEAR(o1.OrderDate) = YEAR(DATEADD(mm, -1, MAX(Orders.OrderDate)))
   ) As SalesAmountLastMonth
    FROM [dbo].[Orders]
    INNER JOIN [dbo].[Order Details] ON [Order Details].OrderID = Orders.OrderID
    WHERE Year(Orders.OrderDate) = 1997
    GROUP BY 
   MONTH(Orders.OrderDate) 
   , YEAR(Orders.OrderDate)
  ) data
 ) t
 UNPIVOT (Amount for AmountType in (Reduction, BaseValue, Increase)) As Amount
ORDER BY YearNumber, MonthNumber

 

Nachdem diese nun eingelesen wurden sind, habe ich zu jedem Monat 3 Zeilen mit den notwendigen Informationen. Nun lasse ich die jeweilige Kategorie noch sortieren, damit der Basis Wert im Diagramm unten liegt. Übergibt man nun die Daten in ein gestapeltes Diagramm, sieht das folgendermaßen aus:

1

 

2

 

 

3

Nun müssen lediglich folgende Anpassungen gemacht werden:

  • Die Kategorie mit dem Basis Wert ohne Farbe einstellen
  • Verlustwerte rot färben
  • Gewinnwerte grün färben
  • Die Beschriftungen ausblenden

4

Voila! Fertig ist das Wasserfall Diagramm mit Hilfe von PowerPivot.

 

Waterfall Chart with PowerPivot

 

After I’ve created waterfall charts manually in Excel, I want to show here how this works in conjunction with PowerPivot for Excel. The data is from the good old Northwind database.

To create a waterfall chart, I need the following information:

  • Current sales
  • Turnover last month
  • reduction and increase in the sales
  • Time information

With the following query I read the data and make a unpivot so I can better handle the data in PowerPivot:

SELECT
 YearNumber
 , MonthNumber 
 , Amount
 , AmountType
 , CASE AmountType
  WHEN 'BaseValue' THEN 1
  WHEN 'Increase' THEN 2
  WHEN 'Reduction' THEN 3
 END As CategorySort
FROM ( SELECT 
  CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  < 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth) * -1 
    ELSE 0
   END As Reduction
  , Data.SalesAmountLastMonth - 
   CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  < 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth) * -1 
    ELSE 0
   END As BaseValue
  , CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  > 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth)  
    ELSE 0
   END As Increase
  , Data.MonthNumber
  , Data.YearNumber
 FROM (
  SELECT
   MONTH(Orders.OrderDate) As MonthNumber
   , YEAR(Orders.OrderDate) As YearNumber
   , SUM(Quantity*UnitPrice) As SalesAmount
   , (
    SELECT SUM(od1.Quantity*od1.UnitPrice) FROM [dbo].[Orders] o1
    INNER JOIN [dbo].[Order Details] od1 ON od1.OrderID = o1.OrderID
    WHERE MONTH(o1.OrderDate) = MONTH(DATEADD(mm, -1, MAX(Orders.OrderDate))) 
    AND YEAR(o1.OrderDate) = YEAR(DATEADD(mm, -1, MAX(Orders.OrderDate)))
   ) As SalesAmountLastMonth
    FROM [dbo].[Orders]
    INNER JOIN [dbo].[Order Details] ON [Order Details].OrderID = Orders.OrderID
    WHERE Year(Orders.OrderDate) = 1997
    GROUP BY 
   MONTH(Orders.OrderDate) 
   , YEAR(Orders.OrderDate)
  ) data
 ) t
 UNPIVOT (Amount for AmountType in (Reduction, BaseValue, Increase)) As Amount
ORDER BY YearNumber, MonthNumber

After I imported the data, I have 3 lines for each month with the necessary information. Now I let the respective category yet sort, so that the base value is in the chart below. Then we can create a stacked bar chart:
1

 

2

 

 

3

 

Now only the following adjustments must be made​​:
– Set the category to the base value without color
– Reduction is set to red
– Increase is set to ​​green
– Hide the Labels

4

Voila! Finish is the waterfall chart using PowerPivot.

 

Robert Panther: BASTA! vom 22.-26. September 2014 in Mainz

Auch in diesem Jahr bin ich wieder auf der BASTA! in Mainz als Speaker im Einsatz.

Bereits am Dienstag halte ich von 10:00 bis 11:15 Uhr einen Vortrag zum Thema Performante Datenbankzugriffe mit T-SQL, in dem ich altbewährte aber auch neuere Methoden vorstellen will um effektive SQL-Abfragen zu schreiben.

Am Mittwoch unterstütze ich meinen Kollegen von 14:00 bis 15:15 Uhr Florian Nattermann bei seinem Vortrag zum Thema In-Memory Datenbanken vs. Columnstore Index, in dem er diese beiden neuen Technologien gegenüberstellt.

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

BASTA! Herbst 2014 Speakerbutton 2


Tillmann Eitelberg: SSIS ReportGenerator Task 1.8

Auch der SSIS ReportGenerator Task hat diese Woche eine Aktualisierung erfahren. Man merkt vielleicht, auch für eigene Projekte muss ich so langsam das ein oder andere auf SQL Server 2014 umstellen.   Neben dem Update auf SQL Server 2014, wodruch die Komponente jetzt SQL Server 2008 – 2014 unterstützt, wurden einige kleinere Bugs behoben. So […]

Philipp Lenz: Stichtagsinformationen zu einem Datum

uhr(english Version below)

Durch einen Eintrag auf http://sqlmag.com/t-sql/cheat-sheet-calculating-important-dates habe ich mich inspirieren lassen, eine kleine Funktion aus den Statements zu erstellen, die zu einem Datum Informationen liefert. Ganz praktisch bei Reporting Services oder im PowerBI Umfeld, wenn man in einem Bericht verschiedenen Zeit Informationen zu den Daten anzeigen will, bspw. aus welcher Zeit-Periode abgefragt wurde wenn ein Report bspw. nur einen Stichtag aufnimmt, aber alle Daten aus dem aktuellen Jahr selektiert oder dem Quartal …

 

By an entry on http://sqlmag.com/t-sql/cheat-sheet-Calculating-important-dates I’ve inspired me to create a small function of the statements that delivers information to a date. Useful in Reporting Services or in PowerBI environment, if you will, in a report several times to display information about the data. For example, if a report only takes a date as a parameter, but selects all data from the current year or the last quarter …

Funktion / Function:

CREATE FUNCTION dbo.InformationForADate(@QualifyingDate DATETIME)
RETURNS TABLE 
RETURN (
	SELECT
	(SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate) - 1 , '19000101')) AS [FIRST DAY OF LAST YEAR], 
	(SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate), '19000101')) AS [FIRST DAY OF This YEAR],
	(SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate) + 1 , '19000101')) AS [FIRST DAY OF NEXT YEAR],
	(SELECT DATEADD(d, -1, DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate), '19000101'))) AS [LAST DAY OF Last YEAR],
	(SELECT DATEADD(d, -1, DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate) + 1 , '19000101')))  AS [LAST DAY OF This YEAR],
	(SELECT DATEADD(d, -1, DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate) + 2 , '19000101'))) AS [LAST DAY OF NEXT YEAR],
	(SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate) - 1, '19000101')) AS [FIRST DAY Previous MONTH],
	(SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate), '19000101')) AS [FIRST DAY CURRENT MONTH],
	(SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate) + 1, '19000101')) AS [FIRST DAY NEXT MONTH],
	(SELECT DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate), '19000101'))) AS [LAST DAY Previous MONTH],
	(SELECT DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate) + 1, '19000101'))) AS [LAST DAY This MONTH],
	(SELECT DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate) + 2, '19000101'))) AS [LAST DAY NEXT MONTH],
	(SELECT DATEADD(d, -1, DATEDIFF(d, 0, @QualifyingDate))) AS [Midnight Yesterday],
	(SELECT DATEADD(d, -0, DATEDIFF(d, 0, @QualifyingDate))) AS [Midnight Today],
	(SELECT DATEADD(d, 1, DATEDIFF(d, 0, @QualifyingDate))) AS [Midnight Tomorrow],
	(SELECT DATEADD(ss, (60*60*24)-1, DATEADD(d, -1, DATEDIFF(d, 0, @QualifyingDate)))) AS [11:59:59 Yesterday],
	(SELECT DATEADD(hh, 12, DATEADD(d, -1, DATEDIFF(d, 0, @QualifyingDate)))) AS [Noon Yesterday],
	(SELECT DATEADD(ms, (1000*60*60*24)-2, DATEADD(d, -1, DATEDIFF(d, 0, @QualifyingDate)))) AS [11:59:59.997 Yesterday]);

Abfrage / Query:

DECLARE @QualifyingDate DATETIME = GETDATE();
SELECT * FROM dbo.InformationForADate(@QualifyingDate)

Tillmann Eitelberg: Dimension & Metrics

In der aktuellen Google Analytics Version, sind ein paar kleine Änderung für den Umgang mit den Dimensions & Metrics enthalten. Für die erste Verison der Google Analytics Komponente hatte ich mir ein kleines Tool geschrieben, mit dem ich die Dimensionen und Metriken pflegen und dann in das verwendete XML Format exportieren konnte. Google hatte zu […]

Tillmann Eitelberg: SSIS Analytics Version 1.7 beta

Nach einiger Zeit habe ich wieder eine neue Version der SSIS GoogleAnalyticsSource Komponente bei Codeplex veröffentlicht. Neben verschiednen kleineren Bug fixes steht die Komponente in der aktuellen Version jetzt auch endlich für den SQL Server 2014 zur Verfügung. Eine neue Version für SQL Server 2008 wird es dagegen nicht mehr geben. Wer die Komponente auf […]

Torsten Schuessler: #CU package 3 for SQL Server 2014

The Cumulative update package 3 for Microsoft SQL Server 2014 (Build 12.0.2402.0) is available:

Cumulative update package 3 for SQL Server 2014

Important! This Cumulative Update includes MS14-044!!!

I wish you a nice day,
tosc

Torsten Schuessler

Philipp Lenz: PowerPivot: Switch() Funktion – oder auch: Wie komme ich ohne Hilfstabellen bei Übersetzungen aus?

PowerPivot_LogoEnglish version below

Wenn man einfache Übersetzungen in Daten vornehmen wollte, bspw. Gruppenschlüssel in Namen zu übersetzen, bediente ich mich bisher immer an Hilfstabellen die ich in Excel definiert und dann in das Datenmodell eingebunden haben.

So in etwa – eine Tabelle beinhaltet einen Schlüssel um eine Kundengruppe zu beschreiben. Die Beschreibung befindet sich in einer Hilfstabelle die in PowerPivot eingebunden wird und per Verknüpfung dann die Übersetzung vornimmt. Problem dabei ist meist, wenn ein Wert in den Daten vorkommt, der nicht in der Übersetzung Tabelle vorkommt. Da habe ich dann per einer berechneten Spalte per Bedingter Anweisung die fehlenden Werte markiert.

Hier der vorherige Weg:

2014-08-18_12-37-08 2014-08-18_12-39-03

 

 

  

  

 

  

 

 

 

 

 

Hier kommt nun die SWITCH Funktion ins Spiel.

Mit dieser kann ich Werte überprüfen und übersetzen. Grundsätzlich geht das Ganze auch mit einer IF Funktion, aber deutlich unübersichtlicher und somit schlecht in der Wartung.

=SWITCH(Customers[Group]; "A"; "High Priority"; "B"; "B Customers"; 
"C"; "C Customers"; "no valid group")

Der Grundsyntax von SWITCH:

SWITCH(expression, 
    value1, result1,
    value2, result2,
     :
     :
     else
    )

2014-08-18_12-41-07

 

 

PowerPivot Switch Function – or the end of the linked translation tables?

If you wanted to make simple translations in data, for example I want to translate a Customer Group Key in a name, I used to create translations Tables in Excel and then integrated into the data model of PowerPivot.
Something like that – a table contains a key to a customer group to describe. The description is another table that is linked in PowerPivot and then performs the translation by a relationship. Problem is mostly, if a value exists in the data that is not present in the translation table. Since then I have checked the missing values ​​by a calculated column using a IF/ELSE statement.
Here the previous path:
2014-08-18_12-37-08 2014-08-18_12-39-03

 

 

  

  

 

  

 

 

 

 

 

Here are the SWITCH function:
With this I can check values ​​and translate. Basically, the whole thing goes well with an IF function, but much less clear and thus poorly in maintenance.

 =SWITCH (Customers[Group], "A", "High Priority", "B", "B Customers", 
"C", "C Customers", "no valid group")

The basic syntax of SWITCH:

SWITCH(expression, 
    value1, result1,
    value2, result2,
     :
     :
     else
    )

2014-08-18_12-41-07
 

Uwe Ricken: ISNULL als Prädikat – SEEK oder SCAN?

Im Gespräch mit einem Kunden haben wir uns über NON SARGable Abfragen unterhalten. Dabei ist unter anderem auch ausgeführt worden, dass Funktionen grundsätzlich zu Index-Scans führen, da sie immer jede Zeile überprüfen müssen. Dieses Thema habe ich im Artikel “Optimierung von Datenbankmodellen – SARGable Abfragen” bereits ausführlich behandelt. Viele Funktionen arbeiten tatsächlich nach diesem Prinzip; dennoch ist z. B. die Arbeitsweise von ISNULL als Predikat davon abhängig, wie das Attribut in der Tabelle definiert wird.

Testumgebung

Um die unterschiedliche Arbeitsweise von ISNULL zu demonstrieren, werden zwei Tabellen mit – fast – identischer Struktur definiert und mit 10 Datensätzen gefüllt.

-- Tabelle mit NULL-Attribut
CREATE TABLE dbo.ZIP_NULL
(
    Id     INT       NOT NULL,
    CCode  CHAR(3)   NULL,
    ZIP    CHAR(10)  NULL,
 
    CONSTRAINT pk_ZIP_NULL_Id PRIMARY KEY CLUSTERED (Id)
);
GO
 
CREATE INDEX ix_ZIP_NULL_CCode ON dbo.ZIP_NULL (CCode);
GO
 
CREATE TABLE dbo.ZIP_NOT_NULL
(
    Id     INT       NOT NULL,
    CCode  CHAR(3)   NOT NULL,
    ZIP    CHAR(10)  NULL,
 
    CONSTRAINT pk_ZIP_NOT_NULL_Id PRIMARY KEY CLUSTERED (Id)
);
GO
 
CREATE INDEX ix_ZIP_CCode ON dbo.ZIP_NOT_NULL (CCode);
GO
 
INSERT INTO dbo.ZIP_NULL (Id, CCode, ZIP)
VALUES
    (1, 'DE', '12345'),
    (2, 'DE', '12346'),
    (3, 'DE', '12347'),
    (4, 'DE', '12348'),
    (5, 'CH', '12349');
 
INSERT INTO dbo.ZIP_NOT_NULL
SELECT * FROM dbo.ZIP_NULL;
GO

Die Tabelle [dbo].[ZIP_NULL] besitzt ein Attribut [CCode], das NULL erlaubt während die zweite Tabelle [dbo].[ZIP_NOT_NULL] für dieses Attribut keine NULL-Einträge zulässt. Auf das Attribut [CCode] wird in beiden Fällen ein nonclustered Index erstellt. Nachdem die Testumgebung fertig gestellt ist, werden identische Abfragen auf beide Tabellen ausgeführt. Um die Unterschiede im Ausführungsplan besser deutlich zu machen, werden UNION ALL Abfragen ausgeführt.


Abfragen


Um zu überprüfen, wie sich ISNULL als Prädikat in Abfragen verhält, wird eine Abfrage verwendet, die den Index auf dem Attribut [CCode] nutzt.



SELECT Id, CCode FROM dbo.ZIP_NULL WHERE ISNULL(CCode, 'CH') != 'DE'
UNION ALL
SELECT Id, CCode FROM dbo.ZIP_NOT_NULL WHERE ISNULL(CCode , 'CH') != 'DE';
GO


Die obige Abfrage wird als UNION ALL-Abfrage ausgeführt, um die Suchoperationen in einem Ausführungsplan sichtbar zu machen. Führt man die Abfrage aus, erhält man den folgenden Ausführungsplan:


EXECUTION_PLAN_01


Während für die Tabelle [dbo].[ZIP_NULL] ein Index Scan verwendet wird, kann für die zweite Tabelle [dbo].[ZIP_NOT_NULL] ein deutlich effektiverer Index Seek angewendet werden. Die Eigenschaften der beiden Operatoren zeigen, wie ISNULL auf den Index angewendet werden kann:


EXECUTION_PROPERTIES_01EXECUTION_PROPERTIES_02




Die linke Abbildung zeigt die Eigenschaften des INDEX SCAN. Für die Operation wurde der Index [ix_ZIP_NULL_CCode] verwendet. Für den Index Scan konnte kein SEEK-Prädikat verwendet werden; die Funktion ISNULL muss auf jeden Datensatz im Index angewendet werden. Ein Blick auf die rechte Abbildung zeigt, dass ein – effektiverer – Index Seek angewendet werden kann. Die Funktion ISNULL wurde vollständig ignoriert und die Abfrage wird behandelt wie ein Prädikat [CCode <> DE].


Begründung


Der Query Optimizer von Microsoft SQL Server ist “intelligent” genug, um festzustellen, dass für das Attribut [CCode] in der Tabelle [dbo].[ZIP_NOT_NULL] eine Einschränkung besteht, die sicherstellt, dass das Attribut nie NULL sein kann. Der Query Optimizer muss also nicht jede Zeile prüfen, um festzustellen, ob ein NULL-Wert im Attribut vorhanden ist; die Funktion ISNULL ist für den Index [ix_ZIP_NOT_NULL_CCode] sinnlos.


Zusammenfassung


Bereits bei der Definition von Tabellenstrukturen kann im Vorfeld für eine Unterstützung des Query Optimizers gesorgt werden. ISNULL ist nur eines von vielen Beispielen, wie Einschränkungen den Query Optimizer die Arbeit erleichtern.


Herzlichen Dank fürs Lesen!


Tillmann Eitelberg: Google Analytics Sampling Data

Immer wieder gibt es Irritationen, wenn Benutzer die Zahlen aus der Google Analytics Source Komponente mit den Zahlen im Google Analytics Portal vergleichen. Bei kleineren Datenmengen sind die Daten meistens identisch, bei größeren Datenmengen variieren dagegen die Daten – unter umständen auch recht stark. Dieses Verhalten stellt nicht direkt einen Fehler innerhalb der SSIS Komponente […]

Philipp Lenz: Standardfeldsatz in PowerPivot und PowerView

PowerPivot_LogoTabellen können im PowerPivot Datenmodell so vorbereitet werden, dass man in einem Klick eine vorgefertigte Aufbereitung in PowerView erhalten kann.

see english version below

Als Datengrundlage dient hier eine Abfrage aus der Adventure Works:

SELECT Production.Product.ProductID, Production.Product.Name ProductName, Production.Product.Color, Production.Product.ListPrice, Production.ProductCategory.Name AS Category, Production.ProductPhoto.ThumbNailPhoto, 
                  Production.ProductSubcategory.Name AS Subcategory
FROM     Production.Product INNER JOIN
                  Production.ProductProductPhoto ON Production.Product.ProductID = Production.ProductProductPhoto.ProductID INNER JOIN
                  Production.ProductPhoto ON Production.ProductProductPhoto.ProductPhotoID = Production.ProductPhoto.ProductPhotoID INNER JOIN
                  Production.ProductSubcategory ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID INNER JOIN
                  Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID

Als erstes markiert man in PowerPivot das Feld „ThumbNailPhoto“ als Bild:
piv

Im nächsten Schritt werden die Felder konfiguriert, die bei einer Analyse als Standard in der Reihenfolge ausgegeben werden sollen:
feldsatz

Nun muss das Tabellenverhalten noch so eingestellt werden, dass bei der Verwendung das Bild automatisch mit verwendet wird:
tabellenverhalten

Nun kann in PowerView durch einen Doppelklick auf die Tabelle die Liste erstellt werden. Stellt man nun die Ansicht von der Tabelle auf Karten um, ist die Analyse durch 2 Klicks für den Anwender aufbereitet.
doubleklick

karte

Weitere Informationen gibt es hier:

English Version

Tables can be prepared in the PowerPivot data model so that you can get a ready-made presentation in Power View in a single click.
Following Query are used for this example (Adventure Works)

SELECT Production.Product.ProductID, Production.Product.Name ProductName, Production.Product.Color, Production.Product.ListPrice, Production.ProductCategory.Name AS Category, Production.ProductPhoto.ThumbNailPhoto, 
                  Production.ProductSubcategory.Name AS Subcategory
FROM     Production.Product INNER JOIN
                  Production.ProductProductPhoto ON Production.Product.ProductID = Production.ProductProductPhoto.ProductID INNER JOIN
                  Production.ProductPhoto ON Production.ProductProductPhoto.ProductPhotoID = Production.ProductPhoto.ProductPhotoID INNER JOIN
                  Production.ProductSubcategory ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID INNER JOIN
                  Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID

The first thing you marked in PowerPivot the “Thumbnail Photo” field as an image:
piv

In the next step, the fields are configured to be output in an analysis as the standard, in order:
feldsatz

Now, the behavior of table must be set so that in use, the image is automatically used with:
tabellenverhalten

Now you can create in Power View, double-click the table list. If, now, the view from the table to map to, the analysis is prepared by 2 clicks for the user.
doubleklick

karte

Uwe Ricken: Aufsteigende Indexschlüssel – Performance Killer

Ein reißerischer Titel, oder? Aber tatsächlich ist für die Performance einer Datenbankanwendung das Design ein entscheidender Faktor, der – wie in diesem Fall – schnell zu einem Performancekiller werden kann. Dieses Problem ist in der Community allseits unter dem Begriff “Ascending Key Problem” bekannt. Das Problem tritt in einer Anwendung auf, in der zu einer Haupttabelle [dbo].[master_table] jeweils n Datensätze in einer Detailtabelle [detail_table] gespeichert werden. Sobald neue Datensätze in der Haupt- und Detailtabelle eingetragen werde und anschließend die abhängigen Detaildaten abgefragt werden, verschlechtert sich die Performance dramatisch, wenn es sich um neue Einträge handelte. Die Zusammenhänge zeigt der nachfolgende Artikel.

Problembeschreibung

Im aktuellen Projekt wird zunächst in einer Haupttabelle [dbo].[master_table] ein neuer Datensatz eingetragen. Anschließend werden weitere Datensätze in die Detailtabelle [dbo].[detail_table] eingetragen, die den Primärschlüssel des Hauptdatensatzes als Fremdschlüssel erhalten. Die Anzahl der neuen Datensätze in der Detailtabelle kann sehr stark variieren. Von 1 Datensatz bis zu 1.000 Datensätzen kann das Volumen variieren. Insbesondere, wenn sehr große Datenmengen in der Detailtabelle eingetragen werden, benötigen Abfragen, die zuvor wenige Sekunden liefen, Minuten. Meine Aufgabe besteht darin, diese starken Abweichungen so weit wie möglich zu verhindern.

Testumgebung

Um das Problem zu verdeutlichen, werden zwei Tabellen erstellt; für die Darstellung reicht eine Simplifizierung mit wenigen Attributen.

CREATE TABLE dbo.master_table
(
    Id INT        NOT NULL  IDENTITY (1, 1),
    c1 CHAR(200)  NOT NULL  DEFAULT ('stuff for c1 in master_table'),
 
    CONSTRAINT pk_master_table_Id PRIMARY KEY CLUSTERED (Id)
);
GO

Die erste Tabelle wird die “Kopfdaten” erhalten. Der Primärschlüssel ist gleichzeitig der Clustered Key und wird durch die Funktion IDENTITY als fortlaufend gekennzeichnet.



CREATE TABLE dbo.detail_table
(
    Id         INT       NOT NULL  IDENTITY (1, 1),
    c1         CHAR(200) NOT NULL  DEFAULT ('more stuff for c1 in detail_table'),
    master_id  INT       NOT NULL,
 
    CONSTRAINT pk_detail_table_id PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT fk_master_table_id FOREIGN KEY (master_id) REFERENCES dbo.master_table (id)
);
 
CREATE INDEX ix_detail_table_master_id ON dbo.detail_table (master_Id);
GO

Die zweite Tabelle erhält ebenfalls einen geclusterten Primärschlüssel und – für die referenzielle Integrität – das Attribut [master_id], das durch eine Fremdschlüsseleinschränkung sicherstellt, dass nur Schlüsselwerte der Haupttabelle vorhanden sein dürfen. Zusätzlich wird das Attribut [master_id] aus Optimierungsgründen indexiert!


Nachdem beide Tabellen erstellt worden sind, werden 1.000 Datensätze in die Tabelle [dbo].[master_table] geschrieben. Anschließend werden für jeden Eintrag der Haupttabelle jeweils 1- 10 Datensätze in die Detailtabelle eingetragen.



INSERT INTO dbo.master_table DEFAULT VALUES;
GO 1000
 
DECLARE @i   INT = 1
DECLARE @rows    INT = CAST(RAND() * 10 + 1 AS INT);
DECLARE @cntr    INT = 1;
 
WHILE @i <= 1000
BEGIN
    SET @cntr = 1;
    RAISERROR ('Inserting %i rows for id %i', 0, 1, @rows, @i) WITH NOWAIT;
 
    WHILE @cntr <= @rows
    BEGIN
        INSERT INTO dbo.detail_table (master_id) VALUES (@i);
        SET @cntr += 1;
    END
 
    SET    @i += 1;
    SET    @rows = CAST(RAND() * 10 + 1 AS INT);
END
GO

Nachdem der für die Tests benötigte Datenbestand eingetragen wurde, werden die Statistiken beider Tabellen aktualisiert:



UPDATE STATISTICS dbo.master_table WITH FULLSCAN;
UPDATE STATISTICS dbo.detail_table WITH FULLSCAN;

Testszenario


Für die in dieser Konstellation auftretenden Probleme reicht eine simple Abfrage, die den Index [ix_detail_table_master_id] verwendet. Damit die Verwendung eines gespeicherten Plans ausgeschlossen werden kann, wird die Abfrage mit der Option RECOMPILE ausgeführt!



SELECT m.*,
       d.*
FROM   dbo.master_table AS m INNER JOIN dbo.detail_table AS d
       ON (m.id = d.master_id)
WHERE  m.id = 10 OPTION (RECOMPILE);

Die [Id] wurde willkürlich gewählt und ist nur repräsentativ! Für die obige Abfrage ergibt sich der folgende Ausführungsplan:


EXECUTION_PLAN_01


Im Ausführungsplan ist sehr gut zu erkennen, dass für die Auswahl der Daten aus [dbo].[detail_table] der Index [ix_detail_table_master_id] verwendet wird. Da jedoch nicht alle Attribute für die Ausgabe im Index enthalten sind, müssen die zusätzlichen Werte aus dem Clustered Index mittels Key Lookup ermittelt werden. Insgesamt ergibt sich aus der Abfrage ein vertretbares IO (grafische Darstellung mit http://www.statisticsparser.com/).


STATISTICSPARSER_01


Das oben beschriebene Verhalten ist für JEDEN [Id]-Wert identisch; für jeden Master-Datensatz gibt es zwischen 1 und 10 Detaildatensätze. Im nächsten Schritt wird ein neuer Master-Datensatz mit 1.000 Detaildatensätzen eingetragen und die Abfrage erneut mit der neuen [ID] ausgeführt:



-- Neue ID = 1001
INSERT INTO dbo.master_table DEFAULT VALUES;
GO
 
-- 1.000 Datensätze mit der zuvor erstellten ID
INSERT INTO dbo.detail_table (master_id) VALUES (1001);
GO 1000

Nachdem die neuen Daten eingetragen wurden, wird eine neue Abfrage auf die zuvor erstellte [Id] ausgeführt und dabei ebenfalls das IO gemessen:



SET STATISTICS IO, TIME ON;
GO
 
SELECT m.*,
       d.*
FROM   dbo.master_table AS m INNER JOIN dbo.detail_table AS d
       ON (m.id = d.master_id)
WHERE  m.id = 1001 OPTION (RECOMPILE);
 
SET STATISTICS IO, TIME OFF;
GO

Der Ausführungsplan für die Abfrage ist – trotz Neukompilierung – absolut identisch zum Plan der ersten Ausführung.


EXECUTION_PLAN_02


Obwohl die Datenmenge in der Tabelle [dbo].[detail_table] für die [Id] deutlich höher ist, wird der identische Plan verwendet. Trotz der Option RECOMPILE schlägt der Query Optimizer des Microsoft SQL Servers eine identische Ausführungsstrategie vor! Entsprechend sieht das generierte IO aus:


STATISTICSPARSER_02


Die Ausführungszeit ist 10 Mal höher als bei den vorherigen Abfragen und – bedingt durch die identische Ausführungsstrategie – das IO entsprechend hoch. Doch warum verwendet Microsoft SQL Server keinen effizienteren Ausführungsplan? Das Problem ist sehr schnell lokalisiert, wenn man sich die Eigenschaften der Operatoren des Ausführungsplanes etwas genauer anschaut – insbesondere die geschätzten Datensätze in der Tabelle [dbo].[detail_table]!


EXECUTION_PLAN_03


Obwohl 1.000 Datensätze mit dem Fremdschlüsselwert in der Tabelle vorhanden sind, geht Microsoft SQL Server nur von 1 (!) Datensatz aus.


Warum eine geschätzte Anzahl von 1 Datensatz?


Statistiken zur Abfrageoptimierung sind Objekte, die Informationen über die Verteilung von Werten in Spalten einer Tabelle oder indizierten Sicht enthalten. Der Query Optimizer von Microsoft SQL Server erstellt Ausführungspläne, die auf Statistiken beruhen. Bei jeder Ausführung einer Abfrage prüft Microsoft SQL Server zunächst auf Basis von Statistiken, wie viele Datensätze zu erwarten sind. Basierend auf diesen Werten wird dann ein geeigneter Ausführungsplan erstellt.


Ist die AUTO_UPDATE_STATISTICS-Option zur automatischen Aktualisierung von Statistiken aktiviert, stellt der Abfrageoptimierer fest, wann Statistiken veraltet sind und aktualisiert diese Statistiken, sobald sie von einer Abfrage verwendet werden. Statistiken sind veraltet, wenn die Datenverteilung in der Tabelle oder indizierten Sicht durch INSERT-, UPDATE-, DELETE- oder MERGE-Vorgänge geändert wurde. Das Problem ist jedoch der Schwellenwert, ab dem Microsoft SQL Server eine Aktualisierung der Statistiken vornimmt! Für das Aktualisieren von Statistiken gibt es Schwellenwerte, die wie folgt berechnet werden (http://support.microsoft.com/kb/195565):



  • Wenn die Kardinalität für eine Tabelle weniger als sechs beträgt und die Tabelle sich in der Datenbank tempdb befindet, wird nach jeweils sechs Änderungen in der Tabelle eine automatische Aktualisierung durchgeführt.
  • Wenn die Kardinalität für eine Tabelle größer als 6, jedoch kleiner oder gleich 500 ist, wird der Status nach jeweils 500 Änderungen aktualisiert.
  • Wenn die Kardinalität für eine Tabelle größer als 500 ist, wird die Statistik nach jeweils (500 + 20% der Tabelle) Änderungen aktualisiert.

Sind in der Tabelle [dbo].[detail_table] 5.500 Datensätze vorhanden, müssen 1.600 Änderungen (INSERT, UPDATE, DELETE, MERGE) vorgenommen werden, bevor die Statistiken aktualisiert werden. Es wurden jedoch nur 1.000 Datensätze hinzugefügt – eine Aktualisierung findet also – NOCH – nicht statt! Microsoft SQL Server erstellt bei Erstellung eines Index automatisch Statistiken für die betroffenen Attribute. Dieses Histogramm kann jederzeit abgefragt werden:



DBCC SHOW_STATISTICS ('dbo.detail_table', 'ix_detail_table_master_id') WITH HISTOGRAM;

DBCC_STATISTICS_01


Die Abbildung zeigt die letzten Einträge im Histogramm des Index [ix_detail_table_master_id]. Die erste Spalte repräsentiert einen dedizierten Wert, zu dem “exakte” Statistiken vorliegen, diese Liste kann man nicht individuell anpassen! Als Beispiel für die Interpretation eines Histogramms soll der Wert in Zeile 196 der Abbildung verwendet werden:


Würde nach dem Wert [Id] = 990 gesucht werden, sucht Microsoft SQL Server zunächst im Histogramm, ob ein entsprechender Wert unmittelbar vorhanden ist. Sofern der Wert im Histogramm selbst vorhanden ist, wird im Attribut [EQ_ROWS] geprüft, wie viele Datensätze vorhanden sind. Diesen Referenzwert verwendet der Query Optimizer für den geeigneten Ausführungsplan.


Wird nach dem Wert [Id] = 989 gesucht, wird dieser Wert nicht im Histogramm gefunden. Jedoch kann Microsoft SQL Server an Hand der Werte in [RANGE_HI_KEY] feststellen, dass der Wert zwischen 985 und 990 liegt. Das Attribut [DISTINCT_RANGE_ROWS] in Zeile 196 gibt Auskunft darüber, dass zwischen dem Wert 985 und dem Wert 990 insgesamt 4 unterschiedliche Werte liegen (986, 987, 988, 989). Das Attribut [RANGE_ROWS] besagt, dass diese 4 unterschiedlichen Werte insgesamt 22 Mal in der Tabelle vorkommen. Somit ergibt sich ein Durchschnitt von 5,5 Datensätzen pro Einzelwert. Die beiden nachfolgenden Abbildungen zeigen, wie diese statistischen Werte von Microsoft SQL Server für den Ausführungsplan verwendet werden.



SELECT * FROM dbo.detail_table WHERE master_id = 990 OPTION (RECOMPILE);

EXECUTION_PLAN_04


Die Abfrage nach der [Id] = 990 schätzt 10 Datensätze, die durch die Abfrage zurück geliefert werden.



SELECT * FROM dbo.detail_table WHERE master_id = 989 OPTION (RECOMPILE);

EXECUTION_PLAN_05


Die zweite Abfrage findet im Histogramm keinen entsprechenden Datensatz und muss sich an den Durchschnittswerten orientieren, die zwischen den beiden Werten im Histogramm gespeichert sind.


Für den neuen Wert [ID] = 1001 gibt es im Histogramm KEINE Informationen! Es gibt auch keine Bereichssschlüssel, die größer sind als der Wert 1.000. Im Beispiel konnte mittels Histogramm ein Wert ermittelt werden, der relativ nah an den tatsächlichen Wert lag. Für den neuen Datensatz können aus dem Histogramm keine Daten ermittelt werden – somit geht Microsoft SQL Server IMMER von einem Wert 1 aus (Dieses Vorgehen gilt auch für den neuen Cardinal Estimator von Microsoft SQL Server 2014, wenn nur ein Prädikat verwendet wird).


EXECUTION_PLAN_06


Dieses Problem kann auftauchen, wenn die Indexschlüssel fortlaufend sind und somit in den Statistiken kein adäquater Referenzwert gefunden werden kann. Um den Unterschied in den Ausführungsplänen zu sehen, werden die Statistiken für [dbo].[detail_table] aktualisiert und die Abfrage erneut ausgeführt:



UPDATE STATISTICS dbo.detail_table WITH FULLSCAN;
GO
 
SELECT m.*,
       d.*
FROM   dbo.master_table AS m INNER JOIN dbo.detail_table AS d
       ON (m.id = d.master_id)
WHERE  m.id = 1001;

EXECUTION_PLAN_09


Nur durch das Aktualisieren der Statistiken hat sich der Ausführungsplan geändert. Der Query Optimizer von Microsoft SQL Server erkennt die tatsächliche Anzahl von Datensätzen (1.000) und entscheidet sich für einen Index Scan statt eines Index Seek. Der Index Scan benötigt keinen expliziten Zugriff auf den Clustered Index und ist somit für die Ausführung effizienter als ein INDEX SEEK.


Lösungswege


Das obige Beispiel arbeitet mit kleinen Datenmengen – das tatsächliche Umfeld, in dem es um die Optimierung geht, beinhaltet ca. 500.000.000 Datensätze. Man kann sich also vorstellen, welchen Einfluss veraltete Statistiken auf die Ausführungspläne haben. Nachfolgend werden einige Lösungsvorschläge gegeben, die diese Problematik entschärfen können.


Verwendung von Variablen


Ein Lösungsansatz ist die Verwendung von Variablen, da dann nicht auf das Histogramm sondern auf die Verteilung der Daten (dem sogenannten Density Vektor) zugegriffen wird. Der Query Optimizer verwendet Dichten, um Kardinalitätsschätzungen für Abfragen zu erweitern, die mehrere Spalten aus derselben Tabelle oder indizierten Sicht zurückgeben. Die Abfrage wird wie folgt geändert und ausgeführt:



DECLARE @Id INT = 1001;
 
SELECT  m.*,
        d.*
FROM    dbo.master_table AS m INNER JOIN dbo.detail_table AS d
        ON (m.id = d.master_id)
WHERE   m.id = @Id;

EXECUTION_PLAN_07


Wie man in der Abbildung sehen kann, hilft diese Option nicht wirklich weiter. Das hängt von folgenden Ursachen ab:



  • Die Dichte der Bestandsdaten vor dem Einfügen hatte eine regelmäßige Verteilung (<= 10 Datensätze)
  • Die Statistiken wurden noch nicht aktualisiert

Um das Vorgehen von Microsoft SQL Server zu verstehen, muss man sich einen Überblick über die zur Verfügung stehenden Statistiken verschaffen. Mit dem nachfolgenden Befehl werden ALLE statistischen Informationen zum verwendeten Index ausgegeben. Die für die Abfrage wesentlichen Informationen werden in der Abbildung rot gekennzeichnet:



DBCC SHOW_STATISTICS ('dbo.detail_table', 'ix_detail_table_master_id');

DBCC_STATISTICS_02


Insgesamt sind 5.547 Datensätze in der Tabelle (respektive im Index) vorhanden. Für das Attribut [master_id] besteht eine Dichte von 0,001. Die Dichte wird berechnet aus 1 / Anzahl EINDEUTIGER Werte. Für den vorliegenden Fall gibt es 1.000 eindeutige Zahlenwerte: 1 / 1000 = 0,001. Dieser Wert wird vom Query Optimizer verwendet und mit der Anzahl aller in der Tabelle / Index vorhandenen Datensätze multipliziert. Somit ergibt sich – statistisch – ein Verhältnis von 5,547 Datensätze pro Einzelwert. Dieser Wert ist für das vorliegende Beispiel unbrauchbar; es sind 1.000 neue Datensätze hinzugefügt worden. Der Ausführungsplan kann nicht weiter optimiert werden! Microsoft SQL Server verwendet nicht das Histogramm sondern eine heuristische Vorgehensweise. Sofern eine gleichmäßige Verteilung der Daten in der Detailtabelle vorhanden wäre, wäre dieser Ansatz auf jeden Fall eine Möglichkeit, das Dilemma zu lösen!


OPTIMIZE FOR UNKOWN


Die Option “OPTIMIZE FOR UNKNOWN” weist den Abfrageoptimierer an, beim Kompilieren und Optimieren der Abfrage für alle lokalen Variablen, einschließlich der Parameter, die mit erzwungener Parametrisierung erstellt werden, statistische Daten statt der Anfangswerte zu verwenden. Die Option ist identisch mit der Verwendung von Variablen, die zur Laufzeit instanziiert werden (siehe vorheriges Beispiel). Auch diese Vorgehensweise scheidet für das Beispiel aus.


Optimierung des Index


Das Problem in der Abfrage ist der teure Key Lookup, der die zusätzlich benötigten Informationen aus dem Clustered Index beziehen muss. Um diese Key Lookups zu verhindern, reicht es aus, diese Attribute in den Index mit zu übernehmen. Für das Beispiel ist die Tabelle sehr klein gewählt worden; deswegen ist es – für das Beispiel – unproblematisch. Der Index wird wie folgt angepasst:



CREATE INDEX ix_detail_table_master_id ON dbo.detail_table (master_id) INCLUDE (c1) WITH DROP_EXISTING;

Bedingt durch den Neuaufbau des Index wurden die Statistiken neu erstellt. Es werden 1.000 weitere Datensätze hinzugefügt, um erneut veraltete Statistiken zu erhalten. Anschließend wird die Abfrage mit der neuen [Id] = 1002 abgefragt:



SELECT m.*,
       d.*
FROM   dbo.master_table AS m INNER JOIN dbo.detail_table AS d
       ON (m.id = d.master_id)
WHERE  m.id = 1002;

EXECUTION_PLAN_08


Der teure Key Lookup ist nun verschwunden, da alle Informationen vollständig aus dem Index gelesen werden können. Für das Beispiel dieses Artikels ist diese Variante sicherlich die ideale Lösung – die Realität sieht jedoch etwas anders aus. Da – codebedingt – immer ein SELECT * durch die Applikation ausgeführt wird, müssten alle Attribute der Relation Bestandteil des Index sein. Damit einher geht aber, dass beim Einfügen von neuen Datensätzen die Zeiten für das Laden der Daten verschlechtert werden. Hier muss also entschieden werden zwischen Performance bei der Abfrage oder Performance beim Laden von Daten.


UPDATE STATISTICS manuell ausführen oder als Auftrag implementieren


Eine weitere Möglichkeit besteht darin, die Statistiken – wie weiter oben bereits demonstriert – manuell zu aktualisieren. Durch eine Aktualisierung werden die neuen Werte in das Histogramm übernommen und die Statistiken aktualisiert. Dieses Verfahren ist jedoch für das vorliegende Beispiel unbrauchbar, da die Probleme unmittelbar nach dem Einfügen der neuen Datensätze auftauchen. Der Code müsste also dahingehend geändert werden, dass im Code nach dem Einfügen der neuen Daten erst ein UPDATE STATISTICS ausgeführt wird, bevor die Daten erneut abgefragt werden. Tatsächlich wird diese Option derzeit geprüft – ich persönlich halte sie für ungeeignet, da ein FULLSCAN über die Tabelle ebenfalls sehr rechenintensiv ist.


TraceFlag 2371


Wie oben beschrieben, verwendet Microsoft SQL Server für die Aktualisierung von Statistiken Schwellenwerte, die überschritten sein müssen. Bei kleinen Tabellen sind diese Einstellungen sicherlich ideal. Im aktuellen Fall mit mehreren Millionen Datensätzen wird dieser Schwellenwert von 20% klar zu einem Problem. Bei 500 Millionen Datensätzen müssten zunächst 100.000.000 Änderungen vorgenommen werden, bevor die Statistiken aktualisiert werden. Mit Microsoft SQL Server 2008 R2 SP1 hat Microsoft auf diesen Umstand reagiert und das Traceflag 2371 eingeführt. Mit diesem Traceflag wird auf den konstanten Schwellenwert von 20% verzichtet und der Aktualisierungsintervall für Statistiken wird basierend auf der Anzahl von vorhandenen Datensätzen angepasst. Weitere Details zu diesem Thema können im Blogartikel “Changes to automatic update statistics in SQL Server – traceflag 2371” von Jürgen Thomas nachgelesen werden.


TraceFlag 2389, 2390


Wer nähere Informationen zu diesem Thema nachlesen möchte, der sollte sich den Artikel “Statistics on ascending keys” von Benjamin Nevarez unbedingt durchlesen! Für mein Beispiel sei gesagt, dass das Traceflag unbrauchbar ist, wenn die Anzahl der eingefügten Datensätze zu sehr “streut”. Trotz Verwendung des TF 2389 war maximal eine geschätzte Anzahl von Datensätze von ~11 Datensätzen möglich. Sicherlich eine Verbesserung – aber keine Lösung.


Weiterhin gilt zu beachten, dass die Traceflags in Microsoft SQL Server 2014 nur dann funktionieren, wenn NICHT mit dem neuen “Cardinal Estimator” gearbeitet wird. Dazu gibt es einen kurzen Artikel von Rick Kutschera “Ascending Key in SQL 2014


Zusammenfassung


Das von mir beschriebene Szenario ist in fast allen Datenbanken anzutreffen; um es adäquat zu lösen, sind verschiedene Lösungsansätze denkbar. Entscheidend für die geeignete Lösung sind folgende Faktoren:



  • Verteilung der Datenmenge auf die einzelnen Schlüsselattribute
  • Struktur der Tabelle
  • Analyse der Workloads

Insgesamt ein nicht immer zu 100% lösbares Problem. Der Lösungsansatz, den Index zu erweitern, muss ich verwerfen. Ziel muss es sein, so schnell wie möglich Daten in die Tabellen zu schreiben, da durch die große Datenmenge andere Anwender Probleme bekommen, bereits existierende Datensätze zu bearbeiten (Speicherung wird verzögert).


Das Thema ist auf jeden Fall so interessant, dass ich dazu einen Vortrag vorbereite, den ich auf den Regionaltreffen der PASS Deutschland e. V. vortragen möchte.


Herzlichen Dank fürs Lesen!


Philipp Lenz: Time Dimension

uhr

In diesem Artikel will ich kurz beschreiben wie man eine Zeit Dimension erstellt und in PowerPivot verwendet. Für eine Datums-Dimension siehe hier.

(English Verison)

Als erstes brauchen wir eine Zeit Tabelle:

CREATE TABLE [dbo].[Dim_Time]
(
[Time] TIME NOT NULL,
[am_pm] CHAR(2) NOT NULL,
CONSTRAINT [PK_Table] PRIMARY KEY ([Time])
)

Mit folgendem Script befallen wir die Tabelle:

DECLARE @time TIME = '00:00:01';
DECLARE @ampm CHAR(2);
DECLARE @Stop BIT = 0;

WHILE @Stop = 0
BEGIN
IF @time < '12:00:00'
BEGIN
SET @ampm = 'am';
END
ELSE
BEGIN
SET @ampm = 'pm';
END

INSERT INTO Dim_Time (Time, am_pm)
VALUES (@time, @ampm);

SET @time = DATEADD(ss, 1, @time);
IF @time = '23:59:59' SET @stop = 1;

END

Nun können wir die Tabelle mit Daten in PowerPivot (oder was auch immer ..) verwenden:

2014-07-27_15-53-28 2014-07-27_15-59-51

 

English Version

In this article I will describe, how to create and use a time dimension in a powerpivot model– how to create and populate a date dimension table take a look here.
First we must create the table with the time data:

CREATE TABLE [dbo].[Dim_Time]
(
[Time] TIME NOT NULL,
[am_pm] CHAR(2) NOT NULL,
CONSTRAINT [PK_Table] PRIMARY KEY ([Time])
)

Now we must populate the table with data:

DECLARE @time TIME = '00:00:01';
DECLARE @ampm CHAR(2);
DECLARE @Stop BIT = 0;

WHILE @Stop = 0
BEGIN
IF @time < '12:00:00'
BEGIN
SET @ampm = 'am';
END
ELSE
BEGIN
SET @ampm = 'pm';
END

INSERT INTO Dim_Time (Time, am_pm)
VALUES (@time, @ampm);

SET @time = DATEADD(ss, 1, @time);
IF @time = '23:59:59' SET @stop = 1;

END

Now we can use the data in PowerPivot (or something else):

2014-07-27_15-53-28 2014-07-27_15-59-51

 

Bernd Jungbluth: Seminar - SQL Server Integration Services

Es gibt einen neuen Termin für das Seminar SQL Server Integration Services:

01. Oktober 2014 im Hotel Best Western Premier Bellevue Rheinhotel in Boppard am Rhein

SQL Server Integration Services - kurz SSIS - wird in erster Linie im Segment Business Intelligence zur Konsolidierung und Integration von Daten aus den unterschiedlichsten Datenquellen verwendet, um so eine konsistente Datengrundlage für Analysen und Auswertungen zu liefern.

Das Leistungsspektrum von SSIS bietet sich aber nicht nur für Business Intelligence an.
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 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 2008 R2 und 2012.

Preis: 350 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.

Torsten Schuessler: Time to Say Thank You!

Yes, it is SysAdminDay!

July 25, 2014 14th Annual
System Administrator
Appreciation Day

You are welcome:

I wish you ALL a nice SysAdminDay!

CU
tosc 

Torsten Schuessler: #CU package 1 for SQL Server 2012 Service Pack 2

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

Cumulative Update 1 for SQL Server 2012 SP2

I wish you a nice day,
tosc

Torsten Schuessler

Marcel Franke: Impressions from the SQL Saturday #313

Also this year we had another great SQL Saturday here in Germany. I want to take the chance to say thank you to the organization team, to all the speakers and the volunteers for the big effort and time they spent to make this happen. From my opinion it was again a great success. We used the same location as last year, at the Campus in Augustin, and I personally like the combination of a university campus and a technology event like this. This year we also had more participants than last year and the team was able to deliver very good sessions in 5 parallel tracks (http://sqlsaturday.com/313/schedule.aspx).

I also had the chance to give some of my time to the SQL community and had a session about the different In-Memory technologies in SAP HANA, Power Pivot and SQL Server. If you are interested in my slides, I share them via Slideshare.

 

If you have any feedback or questions don’t hesitate to reach out. Finally I also want to share some impressions from the conference with you. Thank you to our photograph Dirk, who put together some more photos on OneDrive: VCSL3p

SQLSaturday313_Rheinland_449SQLSaturday313_Rheinland_267SQLSaturday313_Rheinland_416SQLSaturday313_Rheinland_423


Filed under: Conferences

Marcel Franke: A call to all Data Engineers and BI workers

In the last two years I had the chance to get my hands on some very exciting Data Analytics projects and I wanted to take the chance to recap and to reach out to Data Engineers and BI Consultants. Why?

In the area of IT we see lot’s of trends coming up every year. Some are going, some are staying, but sometimes we see also a paradigm shift. These shifts have a tremendous impact on the way we worked before and how we will work in the future, for example the rise of the Internet, the area of Business Intelligence and Data Warehouse and the whole E-Commerce shift. And now we can see new shift coming up:

The era of Big Data

The difficult thing with a paradigm shift is we need to rethink certain ideas, the way we did business before and we will do in the future. And if we don’t do it, others will do it and we will not be as successful as we have been in the past.  So let me get to that story in more detail.

Big Data vs. Data Analytics

image

Big Data is now out there for a while and people already understand that storing large amount of data is not good enough. There was a big hype about it and we are now at a point that the words “Big Data” already got a negative touch. It’s very exciting to see the big progress in new technologies like Hadoop, where customers can store nearly all their data. But in 90% of the cases it is totally useless to throw all your data into a data analysis problem. Just talking about technologies does not meet the needs of users and customers anymore.

I also don’t like to talk about Big Data, because it’s misleading, instead I’d like to talk about Data Analytics and that’s what it’s all about. So the focus is clearly on analyzing data and creating value out of it. This is also not big news, but we were told that only a specific type of people with a specific knowledge can do this: Data Scientists.

These guys are currently seen as heroes in the analytics market and everybody is looking for someone with little or no luck. So here’s my point: Analyzing data is not a new venture, in the area of Business Intelligence and Data Mining people did this all the time for years. But what has changed and where does the big shift happens?

We can clearly say that we can’t get around Data Analytics anymore. If you talk with customers and you just want to talk about Data Warehouses and BI you are missing half of the discussion. All the companies I talk to clearly think about Big Data or Data Analytics and how they can combine it with their Data Warehouse and BI solutions. But technology has become secondary in these discussions. Don’t get me wrong, Data Warehouses are still necessary and in use but the focus clearly has changed. We see new types of data that are interesting to analyze like streaming, social, logs, sensor data and there are also new ways to analyze data like pattern recognition, predictions, clustering, recommendations, etc. So the operational data that is typically stored in Data Warehouses is still necessary, but it has to be combined with the other types of data, I mentioned before. But in today’s discussions with customer, it’s all about use cases and solutions.

And in order to close the loop let me quickly come back to the Data Scientists. I agree that we need statistical and mathematical skills to solve problems like customer segmentation, next best offers and recommendations, predictions, data correlations etc. but we need much more skills to provide whole solutions to customers, so a good team mix is much more important.

New skills and approaches

With the era of Big Data and the new analytical possibilities we can also see new solution approaches. Data Analytic projects are much more iterative and evolutionary because research on your data is a big part of your work. Companies discover new use cases and sometimes they change their whole business model, because they find competitive advantages or new possibilities for revenue.

google acquisition of nest, thermostat, ProtectA good example for this are Smart Homes. We can see that the digitalization is now arriving at our homes. In the near future devices in our home are fully connected with each other and share data between each other. When I set my  weak up alarm for the next morning, an app will tell this to my heating system. My heating system then knows when I want to take a shower and need warm water or when I want to drive with my electric car.

Energy providers are highly interested in this information and in my daily behavior of energy consumption. Why?

Because when they better understand my energy consumption, they can better predict their energy sales and also how much energy is consumed at a certain time. And when they better predict the energy consumption of their customers, they can better handle their purchase of power energy at the energy exchange market.

The challenge with these new business models, and there are plenty of others, is that they are new. And for energy companies that have offered power supply in a very classical way for decades, this is a big change. So that’s why also technology providers like Google enter the market. They know how to handle the data, how to analyze it and how to use it for business models to provide additional services. Should you not accept these changes in business models, even when they take some time before they settle on the market, you wake up, when it is too late. Because applying changes need some time and companies need the experience in order to apply these changes step by step

And I think this is the most important learning in the last years. You can stick with you old business models if they work, but if an industry is changing you need to adapt. And Data Analytics happens in several industries and the most successful companies are those, that start small, get their experiences very quickly and are able to adopt the changes. There are very good examples in Germany like the Otto Group in Retail, Yello Strom in the Energy sector and also some new Startups.

As I mentioned before Data Analytic projects need to be very iterative in their approach. A lot of projects start with an idea or a use case or a feeling and we need to quickly understand if there is a business case behind it or not. In order to support those projects we need a different approach, which I call “Laboratory and Factory”.

image

The Laboratory

The Laboratory is for experiments. Here we can test all our use cases, ideas or just discover patterns in data. The important thing is, it must be cheap. We don’t want to spend much money on experiments, if we don’t know the business case behind them yet. The work can be compared to „panning for gold“. There is plenty of gold to be found, but for every gold nugget a multiple of sand needs to be panned. So from a technology perspective I would use whatever fits to the use case. In the laboratory we should be more flexible on different technologies like SQL, Hadoop, Storm, Pig, R or Python, D3 or other tools which help solve our problems.

From a data perspective we can work on a subset of the data. What we probably want to avoid is data sampling, which is often times very time consuming, so we prefer real data first. So the main goal of the laboratory is to…

image_thumb14

The Factory

After we proved our business cases in the laboratory we can than apply our data analytic models to the factory. The factory means that we operate these models on a daily base and couple them to our business processes. Here we typically use the existing enterprise platforms and we often see mixed solutions of classical data analytics platforms combined with Open Source technologies. A new requirement in the last years is that we want to apply our analytical models to the whole data history. Technologies and servers are now capable to make this possible. So our factory gives us the integration of our analytical business models to our daily business at enterprise scale, on the whole data set and probably enriched with external data.

New technologies

image_thumb16

Some month ago I had the chance to visit the European Hadoop conference in Amsterdam and it was a great chance to get another view on Big Data and Data Analytics from an Open Source perspective. It has become very obvious that Hadoop and NoSQL based technologies drive the Big Data and Analytics market. There is a whole industry behind companies like Cloudera, Hortonworks, MapR and others that push new innovations. The Stinger initiative for example was a team project of around 45 companies with 140 developers that improved the performance of the Hive technology by a factor 100 within 1 year. Imagine the power of innovation that these companies like Google, Yahoo, Facebook, Hortonworks and also Microsoft can bring to these technologies when they combine the skills. Clearly when you come from a traditional BI solution like SQL Server, Teradata, Oracle or SAP you would say that there are still some gaps in the usability and ease of use. But on the other side these technologies are built just for Big Data solutions. They offer fantastic capabilities and some of them are great technologies, also if it is sometimes harder to work with them.

And  when you see that all big players in the market like IBM, Oracle, Teradata, Microsoft and SAP have partnerships with Hadoop platform providers, then it is very clear, that there is no way around these technologies anymore. It is just a question how to combine them best. Microsoft for example has a nice offering with the Analytical platform system (APS), which is a scale-out box where you can mix Hadoop and SQL Server in a highly parallel and very high performing way.

Summary

I personally believe in the new paradigm shift of Big Data and Data Analytics. I already had  the chance to enjoy several projects in that area and I’m very happy to start on new ones in the next weeks. But that is also the reason why I wanted to write this article. In order to stay competitive we need to accept changes in the market and start to deal with them. What does that mean?

We have to keep learning new technologies, different approaches, new business models, etc. Old traditional BI projects will be also done in the future but the really interesting and challenging projects will all deal with Data Analytics. There are lots of really fascinating use cases, which due to non-disclosure agreements I can’t talk in more detail about them. But what I can say is that a little bit of ETL, SQL and building a Data Warehouse and some reports is not good enough anymore. Technologies these days can do much more and customers are starting to understand this. The demand and the expectation is increasing especially for analytical models in combination with business process optimizations. So the time is very exciting and I can encourage everybody to get started and if you don’t know where, let me know…

 

References:

http://hadoopilluminated.com/hadoop_illuminated/Hardware_Software.html

http://www.myandroiddaily.com/2014/01/why-you-should-care-about-googles-new.html

http://www.devlounge.net/friday-focus/031111-in-the-lab

http://datasciencebe.com/datalab/

http://picturethepursuitofhappiness.blogsport.de/2009/07/19/neugier/

http://blogs.microsoft.com/blog/2014/04/15/a-data-culture-for-everyone/


Filed under: Big Data, Data Analytics, Data Science

Uwe Ricken: Verwendung von Variablen statt Literalen

Im Forum eines von mir sehr geschätzten MVP-Kollegen wurde eine Frage bezüglich der Verwendung von Variablen anstelle von Literalen gestellt (hier). Das Problem war, dass die Abfrage sich deutlich verlangsamte, wenn Variablen statt Literale verwendet wurden. Warum dieses Verhalten für Microsoft SQL Server jedoch korrekt ist, soll der folgende Artikel zeigen.

Ausgangsfrage

Ich grübele über einem Select, der wesentlich langsamer wird, wenn Variablen als Parameter verwendet werden. Setze ich feste Werte als Parameter, ist der Select ca. 3mal schneller. Die Problematik bewegt sich leider nicht im 10tel-Sekunden-Bereich...

Testszenario

Für den Test wird aus der Tabelle [person].[Person] der AdventureWorks2012 Datenbank  ein paar Attribute des Datenbestandes in eine neue Tabelle [dbo].[Person] kopiert und neu indiziert.
SELECT  BusinessEntityID        AS    Id,
        PersonType,
        FirstName,
        LastName
INTO    dbo.Person
FROM    person.person;
GO
 
-- Eintragen eines dedizierten neuen PersonType
INSERT INTO dbo.Person (Id, PersonType, FirstName, LastName)
VALUES (30000, 'UR', 'Uwe', 'Ricken');
GO
 
-- Clustered Index auf Id
CREATE UNIQUE CLUSTERED INDEX ix_Person_Id ON dbo.Person (Id);
GO
 
-- Index auf PersonType
CREATE INDEX ix_Person_PersonType ON dbo.Person (PersonType);
GO
Um das Problem noch etwas zu verschärfen, wurde ein neuer Datensatz hinzugefügt, dessen [PersonType] ein Alleinstellungsmerkmal besitzt; dabei handelt es sich um meiner Einer…

Nachdem die Tabelle mit allen Indexen erstellt wurde, werden zwei Abfragen mit der gleichen Ergebnismenge ausgeführt:



-- Anzeige von IO aktivieren
SET STATISTICS IO ON;
GO
 
-- Demoabfrage 1
SELECT * FROM dbo.Person WHERE PersonType = N'UR';
GO
 
-- Demoabfrage 2
DECLARE @PersonType NCHAR(2) = N'UR'
SELECT * FROM dbo.Person WHERE PersonType = @PersonType;
GO
 
-- Anzeige von IO deaktivieren
SET STATISTICS IO OFF;
GO

Obwohl beide Abfragen – scheinbar – identisch sind, zeigt ein erster Blick auf das IO, dass Abfrage 2 deutlich mehr IO produzieren muss als Abfrage 1; beide Abfragen verwenden unterschiedliche Ausführungspläne: ExecutionPlan_01


Die prozentuale Verteilung wird durch das generierte IO untermauert:



-- Abfrage 1
Person-Tabelle. Scananzahl 1, logische Lesevorgänge 4, physische Lesevorgänge 0...
 
-- Abfrage 2:
Person-Tabelle. Scananzahl 1, logische Lesevorgänge 116, physische Lesevorgänge 0...

Wird für die erste Abfrage von einem Kostenverhältnis von 6% bei der Ausführung ausgegangen, so fallen die restlichen 94% der Ausführungskosten auf die 2. Abfrage (das sind vom Query Optimizer geschätzte Kosten!). Abfrage 1 verwendet den Index [ix_Person_PersonType] für einen INDEX SEEK während Abfrage 2 einen – deutlich teureren – INDEX SCAN unter Verwendung des Clustered Index verwendet. Das Geheimnis dieses Verhaltens liegt – wie meistens – in den Statistiken. Bevor die Statistiken näher untersucht werden, solle ein Blick auf die Eigenschaften der Ausführungsoperatoren geworfen werden.


ExecutionProperties_01 Die erste Abbildung zeigt die Eigenschaften des INDEX SEEK Operators der ersten Abfrage. Man kann erkennen, dass der Operator den Index [ix_Person_PersonType] verwendet. Um den [SEEK]-Vorgang durchführen zu können, wird das Literal N’UR von Microsoft SQL Server parametrisiert (wegen Wiederverwendbarkeit des Ausführungsplans) und der Indexschlüssel [PersonType] durchsucht. Der wichtigste Punkt für die Ursache des genannten Problems ist die Information über die [geschätzte Anzahl von Zeilen] und die [tatsächliche Anzahl von Zeilen]. Microsoft SQL Server überprüft vor Beginn der Ausführung der Abfrage zunächst die Statistiken und erhält als Information, dass der Wert “UR” im Index nur 1 Mal vorkommt. Basierend auf diesen Informationen entscheidet sich Microsoft SQL Server für einen Index Seek, da das zu erwartende IO deutlich geringer ist als bei einem Index Scan! Ausgehend von einem zu erwartenden Datensatz wird die Abfrage ausgeführt und tatsächlich wird auch nur ein Datensatz an den Client geliefert. Das Verhältnis zwischen geschätzten Datensätzen und tatsächlichen Datensätzen ist in Ordnung. Vollkommen anders jedoch sieht es bei der zweiten Abfragen aus.


 


 

ExecutionProperties_02

Obwohl die zweite Abfrage auch nur einen Datensatz liefert, geht Microsoft SQL Server von vollständig anderen Voraussetzungen bei der Ausführung der Abfrage aus. In der nebenstehenden Abbildung ist zu erkennen, dass Microsoft SQL Server nicht – wie zuvor – von einem Datensatz ausgeht sondern von 2.853,29 Datensätzen. Diese Schätzung liegt im Verhältnis zum Ergebnis vollkommen daneben und aus diesem Grund entscheidet sich der Query Optimizer von Microsoft SQL Server für einen Index Scan. Die Frage, die sich in diesem Zusammenhang stellt, ist natürlich, WARUM weichen die geschätzten Zahlen so weit voneinander ab? Im direkten Zusammenhang steht auf jeden Fall die Verwendung von Parametern statt Literale. Wird ein Literal verwendet, so kann der Query Optimizer diesen Umstand sofort in die Berechnung eines möglichen Ausführungsplans einbeziehen. Bei der Verwendung einer Variablen kann der Query Optimizer von Microsoft SQL Server den möglichen Wert dieser Variablen nicht einbeziehen – Microsoft SQL Server muss also “schätzen”, wie viele Datensätze durch die Abfrage unter Verwendung einer Variablen zurückgeliefert werden. Wie genau Microsoft SQL Server intern dabei vorgeht, zeigt die nachfolgende Erläuterung



Statistiken – der Schlüssel für optimale Abfragen

Wie bereits oben erwähnt, benötigt der Query Optimizer von Microsoft SQL Server Statistiken, um einen effizienten Ausführungsplan zu erstellen. Wie wichtig aktuelle Statistiken für Microsoft SQL Server sind, habe ich bereits im Artikel “Bedeutung von aktuellen Statistiken für Indexe” beschrieben. Für das vorliegende Problem sind die Statistiken des Index [ix_Person_PersonType] interessant und werden mit dem folgenden Befehl ausgegeben:

DBCC SHOW_STATISTICS('dbo.Person', 'ix_Person_PersonType');

DBCC_SHOW_STATISTICS_01

Die Abbildung zeigt alle Informationen zur Statistik des Index [ix_Person_PersonType]. Insgesamt werden durch die Ausführung des Befehls drei Informationsbereiche ausgegeben:

  • Statistik-Information (Header):
  • Density Vektor
  • Histogramm

Verwendung von Literalen

Wenn eine Abfrage mit einem Literal ausgeführt wird, verwendet der Query Optimizer von Microsoft SQL Server das Histogramm. Bezugnehmend auf die erste Abfrage sucht der Query Optimizer im Histogramm in [RANGE_HI_KEY] – immer sortiert - nach dem Literal “UR” und – sofern dieser Eintrag vorhanden ist – kann im Attribut [EQ_ROWS] die Anzahl der zu erwartenden Datensätze ausgelesen werden. Ist der Wert “UR” nicht zu finden, verwendet Microsoft SQL Server [AVG_RANGE_ROWS] als Wert. Aus der obigen Abbildung geht hervor, dass für den Literal “UR” 1 Datensatz zu erwarten ist – somit wendet der Query Optimizer einen INDEX-SEEK als Ausführungsoperator an.

Verwendung von Variablen

Das Verhalten des Query Optimizer ändert sich jedoch, wenn Variablen ins Spiel kommen. Da Variablen in der Kompilierungsphase nicht vom Query Optimizer verwendet werden können, muss die Berechnung anders erfolgen – durch Verwendung des DENSITY Vektors. Der DENSITY Vektor gibt die Dichte eines Wertes im Verhältnis zur Gesamtmenge aller eindeutigen Datensätze an. Die Formel für die Ermittlung dieses Wertes lautet 1 / Anzahl eindeutiger Werte. Insgesamt gibt es 7 unterschiedliche Werte im Schlüsselattribut des Index bei insgesamt 19.973 Datensätzen. 1 / 7 = 0,1428571 ! Diese “Dichte” verwendet der Query Optimizer, wenn eine – zur Laufzeit der Abfrage – unbekannte Variable verwendet wird. 19.973 * 0,1428571 = 2.853,2848583 Microsoft SQL Server geht – basierend auf der Dichte – davon aus (geschätzt), dass 2.853 Datensätze durch die Abfrage geliefert werden. Diese hohe Anzahl von Datensätzen würde – auf Grund des Key Lookup – dazu führen, dass ein deutlich höheres IO produziert würde; ein INDEX SCAN produziert ein deutlich geringeres IO!

Frage – Antwort – Fehler


Bei der Beantwortung der gestellten Frage wurden – leider – falsche Antworten gegeben, die den Sachverhalt nicht wirklich berücksichtigt haben. Warum die Antworten verkehrt sind, möchte ich kurz erläutern:


... weil es auf jeden Fall noch langsamer wäre, weil nicht auf einen gespeicherten Ausführungsplan zurückgegriffen werden kann.


Nein, das ist nicht richtig. Abfragepläne werden – generell - nur dann nicht gespeichert, wenn die Option RECOMPILE für die Abfrage verwendet wird. Ansonsten wird – bis auf ganz wenige Ausnahmen – IIMMER ein Ausführungsplan gespeichert; sonst wäre obige Auswertung nicht möglich gewesen.


Ansonsten sollten weder der Datentyp noch der Umstand, dass Parameter verwendet werden, einen spürbaren Unterschied bei der Ausführung machen.


Nein, das ist auf jeden Fall falsch wie das oben beschriebene Verfahren belegt.


Wir haben über Nacht die Ausführungspläne aktualisieren lassen, das hat am Ende das Problem gelöst.


Ganz bestimmt nicht! Oder anders ausgedrückt: Nicht das oben beschriebene Problem war die Ursache für die langsame Ausführung sondern “Parameter Sniffing”. Mehr Informationen zu Parameter Sniffing habe ich in diesem Artikel beschrieben “Tücken bei der Verwendung von sp_executeSQL”. Würde tatsächlich ein extremes Ungleichgewicht in den Indexschlüsseln vorhanden sein und die Tabelle sehr groß sein, kann das von mir beschriebene Verhalten sicherlich auf die Problembeschreibung angewendet werden.



Herzlichen Dank fürs Lesen!


Philipp Lenz: Einführung in PowerQuery Teil 2 von ?

thIn diesem Eintrag möchte ich das Tutorial in der Einführung in PowerQuery weiterführen. Hier geht es um die Gruppierungsfunktion und das Verbinden zweier Tabellen. Hier werden wir eine Excel Datei auslesen mit 2 Tabellenblättern – eins hält die Bestellungen für das Jahr 2014 und das zweite Blatt die Informationen zu den Kunden. Ziel ist die Auswertung der Umsätze pro Kunde und pro Monat. Normalerweise würde ich das in PowerPivot lösen, aber für eine einfache Aufbereitung der Daten und deren Darstellung in diesem Kontext bietet PowerQuery eine etwas leichtere Methode – da hier gleich die Daten zum Schluss gruppiert zur Verfügung stehen und lediglich dargestellt werden müssen. Vielleicht aber auch Geschmackssache …

pq_1Als erstes wechseln wir auf das Ribbon PowerQuery und laden Daten aus einer Datei, bzw. gleich Excel.

pq_2Im nächsten Schritt befindet sich im Navigator die jeweilige Tabellen aus dem Excel Sheet. Einerseits sehen wir hier die Tabellenblätter aber auch die Tabellen die sich auf den Blättern befinden. Da sich hier jeweils eine Tabelle auf jedem Blatt befindet, wähle ich die Tabellenblätter aus. Hier setze ich gleich die Haken bei beiden, wähle aus, dass die Daten in das Datenmodell (PowerPivot) geladen werden.

Weiterhin wähle ich die erste Tabelle Bestellungen aus und klicke auf Bearbeiten.

 

 

 

 

 

pq_3

Nun muss in beiden Tabellen jeweils die erste Zeile noch als Tabellenüberschrift gekennzeichnet werden. Nachdem dies bei den Bestellungen durchgeführt wurde, klicke ich auf Anwenden und schließen und wähle die Tabelle Kunden im Navigator aus und Bearbeite diese genauso:

pq_3_4Nachdem die Kundentabelle ebenfalls die Überschriften aus der ersten Zeile entnimmt, schließe ich ebenfalls das Fenster über Anwenden und schließen und klicke im Navigator auf Laden damit beide Excel Tabellen in PowerQuery verfügbar sind.

Nun müssen beide Tabellen miteinander verbunden werden …

Dazu wähle ich aus dem Navigator die Kunden Tabelle aus und klicke auf Abfragen zusammenführen:

pq_4

 

 

Nun sind die Tabellen verbunden – technisch ist das wie ein Left Join zu sehen. Über den Haken “nur Übereinstimmende Zeilen einbeziehen” würde das einem Inner Join entsprechen.

pq_5Nun können die Spalten aus der rechten Tabellen hinzugefügt werden.

pq_6

Da ich hier lediglich die Bestellungen aus dem Jahr 2014 vor mir habe und das Ziel eine Auswertung pro Monat ist, transformiere ich die Spalte Bestelldatum über einen Rechtsklick in Monate um. Danach steht das Feld als Monatsnummer zur Verfügung.

Die Transformation kann nachträglich immer noch wieder zurückgestellt werden. An sich handelt es sich nur um eine Formatierung.

 

 

Im nächsten Schritt brauchen wir eine Gesamtsumme. In den Daten steht derzeit nur die Summe der Einzelposition und die Menge zur Verfügung. Daher fügen wir hier nun eine Benutzerdefinierte Spalte ein mit folgender Formel:pq_7

 

pq_8Nun markiere ich noch die nicht mehr benötigten Spalten und entferne diese:

 

pq_9Nun markiere ich die Spalten Name und Monat und klicke auf Gruppieren Nach – nun erfolgt die Gruppierung, bzw. Summierung auf den Gesamtpreis.

pq_10

Nach dem Klick auf Anwenden und Schließen befindet sich im PowerPivot Datenmodell die gruppierte und aufbereitete Tabelle. Diese kann ich nun in Excel darstellen….

 

Robert Panther: Bericht: SQL Saturday in Sankt Augustin

Am 28.06. veranstaltete die PASS zum dritten Mal einen SQL Saturday in Deutschland. Dabei wurde zum zweiten Mal die Hochschule Bonn-Rhein-Sieg in Sankt Augustin als Veranstaltungsort gewählt. Die gute Erreichbarkeit mit dem Auto sowie (am Wochenende) ausreichenden Parkplatzmöglichkeiten sprechen für den Veranstaltungsort. Lediglich die etwas unbequemen Hochschul-Sitzbänke hinterließen einen suboptimalen Eindruck, der durch die qualitativ hochwertigen Vorträge aber mehr als wettgemacht wurde. So fanden sich dann auch ca. 250 Teilnehmer ein, die sich die 30 Vorträge (verteilt auf 5 parallele Tracks zu den Bereichen BI, DBA, Development, Mixed und Open) anhörten. Unter den Speakern waren viele international renommierte Experten, bekannte Buchautoren und MVPs vertreten, darunter Scott Klein, Andreas Wolter, Dr. Holger Schwichtenberg, Dejan Serka, Chris Testa-O’Neill, Matija Lah, Uwe Ricken, Christoph Muthmann, Oliver Engels, Konstantin Klein (um nur einige zu nennen). Die Vorträge deckten verschiedene Themenbereiche und Niveaus ab, so dass für jeden sicherlich etwas Interessantes dabei war. Zwischen den Vorträgen gab es Gelegenheit sich bei den verschiedenen Sponsoren der Veranstaltung über deren Produkte aus dem SQL Server Umfeld zu informieren. Zum Abschluss der Veranstaltung gab es noch diverse Verlosungen der Sponsoren, so dass sich viele Teilnehmer über diverse Sachpreise (vom Buch bis hin zu Hardware) freuen konnten. Doch bereits am Vortag der Veranstaltung trafen sich bereits einige SQL-Enthusiasten an der Hochschule um sich beim Big Data Hackathon intensiv mit Microsoft BigData-Technologien wie HDInsight und PowerBI (PowerQuery, Power View, Power Map und andere) auseinanderzusetzen.

Insgesamt war dieser SQL Saturday eine runde Veranstaltung, die jedem zu empfehlen ist. Schließlich gibt es nicht oft die Möglichkeit quasi zum Nulltarif an so viele interessante Informationen zu kommen. Auf der Website zur Veranstaltung kann man sich zu einigen Vorträgen die PowerPoint-Slides ansehen: http://www.sqlsaturday.com/313/eventhome.aspx

Beim SQL Saturday handelt es sich um eine Veranstaltungsreihe, die von der Professional Association for SQL Server (kurz PASS), weltweit durchgeführt wird. Das Besondere an dieser Veranstaltung ist, dass keinerlei Teilnahmegebühr erhoben wird, die Veranstaltung also komplett durch Sponsoren finanziert wird. Dafür, dass die Teilnehmer einen Samstag Freizeit opfern, bekommen diese hochkarätige Vorträge zu allen möglichen Themen rund um SQL Server von ausgewiesenen Experten zu hören (darunter viele MVPs).


Sascha Lorenz: SQL Server Usergroup Emsland in Lingen – PowerShell 101 für SQL Admins

Am 24.07.14 werde ich die neue SQL Server Usergroup in Lingen im Emsland mit einem Vortrag unterstützen. Die Gruppe wird von William Durkin moderiert.

Start ist um 17:30 Uhr bei der IT-Emsland, Kaiserstraße 10B, 49809 Lingen (Ems). Dort soll es auch Parkplätze geben. Im Anschluss an das Treffen ist noch ein wenig Socializing in einem Lokal geplant.

Mein Vortrag wird “PowerShell 101 für Administratoren” sein, welchen ich auch bereits auf dem SQLSaturday #313 gezeigt hatte. Da ich mehr Zeit habe, werde ich auch ein wenig mehr ins Detail gehen können. ;-)

Und als kleine Besonderheit ist das wohl mein erster Vortrag, welchen ich als SQL Server MVP halten werde. Mal schauen, wie das so ist. Ich freue mich auf Lingen!

Rick Kutschera: Ascending Key in SQL 2014

Don’t know if you are all aware of that, but just to make sure…

SQL 2014 introduced a new cardinality estimator that gets automatically activated once you switch the compatibility level to 120. In general this is a very cool thing, and in the very most cases it brings better results than the old one. There is one scenario though where you might face problems with it: The cardinality estimator was never very good with ascending keys. (e.g. Identity columns.) The old one was bad, the new one is exactly as bad… The problem is… For the old one there was a workaround with Traceflags 2389 and 2390. Those DO NOT work with the new cardinality estimator anymore, they are just ignored. So if you are using those TFs be aware of that change.

Martin Hey: Mit dem Service Broker auf Datenänderungen reagieren

Im SQL Server gibt es seit längerem den SQL Server Service Broker. Mit dessen Hilfe kann man live auf Datenänderungen reagieren. Beispiel gefällig?

Ich habe eine sehr einfache Bibliotheksdatenbank. Enthalten ist nur eine Tabelle (dbo.Books) mit den Spalten ID, Author und Title. Und ich habe eine WPF-Anwendung in der in der MainView per Entitiy Framework die Bücher geladen und an die Liste gebunden werden.

<Window x:Class="MyApp.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="350" Width="525">
    <Grid>
        <ListBox ItemsSource="{Binding BookList}" >
            <ListBox.ItemTemplate>
                <DataTemplate>
                    <TextBlock Text="{Binding Title}"/>
                </DataTemplate>
            </ListBox.ItemTemplate>
        </ListBox>
    </Grid>
</Window>

public class MainViewModel
{
    public MainViewModel()
    {
        ReloadData();
    }

    private void ReloadData()
    {
        using (var entitites = new LibraryEntities())
        {
            bookList.Clear();
            foreach (var book in entitites.Books)
            {
                bookList.Add(book);   
            }
        }
    }

    private readonly ObservableCollection bookList = new ObservableCollection();

    public ObservableCollection BookList
    {
        get
        {
            return bookList;
        }
    }
}

So weit erst einmal kein Hexenwerk. Der spannende Teil kommt jetzt.

Seit ADO.NET 2.0 gibt es die Klasse SqlDependency, mit der Änderungen an den Daten überwacht werden können. Diese kann für diesen Zweck auch hier verwendet werden.

SqlDependency verfügt über 2 statische Methoden Start und Stop und wie der Name schon vermuten lässt, kann man damit das Tracking starten und auch wieder beenden. In meinem Beispiel sind diese im Konstrukor und im Dispose des ViewModels. Der restliche Code wird in der Reload-Methode angefügt...
public class MainViewModel : IDisposable
{
    private string connectionString;

    public MainViewModel()
    {
        connectionString = new LibraryEntities().Database.Connection.ConnectionString;
        dispatcher = Dispatcher.CurrentDispatcher;
        SqlDependency.Start(connectionString);
        ReloadData();
    }

    private void ReloadData()
    {
        using (var entitites = new LibraryEntities())
        {
            bookList.Clear();
            foreach (var book in entitites.Books)
            {
                bookList.Add(book);   
            }
        }

        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (var command = new SqlCommand("SELECT Title, Author FROM dbo.Books", connection))
            {
                command.Notification = null;
                var dependency = new SqlDependency(command);
                dependency.OnChange += OnDependencyChange;
                command.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
    }

    private void OnDependencyChange(object s, SqlNotificationEventArgs e)
    {
        ((SqlDependency)s).OnChange -= OnDependencyChange;

        if (e.Type == SqlNotificationType.Change)
        {
            dispatcher.Invoke(this.ReloadData);
                
        }
    }

    private readonly ObservableCollection<Books> bookList = new ObservableCollection<Books>();

    private Dispatcher dispatcher;

    public ObservableCollection<Books> BookList
    {
        get
        {
            return bookList;
        }
    }

    public void Dispose()
    {
        SqlDependency.Stop(connectionString);
    }
}

... und hier beginnt es etwas schmutzig zu werden.

SqlDependency basiert auf den Möglichkeiten von ADO.NET 2.0. Damit wird hier eine SqlConnection benötigt und ein SqlCommand, das mit einem DataReader auch ausgeführt werden muss. Und es gibt noch ein paar weitere Punkte zu beachten:
  1. Auf der Datenbank muss der Broker aktiviert sein.
  2. Der Benutzer benötigt ausreichende Berechtigungen (SUBSCRIBE QUERY NOTIFICATIONS)
  3. Der Command bei der Initialisierung der SqlDependency muss bestimmten Voraussetzungen entsprechen (also z.B. nicht SELECT * FROM ....)

Alle diese Einschränkungen kann man ausführlich nochmal auf CodeProject nachlesen.

Während der Ausführung ist es dann wichtig zu wissen, dass der Event ein One-Shot ist - heißt er wird nur bei der ersten Änderung ausgelöst. Deshalb muss dann wenn die Daten neu geladen werden auch der Event wieder registriert werden.

Wenn man das alles berücksichtigt, dann kann man damit aber recht coole Sachen machen. Und man ist nicht auf die WPF beschränkt. Mit der Hilfe von OWIN und SignalR auch weitreichender über Datenänderungen informieren.

Sascha Lorenz: SQL Server MVP (Microsoft Most Valuable Professional)

Es ist tatsächlich passiert. So sieht sie also aus, die Mail von Microsoft, welche einem zum MVP macht.

image

Diese Auszeichnung ist eine ganz besondere Ehre für mich. Vielen Dank an Alle die mich vorgeschlagen und in das Programm “geschubst” haben! ;-)

Seit nun fast genau 10 Jahren organisiere ich Usergroup Treffen (Im September das 100. Treffen in Hamburg), spreche auf Veranstaltungen und erstellen Online-Inhalte wie Blogposts und Webcasts. Meinen neuen Status als MVP und die damit verbundenen Möglichkeiten möchte ich nutzen, um der SQL Server Community noch mehr zur Seite zu stehen mit meinen Ideen und Erfahrungen aus dem SQL Server Alltag.

Hier ein wenig Erläuterung direkt von Microsoft zum MVP Award.

Robert Panther: Neues Buch zur Optimierung von SQL-Abfragen

Bei entwickler.press ist gerade mein neues Buch – oder genauer Büchlein – zum Thema Optimierung von SQL-Abfragen erschienen. Das Buch erklärt in kompakter Form am Beispiel von Microsoft SQL Server, wie man performante SQL-Abfragen formuliert. Dabei wird auch auf die verschiedenen Möglichkeiten hingewiesen, die verschiedene Versionen des Produkts (bis hin zu SQL Server 2014) bieten. Viele Optimierungsansätze sind jedoch auch auf SQL-basierte Datenbank-Management-Systeme anderer Hersteller anwendbar. Auch wenn der Text sich primär an Anwendungs- und Datenbankentwickler richtet, dürfte der Inhalt auch für Administratoren interessant sein, zumal die Praxisbeispiele auch diesen Teil mit abdecken.
Das Buch ist bewusst kompakt gehalten und bildet eine auf SQL-Optimierung konzentrierte (aber auch aktualisierte und um neue Inhalte ergänzte) Teilausgabe meines – ebenfalls bei entwickler.press erschienenen – SQL Performance-Ratgebers.

SQL-Abfragen optimieren

Robert Panther
SQL-Abfragen optimieren
Was Entwickler über Performance wissen müssen
entwickler.press
176 Seiten (Softcover)
ISBN: 978-3-86802-123-3
Preis: € 12,90 inkl. MwSt.

Alternativ auch als eBook erhältlich:

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

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

Weitere Infos:


Sascha Lorenz: SQL Server PowerShell Vortrag vom SQLSaturday #313

Hier noch mal alle Folien zu meinem Einführungsvortrag PowerShell für SQL Server Administratoren.

 
 

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