Philipp Lenz: Stichtagsinformationen zu einem Datum

uhr(english Version below)

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

 

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

Funktion / Function:

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

Abfrage / Query:

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

Tillmann Eitelberg: Dimension & Metrics

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

Tillmann Eitelberg: SSIS Analytics Version 1.7 beta

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

Torsten Schuessler: #CU package 3 for SQL Server 2014

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

Cumulative update package 3 for SQL Server 2014

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

I wish you a nice day,
tosc

Torsten Schuessler

Christoph Muthmann: Microsoft Security Updates August 2014 (Teil 2)

Diesmal nur ein Hinweis auf die Security Updates für Windows, welche mittlerweile zurückgezogen wurden.

Full story »

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

PowerPivot_LogoEnglish version below

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

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

Hier der vorherige Weg:

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

 

 

  

  

 

  

 

 

 

 

 

Hier kommt nun die SWITCH Funktion ins Spiel.

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

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

Der Grundsyntax von SWITCH:

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

2014-08-18_12-41-07

 

 

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

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

 

 

  

  

 

  

 

 

 

 

 

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

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

The basic syntax of SWITCH:

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

2014-08-18_12-41-07
 

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

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

Testumgebung

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

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

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


Abfragen


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



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


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


EXECUTION_PLAN_01


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


EXECUTION_PROPERTIES_01EXECUTION_PROPERTIES_02




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


Begründung


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


Zusammenfassung


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


Herzlichen Dank fürs Lesen!


Christoph Muthmann: Trace Flag 2453

Nicht von mir getestet, aber als Neuerung in SQL Server 2012 SP2 sicher eine Erwähnung wert: Trace Flag 2453.

Full story »

Tillmann Eitelberg: Google Analytics Sampling Data

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

Christoph Muthmann: Die neusten Bücher der deutschsprachigen MVPs

Nicht nur auf Events, in Blogartikeln, Foren und sozialen Netzwerken geben MVPs ihr tiefgehendes Wissen weiter. Viele teilen ihr Knowhow und ihre Erfahrungen in unzähligen Buch-Veröffentlichungen.

Full story »

Christoph Muthmann: Microsoft Security Updates August 2014

Es dürfte sicher nicht uninteressant sein, einmal einen genaueren Blick auf das Bulletin MS14-044 zu werfen.

Full story »

Christoph Muthmann: Database Engine Tuning Advisor Hinterlassenschaften

Durch einen Thread im Forum von CA für deren Tool ERwin bin ich heute auf einen Umstand gestoßen, der zumindest eine kurze Notiz wert ist.

Full story »

Philipp Lenz: Standardfeldsatz in PowerPivot und PowerView

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

see english version below

Als Datengrundlage dient hier eine Abfrage aus der Adventure Works:

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

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

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

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

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

karte

Weitere Informationen gibt es hier:

English Version

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

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

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

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

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

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

karte

Uwe Ricken: Aufsteigende Indexschlüssel – Performance Killer

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

Problembeschreibung

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

Testumgebung

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

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

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



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

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


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



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

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



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

Testszenario


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



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

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


EXECUTION_PLAN_01


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


STATISTICSPARSER_01


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



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

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



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

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


EXECUTION_PLAN_02


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


STATISTICSPARSER_02


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


EXECUTION_PLAN_03


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


Warum eine geschätzte Anzahl von 1 Datensatz?


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


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



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

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



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

DBCC_STATISTICS_01


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


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


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



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

EXECUTION_PLAN_04


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



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

EXECUTION_PLAN_05


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


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


EXECUTION_PLAN_06


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



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

EXECUTION_PLAN_09


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


Lösungswege


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


Verwendung von Variablen


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



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

EXECUTION_PLAN_07


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



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

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



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

DBCC_STATISTICS_02


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


OPTIMIZE FOR UNKOWN


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


Optimierung des Index


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



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

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



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

EXECUTION_PLAN_08


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


UPDATE STATISTICS manuell ausführen oder als Auftrag implementieren


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


TraceFlag 2371


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


TraceFlag 2389, 2390


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


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


Zusammenfassung


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



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

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


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


Herzlichen Dank fürs Lesen!


Philipp Lenz: Time Dimension

uhr

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

(English Verison)

Als erstes brauchen wir eine Zeit Tabelle:

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

Mit folgendem Script befallen wir die Tabelle:

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

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

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

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

END

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

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

 

English Version

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

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

Now we must populate the table with data:

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

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

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

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

END

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

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

 

Bernd Jungbluth: Seminar - SQL Server Integration Services

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

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

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

Das Leistungsspektrum von SSIS bietet sich aber nicht nur für Business Intelligence an.
SSIS lässt sich immer dann einsetzen, wenn es darum geht, Daten zu importieren, exportieren, transformieren, aufzubereiten, migrieren, konsolidieren oder zu integrieren - ob nun als einfache Datentransfers, Import-/Export-Routinen, ETL-Lösungen oder als komplexe Datenintegrationslösungen.

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

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

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

Die Teilnehmerzahl ist auf 8 Personen begrenzt.

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

Bernd Jungbluth: Neue Version vom Microsoft SQL Server Migration Assistant for Access verfügbar

Microsoft stellt die Version 6 vom SQL Server Migration Assistant for Access zur Verfügung.
http://www.microsoft.com/en-us/download/details.aspx?id=43690

Mit diesem Assistenten lassen sich Access-Datenbanken zu SQL Server-Datenbanken migrieren.
Unterstützt werden Access-Datenbanken ab Version 97 und SQL Server in den Versionen 2005-2014 sowie Azure SQL

Torsten Schuessler: Time to Say Thank You!

Yes, it is SysAdminDay!

July 25, 2014 14th Annual
System Administrator
Appreciation Day

You are welcome:

I wish you ALL a nice SysAdminDay!

CU
tosc 

Bernd Jungbluth: Seminar - SQL Server Reporting Services

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

30. September 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.

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

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

Cumulative Update 1 for SQL Server 2012 SP2

I wish you a nice day,
tosc

Torsten Schuessler

Marcel Franke: Impressions from the SQL Saturday #313

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

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

 

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

SQLSaturday313_Rheinland_449SQLSaturday313_Rheinland_267SQLSaturday313_Rheinland_416SQLSaturday313_Rheinland_423


Filed under: Conferences

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

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

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

The era of Big Data

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

Big Data vs. Data Analytics

image

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

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

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

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

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

New skills and approaches

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

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

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

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

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

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

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

image

The Laboratory

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

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

image_thumb14

The Factory

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

New technologies

image_thumb16

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

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

Summary

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

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

 

References:

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

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

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

http://datasciencebe.com/datalab/

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

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


Filed under: Big Data, Data Analytics, Data Science

Uwe Ricken: Verwendung von Variablen statt Literalen

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

Ausgangsfrage

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

Testszenario

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

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



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

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


Die prozentuale Verteilung wird durch das generierte IO untermauert:



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

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


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


 


 

ExecutionProperties_02

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



Statistiken – der Schlüssel für optimale Abfragen

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

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

DBCC_SHOW_STATISTICS_01

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

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

Verwendung von Literalen

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

Verwendung von Variablen

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

Frage – Antwort – Fehler


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


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


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


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


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


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


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



Herzlichen Dank fürs Lesen!


Bernd Jungbluth: Seminar - Migration Access nach SQL Server

Es gibt einen neuen Termin für das Seminar Migration Access nach SQL Server:

23. September 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.

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

Bernd Jungbluth: SELECT * FROM [2014 FIFA World Cup Brazil]

Glückwunsch zum 4. Stern :-)

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

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

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

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

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

 

 

 

 

 

pq_3

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

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

Nun müssen beide Tabellen miteinander verbunden werden …

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

pq_4

 

 

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

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

pq_6

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

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

 

 

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

 

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

 

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

pq_10

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

 

Robert Panther: Bericht: SQL Saturday in Sankt Augustin

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

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

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


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

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

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

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

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

Rick Kutschera: Ascending Key in SQL 2014

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

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

Christoph Muthmann: Mainstream Support Endet

Vielleicht ist es nicht allen bewusst, aber heute endet der Mainstream Support für einige SQL Server Versionen.

Full story »

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

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

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

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

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

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

    private readonly ObservableCollection bookList = new ObservableCollection();

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

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

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

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

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

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

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

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

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

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

    private Dispatcher dispatcher;

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

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

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

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

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

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

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

Christoph Muthmann: Noch mehr SQL Server MVPs in Deutschland

Ich freue mich über zwei neue SQL Server MVPs und einen wiederernannten!

Full story »

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

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

image

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

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

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

Christoph Muthmann: Eindrücke vom SQLSaturday#313

Heute nur kurz ein paar Fotos, die alle von Dirk Hondong stammen. Vielen Dank an Dirk für die tolle Bildberichterstattung!

Full story »

Robert Panther: Neues Buch zur Optimierung von SQL-Abfragen

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

SQL-Abfragen optimieren

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

Alternativ auch als eBook erhältlich:

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

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

Weitere Infos:


Sascha Lorenz: SQL Server PowerShell Vortrag vom SQLSaturday #313

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

 
 

Sascha Lorenz: SQLSaturday #313 – Rheinland – Und, wie war's dieses Mal so?

Wie die Zeit vergeht. Letztes Wochenende war schon wieder ein SQLSaturday in Deutschland. Wir sind bei #313 angekommen, auch bekannt als #sqlsatrheinland.

Eigentlich war Vieles wie beim letzten SQLSaturday (#230) an der Fachhochschule Bonn-Rhein-Sieg. Super Veranstaltung, alles gut organisiert durch viele freiwillige Helfer.

Das Essen. Es muss erwähnt werden, dass ein SQLSaturday kostenlos für die Teilnehmer ist und nur durch die Sponsoren finanziert wird. Vielen Dank an diese! Wie letztes Jahr auf hohem Uni Niveau.

Meine Session verlief sehr gut und ich hoffe, ich konnte einen guten Einstieg in Windows PowerShell für SQL Server geben. Es hat großen Spaß gemacht! Die Folien habe ich bereits hochgeladen. Folien für PowerShell 101 für SQL Server Admins

Geschenke. Am Ende gab es dann wieder einen Verlosungsmarathon, welchen wir ein wenig optimiert haben. Wir werden besser und schneller! Und viel Spaß mit der Xbox One und Playstation. ;-)

Dieses Jahr gab es am Freitag keine Precons, sondern einen Big Data Hackathon. Dieser wurde genau wie der SQLSaturday sehr gut angenommen.

Vielen Dank an die Organisatoren und die vielen vielen Freiwilligen vor Ort!
Ich freue mich schon auf den nächsten SQLSaturday in Deutschland!

image

Impression vom Big Data Hackathon.

imageimage

Big Data und Cloud Experten Scott Klein und Sascha Dittmann haben den Freitag moderiert.

image

Natürlich gab es auch wieder ein internationales Speaker Dinner ! Wusstet Ihr, dass es Bönnsch gibt?

image

Und dieses Selfie von Niko Neugebauer wird wohl in die Geschichte des SQL Servers eingehen!

Philipp Lenz: Einführung in PowerQuery, Teil 1 von ?

thIn diesem Blog Eintrag möchte ich eine kurze Einführung/Tutorial in das Thema PowerQuery geben. Das ganze wird eine Serie, Teil 1 von ? – ganz in der Manier des Report Viewers. Schauen wir mal wieviele es werden.

In diesem Beitrag schauen wir uns den Import von Daten aus dem Web an und wie die Daten verarbeitet, transformiert und in das Modell geladen werden können. Weiterhin werden 2 Tabellen mit einander verbunden und als eine Liste später in Excel dargestellt. Soviel zum Ziel…

