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

Maximale Anzahl Zeilen je Datenseite und minimale Datensatzgröße

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

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

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

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

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

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

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

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

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

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

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

 

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

 

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

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

 

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

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

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

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

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

Woran das liegt, sehen wir uns an.

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

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

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

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

We will now look at the reason for this.

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

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

 

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

 

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

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

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

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

 

 DBCCPage_1vs2cols

 

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

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

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

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

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

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

 

  Page_Record_1col9byte_hex

 

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

Hier die Tabelle mit 2 Spalten:

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

Below, see the table with 2 columns:

 

 1410_Page_Record_2cols9byte_hex.png

 

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

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

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

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

 

Page_Record_2cols9byte_binary

 

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

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

 

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

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

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

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

 

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

 

Kommen wir nun zu Part 2:

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

Let us now look at Part 2:

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

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

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

 

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

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

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

 

 Row_Per_Page_Stage1_700

 

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

Was sagt DBCC Page dazu?

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

What does DBCC Page have to say to this?

 

 DBCCPage_Row_Per_Page_Stage1_700_Page_Header

 

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

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

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

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

 

…Insert 36 Rows…

 

Row_Per_Page_Stage2_2Pages

 

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

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

 

 Tab_Footer_Offset_FreeSpace_hex

 

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

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

 

Row_Per_Page_Stage3_736

 

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

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

 

 DBCCPage_Row_Per_Page_Stage3_736_Page_Header

 

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

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

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

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

 

 Tab_Footer_Offset_Full_hex

 

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

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

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

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

 

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

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

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

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

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

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

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

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

 

my nine bytes

Andreas

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

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

Simply: NO.

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

But we had to find a solution to this problem.

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

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

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

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

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

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

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

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

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

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

Disk where you start the SQL Database Migration Wizard from

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

NotSupportedByAzureFile.Config

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

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

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

SQLAzureMW.exe.config

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

DBCollation

The most important key that you have to specify is

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

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

BCPFileDir

Another important key is

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

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

Let’s do the migration

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

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

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

Be careful!

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

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


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

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

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

SQLSATURDAY_BARCELONA

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

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

SEEK?

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

SCAN...

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

PARALLELISIEREN

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

SQL Saturday in Barcelona

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

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

“¡hola de Barcelona”

Herzlichen Dank fürs Lesen!

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

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

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

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

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


[Database_Name]


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


[Database_Owner]


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


[Compatibility_Level]


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


[Collation_Name]


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


[SNAPSHOT_ISOLATION] und [READ_COMMITTED_SNAPSHOT]


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


[LOGICAL_NAME]


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


[TYPE_DESC]


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


[PHYSICAL_NAME]


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



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

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


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


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


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


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


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


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



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


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


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


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


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


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


Herzlichen Dank fürs Lesen!

Christoph Muthmann: Deutsche SQL Server Konferenz 2015

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

Full story »

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

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

Full story »

Christoph Müller-Spengler: Windows Server 2012, .NET Framework 3.5, 0x800F0906, 0x800F081F

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

During the setup the error message opened that

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

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

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

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

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


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

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

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

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

Now you know it too ;-)

Thank’s for reading


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

 

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

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

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

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

 Microsoft_Technical_Server_Summit

 

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

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

 

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

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

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

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

PASS_Summit_2014

Reporting Services Map Reports & Dynamic ZOomiNG:

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

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

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

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

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

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

 

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

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

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

 PASS_Camp

 I hope to see some of you around somewhere,

Andreas

Bernd Jungbluth: Seminar - SQL Server Reporting Services

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

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

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

Die Bereitstellung dieser zentralen Informationsplattform ist Inhalt dieses Seminars.

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

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

Die Teilnehmerzahl ist auf 8 Personen begrenzt

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

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

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

Was ist die PASS?

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

Was ist der PASS Summit?

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

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

Wann sollte man den PASS Summit besuchen?

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

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

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

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

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

Und wann nicht?

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

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

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

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

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

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

Meine persönlichen Erwartungen an den PASS Summit.

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

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

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

Herzlichen Dank fürs Lesen!

Christoph Muthmann: SQL Server 2008 Service Pack 4 has released

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

Full story »

Falk Krahl: Neues Servicepack für SQL Server 2008

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

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

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

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

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

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

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

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

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

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

So here’s the script from the eBook:

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

This might show you a result like this:

TotalSignalWaitTime

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

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

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

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

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

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

TotalSignalWaitTime_WithoutBenignWaits

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

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

BothSignalWaits

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

This leads us to the following script:

DECLARE @PercentageSignalWaitsOfTotalTime FLOAT
DECLARE @PercentageSignalWaitsOfTotalTime_WithoutBenignWaits FLOAT

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

