Bernd Jungbluth: Seminar - Datenbankentwicklung mit SQL Server

Heute möchte mein neues Seminar “Datenbankentwicklung mit SQL Server” vorstellen.

Inhalt dieses Seminars ist die Datenbankmodellierung wie auch das Erstellen und Verwenden von Gespeicherten Prozeduren, Sichten, Funktionen und Triggern.

Zur Datenbankentwicklung gehört neben dem Modellieren der Tabellen auch das Abbilden von Geschäftslogik. Zum Teil erfolgt dies bereits mit den Tabellen und deren Regeln zur Datenintegrität. Komplexere Logik lässt sich im SQL Server mit T-SQL in Gespeicherten Prozeduren, Sichten, Funktionen und Triggern realisieren.

Das Seminar findet am 24. März 2015 im Hotel „Best Western Premier Bellevue Rheinhotel“ in Boppard am Rhein statt.

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

Die Teilnehmerzahl ist auf 8 Personen begrenzt. Aktuell gibt es noch 4 freie Plätze.
Der Anmeldeschluss ist am 20. Februar 2015.

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

Falk Krahl: Neues Update für SQL Server 2012 SP1

Auch für den SQL Server 2012 mit Servicepack 1 ist ein neues Update erschienen. Es handelt sich um das kumulative Update 14. Es kann unter folgendem Link angefordert werden:
SQL Server 2012 SP1 CU14

Torsten Schuessler: #CU package 4 for SQL Server 2012 SP2

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

Cumulative update package 4 for SQL Server 2012 SP2

I wish you a nice day,
tosc

 

Torsten Schuessler

Falk Krahl: Neues Update für SQL Server 2012 SP2

Soeben ist für den SQL Server 2012 mit Servicepack 2 ein neues Update erschienen. Es handelt sich um das kumulative Update 04. Es kann unter folgendem Link angefordert werden:
SQL Server 2012 SP2 CU04

Christoph Muthmann: Henry Habermacher

Leider haben wir wieder ein hochgeschätztes Mitglied der SQL Server Community verloren. Vor einem Monat ist der Access-MVP Henry Habermacher verstorben.

Full story »

Christoph Muthmann: SP4 auf SQL Server 2005 Express anwenden

Warum ich noch über solche alten Versionen schreibe, wird sich der ein oder andere fragen. Ganz einfach, dies ist eine Voraussetzung für den Upgrade auf SQL Server 2014 Express.

Full story »

Uwe Ricken: SQL Server Konferenz in Darmstadt

Liebe SQL Gemeinde; es ist mal wieder soweit. Auch 2015 wird die im letzten Jahr ins Leben gerufene Konferenz “SQL Server Konferenz” wieder in Darmstadt ihre Tore öffnen. Ähnlich wie in meinem Artikel über den PASS Summit 2014 möchte ich mit diesem Artikel die Vor- und Nachteile eines Besuchs der SQL Server Konferenz aus meiner persönlichen Sicht beschreiben.

Was ist die SQL Server Konferenz?

Die SQL Server Konferenz fand im Jahr 2014 zum ersten Mal im darmstadtium mitten im Herzen von Darmstadt statt. Die SQL Server Konferenz 2014 war ein so großer Erfolg, dass sich die PASS Deutschland e. V. in Zusammenarbeit mit Microsoft Deutschland als Organisatoren erneut für die Durchführung der SQL Konferenz für das Jahr 2015 entschlossen hat.

SQLKonferenz

Deutsche und internationale Experten präsentieren, diskutieren und erläutern Themen wie SQL Server vNext, Self-Service BI, Hochverfügbarkeit, Big Data, Continuous Integration, In-Memory Computing, Data Stewardship, Data Vault und Modern Data Warehouse, Machine Learning, das Internet der Dinge, Enterprise Information Management und viele weitere spannende Themen. In vier parallelen Tracks werden alle Neuheiten rund um den Microsoft SQL Server und die Microsoft Datenplattform von erfahrenen und bekannten Sprechern aus dem In- und Ausland angeboten.

Die Location

Die SQL Server Konferenz findet auf mehr als 18.000 qm im darmstadtium im Herzen von Darmstadt statt und ist sowohl mit öffentlichen als auch individuellen Verkehrsmitteln schnell und bequem zu erreichen. Ressourcenschonende Nachhaltigkeit und eine in Deutschland einmalige IT Infrastruktur sind neben der fantastischen Lage mitten im Rhein-Main-Gebiet Merkmale, die für das darmstadtium zählen.

Die Sprecher

So wichtig für jede Konferenz die Besucher, deren Interessen und die Themen sind; es müssen Leute da sein, die den kompliziertesten Sachverhalt in einfache und verständliche Sprache übersetzen – die Sprecher. Jede Konferenz lebt von ihren Sprechern. Ich kenne zwar nicht alle Sprecher persönlich ABER ... wenn man sich mal die Liste der Sprecher hier anschaut, wird man schnell erkennen, dass man es mit hochgradig spezialisierten Experten zu tun hat, die ihr Metier in der Tiefe verstehen. Neben nationalen Sprechern sind dieses Mal sehr viele internationale Sprecher auf der SQL Server Konferenz vertreten, die auf der ganzen Welt für ihre Kenntnisse und ihr Engagement für die Community bekannt sind. Bevor ich auf einige Sprecher im Detail eingehe, möchte ich ausdrücklich darauf hinweisen, dass die Sprecher der Hauptkonferenz sich alle KOSTENLOS engagieren. Anfahrt, Hotel, Vorbereitungen – es gibt keinen Cent für die Tätigkeiten. Die Sprecher machen es aus Passion! Ich möchte hier nur einige wenige hervorheben, die ich sehr interessant finde (das soll aber keine Wertung darstellen!).

Kalen Delaney

Kalen Delaney dürfte allen DBAs durch ihre “Bibel” SQL Server Internals bekannt sein.  Ich hatte das Vergüngen, Kalen im Jahre 2013 auf der SQLRally in Stockholm persönlich kennen zu lernen. Ich kann jedem nur empfehlen, ruhig mal ein paar Worte mit ihr zu wechseln. Dass Ihr – neben Paul Randal – die Storage Engine bestens bekannt ist, dürfte jeder Leser ihrer Bücher wissen. Ich habe mich mit ihren Büchern auf meine MCM Prüfungen vorbereitet.

Thomas LaRock

Thomas war bereits letztes Jahr auf der SQL Server Konferenz und hat wohl eindrucksvoll bewiesen, dass ihm Jägermeister nicht unbekannt ist. Scherz beiseite; Thomas ist der Präsident der PASS und ebenfalls einer von ca. 200 MCM weltweit. Ich habe ihn persönlich auf der SQL Server Konferenz im letzten Jahr kennengelernt; wie Kalen Delaney ein sehr erfahrener SQL Server Experte, der es mag und versteht, sein Wissen über Microsoft SQL Server in die Community zu tragen.

Steve Jones

Wer kennt nicht DIE Anlaufstelle für SQL Server Themen im Internet: http://www.sqlservercentral.com ist für jeden DBA / BI Experten immer wieder eine Anlaufstelle, wenn es darum geht, Probleme zu lösen. Es gibt eigentlich nichts, was dort nicht zu finden ist. Steve Jones ist der Betreiber der Webseite, die mittlerweile zu Red Gate gehört. Steve habe ich bei einem Vortrag in Seattle auf dem PASS Summit 2014 gehört und gesehen. Fantastisch!

Régis Baccaro

Régis Baccaro kenne ich von vielen SQL Saturday Events in Europa, die ich in 2014 als Sprecher besucht habe. Es gibt eigentlich keinen SQL Saturday, auf dem Régis nicht schon eindrucksvoll sein Wissen in Bezug auf BI-Themen von Microsoft SQL Server unter Beweis gestellt hat. Ein Franzose, der in Dänemark lebt und mit mir eine Passion teilt – gutes Essen.

Boris Hristov

Boris Hristov ist für mich das Paradebeispiel für Engagement für die Community. Boris kommt aus Bulgarien. Über seine Hangouts bin ich mit Boris zum ersten Mal in Kontakt gekommen; seitdem verbindet uns – wie übrigens alle Mitglieder von #sqlcommunity – eine Freundschaft, die – wie bei Régis auch, immer wieder “refreshed” wird, wenn wir uns auf den zahlreichen SQL Saturday Veranstaltungen treffen. Boris Hristov scheut keine Kosten, um sein exzellentes Wissen in Europa in die Community zu tragen.

Oliver Engels / Tillmann Eitelberg

Jedes PASS Mitglied in Deutschland kennt natürlich unseren Vorstand. Mich verbindet mit Oliver und Tillmann eine Freundschaft, die ich sehr zu schätzen weis. Den beiden macht so leicht niemand was in Sachen BI und SSIS vor. Obwohl ich nur einmal in einer Session der Beiden gesessen bin, war ich dennoch begeistert von den Möglichkeiten von Power BI – und das. obwohl ich kein Freund von “Clicky Bunti” (Insider) bin.

Marcel Franke

Marcel Franke habe ich das erste Mal in Stockholm auf der SQL Rally kennen gelernt. Echt beeindruckend, was Marcel in Sachen BI, “Big Data” und “Data Analytics” drauf hat – sicherlich ein sehenswerter Vortrag für alle Besucher, die viel mit diesen Themen in Berührung kommen.

Patrick Heyde

Seit dem SQL Server Bootcamp im Dezember 2014 ist Patrick für mich nur noch “Mr. Azure”. Patrick kennt als Mitarbeiter von Microsoft die Azure Welt, wie kein Zweiter. Seine Vorträge gefallen mir vor allen Dingen deswegen, da Patrick es in seiner ruhigen Art sehr gut versteht, die Komplexität von Azure für alle Zuhörer verständlich zu vermitteln.

Niko Neugebauer

Niko Neugebauer ist für mich DER Experte für Themen rund um Columnstore Indexes. Es gibt eigentlich nichts, was Niko zu diesem Thema nicht kennt. Sein Wissen ist phänomenal und mehr als 40 Blogeinträge auf seinem Blog beschäftigen sich ausschließlich mit dem Thema Columnstore Indexe; zu lesen unter http://www.nikoport.com. Ein Besuch einer Session von Niko kann ich nur empfehlen.

Natürlich sind noch viele viele andere Sprecher “am Start”, die sich in Europa für die Community engagieren. Sei es Allen Mitchel (Einer der vielen Organisatoren von http://www.sqlbits.com), Milos Radivojevic, Cathrine Wilhelmsen, ...). Ein Besuch der SQL Server Konferenz ist sicherlich lohnenswert.

Die Themen

Bei der Auswahl der Themen haben sich die Organisatoren es sich sicherlich nicht leicht gemacht. Ich weiß nicht, wie viele Sessions eingereicht wurden – aber ich bin davon überzeugt, dass es weitaus mehr waren, als in den drei Tagen vorgetragen werden können. Ich bin sicher, dass unter den vielen Themen zu

  • Administration (DBA),
  • Business Intelligence (BI),
  • Applikationsentwicklung (DEV)
  • Big Data & Information Management (BD)

für jedes Interessensgebiet was dabei ist. Ich bin gespannt auf die Neuigkeiten im Bereich BI und freue mich auf die interessanten Sessions.

Warum zur SQL Server Konferenz?

Die SQL Server Konferenz ist eine Veranstaltung in Deutschland, die es in dieser Form so nicht gibt. In drei Tagen (am ersten Tag werden PreCons veranstaltet) werden viele Themen rund um den Microsoft SQL Server behandelt. Wer täglich mit Microsoft SQL Server in Themen rund um DBA, DEV, BI involviert ist, sollte sich die Zeit nehmen, die SQL Server Konferenz im darmstadtium in Darmstadt zu besuchen.

Ein – aus meiner Sicht – ganz wichtiges Thema JEDER Konferenz ist das Networking. Als ich im Jahre 2013 mit dem Besuch von Konferenzen begonnen habe, war ich natürlich noch etwas “schüchtern” in Bezug auf “große Namen”. Nie habe ich so falsch gelegen, wie mit der These der arroganten “Superstars”. Alles sehr nette Leute, die es sehr freut, wenn man sich mit Ihnen über UNSER Lieblingsthema Microsoft SQL Server austauscht. Aber man darf sich auch sehr gerne über andere Dinge unterhalten.

Ich habe über die Konferenzen viele neue Menschen getroffen; einige davon bezeichne ich als Freunde. Das Networking gibt die Möglichkeit, auch abseits von besuchten Sessions mit den Teilnehmern über das eine oder andere Problem zu sprechen – erstaunlich, wie schnell man manchmal eine Lösung zu einem Problem findet, über das man schon mehrere Tage gebrütet hat.

Kosten

Bei “Kosten” hört der Spaß in der Regel auf. Die Kosten für die SQL Server Konferenz halte ich persönlich für angemessen. Für die Teilnahme an der SQL Server Konferenz gibt es drei Kostenmodelle (Preise EXKLUSIVE MwSt.).

  • Besuch der PreCon: 349,00 €
  • Hauptkonferenz vom 04.02.2015 bis 05.02.2015: 699,00 €
  • Komplettpaket (PreCon und Hauptkonferenz): 949,00 €

Hört sich erst mal teuer an; ist es aber aus meiner Sicht für die Leistungen nicht. Als Mitglied in der PASS e.V. (kostenlos) gibt es noch einen Rabatt. Einen gesonderten Rabatt gibt es, wenn mehr als 2 Personen eines Unternehmens an der SQL Server Konferenz teilnehmen möchten. Weiter Informationen zu den Rabatten sind über die Emailadresse info@event-team.com zu erfahren.

Aussteller

Die IT ist im Wandel und das eigentlich stetig. Es ist auch für den SQL Experten wichtig, dass er “auf der Höhe der Zeit” bleibt. Nicht nur die neuen Versionen von Microsoft SQL Server sollten im Fokus eines jeden Besuchers stehen sondern auch, was sich auf dem Markt der Tools und der Hardware tut. Allein die Änderungen im Bereich Storage in den letzten Jahren sind gewaltig. Wie monitore ich meine SQL Server? Welche Hardware ist für meine SQL Server Anwendungen die richtige Wahl? All diese Fragen können in persönlichen Gesprächen mit den Ausstellern in Ruhe besprochen werden. Ein Besuch auf den Ausstellungsflächen der Sponsoren ist immer sinnvoll – viel Spaß beim “Stöbern”.

Was man verbessern könnte

Tatsächlich gefällt mir die SQL Server Konferenz so, wie sie ist, SEHR GUT. Lediglich ein Punkt ist aus meiner Sicht ein “must have” – Videoaufnahmen aller Sessions! Mir persönlich fällt es schwer, in manchen Slots einen “Favoriten” auszuwählen. Es sind alles exzellente Themen, die in den einzelnen Sessions behandelt werden aber ich kann mich nur für eine entscheiden.

Wie gerne würde ich mir die Session von Andreas Wolter über “In Memory OLTP” anhören; schade nur, dass zeitgleich meine eigene Session läuft. Ich glaube, dass es vielen Besuchern so geht, wenn mehrere – für den Einzelnen – interessante Themen zeitgleich behandelt werden.

Wenn es sehr komplexe Themen sind (z. B. der Vortrag von Niko Neugebauer), dann freut man sich, dass man die Session nachträglich noch einmal in Ruhe vor dem eigenen Computer anschauen kann und die Ruhe und die Zeit hat, die gezeigten Beispiele am heimischen Computer noch einmal nachzuspielen. Ein nicht zu unterschätzender Vorteil!

Ich habe auf dem PASS Summit 2014 gesehen, wie professionell dort JEDE Session aufgenommen wurde. Jeder Teilnehmer des PASS Summit hat so die Möglichkeit, nachträglich die für ihn interessanten Sessions noch einmal anzuschauen. Sollte jemand Interesse an dem Material haben, der nicht auf dem SUMMIT war, so hat er die Möglichkeit, gegen einen kleinen Obolus einen USB-Stick mit dem ganzen Material zu erwerben. Ich glaube, das wäre auch ein sehr gutes Modell für die SQL Server Konferenz – aber das ist Jammern auf sehr hohem Niveau. Es ist ja auch erst die zweite Konferenz dieser Art – es muss jedes Jahr eine kleine Verbesserung geben...

Mein Thema

Ich selbst bin ebenfalls mit einer Session auf der SQL Server Konferenz vertreten. Ich arbeite als SQL Server Architekt und SQL Server Berater in vielen Unternehmen in Deutschland und Europa. Bei den vielen hundert Einzeleinsätzen habe ich es recht häufig mit Performance-Problemen zu tun. Nicht selten sind es Architekturprobleme aber über 40% meiner Einsätze resultieren in dem Ergebnis, dass Indexe nicht richtig genutzt werden oder aber Indexe grundsätzlich fehlen. Mein Thema behandelt genau diese Szenarien aus den vielen Kundeneinsätzen, über die ich auch teilweise schon geblogged habe. “Indexing - alltägliche Performanceprobleme und Lösungen” wird mein Thema sein. Ich werde in 60 Minuten 6 – 10 gängige Probleme und deren Lösungen aufzeigen. Ich verspreche, dass es nicht langweilig wird, da die Session zu fast 90% mit Demos belegt ist. Es versteht sich von selbst, dass ich für jeden Interessierten sowohl vor als auch nach meiner Session sehr gerne für weitere Fragen rund um Microsoft SQL Server zur Verfügung stehe.

Ich freue mich auf einen regen Gedanken- und Wissensaustausch mit allen Besuchern der SQL Server Konferenz im darmstadtium in Darmstadt und wünsche allen Lesern und Besuchern viel Spaß!

Nachtrag

Mit “unserem” Vorsitzenden der PASS e. V. – Oliver Engels - habe ich auf dem SQL Boot Camp 2014 in Seeheim-Jugenheim ein kurzes Gespräch über die SQL Server Konferenz generell und im Speziellen zu meinem Thema geführt. Vielleicht macht das Gespräch ja Lust auf mehr.

Ich freue mich auf die SQL Server Konferenz! Ich freue mich sehr auf die Sprecher und ihre Themen aber am meisten freue ich mich auf die Fachbesucher und SQL Server Experten, mit denen ich über mein Lieblingsprodukt fachsimpeln kann. Es ist schön, Teil einer so interessanten und vielfältigen Community zu sein. Jede Konferenz zu Microsoft SQL Server in Deutschland ist ein Gewinn – ich werde alles dazu beitragen, dass die SQL Server Konferenz im Februar ein Gewinn für die Community wird. Ich hoffe sehr, dass auch die Besucher der Konferenz die SQL Server Konferenz wert schätzen und eine rege Teilnahme zu verzeichnen ist.

Bis zum 03.02.2015 in Darmstadt im darmstadtium auf der SQL Server Konferenz.

Herzlichen Dank fürs Lesen!

Uwe Ricken: Clustered Key – variable oder feste Länge

In LinkedIn wurde eine interessante Frage diskutiert, die in ihrem Ursprung zwar nicht unmittelbar mit dem Titel dieses Artikels zusammenhängt, mich aber auf Grund der abgebildeten Datenstruktur zu der Fragestellung dieses Artikels anregte. Dieser Artikel beschreibt, warum ein Clustered Index nach Möglichkeit immer einen Datentypen mit fester Länge besitzen sollte.

Der Clustered Key ist das Ordnungskriterium für einen Clustered Index. Der Schlüssel kann aus einem oder mehreren Attributen einer Tabelle bestehen. Bei der Definition des Index können bis zu 16 Spalten in einem einzigen zusammengesetzten Indexschlüssel kombiniert werden. Die maximal zulässige Größe der Werte des zusammengesetzten Index beträgt 900 Byte. Die Datentypen ntext, text, varchar(max), nvarchar(max), varbinary(max), xml oder image dürfen nicht als Schlüsselspalten für einen Index angegeben werden.

Struktur eines Datensatzes

Um zu verstehen, welche Nachteile ein Index mit variabler Datenlänge besitzt, muss man wissen, wie ein Datensatz in Microsoft SQL Server “strukturiert” ist. Der nachfolgende Code erstellt eine Tabelle mit einem Clustered Index auf “Customer_No”.

IF OBJECT_ID('dbo.Customer', 'U') IS NOT NULL
    DROP TABLE dbo.Customer;
    GO
 
CREATE TABLE dbo.Customer
(
    Customer_No      CHAR(10)    NOT NULL,
    Customer_Name    CHAR(1000)  NOT NULL,
 
    CONSTRAINT pk_Customer_No PRIMARY KEY CLUSTERED (Customer_No)
);
GO

Die Tabelle wird anschließend mit 1.000 Testdatensätzen für weitere Analysen der Datenstruktur befüllt. Um die Struktur eines Datensatzes zu analysieren, werden Informationen zur gespeicherten Position benötigt.



SELECT sys.fn_PhysLocFormatter(%%physloc%%), * FROM dbo.Customer;
ROWSET_01

Auf Datenseite 156 werden sieben Datensätze gespeichert. Um die Datenseite – und damit die Struktur der Datensätze – einzusehen, wird mit DBCC PAGE der Inhalt der Datenseite untersucht. Wichtig bei der Verwendung von DBCC PAGE ist die vorherige Aktivierung des Traceflags 3604, um die Ausgabe vom Fehlerprotokoll in SQL Server Management Studio umzuleiten.



DBCC TRACEON (3604);
DBCC PAGE ('demo_db', 1, 156, 3);
DBCC_PAGE_01

In der Abbildung wird der erste Datensatz der Datenseite angezeigt. Der blau gerahmte Bereich repräsentiert die tatsächlich auf der Datenseite gespeicherten Informationen. Es ist erkennbar, dass der Datensatz eine Länge von 1.017 Bytes besitzt; berechnet man jedoch die Länge des Datensatzes basierend auf der Tabellendefinition, wird man bemerken, dass die Länge eigentlich nur 1.010 Bytes sein müssten. Die zusätzlichen Bytes werden für die Definition der Metadaten eines Datensatzes benötigt. Die Anzahl der zusätzlichen Informationen (Overhead) ist von den Datentypen einer Tabelle abhängig.


Datensatz-Metadaten


Die ersten zwei Bytes (0x10 00) speichern Informationen über die Eigenschaften eines Datensatzes. Da es sich um einen “gewöhnlichen” Datensatz handelt, spricht man von einem “PRIMARY RECORD”. Die nächsten zwei Bytes (0xf6 03) bestimmen das Offset zur Information über die Anzahl der Spalten in einem Datensatz. Zwischen dem Header (4 Bytes) und dem Offset befinden sich die Daten aller Attribute mit festen Datentypen.


Fixed Length Bereich


Der Hexadezimalwert 0xf6 03 ergibt umgerechnet den Dezimalwert 1.014. 4 Bytes für den Header + 10 Bytes für das Attribut [Customer_No] + 1.000 Bytes für das Attribut [Customer_Name] ergeben den Wert von 1.014 Bytes.


DBCC_PAGE_02


Die obige Abbildung zeigt den Datenbereich ab dem Byte 1.014 (rot).


Anzahl der Attribute (Spalten)


Die nächsten zwei Bytes (0x02 00) geben Aufschluss über die Anzahl der Spalten.


NULL-Bitmap


Nach den Informationen über die Struktur des Datensatzes folgen die Informationen über über Attribute, die NULL Werte enthalten dürfen (NULL Bitmap). Hierbei gilt, dass pro 8 Attribute einer Tabelle jeweils 1 Byte belegt wird. Erlaubt eine Spalte einen NULL Wert so wird das entsprechende Bit gesetzt. Da für das obige Beispiel kein Attribut einen NULL Wert zulässt, ist dieser Wert 0 (0x00).


Variable Length Bereich


Das Ende der Datensatzstruktur ist der Datenbereich für Attribute mit variabler Länge. Spalten mit variabler Länge sind mit einem deutlich höheren Aufwand von Microsoft SQL Server zu verwalten; ein Datensatz wird – bedingt durch die zusätzlichen Informationen – deutlich größer (siehe nächstes Beispiel). Für JEDES Attribut mit einem variablen Datentypen werden zwei Bytes für das Offset gespeichert, an dem der Wert für das Attribut endet. Erst zum Schluss werden die Daten selbst gespeichert. Das obige Beispiel besitzt KEINE Attribute mit variablen Datentypen; Informationen über variable Datentypen müssen nicht gespeichert werden.


Clustered Index mit variablem Datentyp


Um die Datenstruktur eines Clustered Index mit variabler Datenmenge zu untersuchen, werden die Daten der bestehenden Tabelle [dbo].[Customer] in eine neue Tabelle übertragen.



IF OBJECT_ID('dbo.Customer_Variable', 'U') IS NOT NULL
    DROP TABLE dbo.Customer_Variable;
    GO
 
CREATE TABLE dbo.Customer_Variable
(
    Customer_No    VARCHAR(10)  NOT NULL,
    Customer_Name  CHAR(1000)   NOT NULL,
 
    CONSTRAINT pk_Customer_Variable_Customer_No PRIMARY KEY CLUSTERED (Customer_No)
);
GO
 
-- Übertragung aller Daten aus dbo.Customer
INSERT INTO dbo.Customer_Variable WITH (TABLOCK)
SELECT * FROM dbo.Customer;
GO

Mit der folgenden Abfrage werden aus jeder Tabelle der jeweils erste Datensatz ausgegeben. Zusätzlich werden Informationen zur Datenseite ausgegeben, auf der die Datensätze gespeichert wurden.



SELECT TOP 1 'Fixed length' AS ClusterType, sys.fn_PhysLocFormatter(%%physloc%%), * FROM dbo.Customer
UNION ALL
SELECT TOP 1 'Variable length' AS ClusterType, sys.fn_PhysLocFormatter(%%physloc%%), * FROM dbo.Customer_Variable;
GO
ROWSET_02

Der zu untersuchende Datensatz befindet sich auf Datenseite 1.144 und hat folgende Struktur:


DBCC_PAGE_03


Der “identische” Datensatz ist um 4 Bytes “gewachsen”. Statt 1.017 Bytes benötigt der Datensatz 4 weitere Bytes. Diese vier weiteren Bytes resultieren aus der geänderten Datensatzstruktur. Die nächste Abbildung zeigt den betroffenen Ausschnitt vergrößert an.


DBCC_PAGE_04


Die Abbildung zeigt das Ende der Datensatzstruktur. Die ersten zwei markierten Bytes repräsentieren die Anzahl der Spalten des Datensatzes. Das nächste Byte repräsentiert das NULL Bitmap. Der anschließende Bereich (2 Bytes) ist eine der Ursachen für das Wachstum der Datensatzgröße.


Der Hexadezimalwert 0x01 00 bestimmt die Anzahl der Spalten mit variablen Datensätzen. Die nächsten zwei Bytes (0xfd 03) bestimmen das Offset für das Ende des Datenbereichs für die erste Spalte mit variabler Datenlänge. Da keine weiteren Spalten vorhanden sind, beginnen unmittelbar im Anschluss die Daten des Clustered Keys.


Wer mehr Informationen zu den Strukturen eines Datensatzes wünscht, dem sei natürlich das Buch “Microsoft SQL Server 2012 Internals” von Kalen Delaney (b | t) empfohlen. Alternativ gibt es zu diesem Thema auch einen sehr guten Artikel von Paul Randal (b | t) unter dem Titel “Inside the storage engine: Structure of a record” .


Overhead


Bedingt durch die Struktur eines Datensatzes wächst die Datenlänge um maximal vier weitere Bytes. Hat die Tabelle bereits Attribute mit variabler Datenlänge gehabt, sind es lediglich zwei weitere Bytes für das Offset.


Neben der Vergrößerung des Datensatzes haben variable Datentypen noch andere Nachteile als Clustered Index:



  • Der Overhead von maximal 4 Bytes ist – auf den einzelnen Datensatz reduziert – unerheblich. Die zusätzlichen 4 Bytes können aber dazu führen, dass weniger Datensätze auf einer Datenseite gespeichert werden können.
  • CPU Zeit für die Evaluierung des Datenwertes, da erst das Offset für die variablen Bereiche und anschließend das Offset für den Datenbereich errechnet werden muss. Es ist sicherlich kein nennenswerter Zeitverlust aber – wie in vielen Dingen – ist die Menge der Datensätze für den Overhead entscheidend.
  • Das Schlüsselattribut eines Clustered Index wird in jedem non clustered Index gespeichert. Somit verteilt sich der Overhead auch auf non clustered Indexe.

Herzlichen Dank fürs Lesen!


Hinweis


Wer Kalen Delaney live erleben möchte, dem sei die SQL Konferenz in Darmstadt vom 03.02.2015 – 05.02.2015 sehr ans Herz gelegt. Die Agenda der SQL Konferenz liest sich wie das Who ist Who der nationalen und internationalen SQL Community.


Ich bin ebenfalls als Sprecher mit einem interessanten Thema “Indexing - alltägliche Performanceprobleme und Lösungenvertreten.


SQLKonferenz


Anmerkung


Dankenswerter Weise wurde ich durch einen Kommentar des von mir sehr geschätzten Kollegen Torsten Strauss darauf aufmerksam gemacht, dass der Artikel einige Voraussetzungen “unterschlagen” hat und somit zu Fehlinterpretationen führen kann.


Die Grundvoraussetzung zur Idee zu diesem Artikel ist, dass immer mit “gleichen” Karten gespielt wird – soll heißen, dass unabhängig vom Datentypen IMMER der gleiche Datenbestand verwendet wird. Ein fairer Vergleich wäre sonst nicht möglich! Spreche ich im ersten Beispiel vom Datentypen CHAR mit einer Länge von 10 Bytes, setzt das Beispiel mit dem Datentypen VARCHAR und einer maximalen Zeichenlänge von 10 Bytes voraus, dass ebenfalls 10 Zeichen pro Datensatz im Clustered Index gespeichert werden!

Christoph Müller-Spengler: How to tune ISV apps – Capture the workload

This is the first article of my blog post series How to tune ISV apps
In today’s blog post i will describe the steps that you have to do in order to capture a typical workload during business hours.

Importance of the baseline.

Why is it important to capture a workload?
Because you need something that you can compare your improvements against. If you have no baseline, you cannot guarantee that the improvements you have performed against your database are real improvements or just drawbacks.

The tools you need to capture the workload are:

  • SQL Server Profiler
  • SQL Server Management Studio

Don’t worry – SQL Server Profiler will not be used to capture the workload on the production server, it is just the tool to create the right “setup”.

Create the Tracefile

Start SQL Server Profiler, whether from the production server or another server does not matter, because we will not bring the production server to it’s knees ;-)
Choose “File -> New Trace…”
CW_Profiler1