2014-06-28_21-19-21Starten wir auf einer Webseite. Dort liegt eine fiktive Preisliste die außerhalb der eigenen Umgebung liegt. D.h. diese Liste steht weder auf internen SQL Servern o.ä. zur Verfügung und liegt auch nicht unter der eigenen Kontrolle. Dennoch soll diese Preisliste in Excel bzw. PowerPivot verarbeitet werden. Um an solche Daten zu kommen, bietet sich das kostenlose Excel 2013 Addin “PowerQuery” an.

2014-06-28_21-20-19In Excel wechseln wir auf den Ribbon Power Query und laden Daten aus dem Web. Dort wird die URL der Webseite eingegeben.

2014-06-28_21-21-12Nach dem Bestätigen werden uns 2 Tabellen angeboten. Auf der Webseite war schon zu sehen das die erste Tabelle die Preisliste darstellt. Wenn man sich nicht sicher ist, fährt man mit der Maus über die Einträge und bekommt dort schon eine Vorschau.

Mit einem Klick auf Bearbeiten wechselt man in ein eigenes PowerQuery Fenster um die Verarbeitungsoptionen festzulegen.

2014-06-28_21-24-03In dem Bereich “Angewendete Schritte” wird eine Historie geführt. Das erste nach dem Laden ist immer das setzen, dass die erste Zeile die Überschriften der Zellen beinhaltet. Jegliche Schritte können hier manuell gelöscht werden. Da die Spalte Rabatt leider die Daten mit Punkten anstatt einem Komma in der Fließkomma Zahl trennt, muss dies beim Import geändert werden, da ansonsten dies in Excel als Tausender Trennzeichen interpretiert wird. Weiterhin muss das Prozentzeichen entfernt werden. Beim Preis wurde schon automatisch das Euro Zeichen entfernt. Über den Bereich Werte Ersetzen werden diese Änderungen vorgenommen.

2014-06-28_21-24-572014-06-28_21-25-29Weiterhin wird die Spalte noch als Datentyp Zahl definiert. Der ETL Prozess ist somit eigentlich abgeschlossen. Über den Bereich “Einstellungen laden” können nun die Daten nach Excel und/oder PowerPivot (via in Datenmodell laden) übergeben werden.

2014-06-28_21-32-59Nun haben wir aber noch eine zweite Preisliste in einem anderen Format die hier dieser Liste angefügt werden soll. Ein Feature was in PowerPivot schonmal nicht möglich wäre, da wir so nur zwei Tabellen bilden könnten. Diese Preisliste unterscheidet sich in den Spalten. Die Rabatt Spalten fehlen völlig und die Beschreibung wie auch die Preisspalte sind auch noch anders benannt. Dennoch importieren wir einfach über PowerQuery die Daten:

2014-06-28_21-34-212014-06-28_21-35-14

 

 

 

 

 

 

 

Hier benennen wir erstmal die Spalten so wie in der vorherigen Tabelle indem man mit einem Doppelklick auf die Überschriften der Spalten klickt und die vorherigen Werte überschreibt.

2014-06-28_21-37-30Nun fügen wir einfach die beiden fehlenden Spalten noch der Tabelle hinzu damit die beiden Tabellen das gleiche Format/Modell haben. Als Standardwert tragen wir hier einfach eine 0 ein.

2014-06-28_21-40-37Nun nutzen wir das Feature “Abfrage anfügen”. Hier wählen wir die vorherige in PowerQuery verarbeitete Tabelle aus und bestätigen das Fenster mit OK.

Nun sind beide Tabellen in einer zusammengeführt. Über den Aktualisieren Knopf werden auch beide Tabellen aktualisiert, d.h. aus zwei Schritten wird einer.

2014-06-28_21-41-41Wenn nun die Daten nach PowerPivot oder Excel übergeben werden, liegen die Daten in einer Tabelle.

Fazit:
Mächtiges Tool was teils komplexe ETL Prozesse in den Self Service BI Bereich bringt und Anwendern die Möglichkeit bietet, Internet Daten schnell und einfach ohne IT zu verarbeiten.

 

 

 

 

 

Philipp Lenz: PowerBI: Automatisches Aktualisieren von PowerPivot Mappen aus onPremise Umgebungen

PowerBIErgänzend zu den Unterlagen von Microsoft zum Thema Power BI wie man einen Gateway und eine Datenquelle in einer on Premise Umgebung einrichtet, will ich das hier nochmal etwas deutlicher und bebildert beschreiben, da es hier doch einige Stolpersteine gibt …

Kurz vor ab  – wofür ist das ganze Thema gut? Mit Power BI können PowerPivot Arbeitsmappen mit oder ohne PowerView auf einen Office 365 SharePoint Online Server gelegt werden. Bei dem Thema bekommen immer die meisten Angst (“oh je, da sind ja alle meine Daten in der Cloud und was sagt der Datenschützer dazu?”) – ich persönlich halte i.d.R. nur die Daten in den Arbeitsmappen die ich für die Ermittlung der Kennzahlen und dessen Visualisierung benötige – mehr nicht! D.h. darin befindet sich nicht der Name meines Kunden oder dessen Detail Daten, denn sowas brauche ich eher selten in einem Balkendiagramm bei der Visualisierung einer Kennzahl auf einem Dashboard. Sollte ich dennoch solche Daten brauchen, bspw. bei einem Kundenblatt, dann verlasse ich mich auf die geschlossene Auftragsdatenverarbeitungserklärung (kurz ADV) mit Microsoft und das die Daten in Europa liegen und ich dies mit meinem Kunden auch so vereinbart und besprochen haben, aber das soll hier kein Vortrag zum Thema Datenschutz und Datensicherheit in der Cloud werden.

Nun denn, wenn meine Excel Sheets nun in der Cloud liegen, müssen ja auch irgendwie die Daten via der Datenquellen aktualisiert werden. D.h. die Datenquellen liegen in einer lokalen Installation innerhalb meiner Infrastruktur und die Aktualisierung soll möglichst automatisch und außerhalb der Lastzeiten liegen.

Um dies zu realisieren, installiere ich einen Gateway Dienst von Office 365 der auf einem Server innerhalb meiner Infrastruktur ausgeführt wird. Dieser nimmt die Aktualisierung vor indem dieser die Daten aus meiner SQL Server Datenquelle extrahiert und zum PowerPivot Excel Sheet in Office 365 transportiert. Dieser Weg ist m.E. auch recht sympathisch, da die Verbindung aus meiner Umgebung ausgehend und besser kontrollierbar ist. Wir der Gateway Dienst installiert wird, wird recht einfach und gut in diesem Dokument beschrieben. Sobald das Gateway online ist, sieht man dies auf der Power BI Admin Center Webseite:
28-06-_2014_15-21-30Auf dieser Übersichtswebseite sieht man jegliches eingerichtete Gateway. D.h. es können auch mehrere Gateways installiert und betrieben werden um so die Arbeitsmappen mit verschiedenen Quellen zu versorgen. Stand jetzt, funktioniert die automatische Aktualisierung von PowerPivot Mappen nur mit SQL Server Datenquellen. Über den lokalen Gateway Manager (“Microsoft-Datenverbindungsgateway-Konfigurations-Manager”) erhält man auch die nötigen Informationen zum verwendeten Endpunkt in Bezug auf das Protokoll und den Port der selbstverständlich in der Firewall (sofern ausgehende Verbindungen geblockt werden) eingetragen werden muss.

Sobald das Gateway läuft, sollte man zuerst die PowerPivot Mappe sich anschauen und die darin enthaltene Verbindung überprüfen:

28-06-_2014_15-29-34Innerhalb der PowerPivot Mappe wechselt man auf die vorhandenen Verbindungen, dort auf bearbeiten und auf Erweitert. Hier erhält man alle Informationen die in der Datenquelle in Office 365 eingetragen werden müssen (Hinweis, auch wenn fast selbstverständlich, aus aktuellem Anlass: Auf die Servernamen . oder localhost sollte verzichtet werden, wenn der Gateway Server nicht gleich der SQL Server ist …).

Nun trägt man genau diese Informationen in der neuen Datenquelle im PowerBI Admin Center ein:

28-06-_2014_15-35-08Hier sollte man unbedingt darauf achten, dass der Benutzer die entsprechende Berechtigungen erhalten hat der konfiguriert wurde um die Daten zu lesen aber auch das der gleiche Verbindungsanbieter gewählt wurde, der auch in der PowerPivot Mappe angegeben wurde. Sollten irgendwelche Daten abweichen, war meine Erfahrung das die Aktualisierung einfach nicht funktioniert hat – irgendwie auch logisch :-)

Nun kann man die PowerPivot Mappe in die PowerBI Gallerie legen und auch hier die automatische Aktualisierung konfigurieren. Durch die Warn-Mechanismen wird man auch auf fehlerhafte Aktualisierungen aufmerksam gemacht. Sollte eine Aktualisierung nach ca. 10 mal nicht funktionieren, wird automatisch die Aktualisierung deaktiviert, bzw. der Zeitplan.

Klasse finde ich bei Power BI dann auch die Unterstützung der Power BI QA, also das man dem Server fragen stellt und dieser die anhand des Datenmodells beantwortet. Wenn man dies das erstemal verwendet, merkt man wie schlecht häufig die Felder und Tabellen wie auch Measures benannt sind, so das eine Frage dann lautet Anzahl der Anzahl nach Beschreibung … Aber eben auch die Features der Power BI App im Windows Store die kostenlos dazu zählt ist klasse, so bekommen die Adressaten die Power View Berichte der Excel Arbeitsmappen als Touch App auf die Endgeräte. Lediglich der Preis von ca. 32 € pro Nutzer und Monat ist etwas abschreckend, aber schauen wir mal wie sich das entwickelt. Am besten einfach mal selber mit einer Evaluation ausprobieren.

 

Sascha Dittmann: MapReduce Entwurfsmuster - Numerische Aggregation (Standardabweichung 2/2)

MapReduce Entwurfsmuster - DurchschnittswerteIm heutigen Teil meiner kleinen Serie der MapReduce Entwurfsmustern für Microsoft HDInsight dreht es sich erneut um die Berechnung der Standardabweichung und des Medians.
Diesmal werde ich den vorhandenen Algorithmus dahingehend erweitern, dass die Nutzung eines Combiners ermöglicht wird...

Im vergangene Teil dieser Serie hatte ich bereits eine vereinfachtes Beispiel zur Berechnung der Standardabweichung und des Medians vorgestellt.

Das Problem mit dem vorgestellten Algorithmus ist allerdings das er keinen Combiner verwenden kann und dadurch - bei größeren Datenmengen - recht resourcenhungrig wird.

 

Beispiel "Standardabweichung und Median (mit Combiner)"

In der Mapper-Funktion hat sich im Vergleich zum vergangene Teil nichts geändert.
Diese extrahiert weiterhin das CreationData- und Text-Attribut und gibt die Stunde und Zeichenlänge als Key/Value-Pair zurück:

public class MedianStdDevMapper : MapperBase
{
  public override void Map(string inputLine, MapperContext context)
  {
    var parsed = XmlUtils.ParseXml(inputLine);

    if (parsed == null 
        || !parsed.ContainsKey("CreationDate") 
        || !parsed.ContainsKey("Text"))
    {
      context.CoreContext.IncrementCounter(
        "Median / Std. Dev. Mapper", "Invalid Rows", 1);
      return;
    }

    DateTime creationDate;
    if (!DateTime.TryParse(parsed["CreationDate"], out creationDate))
    {
      context.CoreContext.IncrementCounter(
        "Median / Std. Dev. Mapper", "Invalid Creation Dates", 1);
      return;
    }

    var text = parsed["Text"];

    context.EmitKeyValue(
      creationDate.Hour.ToString(CultureInfo.InvariantCulture),
      text.Length.ToString(CultureInfo.InvariantCulture));
  }
}

 