SELECT		@PercentageSignalWaitsOfTotalTime - @PercentageSignalWaitsOfTotalTime_WithoutBenignWaits AS [IfNegativeThenCPUPressure]
			, @PercentageSignalWaitsOfTotalTime AS [PercentageSignalWaitsOfTotalTime]
			, @PercentageSignalWaitsOfTotalTime_WithoutBenignWaits AS [PercentageSignalWaitsOfTotalTime_WithoutPaulRandalsBenignWaits]

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

IfNegativeThenCPUPressure

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

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

BothSignalWaitsOnBoredSQLServer

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

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

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

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

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

DECLARE @PercentageSignalWaitsOfTotalTime FLOAT
DECLARE @PercentageSignalWaitsOfTotalTime_WithPaulRandalsExclusions FLOAT
Declare @SubstractedValue FLOAT

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

SELECT		@SubstractedValue = @PercentageSignalWaitsOfTotalTime - @PercentageSignalWaitsOfTotalTime_WithPaulRandalsExclusions;

SELECT		CASE
				WHEN @PercentageSignalWaitsOfTotalTime < 15.0  THEN 'no'
                WHEN @PercentageSignalWaitsOfTotalTime > 15.0
				     AND @SubstractedValue > 0.0 THEN 'no'
				WHEN @PercentageSignalWaitsOfTotalTime > 15.0
				     AND @SubstractedValue < 0.0 THEN 'YES'
			END AS [IsSQLServerUnderCPUPressure?]
			, @PercentageSignalWaitsOfTotalTime AS [PercentageSignalWaitsOfTotalTime]
			, @PercentageSignalWaitsOfTotalTime_WithPaulRandalsExclusions AS [PercentageSignalWaitsOfTotalTime_WithoutPaulRandalsBenignWaits];

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

FinalResult

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

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

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

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

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

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

I hope you enjoyed reading this blog post,
Thanks
Christoph


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

This SP has been released!

Full story »

Bernd Jungbluth: Seminar - Migration Access nach SQL Server

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

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

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

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

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

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

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

Der Anmeldeschluss ist am  7. November 2014.

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

Falk Krahl: Neues Servicepack für SQL Server 2008 R2

Für den SQL Server 2008 R2 ist bereits am Freitag ein neues Update erschienen. Es handelt sich dabei um das Servicepack 3. Es kann unter folgendem Link angefordert werden.
SQL Server 2008 R2 SP3

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

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

Ein Blick in die Interna einer Datenbankdatei

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

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

  • uniform extent
  • mixed extent

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

Mixed vs uniform extent

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

PFS = Page Free Space

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

GAM = Global Allocation Map

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

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

SGAM = Shared Global Allocation Map

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

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

    Bitmuster GAM SGAM

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

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

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

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



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

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



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

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


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



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


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

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



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

Analyse der Objektanlage


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


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



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

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



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

SELECT_RESULT_01


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


Transaktion – Tabelle erstellen


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



SELECT  Operation, Context, AllocUnitId, AllocUnitName, [Page ID], [Slot ID]
FROM    sys.fn_dblog(NULL, NULL)
WHERE   [Transaction ID]
IN
(
    SELECT  [Transaction ID]
    FROM    sys.fn_dblog(NULL, NULL)
    WHERE   [Transaction Name] = 'CreateTable'
);

fn_dblog_01


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


Transaktion – Datensatz eintragen


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


fn_dblog_02


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


Zusammenfassung


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


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



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

Hinweis:


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


Herzlichen Dank fürs Lesen!

Tillmann Eitelberg: Technical Summit 2014

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

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

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

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

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

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

 

Torsten Schuessler

Tillmann Eitelberg: Get together! Working with SSIS and HDInsight

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

Christoph Muthmann: Vergessene Jobs: eMails bereinigen

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

Full story »

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

SQL Server 2012 Service Pack 1 Cumulative Update #12

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

Cumulative update package 12 for SQL Server 2012 SP1

SQL Server 2012 Service Pack 2 Cumulative Update #2

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

Cumulative update package 2 for SQL Server 2012 SP2

I wish you a nice day,
tosc

Torsten Schuessler

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

Verwaltungs-Data Warehouse Datensammler & AlwaysOn Hochverfügbarkeitsgruppen

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

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

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

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

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

The following graph illustrates a possible setup using the latter:

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

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

 

 MDW_Data_Collector_AlwaysOn

 

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

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

Part 1: Databases in secondary role

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

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

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

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

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

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

Part 1: Datenbanken in Secondary-Rolle

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

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

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

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

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

 

AlwaysOn_AvailabiltyGroup_Config_ReadableSecondary

 

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

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

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

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

Part 2: Configuration of the MDW-Clients

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

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

Part 2: Konfiguration der MDW-Clients

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

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

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

 

MDW_Config_Server

 MDW_Config_Logins_Users_Roles

 

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

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

 

MDW_Config_Server_Proxy

 

MDW_Config_Proxy

 

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

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

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

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

 

 MDW_Report_Server_Selection

 

 

 

 

 

 

 

 

 

 

Happy collecting

 

Andreas

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

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

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

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

Christoph Muthmann: Vergessene Jobs: msdb sysssislog

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