Connect to your SQL Server instance using the credentials that you have. (You need at least the “ALTER TRACE” permission for you SQL Server Login.)
CW_Profiler2

In the next wizard the only important thing is the template that you choose, this has to be “TSQL_Replay”. With that template you are able to really replay one and the same workload against many different versions of our optimized database. Everytime you do that, you can check wether your changes have been good ones or bad ones.
CW_Profiler3

Just click “Run”. The grid with the incoming requests appears.
Click immediately on the red square button “Stop” in order to stop the trace.
CW_Profiler41

Now you have to export this new “Script Trace Definition” to a T-SQL file. Just save it anywhere, you can later copy’n’paste it to your production server, if you did not already start the SQL Server Profiler on that very server.
CW_Profiler5

Alter the Trace Definition File

Now you have to open the file and make some settings and tweak it a little bit.

  • Set an appropriate file size. Once you run this Trace Definition File in order to create the real trace files on file system, the trace will generate a new file each time it hits the threshold defined at the parameter “@maxfilesize”. The default for this value is 5 what means 5 MB. Personally i set it to 50 MB:
    set @maxfilesize = 50
    
  • Specify the location where the trace files should be written.
    !Important!: Please make sure that the amount of disk space you need is in place, because the amount of data you collect depends on the workload that you really have got on the production server and also on the duration that you want to capture.
    I captured a workload on a OLTP Helpdesk Trouble Ticket System between 1:30pm and 15:00pm and collected 4 GB of data.
    So please edit the following line, so that it fits your needs.
    You have to change two things in this line:

    • Change the value of the second parameter from “0” to “2” in order to activate the rollover option.
      Otherwise you will find the following error in SQL Server Errorlog:
      Trace ID ‘2’ was stopped because of an error. Cause: Stopping the trace because the current trace file is full and the rollover option is not specified.. Restart the trace after correcting the problem.

    • At the third parameter specify a proper path and filename.
    exec @rc = sp_trace_create @TraceID output, 2, N'D:\CapturedTraces\Trace', @maxfilesize, NULL, 200
    

    As mentioned in the notes above the line, the “.trc” extension will be appended to the filename automatically.

  • At the end of the file you will find a line with
    exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - d7cd774c-ec84-488e-afab-2211df8a6ead'
    

    If the SQL Server Instance you want to trace, hosts more than one database, you have to apply another filter like this, where you have to replace “YourDatabaseNameHere”:

    exec sp_trace_setfilter @TraceID, 35, 0, 6, N'YourDatabaseNameHere'
    
  • Now back to the very top of the file you have to add TSQL code to backup you database before executing the capturing trace like this:
    BACKUP DATABASE [YourDatabaseNameHere]
    TO DISK = N'E:\MSSQL10_50.MSSQLSERVER\MSSQL\YourDatabaseNameHere_4_Replay.bak'
    WITH
    	COPY_ONLY
    	, NOFORMAT
    	, NOINIT
    	, NAME = N'YourDatabaseNameHere-Full Database Backup'
    	, SKIP
    	, COMPRESSION
    	, STATS = 1
    GO
    

    Once again, just replace “YourDatabaseNameHere” with your real database name.
    !Important!: Please make sure that you have set the “COPY_ONLY” option in order to leave the backup chain unbroken. If your version of SQL Server supports backup compression, please also add “COMPRESSION”.

    Execute the Trace

    Once you have finished tweaking your “Trace Definition File”, open your SQL Server Management Studio, connect to your SQL Server instance, open a “New Query” and paste the code or just open the Trace Definition File.

    Now: execute the script, pressing “F5″ or clicking “! Execute”.

    In the result grid, you will see
    CW_Profiler6
    This is the ID of your very trace that you started. Keep this ID in mind or elsewhere for later use.

    If you have a look at the file system, you will see the created file(s) in your specified folder. Once the 50 MB have been reached, SQL Server rolls over to the next file, named YourFileNameHere_1.trc
    CW_Profiler7

    Stop the Trace

    After your defined period of time that you wanted to capture, you have to stop the SQL Trace. Here comes the ID of the trace into play as you have to pass it to the stored procedure that you execute as the first parameter.

    exec sp_trace_setstatus 2, 0
    

    After that, just delete the Trace so that there are no more processes living in SQL Server.

    exec sp_trace_setstatus 2, 2
    

    Save the data

    Now that you successfully captured the workload, just move the backup and all of your trace files away from your production server to a location that is safe.

    Summary

    In today’s blog post you have learned how to make use of SQL Server Profiler and SQL Server Management Studio to capture a workload on your production server in order to be able to replay it whenever you want against a specified backup that you made beforehand.

    I hope you enjoyed reading this blog post and will return for the next one when i will cover the topic “Diagnosis and Doing with “sp_BlitzIndex™””

    Thanks,
    Christoph


    Christoph Muthmann: Early Bird weiterhin für PASS Mitglieder verfügbar

    Deutsche SQL Server Konferenz 2015 - Early Bird Tickets weiterhin für PASS e.V. Mitglieder verfügbar!

    Full story »

    Marcel Franke: 2014 in review

    The WordPress.com stats helper prepared a 2014 annual report for my blog.

    Here’s an excerpt:

    The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 91,000 times in 2014. If it were an exhibit at the Louvre Museum, it would take about 4 days for that many people to see it.

    Click here to see the complete report.


    Filed under: Uncategorized

    Uwe Ricken: INTERN: Änderung der Größe eines Datenfeldes

    Auf Grund zahlreicher Emails unter MVP-Kollegen bin ich der Frage nachgegangen, wie sich die Änderung der Größe eines Datenfeldes mit fester Größe auf den Datenkonsum auswirkt. Ein weiterer sehr interessanter Aspekt war der Frage, wie sich die Änderung der Datengröße auf das Transaktionsvolumen auswirkt. Der nachfolgende Artikel geht diesen Fragen nach und beleuchtet die internen Prozesse des Vorgangs.

    Wie häufig kommt es vor, dass das ursprünglich entwickelte Design der Metadaten durch die aufkommenden Datenmengen obsolet wird? Letztendlich bleibt dem DBA die Möglichkeit, die bestehende Metadatenstruktur mittels ALTER TABLE ... ALTER COLUMN schnell und einfach zu ändern. Bei wenigen Datenzeilen gestaltet sich die Operation – in der Regel – recht einfach und schnell; deutlich mehr Überlegungen sind ratsam, wenn es um mehrere Millionen Datensätze geht. In diesem Fall sollte der komplette Vorgang zunächst in einer Testumgebung durchspielt werden und die Messwerte für Zeit und Volumen sorgfältig mit einer sinnvollen Strategie abgewogen werden!

    Testumgebung

    Für die Testumgebung wird eine Tabelle mit 10.000 Datensätzen gefüllt. Pro Datenseite werden – basierend auf einer Datensatzlänge von 2.015 Bytes – 4 Datensätze gespeichert.

    -- Falls die Tabelle bereits existiert, wird sie gelöscht
    IF OBJECT_ID('dbo.demo_table', 'U') IS NOT NULL
        DROP TABLE dbo.demo_table;
        GO
     
    -- Erstellen der Testtabelle
    CREATE TABLE dbo.demo_table
    (
        Id    INT         NOT NULL,
        c1    CHAR(2001)  NOT NULL    DEFAULT ('A'),
        c2    DATE        NOT NULL
    );
    GO
     
    -- Clustered Index liegt auf Attribut c2
    CREATE UNIQUE CLUSTERED INDEX ix_demo_table_c2 ON dbo.demo_table (c2)
    GO

    Die Tabelle besitzt einen Clustered Key auf [c2]. Jeder Datensatz hat eine feste Datenlänge von 2.015 Bytes. Mit insgesamt 10.000 Datensätzen belegt die Tabelle in der Datenbank 2.502 Datenseiten im Leaf-Level (http://msdn.microsoft.com/de-de/library/ms190457.aspx).



    -- Anzahl belegter Datenseiten in Tabelle
    SELECT  OBJECT_NAME(p.object_id)    AS    table_name,
            au.type_desc,
            au.total_pages,
            au.used_pages,
            au.data_pages
    FROM    sys.partitions AS P INNER JOIN sys.allocation_units AS AU
            ON (p.partition_id = au.container_id)
    WHERE   p.object_id = OBJECT_ID('dbo.demo_table', 'U');

    RECORDSET_03


    Basierend auf der Definition des Clustered Keys wird ein Datensatz auf einer Datenseite in der folgenden Reihenfolge gespeichert:



    -- Interne Struktur eines Datensatzes auf einer Datenseite
    SELECT  c.name                AS column_name,
            c.column_id,
            pc.max_inrow_length,
            pc.system_type_id,
            t.name                AS    type_name,
            pc.leaf_offset
    FROM    sys.system_internals_partition_columns pc INNER JOIN sys.partitions p
            ON (p.partition_id = pc.partition_id) LEFT  JOIN sys.columns c
            ON (
                  column_id = partition_column_id AND
                  c.object_id = p.object_id
               ) INNER JOIN sys.types AS T
            ON (pc.system_type_id = t.system_type_id)
    WHERE   p.object_id = OBJECT_ID('dbo.demo_table');

    RECORDSET_01


    Alle Datentypen sind Datentypen mit fester Länge. Die Spalte [leaf_offset] speichert die Offsets der einzelnen Spaltenwerte eines Datensatzes. Ein Datensatz beginnt immer bei Offset 0x04 da der Datensatzheader eine feste Länge von 4 Bytes besitzt (0x00 – 0x03). Nach der Speicherung des Datums (3 Bytes) werden die Daten des Attributs [Id] (4 Bytes) an der Position 0x07 gespeichert und der Inhalt des letzten Attributs [c1] wird ab Position 0x0B gespeichert.


    Unabhängig von der Struktur der Metadaten werden die Clustered Key(s) einer Tabelle am Beginn eines Datensatzes gespeichert wenn es sich beim Datentypen des Clustered Keys um einen Datentypen mit fester Länge handelt.


    ALTER TABLE ... ALTER COLUMN


    Die bestehende Datenstruktur soll im Beispiel angepasst werden. Der Datentyp des Attributs [Id] soll von einem INT-Datentypen (4 Bytes) zu einem BIGINT-Datentypen (8 Bytes) umgewandelt werden. Durch die Konvertierung verdoppelt sich das benötigte Speichervolumen für [Id]. Auf einer Datenseite sind maximal 8.060 Bytes für Daten reserviert. Wird die Datenlänge vergrößert, passen die Daten nicht mehr vollständig auf eine Datenseite und es erfolgt ein – kostenintensiver – Page Split!



    BEGIN TRANSACTION;
        -- Änderung des Datentypen INT zu BIGINT
        ALTER TABLE dbo.demo_table ALTER COLUMN [Id] BIGINT NOT NULL;
     
        -- Wie häufig wurden Page Splits durchgeführt?
        SELECT  Operation, COUNT_BIG(*)
        FROM    sys.fn_dblog(NULL, NULL) AS FD
        WHERE   Operation IN
                (
                   'LOP_DELETE_SPLIT',
                   'LOP_MODIFY_COLUMNS'
                )
        GROUP BY
                Operation;
    COMMIT TRANSACTION;

    RECORDSET_02


    Das Ergebnis zeigt die Modifikation von 10.000 Datensätzen innerhalb der Transaktion. Da mit jeweils 4 Datensätzen die Datenseiten vollständig belegt waren, mussten die Daten JEDER Datenseite auf weitere Datenseiten verteilt werden – eine sehr kostenintensive Operation.


    Interne Verwaltung


    Die Operation als solche ist – bedingt durch die auftretenden Page Splits – sehr kostenintensiv und würde deutlich kostspieliger werden, wenn beim Erweitern des benötigten Speichers Bestandsdaten überschrieben würden. Microsoft SQL Server verfährt bei der Erweiterung anders als man vermutet.



    Slot 0 Offset 0x60 Length 2015
    Record Type = PRIMARY_RECORD   Record Attributes =  NULL_BITMAP  Record Size = 2015
    Memory Dump @0x0000000015E9A060
     
    Slot 0 Column 3 Offset 0x4 Length 3 Length (physical) 3
    c2 = 1953-01-01
     
    Slot 0 Column 1 Offset 0x7 Length 4 Length (physical) 4
    Id = 2447
     
    Slot 0 Column 2 Offset 0xb Length 2001 Length (physical) 2001
    c1 = P46Q7LE7JCKDLJPWX5JRZYKO0297SN5A8TTI3F9SWZTK1JGL66CNZN1R...
    <... SNIP ...>
     
    Slot 0 Offset 0x0 Length 0 Length (physical) 0
    KeyHashValue = (0950748c2d13)

    Der obige Code zeigt den Auszug einer Datenseite vor der Änderung des Datentypen. Der Auszug der Datenseite entspricht einem Datensatz in der Tabelle. Sehr gut zu erkennen ist, dass das Schlüsselattribut [c2] an erster Position gespeichert wird. Anschließend folgen [Id] und [c1].


    Nachdem der Datentyp für das Attribut [Id] von INT auf BIGINT geändert wurde, sieht die Datenseite wie folgt aus:



    Slot 0 Column 3 Offset 0x4 Length 3 Length (physical) 3
    c2 = 1953-01-01                     
     
    Slot 0 Column 67108865 Offset 0x7 Length 0 Length (physical) 4
    DROPPED = NULL                      
     
    Slot 0 Column 2 Offset 0xb Length 2001 Length (physical) 2001
    c1 = P46Q7LE7JCKDLJPWX5JRZYKO0297SN5A8TTI3F9SWZTK1JGL66CNZN1R...
     
    Slot 0 Column 1 Offset 0x7dc Length 8 Length (physical) 8
    Id = 2447                           
     
    Slot 0 Offset 0x0 Length 0 Length (physical) 0
    KeyHashValue = (0950748c2d13)

    Der obige Auszug aus der gleichen Datenseite zeigt, wie Microsoft SQL Server vorgeht, wenn der feste Datentyp eines Attributs vergrößert wird. Aus transaktioneller Sicht wäre das “verschieben” des Offsets eine Katastrophe. In diesem Fall müsste Microsoft SQL Server jeweils die ganze Datenseite verschieben. Die nachfolgenden Abbildungen zeigen, welchen Aufwand Microsoft SQL Server bewältigen müsste, wenn der Prozess so umständlich ablaufen würde.


    PPT_01


    Die obige Abbildung zeigt vier Datensätze, die auf einer Datenseite gespeichert sind. Die Offsets mit dem * sollen die Attribute repräsentieren, die durch einen neuen – größeren – Datentypen ersetzt werden sollen. Würde nun tatsächlich der Speicherbereich an “Ort und Stelle” erweitert werden, müssten alle nachfolgenden Speicherbereiche verschoben werden.


    PPT_02


    Würde Microsoft SQL Server tatsächlich dieses Verfahren verwenden, müssten für die Erweiterung von Datensatz “A” 38 “Einheiten” verschoben werden (Vorgang 1); um Datensatz “B” zu verschieben, müssten weitere 28 “Einheiten” verschoben werden. Für Datensatz “C” wären es immerhin noch 18 “Einheiten und für den letzten Datensatz weitere 8 “Einheiten – somit insgesamt 92 “Einheiten”. Es scheint logisch, dass für diesen Vorgang nicht nur erhebliche Daten im Transaktionsprotokoll entstehen sondern dass auch der Vorgang selbst sehr zeitintensiv ist. Aus diesem Grund geht Microsoft SQL Server wie folgt vor, wenn ein größerer Datentyp verwendet wird.


    PPT_03


    Statt die ganze Datenseite zu verschieben, wird die ursprüngliche Spalte als [DROPPED] gekennzeichnet und am Ende des Datenbereichs mit fester Länge wird das Attribut neu angelegt. Durch dieses Verfahren reduziert sich der Overhead für das Verschieben von Daten erheblich.


    Ein Blick auf die Datenseite selbst zeigt, wie Microsoft SQL Server die Attribute neu ausgerichtet hat:



    Slot 0 Offset 0x60 Length 2023
    Record Type = PRIMARY_RECORD  Record Attributes =  NULL_BITMAP  Record Size = 2023
    Memory Dump @0x0000000015E9A060
     
    Slot 0 Column 3 Offset 0x4 Length 3 Length (physical) 3
    c2 = 1953-01-01                     
     
    Slot 0 Column 67108865 Offset 0x7 Length 0 Length (physical) 4
    DROPPED = NULL                      
     
    Slot 0 Column 2 Offset 0xb Length 2001 Length (physical) 2001
    c1 = 7O9E4PMJ0SEX8N6PN2GPC54XBNY65LOQG1TU4LXJKIKPLYY4JAB...
    <;... SNIP...>
     
    Slot 0 Column 1 Offset 0x7dc Length 8 Length (physical) 8
    Id = 2447
     
    Slot 0 Offset 0x0 Length 0 Length (physical) 0
    KeyHashValue = (0950748c2d13)

    Der Auszug gibt eine Vielzahl von Informationen über den Vorgang preis. Auffällig ist, dass der Datensatz nicht um 4 Bytes gewachsen ist sondern um 8 Bytes! Dieses Ergebnis ist dem Umstand geschuldet, dass das ursprüngliche Attribut nicht physikalisch aus dem Datensatz entfernt wurde sondern weiterhin 4 Bytes konsumiert (rot gekennzeichnet). Zum bestehenden Datensatz ist ein *neues* Attribut hinzugekommen, dass weitere 8 Bytes konsumiert. Dieses Attribut wird im Datensatz als [Column 1] deklariert. Sehr gut erkennbar ist, dass sich die Anordnung der Attribute im Datensatz geändert hat. Die ursprüngliche physikalische Reihenfolge war [c2], [Id], [c1]. Durch die Erweiterung sieht die physikalische Reihenfolge nun wie folgt aus:



    -- Interne Struktur eines Datensatzes auf einer Datenseite
    SELECT  c.name                AS column_name,
            c.column_id,
            pc.max_inrow_length,
            pc.system_type_id,
            t.name                AS    type_name,
            pc.leaf_offset
    FROM    sys.system_internals_partition_columns pc INNER JOIN sys.partitions p
            ON (p.partition_id = pc.partition_id) LEFT  JOIN sys.columns c
            ON (
                  column_id = partition_column_id AND
                  c.object_id = p.object_id
               ) INNER JOIN sys.types AS T
            ON (pc.system_type_id = t.system_type_id)
    WHERE   p.object_id=object_id('dbo.demo_table');
    GO
    RECORDSET_04

    Das zuvor an Position 2 geführte Attribut [Id] ist logisch nicht mehr vorhanden. Es konsumiert weiterhin 4 Bytes und beginnt an Offset 0x07. Das Attribut wurde “an anderer Stelle” im Datensatz neu implementiert. Das Attribut [Id] ist nun an das Ende des Bereichs für feste Datenlänge “gerutscht” und konsumiert dort 8 Bytes.


    Lösung


    Um den überflüssigen Speicherverbrauch wieder aus den Datenseiten zu eliminieren, reicht es aus, den Clustered Index neu aufzubauen. Durch den REBUILD wird der Speicherbereich wieder freigegeben und die Attribute wieder in der korrekten physikalischen Reihenfolge angeordnet.



    ALTER INDEX ix_demo_table_c2 ON dbo.demo_table REBUILD;

    RECORDSET_05


    Zusammenfassung


    Das Erweitern von festen Datentypen erfordert eine sorgfältige Planung, wenn es sich um sehr große Tabellen handelt. Bedingt durch den gewaltigen Overhead sind bei solchen Metadaten-Änderungen folgende Aspekte zu berücksichtigen und nach Möglichkeit in einer Testumgebung sorgfältig zu prüfen:


    Vollständig protokollierte Transaktion


    Änderungen von Metadaten sind immer vollständig protokollierte Transaktionen. Dieser Umstand bedeutet in der Umsetzung, dass bei sehr hoher Datenmenge ein nicht zu unterschätzendes Transaktionsvolumen generiert wird. Aus diesem Grund sollte bereits vor der Umsetzung das Transaktionsprotokoll ausreichend dimensioniert sein, um Wartezeiten durch AUTOGROWTH zu vermeiden. Dateien eines Transaktionsprotokolls partizipieren nicht von Instant File Initialization und somit ist der zeitliche Overhead für Vergrößerungsvorgänge der Protokolldatei ein Faktor bei der Berechnung des Zeitaufwands.


    Fragmentierung von Indexen


    Durch die Vergrößerung der Datensatzlänge muss dieser Platz im Datensatz reserviert werden. Wird die Vergrößerung auf einen frisch aufgebauten Index angewendet, ist davon auszugehen, dass bereits durch die Page Splits ca. 4 – 6 KByte zusätzliches Transaktionsvolumen pro Datenseite generiert werden.


    Man kann diese Overhead weitestgehend vermeiden, indem man folgende Punkte VOR der Vergrößerung beachtet und umsetzt:


    • Analyse der durch den Index belegten Datenseiten (siehe Abfrage oben)
    • Überprüfung der durchschnittlichen Anzahl von Datensätzen / Datenseite
    • Neuaufbau des Index (im geeigneten Servicefenster) mit einem FILLFACTOR, der so viel Platz auf der Datenseite belässt, dass eine Erweiterung ohne Page Splits durchgeführt werden kann.


    Beispiel für die Berechnung des FILLFACTOR für den Neuaufbau des Index:


    Ein Index belegt 100.000 Datenseiten. Ein Datensatz hat eine Länge von ~400 Bytes. Somit passen auf eine Datenseite 20 Datensätze. Ein Attribut vom Datentypen [INT] soll zu einem [BIGINT] geändert werden. Somit müssen auf einer Datenseite für den Erweiterungsvorhang 20 * 8 Bytes = 160 Bytes zusätzlich zur Verfügung stehen. Ein FILLFACTOR von 5% ist für den zu bewältigenden Änderungsvorgang ein guter Ausgangswert für den Index. Der gesamte Vorgang der Vergrößerung unterteilt sich somit in drei Phasen:



    • Neuaufbau des Index mit einem FILLFACTOR von 5%
    • Anpassung des Datentypen (INT –> BIGINT)

    Herzlichen Dank fürs Lesen!


    PS: Ich wünsche allen Lesern meines Blogs einen guten Start, Glück und Gesundheit für 2015. Ich hoffe, dass die von mir gewählten Themen interessant und informativ sind. Ich freue mich auf 2015 mit vielen Konferenzen und neuen Kontakten.


    Es würde mich sehr freuen, die Leser meines Blogs persönlich kennenzulernen – also einfach mal ansprechen, wenn wir uns auf einer Konferenz begegnen sollten.


    Die erste Möglichkeit in diesem Jahr wäre z. B. die SQL Konferenz (http://www.sqlkonferenz.de). Dort spreche ich am 05.02.2015 über tägliche Indexprobleme, wie man sie erkennt und wie man sie lösen kann!

    Tillmann Eitelberg: SQL Konferenz 2015

      SQL Konferenz 2015 Bald ist es wieder so weit, die SQL Konferenz für das Jahr 2015 startet vom 3. Februar bis 5. Februar im Darmstadtmium in Darmstadt. Im Folgenden möchte ich als einer der Verantwortlichen der Konferenz einen etwas detaillierteren Einblick in die Agenda geben. Auswahl der Sessions und Kommentare stellen meine persönliche Selektion […]

    Uwe Ricken: Kombinierte Indexe richtig definieren

    Ganz aktuell habe ich bei einem Kunden ein Problem in einer Abfrage entdeckt, die trotz “korrektem” Index nicht optimal ausgeführt wird. Das Problem war relativ schnell gefunden; die Reihenfolge der Attribute im Index waren nicht korrekt implementiert. Der Artikel beschreibt, wie wichtig bei der Erstellung von Indexen die Berücksichtigung von Abfragemustern ist.

    Szenario

    Der Kunde setzt ein Online-Bestellsystem ein, das neben einer Kundennummer ([Customer_Id]) das Datum der Bestellung ([OrderDate]) speichert. Für die nachfolgenden Beispiele wird eine einfache Tabellenstruktur mit 100.000 Datensätzen erstellt. Alle 100.000 Aufträge datieren aus dem Jahr 2014. Für die Erstellung der Testdaten habe ich den SQL Data Generator von Red Gate verwendet!

    -- Erstellung der Demotabelle
    CREATE TABLE dbo.Orders
    (
        Id           INT     NOT NULL    IDENTITY (1, 1),
        OrderNo      CHAR(5) NOT NULL,
        Customer_Id  INT     NOT NULL,
        OrderDate    DATE    NOT NULL,
     
        CONSTRAINT pk_Orders_ID PRIMARY KEY CLUSTERED (Id)
    );
    GO
     
    -- Zusammengesetzter Index nach Datum und Kunden-Id
    CREATE INDEX ix_Orders_OrderDate ON dbo.Orders
    (
        OrderDate,
        Customer_Id
    );
    GO

    Abfrage(n)


    Die Tabelle enthält 100.000 Datensätze. Von diesen 100.000 Datensätzen sind ca. 9.000 Datensätze aus dem Januar 2014. Die Abfrage sucht alle Aufträge, die im Januar 2014 getätigt wurden:



    SELECT Id, Customer_Id, OrderDate
    FROM   dbo.Orders
    WHERE  OrderDate BETWEEN '20140101' AND '20140131';

    Der zugehörige Ausführungsplan sieht ideal aus und zeigt, dass der zuvor erstellte Index [ix_Orders_OrderDate] verwendet wird um einen INDEX SEEK auf [OrderDate] auszuführen.


    EXECUTION_PLAN_01 


    Der Ausführungsplan zeigt 8.426 gefundene Datensätze. Das Ergebnis der Operation war vorhersehbar, da [OrderDate] im Index das primäre Indexattribut repräsentiert. In diesem Fall ist es unerheblich, dass [Customer_ID] als Prädikat nicht verwendet wurde.


    Diese Abfrage wird vom Benutzer jedoch nicht nur zur Eingrenzung des Bestelldatums angewendet sondern ausschließlich in Verbindung mit einer gültigen Kundennummer ([Customer_Id]). Die Abfrage muss um die Eingrenzung nach der Kundennummer erweitert werden:



    SELECT Id, Customer_Id, OrderDate
    FROM   dbo.Orders
    WHERE  OrderDate BETWEEN '20140101' AND '20140131' AND
           Customer_Id = CAST(5 AS INT);

    Der Ausführungsplan der obigen Abfrage lässt erahnen, dass erneut ein INDEX SEEK verwendet wird. Schaut man jedoch auf die Eigenschaften des Abfrageoperators, kann man Anomalien erkennen, die darauf hinweisen, dass der INDEX SEEK nicht “ideal” verwendet wird.


    EXECUTION_PLAN_02


    Die Abbildung zeigt, dass für das Datum erneut ein SEEK verwendet werden konnte während jedoch die Suche nach der Kundennummer ([Customer_Id]) mit Hilfe eines Filters durchgeführt wird. In diesem Fall spricht man von einem Index Range Scan! Um die Filteroperation sichtbar zu machen, wird Traceflag 9130 als Option verwendet.



    SELECT Id, Customer_Id, OrderDate
    FROM   dbo.Orders
    WHERE  OrderDate BETWEEN '20140101' AND '20140131' AND
           Customer_Id = CAST(5 AS INT)
    OPTION (QUERYTRACEON 9130);

    EXECUTION_PLAN_03


    Wie der Ausführungsplan zeigt, müssen zunächst erneut 8.426 Datensätze aus dem Datumszeitraum gesucht werden. Diese Datenmenge wird evaluiert um sie anschließend mit Hilfe eines Filters auf die – in diesem Beispiel – verbliebenen 20 Datensätze zu reduzieren. Microsoft SQL Server muss also 8.426 Datensätze überprüfen, um alle Datensätze mit der Kundennummer 5 zu filtern! Microsoft SQL Server geht bei der Suche nach den Datensätzen wie folgt vor:



    |--Index Seek(OBJECT:([demo_db].[dbo].[Orders].[ix_Orders_OrderDate]),
    SEEK:
    (
       ([demo_db].[dbo].[Orders].[OrderDate], [demo_db].[dbo].[Orders].[Customer_Id]) >= (CONVERT_IMPLICIT(date,[@1], 0), [@3]) AND
       ([demo_db].[dbo].[Orders].[OrderDate], [demo_db].[dbo].[Orders].[Customer_Id]) <= (CONVERT_IMPLICIT(date,[@2], 0), [@3])
    ),
    WHERE:([demo_db].[dbo].[Orders].[Customer_Id]=[@3]) ORDERED FORWARD)

    Es ist zu erkennen, dass für die Suche nach den Datumswerten ein – optimaler – INDEX SEEK verwendet wird. Jedoch muss für die Suche nach der Kundennummer eine WHERE-Klausel verwendet werden, die im Ausführungsplan als FILTER-Operator gekennzeichnet ist. Ursächlich für dieses Verhalten ist die Verwendung von >=, <=, BETWEEN.


    Microsoft SQL Server kann für die Kombination [OrderDate] und [Customer_Id] nicht auf Statistiken zurück greifen. Microsoft SQL Server speichert im Histogramm ausschließlich die Werte des ersten Attributs eines Index.



    DBCC SHOW_STATISTICS('dbo.Orders', 'ix_Orders_OrderDate') WITH HISTOGRAM;
    DBCC SHOW_STATISTICS_HISTOGRAM_01

    Dadurch bedingt werden zunächst alle Datumswerte aus der Datenmenge extrahiert die nachträglich auf der Kundennummer ([Customer_Id]) gefiltert werden.


    Warum INDEX SEEK + FILTER?


    Warum kann Microsoft SQL Server nicht idealer Weise bereits beim INDEX SEEK über [OrderDate] das Attribut [Customer_Id] berücksichtigen? Dieser Umstand ist der generellen Struktur eines Index geschuldet. Ein Index ist in einer B-TREE Struktur organisiert. Hierbei wird der Indexbaum immer von einem Root-Knoten bis zu einem Leaf-Knoten durchsucht, um die angeforderten Daten zu finden. Um die Indexstruktur abzubilden, wird die DMF [sys].[dm_db_database_page_allocation] verwendet:



    -- Show the index structure of the index ix_Orders_OrderDate
    SELECT  page_type_desc,
            page_level,
            allocated_page_page_id,
            previous_page_page_id,
            next_page_page_id
    FROM    sys.dm_db_database_page_allocations
    (
        DB_ID(),
        OBJECT_ID('dbo.Orders', 'U'),
        3,
        NULL,
        'DETAILED'
    ) AS DDDPA
    WHERE   is_allocated = 1
    ORDER BY
        page_type DESC,
        page_level DESC,
        previous_page_page_id ASC;

    Die Abbildung zeigt, wie von der Root Page (11.208) abwärts die Leaf-Ebene für alle Daten des Januar 2014 durchsucht wird.


    INDEX_RANGE_SCAN_01


    Entscheidend für die Arbeit der Query Engine ist, dass der Index erst nach [OrderDate] und anschließend nach [Customer_Id] sortiert ist. Microsoft SQL Server kann nicht in einem Prozessschritt sowohl Datum als auch Kundennummer mit einem effektiven SEEK erkennen. Vielmehr müssen erst alle Bestellungen von Januar 2014 ermittelt werden und anschließend kann die Ergebnismenge nach der entsprechenden Kundennummer “gefiltert” werden!


    Reihenfolge der Attribute in Index


    Immer wieder wird empfohlen, einen Index nach der Kardinalität seiner Attribute zu erstellen; diese Aussage ist nur bedingt richtig – wie das obige Bespiel beeindruckend zeigt. Kardinalität allein kann nicht das Kriterium sein, nach dem ein Index erstellt wird; es gilt auch die Abfragen selbst zu analysieren. Wenn – wie im vorliegenden Beispiel – ein Index sehr häufig für Mengenoperationen verwendet wird, kann die gewählte Indexstrategie schnell zu einem Bumerang werden. Für das aktuelle Szenario wurde der Index wie folgt umgebaut:



    CREATE INDEX ix_Orders_OrderDate ON dbo.Orders (Customer_Id, OrderDate) WITH DROP_EXISTING;

    Wir die gleiche Abfrage erneut ausgeführt, reduziert sich das IO um über 90%, da der Index ix_Orders_OrderDate wesentlich effektiver verwendet werden kann:



    SELECT Id, Customer_Id, OrderDate
    FROM   dbo.Orders
    WHERE  OrderDate BETWEEN '20140101' AND '20140131' AND
           Customer_Id = CAST(5 AS INT)
    OPTION (QUERYTRACEON 9130);
    EXECUTION_PLAN_04

    Die Details des Ausführungsplans offenbaren, dass sowohl nach der Kundennummer als auch nach dem Auftragsdatum mit Hilfe eines INDEX SEEK gesucht werden kann. Die Ursache hierfür liegt in der – neuen – Struktur des Indexes.


    INDEX_RANGE_SCAN_02


    Die neue Struktur des Indexes wird durch die Kundennummer ([Customer_Id]) kontrolliert. Da der Index aus [Customer_Id] und [OrderDate] besteht, wird das zweite Attribut ([OrderDate]) ebenfalls im Index sortiert. Wird – wie in der Beispielabfrage – nach einem Kunden mit der Customer_Id = 5 gesucht, kann der Query Optimizer einen optimalen INDEX SEEK verwenden, da er lediglich einen Indexwert betrifft. Die gleiche Operation kann nun auch für das Auftragsdatum verwendet werden, da dieses Attribut im Index vorhanden und sortiert ist. Die Query Engine KANN beim INDEX SEEK das Datum während des Zugriffs prüfen. Ist die obere Grenze des zu filternden Auftragsdatums erreicht, kann die Query Engine die Arbeit beenden; es kann keine weiteren Aufträge mit einem Bestelldatum im Januar geben, die nach dem 31.01.2014 getätigt wurden!


    Zusammenfassung


    Die richte Zusammenstellung / Komposition eines Index hängt nicht immer von starren Regeln ab. Vielmehr muss immer wieder der eigentliche Workload im Fokus stehen. Wenn – wie das obige Beispiel recht deutlich zeigt – stur nach “Vorgabe” implementiert wird, ergeben sich daraus hervorragende Optimierungsmöglichkeiten, die für Überraschungen sorgen können.


    Herzlichen Dank fürs Lesen!


    PS: Dieses Problem wie auch viele andere werde ich auf der SQL Konferenz 2015 in Darmstadt demonstrieren, analysieren und mit Lösungswegen präsentieren. Die SQL Konferenz findet in 2015 das zweite Mal mit einer beeindruckenden Themen- und Sprechervielfalt rund um SQL Server statt nachdem bereits die Premiere in 2014 ein voller Erfolg war.


    Seien es Fragen zu diesem Artikel oder aber auch generelle Fragen zur Indexierung oder zur Database Storage – bitte nicht zögern, mich einfach anzusprechen. Ich freue mich über jeden Gedankenaustausch!

    SQLPodcast: #009 – Windows PowerShell

    Im Gespräch mit Sascha Lorenz, Microsoft SQL Server MVP, rund um das Thema Microsoft PowerShell.

    Marcel Franke: Conference time – SQL Server Conference 2015

    728x90_ImSpeaking

    Although everybody prepares for Christmas and the holidays, some people already prepare the first conferences starting early next year. And so I’m very happy that the SQL Server Conference 2015 will take place in Darmstadt again. We will have 2 days full of great sessions with international speakers. So also for English speaking people there are lot’s of sessions to follow. And there is a full day only for PreCon Trainings.

    I’m also very happy to say that I have the chance to support the community with a session about “SAP Hana, Power Pivot and SQL Server – In-Memory technologies compared”.

    So register yourself, take a seat and enjoy the sessions.


    Filed under: Conferences

    Falk Krahl: Neues Servicepack für SQL Server 2014

    Heute ist für den SQL Server 2014 ein neues Update erschienen. Es handelt sich dabei um das kumulative Update 05. Es kann unter folgendem Link angefordert werden.
    Kumulatives Update 05 für SQL Server 2014

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

    Ganz frisch erschienen und im SQL Server Releaseblog veröffentlicht wurde ein CU.

    Full story »

    Christoph Muthmann: Die Reinheit der Daten

    Das ist mal wieder ein Artikel, der auch mit "Es war einmal" anfangen könnte. Es war einmal vor dem Jahre 2005...

    Full story »

    Christoph Muthmann: Passwort gleich Login

    Heute nur ein kurzer Tipp dazu, wie man die SQL Logins findet, deren Passwort identisch mit dem Namen ist.

    Full story »

    Christoph Müller-Spengler: Last “Touch” on each Database per Instance

    cms4j:

    Enhanced the T-SQL Script by adding last_user_scan, last_user_seek and last_user_lookup and compared the values against each other to get the most recent access to the very table.

    Originally posted on cms4j:

    Today i was asked if some of the databases on a specific instance were in use or not.
    As this is an interesting question that could be easily answered by the SQL Server DMVs i wrote a little script to answer this question.

    It creates a temporary table with five colummns named DatabaseName, TableName, IndexType, LastTouch and ActualDate (this is just for comparison).
    It then executes sp_MSforeachdb and inserts a record for each database within the instance except for the system databases.
    It then tells us which table was recently used and when.

    1
    /*
    @cms4j 2014-01-13
    Script to get the latest "touch" on each database
    to get to know wheather the database is in use or not.
    */

    CREATE TABLE #LastTouchOnDatabase
    (
    [DatabaseName] nvarchar(200) NULL
    , [TableName] nvarchar(200) NULL
    , [IndexType] nvarchar(200) NULL
    , [LastTouch] datetime NULL
    , [ActualDate] datetime NULL
    )

    EXEC dbo.sp_MSforeachdb 'USE [?];
    INSERT INTO #LastTouchOnDatabase
    (DatabaseName…

    View original 148 more words


    Christoph Muthmann: PASS KBD 10 Jahre

    Am Montag hatten wir Gelegenheit eine tolle Veranstaltung mit großer Tombola in Köln auszurichten. Hier einige Eindrücke.

    Full story »

    Christoph Müller-Spengler: Blog Post Series „How to tune ISV apps“

    Inspired by Brent Ozars Blog post http://www.brentozar.com/archive/2014/12/whos-allowed-add-indexes-isv-apps/ I will start this series.

    It will consist of:

    1. Capture the workload
      Capturing a typical workload during business hours.
    2. Diagnosis and Doing with “sp_BlitzIndex™”
      Diagnose and Delete/Alter/Add Indexes with the help of Brent Ozars “sp_BlitzIndex™”.
    3. Diagnosis and Doing with “Find Key Lookups in Cached Plans”
      Diagnose and Alter/Add Indexes with the help of Kendal van Dykes “Find Key Lookups in Cached Plans”.
    4. Setup Distributed Replay
      Setup a SQL Server Instance with Distributed Replay.
    5. Replay the workload
      Replay the captured workload against a backup first taken before capturing the first workload.
    6. Analysis of Performance Improvements
      Analyze two captured workloads with the help of Qure Analyzer.
    7. Analysis with Chrome Developer Tools.
      Load the ISV app in Chrome and make use of the Developer Tools to measure the response times of the Database Server.
    8. Subjective measurements
      Let human beings test the improved app.

    Hope to keep you as readers,
    Christoph Müller-Spengler


    Sascha Lorenz: 10 Jahre SQL Server Usergroup in Hamburg – PASS Deutschland e.V. feiert !

    Hallo PASS’ler im Norden,

    hiermit möchten wir zu einem besonderen Treffen der Regionalgruppe Hamburg einladen!

    Es gibt etwas zu feiern! Die PASS Deutschland besteht seit 10 Jahren, im Oktober haben wir uns in der Regionalgruppe zum 100. Mal getroffen und wir wollen mit Euch gemeinsam das Jahr gemütlich ausklingen lassen. Deswegen gibt es leckeres Essen und eine Verlosung von Überraschungspreisen.

    Bitte bringt gern Kollegen und Freunde mit, die sich auch mit dem SQL Server beschäftigen. Wir freuen uns auf Euch!

    Donnerstag, 11. Dezember 2014,
    18:00 Uhr bis ca. 20:30 Uhr,
    (Vortrag beginnt ab 18:30 und geht bis ca. 20:30 Uhr, davor sorgen wir dafür, dass ihr nicht verhungert…)

    in der MICROSOFT Niederlassung Hamburg (Adresse unten)

    Thema: SQL Server Data Tools für Datenbankentwickler von Peter Horst
    ”Wir werden das Entwickeln von Datenbankanwendungen mit den Microsoft SQL Server Data Tools kennenlernen. Themen wie Delta-Deployment auf unterschiedliche Datenbankschemaversionen, Refactoring, Code-Analyse & Datenbank Unit Test werden nicht fehlen.”

    Wir treffen uns bei:
    Microsoft Deutschland GmbH
    Geschäftsstelle Hamburg
    Gasstraße 6a
    22761 Hamburg

    Kostenlose Parkplätze befinden sich hinter dem Gebäude. Der Parkplatz ist
    über die Rampe mit dem Schild "Microsoft Kunden" erreichbar.

    Nur wenige Minuten zu Fuß ist der S-Bahnhof Bahrenfeld entfernt (S1/S11).

    Ansprechpartner vor Ort: MS Empfangs-Team, Cornelia Matthesius und Sascha Lorenz.

    Wir bitten um eine vorherige Anmeldung per Email an: slo@sqlpass.de

    Wichtig: Wir benötigen die Anmeldungen 2 Tage vor dem Treffen, da wir uns
    2014 bei Microsoft treffen können und dort Besucherausweise ausgestellt
    werden! Spontane Teilnehmer sind dennoch willkommen. ;-)

    Sascha Lorenz: Power BI - SQL Server Konferenz 2015

    120x600_SQL_Server_Konferenz7[4][4]Wie ich auch schon in diesem Blogpost angemerkt habe, ist die kommende SQL Server Konferenz in Darmstadt ein ganz besonderes Event. Daher möchte ich in den kommenden Tagen einige Sessions im Detail vorstellen.

    Microsoft BI vNext (Neues aus der Welt von Power BI)
    ”In den letzten hat Microsoft stark im Bereich Self Service BI investiert und die diversen Produkte unter dem Label PowerBI zusammengefasst. In diesem einführenden Vortrag stellt Frank Geisler die Produkte PowerQuery, PowerPivot, PowerView und PowerMap vor. Es wird gezeigt wie man über PowerQuery Daten in eine PowerBI Lösung importieren kann, wie man dann mit Hilfe von PowerPivot ein Datenmodell aufbaut um dieses dann mit PowerView und PowerMap zu visualisieren. Zum Schluss des Vortrages wird noch gezeigt wie man die fertige Lösung auf der PowerBI Website bereitstellen und mit Q&A abfragen kann.”

    Von:

    Frank Geisler ist geschäftsführender Gesellschafter der GDS Business Intelligence GmbH und beschäftigt sich in seinem Unternehmen mit dem Microsoft BI Stack und SharePoint Lösungen. Dabei legt er als MCSE – Business Intelligence und MCSE – Data Plattform sowohl Wert auf die Administration als auch die Entwicklung von BI Systemen und kennt den SQL-Server seit der Version 6.5 und SharePoint seit dem Projekt „Tahoe“. Frank hält des Öfteren Vorträge auf Konferenzen, an Universitären oder Usergroup-Treffen und schreibt regelmäßig Artikel für verschiedene deutsche Fachzeitschriften. Außerdem hat er schon einige Bücher veröffentlicht, unter anderem hat er ein grundlegendes Buch zum Thema „Datenbanken“ geschrieben und ist Mitautor der „SharePoint für Dummies“-Bücher. Er gehört zu den Gründungsmitgliedern der PASS Deutschland e.V. und ist zusammen mit Klaus Höltgen Chapter Leader der Regionalgruppe Ruhrgebiet. Für seine Community Arbeit ist Frank im Juli 2014 zum MVP SQL Server ernannt worden.

    &

    Volker Hinz ist führender Lösungsberater für Business Intelligence im Großkundenvertrieb der Microsoft Deutschland GmbH. Architekturberatung rund um SQL, BI und Office-Themen sowie deren Integration in heutige Kundenumgebungen gehört zu seinen Schwerpunkten.

    Christoph Muthmann: Office 365 Konferenz

    Wer sich für den Bereich Office 365 interessiert, sollte sich für die Konferenz in Köln anmelden.

    Full story »

    Christoph Müller-Spengler: Is SQL Server under high Memory Pressure?

    A few weeks ago, i started a blog post series that covers the finding of performance bottlenecks in SQL Server. In the first blog post in the series https://cms4j.wordpress.com/2014/09/30/is-sql-server-under-high-cpu-pressure/
    i focused on CPU Pressure.

    In this blog post i will explain how to decide from just a few queries to SQL Server DMVs if SQL Server is under high Memory pressure.

    We all know there are far more reasons why SQL Server could be slow, but let’s stick with Memory for now.

    Page Life Expectancy

    A first and very good indicator is the so called Page Life Expectancy (PLE) that can be queried by the DMV sys.dm_os_performance_counters. The Page Life Expectancy tells us how long a data page that was read from disk subsystem will stay in SQL Server’s Buffer Pool.
    In former times when SQL Server just had about 4 GB of RAM a value of 300 (seconds) was recommended by Microsoft. But times are changing, so this is no more longer the amount of RAM that you will find in nowadays SQL Servers.

    Jonathan Kehayias has a very good blog post about this:
    http://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/ -> What’s Wrong about Page Life Expectancy >= 300?

    So depending on your Servers RAM you have to define a different value than 300.
    Jonathan calculates
    Recommended PageLifeExpectancy = Physical RAM / 4 * 300

    Top Wait Type

    SQL Server always provides you with statistics what he is waiting on for the most of the time. This can be queried from the DMV sys.dm_os_wait_stats.
    With high memory pressure i often experienced the Wait Type PAGEIOLATCH_XX as the top wait type.
    This is because not all data pages that SQL Server wants to hold in the Buffer Pool fit into it – so SQL Server has to drop some pages from the Buffer Pool in order to read some new fresh pages from the disk subsystem.
    Please note that this Wait Type must not always mean that you have Memory Pressure. It could also be a slow disk subsystem or a poor indexing strategy, because there are too many table scans instead of index seeks.
    Please take a look at Paul Randals blog post http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/.
    I added two more wait types to the “benign” waits: “BACKUPBUFFER” and “BACKUPBIO” as the backup process does not make use of the Buffer Pool.

    Buffer Pool Rate

    Joining the discussion of the right value for Page Life Expectancy Thomas LaRock brought up another calculation for his so called Buffer Pool Rate. In his blog post
    http://thomaslarock.com/2012/05/are-you-using-the-right-sql-server-performance-metrics/ -> 5. BUFFER POOL I/O RATE
    he develops this value as
    MAX MEMORY / PLE = xx.x MB/sec
    He likes to have it below 20 MB/sec.

    Building the T-SQL Script

    So with these three criteria i built a script.

    /*
    	CMS, 05.12.2014:
    	Is SQL Server under Memory Pressure?
        
        This check consists of 3 Steps:
        1) Check Actual Page Life Expectancy
        2) Check Top Wait Stat                  - with the help of Paul Randal's Wait Stats CHECK
    
    http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
    
        3) Check Buffer Pool Rate               - With the help of SQL Rockstars Buffer Pool Rate Calculation
                                                  http://thomaslarock.com/2012/05/are-you-using-the-right-sql-server-performance-metrics/ -> Buffer Pool I/O Rate
        
    */
    
    SET NOCOUNT ON
    
    DECLARE @MaxServerMemory FLOAT
    DECLARE @ActualPageLifeExpectancy FLOAT
    DECLARE @RecommendedPageLifeExpectancy FLOAT
    DECLARE @RecommendedMemory FLOAT
    DECLARE @TopWaitType sysname
    DECLARE @BufferPoolRate FLOAT
    
    -- ####################################################################################################
    SELECT  @MaxServerMemory = (1.0 * cntr_value / 1024 / 1024)
    		, @RecommendedPageLifeExpectancy = Convert(INT ,(1.0 * cntr_value) / 1024 / 1024 / 4.0 * 300)
    FROM    sys.dm_os_performance_counters
    WHERE   counter_name = 'Target Server Memory (KB)'
    
    SELECT  @ActualPageLifeExpectancy = 1.0 * cntr_value
    FROM    sys.dm_os_performance_counters
    WHERE   object_name LIKE '%Buffer Manager%'
    AND		LOWER(counter_name) = 'page life expectancy'
    
    -- ####################################################################################################
    /*
    Check TOP Wait Type.
    If the TOP 1 Wait Type is PAGEIOLATCH_SH it indicates that SQL Server is waiting on datapages to be read 
    into the Buffer Pool.
    Memory Pressure might not be the root cause, it can be a poor indexing strategy
    because way too many pages have to be read into the buffer pool and they are deleted from the buffer pool too early.
    */
    ;WITH [Waits] AS
        (SELECT
            [wait_type],
            [wait_time_ms] / 1000.0 AS [WaitS],
            ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
            [signal_wait_time_ms] / 1000.0 AS [SignalS],
            [waiting_tasks_count] AS [WaitCount],
            100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
            ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
        FROM sys.dm_os_wait_stats
        WHERE [wait_type] NOT IN (
            N'BROKER_EVENTHANDLER',             N'BROKER_RECEIVE_WAITFOR',
            N'BROKER_TASK_STOP',                N'BROKER_TO_FLUSH',
            N'BROKER_TRANSMITTER',              N'CHECKPOINT_QUEUE',
            N'CHKPT',                           N'CLR_AUTO_EVENT',
            N'CLR_MANUAL_EVENT',                N'CLR_SEMAPHORE',
            N'DBMIRROR_DBM_EVENT',              N'DBMIRROR_EVENTS_QUEUE',
            N'DBMIRROR_WORKER_QUEUE',           N'DBMIRRORING_CMD',
            N'DIRTY_PAGE_POLL',                 N'DISPATCHER_QUEUE_SEMAPHORE',
            N'EXECSYNC',                        N'FSAGENT',
            N'FT_IFTS_SCHEDULER_IDLE_WAIT',     N'FT_IFTSHC_MUTEX',
            N'HADR_CLUSAPI_CALL',               N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
            N'HADR_LOGCAPTURE_WAIT',            N'HADR_NOTIFICATION_DEQUEUE',
            N'HADR_TIMER_TASK',                 N'HADR_WORK_QUEUE',
            N'KSOURCE_WAKEUP',                  N'LAZYWRITER_SLEEP',
            N'LOGMGR_QUEUE',                    N'ONDEMAND_TASK_QUEUE',
            N'PWAIT_ALL_COMPONENTS_INITIALIZED',
            N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
            N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
            N'REQUEST_FOR_DEADLOCK_SEARCH',     N'RESOURCE_QUEUE',
            N'SERVER_IDLE_CHECK',               N'SLEEP_BPOOL_FLUSH',
            N'SLEEP_DBSTARTUP',                 N'SLEEP_DCOMSTARTUP',
            N'SLEEP_MASTERDBREADY',             N'SLEEP_MASTERMDREADY',
            N'SLEEP_MASTERUPGRADED',            N'SLEEP_MSDBSTARTUP',
            N'SLEEP_SYSTEMTASK',                N'SLEEP_TASK',
            N'SLEEP_TEMPDBSTARTUP',             N'SNI_HTTP_ACCEPT',
            N'SP_SERVER_DIAGNOSTICS_SLEEP',     N'SQLTRACE_BUFFER_FLUSH',
            N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
            N'SQLTRACE_WAIT_ENTRIES',           N'WAIT_FOR_RESULTS',
            N'WAITFOR',                         N'WAITFOR_TASKSHUTDOWN',
            N'WAIT_XTP_HOST_WAIT',              N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
            N'WAIT_XTP_CKPT_CLOSE',             N'XE_DISPATCHER_JOIN',
            N'XE_DISPATCHER_WAIT',              N'XE_TIMER_EVENT')
        -- @cms4j: do not take BACKUP into Account, as it does not use the Buffer Cache
    	AND  [wait_type] NOT IN (
            N'BACKUPBUFFER', N'BACKUPIO')
        AND [waiting_tasks_count] > 0
     )
    SELECT @TopWaitType = (SELECT top 1
        MAX ([W1].[wait_type])
        FROM [Waits] AS [W1]
    INNER JOIN [Waits] AS [W2]
        ON [W2].[RowNum] <= [W1].[RowNum]
    GROUP BY [W1].[RowNum]
    HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95); -- percentage threshold
    
    
    -- ####################################################################################################
    -- SQL Rockstar comes up with BUFFER POOL RATE. But that depends on SUPER DUPER STORAGE. If that is low, the BufferPoolRate will also be slow.
    SELECT  @BufferPoolRate = ( 1.0 * cntr_value / 128.0 )
            / ( SELECT  1.0 * cntr_value
                FROM    sys.dm_os_performance_counters
                WHERE   object_name LIKE '%Buffer Manager%'
                        AND LOWER(counter_name) = 'page life expectancy' )
    		FROM    sys.dm_os_performance_counters
    WHERE   object_name LIKE '%Buffer Manager%'
            AND counter_name = 'Database pages'
    
    -- ####################################################################################################
    -- Calculate Recommended Max Memory
    SELECT @RecommendedMemory = Convert(INT, @RecommendedPageLifeExpectancy / @ActualPageLifeExpectancy * @MaxServerMemory)
    
    -- ####################################################################################################
    -- Put all the things together...
    SELECT	CASE
    			WHEN @RecommendedMemory > @MaxServerMemory AND @TopWaitType LIKE 'PAGEIOLATCH_%' AND @BufferPoolRate > 20.0 THEN '3 x YES'
                WHEN
    				(@RecommendedMemory > @MaxServerMemory AND @TopWaitType LIKE 'PAGEIOLATCH_%')
    				OR
    				(@RecommendedMemory > @MaxServerMemory AND @BufferPoolRate > 20.0) THEN '2 x YES'
                WHEN @RecommendedMemory > @MaxServerMemory AND @TopWaitType NOT LIKE 'PAGEIOLATCH_%' THEN '1 x YES, TOP Wait: ' + @TopWaitType
                WHEN @RecommendedMemory < @MaxServerMemory  THEN 'no'
            END AS [Is SQL Server under Memory Pressure?]
            , @MaxServerMemory AS [Max Server Memory (GB)]
            , @ActualPageLifeExpectancy AS [Actual PLE]
            , @RecommendedPageLifeExpectancy AS [Recommended PLE]
            , @RecommendedMemory AS [Recommended Memory (GB)]
            , @TopWaitType AS [Top Wait Type]
            , @BufferPoolRate AS [BufferPool Rate, you want < 20]
    
    

    So if all of the three criteria equal to “true” the script will show ‘3 x YES’.
    If it’s Page Life Expectancy and PAGEIOLATCH_XX or Page Life Expectancy and Buffer Pool Rate, you will get ‘2 x YES’.
    Otherwise you will get other results as shown in the script.

    Please keep in mind that this is just the starting point where you have to investigate further and proove your first results with more detailed reasons why SQL Server is under high Memory Pressure.

    Thank you for reading,
    Christoph Müller-Spengler

    Other blog posts in that series:


    Sascha Lorenz: Azure ML (Machine Learning) – SQL Server Konferenz 2015

    120x600_SQL_Server_Konferenz7[4]Wie ich auch schon in diesem Blogpost angemerkt habe, ist die kommende SQL Server Konferenz in Darmstadt ein ganz besonderes Event. Daher möchte ich in den kommenden Tagen einige Sessions im Detail vorstellen.

    ”How to do Analytics with Azure ML
    Diese Session zeigt End-to-End wie man große Datenmengen mit Azure ML analysiert, Vorhersagen generiert und diese in Power BI visualisieren und aus Geschäftsanwendungen nutzen kann. Demo-lastig. “

    Von:

    Artus Krohn-Grimberghe
    Dr. Artus Krohn-Grimberghe has spent the past 6 years immersed in advanced data mining topics. He currently works as assistant professor for Analytic Information Systems and Business Intelligence in Germany. His research focuses on data mining algorithms for e-commerce and mobile business. When he isn’t researching new algorithms or teaching, he is consulting for predictive analytics clients.

    Martin Vach
    Martin Vach ist führender Lösungsberater für Business Intelligence im Großkundenvertrieb der Microsoft Deutschland GmbH. Architekturberatung rund um SQL, BI und Office-Themen sowie deren Integration in heutige Kundenumgebungen gehört zu seinen Schwerpunkten.

    Sascha Lorenz: Azure Search – SQL Server Konferenz 2015

    120x600_SQL_Server_Konferenz7Wie ich auch schon in diesem Blogpost angemerkt habe, ist die kommende SQL Server Konferenz in Darmstadt ein ganz besonderes Event. Daher möchte ich in den kommenden Tagen einige Sessions im Detail vorstellen.

    “Looking for Answers with Azure Search
    Want to learn how to search your massive data repositories in Azure?
    Want to learn how to find value from your data quickly?
    Azure search is a new offering from Microsoft. Being able to search your data repositories at scale is something Azure cloud users have been screaming for. Well now it is here so come to this session and learn how to use Azure search.”

    Von Allan Mitchell

    “I work with companies helping them to decide how best to store their data
    Key/Value (Redis)
    Document (DocumentDB, Mongo)
    Relational (SQL Server)
    NoSQL (Hadoop/HBase)
    I also help customers to decide on whether a move to cloud based offerings makes sense. The Azure platform from Microsoft has a lot to offer and it isn't necessarily a Cloud/On Premise binary choice. Hybrid scenarios make a lot of sense too. I also work with customers who have large scale or interesting data movement requirements. This could be batch processes which are complex or real-time sensor and telemetry data.”

    Sascha Lorenz: Azure Data Factory und SSIS – SQL Server Konferenz 2015

    120x600_SQL_Server_Konferenz

    Wie ich auch schon in diesem Blogpost angemerkt habe, ist die kommende SQL Server Konferenz in Darmstadt ein ganz besonderes Event. Daher möchte ich in den kommenden Tagen einige Sessions im Detail vorstellen.

    Azure Data Factory und SSIS: Neue Wege zur Ochestrierung von Cloud und On Prem

    Mit der Azure Data Factory (ADF) hat Microsoft einen neuen Managed Service in Azure herausgebracht, welcher für das Laden und Aufbereiten von Datenströmen aus der Cloud und on Prem neue Wege geht. In dieser Session werden wir einen tieferen Einblick in die Technologie erlauben, schauen uns LIVE an wie man mit JSON und PowerShell eine "Fabrik" erstellt, Pipelines baut und den Betrieb monitored. Natürlich blicken wir auch in Richtung Zukunft und auf das was ADF im Unterschied zu unserem "Liebling" SSIS bietet.

    Von Tillmann Eitelberg & Oliver Engels :

    Tillmann Eitelberg ist Geschäftsführer der oh22information services GmbH und hat sich mit seinem Unternehmen auf die Themengebiete Datenqualität, Datenintegration und Master Data spezialisiert. Er schreibt regelmäßig über diese Themen sowie seine kleine Leidenschaft Spatial Data in seinem Blog unter www.ssis-components.net und hat verschiedene SSIS Komponenten bei Codeplex veröffentlicht. Tillmann ist SQL Server MVP. Den PASS Deutschland unterstützt er als 2. Vorsitzender, als Chapter Leader für die Region Köln/Bonn/Düsseldorf sowie als Regional Mentor Germany für PASS Global.

    Oliver Engels ist Vorstandsvorsitzender der oh22data AG, Bad Camberg im Taunus. Er gehört zur Spezies der Betriebswirte, die es nicht lassen können, sich mit IT Themen auseinander zu setzen. Das macht er jetzt bereits seit über 20 Jahren mit dem Schwerpunkt auf Marketing- und Vertriebssystemen.
    Er beschäftigt sich intensiv mit CRM, Datenqualität, Business Intelligence, Enterprise Reporting und der Analyse von Marketing- und Vertriebsdaten. Den MS SQL Server beschäftigt er seit der Version 4. Er gehört zu den Gründungsmitgliedern der PASS Deutschland, ist 1. Vorsitzender, Chapter Leader Rhein/Main sowie Regional Mentor Germany für PASS Global. Oliver ist SQL Server MVP und im Microsoft Advisory Board für SQL Information Services. Wenn er sich mal nicht mit Daten beschäftigt, dann ist er draußen in der Natur, sei es mit seinen Golden Retrievern im Wald oder mit den Wanderstiefeln auf matschigen Trails.

    Sascha Lorenz: SQL Server Konferenz 2015 – I’m speaking

    SQL_Server_Konferenz_ImSpeaking

    Die Zeit rennt mal wieder. Die nächste SQL Server Konferenz ist schon wieder komplett geplant. Am 03. – 05.02.2015 in Darmstadt im darmstadtium gibt es wieder zahlreiche Sessions zum Thema Microsoft SQL Server und Data Plattform.

    Die SQL Server Konferenz in Zahlen zusammengefasst: 40 Sessions, 4 Tracks, 38 Sprecher, 20 MVPs, 17 internationale Sprecher und damit ist die SQL Konferenz ein internationale Format geworden und für unsere Region einmalig. Und das Ganze zu einem Hammerpreis!! 

    Ich bin wieder als Sprecher für die Konferenz ausgewählt worden, was mich sehr freut. Es wird um das sehr spannende Thema Data Vault gehen. Den Vortrag werde ich gemeinsam mit meinem PSG Kollegen Jan Streblow halten, welcher auch Erfahrung in Projekten mit diesem Data Warehouse Ansatz sammeln konnte.

    “Data Vault in der Praxis mit dem SQL Server

    Data Vault ist als alternativer Modellierungsansatz für Data Warehouse Umgebungen mittlerweile sehr bekannt geworden. Dennoch fehlt vielen Teams noch die Praxis zur Bewertung der Methode, um in der Architekturphase eines Data Warehouse eine Entscheidung begründen zu können.

    Diese Session gibt Entscheidern und Entwicklern einen Überblick über die in der Methode vorgeschlagenen Artefakte, skizziert einen Architekturansatz für das sogenannte Core Data Warehouse und zeigt die Vorteile auf gegenüber der klassischen Denormalisierung im Data Warehouse in der Realisierung mit dem SQL Server und den SQL Server Integrationen Services.

    Gezeigt werden Beispiele aus den Bereichen der privaten Wirtschaft und der öffentlicher Verwaltung.”

    Dirk Hondong: SQLKonferenz 2015: der SQLPaparazzo sucht Paparazzi

    Hallo liebe PASS Deutschland Community,

    sicherlich habt Ihr es schon mitbekommen: die nächste SQLKonferenz ist in Sicht mit einer PreCon am 3. Februar und 2 darauf folgenden Konferenztagen. Veranstaltungsort ist, wie auch 2014, das Darmstadtium

    SQLKonferenz_2014_Montag_043

    und die Agenda ist echt der Wahnsinn.

    Nachdem ich auf der SQLKonferenz 2014 und auch dem SQLSaturday #313 an der Hochschule Bonn-Rhein-Sieg als quasi als “Einzelkämpfer” unterwegs war, um die Events ein wenig in Bildern festzuhalten, suche ich für das Event 2015 wieder ein wenig Unterstützung. Wer schon 2014 auf der Konferenz dabei war, hat mich ja ständig durch das Darmstadtium wuseln sehen. Ich bin daher wohl auch der einzige Teilnehmer, der von sich behaupten kann, alle Sessions besucht zu haben. Jedoch ist das auf Dauer schon ein wenig anstrengend und einer einzelnen Session von A bis Z folgen klappt dann leider nicht.

    Von daher frage ich einfach mal in die Community Runde, wer Interesse hat mich auf der SQLKonferenz 2015 zu unterstützen. Auf dem SQLSaturday #230 hatte das ja auch sehr gut geklappt.

    Rückmeldungen einfach via Kommentar, Twitter, Facebook, Email oder mir persönlich geben bei einem der nächsten SQLPass Events.

     

     


    Steffen Krause [MS]: Wieder da bei Microsoft

    Nur kurz: Ich bin wieder da - als Cloud Solution Architect für Microsoft Azure. More to come... Gruß, Steffen...(read more)

    Falk Krahl: Neues Update für SQL Server 2012 SP1

    Ebenfalls heute ist für den SQL Server 2012 mit Servicepack 1 ein neues Update erschienen. Es handelt sich um das kumulative Update 13. Es kann unter folgendem Link angefordert werden:
    SQL Server 2012 SP1 CU13

    Falk Krahl: Neues Update für SQL Server 2012 SP2

    Heute ist für den SQL Server 2012 mit Servicepack 2 ein neues Update erschienen. Es handelt sich um das kumulative Update 03. Es kann unter folgendem Link angefordert werden:
    SQL Server 2012 SP2 CU03

    Uwe Ricken: Logins und Serverrollen ermitteln

    Grundlage aller Serveranalysen im Rahmen von Sicherheitsüberprüfungen ist eine Übersicht über die verfügbaren Logins (Server Prinzipale) und ihre Gruppenzugehörigkeit. War es seit der Version 2005 bis zur Version 2008 R2 noch möglich, mit Hilfe einer PIVOT-Abfrage die Zugehörigkeiten zu festen Serverrollen abzufragen, wurde mit Version 2012 die erstmals die Möglichkeit gegeben, eigene Serverrollen zu definieren. Wie dennoch eine Übersicht aller Berechtigungen ausgegeben werden kann, zeigen die folgenden Skripte im Zusammenhang mit einer umfangreichen Erläuterung zu den festen Serverrollen in Microsoft SQL Server.

    Sicherheitsinformationen < 2012

    Die Rollen von Microsoft SQL Server bis zur Version 2008 (R2) waren vordefiniert und konnten nicht individuell erweitert werden. Von daher war eine Analyse der Rollenzugehörigkeit für die Versionen 2005 – 2008 R2 durch eine PIVOT-Abfragen möglich, wie das nachfolgende Skript zeigt:

    SELECT  login_name,
            login_type,
            [public],
            [sysadmin],
            [securityadmin],
            [serveradmin],
            [setupadmin],
            [processadmin],
            [diskadmin],
            [dbcreator],
            [bulkadmin]
    FROM
    (
        SELECT  SP.name         AS  login_name,
                SP.type         AS  login_type,
                'public'        AS  server_role,
                sp.principal_id
        FROM    sys.server_principals AS SP
        WHERE   type != 'R'
     
        UNION
     
        SELECT  SP.name         AS  login_name,
                SP.type         AS  login_type,
                role_list.name  AS  server_role,
                SP.principal_Id
        FROM    sys.server_principals AS SP LEFT JOIN
                (
                    SELECT  SRM.member_principal_id,
                            SR.name
                    FROM    sys.server_role_members AS SRM INNER JOIN sys.server_principals AS SR
                            ON (SRM.role_principal_id = SR.principal_id)
                ) AS role_list ON
                (sp.principal_id = role_list.member_principal_id)
        WHERE    SP.type != 'R'
    ) AS SecList
    PIVOT
    (
        COUNT(principal_id)
        FOR  server_role
        IN
        (
            [public],
            [sysadmin],
            [securityadmin],
            [serveradmin],
            [setupadmin],
            [processadmin],
            [diskadmin],
            [dbcreator],
            [bulkadmin]    
        )
    ) AS pvt
    ORDER BY
        login_type,
        login_name;

    Das Skript sieht auf dem ersten Blick umständlich aus da es durch die Verwendung einer PIVOT-Abfrage recht umfangreich und kompliziert erscheint. Prinzipiell gilt jedoch die besondere Beachtung den fest definierten Serverrollen. Diese “festen Serverrollen” sind in Microsoft SQL Server Standard und konnten bis zur Version Microsoft SQL Server 2012 nicht erweitert werden.


    Sicherheitsinformationen >= 2012


    Mit Microsoft SQL Server 2012 war es erstmals möglich, eigene Serverrollen zu definieren. Diese Möglichkeit wurde schon seit vielen Jahren gefordert, um in Enterprise-Umgebungen Möglichkeiten zu haben, granulare Berechtigungskonzepte für Administratoren festzulegen. Bisher war es immer notwendig, DBAs mit [sysadmin]-Berechtigungen auszustatten. Mit Microsoft SQL Server 2012 könnten erstmals dedizierte Berechtigungen erteilt werden, die sich nicht nur auf vordefinierte Bereiche erstrecken.


    Auf Grund der Variabilität der Serverrollen kann das zuvor verwendete Skript nicht mehr angewendet werden. Man kann nicht mehr von den fest definierten Serverrollen ausgehen sondern muss ebenfalls einkalkulieren, dass individuelle Serverrollen implementiert wurden. Das nachfolgende Skript reagiert auf diese Variabilität, indem eine Ergebnistabelle dynamisch erstellt wird.



    /* wenn die Ausgabetabelle bereits existiert, muss sie erst gelöscht werden */
    IF OBJECT_ID('tempdb..##Result') IS NOT NULL
        DROP TABLE ##Result;
        GO
     
    /*
        Erstellung der Ausgabetabelle basierend auf den vorhandenen Serverrollen
    */
    DECLARE    @stmt        NVARCHAR(4000);
    DECLARE    @col_enum    NVARCHAR(1000) = N'';
    SET        @stmt  =    'CREATE TABLE ##Result([PrincipalName] nvarchar(128) NOT NULL PRIMARY KEY CLUSTERED, login_type CHAR(5) NOT NULL $Column_Names);';
     
    SELECT  @col_enum = @col_enum + ', ' + QUOTENAME(name) + ' TINYINT NOT NULL DEFAULT (0)'
    FROM    sys.server_principals AS SP
    WHERE   type_desc = 'SERVER_ROLE'
    ORDER BY
            principal_id;
     
    /* Ersetzen der Textkomponente durch die tatsächlich zu erstellenden Attribute */
    SET   @stmt =  REPLACE(@stmt, '$Column_Names', @col_enum);
    EXEC  sp_executeSQL @stmt;
     
    /* Eintragen aller vorhandenen Logins des Servers */
    INSERT INTO ##Result ([PrincipalName], [login_type], [public])
    SELECT  name, type, ISNULL(IS_SRVROLEMEMBER('public', name), 0)
    FROM    sys.server_principals
    WHERE   type_desc != 'SERVER_ROLE';
     
    /* Ermittlung aller verfügbaren Serverrollen für jedes Login */
    DECLARE c CURSOR
    FOR
        SELECT  N'UPDATE ##Result SET ' + QUOTENAME (R.name) + ' = 1 WHERE [PrincipalName] = '+ QUOTENAME(L.name, '''') + ';'
        FROM    sys.server_principals AS L INNER JOIN sys.server_role_members AS SRM
                ON (l.principal_id = SRM.member_principal_id) INNER JOIN sys.server_principals AS R
                ON (SRM.role_principal_id = R.principal_id);
     
    OPEN c;
     
    FETCH NEXT FROM c INTO @stmt;
    WHILE @@FETCH_STATUS != -1
    BEGIN
        EXEC sp_executeSQL @stmt;
        FETCH NEXT FROM c INTO @stmt;
    END
     
    CLOSE c;
    DEALLOCATE c;
     
    /* Ausgabe des Ergebnisses */
    SELECT * FROM ##Result
    ORDER BY
        [login_type],
        [PrincipalName];

    Das Skript wirkt ebenfalls recht umfangreich, ist aber auf Grund seiner modularen Schritte recht einfach zu lesen. Im ersten Schritt wird eine globale temporäre Tabelle erstellt, die später für die Ausgabe vorgesehen ist. Die Besonderheit dieser Ausgabetabelle besteht aus drei Dingen:



    • es handelt sich um eine globale temporäre Tabelle, da später mit sp_executeSQL befüllt und aktualisiert wird. Da sp_executeSQL in einem eigenen Kontext ausgeführt wird, kann die Ausführung nur auf globale Objekte zugreifen, die vorhanden sind.
    • Im Ausführungsstring für die Erstellung der Tabelle [##Result] befindet sich ein Textelement “$Column_Names”. Dieses Textelement wird vor der Ausführung des Codes durch die Namen der vorhandenen Serverrollen ersetzt. Dadurch wird in der Tabelle für jede Serverrolle ein eigenes Attribut erzeugt.
    • SQL Injection kann mit großer Wahrscheinlichkeit ausgeschlossen werden, da die Attribute mit QUOTENAME() in eckige Klammern eingeschlossen werden und damit nicht als Codestrukturen erkannt werden.

    Ist die temporäre Tabelle ##Result erstellt, werden alle vorhandenen Logins in die Tabelle eingetragen. Da für alle Attribute, die Serverrollen repräsentieren der Standardwert 0 gilt, müssen abschließend die vorhandenen Zuordnungen von Serverlogins und Serverrollen ausgewertet und aktualisiert werden. Die Ermittlung dieser Informationen wird mit einem Cursor durchgeführt. Sobald die Evaluierungsphase abgeschlossen ist, werden die Informationen ausgegeben.


    Feste Serverrollen


    Für die Bewertung der Sicherheit eines vorhandenen Microsoft SQL Server ist diese Auswertung ein erster wichtiger Schritt, der jedoch noch weitere Prüfungen nach sich zieht. Hierzu zählen insbesondere die dedizierten Serverberechtigungen für jeden einzelnen Login. Microsoft SQL Server verfügt über Standardrollen für die Administration des Servers, die über unterschiedliche Berechtigungen verfügen. Die nachfolgende Liste beschreibt die festen Serverrollen in einem Microsoft SQL Server.


    sysadmin


    Die Mitglieder der festen Serverrolle [sysadmin] verfügen über ALLE Berechtigungen. Rechte von Mitgliedern der Serverrolle [sysadmin] können nicht eingeschränkt werden. Grundsätzlich sollten weder Applikationskonten noch Logins von Standardbenutzern über diese Rechte verfügen! Leider kommt es immer wieder vor (<=90%), dass Applikationen, die durch Softwarehersteller auf den Kundenservern implementiert werden, Logins verwenden, die Mitglied der festen Serverrolle [sysadmin] sind. Wenn ein Softwarehersteller nach diesen Rechten für seine Applikation fragt, sollte Vorsicht angebracht sein und in einer Gegenfrage die speziellen Berechtigungen für die Anwendung angefordert werden, die eine Mitgliedschaft in der Serverrolle [sysadmin] berechtigen. Ebenfalls impliziert die Mitgliedschaft in der festen Serverrolle [sysadmin] den Zugang zu allen Datenbanken und ihre Inhalte (Daten)!


    serveradmin


    Die Mitglieder dieser festen Serverrolle können – wie sich aus dem Namen der Serverrolle bereits ableiten lässt – die Konfiguration eines Servers ändern. Weiterhin besteht für Mitglieder der festen Serverrolle [serveradmin] die Möglichkeit, die Dienste des Microsoft SQL Server neu zu starten. Die Mitglieder der festen Serverrolle [serveradmin] sind trotz des Namens SEHR eingeschränkt. Ihre Berechtigungen erstrecken sich ausschließlich auf die Konfiguration von Einstellungen, die mittels sp_configure abgerufen werden können. Ein Mitglied der festen Serverrolle [serveradmin] kann weder Einstellungen für den SQL Agent konfigurieren noch Datenbanken oder neue Logins / Benutzer anlegen. Außerdem können keine globalen oder temporären Traceflags gesetzt werden.


    securityadmin


    Mitglieder der festen Serverrolle [securityadmin] können die Eigenschaften von Logins modifizieren. Jedoch können Mitglieder der festen Serverrolle [securityadmin] ein Login nicht einer Serverrollen zuweisen,  der er nicht selbst als Mitglied zugeordnet ist.


    Dennoch ist vor der Vergabe dieser Privilegien sorgfältig abzuwägen! Als Mitglied der festen Serverrolle [securityadmin] besteht die Möglichkeit, weitreichende Berechtigungen “durch die Hintertür” an andere Logins zu vergeben. Das nachfolgende Skript – ausgeführt als ein Mitglied der festen Serverrolle [securityadmin] – verdeutlicht die Problematik.



    -- Erstellen eines neuen Logins
    CREATE LOGIN TEST WITH PASSWORD = 'abc$123', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;
    GO
     
    -- Hinzufügen zur Serverrolle [securityadmin] funktioniert
    ALTER SERVER ROLE [securityadmin] ADD MEMBER [TEST];
     
    -- Hinzufügen zu Serverrolle [processadmin schlägt fehl,
    -- da nicht selbst Mitglied
    ALTER SERVER ROLE [processadmin] ADD MEMBER [TEST];
    GO
     
    -- Zuweisung von Berechtigungen auf Serverebene, die weit
    -- über die Berechtigungen der eigenen Serverrolle hinausgehen
    GRANT ALTER ANY DATABASE TO [TEST];
    GRANT ADMINISTER BULK OPERATIONS TO [TEST];
    GRANT CONTROL SERVER TO [TEST];
    GO

    Das Skript erstellt ein neues Login mit dem Namen [Test]. Anschließend sollen diesem neuen Login zwei Serverrollen zugewiesen werden. Die Zuweisung zur ersten Gruppe funktioniert einwandfrei, da der ausführende Benutzer selbst Mitglied der Serverrolle [securityadmin] ist. Der zweite Aufruf schlägt fehl, da der ausführende Benutzer nicht selbst über die notwendigen Berechtigungen verfügt.


    Problematisch wird die Zuweisung von serverweiten Berechtigungen. Die letzten drei Befehle können von einem Mitglied der festen Serverrolle [securityadmin] ausgeführt und umgesetzt werden. Somit verfügt der neue Login z. B. über die Möglichkeit der Serverkontrolle und somit über die Möglichkeit der Verwendung von “Dynamic Management Objects”, die einem Mitglied der festen Serverrolle [securityadmin] versagt sind. Da ein Mitglied der festen Serverrolle [securityadmin] das Login selbst erstellt hat, kann er sich nun mit diesem Konto anmelden und anschließend die Serverinformationen abfragen oder aber – wie das obige Beispiel zeigt – mit diesem Konto den SQL Server kontrollieren. Microsoft verweist ausdrücklich auf die erweiterten Sicherheitsbedenken in der OH:


    Durch die Möglichkeit, Zugriff auf Database Engine (Datenbankmodul) zu gewähren und Benutzerberechtigungen zu konfigurieren, kann der Sicherheitsadministrator die meisten Serverberechtigungen zuweisen.Die Rolle securityadmin muss als Entsprechung der Rolle sysadmin behandelt werden.


    processadmin


    Den Mitgliedern der festen Serverrolle [processadmin] ist es erlaubt, aktive Prozesse (Threads) zu beenden indem Sie mit KILL die entsprechende Session beenden. Diese Berechtigung hört sich zunächst recht harmlos an, hat aber – aus meiner Sicht – erhebliche Mankos in Bezug auf die Datensicherheit. Mit der Mitgliedschaft in der festen Serverrolle [processadmin] geht einher, dass das Login nicht nur Prozesse beenden kann sondern mittels “Dynamic Management Objects” ebenfalls Möglichkeiten besitzt, bestehende Sessions zu überwachten und zu analysieren! Das nachfolgende Skript kann ein Mitglied der festen Serverrolle [processadmin] ohne Probleme ausführen:



    SELECT  DES.session_id,
            DEC.last_read,
            DEC.last_write,
            DEST.text            AS last_command,
            req_command.text     AS current_command,
            DEQP.query_plan      AS current_plan,
            DER.last_wait_type
    FROM    sys.dm_exec_connections AS DEC INNER JOIN sys.dm_exec_sessions DES
            ON (DEC.session_id = DES.session_id) LEFT JOIN sys.dm_exec_requests AS DER
            ON (
                 DEC.connection_id = DER.connection_id AND
                 DEC.most_recent_session_id = DER.session_id
               )
            CROSS APPLY sys.dm_exec_sql_text(DEC.most_recent_sql_handle) AS DEST
            OUTER APPLY sys.dm_exec_sql_text(DER.sql_handle) AS req_command
            OUTER APPLY sys.dm_exec_query_plan(DER.plan_handle) AS DEQP
    WHERE   DES.is_user_process = 1;

    Das ein Mitglied der festen Serverrolle [processadmin] überprüfen muss, wann eine Session zum letzten Mal aktiv war oder worauf eine aktive Sitzung zuletzt gewartet hat, ist unbestreitbar eine Grundvoraussetzung für ein verantwortliches Handeln. Problematisch wird es – aus meiner Sicht – jedoch, wenn es darum geht, auch die Ausführungsbefehle sichtbar zu machen. Hier geht die Berechtigung eines Mitglieds der festen Serverrolle [processadmin] zu weit, da durch die Auswertung von SQL Befehlen Informationen preis gegeben werden, die u. U. nicht für Dritte zugänglich sein sollten (z. B. ein UPDATE einer Mitarbeitertabelle, in der Gehälter gespeichert werden).


    setupadmin


    Die Mitglieder der festen Serverrollen [setupadmin] haben die Möglichkeit, Verbindungsserver (“Linked Server”) hinzuzufügen.


    bulkadmin


    Auch hier ist der Name selbst erklärend. Mitglieder dies festen Serverrolle [bulkadmin] verfügen über die Berechtigung, BULK INSERT Operationen ausführen zu können. In Seminaren taucht immer wieder die Frage auf, warum es diese Rolle überhaupt gibt. Die Antwort ist recht einfach: Mit BULK INSERT werden externe Datendateien in Datenbanken importiert. Ein Mitglied der festen Serverrolle [bulkadmin] erhält – indirekt – einen Zugriff auf Ressourcen, die sich außerhalb von Microsoft SQL Server befinden.


    diskadmin


    Mitglieder der festen Serverrolle [diskadmin] verfügen über die Berechtigung zur Verwaltung von Datenträgerdateien. Diese Serverrolle ist aus meiner Sicht wertlos, da sie einzig die Berechtigung zur Verwaltung von BACKUP-Medien erteilt. Sollte man Mitglied der festen Serverrolle [diskadmin] sein, so sollte man bereits einige Erfahrung mit T-SQL haben; die Verwendung von SQL Server Management Studio (SSMS) ist trotz Mitgliedschaft in der festen Serverrolle [diskadmin] nicht möglich! Das SSMS in Bezug auf Verwaltung seine Tücken hat, habe ich bereits im Artikel “GUI vs. T-SQL – trotz dbo / db_owner keine Berechtigungen?” beschrieben.


    Versucht man, als Mitglied der festen Serverrolle [diskadmin] über SSMS ein neues BACKUP Medium anzulegen, erhält man die folgende Fehlermeldung:


    CREATE NEW BACKUP MEDIA


    Auch die Verwaltung bereits bestehender Backupmedien gestaltet sich schwierig, wenn nicht zusätzlich zur bestehenden Mitgliedschaft in der festen Serverrolle [diskadmin] die Mitgliedschaft in der festen Serverrolle [dbcreator] besteht!


    CHECK BACKUP MEDIA


    Um ein Backupmedium zu erstellen, muss also mit T-SQL gearbeitet werden



    EXEC sp_addumpdevice @devtype = 'disk', @logicalname = 'DB_BACKUP', @physicalname = 'S:\backup\db_backup.med';

    Mit sp_addumpdevice ist es möglich, ein BACKUP-Medium zu erstellen; auf Grund der Voraussetzung weiterer Berechtigungen sind weitere Operationen wie BACKUP und RESTORE nicht möglich! Hierzu bedarf es mindestens der Zugehörigkeit zur festen Serverrolle [dbcreator].


    dbcreator


    Mitglieder der festen Serverrolle [dbcreator] können beliebige Datenbanken erstellen, ändern, löschen und wiederherstellen. Diese – eher unscheinbare – Berechtigung sollte mit Sorgfalt zugeteilt werden. Ein Mitglied der festen Datenbankrolle [dbcreator] kann von JEDER Datenbank eine Datensicherung machen. Insbesondere bei Datenbanken, die im vollständigen Wiederherstellungsmodus laufen, sollte ein Mitglied der festen Datenbankrolle [dbcreator] darauf aufmerksam gemacht werden, dass BACKUP-Befehle immer mit der Option COPY_ONLY ausgeführt werden sollten um die Sicherungskette der Protokollsicherungen nicht zu unterbrechen.


    Ein zusätzliches Manko der festen Serverrolle [dbcreator] ist die fehlende Möglichkeit, den Eigentümer einer neu angelegten Datenbank nachträglich zu ändern. Diese Berechtigung fehlt einem Mitglied der festen Datenbankrolle [dbcreator] nämlich trotz “... beliebige Datenbanken erstellen, ändern, löschen ...”.


    Einen Vorteil hat die feste Serverrolle [dbcreator] im Zusammenhang mit der Wiederherstellung von Datenbanken jedoch... führt ein Mitglied der festen Serverrolle [sysadmin] eine Wiederherstellung einer Datenbank von einer Datensicherung aus, so wird – automatisch – der ausführende Benutzer zum Eigentümer der Datenbank. Führt ein Mitglied der festen Datenbankrolle [dbcreator] eine Wiederherstellung einer Datenbank aus einer Datensicherung aus, so bleibt der ursprüngliche Eigentümer der Datenbank erhalten. Sehr bedauerlich, dass diese Einstellung nicht grundsätzlich die Standardeinstellung ist. Unabhängig davon, ob ein Mitglied der festen Serverrolle [sysadmin] oder ein Mitglied der festen Serverrolle [dbcreator] die Wiederherstellung initiiert sollte der ursprüngliche Eigentümer einer Datenbank bei einer Wiederherstellung erhalten bleiben.


    Herzlichen Dank fürs Lesen!

    Marcel Franke: What’s your passion in life?

    This morning I have seen an interesting interview with Microsoft’s CEO Satya Nadella at the University of Berlin (Satya Nadella as guest at TU Berlin) and he was talking about the new generation, technologies, students, changing business models and passion. So it made me think about my passion and what makes me happy every day, even if I need to stand up at 5 in the morning (of course Smile)?

    20140221_pmONE_Scharmuetzelsee_Veranstaltung-53

    So here are my 4 major passions:

    • It’s about my customers: I really like the time in which we live now. We can see a big paradigm shift in the area of Digital Enterprises, Big Data, Data Analytics and the Internet of things. So a lot of fascinating projects happens these days and I feel very lucky to be part of some of them.
    • It’s about my team: Because this new world is so fast changing and challenging it’s so important to have a great team in order to be successful. And what I like in my team is, that we share the same passion. And if you share the same passion, you can achieve fantastic things and have a lot of fun and success.
    • It’s about technologies: I have a good technical background and focused my research in the last month on Data Analytics, Big Data, Machine Learning and new technologies that came up with the evolution of the cloud technologies. And there is a great video I always remember from Lars Thomsen about “520 weeks of future”: 520 Wochen Zukunft — die zweite Dekade der grossen Chancen. Unfortunately this video is in German, but it makes me think.
    • It’s about education: I started to learn to self educate myself at my first days at university. All of my colleagues were familiar with the Internet, websites, mp3 and all the great things in the year of 2000. So I had to catch up. And after some years in consulting with lot’s of experiences I got in so many projects, I felt that it’s now time to share these experiences with others and help educating our new generation. That’s why I started to give trainings, talks on conferences, published whitepapers and started to write a blog.

    So what are yours? What makes you excited in your day to day job?

    Have fun…


    Filed under: This & that

    Uwe Ricken: Rangfolge von Datentypen – Auswirkung auf Abfragen

    In dem zuvor geschriebenen Artikel “Warum korrekte Datentypen für WHERE-Klauseln wichtig sind” wurde beschrieben, wie wichtig es ist, dass in den WHERE-Klauseln immer die richtigen Datentypen verwendet werden. Dieser Artikel zeigt, dass neben der Wahl der korrekten Datentypen bei Abfragen auch die Datentypen selbst eine wichtige Rolle für eine optimale Ausführung spielen.

    Wer bei der Abfrage von Daten nicht den richtigen Datentypen beachtet, kann recht schnell in die Verlegenheit kommen, dass ein performanter INDEX SEEK zu einem INDEX SCAN werden kann. Das Beispiel zeigt, wie die Rangfolge der verwendeten Datentypen die Ausführung von Abfragen in Verbindung mit falschen Datentypen für den Abfragewert beeinflusst.

    Testumgebung

    Für die Demonstration des oben beschriebenen Themas wird eine Tabelle mit 1.000 Datensätzen erstellt. Anschließend wird ein non clustered Index auf dem Attribut [c2] erstellt, um die unterschiedlichen Ausführungsszenarien zu untersuchen.

    -- Testtabelle
    CREATE TABLE dbo.foo
    (
        Id INT          NOT NULL    IDENTITY (1, 1),
        c1 MONEY        NOT NULL    DEFAULT (100),
        c2 VARCHAR(20)  NOT NULL
    );
    GO
     
    -- Eintragen von 1.000 Datensätzen
    DECLARE @i INT = 1;
    DECLARE @sn INT;
    WHILE @i <= 1000
    BEGIN
        SET @sn = RAND() * 1000
        INSERT INTO dbo.foo (c2) VALUES (CAST(@sn AS VARCHAR(20)));
        SET @i += 1;
    END
    GO
     
    -- Erstellung von Index für Datenzugriff
    CREATE INDEX ix_foo_c2 ON dbo.foo (c2) INCLUDE (c1);
    GO

    Sobald die Tabelle erstellt und die Daten eingetragen sind, werden die beiden folgenden Abfragen ausgeführt...



    SELECT c1, c2 FROM dbo.foo WHERE c2 = 790;
    SELECT c1, c2 FROM dbo.foo WHERE c2 = '790';
    GO

    Es wird der folgende Ausführungsplan für die Abfragen generiert:


    EXECUTIONPLAN_01


    Scheinbar gleiche Abfragen erzeugen unterschiedliche Ausführungspläne, dessen ursprünglicher Grund bereits in dem zuvor angegebenen Artikel beschrieben wurde. Die erste Abfrage muss einen – kostspieligen – INDEX SCAN durchführen, da jeder Wert im Attribut [c2] explizit in den Datentypen [INT] umgewandelt werden muss; somit ist eine gezielte Suche im Index nicht möglich. In der zweiten Abfrage wurde der korrekte Datentyp verwendet und somit kann ein effizienter INDEX SEEK angewendet werden. Wie schaut es aber aus, wenn der korrekte Datentyp kein String sondern ein numerischer Wert ist?


    Um diese Frage zu beantworten, wird der Datentyp von Attribut [c2] in einen “INT” umgewandelt. Damit der Datentyp geändert werden kann, dürfen keine Objekte auf das Attribut verweisen. Aus diesem Grund muss der Index [ix_foo_c2] gelöscht werden. Nach der Änderung des Datentypen kann der Index erneut erstellt werden.



    -- Löschen des Index auf Attribut [c2]
    DROP INDEX ix_foo_c2 ON dbo.foo;
    GO
     
    -- Änderung des Datentypen von varchar zu int
    ALTER TABLE dbo.foo ALTER COLUMN c2 INT NOT NULL;
     
    -- Erstellung des Index ix_foo_c2
    CREATE INDEX ix_foo_c2 ON dbo.foo (c2) INCLUDE (c1);
    GO

    Sobald die Änderungen abgeschlossen sind, können die Abfragen erneut ausgeführt werden. Tatsächlich hat sich auf Grund der Änderung der Datentypen der Ausführungsplan für den “falschen” Datentypen geändert; er führt TROTZ falschen Datentypen einen performanten INDEX SEEK aus.


    EXECUTIONPLAN_02


    Verrückte Welt, oder? Warum wird (entgegen der Erwartungen) trotz eines falschen Datentypen ein INDEX SEEK ausgeführt. Die Antwort liegt in den Details des Ausführungsplans versteckt.


    EXECUTION_PROPERTIES_01


    Interessant ist die Analyse des SEEK-Prädikats. Es ist erkennbar, dass im SEEK-Prädikat der Datentyp des zu suchenden Wertes konvertiert wird und nicht die Werte im Attribut [c2].


    Dieser Umstand führt dazu, dass Microsoft SQL Server einen performanten INDEX SEEK durchführen kann. Es muss nicht jeder Wert in [c2] konvertiert werden sondern der “Suchwert” wird zum Datentypen konvertiert, der dem Datentypen von [c2] entspricht. Dieses Verhalten ist der Rangfolge der Datentypen geschuldet, die von Microsoft SQL Server unterstützt werden.


    Allgemein werden Textdatentypen immer nachrangig zu numerischen Datentypen behandelt; somit müssen sich diese Datentypen einer Konvertierung unterwerfen, wenn sie mit Werten zusammenarbeiten müssen, die in der Rangfolge über dem eigenen Datentypen stehen. Die von Microsoft SQL Server verwendete Rangfolge stellt sich wie folgt dar:


    1.  user-defined data types (highest)
    ...
    5.  datetime2
    ...
    10. float
    ...
    15. int
    ...
    19. ntext
    ...
    23. uniqueidentifier
    24. nvarchar (including nvarchar(max) )
    26. varchar (including varchar(max) )
    ...
    29. binary (lowest)


    Die vollständige Liste der Rangfolge für Datentypen findet sich hier “Data Type Precedence (Transact-SQL)”. Für das angeführte Beispiel ist erkennbar, dass der Datentyp [varchar] nachrangig zum Datentypen [int] ist; er muss sich “unterordnen” und wird – sofern möglich – zu einem numerischen Datentypen konvertiert.


    Herzlichen Dank fürs Lesen!

    Falk Krahl: Neues Servicepack für SQL Server 2014

    Bereits am 21.10.2014 ist für den SQL Server 2014 ein neues Update erschienen. Es handelt sich dabei um das kumulative Update 04. Es kann unter folgendem Link angefordert werden.
    Kumulatives Update 04 für SQL Server 2014

    Andreas Wolter: Maximum number of rows per data page and minimal record size (SQL Server storage internals)

    Maximale Anzahl Zeilen je Datenseite und minimale Datensatzgröße

    (DE)
    In einer meiner letzten Master-Classes tauchte die nicht ganz ernste aber dennoch interessante Fragestellung auf:

    Wie viele Zeilen passen eigentlich maximal auf eine Datenseite? – wenn die Datensätze/Records so klein wie möglich sind.

    Zunächst, Part 1, was ist der kleinstmögliche Datensatz in SQL Server?

    Um das zu beurteilen, ist es gut, die Datentypen sowie die Struktur eines Datensatzes genau zu kennen.
    Man könnte versucht sein, anzunehmen, dass eine Spalte vom Datentyp bit der kleinstmögliche Datensatz ist. Der erfahrene SQL‘er wird wissen, dass ein bit allein auch immer mindestens 1 byte in einem Record benötigt – erst bei mehreren Spalten dieses Datentyps, kommt ein Platzersparnis ins Spiel (bit (Transact-SQL)).

    Der nächste Kandidat sind Datentypen, die laut der Liste in Books Online nur 1 byte Speicherplatz benötigen. Das wären folgende:

    (EN)
    In one of my last Master classes, a not quite so serious but nevertheless interesting question was brought up:

    How many rows maximally do actually fit on a data page? – if the data sets/records are as small as possible.

    First of all, part 1, what is the smallest possible data set in SQL Server?

    In order to assess this, it is commendable to know exactly the data types as well as the structure of a data set. 

    One could be tempted to assume that a column of the data type bit is the smallest possible data set. The experienced SQL people will know that a bit alone always also requires at least 1 byte in one record – only with several columns of this data type, the place-saving aspect comes into play (bit (Transact-SQL)).

    The next candidate are data types which according to the list in Books Online use only 1 byte of storage. These would be the following:

     

    • bit
    • char(1)
    • binary(1)
    • tinyint

     

    Tatsächlich benötigen in diesem besonderen Fall, einer einzigen Spalte je Datensatz, auch Records mit Nettowert von 2 Bytes, 9 Bytes auf der Datenseite:

    As a matter of fact, in this particular case of a single column per record, also records with a net value of 2 bytes use 9 bytes on the data page:

     

    • char(2)
    • nchar(1)
    • binary(2)
    • smallint

    Wie kommt das?
    Das liegt an der Struktur der Datensätze. Diese ist hinlänglich in diversen Blogs dokumentiert (z.B. hier Inside the Storage Engine: Anatomy of a record und hier: SQL Server Storage Internals 101 ), jedoch eher für allgemeine Zwecke und dieser Sonderfall (eine 1-byte-Spalte) ist dabei weniger im Fokus.

    Theoretisch würden für einen Datensatz 8 Bytes ausreichen:

    4 Bytes: Datensatzkopf (Typ + Zeiger auf Null-Bitmap)
    1 Byte: Daten
    2 Bytes: Anzahl der Spalten im Record
    1 Byte: NULL Bitmap

    Dazu kommt noch der 2 Bytes große Zeiger im Page-Offset, was dann 10 Bytes ergeben würde.
    Dennoch belegt ein solcher Datensatz 9 Bytes auf der Seite/Page + Offset.

    Woran das liegt, sehen wir uns an.

    How come?
    This has to do with the structure of the data sets, which is sufficiently documented in various blogs (e.g. here: Inside the Storage Engine: Anatomy of a record and here: SQL Server Storage Internals 101 ), but rather for general purposes, and this special case (a 1-byte-column) is less focused on here.

    Theoretically, 8 bytes would be sufficient for a data set:

    4 bytes: data set head (type + pointer to NULL-bitmap)
    1 byte: data
    2 bytes: number of columns in record
    1 byte: NULL bitmap

    Add to that the 2-bytes-pointer in the page offset, which would then result in 10 bytes.
    Despite this, such a data set uses 9 bytes on the page/page + offset.

    We will now look at the reason for this.

    Im Folgenden definiere ich 2 Tabellen mit jeweils einer bzw. 2 Spalten von Datentyp tinyint, der ja bekanntlich einen Byte benötigt:

    In the following, I am defining 2 tables with one and 2 columns each of the data type tinyint, which is known to use 1 byte:

     

    CREATE TABLE T1col
    (col1 tinyint null)
    GO
    CREATE TABLE T2col
    (col1 tinyint null, col2 tinyint null)

     

    Danach füge ich zuerst Datenätze mit dem Wert „255“ bzw „255, 255“ ein, und danach einige mit Wert „NULL“.

    Mit DBCC Page kann man sich den Header der Datenseiten beider Tabellen ansehen, und findet eine kleine Überraschung (Ergebnis reduziert):

    Next, I am inserting, first, data set of the value “255” or “255, 255” and then a few of the value “NULL.”

    With the DBCC page, one can look at the header of the data pages of both tables, and one will find a small surprise (reduced result). 

     

     DBCCPage_1vs2cols

     

    Obwohl die Größe der Daten fixer Länge (pminlen) mit 5 bzw. 6 unterschiedlich angegeben wird, ist der freie Speicherplatz auf der Seite (m_freeCnt) identisch! (rot)

    Der Datensatz belegt in beiden Fällen jedoch 9 Bytes im Page-body (blau).

    So sieht die Tabelle, bestehend aus einer Spalte, mit einigen Datensätzen gefüllt, auf der Festplatte aus:

    Even though the size of the fixed-length data (pminlen) is specified differently, with 5 and 6 respectively, the free storage on the page (m_freeCnt) is identical! (red)

    The record, however, uses in both cases 9 bytes in the page body. (blue)

    This is what the table, consisting of one column, filled up with a few records, looks like on the hard drive:

     

      Page_Record_1col9byte_hex

     

    Man sieht, dass 9 Bytes belegt sind, jedoch nur, da nach der NULL Bitmap noch ein Byte jedem Datensatz anhängig ist (gelb markiert).

    Hier die Tabelle mit 2 Spalten:

    One can see that 9 bytes are used, but only because after the NULL bitmap, one extra byte is attached to each data set (marked in yellow).

    Below, see the table with 2 columns:

     

     1410_Page_Record_2cols9byte_hex.png

     

    Auch hier also 9 Bytes, mit dem Unterschied, wie das letzte Byte verwendet wird.

    Noch etwas deutlicher wird es im Binärformat. Das ist die Tabelle mit 2 Spalten – auch diese benötigt 9 Bytes, und man sieht unten, wie die NULL Bitmap zu ihrem Namen kommt:

    Here, too, it is 9 bytes, with the difference being how the last byte is used.

    It becomes a bit clearer in the binary format.
    This is the table with 2 columns –this one uses 9 bytes as well, and you can see below how the NULL bitmap gets its name:

     

    Page_Record_2cols9byte_binary

     

    Dieser eine Byte, der für mich keinen klaren Nutzen hat, führt also zu dem Ergebnis, das beide Tabellen letztlich 9 Bytes je Record auf der Festplatte benötigen.

    This one byte, which to me does not have any clear purpose, is what leads to the result that both tables ultimately use 9 bytes per record on the hard drive.

     

    Die minimale Datensatzgröße ist daher in der Tat 9 Bytes. Der zweitgrößte Datensatz ist jedoch auch 9 Bytes. :-D

    Dabei darf beliebig gewählt werden zwischen 2 Spalten à 1 Byte oder 1 Spalte à 2 Bytes :-)
    Daher die Liste:

    The minimal record size is thus in fact 9 bytes. The second biggest record, however, is also 9 bytes. :-D

    Here, one may freely choose between 2 columns à 1 byte or 1 column à 2 bytes :-).
    Hence the list:

     

    • bit
    • char(1)
    • binary(1)
    • tinyint
    • char(2)
    • nchar(1)
    • binary(2)
    • smallint

     

    Kommen wir nun zu Part 2:

    Wie viele Datensätze passen maximal auf eine Datenseite, wenn wir jetzt wissen, dass jeder Datensatz mindestens 9 Bytes + Offset benötigt?

    Let us now look at Part 2:

    How many records fit maximally on a data page if we now know that every data set requires a minimum of 9 bytes + offset?

    Wir testen mit einer Tabelle, bestehend aus einer Spalte mit Datentyp tinyint – wohlwissend, dass es dasselbe Ergebnis bringen würde, wenn wir smallint oder etwas anderes aus der Liste oben nehmen würden.

    We are testing with one table of one column with datatype tinyint – knowing full well that it would have the same outcome if we were to take smallint or something else from the list above.

     

    CREATE TABLE T3_Tinyint
    (col tinyint NOT NULL)
    GO
    …Insert 700 Rows…

    Sehen wir uns an, wie voll die Datenseite geworden ist, und welche Page_ID diese hat:

    Then, we will check again how full the data page has become, and which Page_ID it has:

     

     Row_Per_Page_Stage1_700

     

    Ausgehend davon, dass eine Datenseite 8192 Bytes groß ist und abzüglich Header 8096 Bytes für Daten zur Verfügung stehen, bedeutet ein Füllgrad von ~95,107%, das noch gut ~396 Bytes zur Verfügung stehen. Durch 11 ergibt das 36. - 36 Datensätze haben also noch Platz!

    Was sagt DBCC Page dazu?

    Based on the fact that a data page is 8192 bytes in size and that, less the header, 8096 bytes are available for data, a fill degree of ~95,107% means that some ~396 are still available. Divided by 11 this equals 36 – there is still room for 36 records!

    What does DBCC Page have to say to this?

     

     DBCCPage_Row_Per_Page_Stage1_700_Page_Header

     

    Auch hier: 396 Bytes frei – na dann war unsere Überschlagsrechnung gar nicht so schlecht. :-)

    Das heißt rein rechnerisch müssten weitere 36 Datensätze auf die Seite passen.
    Also „rauf damit“.

    396 bytes free – well, then, our back-of-the-envelope calculation wasn’t so bad at all. :-)

    That is to say that in purely arithmetical terms, a further 36 records should fit on the page.
    So “let’s put them on.”

     

    …Insert 36 Rows…

     

    Row_Per_Page_Stage2_2Pages

     

    2 Seiten, direkt ab dem 701. Datensatz.
    Was ist da los?
    Im Hex Editor betrachten wir „das Grauen“:

    2 pages, directly from the 701st data set.
    What is going on there?
    In the hex editor, we are looking at “the horror”:

     

     Tab_Footer_Offset_FreeSpace_hex

     

    Freier Platz! – Fragmentierung, „Igitt“ ;-)
    Was machen wir da?
    Ein Rebuild der Tabelle, was sonst.

    Free space! – Fragmentation, „yuck“ ;-)
    What to do?
    A rebuild of the table, what else.

     

    Row_Per_Page_Stage3_736

     

    Und schon ist die Seite zu glatten 100% gefüllt: 736 Datensätze.
    Und das sagt der Header:

    And just like that, the page is filled with a sleek 100%: 736 records.
    And this is what the header says:

     

     DBCCPage_Row_Per_Page_Stage3_736_Page_Header

     

    Ja, die Page_ID ist eine andere – aber nicht, weil ich gemogelt hätte, sondern weil die Storage Engine für den Rebuild neuen Platz reserviert, und den alten nach getaner Arbeit freigegeben hat.

    Und auch auf Platte sieht es jetzt so aus, wie es sein sollte – bis zum Ende aufgefüllt:

    Yes, the Page_ID is a different one – but not because I might have cheated, but because the storage engine allocated new space for the rebuild, and released the old one after the done work.

    On the drive, too, it now looks exactly how it’s supposed to – filled up to the end:

     

     Tab_Footer_Offset_Full_hex

     

    Übrigens: Wenn ich anstelle der Heap-Speicherung der Tabelle einen Clustered Index als Struktur gewählt hätte, wären die Daten in den allermeisten Fällen sofort auf der einen Seite zu 100% abgelegt worden
    – aber was tut man nicht alles für die Spannungskurve :-)

    Ansonsten gilt auch hier die Regel: „Niemals auf etwas verlassen, was man nicht selbst getestet hat“ ;-)

    By the way, had I chosen a Clustered Index as structure instead of the Heap-storage, in most cases, the data would have been stored immediately to a 100% on the one page
    – but what’s the fun in that! :-)

    Otherwise, the rule also applies here: “Never rely on something you haven’t tested yourself” ;-)

     

    Ergebnis:
    Die Antwort auf die Frage lautet daher nicht 700, nicht 732, oder gar 809, sondern: 736 Datensätze passen maximal auf eine Seite.

    - und dabei macht es noch nicht einmal einen Unterschied, ob man eine Spalte mit 1 oder 2 Bytes Größe, oder gar 2 Spalten mit je einem Byte Größe verwendet.

    Da jeder Datensatz 9 Bytes zzgl. 2 Bytes Record Offset benötigt, haben wir damit die maximale Kapazität einer SQL Server Datenseite mit 8096 Bytes exakt ausgereizt! :-)

    - Nicht zu verwechseln mit der maximalen Zeilenlänge von 8060 Bytes.

    Result:
    The answer to this question, therefore, is not 700, not 732, or even 809, but: a maximum of 736 data records fits on one page.

    - and it does not even make a difference, if one uses one column with 1 or 2 bytes in size, or even 2 columns with one byte in size.

    Since every data record uses 9 bytes plus 2 bytes record offset we will have exactly exhausted the maximum capacity of an SQL Server data page with 8096 bytes! :-)

    - Not to be confused with the maximal row length of 8060 bytes.

     

    my nine bytes

    Andreas

    Christoph Müller-Spengler: Migrate SharePoint Database from SQL Server 2012 backwards to SQL Server 2008 R2

    Last week we were faced with the situation that we had to deploy a Database for SharePoint from another Service Provider.
    No problem at all, just download the Backup-File, Restore it to the SQL Server Instance and mount it into SharePoint.

    Simply: NO.

    In our case we were provided with a SQL Server 2012 Database. It’s not possible to restore a Database from a SQL Server Instance running “SQL Server 2012″ into a SQL Server Instance running “SQL Server 2008 R2″.

    But we had to find a solution to this problem.

    First thought was to choose
    Database -> right mouse click -> Tasks -> Export data tier.

    So we tested it with an existing database in a SQL Server Instance running SQL Server 2012. But we had no luck, the database contained views that referenced objects from system databases.

    So we tried with another database. Still no luck as this one used extended properties, the ones that are used to describe what specific use this very column in that specific table has.

    But there had to be a way to solve the problem.

    That’s where the SQL Database Migration Wizard comes into play.
    SQL Database Migration Wizard is a tool that you can download from Codeplex: https://sqlazuremw.codeplex.com/.
    Fun fact: The name still references an older name of SQL Database. For the full history of names go to http://en.wikipedia.org/wiki/SQL_Azure.

    Be careful to download the corresponding version to your SQL Server version:

    • v3x: SQL Server 2008 R2 SP1
    • v4x: SQL Server 2012
    • v5x: SQL Server 2014

    Just download the right version for you, copy it to your Management Machine and start it from “SQLAzureMW.exe”.
    The Wizard itself is really self-explanatory and there are a dozen blog posts out there how to use this wizard,
    e.g. Grant Fritchey’s from May 2011: SQL Azure Migration Wizard to migrate OnPremise Databases to Microsoft Azure
    or by Sascha Dittmann as part of a Backup strategy from Azure to OnPremise: Datensicherung unter SQL Azure
    As you can see, all directions are possible, even
    Azure -> Azure
    and
    OnPremise -> OnPremise.
    That’s what makes the SQL Database Migration Wizard so powerful.

    As mentioned in the Blog post title we had to deal with a SharePoint Database that we wanted to “restore” on another OnPremise SQL Server Instance.

    This is where i want to point out the secrets that you have to be aware of in this very special case.

    Disk where you start the SQL Database Migration Wizard from

    Make sure that you place the SQL Database Migration Wizard on a drive that has at least double the amount of free space available of the database you want to migrate. This is because SQL Database Migration Wizard creates some temporary objects along with the scripted data. You do not want your SQL Server Instance to stop because you placed the SQL Database Migration Wizard on your C: drive and run out of space!

    NotSupportedByAzureFile.Config

    By default this file contains all that stuff that SQL Database is not capable of or simply does not support,
    e.g. Fill Factor with Indexes, BULK INSERT and so on.

    As you know that your OnPremise SQL Server Instance supports everything, you just have to comment all the exceptions (in theory). Under pressure i was not even able to build a valid XML file so i erased all exceptions and came up with a file like this:

    <?xml version="1.0"?>
    <TSQLNotSupportedByAzure xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <DefaultMessage>{0} is not supported in current version of SQL Azure</DefaultMessage>
        <Skip>
            <SupportedStatement Text="SET ANSI_NULLS ON" />
            <SupportedStatement Text="SET QUOTED_IDENTIFIER ON" />
        </Skip>
        <Table>
            <TableStatement>
                </TableStatement>
        </Table>
        <Index>
            <IndexOptions>
            </IndexOptions>
        </Index>
        <Schema>
            <SchemaChecks>
                </SchemaChecks>
        </Schema>
        <View>
            <ViewStatement>
            </ViewStatement>
        </View>
        <GeneralTSQL>
            </GeneralTSQL>
        <ActiveDirectorySP>
            </ActiveDirectorySP>
        <BackupandRestoreTable>
        </BackupandRestoreTable>
        <ChangeDataCapture>
        </ChangeDataCapture>
        <DatabaseEngineSP>
        </DatabaseEngineSP>
        <DatabaseMailSP>
        </DatabaseMailSP>
        <DatabaseMaintenancePlan>
        </DatabaseMaintenancePlan>
        <DataControl>
        </DataControl>
        <DistributedQueriesSP>
        </DistributedQueriesSP>
        <FullTextSearchSP>
        </FullTextSearchSP>
        <GeneralExtendedSPs>
        </GeneralExtendedSPs>
        <IntegrationServicesTable>
        </IntegrationServicesTable>
        <LogShipping>
        </LogShipping>
        <MetadataFunction>
        </MetadataFunction>
        <OLEAutomationSP>
        </OLEAutomationSP>
        <OLEDBTable>
        </OLEDBTable>
        <ProfilerSP>
        </ProfilerSP>
        <ReplicationSP>
        </ReplicationSP>
        <ReplicationTable>
        </ReplicationTable>
        <RowsetFunction>
        </RowsetFunction>
        <SecurityFunction>
        </SecurityFunction>
        <SecuritySP>
        </SecuritySP>
        <SQLMailSP>
        </SQLMailSP>
        <SQLServerAgentSP>
        </SQLServerAgentSP>
        <SQLServerAgentTable>
        </SQLServerAgentTable>
        <SystemCatalogView>
        </SystemCatalogView>
        <SystemFunction>
        </SystemFunction>
        <SystemStatisticalFunction>
        </SystemStatisticalFunction>
        <Unclassified>
        </Unclassified>
    </TSQLNotSupportedByAzure>
    

    SQLAzureMW.exe.config

    This configuration file does contain not only the L10N strings for the GUI, but also some Application Settings, starting with line 207 ;-)

    DBCollation

    The most important key that you have to specify is

    <add key="DBCollation" value="Latin1_General_CI_AS_KS_WS"/>
    

    because SharePoint needs this collation for all of it’s databases.
    If you do not specify the DBCollation, the SQL Database Migration Wizard will simply not set this property for the Database and therefore the Default Collation of the SQL Server Instance will be set for the Database that you migrate. Trust me, i have done this once and will never forget about it, as a 30 GB Database will take some time to migrate :-/

    BCPFileDir

    Another important key is

    <add key="BCPFileDir" value="E:\BCPData"/>
    

    because this is where your scripted data will reside and surely you remember that you do not want to fill up your system drive ;-)

    Let’s do the migration

    Once you payed attention to these important points, you can start the SQL Database Migration Wizard by executing the exe file.

    • On the first screen hit the radio button “Analyze / Migrate: Database” and click “Next >”.
    • Enter the Source-SQL Server Instance information along with your Authentication credentials, specify the Source Database by it’s name and click “Connect”.
    • Select the Database and click “Next >”
    • Hit the radio button “Script all database objects” and click “Next >”
    • The “Script Wizard Summary” just shows what it’s named for. Click “Next >”
    • You are prompted with the Question: “Ready to generate SQL script?” Click “Yes” and help yourself to some coffee ;-)
    • After a few seconds or some hours, the blue progress bar on top will hit the right border of the wizard and below the progress bar the simple hint “Done!” will appear. You now have the possibility to save the logged information to your hard disk.
    • Click “Next >”
    • Enter the Destination-SQL Server Instance information along with your Authentication credentials, leave the radio button next to “Master DB (List all databases) and click “Connect”.
    • You might now select an existing Database that you want to migrate your Source Database into. Otherwise click “Create Database”
    • Enter the Database Name and be lucky, that you have specified the “SharePoint Collation” in the file “SQLAzureMW.exe.config” because you do not really want to scroll down the whole list of available Collations and pick the right one by accident ;-). Click “Create Database”.
    • Click “Next >”.
    • You are prompted with the question: “Execute the script against destination server”? Of course you want to: click “Yes”.
    • Once again the blue progress bar flushes the screen and you read the four letters “Done!”.
    • Again you have the chance to save the logged information to your hard disk.
    • As you have reached the final screen of the Wizard, klick “Exit”.

    Now you have migrated a database running on a SQL Server 2012 Instance into a SQL Server 2008 Instance.

    Be careful!

    Technically i bet you can “downgrade” a Database from SQL Server 2014 to SQL Server 2005. But keep in mind that from one version to the next some things might have changed within SQL Server.
    SQL Server is for good reason not backward compatible.
    Some names of DMVs might have changed, the call of RAISERROR has changed.
    I even think that it’s not possible to migrate assemblies that you have written in C# and deployed into SQL Server using the CLR. This is not proven, just a thought.
    So please be careful and test all of the functionality that the Application that uses the Database provides is still working.

    I hope you enjoyed this blog post,
    Thanks for reading, Christoph


    Uwe Ricken: SCAN, SEEK und PARALLELITÄT in REALITÄT

    Dieser Beitrag ist nicht ganz ernst gemeint und soll nur eine Brücke schlagen zwischen der eher langweiligen Theorie bei der Abfrage von Daten und der Realität; also viel Spaß beim Lesen!

    Ich habe die Ehre, am Samstag, den 24.10.2014 auf dem ersten SQL Saturday Spaniens in Barcelona zu sprechen. Mit einer sehr geschätzten internationalen Kollegin – Jen Stirrup (w | t) aus Schottland – wurden wir vom Orgateam am Flughafen empfangen, um uns zum Hotel zu fahren.

    SQLSATURDAY_BARCELONA

    DAFÜR ERST EINMAL EIN GROSSES DANKE SCHÖN AN DIE ORGANISATOREN!

    Nun sollte es sich begeben, dass unser Fahrer Ruben Pertusa Lopez (b | t) das Auto im Parkhaus des Flughafens Barcelona abgestellt hatte aber nicht mehr wusste, wo :)!

    SEEK?

    An ein SEEK war überhaupt nicht mehr zu denken, da wir nicht mehr gezielt zum Auto gehen konnten. Also blieb uns nichts anderes über als mit einem

    SCAN...

    durch die Reihen zu gehen, um nach dem Auto zu suchen. Jen Stirrup als BI Spezialistin folgte brav den Experten der Database Engine (schließlich ist die BI ja von den Daten aus der Database Engine abhängig). Irgendwann während des Scanvorgangs (wir sind alle gemeinsam durch die Reihen gelaufen) kam mir eine MASTER-Idee: “Es sind zu viele Autos für einen single thread; lasst uns ...

    PARALLELISIEREN

    mit insgesamt 4 möglichen Threads sind wir dann – jeder jeweils eine ihm zugewiesene Reihe von Autos – gemeinsam durch das Parkhaus gelaufen, bis das Auto gefunden wurde. Der Task konnte aber erst erfolgreich abgeschlossen werden, als alle Cores mit ihrer Arbeit fertig waren und wir uns gemeinsam im Auto davon machten. Ein winzig kleiner CXPACKET Wait Stat war das Ergebnis unseres Tasks. Die Daten (Jen und ich) wurden erfolgreich im Hotel abgeliefert – ein großes Danke an Ruben!

    SQL Saturday in Barcelona

    Es freut mich sehr, dass ich auf dem ersten SQL Saturday überhaupt in Spanien mit einem Vortrag ausgewählt wurde. Ich werde über die Interna von DML (INSERT/UPDATE/DELETE) sprechen und dabei mein Lieblingsthema – die Database Engine – im Detail behandeln.

    Jedem interessierten SQL Experten kann ich nur empfehlen, auch mal einen SQL Saturday zu besuchen; das wunderbare am SQL Saturday ist, dass er dank Sponsoren kostenlos ist! Alle anstehenden Events können auf http://www.sqlsaturday.com eingesehen werden. Ich bin der Meinung, dass es sich lohnt!. Normalerweise kosten Expertenseminare einen Haufen Geld – hier bieten die Sprecher aus ganz Europa ihr Know How kostenlos an! Und das wichtigste beim SQL Saturday überhaupt ist, dass man überaus hilfsbereite, nette und wunderbare Menschen trifft, die alle die gleiche Passion haben – Microsoft SQL Server! In diesem Sinne...

    “¡hola de Barcelona”

    Herzlichen Dank fürs Lesen!

    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 Müller-Spengler: Windows Server 2012, .NET Framework 3.5, 0x800F0906, 0x800F081F

    This week i tried to install SQL Server 2012 on a freshly provided Windows Server 2012.

    During the setup the error message opened that

    “… computer cannot download the required files from Windows Update”

    So i tried to add the .NET 3.5 Feature from Server Manager, but that even did not work out. Once again i was prompted with the above mentioned error message.

    To make a long story short, if you google the buzz words from the title of this Blog post, you will see a whole bunch of other Blog posts or questions and answers on stackoverflow.com etc pp. All this stuff did not work out. At least a colleague of mine found the solution on this Blog:

    http://consulting.risualblogs.com/blog/2012/07/04/enabling-net-3-5-on-server-2012-rp-source-files-not-found/

    There is also another requirement to install the .NET 3.5 ServerFeatures component before .NET 3.5 can be installed. To do this you need to run the following DISM commands;


    DISM.EXE /Online /Enable-Feature /FeatureName:NetFX3ServerFeatures /Source:Z:\Sources\SXS

    DISM.EXE /Online /Enable-Feature /FeatureName:NetFX3 /Source:Z:\Sources\SXS

    … where Z is the drive letter of the Server 2012 Media.

    Sometimes it’s just that easy.
    When you know it.

    Now you know it too ;-)

    Thank’s for reading


    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!

    Falk Krahl: Neues Servicepack für SQL Server 2008

    Gestern wurde für den SQL Server 2008 ebenfalls ein neues Servicepack bereit gestellt. Es handelt sich dabei um das Servicepack 4. Es kann unter folgendem Link herunter geladen werden.
    SQL Server 2008 SP4

    Christoph Müller-Spengler: Is SQL Server under high CPU pressure?

    Sometimes we get a phone call, a trouble ticket or just a visit from a colleague telling us

    “The application xy is slow, what the heck is going on with the underlying SQL Server?”

    This is the only information that we are provided with – so we have to investigate what’s really going on with SQL Server.
    We even have to prove that it is SQL Server that makes the app so slow, or we can prove that it is not SQL Server.

    There are plenty of ways to find out what’s going on, some people start with the amazing script by BrentOzar sp_AskBrent®, some rely on the Wait Statistics of SQL Server, you even might visit the server via Remote Desktop and have a look at the Task Manager. But how can you tell that the high green line in the CPU box is caused by SQL Server?

    For me it would be handy to have a script that i can execute directly in SQL Server Management Studio that would tell me what’s going on. It would tell me from the insights of SQL Server if there is something going wrong.

    In todays blog post i focus on just CPU pressure. We all know there are far more reasons why SQL Server could be slow, but let’s stick with CPU for now.

    Starting with a script taken from the eBook
    “Troubleshooting SQL Server A Guide for the Accidental DBA” by Jonathan Kehayias and Ted Krueger
    we find a script that shows us the CPU pressure via the signal wait time.

    The signal wait time is the time that is measured within SQL Server’s DMVs when a task that is runnable and has all ressources available (e.g. the data pages are read from disk into the Buffer Pool) is waiting for the CPU to become available for him. So the signal wait time is pure CPU wait time.

    So here’s the script from the eBook:

    SELECT  SUM(signal_wait_time_ms) AS TotalSignalWaitTime ,
            ( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))
              / SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 ) AS PercentageSignalWaitsOfTotalTime
    FROM    sys.dm_os_wait_stats
    -- (Page 79, Listing 3.1: Verifying CPU Pressure via signal wait time.)
    

    This might show you a result like this:

    TotalSignalWaitTime

    You might be impressed: Wow, SQL Server is waiting about 20% of the time for the CPU to become available for the next runnable task…

    The interesting thing here is, that even on a SQL Server box that is sitting around bored, the results may be nearly the same. So i came across a blog post by Paul S. Randal:
    Wait statistics, or please tell me where it hurts
    In this blog post he clarifies:

    “Bunch of waits are being filtered out of consideration, waits happen all the time and these are the benign ones we can usually ignore.” (Paul S. Randal)

    So i added the WHERE clause to the upper SELECT statement and got the following script:

    SELECT  SUM(signal_wait_time_ms) AS TotalSignalWaitTime ,
            ( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))
              / SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 ) AS PercentageSignalWaitsOfTotalTime_WithoutBenignWaits
    FROM    sys.dm_os_wait_stats
    WHERE	[wait_type] NOT IN (
    			N'BROKER_EVENTHANDLER',             N'BROKER_RECEIVE_WAITFOR',
    			N'BROKER_TASK_STOP',                N'BROKER_TO_FLUSH',
    			N'BROKER_TRANSMITTER',              N'CHECKPOINT_QUEUE',
    			N'CHKPT',                           N'CLR_AUTO_EVENT',
    			N'CLR_MANUAL_EVENT',                N'CLR_SEMAPHORE',
    			N'DBMIRROR_DBM_EVENT',              N'DBMIRROR_EVENTS_QUEUE',
    			N'DBMIRROR_WORKER_QUEUE',           N'DBMIRRORING_CMD',
    			N'DIRTY_PAGE_POLL',                 N'DISPATCHER_QUEUE_SEMAPHORE',
    			N'EXECSYNC',                        N'FSAGENT',
    			N'FT_IFTS_SCHEDULER_IDLE_WAIT',     N'FT_IFTSHC_MUTEX',
    			N'HADR_CLUSAPI_CALL',               N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
    			N'HADR_LOGCAPTURE_WAIT',            N'HADR_NOTIFICATION_DEQUEUE',
    			N'HADR_TIMER_TASK',                 N'HADR_WORK_QUEUE',
    			N'KSOURCE_WAKEUP',                  N'LAZYWRITER_SLEEP',
    			N'LOGMGR_QUEUE',                    N'ONDEMAND_TASK_QUEUE',
    			N'PWAIT_ALL_COMPONENTS_INITIALIZED',
    			N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
    			N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
    			N'REQUEST_FOR_DEADLOCK_SEARCH',     N'RESOURCE_QUEUE',
    			N'SERVER_IDLE_CHECK',               N'SLEEP_BPOOL_FLUSH',
    			N'SLEEP_DBSTARTUP',                 N'SLEEP_DCOMSTARTUP',
    			N'SLEEP_MASTERDBREADY',             N'SLEEP_MASTERMDREADY',
    			N'SLEEP_MASTERUPGRADED',            N'SLEEP_MSDBSTARTUP',
    			N'SLEEP_SYSTEMTASK',                N'SLEEP_TASK',
    			N'SLEEP_TEMPDBSTARTUP',             N'SNI_HTTP_ACCEPT',
    			N'SP_SERVER_DIAGNOSTICS_SLEEP',     N'SQLTRACE_BUFFER_FLUSH',
    			N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
    			N'SQLTRACE_WAIT_ENTRIES',           N'WAIT_FOR_RESULTS',
    			N'WAITFOR',                         N'WAITFOR_TASKSHUTDOWN',
    			N'WAIT_XTP_HOST_WAIT',              N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
    			N'WAIT_XTP_CKPT_CLOSE',             N'XE_DISPATCHER_JOIN',
    			N'XE_DISPATCHER_WAIT',              N'XE_TIMER_EVENT')
    AND		[waiting_tasks_count] > 0
    

    Executing that against the very same SQL Server Instance, i got the following result:

    TotalSignalWaitTime_WithoutBenignWaits

    Oh wait – this reduced the real wait for CPU to a bit more than 3% Signal Wait Time.
    So from this point of view, this SQL Server Instance could not be under high CPU pressure.

    So i executed the script without Paul’s benign waits against a server that i know is under CPU pressure:

    BothSignalWaits

    Now our Percent of Signal Wait Time Without Benign Waits is even higher than the Total Percent Value. That means that the “bad” waits should be taken even more into account as these Waitings take relatively longer than All Waits.
    So this must be a very good indicator for high CPU pressure.
    It’s just that easy: Just substract the value without the benign waits from the total value – if you get a negative value, there you are: This really must be a SQL Server under high CPU pressure.

    This leads us to the following script:

    DECLARE @PercentageSignalWaitsOfTotalTime FLOAT
    DECLARE @PercentageSignalWaitsOfTotalTime_WithoutBenignWaits FLOAT
    
    SELECT		@PercentageSignalWaitsOfTotalTime = (( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))
                / SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 ))
    			, @PercentageSignalWaitsOfTotalTime_WithoutBenignWaits = 
    			  (SELECT		
    							( SUM(CAST(PaulR.signal_wait_time_ms AS NUMERIC(20, 2)))
    							/ SUM(CAST(PaulR.wait_time_ms AS NUMERIC(20, 2))) * 100 )
    				FROM		sys.dm_os_wait_stats PaulR
    				WHERE		PaulR.wait_type NOT LIKE '%SLEEP%' -- remove eg. SLEEP_TASK and
    				-- LAZYWRITER_SLEEP waits
    				AND			PaulR.wait_type NOT LIKE 'XE%'	 -- remove Extended Events
    				AND			PaulR.wait_type NOT IN (			 -- remove system waits
    						N'BROKER_EVENTHANDLER',             N'BROKER_RECEIVE_WAITFOR',
    						N'BROKER_TASK_STOP',                N'BROKER_TO_FLUSH',
    						N'BROKER_TRANSMITTER',              N'CHECKPOINT_QUEUE',
    						N'CHKPT',                           N'CLR_AUTO_EVENT',
    						N'CLR_MANUAL_EVENT',                N'CLR_SEMAPHORE',
    						N'DBMIRROR_DBM_EVENT',              N'DBMIRROR_EVENTS_QUEUE',
    						N'DBMIRROR_WORKER_QUEUE',           N'DBMIRRORING_CMD',
    						N'DIRTY_PAGE_POLL',                 N'DISPATCHER_QUEUE_SEMAPHORE',
    						N'EXECSYNC',                        N'FSAGENT',
    						N'FT_IFTS_SCHEDULER_IDLE_WAIT',     N'FT_IFTSHC_MUTEX',
    						N'HADR_CLUSAPI_CALL',               N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
    						N'HADR_LOGCAPTURE_WAIT',            N'HADR_NOTIFICATION_DEQUEUE',
    						N'HADR_TIMER_TASK',                 N'HADR_WORK_QUEUE',
    						N'KSOURCE_WAKEUP',                  N'LAZYWRITER_SLEEP',
    						N'LOGMGR_QUEUE',                    N'ONDEMAND_TASK_QUEUE',
    						N'PWAIT_ALL_COMPONENTS_INITIALIZED',
    						N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
    						N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
    						N'REQUEST_FOR_DEADLOCK_SEARCH',     N'RESOURCE_QUEUE',
    						N'SERVER_IDLE_CHECK',               N'SLEEP_BPOOL_FLUSH',
    						N'SLEEP_DBSTARTUP',                 N'SLEEP_DCOMSTARTUP',
    						N'SLEEP_MASTERDBREADY',             N'SLEEP_MASTERMDREADY',
    						N'SLEEP_MASTERUPGRADED',            N'SLEEP_MSDBSTARTUP',
    						N'SLEEP_SYSTEMTASK',                N'SLEEP_TASK',
    						N'SLEEP_TEMPDBSTARTUP',             N'SNI_HTTP_ACCEPT',
    						N'SP_SERVER_DIAGNOSTICS_SLEEP',     N'SQLTRACE_BUFFER_FLUSH',
    						N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
    						N'SQLTRACE_WAIT_ENTRIES',           N'WAIT_FOR_RESULTS',
    						N'WAITFOR',                         N'WAITFOR_TASKSHUTDOWN',
    						N'WAIT_XTP_HOST_WAIT',              N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
    						N'WAIT_XTP_CKPT_CLOSE',             N'XE_DISPATCHER_JOIN',
    						N'XE_DISPATCHER_WAIT',              N'XE_TIMER_EVENT'))
    FROM		sys.dm_os_wait_stats
    
    SELECT		@PercentageSignalWaitsOfTotalTime - @PercentageSignalWaitsOfTotalTime_WithoutBenignWaits AS [IfNegativeThenCPUPressure]
    			, @PercentageSignalWaitsOfTotalTime AS [PercentageSignalWaitsOfTotalTime]
    			, @PercentageSignalWaitsOfTotalTime_WithoutBenignWaits AS [PercentageSignalWaitsOfTotalTime_WithoutPaulRandalsBenignWaits]
    
    

    Executed against the SQL Server it puts this result to the grid:

    IfNegativeThenCPUPressure

    Hey, minus 20% is a big deal, isn’t it?

    But wait!
    Let’s double check it with SQL Servers we know are just sitting around bored.

    BothSignalWaitsOnBoredSQLServer

    Hmmm, following my own thoughts how to determine if a SQL Server is under high CPU pressure or not, this would lead to “False Positives”. So we have to set a threshold.

    The following article from the Technet Magazine SQL Server: SQL Server Delays Demystified as an excerpt from “SQL Server DMV Starter Pack,” published by Red Gate Books (2010), written by Glenn Berry, Louis Davidson and Tim Ford, states that

    The key metric, with regard to potential CPU pressure, is the signal wait as a percentage of the total waits. A high-percentage signal is a sign of excessive CPU pressure. The literature tends to quote “high” as more than about 25 percent, but it depends on your system.

    On our systems, we treat values greater than 10 percent to 15 percent as a worrying sign. Overall, the use of wait statistics represents a very effective means of diagnosing response times in your system. In simple terms, you either work or you wait. Response time equals service time plus the wait time.

    Applying the threshold of 15 percent for the Percentage of Signal Waits of Total Time i end up with this script:

    DECLARE @PercentageSignalWaitsOfTotalTime FLOAT
    DECLARE @PercentageSignalWaitsOfTotalTime_WithPaulRandalsExclusions FLOAT
    Declare @SubstractedValue FLOAT
    
    SELECT		@PercentageSignalWaitsOfTotalTime = (( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))
                / SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 ))
    			, @PercentageSignalWaitsOfTotalTime_WithPaulRandalsExclusions = 
    			  (SELECT		
    							( SUM(CAST(PaulR.signal_wait_time_ms AS NUMERIC(20, 2)))
    							/ SUM(CAST(PaulR.wait_time_ms AS NUMERIC(20, 2))) * 100 )
    				FROM		sys.dm_os_wait_stats PaulR
    				WHERE		PaulR.wait_type NOT LIKE '%SLEEP%' -- remove eg. SLEEP_TASK and
    				-- LAZYWRITER_SLEEP waits
    				AND			PaulR.wait_type NOT LIKE 'XE%'	 -- remove Extended Events
    				AND			PaulR.wait_type NOT IN (			 -- remove system waits
    						N'BROKER_EVENTHANDLER',             N'BROKER_RECEIVE_WAITFOR',
    						N'BROKER_TASK_STOP',                N'BROKER_TO_FLUSH',
    						N'BROKER_TRANSMITTER',              N'CHECKPOINT_QUEUE',
    						N'CHKPT',                           N'CLR_AUTO_EVENT',
    						N'CLR_MANUAL_EVENT',                N'CLR_SEMAPHORE',
    						N'DBMIRROR_DBM_EVENT',              N'DBMIRROR_EVENTS_QUEUE',
    						N'DBMIRROR_WORKER_QUEUE',           N'DBMIRRORING_CMD',
    						N'DIRTY_PAGE_POLL',                 N'DISPATCHER_QUEUE_SEMAPHORE',
    						N'EXECSYNC',                        N'FSAGENT',
    						N'FT_IFTS_SCHEDULER_IDLE_WAIT',     N'FT_IFTSHC_MUTEX',
    						N'HADR_CLUSAPI_CALL',               N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
    						N'HADR_LOGCAPTURE_WAIT',            N'HADR_NOTIFICATION_DEQUEUE',
    						N'HADR_TIMER_TASK',                 N'HADR_WORK_QUEUE',
    						N'KSOURCE_WAKEUP',                  N'LAZYWRITER_SLEEP',
    						N'LOGMGR_QUEUE',                    N'ONDEMAND_TASK_QUEUE',
    						N'PWAIT_ALL_COMPONENTS_INITIALIZED',
    						N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
    						N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
    						N'REQUEST_FOR_DEADLOCK_SEARCH',     N'RESOURCE_QUEUE',
    						N'SERVER_IDLE_CHECK',               N'SLEEP_BPOOL_FLUSH',
    						N'SLEEP_DBSTARTUP',                 N'SLEEP_DCOMSTARTUP',
    						N'SLEEP_MASTERDBREADY',             N'SLEEP_MASTERMDREADY',
    						N'SLEEP_MASTERUPGRADED',            N'SLEEP_MSDBSTARTUP',
    						N'SLEEP_SYSTEMTASK',                N'SLEEP_TASK',
    						N'SLEEP_TEMPDBSTARTUP',             N'SNI_HTTP_ACCEPT',
    						N'SP_SERVER_DIAGNOSTICS_SLEEP',     N'SQLTRACE_BUFFER_FLUSH',
    						N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
    						N'SQLTRACE_WAIT_ENTRIES',           N'WAIT_FOR_RESULTS',
    						N'WAITFOR',                         N'WAITFOR_TASKSHUTDOWN',
    						N'WAIT_XTP_HOST_WAIT',              N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
    						N'WAIT_XTP_CKPT_CLOSE',             N'XE_DISPATCHER_JOIN',
    						N'XE_DISPATCHER_WAIT',              N'XE_TIMER_EVENT'))
    FROM		sys.dm_os_wait_stats;
    
    SELECT		@SubstractedValue = @PercentageSignalWaitsOfTotalTime - @PercentageSignalWaitsOfTotalTime_WithPaulRandalsExclusions;
    
    SELECT		CASE
    				WHEN @PercentageSignalWaitsOfTotalTime < 15.0  THEN 'no'
                    WHEN @PercentageSignalWaitsOfTotalTime > 15.0
    				     AND @SubstractedValue > 0.0 THEN 'no'
    				WHEN @PercentageSignalWaitsOfTotalTime > 15.0
    				     AND @SubstractedValue < 0.0 THEN 'YES'
    			END AS [IsSQLServerUnderCPUPressure?]
    			, @PercentageSignalWaitsOfTotalTime AS [PercentageSignalWaitsOfTotalTime]
    			, @PercentageSignalWaitsOfTotalTime_WithPaulRandalsExclusions AS [PercentageSignalWaitsOfTotalTime_WithoutPaulRandalsBenignWaits];
    
    

    This is what i get to see if i run this script against a SQL Server under high CPU pressure:

    FinalResult

    Finally we got it! A nearly rock solid indicator that a SQL Server is under high CPU pressure.

    Something i have to say at the very end of this blog post:

    Querying against the Dynamic Management Views of SQL Server means querying against data, that is collected from the last SQL Server Instance Restart. Unless you clear the cache e.g. executing this little script:

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

    which you might do to ensure that you get an isolated set of data from a special given starting point.

    So with a SQL Server Instance, that has been up and running for a long time, you get an average value for this period.
    But from my experience executing the final script against a SQL Server that is currently under high CPU pressure, it will indeed show the ‘YES’ in the column of interest.
    And even if the SQL Server is not under high CPU pressure at the very moment, the DMVs tell you that at the end of the day the SQL Server has been under high CPU pressure. And that is also worth investigating why that happened.

    I hope you enjoyed reading this blog post,
    Thanks
    Christoph

    Other blog posts in that series


    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.

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