Um die Datenmenge zu verkleinern, aggregiert die Combiner-Funktion die erhaltenen Values vor.
Gleiche Textlängen werden hierbei mit ihrer jeweiligen Anzahl als neue Values zurückgegeben.

public class MedianStdDevCombiner 
  : JsonOutReducerCombinerBase<MedianStdDevData>
{
  public override void Reduce(string key, 
    IEnumerable<string> values, 
    JsonReducerCombinerContext<MedianStdDevData> context)
  {
    var query = values
      .Select(int.Parse)
      .GroupBy(v => v)
      .Select(grp => new MedianStdDevData
      {
        Value = grp.Key,
        Count = grp.Count(),
      });

    foreach (var value in query)
      context.EmitKeyValue(key, value);
  }
}

 

Bei der Combiner-Funktion nutze ich erneut eine POCO-Klasse, sowie eine JSON-Basisklasse, um mir die Datenübertragung zu vereinfachen.

public class MedianStdDevData 
{ 
  public int Value { get; set; } 
  public int Count { get; set; } 
}

 

Bei der Reducer-Funktion müssen die komprimierten Values wieder "ausgepackt" werden, um anschließend den Median, die Varianz, sowie die Standardabweichung berechnen zu können.

public class MedianStdDevReducer 
  : JsonInReducerCombinerBase<MedianStdDevData>
{
  public override void Reduce(string key, 
    IEnumerable<MedianStdDevData> values, 
    ReducerCombinerContext context)
  {
    float sum = 0;
    long totalComments = 0;
        
    var commentLengthCounts = new Dictionary<int, long>();
    foreach (var data in values)
    {
      totalComments += data.Count;
      sum += data.Value * data.Count;
      if (!commentLengthCounts.ContainsKey(data.Value))
        commentLengthCounts.Add(data.Value, data.Count);
      else
        commentLengthCounts[data.Value] += data.Count;
    }

    // calculate median
    double median = 0;
    var medianIndex = totalComments / 2;
    long previousComments = 0;
    var prevKey = 0;
    foreach (var entry in commentLengthCounts.OrderBy(e => e.Key))
    {
      if (previousComments <= medianIndex 
          && medianIndex < previousComments + entry.Value)
      {
        if (totalComments % 2 == 0 && previousComments == medianIndex)
          median = (entry.Key + prevKey) / 2.0f;
        else
          median = entry.Key;
        break;
      }
      previousComments += entry.Value;
      prevKey = entry.Key;
    }

    // calculate standard deviation
    var avg = sum / totalComments;
    var sumOfSquares = commentLengthCounts
      .Sum(entry => Math.Pow(entry.Key - avg, 2) * entry.Value);
    var stdDev = Math.Sqrt(sumOfSquares / (totalComments - 1));

    context.EmitKeyValue(
      key,
      String.Format("{0}\t{1}", median, stdDev));
  }
}

 


Weitere Informationen Weitere Informationen:

Uwe Ricken: Sperrverhalten von Shared Locks…

Auf Grund einer Anfrage des von mir sehr geschätzten Kollegen Johannes Curio (e | w), die sich um Sperren von Objekten in einem HEAP drehte, habe ich mich etwas intensiver mit dem Sperrverhalten von Microsoft SQL Server beschäftigt, da die grundsätzliche Frage war, ob Microsoft SQL Server in einem HEAP jede Datenseite / jeden Datensatz nach dem Scannen sofort wieder freigibt. Die Antwort – wie meistens bei Microsoft SQL Server – … “It depends”. Dieser Artikel beschreibt die unterschiedlichen Sperrverhalten bei SELECT-Statements unter Berücksichtigung der verschiedenen ISO-Isolationsstufen in einem HEAP und in einem Clustered Index.

Testumgebung

Alle Tests verwenden einen HEAP, der mit 1.000 Datensätzen gefüllt wird. Zwei Fragen sollen mit den nachfolgenden Tests beantwortet werden:
  • Sperrt Microsoft SQL Server bei einem SELECT in einem HEAP jeden Datensatz?
  • Wird nach dem Lesen einer Ressource unmittelbar eine Freigabe der Ressource ausgelöst?
-- Erstellen der Demotabelle
CREATE TABLE dbo.Customer
(
    Id    INT        NOT NULL  IDENTITY(1,1),
    Name  CHAR(100)  NOT NULL,
    Ort   CHAR(100)  NOT NULL
);
GO
 
-- Füllen der Demotabelle mit 1.000 Datensätzen
DECLARE @i INT = 1;
WHILE @i <= 10000
BEGIN
    INSERT INTO dbo.Customer (Name, Ort)
    VALUES ('Kunde ' + CAST(@i AS VARCHAR(10)), 'Frankfurt am Main');
 
    SET @i += 1;
END
GO
 
-- Aktualisierung von 10 Datensätzen für hohe Selektivität
UPDATE dbo.Customer
SET    Ort = 'Erzhausen'
WHERE  Id % 1000 = 0;
GO

Die Tabelle hat 1.000 Datensätze und besitzt keinen Index. Für die Tests mit unterschiedlichen ISO-Isolationsstufen reicht dieses einfache Modell aus.

Hinweis(e)

Der Fokus dieses Artikels liegt in dem Sperrverhalten bei SELECT-Statements unter Berücksichtigung verschiedener ISO-Isolationsstufen; viele andere – aber ebenso wichtige – Details können nur bedingt in dieser Tiefe beschrieben werden, da sie sonst den Rahmen dieses Artikels sprengen würden; es werden jedoch weiterführende Links zu den entsprechenden Themen / Objekten genannt oder aber die Fachausdrücke unmittelbar mit Links versehen! Ein wichtiger Link sei bereits vorab genannt – die unterschiedlichen Sperren, die von Microsoft SQL Server verwendet werden können können unter “Kompatibilität von Sperren” genauer studiert werden. Dieser Artikel beleuchtet nicht die Sperr-Möglichkeiten auf Statement-Level (ROWLOCK / TABLOCK, …) sondern ausschließlich das automatische Sperrverhalten von Microsoft SQL Server unter den verschiedenen ISO-Isolationsstufen.

Protokollierung mit Microsoft SQL Server Profiler

Ich höre bereits die Aufschreie der Anhänger von “Extended Events” aber für die benötigte Protokollierung ist Microsoft SQL Server Profiler vollkommen ausreichend und er bietet mir verbesserte lesbare Ergebnisse (für diesen Blockeintrag).

Warum eine dedizierte Protokollierung?

Das Problem von Shared Locks – bei einigen ISO-Isolationsstufen – besteht darin, dass man sie nicht “sichtbar” machen kann, indem man eine dedizierte Transaktion beginnt, ein SELECT ausführt und diese Transaktion geöffnet lässt. Eine Kontrolle der gesetzten Sperren ist daher sehr schwierig und nur während der Ausführung des Befehls in einer SQL Server Profiler-Sitzung oder mit Extended Events zu kontrollieren.

Konfiguration der Microsoft SQL Server Profiler Sitzung


SQL_PROFILER_SETTINGS_01

Während der Ausführung der SQL-Abfragen wird aufgezeichnet, wann die Ausführung des Befehls beginnt [SQL:StmtStarting] und wann die Ausführung beendet wird [SQL:StmtCompleted]. Gleichwohl muss aufgezeichnet werden, wann eine Objektsperre gesetzt wird [Lock:Acquired] und wann die zuvor gesetzte Sperre wieder aufgehoben wird [Lock:Released]. Die zu jeder Aktion aufzuzeichnenden Informationen erstrecken sich vom Sperrmodus [Mode], der auf das Objekt [ObjectID] gesetzt wird als auch auf die Ressource, die in [TextData] angezeigt wird. Der Sperrtype wird in der Spalte [Type] angezeigt. Um unnötige Aktionen aufzuzeichnen, empfiehlt es sich, auf [SPID] einen Filter zu setzen, um ausschließlich Aktionen der Verbindungen zu filtern, in der die nachfolgenden Abfragen ausgeführt werden. Wer mehr über die Konfigurationsmöglichkeiten des Microsoft SQL Server Profilers wissen möchte, wird hier fündig.

Alternative Ausgabe mittels TRACEFLAGS


Die Ausgabe von Sperren (ähnlich wie in der Aufzeichnung mit dem Profiler oder Extended Events) kann man auch unmittelbar in SQL Server Management Studio ausgeben lassen; natürlich sind es auch hier wieder Traceflags, die solche Dinge ermöglichen, wie der folgende Code zeigt:


DBCC TRACEON(-1, 3604,1200) WITH NO_INFOMSGS;
TF 3604 leitet Ergebnisse nicht in das Fehlerprotokoll des Microsoft SQL Server sondern in die Ausgabe von SQL Server Management Studio und TF 1200 aktiviert die Ausgabe von Sperrinformationen. Bitte darauf achten, dass TF 1200 nicht dokumentiert ist und somit auch nicht in einem Produktivumfeld eingesetzt werden sollte. Weiterhin gilt es zu beachten, dass – je nach Anzahl der Sperren – eine sehr große Datenmenge an den Client zurückgeliefert werden kann! Grundsätzlich wird die Ausführung einer Abfragen bei aktiviertem TF deutlich beeinflusst! Ein weiterer – nicht zu unterschätzender – Punkt, der ganz besonders bei Produktionssystemen beachtet werden sollte; es handelt sich um einen GLOBALEN Traceflag. Die Aktivierung gilt also nicht nur für die aktuelle Session sondern für alle Sessions, die auf dem Microsoft SQL Server ausgeführt werden.

Testabfragen

Für die Tests werden zwei Abfragetypen verwendet, die wiederkehrend unter einer jeweils anderen ISO-Isolationsstufe ausgeführt werden. Dabei handelt es sich jeweils um Abfragen auf die ID als auch auf das Attribut [Ort] in der Tabelle [dbo].[Customer]. Mit der Abfrage auf die [ID] soll eine Auswahl mit hoher Selektivität untersucht werden, während eine Abfrage auf das Attribut [Ort] mit einer sehr hohen Datenmenge (99%) arbeitet.
Allen verwendeten Abfragen ist gemein, dass sie vor der Ausführung die zu überprüfende Isolationsstufe explizit für die Session setzen. Weitere Details zum Setzen von manuellen Isolationsstufen finden sich hier.

Abfrage mit hoher Selektivität

Grundsätzlich stellt sich in einem Heap nicht die Frage nach “Selektivität”, da immer ein Table Scan durchgeführt werden muss (schließlich kann nicht gezielt über einen Index gesucht werden). Aber es gibt im Rahmen von Sperren interessante Merkmale, die bei entsprechender Selektivität auffallend sind. Ein Abfrage auf eine explizite ID ist hoch selektiv, da die ID – unabhängig davon, ob es sich um einen Heap handelt – immer nur einen Datensatz zurückliefert. Die CAST-Funktion wird verwendet, um sicherzustellen, dass keine implizite Konvertierung (wie hier beschrieben) durchgeführt wird.



SELECT * FROM dbo.Customer WHERE Id = CAST(10 AS int);

GO

Abfrage mit niedriger Selektivität

Abfragen mit niedriger Selektivität können sehr große Datenmengen liefern, wenn die Auswahl auf einen Wert fällt, der sehr häufig vorkommt. In der Testumgebung wird im [Ort] sehr häufig “Frankfurt am Main” verwendet (90%). Abfragen auf das Attribut [Ort] mit der Einschränkung “Frankfurt am Main” werden immer 9.990 Datensätze (99%) liefern; nur 1 % der Datensätze hat einen anderen Wert (“Erzhausen”) in diesem Attribut.



SELECT * FROM dbo.Customer WHERE Ort = 'Frankfurt am Main';