Full story »

Philipp Lenz: Nachtrag zu Visual Studio Online

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

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

 

English Version:

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

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

Christoph Muthmann: Vergessene Jobs: syspolicy_purge_job

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

Full story »

Bernd Jungbluth: Access und SQL Server

Migration und Erstellung von Mehrbenutzeranwendungen

Access und SQL Server

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

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

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

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

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

Umfangreiche Leseprobe (208 Seiten als PDF - 8 MB) 

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

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

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

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

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

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

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

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

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

Full story »

SQLPodcast: #008 – Microsoft Azure

008

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

Download: SQLPodcast #008 (113)

Links:

Philipp Lenz: Wasserfall Diagramme mit PowerPivot

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

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

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

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

 

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

1

 

2

 

 

3

Nun müssen lediglich folgende Anpassungen gemacht werden:

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

4

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

 

Waterfall Chart with PowerPivot

 

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

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

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

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

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

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

 

2

 

 

3

 

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

4

Voila! Finish is the waterfall chart using PowerPivot.

 

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

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

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

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

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

BASTA! Herbst 2014 Speakerbutton 2


Tillmann Eitelberg: SSIS ReportGenerator Task 1.8

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

Philipp Lenz: Stichtagsinformationen zu einem Datum

uhr(english Version below)

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

 

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

Funktion / Function:

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

Abfrage / Query:

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

Tillmann Eitelberg: Dimension & Metrics

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

Tillmann Eitelberg: SSIS Analytics Version 1.7 beta

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

Falk Krahl: Neues Servicepack für SQL Server 2014

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

Torsten Schuessler: #CU package 3 for SQL Server 2014

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

Cumulative update package 3 for SQL Server 2014

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

I wish you a nice day,
tosc

Torsten Schuessler

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

PowerPivot_LogoEnglish version below

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

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

Hier der vorherige Weg:

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

 

 

  

  

 

  

 

 

 

 

 

Hier kommt nun die SWITCH Funktion ins Spiel.

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

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

Der Grundsyntax von SWITCH:

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

2014-08-18_12-41-07

 

 

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

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

 

 

  

  

 

  

 

 

 

 

 

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

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

The basic syntax of SWITCH:

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

2014-08-18_12-41-07
 

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

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

Testumgebung

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

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

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


Abfragen


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



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


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


EXECUTION_PLAN_01


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


EXECUTION_PROPERTIES_01EXECUTION_PROPERTIES_02




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


Begründung


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


Zusammenfassung


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


Herzlichen Dank fürs Lesen!


Tillmann Eitelberg: Google Analytics Sampling Data

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

Philipp Lenz: Standardfeldsatz in PowerPivot und PowerView

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

see english version below

Als Datengrundlage dient hier eine Abfrage aus der Adventure Works:

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

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

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

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

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

karte

Weitere Informationen gibt es hier:

English Version

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

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

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

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

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

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

karte

Uwe Ricken: Aufsteigende Indexschlüssel – Performance Killer

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

Problembeschreibung

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

Testumgebung

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

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

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



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

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


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



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

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



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

Testszenario


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



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

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


EXECUTION_PLAN_01


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


STATISTICSPARSER_01


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



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

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



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

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


EXECUTION_PLAN_02


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


STATISTICSPARSER_02


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


EXECUTION_PLAN_03


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


Warum eine geschätzte Anzahl von 1 Datensatz?


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


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



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

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



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

DBCC_STATISTICS_01


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


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


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



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

EXECUTION_PLAN_04


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



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

EXECUTION_PLAN_05


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


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


EXECUTION_PLAN_06


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



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

EXECUTION_PLAN_09


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


Lösungswege


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


Verwendung von Variablen


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



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

EXECUTION_PLAN_07


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



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

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



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

DBCC_STATISTICS_02


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


OPTIMIZE FOR UNKOWN


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


Optimierung des Index


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



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

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



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

EXECUTION_PLAN_08


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


UPDATE STATISTICS manuell ausführen oder als Auftrag implementieren


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


TraceFlag 2371


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


TraceFlag 2389, 2390


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


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


Zusammenfassung


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



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

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


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


Herzlichen Dank fürs Lesen!


Philipp Lenz: Time Dimension

uhr

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

(English Verison)

Als erstes brauchen wir eine Zeit Tabelle:

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

Mit folgendem Script befallen wir die Tabelle:

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

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

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

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

END

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

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

 

English Version

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

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

Now we must populate the table with data:

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

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

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

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

END

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

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

 

Bernd Jungbluth: Seminar - SQL Server Integration Services

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

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

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

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

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

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

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

Die Teilnehmerzahl ist auf 8 Personen begrenzt.

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

Torsten Schuessler: Time to Say Thank You!

Yes, it is SysAdminDay!

July 25, 2014 14th Annual
System Administrator
Appreciation Day

You are welcome:

I wish you ALL a nice SysAdminDay!

CU
tosc 

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

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

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

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