GO

Isolationsstufe “READ COMMITTED”

Die Isolationsstufe “READ COMMITTED” ist die Standardeinstellung für Datenbanken in Microsoft SQL Server. READ COMMITTED bedeutet, dass ausschließlich Datensätze gelesen werden, die VOR dem Lesevorgang mittels COMMIT in der Datenbank gespeichert worden sind. Durch dieses Verfahren werden “Dirty Reads” vermieden. Im Microsoft SQL Server Profiler werden bei Ausführung der hoch selektiven Abfrage (es wird 1 Datensatz ausgegeben!) folgende Sperrverhalten aufgezeichnet (Auszug):

SQL_PROFILER_RESULTS_01
Das Ergebnis zeigt den Beginn der Aufzeichnung mit dem Absetzen des eigentlichen SQL Befehls. Anschließend wird ein “Intent Shared Lock” auf die Tabelle selbst gesetzt. Ein IS-Lock wird von Microsoft SQL Server IMMER gesetzt, um eine – mögliche – Lock-Eskalation durchführen zu können. Mit einer IS-Sperre signalisiert Microsoft SQL Server, dass diese Objekte nicht durch andere konkurrierende /inkompatible Sperren blockiert werden können.
Die nachfolgenden 16 Zeilen beantworten bereits die ursprüngliche Frage. “Gibt Microsoft SQL Server nach dem Scannen einer Ressource die Sperre wieder auf?”. Ja – in der Isolationsstufe “READ COMMITTED” werden gesetzte Shared Locks unmittelbar nach dem Scan der Ressource wieder freigegeben, damit andere Operationen die Datenseiten verwenden können. Auch die zweite Frage, die sich unmittelbar an die erste anschließt: “Führt Microsoft SQL Server Zeilensperren durch?” kann mit diesem einfachen Test belegt werden. Im Isolationsmodus “READ COMMITTED” werden KEINE Datensätze gesperrt! Alle Sperren werden auf Ebene einer Datenseite durchgeführt. Sobald Microsoft SQL Server die Datenseite nach dem Kriterium durchsucht hat, wird die  nächste Datenseite durchsucht. Zuvor wird die mit einem Shared Lock versehene Datenseite wieder freigegeben.
Die Sperre auf einer Datenseite – statt einer Datenzeile – macht in einem HEAP Sinn, da kein Index verwendet werden kann. Sofern Microsoft SQL Server nicht gezielt durch Indexes die geforderten Daten anfordern kann (SEEK), muss immer die vollständige Datenseite nach passenden Datensätzen durchsucht werden (SCAN). Durch das Sperren ganzer Datenseiten werden die Ressourcen (RAM / CPU) des Microsoft SQL Server geschont. Eine Zeilensperre ist aber auch in einer indexierten Tabelle nicht erforderlich, da keine Änderungen an den Datenzeilen durchgeführt werden müssen. Nur in den restriktiveren ISO-Isolationsstufen müssen u. U. Zeilensperren verwendet werden (wie die nachfolgenden Beispiele zeigen werden).
SQL_PROFILER_RESULTS_02

Schemastabilität während der Ausführung von Abfragen

Nachdem alle Datenseiten eingelesen wurden, wird eine SCH-S Sperre zur Sicherung der Schemastabilität für die Tabelle selbst gesetzt. Diese Sperre ist für Microsoft SQL Server relevant, damit Objekte während der Ausführung der Abfrage keinen Strukturänderungen unterzogen werden können. Aus diesem Grund wird VOR der Ausführung der Abfrage eine Sperre zur Schemastabilität gesetzt! Diese besondere Sperre wird ausschließlich bei HEAPS angewendet.

Isolationsstufe “READ UNCOMMITTED”

Wie bereits zu Beginn erwähnt, verhält sich Microsoft SQL Server bei der Verwendung von Sperren unter der Verwendung von verschiedenen Isolationsstufen unterschiedlich. Die Isolationsstufe “READ UNCOMMITTED” ist in der Regel nicht zu empfehlen, da sie sogenannte “Dirty Reads” zulässt. Dirty Reads bedeuten, dass ein SQL Befehl auch Daten von Datenseiten liest, die zwar schon geändert aber noch nicht bestätigt wurden (Die Daten wurden innerhalb einer noch nicht abgeschlossenen Transaktion geändert). Wie verhält es sich nun mit den Objektsperren, wenn ein SELECT-Befehl für einen HEAP abgesetzt wird. Die folgende Abbildung zeigt die gesetzten Sperren bei Verwendung der zweiten Abfrage mit der ISO-Isolationsstufe “READ UNCOMMITTED”.
SQL_PROFILER_RESULTS_03
Interessant ist bei diesem Ergebnis, dass – und das gilt ausschließlich für HEAPS – ein Shared Lock auf den “Index” selbst gesetzt wird (12 – HOBT). “HOBT” steht für HeapOrBTree und die Tabelle in unserem Beispiel ist eine Tabelle ohne Indexe (HEAP). Die generelle “SCH-S” Sperre muss gesetzt werden, um Modifikationen an der Tabelle selbst zu verhindern. Dieses Verhalten wird sehr gut in den BOL beschrieben: “ Alle Abfragen, auch solche mit READUNCOMMITTED- und NOLOCK-Hinweisen, aktivieren bei der Kompilierung und Ausführung Sperren des Typs Sch-S (Schemastabilität).
Die [BULK_OPERATION]-S-Sperre auf den “Index” wird ausschließlich auf HEAPS angewendet und verhindert das Lesen von unformatierten – neuen – Datenseiten, die während des Lesevorgangs durch neue Datensätze/geänderte Datensatze (FORWARDED RECORDS) generiert werden können. Grundsätzlich unterliegt die Belegung von Speicher durch neue Datensätze in einem HEAP anderen Regeln als in einem Clustered Index (das genaue Verfahren habe ich detailliert in einem TECHNET-Artikel hier beschrieben (englisch)). Immer wieder liest man im Internet, dass READ UNCOMMITTED / NOLOCK keine Sperren verwendet. Das obige Beispiel demonstriert eindeutig, dass es sich dabei um eine Falschaussage handelt!

Isolationsstufe “REPEATABLE READ”

Die Isolationsstufe “REPEATABLE READ” gehört zu den restriktiveren Isolationsstufen. “REPEATABLE READ” definiert, dass Anweisungen keine Daten lesen können, die geändert wurden, für die jedoch noch kein Commit von anderen Transaktionen ausgeführt wurde (wie READ COMMITTED) jedoch können darüber hinaus von der aktuellen Transaktion gelesene Daten erst nach Abschluss der aktuellen Transaktion von anderen Transaktionen geändert werden. Um die Ergebnisse besser erklären zu können, wird mit dem nachfolgenden Skript zunächst die exakte Position des Datensatzes mit der Id = 10 ermittelt:

SELECT sys.fn_physlocformatter(%%physloc%%) AS Position, * FROM dbo.Customer WHERE Id = CAST(10 AS int);

PHYSICAL_LOCATION_01
Im konkrete Beispiel befindet sich der Datensatz mit der Id = 10 auf der Datenseite 163 in Slot = 9. Diese Position wird für die Untersuchung der von Microsoft SQL Server gesetzten Sperren in den engeren Fokus rücken.
SQL_PROFILER_RESULTS_04
Die Auswertung der Aufzeichnung mit dem Microsoft SQL Server Profiler zeigt, dass zunächst eine IS-Sperre auf die Tabelle selbst gesetzt wird. Anschließend wird sofort eine IS-Sperre auf die erste Datenseite (163) gesetzt. Das nachfolgende Verhalten unterscheidet sich erheblich von den zuvor beschriebenen Isolationsstufen. Tatsächlich wird JEDE Datenzeile zunächst mit einer S-Sperre versehen, um sie nach der Prüfung sofort wieder freizugeben. Dieses Verhalten gilt jedoch nicht für die Datenzeile, die sich auf der Datenseite 163 in Slot = 9 befindet! Die obige Abbildung zeigt, dass ein Shared Lock auf die Datenzeile (RID) angewendet wird, der aber nach der Prüfung nicht wieder freigegeben wird.
Die nächste Abbildung der Ergebnisse des Microsoft SQL Server Profilers zeigen, wann eine Freigabe der Datenzeile erfolgt – nachdem die Ausführung der Abfrage abgeschlossen und die Transaktion beendet ist!
SQL_PROFILER_RESULTS_05
Dieses Ergebnis ist absolut schlüssig, wenn man die Vorgehensweise der Sperren bei REPEATABLE READ genauer kennt. Der besondere Unterschied von REPEATABLE READ zu den bisher beschriebenen Isolationsstufen besteht darin, dass bei Microsoft SQL Server in dieser Isolationsstufe sichergestellt, dass ein Datensatz, der innerhalb einer Transaktion gelesen wird, auch bei einem erneuten Lesen innerhalb der gleichen Transaktion identische Daten besitzen muss. Würde Microsoft SQL Server nach dem Lesen des Datensatzes mit der ID = 10 den Datensatz wieder freigeben, wäre folgende Situation möglich:


  • Transaktion 1 setzt eine Sperre auf den Datensatz ID = 10, liest den Datensatz und hebt die Sperre wieder auf
  • Transaktion 2 bearbeitet nach der Freigabe den Datensatz mit der ID = 10 und ändert z. B. den Ort
  • Transaktion 1 setzt erneut eine Sperre auf den Datensatz ID = 10 und liest den Datensatz erneut ein.
    Diesmal sind jedoch nicht mehr die ursprünglichen Werte des ersten Lesevorgangs vorhanden sondern durch Transaktion 2 geänderten Daten.
Damit solche Änderungen nicht vorkommen können, können Datensätze während der gesamten Transaktion gesperrt werden, die zuvor beschriebenen Isolationsstufen können diese Abgrenzung nicht leisten!

Isolationsstufe “SERIALIZABLE”

Die Isolationsstufe “SERIALIZABLE” ist die restriktivste ISO-Isolationsstufe, die mit Microsoft SQL Server angewendet werden kann. In der ISO-Isolationsstufe “SERIALIZABLE” werden Bereichssperren in den Schlüsselwertbereichen eingerichtet. Dadurch wird verhindert, dass andere Transaktionen Zeilen aktualisieren oder einfügen, die den von der aktuellen Transaktion ausgeführten Anweisungen entsprechen würden. Wie restriktiv die Sperren gesetzt werden, sollen die nachfolgenden Abbildungen und Erläuterungen demonstrieren.
Für die Demonstration wird erneut eine Abfrage auf ID = 10 ausgeführt, die lediglich einen Datensatz zurückliefern wird.
SQL_PROFILER_RESULTS_06
Immer mit dem Hintergedanken, dass es sich bei der Tabelle [dbo].[Customer] um einen HEAP handelt, wird das Ergebnis schnell einleuchten. Obwohl nur ein Datensatz benötigt wird, muss ein vollständiger Table Scan durchgeführt werden. Es müssen also – wie schon zuvor geschehen – ALLE Datensatze durchsucht werden. Es wird die Tabelle als vollständiger Wertebereich durchsucht! Basierend auf der Tatsache, dass in einem HEAP nicht explizite Schlüsselwerte gesperrt werden können (Ausnahme REPEATABLE READ), wird die vollständige Tabelle gesperrt. Während einer Transaktion in der ISO-Isolationsstufe SERIALIZABLE können in einen HEAP keine weiteren Datensätze eingetragen werden!

Verhalten von Shared Locks in indizierten Tabellen

Das Sperrverhalten von SELECT-Statements ändert sich beim Zugriff auf Indexe, sofern ein INDEX SEEK angewendet werden kann. In diesem Fall werden – je nach Selektivität – nur die Datenseiten / Datenzeilen gesperrt, die durch den INDEX-SEEK gezielt verwendet werden können. Die Ergebnisse können sehr leicht selbst überprüft werden. Zunächst wird die Tabelle mit einem Clustered Index auf [ID] versehen. Zusätzlich wird ein Index für den Ort angelegt.

CREATE UNIQUE CLUSTERED INDEX ix_Customer_Id ON dbo.Customer (Id);

CREATE INDEX ix_Customer_Ort ON dbo.Customer (Ort) INCLUDE (Name);
Die nachfolgenden SQL-Statements mit ihren jeweiligen Aufzeichnungen im Profiler zeigen, wie sich das Sperrverhalten ändert. Die Ausführungen werden aber nur kurz angerissen und bei Besonderheiten vertieft.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM dbo.Customer WHERE ID = CAST(10 AS INT) ;

GO

SQL_PROFILER_RESULTS_07
Einziger Unterschied zum Sperrverhalten in einem HEAP ist die ausschließliche Sperre der Datenseite, in der sich der betreffende Datensatz befindet.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT * FROM dbo.Customer WHERE Ort = 'Erzhausen';

GO

SQL_PROFILER_RESULTS_08
Da es sich nicht mehr um einen HEAP handelt, ist ausschließlich eine SCH-S Sperre erforderlich, um Änderungen an den strukturellen Daten (Metadaten) der Tabelle zu verhindern (Schemastabilität).

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT * FROM dbo.Customer WHERE Id = CAST(10 AS INT);

GO

SQL_PROFILER_RESULTS_09
Bei “REPEATABLE READ” muss sichergestellt sein, dass der Datensatz, den Microsoft SQL Server einliest auch in weiteren Lesevorgängen innerhalb der gleichen Transaktion unverändert ist. Aus diesem Grund wird eine Zeilensperre durchgeführt. Der Wert in [TextData] entspricht dem KeyHashValues des Datensatzes auf der Datenseite.

DBCC TRACEON (3604);

DBCC PAGE ('demo_db', 1, 1480, 3) WITH TABLERESULTS;

Mit dem obigen Befehl kann der Inhalt der Betroffenen Datenseite (1480) sichtbar gemacht werden. Für die bessere Lesbarkeit wird die Ausgabe als Tabelle forciert.

DBCC_PAGE_01
Die letzte Abfrage weicht etwas ab, um eine Bereichssperre zu demonstrieren, die nur in der Isolationsstufe “SERIALIZABLE READ” auftreten kann.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT * FROM dbo.Customer WHERE Id BETWEEN 10 AND 20;

GO

SQL_PROFILER_RESULTS_10
Eine Bereichssperren muss von Microsoft SQL Server gesetzt werden, um zu verhindern, dass zwischen den gesperrten Datensätzen weitere Datensätze eingefügt werden können. Würde z. B. in einer anderen Transaktion versucht werden, einen neuen Datensatz mit einer ID zwischen 10 und 20 einzutragen, wird die Transaktion so lange gesperrt, bis die von Microsoft SQL Server in der aktuellen Transaktion gehaltenen Bereichssperren wieder freigegeben werden.

Zusammenfassung

Erst einmal herzlichen Dank an Johannes Curio für die interessante Frage; zum einen hat sie mir Material für einen neuen Blogeintrag geliefert und zum anderen – wie meistens – musste ich mich wieder etwas intensiver mit Themen auseinandersetzen, die weit über die normale Verwendung hinausgehen, aber auf jeden Fall wieder viele Dinge in einem – einleuchtenden – neuen Licht zeigen.

Herzlichen Dank fürs Lesen!

Sascha Dittmann: MapReduce Entwurfsmuster - Numerische Aggregation (Standardabweichung 1/2)

MapReduce Entwurfsmuster - DurchschnittswerteIn diesem Teil meiner kleinen Serie der MapReduce Entwurfsmustern für Microsoft HDInsight erweitere ich die Gruppe der Numerische Aggregation mit einem vereinfachten Beispiel zur Berechnung der Standardabweichung und des Medians...

Nachdem ich im ersten Teil und zweiten Teil dieser Serie relative einfache MapReduce Algorithmen vorgestellt habe, möchte ich mit diesem um dem kommenden Teil den Schwierigkeitsgrad leicht erhöhen.

Die Standardabweichung (ein Maß für die Streuung der Werte) wird durch die Quadratwurzel der Varianz der untersuchten Werte berechnet.

Die dafür benötigte Varianz der Werte wird aus der Summe der quadrierten Abweichungen vom Durchschnittswert berechnet, welche noch durch die Anzahl der Werte - 1 geteilt wird.

 

Beispiel "Standardabweichung und Median (ohne Combiner)"

Bei diesem Beispiel sollen die Standardabweichung und der Median für die Kommentarlänge pro Stunde errechnet werden.

Die hierzu verwendete Mapper-Funktion ist nahezu identisch mit der aus dem vergangenen Artikel dieser Serie:

public class MedianStdDevMapper : MapperBase
{
  public override void Map(string inputLine, MapperContext context)
  {
    var parsed = XmlUtils.ParseXml(inputLine);

    if (parsed == null 
        || !parsed.ContainsKey("CreationDate") 
        || !parsed.ContainsKey("Text"))
    {
      context.CoreContext.IncrementCounter(
        "Median / Std. Dev. Mapper", "Invalid Rows", 1);
      return;
    }

    DateTime creationDate;
    if (!DateTime.TryParse(parsed["CreationDate"], out creationDate))
    {
      context.CoreContext.IncrementCounter(
        "Median / Std. Dev. Mapper", "Invalid Creation Dates", 1);
      return;
    }

    var text = parsed["Text"];

    context.EmitKeyValue(
      creationDate.Hour.ToString(CultureInfo.InvariantCulture),
      text.Length.ToString(CultureInfo.InvariantCulture));
  }
}

 

Eine Combiner-Funktion kann bei diesem vereinfachen Algorithmus nicht verwendet werden.
Im kommenden Teil stelle ich allerdings eine komplexere Variante vor, die einen Combiner beinhaltet. 

Die Reducer-Funktion berechnet anschließend den Median (Mittlerer Wert der sortierten Liste), die Varianz der Werte (s.o.), sowie die Standardabweichung (Quadratwurzel der Varianz).

Anschließend liefert der Reducer erneut die Ausgabewerte - zur späteren Weiterverarbeitung - im Tab-getrennten Format zurück.

public class MedianStdDevReducer : ReducerCombinerBase
{
  public override void Reduce(string key, 
    IEnumerable values, 
    ReducerCombinerContext context)
  {
    float sum = 0;
    int count = 0;

    var commentLengths = new List();

    foreach (var value in values.Select(float.Parse))
    {
      commentLengths.Add(value);
      sum += value;
      count++;
    }

    commentLengths.Sort((x, y) => x.CompareTo(y));

    // calculate median
    double median;
    if (count % 2 == 0)
    {
      // if commentLengths is an even value, average middle two elements
      median = (commentLengths[Convert.ToInt32(count / 2 - 1)]
        + commentLengths[Convert.ToInt32(count / 2)]) / 2.0f;
    } else {
      // else, set median to middle value
      median = commentLengths[Convert.ToInt32(count / 2)];
    }

    // calculate standard deviation
    var avg = sum / count;
    var sumOfSquares = commentLengths
      .Sum(commentLength => Math.Pow(commentLength - avg, 2));
    var stdDev = Math.Sqrt(sumOfSquares / (count - 1));

    context.EmitKeyValue(
      key,
      String.Format("{0}\t{1}", median, stdDev));
  }
}

 


Weitere Informationen Weitere Informationen:

Sascha Dittmann: MapReduce Entwurfsmuster - Numerische Aggregation (Average)

MapReduce Entwurfsmuster - DurchschnittswerteIm zweiten Teil meiner kleinen Serie der MapReduce Entwurfsmustern für Microsoft HDInsight möchte ich ein weiteres Beispiel für eine Numerische Aggregation vorstellen.
Diesmal geht es um die Berechnung von Durchschnittswerten...

Im ersten Teil dieser Serie hatte ich ein Beispiel für einen Min/Max/Count MapReduce-Algorithmus vorgestellt.

Diesmal möchte ich die Sammlung um ein Beispiel für eine Durchschnittswertberechnung erweitern.

 

Beispiel "Average"

Bei diesem Beispiel soll die durchschnittliche Kommentarlänge pro Stunde errechnet werden.

Hierzu extrahiert die Mapper-Funktion die Werte der CreationDate- und Text-Attribute der XML-Quelle und gibt den Stunden-Wert, sowie die Textlängem als Key/Value-Paare zurück:

public class AverageMapper 
  : JsonOutMapperBase<CountAverageData>
{
  public override void Map(string inputLine, 
    JsonMapperContext<CountAverageData> context)
  {
    var parsed = XmlUtils.ParseXml(inputLine);

    if (parsed == null 
        || !parsed.ContainsKey("CreationDate") 
        || !parsed.ContainsKey("Text"))
    {
      context.CoreContext.IncrementCounter(
        "Average Mapper", "Invalid Rows", 1);
      return;
    }

    DateTime creationDate;
    if (!DateTime.TryParse(parsed["CreationDate"], out creationDate))
    {
      context.CoreContext.IncrementCounter(
        "Average Mapper", "Invalid Creation Dates", 1);
      return;
    }

    var text = parsed["Text"];

    context.EmitKeyValue(
      creationDate.Hour.ToString(CultureInfo.InvariantCulture),
      new CountAverageData
      {
        Average = text.Length,
        Count = 1,
      });
  }
}

 

Die Combiner- und Reducer-Funktionen aggregieren anschließend die entsprechenden Werte.

Damit die Werte durch die Combiner-Funktion nicht verfälscht werden, muss die Summe mittels der Anzahl der Datensätze rekonstruiert werden.

public class AverageCombiner 
  : JsonInOutReducerCombinerBase<CountAverageData, CountAverageData>
{
  public override void Reduce(string key, 
    IEnumerable<CountAverageData> values, 
    JsonReducerCombinerContext<CountAverageData> context)
  {
    float sum = 0;
    float count = 0;

    foreach (var value in values)
    {
      sum += value.Count * value.Average;
      count += value.Count;
    }

    context.EmitKeyValue(key, new CountAverageData
    {
      Average = sum / count,
      Count = count,
    });
  }
}

 

Der Reducer liefert erneut die Ausgabewerte - zur späteren Weiterverarbeitung - im Tab-getrennten Format zurück.

public class AverageReducer
  : JsonInReducerCombinerBase<CountAverageData>
{
  public override void Reduce(string key, 
    IEnumerable<CountAverageData> values, 
    ReducerCombinerContext context)
  {
    float sum = 0;
    float count = 0;

    foreach (var value in values)
    {
      sum += value.Count * value.Average;
      count += value.Count;
    }

    context.EmitKeyValue(key, new CountAverageData
    {
      Average = sum / count,
      Count = count,
    }.ToString());
  }
}

 

public class CountAverageData
{
  public float Average { get; set; }
  public float Count { get; set; }

  public override string ToString()
  {
    return String.Format("{0}\t{1}", Average, Count);
  }
}

 


Weitere Informationen Weitere Informationen:

Andreas Wolter: SQL Server Database Ownership: survey results & recommendations

 SQL Server Datenbankbesitz: Umfrageergebnisse und Empfehlungen

 

(en)
You may remember the survey on database ownership which I launched several months ago.

In the following, I am now presenting the results and giving my official recommendation for a best practice for security in terms of database ownership.

(de)
Ihr erinnert Euch vielleicht an die Umfrage zu Datenbankbesitz, die ich vor einigen Monaten gestartet habe.
Hier präsentiere ich nun die Ergebnisse und gebe meine offiziellen Empfehlungen für Sicherheits-Best Practice hinsichtlich Datenbankbesitz.

First the results.

I received data from 58 different servers and 905 databases altogether.
That’s not bad, and sufficient for my purpose of giving you, my readers, the opportunity to find out how others configure their servers.

Many thanks to all those who submitted!

You may still share results but I can’t promise how soon I can include them. (Here is the survey plus the script for collection)
So now to the details. I put the most interesting data in charts.
The most obvious issue is that of the external owner’s account, which is most often and not very surprisingly sa:

Zuerst einmal die Ergebnisse.

Ich habe insgesamt von 58 verschiedenen Servern und 905 Datenbanken Daten erhalten.
Das ist nicht schlecht. Und es ist ausreichend für meine Zwecke – Euch, meinen Lesern, die Gelegenheit zu gegeben herauszufinden, wie andere ihre Server konfigurieren.

Vielen Dank an alle, die ihre Daten eingereicht haben!

Ihr könnt eure Ergebnisse immer noch mit mir teilen, aber ich kann euch nicht versprechen, wie bald ich sie mit einschließen kann. (Hier sind die Umfrage sowie das Skript für die Datenerfassung.)

Nun zu den Details. Ich habe die interessantesten Daten in Diagramme gesetzt.
Die offensichtlichste Frage ist die des Kontos des externen Besitzers, das am häufigsten und nicht überraschenderweise sa ist:

  SQL_Server_External_Database_Owner_pct

 

57% of all databases belong to sa himself. Actually, this is better than expected. But let’s dive further – what’s the server role behind the remaining 42%?

57% aller Datenbanken gehören sa selbst. Dies ist sogar besser als erwartet. Aber schauen wir mal genau hin – was ist die Server-Rolle hinter den verbleibenden 42%?

 SQL_Server_Role_Membership_of_Database_Owner_pct

 

Ok, that changes the picture quite a bit. Almost 80% of all Database owners are sysadmin. So that is by no means any better than sa.
Then some other accounts follow, which means those have low privileges (“excellent”), and then comes dbcreator, securityadmin, that are later followed by some other high privileged server roles, though with much less power.

So in other words: only 7% of all those databases have been looked at with security in mind by only using low privileged accounts as owners.

If you are interested in the plain numbers, here they go:

Ok, das verändert das Bild schon entscheidend. Fast 80% aller Datenbankenbesitzer sind sysadmin. Das ist also keineswegs besser als sa.
Es folgen einige andere Konten, das bedeutet, dass diese niedrige Privilegien (“hervorragend”) haben, und dann folgen dbcreator, securityadmin, auf die später einige andere hochprivilegierten Serverrollen folgen, doch mit weitaus weniger Privilegien.

Mit anderen Worten: nur 7% aller dieser Datenbanken wurden im Hinblick auf Sicherheit betrachtet, indem von Besitzern nur niedrigprivilegierte Konten verwendet wurden.

Wenn euch die genauen Zahlen interessieren, hier sind sie:

 SQL_Server_Role_Membership_of_Database_Owner_num

I did include some of the security-wise critical database- & server configurations:

  1. Is the database set to be “Trustworthy”?
  2. Is the database set to have “Database chaining on”?
  3. Is the Server set to have “cross database chaining on”?

Those are actually the even more important results.

Since the system databases need to have a different setting by default, I am excluding them, making it a total of 847 User databases.
Of which 30 have the trustworthy bit set to on, and 35 have the database chaining.
What you can’t see in this graph, but what I can tell from the raw data, is that those 30 “trustworthy” databases all are owned by a sysadmin.
And THIS now is the biggest security-hole in this area!
Here a graph on that:

Ich habe einige der sicherheitstechnisch kritischen Datenbank- und Serverkonfigurationen mit eingeschlossen:

  1. Ist die Datenbank auf „Trustworthy“ eingestellt?
  2. Ist in der Datenbank „Datenbank-Verkettung an“ eingestellt?
  3. Ist im Server „cross database chaining on“ eingestellt?

Diese sind eigentlich die wichtigeren Ergebnisse.

Da die Systemdatenbanken standardmäßig eine andere Einstellung haben müssen, schließe ich sie in meiner Bewertung aus, so dass ich auf insgesamt 847 Nutzer-Datenbanken komme.
30 von ihnen haben das Trustworth-Bit eingestellt, und 35 haben die Datenbanken-Verkettung eingeschaltet.
Was ihr in dieser Grafik nicht sehen könnt, aber was ich aus den Rohdaten erkennen kann, ist, dass diese 30 „vertrauenswürdigen“ Datenbanken alle im Besitz von einem sysadmin sind.
Und DAS ist das größte Sicherheitsloch in diesem Bereich!
Hier ein Diagramm dazu:

 

SQL_Server_Critical_Database_Settings 

In the interest of time I will focus this post on recommendation rather than explaining all the risks involved. At the end though I will provide some links for further reading.

Aus Zeitgründen werde ich diesen Eintrag auf Empfehlungen beschränken, als alle Risiken zu erklären. Am Ende werde ich jedoch einige Links für weiterführende Lektüre angeben.

Possibilities

So what are the general variations of database ownership?
Let me start with the most common and actually WORST possibilities (Yes, I mean it exactly as I say ;-) ):

  1. SA-Account
  2. Some other SQL-Account with sysadmin privileges
  3. Windows Login with sysadmin privileges

A first improvement(? – really?):

      4. Any of the above with Status = Disabled

 And then:

     5.   A ”shared” account without any special server role or permissions (aka “1 Account per Server”)

     6.   1 Account per Database

     7.    1 Account per Application

     8.   1 Account per Group of databases

 + all of them not only Disabled but with a Denied Connect-Permission

Möglichkeiten

Was sind also die allgemeinen Variationen von Datenbanken-Besitztum?

Fangen wir mit den häufigsten und eigentlich SCHLECHTESTEN Möglichkeiten an (Ja, das meine ich genau so, wie es hier steht ;-) ):

 

  1. SA-Konto
  2. Irgendein anderes SQL-Konto mit sysadmin-Privilegien
  3. Windows Login mit sysadmin-Privilegien

 Eine erste Verbesserung(? – wirklich?):

     4.   Alle der oben angegebenen mit Status = Deaktiviert

 Und dann:

     5.     Ein „geteiltes“ Konto ohne eine spezielle Serverrolle oder Rechte (Alias „1 Konto pro Server“)

     6.   1 Konto pro Datenbank

     7.   1 Konto pro Anwendung

     8.   1 Konto pro Datenbank-Gruppe

 + alle davon nicht nur Deaktiviert sondern mit einer verweigerten Verbindungs-Berechtigung 

My Recommendation:

Depending on your environment: Any of 5, 6, 7 or 8:

Create a specific Login without any extra permissions + Deny Connect.

The most simple approach and yet better than sa is: one database owner per server.
Example for (5):

  • Database1 owned by DBOwner
  • Database2 owned by DBOwner
  • Database3 owned by DBOwner

 Simple and self-explanatory.

The other extreme and most secure is: per database.
Example for (6):

  • Database1 owned by DBOwner_Database1
  • Database2 owned by DBOwner_Database2
  • Database3 owned by DBOwner_Database3
  • Database4 owned by  DBOwner_Database4

 Some applications use a number of different databases. For them it’s perfectly fine to use the same database owner account. So create an account per application.

Example for (7):

  • App1Database1 owned by DBOwner_App1
  • App1Database2 owned by DBOwner_App1
  • App2Database1 owned by DBOwner_App2
  • App2Database owned by  DBOwner_App2

 Another approach is kind of a compromise between 1 Database-Owner Account per Server and One per database: Define the level of security needed per database. Then create a dedicated account for the most critical Databases. And for the others use a shared owner/account, possibly divided in 2 or more groups.

Example for (8):

  • CriticalDatabase1 owned by DBOwner_Level1Dedicated1
  • CriticalDatabase2 owned by DBOwner_ Level1Dedicated2
  • Level2Database1 owned by DBOwner_Level2
  • Level2Database2 owned by DBOwner_Level2

 I hope my samples give you an idea. :-)

So why this effort?
Let me put it this way: ”Why not sa?”.
First: If you think about it, it actually makes little sense that the highest privileged account in SQL Server is being recommended by so many, even professionals + in Whitepapers (!) – when security is the focus. It is really wrong, as wrong as it could possibly get.
I mean, as you can see, there are other options out there.
The top reason why SA keeps getting recommended is administration itself: It eases the setup for failover and regular database restores, since SA is always available at any server and hence a broken database owner can be avoided with almost no extra work.
But that’s “only” from a perspective of maintenance.
With regard to security it is totally on contrary to the Principle of least privilege.

It may not matter a lot, if everything else is tightened, but that’s hardly a thing to rely on especially in bigger environments where things change and many people have access and permissions to.
Especially in the context of the trustworthy-setting for a database, this completely opens the system for privilege escalation attacks from inside. It is then a piece of cake to gain system level permissions once you are for example in the db_owner database group – like many applications are, if they are not sysadmin already.
- Remember: the owner of a database cannot be denied anything inside and with his database. So he can change structure, create backups, break log-backup-chain and also drop it completely.

And since the attack starts from inside, it really doesn’t matter whether the sa/sysadmin account is disabled as you may now realize.

Having a dedicated account with zero special permissions as database owner prevents database principals from gaining system level permissions as a sysadmin has, even in the case of the database being trustworthy.
And trustworthy is one of the dirty little shortcuts for developers implementing CLR code inside the database and avoiding the hassle of having to use certificates under certain conditions. The same is often done for code that needs to get server-level data from inside the database.

Meine Empfehlung:

Abhängig von eurer Umgebung: eine von 5, 6, 7 oder 8:

Ein spezifisches Login errichten ohne extra  Rechte + Deny Connect.

Die einfachste Herangehensweise und doch besser als sa ist: eine Datenbank pro Server.

Beispiel für (5):

  • Datenbank1 im Besitz von DBOwner
  • Datenbank2 im Besitz von DBOwner
  • Datenbank3 im Besitz von DBOwner

 Einfach und selbsterklärend.

 Das andere Extrem und dabei die sicherste ist: pro Datenbank.

Beispiel für (6):

  • Datenbank1 in Besitz von DBOwner_Database1
  • Datenbank2 in Besitz von DBOwner_Database2
  • Datenbank3 in Besitz von DBOwner_Database3
  • Datenbank4 in Besitz von DBOwner_Database4

Einige Anwendungen verwenden eine Reihe von unterschiedlichen Datenbanken. Für sie ist es völlig ausreichend, das gleiche Datenbankbesitzerkonto zu verwenden. Erstellt also ein Konto pro Anwendung.

Beispiel für (8):

  • App1Database1 in Besitz von DBOwner_App1
  • App1Database2 in Besitz von DBOwner_App1
  • App2Database1 in Besitz von DBOwner_App2
  • App2Database in Besitz von DBOwner_App2

Eine andere Herangehensweise ist eine Art Kompromiss zwischen 1 Datenbankenbesitzerkonto pro Server und einem pro Datenbank: Definiere das Sicherheitslevel, das je Datenbank gebraucht wird. Dann erstelle ein spezielles Konto für die kritischsten Datenbanken. Und für die anderen Besitzer einen gemeinsamen Besitzer-/Konto verwenden, möglicherweise in 2 oder mehr Gruppen geteilt.

Beispiel für (7):

  •  CriticalDatabase1 in Besitz von DBOwner_Level1Dedicated1
  • CriticalDatabase2 in Besitz von DBOwner_ Level1Dedicated2
  • Level2Database1 in Besitz von DBOwner_Level2
  • Level2Database2 in Besitz von DBOwner_Level2

 Ich hoffe, meine Beispiele geben euch eine Vorstellung. :-)

Aber warum diese Mühe?
Lasst es mich so ausdrücken: “Warum nicht sa?”
Zuallererst: Denkt man darüber nach, ergibt es eigentlich wenig Sinn, dass das höchstprivilegierte Konto beim SQL Server von so vielen empfohlen wird, selbst von Profis + in Whitepapers (!) – wenn Sicherheit im Fokus steht. Es ist wirklich falsch, so falsch wie es nur irgend sein kann.

Schließlich gibt es da draußen, wie ihr sehen könnt, noch andere Optionen.

Die Grund Nr. 1, warum SA immer wieder empfohlen wird, ist die Administration selbst: Es erleichtert das Einrichten für Failover und regelmäßige Datenbankenwiederherstellungen, da SA immer auf jedem Server verfügbar ist und damit ein kaputter Datenbankbesitzer mit wenig zusätzlichem Aufwand verhindert werden kann.
Aber das ist „nur“ aus Sicht der Wartung.
Was die Sicherheit angeht, steht es völlig im Gegensatz zum Prinzip des geringsten Privilegs.

Es mag nicht viel ausmachen, wenn alles andere straff sitzt, aber darauf sollte man sich nicht verlassen, besonders in größeren Umgebungen, wo sich Dinge ändern und viele Leute Zugriff und Befugnisse haben.

Besonders im Kontext der Trustworthy-Einstellung für eine Datenbank öffnet dies das System komplett für privilege escalation-Angriffe von innen. Dann ist es ein Kinderspiel, Systemlevel-Befugnisse zu erlangen, wenn man einmal z.B. in der db_owner Datenbankengruppe ist – wie es viele Anwendungen sind, wenn sie nicht bereits sysadmin sind.

Denkt dran: dem Datenbankenbesitzer kann weder innen noch mit seiner Datenbank etwas verweigert werden. Er kann also die Struktur verändern, Backups erstellen, Log-Backup-Chain brechen und sie auch komplett löschen.

Und da der Angriff von innen anfängt, ist es wirklich egal, ob das sa/sysadmin Konto deaktiviert ist, wie ihr jetzt realisiert haben werdet.
Ein spezielles Konto mit Null speziellen Befugnissen als Datenbankbesitzer zu haben hindert Datenbank-Prinzipale daran, System-Level-Befugnisse zu erlangen, wie sie ein sysadmin hat, selbst in dem Fall, dass die Datenbank vertrauenswürdig ist.
Und „trustworthy“ ist eine der unsauberen kleinen Abkürzungen für Entwickler, die CLR-Code im Innern der Datenbank ausführen und sich dabei die Umstände sparen, unter bestimmten Bedingungen Zertifikate benutzen zu müssen. Dasselbe wird oft für Code gemacht, der Server-Level-Daten aus dem Innern der Datenbank erreichen muss.

Call for actions:

Check your databases. You can find my script here: Security-Check-Script & Survey: SQL Server Security - Database-Owners, critical Permissions and role membership
Now when you start with securing your databases from database-ownership standpoint, you have to make sure that the very account does exist at any sever where this database gets restored/failed over. Usually you will have a technique in place already to synchronize your server-level principals to your other servers. So this is just one or several more of them.

Also make sure you fully understand your environment and possibly application needs before you just change the owner of your databases. You can start by reading through the links at the bottom.

Vote for an improvement in SQL Server:
I have created a suggestion as Connect Item which tackles this problem. My idea is having Microsoft include a special “DBOwner” Account at server level by default, which not only pre-exists and has not permissions, but also never compares to another. I think this would make it much easier to get rid of the habit of “sa” everywhere by also making it simple to maintain.
Please vote here: Providing a special Server principal for Database Ownership

Handlungsaufruf:

Überprüft eure Datenbanken. Ihr könnt meinen Skript hier finden: Sicherheitsprüfungs-Script & Umfrage: SQL Server Datenbankbesitzer, kritische Rechte und Rollenmitgliedschaft

Wenn ihr jetzt anfangt, eure Datenbanken aus der Perspektive von Datenbanken-Besitz zu sichern, müsst ihr dabei sicherstellen, dass dasselbe Konto auf jedem Server existiert, wo diese Datenbank wiederhergestellt/failed over wird. Normalerweise werdet ihr bereits eine Technik haben, wie ihr eure Server-Level-Prinzipale mit euren anderen Servern synchronisiert. Das sind also nur eine oder einige mehr davon.

Stellt außerdem sicher, dass ihr eure Umgebung und möglicherweise Anwendungsbedürfnisse vollständig versteht, bevor ihr den Besitzer eurer Datenbanken einfach ändert. Ihr könnt damit anfangen, indem ihr euch unten aufgelisteten Links durchlest.

Abstimmen für eine Verbesserung im SQL Server:
Ich habe einen Vorschlag als Connect Item erstellt, der dieses Problem behandelt. Meine Vorstellung ist es, Microsoft dazu zu bringen, standardmäßig ein spezielles „DBOwner“ Konto auf Server-Level auszuliefern, das nicht nur bereits immer vorab existiert und keine Rechte hat, sondern auch nie mit anderen vergleichbar ist. Ich denke, dass dies es viel einfacher machen würde, die allgegenwärtige Gewohnheit des „sa“ loszuwerden und es gleichzeitig auch einfach Wartbar machen würde.
Bitte hier Eure Stimme abgeben: Providing a special Server principal for Database Ownership

I hope this was helpful.

If you have any questions feel free to comment.
Let me finish up with some links for further readings:

Ich hoffe, das war hilfreich.

Wenn ihr noch Fragen habt, kommentiert gern.
Zum Abschluss einige Links für weiterführende Lektüre:

 

Highly recommended reading:

Dringend empfohlen:

Giving Permissions through Stored Procedures
Ownership Chaining, Certificates and the Problematic EXECUTE AS from Erland Sommarskog

More on Disabling and Deny Connect:

Mehr zu…

DISABLE and DENY LOGIN, DENY USER & Effect on Impersonation and Permissions

More on trustworthy:

 

The TRUSTWORHY bit database property in SQL Server 2005

TRUSTWORTHY Database Property

Extending Database Impersonation by Using EXECUTE AS

Discussions:

Database/Object Ownership Misalignment

database ownership - sa disabled

 

Happy Securing

 

Andreas

Sascha Dittmann: MapReduce Entwurfsmuster - Numerische Aggregation (Min/Max/Count)

MapReduce Entwurfsmuster - Einfache AggregationIn den letzten Monaten ist es leider etwas ruhig auf meinem Blog geworden, was zum größten Teil an meinem neuen Freizeitprojekt - den Azure Management Apps - lag.
Um diese Stille zu durchbrechen, möchte ich eine kleine Serie mit MapReduce Entwurfsmustern für Microsoft HDInsight starten...

Beginnen möchte ich mit einem sehr einfachen Entwurfsmustern aus dem Bereich "Numerische Aggregation".

Hierbei werden die Datensätze nach einem bestimmtem Feld gruppiert und Aggregate von einem oder mehreren anderen Feldern gebildet.
Beispiele für diese Aggregate sind Summen, Standardabweichungen, das Zählen von Datensätzen, Mindest-, Maximal-, Median- oder Durchschnittswerte.

 

Voraussetzungen

Das hier vorgestellt Entwurfsmuster kann genutzt werden, wenn folgende Voraussetzungen erfüllt sind:

  • Es handelt sich um numerische Daten
  • Die Daten können nach einem bestimmten Feld gruppiert werden

 

Musterstruktur

  • Mapper
    Die Mapper-Funktion gibt bei diesem Entwurfsmuster als Key das Feld zurück, nachdem gruppiert werden soll, und als Value eine Liste der zu aggregierenden Felder.
  • Combiner
    Die Combiner-Funktion stellt hier eine ideale Möglichkeit bereit die gesammelten Werte vorzuaggregieren. Somit wird der Datentransfer zwischen den Clusterknoten minimiert.
  • Partitioner
    Bei besonders riesigen, unausgewogenen Datenmengen können Numerische Aggregationen durch den Einsatz eines benutzerdefinierten Partitioners profitieren.
    Dieser muss dann die Daten möglichst gleichmäßig auf die Reducer verteilen.
    Da diese Art von Datenquellen allerdings recht selten vorkommen, ist in den meisten Fällen der damit erreichte Zeitvorteil sehr gering.
  • Reducer
    Die Reducer-Funktion führt dann die eigentlichen Aggregationen durch und gibt als Key das Feld nachdem gruppiert wurde, und als Value die Liste der aggregierten Felder zurück.

 

Mögliche Einsatzszenarien

  • Wörter Zählen
    Wörter Zählen – das "Hello World"-Beispiel von MapReduce - ist eine Numerische Aggregation.
    Hier splittet die Mapper-Funktion die vorhandenen Texte in ihre einzelnen Wörter auf und gibt jedes Wort als Key und eine "1" als Value zurück.
    Die Combiner- und Reducer-Funktion summiert diese Values anschließend auf.
  • Anzahl von Datensätzen 
    Ein oft genutztes Verfahren, um ein besseres Verständnis für die Datenmengen in einem bestimmten Intervall zu erhalten, wie beispielsweise Wöchentlich, Täglich, Stündlich, etc..
  • Min. / Max. / Anzahl
    Mit dem zusätzlichen Mindest- und Maximalwert können auch dynamische Intervalle dargestellt werden, wie beispielsweise das Datum des erster und letzten Forumbeitrags eines Benutzers und die Gesamtzahl der Beiträge in diesem Zeitfenster.
  • Durchschnitt / Medianwert / Standardabweichung
    Die Implementierung dieser drei Werteberechnungen ist nicht ganz so trivial wie bei den vorangegangenen Beispielen.
    Der Einsatz einer Combiner-Funktion ist zwar möglich, macht allerdings der Algorithmus komplexer.
    Dazu mehr in den kommenden Blog Posts. 

 

Hinweise zu den Beispielen dieser Artikelserie

Wenn immer möglich nutze ich Visual Studio und C# für die Beispiele dieser Artikelserie.
In besonderen Fällen, wie beispielsweise bei der Erstellung von benutzerdefinierten Partitionern oder der Nutzung von besonderen Hadoop-Eigenschaften, werde ich auf Java zurückgreifen.

Als Datenbasis verwende ich einen Auszug von Stack Overflow der hier heruntergeladen werden kann.
Diese Daten stehen unter der Creative Commons BY-SA 3.0 Lizenz.

Zur Vereinfachung habe ich die Algorithmen zusammen mit der jeweiligen Job-Konfiguration in gemeinsame Konsolenprojekte gepackt.
In der Praxis sollten die Algorithmen in separate Klassenbibliotheken eingebettet werden.

Um Boilerplate-Code für die Nutzung des Hadoop-Streaming-APIs zu vermeiden, nutze ich das NuGet Package Microsoft .NET Map Reduce API For Hadoop.

Den Source Code werde ich auf hier auf GitHub veröffentlichen.

 

Beispiel "Min / Max / Count"

Das erste Code-Beispiele berechnet die Anzahl der Kommentare pro Benutzer in einem dynamischen Zeitintervall.

Hierzu extrahiert die Mapper-Funktion die Werte für UserId und CreationDate aus der XML-Quelle und gibt diese als Key/Value-Paare zurück:

public class MinMaxCountMapper : JsonOutMapperBase<MinMaxCountData>
{
  public override void Map(string inputLine, JsonMapperContext<MinMaxCountData> context)
  {
    var parsed = XmlUtils.ParseXml(inputLine);

    if (parsed == null 
        || !parsed.ContainsKey("CreationDate") 
        || !parsed.ContainsKey("UserId"))
    {
      context.CoreContext.IncrementCounter(
        "Min Max Count Mapper", "Invalid Rows", 1);
      return;
    }

    DateTime creationDate;
    if (!DateTime.TryParse(parsed["CreationDate"], out creationDate))
    {
      context.CoreContext.IncrementCounter(
        "Min Max Count Mapper", "Invalid Creation Dates", 1);
      return;
    }

    context.EmitKeyValue(parsed["UserId"], new MinMaxCountData
    {
      Min = creationDate,
      Max = creationDate,
      Count = 1,
    });
  }
}

 

Um einen eleganten Weg aufzuzeigen, wie man mit dem NuGet Package Microsoft .NET Map Reduce API For Hadoop auch komplexe Datenstrukturen übertragen kann, habe ich statt der üblichen MapperBase-Klasse diesmal von der JsonOutMapperBase-Klasse abgeleitet.

Diese bietet eine automatische Serialisierung ins JSON-Format an (Üblich wäre eine einfache Wertetrennung mittels Tabs).

Die hierbei verwendete Datenklasse sieht dabei wie folgt aus:

public class MinMaxCountData
{
  public DateTime Min { get; set; }
  public DateTime Max { get; set; }
  public long Count { get; set; }

  public override string ToString()
  {
    return String.Format("{0}\t{1}\t{2}", Min, Max, Count);
  }
}

 

Die Combiner- und Reducer-Funktionen aggregieren anschließend die entsprechenden Werte.

public class MinMaxCountCombiner :
  JsonInOutReducerCombinerBase<MinMaxCountData, MinMaxCountData>
{
  public override void Reduce(string key, 
    IEnumerable<MinMaxCountData> values, 
    JsonReducerCombinerContext<MinMaxCountData> context)
  {
    var data = values.ToList();

    context.EmitKeyValue(key, new MinMaxCountData
    {
      Min = data.Min(v => v.Min),
      Max = data.Max(v => v.Min),
      Count = data.Sum(v => v.Count),
    });
  }
}

 

Um die Daten später weiterverarbeiten zu können, gebe ich die Werte beim Reducer wieder im üblichen Tab-getrennten Format zurück.

public class MinMaxCountReducer : 
  JsonInReducerCombinerBase<MinMaxCountData>
{
  public override void Reduce(string key, 
    IEnumerable<MinMaxCountData> values, 
    ReducerCombinerContext context)
  {
    var data = values.ToList();

    context.EmitKeyValue(key, new MinMaxCountData
    {
      Min = data.Min(v => v.Min),
      Max = data.Max(v => v.Min),
      Count = data.Sum(v => v.Count),
    }.ToString());
  }
}

 

Ausführen im Hadoop Emulator

Um jetzt den Code im lokalen Hadoop Emulator auszuführen, kann zum Einen die im NuGet-Package mitgelieferte MRRunner.exe genutzt oder zum Anderen eine Konsolenapplikation erstellt werden:

public class MinMaxCountJob 
  : HadoopJob<MinMaxCountMapper, MinMaxCountCombiner, MinMaxCountReducer>
{
  public override HadoopJobConfiguration Configure(ExecutorContext context)
  {
    return new HadoopJobConfiguration
    {
      InputPath = "/samples/comments",
      OutputFolder = "output/MinMaxCount"
    };
  }
}

static void Main(string[] args)
{
  var hadoop = Hadoop.Connect();
  hadoop.MapReduceJob.ExecuteJob<MinMaxCountJob>();
}

 


Weitere Informationen Weitere Informationen:

Philipp Lenz: Automatisiert Adressen in Geo-Daten umwandeln und die Entfernung zu einem Standort berechnen

WeltkugelImmer wieder habe ich das Problem, dass ich haufenweise Adressen habe und diese bspw. in einem Report (Reporting Services) verarbeiten muss. D.h. ich brauche Geo-Daten und will diese natürlich nicht Datensatz für Datensatz in Längen- und Breitengrad übersetzen. In diesem Artikel will ich kurz beschreiben, wie eine CSV Datei mit Adressen automatisch mit den Google Webdiensten in Längen- und Breitengrad übersetzt wird und diese Daten dann im SQL Server wiederverwendet werden können.

Beispiel: Eine CSV Datei mit verschiedenen Imbiss Buden aus Koblenz (via den Gelben Seiten abgefragt):

2014-06-21_15-17-21

Via PHP wurde nun die Datei zeilenweise ausgelesen und die Adresse wurde an Google gesendet:2014-06-21_15-20-19

Die Ausgabe des Scripts ergibt folgendes:2014-06-21_15-22-20

Diese Daten können nun super per SSIS in die Datenbank eingelesen werden – natürlich kann man auch gleich die Daten in den SQL Server, bzw. die Datenbank schreiben. Die Tabelle dazu:
2014-06-21_15-24-48

Und nun brauchen wir noch eine Tabelle wo wir unseren eigenen Standort reinschreiben:
2014-06-21_15-25-29

Nun haben wir eine Tabelle in der die Adressen sind und auch eine Tabelle wo unser Standort enthalten ist. Über je ein Update Statement lassen wir gleich das GEO-Feld in den beiden Tabellen füllen (aus Längen- und Breitengrad) und über die STDistance Funktion können wir dann die jeweilige Entfernung zwischen dem Standort und der jeweiligen Adresse finden – so finden wir den nächsten Imbiss :-)

UPDATE dbo.Orte SET geo = GEOGRAPHY::STPointFromText(‘POINT(‘ + CONVERT(VARCHAR, laengengrad) + ‘ ‘ + CONVERT(VARCHAR, breitengrad) + ‘)’,4326)

UPDATE meinStandort SET geo = GEOGRAPHY::STPointFromText(‘POINT(‘ + CONVERT(VARCHAR, laengengrad) + ‘ ‘ + CONVERT(VARCHAR, breitengrad) + ‘)’,4326)

SELECT name, orte.adresse, orte.geo.STDistance(ms.geo) As [Entfernung In Meter] FROM dbo.Orte CROSS JOIN meinStandort ms

Sascha Lorenz: SQL Server PowerShell ? Lohnt es sich überhaupt damit zu beschäftigen ?

Diese Frage wurde mir in den letzten Tagen häufig gestellt, als es sich herumgesprochen hat, dass ich auf dem SQLSaturday #313 einen Crashkurs PowerShell für SQL Server Administratoren halte.

Und warum gerade ich? In den letzten Jahren bin ich ja eher nicht durch das Thema Administration in der Öffentlichkeit aufgefallen. Bin doch eher der BI Entwickler, welcher durch konsequente Nutzung der Möglichkeiten vieles im BI Projekt als auch im späteren Betrieb automatisiert.

Da PowerShell vereinfacht gesagt eine Shell ist, mit welcher das .NET Framework sehr komfortabel genutzt werden kann, bin ich wieder voll im Spiel und gefühlt ist meine SQLSat Session quasi die Fortsetzung vieler meiner Community-Projekte und Vorträge in denen ich mit .NET Bibliotheken gearbeitet habe.

Nur warum PowerShell für den SQL Server? “Wir” haben doch T-SQL und eine Menge an Komfortfunktionen rund um den Betrieb eines SQL Servers.

Da schaut es bei den Kollegen für Exchange Server oder SharePoint Farmen schon ganz anders aus. Die können sich mittlerweile ohne PowerShell gefühlt keinen Meter bewegen. Viele Funktionen sind, so wurde mir zugetragen, auch NUR noch über PowerShell erreichbar. Daher ist in diesen Communities das Thema auch wesentlich populärer als bei “uns”.

Also, wozu braucht nun ein SQL Administrator PowerShell?

Brauchen zurzeit noch nicht zwingend, dennoch ist es schon äußerst nützlich und zwar immer dann, wenn etwas automatisiert werden soll, was außerhalb des SQL Servers liegt. Oder wenn einfach mal der SQL Server Dienst selbst an der Reihe ist.

Neben meinem Community Leben beschäftige ich mich sehr intensiv mit Administration. Meine Kollegen bei der PSG verantworten große und komplexe SQL Server Umgebungen, welche zum Teil auch global und rund um die Uhr genutzt werden. Da ist das Thema Standardisierung und Automatisierung sehr wichtig, um einen konstant robusten Betrieb zu ermöglichen. Und der Betrieb betrifft häufig auch das Betriebssystem, Storage-Systeme, AD-Gruppen, Virtualisierung etc. pp. Vieles ließe sich sehr wahrscheinlich mit ein wenig Kreativität nativ in T-SQL lösen (und ich hätte einen Wahnsinnsspaß mir das auszudenken), nur hat sich um die SQL Server Welt herum im Microsoft Eco-System PowerShell als das Allzweckwerkzeug für viele Aufgabenstellungen entwickelt.

Und dann, dann ist da noch die Cloud. Ja, die Cloud. Und so richtig sinnvoll werden Szenarien in der Cloud erst, wenn der Grad an Automatisierung sehr hoch ist. Ich weiß, dass bei dem Thema noch viele Mitglieder aus der Community zusammenzucken und ein leises “das wird nie bis zu mir kommen…” von sich geben. Dennoch stelle ich in der Zusammenarbeit mit Microsoft fest, dass immer mehr Kunden die Einsatzmöglichkeiten von Azure prüfen oder gar schon am implementieren sind. Und auch hier ist eine Automatisierung der Provisionierung von Cloud Diensten am einfachsten mit PowerShell zu erreichen.

PowerShell soll kein Ersatz für das administrative T-SQL sein und vieles lässt sich auch sehr gut wenn nicht sogar einfacher damit abbilden. Dennoch ist PowerShell eine gute Ergänzung für die tägliche Arbeit und ermöglicht neue einfachere Lösungen.

Gebt PowerShell eine Chance, schaut Euch die grundlegenden Konzepte an und entscheidet dann.

Marcel Franke: June is my conference month

It was quite a busy time for me in the last month, with lot’s of very interesting project and exciting customers, especially in there area of Big Data and Data Analytics. That’s why it’s a little bit quite currently on my blog. Apologizes for this, but I will keep up writing. Additionally June seems to be my conference month for this year and I will be happy if I could see and talk to some of you. So here is my current schedule:

TDWI Conference 2014, 23. – 25.06.

I will have a session together with Ralph Kemperdick from Microsoft on “Analytical Platform System (former known as PDW) – Real World Experiences”

 

SQL Saturday 313, 28.6.

I’m very happy that I have again the chance to present on the SQL Saturday in Germany. Also this year the SQL Saturday will be at the Hochschule Bonn-Rhein-Sieg. And my Session will be about “Comparing SAP HANA, Power Pivot and SQL Server – In-Memory-Technologies”.

 

Datalympics 2014, 02.07.

This is a new conference to me, where I’m very excited about and happy to give a speech. My session will be on “Analytical Powerhouse − Data Analytics based on Microsoft”.

 

I think this will be a very exciting weeks and after my vacation I will follow up on my blogs.


Filed under: Uncategorized

Tillmann Eitelberg: SQLSaturday

In den letzten Monaten konnten Oliver (t: @oengels) und ich mit dem SQLSaturday #280 (Wien), #267 (Lissabon) und #281 (Edinburgh) wieder an verschiedenen SQLSaturday bei unseren euopäischen PASS Freunden teilnehmen. Mit unseren Vorträgen “Siena calling! Project Siena a RBID App?”, “Power BI – The self service BI Lifecycle in the cloud” und “Web Analytics with […]

Torsten Schuessler: Microsoft SQL Server 2012 Service Pack 2

The Service Pack 2 for Microsoft SQL Server 2012 (Build 11.0.5058) is available:

Bugs that are fixed in SQL Server 2012 Service Pack 2

Download:

Microsoft® SQL Server® 2012 Service Pack 2 (SP2)

I wish you a nice day,
tosc

Torsten Schuessler

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