Category: "Storage Engine"

Index-Cheat-Sheet. The complete Index-compatibility Matrix for SQL Server

(DE)
Mit dem Aufkommen der Columnstore Technologie und auch der In-Memory Technologie in SQL Server 2014 sind insgesamt 4 neue Indextypen zu den althergebrachten page-basierten hinzugekommen.

Wo es früher nur die Auswahl an „Clustered oder Non-Clustered“ (Deutsch auch „gruppiert oder nicht-gruppiert“) gab, gibt es mittlerweile auch Clustered Columnstore, Non-Clustered Columnstore, Nonclustered Hash und Nonclustered Range-Indexe.

Und seit SQL Server 2016 sind diese Indextypen untereinander noch weiter kombinierbar.
Vereinfacht ausgedrückt lassen sich nun sowohl die Page-basierten Indexe als auch die Memory-optimierten Tabellen mit Columnstore Indexen kombinieren.
Page(„Disk“)-basiert + Memory-optimiert funktioniert jedoch nicht.

(EN)
With the emergence of the Columnstore technology and the In-Memory technology in SQL Server 2014, a total of 4 new index types have been added to the conventional page-based types.

When previously there had only been the choice of “Clustered or Non-Clustered,” now there are also Clustered Columnstore, Non-Clustered Columnstore, Nonclustered Hash und Nonclustered Range-Indexes.

And since SQL Server 2016, these index types can be further combined. Simply put, now it is possible to combine both page-based indexes and memory-optimized tables with Columnstore indexes.
However, Page (“Disk”)-based + Memory-optimized does not work.

Um auf schnelle Weise sehen zu können, welche Kombination an Indexen möglich ist, habe ich eine Matrix erstellt, welche die Kombinationsmöglichkeiten darstellt.
Angereichert mit Zusatzinformationen kann man den „Index-Spickzettel“ in Form einer pdf herunterladen. Und so sieht er dann aus:

In order to quickly determine which combination in indexes is possible, I created a matrix that illustrates the combination options.

Completed with additional information, you can download the “index cheat sheet” as a PDF document. This is how it looks:

 Index Cheat-Sheet_preview

Download

Herunterladen kann man es hier bei Technet:

Download

You can download it here at Technet:

gallery.technet.microsoft.com/Index-Cheat-Sheet-The-8378ac1b

 

Insgesamt sind gibt es also mittlerweile 4 verschiedene Basis-Strukturen:

  1. Heaps
  2. Clustered Indexe page-basiert
  3. Clustered Columnstore Indexe und
  4. sogenannte varHeaps, die Struktur der memory-optimierten Tabellen.

In total, there are now 4 different basic structures:

  1. Heaps
  2. Clustered page-based indexes
  3. Clustered Columnstore indexes and
  4. so-called varHeaps, the structure of memory-optimized tables

- Nicht enthalten sind: Indexed Views, XML-Indexe und Räumliche (Spatial) Indexe, sowie die Implikationen bei Sparse-Columns.
Prinzipiell basieren auch diese alle noch auf den page-basierten b-Tree Indexen, sind durch die teilweise auf der CLR basierenden Datentypen jedoch wesentlich eingeschränkter.

- Unique Indexe habe ich nicht gesondert betrachtet, da sich diese für meine Betrachtung nicht anders als ihre Nicht-Unique Pedanten verhalten.

- Not included are: Indexed Views, XML-Indexes and spatial indexes, as well as the implications in Sparse-Columns.

Generally, the latter are all still based on the page-based b-Tree indexes, but are significantly more restricted due to the data types partially based on CLR.

- I did not consider Unique Indexes separately as for the purpose of my consideration they do not behave differently to the way their non-unique counterparts do.

Zusätzlich habe ich noch einige Grundregeln und Höchstgrenzen mit aufgenommen.

Dem aufmerksamen Leser wird dabei vielleicht auffallen, dass die maximale Anzahl der Index-Spalten in SQL Server 2016 von 16 auf 32 angehoben worden ist. – Bitte seht das aber nicht als Einladung an, diese Limit auch nur Ansatzweise auszunutzen!

In addition, I have included a few basic rules and maximum limits.

The attentive reader may notice that the maximum number of index columns in SQL Server 2016 has increased from 16 to 32. – But please do not regard it as an invitation to even attempt to exploit these limits!

Ich hoffe diese Übersichten sind eine hilfreiche Erinnerungsstütze nicht  nur für Einsteiger, sondern auch für solche, die sich nicht immer an jede Regel erinnern können. Zum Weiterlesen sind auch einige Links auf BOL hinterlegt.

I hope these overviews are a helpful mnemonic device not only for beginners but also for those who don’t always remember every single rule. Links for further readings at BOL are also listed.

Hinweis: Die Übersicht basiert auf dem aktuellen Stand der Technik: SQL Server 2016.

Bereits gegenüber SQL Server 2014 gibt es wesentliche Unterschiede, und viel mehr noch zu 2012.
Kurz zusammengefasst gab es in der Version 2012 noch keine Memory-optimierten Tabellen und Clustered Columnstore Indexe. Diese kamen erst 2014 in den SQL Server. Und erst im 2016er gibt es die Kombinationsmöglichkeiten.

Folgende Artikel gab es zu diesen Themen bereits von mir:

Note: This overview is based on the current technical state: SQL Server 2016.

There are already significant differences to SQL Server 2014, and even more to 2012.
In a nutshell, in the 2012 version, there had not been any memory-optimized tables or Clustered Columnstore indexes. They only made it into the SQL Server in 2014. And only with the 2016 SQL Server there are the combination possibilities.

The following articles on these topics I have published before:

 

  1. The SQL Server 2016 In-Memory Evolution – from row-store to columnstore to in-memory operational analytics
  2. SQL Server 2016 – the Security & Performance Release / ein Sicherheits- und Performance-Release
  3. Artikel „SQL Server 2014 – Neues Fundament“ in iX Ausgabe 5/2014, Richtigstellungen zu In-Memory OLTP und ColumnStore Indexes und warum AlwaysOn nicht gleich Always On ist / SQL Server 2014 – New Fundament” in iX Issue 5/2014, corrections in In-Memory OLTP and ColumnStore Indexes, and why AlwaysOn is not the same as Always On
  4. The Columnstore Indexes & Batch Mode Processing (R)Evolution in SQL Server 2014

 

Happy Indexing

Andreas

The SQL Server 2016 In-Memory Evolution – from row-store to columnstore to in-memory operational analytics

 

(DE)
Der SQL Server 2016 ist da, und damit nun auch die „Version 2“ der In-Memory OLTP Engine, wenn man so will.

-           Was hinter der im SQL Server 2014 eingeführten In-Memory Engine von SQL Server steckt, habe ich auf diversen internationalen Konferenzen und in der IX bereits 2014 vorgestellt. Zu einem Blog-Artikel bin ich leider bisher nicht gekommen.

In diesem Artikel möchte ich die Neuerungen und Verbesserungen beleuchten, an denen Microsoft die letzten 2 Jahre gearbeitet hat, und die viel auf Kundenfeedback zurückzuführen sind. Feedback, welches in einem großen Maße noch aus „nicht umsetzbar, weil dies und dies fehlt“ bestand.

Und eines kann ich vorwegnehmen: in meinen Augen hat Microsoft die überwiegende Anzahl an Blockern adressieren können.

(EN)
SQL Server 2016 is finally available and, by extension, the “version 2” of the In-Memory OLTP Engine, if you will.

-           At various international conferences and already at the IX in 2014 I have presented what is behind the In-Memory Engine of SQL Server introduced in SQL Server 2014. Only I had not yet found the time to put it into a blog article so far.

In this article I will illuminate the innovations and improvements Microsoft has been working on for the past 2 years, and which can be attributed much to customer feedback. In fact, feedback that to a great extent consisted of notes like “not practicable because this and this is missing.”

And let me say one thing before I start: in my view, Microsoft has been able to address the majority of blockers.

Das heißt, In-Memory ist für jeden mindestens eine Evaluierung Wert, und in fast allen Datenbank-Projekten finden sich Strukturen, sie man In-Memory eleganter lösen kann. – Ok, nicht für ganz jeden, denn dieses Feature ist leider der Enterprise-Edition vorbehalten.

That means, everybody should at least consider evaluating In-Memory, and in almost all database projects there are structures that can be solved more elegantly In-Memory. – Ok, maybe not for everybody, because this feature is unfortunately limited to the enterprise edition.

 

Die wichtigsten Neuerungen für memory optimierte Tabellen sind:

Man kann nun sowohl Unique Indexe als auch Fremdschlüssel-Constraints definieren. Letztere sind nur zwischen memory-optimierten Tabellen möglich (und nicht zwischen Disk-/Page-basierten und memory-optimierten Tabellen), und müssen sich immer auf den Primärschlüssel beziehen – der Verweis auf Unique Indexe ist nicht möglich.

Auch sind nun NULL-Werte in Nicht-Unique Indexen zulässig (Anders als bei Disk-basierten Tabellen nicht in Unique-Indexen!).

Ebenfalls sehr wichtig ist die Unterstützung aller Codepages und von Nicht-Unicode-Daten sowie die Verschlüsselung der memory-optimierten Daten mit TDE (ergo nicht im Arbeitsspeicher selber sondern der Daten, die auf der Festplatte abgelegt werden). *1

Das waren in meinen Augen die häufigsten Blocker in Projekten, in denen In-Memory evaluiert wurde, da es dafür kaum praktikable Workarounds gab.

*1 Daten-Verschlüsselung mit den ENCRYPTION-Funktionen in SQL Server wird nicht unterstützt – das gilt auch für die neue Always Encrypted Technologie und Dynamic Data Masking.

Row-Level Security von SQL Server 2016 wird aber unterstützt. Die Prädikate und Funktionen, müssen dann nativ kompiliert werden. Sehr cool, wenn ihr mich fragt.

The most important innovations for memory-optimized tables are:

It is now possible to define Unique Indexes as well as foreign key constraints. The latter are only possible between memory-optimized tables (and not between disk-/page-based and memory-optimized tables), and must always refer to the primary key – referring to Unique Indexes is not possible.

Moreover, NULL-values in Non-Unique Indexes are now allowed (as opposed to disk-based tables not in Unique Indexes!).

Equally very important is the support of all code pages and of non-Unicode data as well as the encryption of memory-optimized data with TDE (hence not in the main memory itself but of the data that stored on disk). *1

In my view, these were the most frequent blockers in projects in which In-Memory was evaluated, as there were hardly any practicable workarounds for this issue.

*1 Data encryption with the ENCRYPTION functions in SQL Server is not supported – this is also true for the new Always Encrypted Technology and Dynamic Data Masking.

Row-Level Security of SQL Server 2016 yet is supported. The predicates and functions must consequently be compiled natively.

Very cool, if you ask me.

Eine weitere Einschränkung ist mit der Möglichkeit, Memory-optimierte Tabellen im Nachhinein zu ändern, entfallen.
Unterstützt ist das Hinzufügen, Entfernen, und Ändern von Spalten und Indexen im Nachhinein. Anstelle CREATE/ALTER/DROP Index muss hier nun ALTER TABLE verwendet werden, da bei Memory optimierten Tabellen Indexe Teil der Tabellen-Definition sind (und in  Gesamtheit kompiliert werden).
Besonders wichtig hierbei ist, dass man darüber nun auch den Bucket-Count von Hash-Indexen ändern kann, der sich naturgemäß ja im Betrieb sehr stark ändern kann mit der Zeit.

So sieht das im Code an einem Beispiel aus:

A further limitation has been eliminated with the possibility of altering Memory-optimized tables afterwards.
Adding, dropping and altering columns and indexes afterwards is supported. Instead of CREATE/ALTER/DROP index it must now be used ALTER TABLE, since in Memory-optimized tables indexes are part of the table definition (and are being compiled in its entirety).
It is particularly important here that it is now also possible to change the bucket count of Hash-indexes which during operation may naturally change considerably over time. 

This is how it looks in a sample code:

Add_Alter_Drop_Hash_Index 

Statistiken können auch mit SAMPLE anstelle FULLSCAN aktualisiert werden, und vor allem auch automatisch.

Datentypen: LOB-Datentypen wie varchar/varbinary(max) werden unterstützt, und werden „off-row“ gespeichert.

Statistics can also be updated with SAMPLE instead of FULLSCAN, and, above all, automatically as well.

Data type: LOB datatypes such as varchar/varbinary(max) are supported and stored “off-row.”

Wichtige, neu unterstützte T-SQL Funktionalitäten innerhalb von Natively compiled Stored Procedures und, ganz neu, Functions sind: die OUTPUT-Klausel, UNION und UNION ALL, DISTINCT, OUTER JOINs, Unterabfragen.

Außerdem können nativ kompilierte Prozeduren nun auch mit ALTER PROCEDURE verändert werden. Dadurch werden sie naturgemäß im letzten Schritt in der neuen Form kompiliert abgelegt.
Um für den Fall von geränderten Statistiken einen neuen Ausführungsplan zu ermöglichen, kann man nun auch sp_recompile gegen nativ kompilierte Prozeduren (und Funktionen) ausführen.

Important, newly supported T-SQL functionalities within Natively compiled Stored Procedures and, brand new, Functions, are: the OUTPUT clause, UNION and UNION ALL, DISTINCT, OUTER JOINs, subqueries.

Moreover, natively compiled procedures can now also be changed with ALTER PROCEDURE. In this way, they will naturally be stored compiled in the new shape in the last step.
In order to facilitate a new implementation plan in the case of changed statistics one can now also executive sp_recompile against natively compiled procedures (and functions).

 

Auch an der Performance wurde weiter geschraubt. So können memory-optimierte Tabellen und Hash-Indexe jetzt (im InterOP Mode) parallel gescannt werden. Im IO Bereich wurde der gesamte Checkpoint-Prozess überarbeitet und die Datenfiles können nun mit multiplen Threads geschrieben und gelesen werden, wodurch sich der Durchsatz fast auf ein zehnfaches erhöhen kann (wenn das IO-Subsystem da mithält).

Performance, too, was further tweaked. As a result, memory-optimized tables and Hash-indexes can now (in InterOP mode) be scanned simultaneously. In the IO area, the entire checkpoint process was reviewed and the data files can now be read and written with multiple threads, which may result in an almost tenfold increase of the throughput (if the IO-subsystem keeps up with it).

 Parallel_Scan_HashIndex_In-MemoryOLTP

 

Columnstore-Technologie

Was hat sich eigentlich in der anderen, seit 2012 im SQL Server integrierten Storage-Engine „Vertipaq“ mit den Columnstored Indexen getan? Diese sind ja ebenfalls Main-memory optimiert, jedoch mit ganz anderem Ziel: Speicherplatzoptimierung und effiziente OLAP-Style-Queries.

Die Neuerungen hier sind sehr essentiell:

Beide Columnstore Index Typen, Clustered und Nonclustered, sind nun aktualisierbar!
Außerdem lassen sich Columnstore Indexe nun mit weiteren traditionellen btree-Indexen ergänzen. Das ist wichtig, weil ja nicht jede Abfrage wirklich von der Columnstore-Speicherform profitiert. Dieser Zugewinn an Flexibilität ist ein entscheidender Vorteil gegenüber den bisherigen Releases und kann gar nicht genug betont werden.

Und noch etwas ist nun möglich: Nonclustered Columnstore können mit einem Filter erstellt werden.

Columnstore Technology

What has been going on in the other Storage-Engine “Vertipaq”, integrated in SQL Server since 2012, with the Columnstored Indexes? These are also Main-memory optimized, but with an entirely different objective:
Storage space optimization and efficient OLAP-style queries.

The innovations here are very essential:

Both Columnstore Index Types, Clustered and Nonclustered, can now be updated!
Additionally, Columnstore Indexes can now be extended with further traditional btree-indexes. This is important, as not every query really profits from the Columnstore storage form. This gain in flexibility is a decisive advantage over the previous releases and cannot be emphasized enough.

And something else is now possible: Nonclustered Columnstore can be created with a filter.

Mit diesen neuen Techniken lässt sich zum Beispiel folgendes Problem lösen:

Eine Tabelle mit Verkaufstransaktionen wird im Sekundentakt durch kleine Inserts gefüllt.
Nebenbei möchte man aber auch diverse Berichte mit Tages- und Tageszeitaggregationen bereitstellen. So aktuell wie möglich natürlich.
Das Problem ist hierbei typischerweise, dass man sich hier entscheiden muss zwischen Indexen für alle Berichtsabfragen und denen, die minimal notwendig sind für etwaige Updates. Inserts benötigen ja für sich gesehen keine Indexe.
Durch diese Kombination entstehen die mit vielen Indexen überfrachteten OLTP-Tabellen, die ich bei meinen Einsätzen oft entdecke und die es dann gilt, „wegzuoptimieren“.

Mit der Möglichkeit einen Nonclustered Columnstore Index zusätzlich zu dem Clustered Index anzulegen, spart man nicht nur Indexe, (denn der Columnstore-Index kann ja jede nötige Spalte abdecken) sondern mit einem geschickt gesetzten Filter kann man auch den Index-Overhead vermeiden, der sonst die eigentlich wichtigeren Inserts treffen würde.

Die Vermischung aus OLTP- und OLAP-Abfragen sind eines der typischsten Probleme von Datenbanken, und diese neuen Möglichkeiten sind daher einfach ein Traum für Datenbank-Architekten.
So sieht das im Code aus:

By means of new techniques the following problem can be solved, for example:

A table with sales transactions is filled by small inserts at intervals of seconds.
At the same, one would also like to provide various reports on day and daytime aggregations. Maximally up to date of course.
The problem typically lies in the fact that one has to decide between indexes for all report queries and those that are minimally required for possible updates. Inserts viewed in isolation do not require any indexes.
This combination results in the OLTP tables overloaded with many indexes, which I frequently discover during my work and that then need to be “optimized” (removed).

The possibility to create a Nonclustered Columnstore Index in addition to the Clustered Index does not only save Indexes (because the Columnstore Index can cover every necessary column), but with a smartly applied filter the Index-Overhead can also be avoided that would otherwise affect the actually more important inserts.

The mixing of OLTP and OLAP queries are one of the most typical problems in databases, and these new possibilities are thus simply a dream for database architects.

This is how it will look in code:

 Filtered_NonclusteredColumnstore_Index_SQLCode

40% Performance-Verbessrung im TPC-H Benchmark

Diese Verbesserungen haben den SQL Server 2016 im TPC-H Benchmark performance-technisch mit fast 40% mehr QphH (Query-per-Hour Performance Metric) am SQL Server 2014 vorbeiziehen lassen. Auf dem Screenshot kann man sehen, dass der Benchmark am 9.3.2016 eingesendet wurde, und auch wirklich auf derselben Hardware wie am 1.5.2015 unter SQL Server 2014 erzielt wurde.

40% Performance-Improvement in TPC-H Benchmark

In terms of performance, these improvements have made the SQL Server 2016 pull ahead of SQL Server 2014 by close to 40% more QphH (Query-per-Hour Performance Metric) in the TPC-H Benchmark. You can see in the screenshot that the Benchmark was sent in on 9 March 2016 and really was achieved on the same hardware as under SQL Server 2014 on 1 May 2015.

 TPC-H_3000GB

Weitere wichtige Verbesserungen für Columnstore sind die Unterstützung des SNAPSHOT Isolation Level (und RCSI), was besonders für Read-Only Replicas  von Availability Groups wichtig ist, sowie Online-Defragmentierung und diverse Analyse-Verbesserungen.

Further important improvements for Columnstore include the support of the SNAPSHOT Isolation Level (and RCSI), which is especially important to Read-Only Replicas of Availability Groups, as well as online-defragmentation and various analysis enhancements

 

Das Highlight: Real-time Operational Analytics & In-Memory OLTP

Das Highlight Schlechthin aber ist sicherlich die Kombination aus memory-optimierten Tabellen und ColumnStore Indexen.

Damit werden zwei Features, die für völlig gegensätzliche Abfrage-Typen, OLTP und OLAP, optimiert sind, verschmolzen.

The highlight: Real-time Operational Analytics & In-Memory OLTP

The absolute highlight however is surely the combination of memory-optimized tables and ColumnStore Indexes.

As a result, two features that are optimized for entirely opposing query types – OLTP and OLAP – are merged.

Technik

Technisch kommt ein Clustered Columnstore Index zum Einsatz. Dieser lässt, wie man in dem Bild sehen kann, den „hot-Bereich“ der Daten aus, um den Overhead durch die doppelte Datenhaltung bei Änderungen bzw. den potentiell schnell aufeinander folgenden Inserts in diesem Bereich zu vermeiden. Zusätzlich zu der implizierten Delta Rowgroup (im Bild: Tail), die durch den memory-optimierten Index abgedeckt wird, gibt es eine „deleted rows table“  für gelöschte Daten. Beide Bereiche werden nach dem für Columnstore Indexe standardmäßigen Schwellwert von 1 Million Zeilen asynchron komprimiert/dem CCI hinzugefügt.

An dieser Stelle noch ein Hinweis: auch die maximale Datenmenge, die pro Datenbank in (durable) memory-optimierten Tabellen gehalten werden kann, ist nun entfernt worden!
Nach aktuellem Stand der Technik sind unter Windows Server 2016 damit theoretisch bis zu 12 TB (abzgl. einem Maintenance-overhead) in XTP-memory speicherbar!

Von der Struktur her gestaltet sich das so:

Technical facts

In technical terms, a Clustered Columnstore Index is applied. As can be seen in the image, it omits the “hot-spot” of the data in order to prevent the overhead through the double data storage in case of alterations and the potentially quick succession of inserts in this area. In addition to the implied Delta Rowgroup (in the image: Tail) that is covered by the memory-optimized index, there is a “deleted rows table” for deleted data. Both areas are asynchronously compressed/added to the CCI according to the Columnstore Index standard threshold value of 1 million cells.

At this point, let me add another note: the maximum data amount that can be stored per database in (durable) memory-optimized tables has now been eliminated, too!
As a result, according to the current technical state, in theory up to 12 TB (less a maintenance overhead) can be stored in XTP-memory under Windows Server 2016!

In terms of structure, it would look like this:

 SQL2016_Real_time_operational_analytics

Und die Umsetzung in Code sieht so aus:

And applied in code, it would look like this:

 MemoryOptimizedTable_ClusteredColumnstore_Index_SQLCode

Im Ergebnis hat man nun das Beste aus beiden Welten: hochperformante Inserts/Updates/Deletes und Singleton-Abfragen, und zugleich hochperformante analytische Abfragen, die mit vielen Millionen Zeilen auf einmal hantieren – und zwar zeitgleich auf derselben Tabelle!

The outcome now offers the best from both worlds: high performing inserts/updates/deletes and singleton-queries, and at the same time high performing analytic queries that handle many millions of cells at once – and in fact at the same time in the same table!

 MemoryOptimizedTable_ClusteredColumnstore_Index

Eine Einschränkung beim Abfragen ist, dass der Columnstore Index auf memory-optimierten Tabellen nur im InterOP-Modus funktioniert – also nicht in nativ kompilierten Prozeduren.

Und da wären wir beim letzten Thema:

One restriction in querying is that the Columnstore Index in memory-optimized tables only works in the InterOP mode – thus not in natively compiled procedures.

Which leads us to the last topic:

 

Offene Punkte, fehlende Feature-Unterstützung
Es gibt natürlich auch jetzt noch eine ganze Reihe an Features, die durch die über jahrzehntelange andauernden Entwicklung der SQL-Sprache in den SQL Server übernommen wurde, aber eben noch nicht in die neue XTP-Engine gelangt sind. Das liegt nicht nur daran, dass diese „einfach neu“ ist, sondern auch, dass durch die völlig andere Architektur dieser radikal auf In-Memory getrimmten Engine einige wesentliche Unterschiede zu den althergebrachten Datenbank-Engines bestehen.

Folgende Features vermisse ich persönlich noch am meisten:

  1. DBCC CHECKDB/CHECKTABLE für memory optimierte Tabellen
  2. CASE-Statement
  3. Filtered Indexe
  4. CTEs
  5. Replikation
  6. OFFSET-Operator
  7. Ranking Funktionen
  8. DDL Trigger für CREATE/DROP TABLE und CREATE/DROP PROCEDURE
  9. TRUNCATE TABLE
  10. DATA_COMPRESSION
  11. Datentyp datetimeoffset

Die vollständige Liste befindet sich hier:

Open points, missing feature support

There are of course still a number of features that have been taken over into SQL Server due to the decade-long development of the SQL language, but which have not made it into the new XTP Engine yet. This is not just because the latter is “simply new” but also because due to the completely different architecture of this engine, which is radically tailored to In-Memory, there are several significant differences vis-à-vis the traditional database engines.

Personally, I miss the following features the most:

  1. DBCC CHECKDB/CHECKTABLE for memory-optimized tables
  2. CASE-Statement
  3. Filtered Indexes
  4. CTEs
  5. Replication
  6. OFFSET-Operator
  7. Ranking Functions
  8. DDL Trigger for CREATE/DROP TABLE and CREATE/DROP PROCEDURE
  9. TRUNCATE TABLE
  10. DATA_COMPRESSION
  11. Data type datetimeoffset

For the complete list, visit:

 

Transact-SQL Constructs Not Supported by In-Memory OLTP

Call to Action

Auch wenn die Liste an fehlendem Feature/Funktionssupport immer noch recht lang ist – die wenigsten nutzen wirklich all diese Features voll aus. Und für die meisten dieser übriggebliebenen „Blocker“ gibt es eigentlich recht gute Workarounds, sei es in Form einer anderen Architektur oder Code-technisch. Man muss auch bedenken, dass die In-Memory Tabellen nicht für alle Szenarien überhaupt Sinn machen, sondern eher für die Top-belasteten Tabellen Sinn machen. Und da sollte man sich ohnehin bereits etwas Mühe beim Design gemacht haben.

Generell bin ich der festen Meinung, dass sich in fast jedem Datenbankprojekt einige Stellen finden werden, die von In-Memory Funktionen profitieren können.

Warum kann ich das so sicher sagen?

Bereits seit SQL 2014 gibt es die Möglichkeit neben Memory-optimierten Tabellen auch Memory- optimierten Tabellen Variablen zu verwenden. Und mit diesen lassen sich wiederum viele Temptable-Konstrukte ablösen.

Nun werden dadurch nicht unbedingt gleich ganze Applikationen performanter, aber es ist ein Anfang, sich mit In-Memory Codetechnisch auseinanderzusetzen und langsam aber sicher damit zu programmieren. Ein weiterer „Quick-Win“ ist oft in Datawarehouse-Architekturen im sogenannten „Staging-Bereich“ zu finden, wie er gerade in traditionellen DW-Systemen häufig zum Einsatz kommt.

Und über diese „Einfallstore“ ist man ganz schnell in der „In-Memory-Welt“ angekommen.

Call to Action

Even though the list of missing feature/function support is still quite long, only few really make full use of these features. And for most of the remaining “blockers” there are actually quite good workarounds, be it in the form of a different architecture or in code terms. One has to bear in mind that the In-Memory tables do not necessarily make sense for all scenarios, but rather for the top-affected tables. And as for the latter, one should already have put some effort into the design anyway.

In general, I firmly believe that in almost every database project there are some instances that may profit from In-Memory functions.

Why can I be so sure?

Already since SQL 2014 it has been possible to use memory-optimized table variables aside from memory-optimized tables. And using these, in turn, many temptable-constructs can be replaced. Now that does not necessarily result in higher performing applications right away, but it is a good way to start dealing with In-Memory in terms of code and to slowly but surely start programming with it. A further “Quick-Win” can often be found in data warehouse architectures in the so-called “staging area,” as it is frequently being applied in traditional DW-systems at the moment.

And it is via these “gateways” that you have will have ended up in the “In-Memory world” before you know it.

 

Cu In-Memory

Andreas

PS:

If you are in India in August and want to advance your skills in those new technologies there is still a chance to get a seat in the Precon "Present and Future: In-Memory in SQL Server – from 0 to Operational Analytics Master" at SQL Server Geeks Summit in Bangalore on August 10.

SQL Server Geeks Conference

Konferenzen im SQL Server Sommer 2016 – Conferences in SQL Server summer 2016

(DE)
Kaum von den SQLBits XV in Liverpool zurück, auf der ich die Security-Features des SQL Server 2016 vorstellen durfte (SQL Server 2016 - Security Obscurity and Encryption), bin ich im Moment mit der Planung der nächsten SQL Server Master-Class zu Hochverfügbarkeit mit SQL Server (SHA) beschäftigt, die vom 20.-22.6. bei Frankfurt a.M. stattfinden wird.

Hinweis: in den 3 Wochen seit Freischaltung der Anmeldung sind bereits 70% der Plätze vergeben worden. Wer sich noch einen Platz sichern möchte aber nicht weiß, wie schnell seine Einkaufsabteilung es schafft, kann sich gern an mich direkt wenden.
- Mitglieder der PASS Deutschland e.V. erhalten auch diesmal 15% Rabatt.

(EN)
It feels like I have just been back from the SQLBits XV in Liverpool, where I introduced the Security Features of the SQL Server 2016 (SQL Server 2016 - Security Obscurity and Encryption), and next thing I know, I am already in the middle of planning the next SQL Server Master-Class on High Availability with SQL Server (SHA) that is going to take place from 20-22 June near Frankfurt a.M.

Note: in the 2 weeks since activation of registration already 70% of the places have already been filled. If you want to secure a spot but don’t know how you’re your purchase department will make it, you are welcome to contact me directly.
- Members of PASS Deutschland e.V. will once again receive a 15% discount.

 

 

Davor noch aber findet, wie die beiden Jahre zuvor, der deutsche SQLSaturday „Rheinland“ in der Hochschule St. Augustin bei Bonn statt.
Diesen unterstützt meine Firma, Sarpedon Quality Lab®, wie auch das Jahr zuvor als Sponsor.

But before, the German SQLSaturday „Rheinland“ is taking place, like the previous two years.
My company, Sarpedon Quality Lab®, will be a sponsor again like last year.

  

Ich selber werde auch mit einem Vortrag vertreten sein. Diesmal mit einem Beitrag zu ganz klassischer Performance Tracing. Allerdings nicht, wie man immer noch hin und wieder „in the wild“ sieht mit SQL Profiler, sondern natürlich mit aktuellen Technologien wie Extended Events und sogar der neuen Query Store von SQL Server 2016:

I will also be presenting. This time, it is going to be on the entirely classical topic of Performance Tracing. However, not with SQL Profiler as can be seen once in a while “in the wild,” but of course with current technologies such as Extended Events and even the new Query Store by SQL Server 2016:

Analysieren von SQL Server Workloads mit DMVs und XEvents

Diese Session führt durch eine beispielhafte Performance-Analyse unter der Verwendung von DMVs und Extended Events. Wir sehen, wie man eine Top-Down Analyse mit eingebauten Tools durchführen kann und wie man eine feingradige Analyse selbst mittels den „Wait Statistics“ durchführen kann, um Performance-Problemen und Bottlenecks auf die Schliche zu kommen. Zur Identifizierung von Plan-Änderungen wird auch die neue Query Data Store von SQL Server 2016 zum Einsatz kommen. Wer auf dem Feld der Performance-Analysen noch neu ist oder sehen möchte, wie man an die Herausforderung herangehen kann, wird hier praktische Einblicke erhalten, wie man eine Workload analysieren kann. – Die Session ist selbstverständlich „Profiler-free“ ;-)

Performance Analyzing SQL Server workloads with DMVs and XEvents

This session you will be lead you through an example performance-analysis using mainly DMVs and Extended Events. You will see how a top-down analysis using built-in tools can be conducted. This will include wait statistics on different scopes to identify performance problems and bottlenecks up to identifying query plan changes – with & without using the Query Store of SQL Server 2016. If you are new to performance analyzing this session will give you a practical insight into how to methodically approach performance troubleshooting.

 

 

Asien

Im August bin ich wieder in Asien unterwegs und das zweite Mal in Folge auf der größten SQL Server Konferenz Asiens: dem SQL Server Geeks Summit in Bangalore, Indien.

Asia

In August, I will be traveling Asia again and attending the largest SQL Server Conference in Asia for the second time in a row: the SQL Server Geeks Summit in Bangalore, India.

 

Bangalore

Dort werde ich dieses Jahr eine ganztägige Pre-Con geben. Thema: Die In-Memory Storage Engines von SQL Server, die mit dem SQL Server 2016 umfangreich verbessert worden. Das heißt es geht um ColumnStore, Memory Optimized Tabellen, Memory Optimized Indexe, und die Kobinationsmöglichkeiten mit traditioneller Row-Store oder auch ColumnStore für Mixed OLAP als auch OLTP workloads.
Es wird super spannend und sehr technisch. Wer sich damit noch nicht beschäftigt hat: es wird höchste Zeit. Die Art und Weise, wie man Datenbanken konzipiert und designt ändert sich zurzeit rapide. Ich bleibe bei meinen Vorhersagen, dass ColumnStore bald der Standard für Datawarehousing sein wird, und In-Memory OLTP der standard für hochperformante OLTP-Szenarien. Hier zeige ich die technischen Hintergründe und Machbarkeiten.

This time, I will be giving a full-day Pre-Con. Topic: The In-Memory Storage Engine of SQL Server that have been extensively improved with SQL Server 2016. That is, it will be about ColumnStore, Memory Optimized Tables, Memory Optimized Indexes, and the combination possibilities with traditional Row-Store or also ColumnStore for Mixed OLAP as well as OLTP workloads.
It will definitely be super exciting and very technical. If you haven’t dealt with this yet: it is about time! The way of conceptualizing and designing databases is changing rapidly at the moment. I am sticking to my predictions that ColumnStore will soon be the standard for Datawarehousing, and In-Memory OLTP the standard for high performing OLTP scenarios.
At this Pre-Con, I will be demonstrating the technical background and feasibility.

 

Pre-Con Title:

The Present and Future: In-Memory in SQL Server – from 0 to Operational Analytics Master

Track:                DBA/DEV

Pre-Con Abstract:

When the Columnstore Index technology, based on the xVelocity In-Memory engine, came with SQL Server 2012 in the form of Nonclustered Columnstore, and SQL Server 2014 brought us updatable Clustered Columnstore Indexes plus a completely new In-Memory OLTP Engine, “XTP”, for memory optimized table & indexes, those features were still new and because of their limitations used only rarely.

SQL Server 2016 takes both technology onto a whole new level:

Columnstore indexes among other things now support snapshot isolation and hence fully support readable secondaries. Batch execution is not exclusively for parallel threaded queries anymore. They can be combined with other B-tree indexes and even be filtered and support referential integrity with primary and foreign key constraints. Also so-called In-Memory Operational Analytics is supported by the ability to create Columnstore Indexes on memory optimized tables.

On the other hand the In-Memory engine has been extensively improved in terms of both scalability and T-SQL language support, taking away many of the relevant limitations for adaption of version 1 in a similar way than the Columnstore technology. For example altering of pre-compiled objects is now possible, bucket-counts can be adjusted, natively compiled stored procedures can be recompiled and foreign keys are supported as well as encryption with TDE.

All those improvements will make In-Memory technologies a viable option in many projects. For Datawarehouses many (including me) say, that Columnstore will become the default storage type for all objects. And it can be foreseen that over the years the same will happen for OLTP-tables that have to support highly concurrent workloads will be based on memory optimized tables.

It’s time to extend our skills to those technologies to be able implement and support the new types of storage that are coming to our databases to address the fact of ever more data being stored and queried and performance demands and (real time) analytic requirements going up.

At this full-day training day, Microsoft Certified Master for the Data Platform Andreas Wolter, familiar with SQL Servers In-Memory technologies from the early bits on, will give a complete picture on the current state of technology. Attendees will learn how and where to use either In-Memory OLTP or Columnstore or even both for efficient queries and data storing and the important bits both from developers and administrators perspective.

Modules/Topics Include:

1.          Columnstore Storage Engine and compression internals

2.          What is the benefit for OLAP performance

3.          When to use Clustered or Nonclustered Columnstore Indexes

4.          XTP Engine internals for In-Memory OLTP performance benefits

5.          Memory optimized Tables, indexes and Variables

6.          Natively compiled stored procedures & triggers

7.          Combination of Row-Store, Columnstore/xVelocity and XTP engine for operational analytics

Key Takeaways:

1.          How the new storage engines Columnstore & XTP work behind the covers

2.          What are the strengths and weaknesses of these alternate storage engines and how can they be played out best

3.          How to get a quick start with In-Memory optimized objects in almost any environment

4.          What are the typical performance patterns that these technologies address

5.          How to build highly performing Datawarehouse tables

6.          How to improve OLTP hotspot tables with In-Memory technologies

7.          How to enable real-time analytics of operational data

8.          What’s important from file management perspective for administrators

9.          How can Columnstore and In-Memory Hash- & Range-indexes be maintained

10.        What hotspots can you expect for those technologies – or is there any?

Demos:

1.          Performance-Improvements for OLAP workloads with Nonclustered Columnstore indexes …

2.          … Clustered Columnstore indexes

3.          Performance-Improvements for OLTP workloads with memory optimized tables, indexes and code

4.          Operational analytics on row store vs operational analytics on In-Memory

5.          … all under different workload-types

6.          How do Columnstore indexes handle updates to data under the covers

7.          How In-Memory optimized objects look like on disk

Attendee Pre-requisites:

1.          Basic T-SQL knowledge for code-reading

2.          clustered vs nonclustered indexes basics

 

Obendrein werde ich noch zwei normalere Sessions auf der Hauptkonferenz geben. Die Themen stehen noch nicht fest.

Ich freue mich bereits wieder auf das enthusiastische Publikum in Indien!

Added to that I will give two more normal sessions at the main conference. The topics are not final yet.

I am looking forward to the enthusiastic audience in India again!

 

Nach Indien werde ich auf der SQLSaturday Singapore präsentieren. Diese Konferenz wird bei Microsoft Singapore Operations Pte Ltd, One Marina Boulevard stattfinden – inmitten der berühmtesten Sehenswürdigkeiten Singapurs.

Hier gebe ich möglicherweise auch eine PreCon, aber die Planung ist noch nicht abgeschlossen, also mal schauen, was es sein wird. Auch auf dieses Event freue ich mich sehr.

After India I will be presenting at SQLSaturday Singapore. This event will be held at Microsoft Singapore Operations Pte Ltd, One Marina Boulevard – right in the center of the most famous sights of Singapore.

Also here I might give a PreCon, but the planning is not finalized yet, so let’s see what it will be. I am very much looking forward to this event as well.

SingaporeCu in St. Augustin, Bangalore or Singapore – your turn to choose ;-)

Andreas

My conference-sessions in 2015: from Extended Events over In-Memory to Security

(en)
Finally I get to write about my conference-talks in 2015.

The year started really great with the German SQL Server Konferenz in Darmstadt with 2 sessions on In-Memory OLTP in SQL Server 2014 – one of them being even a full-day PreCon, which I co-presented with Niko Neugebauer,  who was talking about Clustered ColumnStore Indexes.

(de)
Endlich komme ich dazu, auf meine Konferenz-Vorträge dieses Jahr einzugehen.

Das Jahr begann gleich großartig mit der Deutschen SQL Server Konferenz in Darmstadt mit 2 Sessions zu In-Memory OLTP in SQL Server 2014 – eine davon sogar eine ganztägige PreCon, welche ich mit Niko Neugebauer co-präsentierte, der über Clustered ColumnStore Indexe sprach.

 PreCon: “In-Memory Internas: Clustered Columnstore & In-Memory OLTP Deep Dive”

Session: “In-Memory OLTP für Entwickler“ (In-Memory OLTP for Developers)

 

In March I was happy to have been speaker for the second time at the SQLBits in London -
probably THE event outside of PASS in Europe if not worldwide.
There I presented one of my favorite topics on Performancetools: Extended Events

Im März war ich glücklich zum zweiten Mal auf den SQLBits in London als Sprecher zu sein – wahrscheinlich DEM Event außerhalb der PASS in Europe wenn nicht weltweit.
Dort präsentierte ich zu einem meiner Lieblingsthemen zu Performancetools: Extended Events

 

Extended Events – Top Features

 

In April I joined the SQLDay Poland for the first time, giving a session on Locking & Blocking and a second session on Security, namely SQL Injection:

Im April war ich das erste Mal auf dem SQLDay Poland  , mit einer Session zu Sperren & Blockaden und einer zweiten Sessio zu Sicherheit, genauer SQL Injection:

 

From Locks to Dead-locks.  – Concurrency in SQL Server

“SQL Attack…ed” – SQL Server under attack via SQL Injection

 

For June I am happy to be able to announce that I will again present on Extended Events at the SQL Saturday #409 Rheinland/Germany. -
In fact I have already presented at the first German SQL Saturday in 2012 on Extended Events (“Tracing with SQL Server 2012 Extended Events”) – I hope this year it will be the final round to get even the last one off from the old & dusty SQL Profiler.

Added to that I am honored to give one of the 2 full day and free of cost PreCons: Together with Patrick Heyde, Microsoft (Technet-Blog), I will talk on SQL Server in Azure Environments. Specifically on how to optimally configure such a deployments to performance and cost-efficiency at the same time. (Also see my blog article “SQL Server in Microsoft Azure: How to gain performance by flexibility and save costs at the same time”)

Besides giving a free PreCon and regular session, my Company Sarpedon Quality Lab is again officially sponsoring this home-event. This is part of my commitment to the German PASS Community – especially since my own family starts taking more time, this is a bit of what I can do to support the PASS Deutschland e.V..

Für den Juni darf ich mich glücklich schätzen, bekanntzugeben, dass ich auf dem SQL Saturday #409 Rheinland wieder zu Extended Events präsentieren werde. – Tatsächlich hatte ich bereits auf dem ersten Deutschen SQL Saturday 2012 zu Extended Eventzs präsentiert
(“Tracing with SQL Server 2012 Extended Events”) – Ich hoffe dieses Jahr wird die letzte Runde sein, um auch den Letzten von dem alten & verstaubten SQL Profiler abzuholen.

Zusätzlich dazu fühle ich mich geehrt, eine der 2 ganztägigen und kostenlosen PreCons zu geben: Zusammen mit Patrick Heyde, Microsoft (Technet-Blog), werde ich über SQL Server in Azure Umgebungen sprechen. Und zwar, wie man solche Deployments optimal konfiguriert um sowohl Performance- als auch Kosteneffizient zugleich zu sein. (Siehe auch meinen Blog-Artikel “SQL Server in Microsoft Azure: Wie man durch Flexibilität Leistung gewinnt und zugleich Kosten spart”)

Abgesehen von der kostenblosen PreCon und regulärem Vortrag, ist meine Firma Sarpedon Quality Lab wieder offizieller Sponsor dieses Events. Das ist Teil meines Engagements für die deutsche PASS Community – speziell seit meine eigene Familie mehr Zeit beansprucht, ist das ein wenig dessen, was ich tun kann, den PASS Deutschland e.V. zu unterstützen.

 

PreCon: Hybrid IT – Azure Scenarios & Dynamic Infrastructure

Tracing with Extended Events – Top Features

-           If you are still using Profiler, or just started with XEvents, be sure to come along to see some of the nifty features ;-)

 

In September I will be at the SQLSaturday #413 Denmark in Copenhagen, organized among other by Regis Baccaro. I will be giving yet another PreCon on In-Memory OLTP and Clustered ColumnStore and a regular session on SQL Server storage.

Im September werde ich auf dem SQLSaturday #413 Denmark in Kopenhagen, organisiert neben anderen von Regis Baccaro, sein. HIer gebe ich wieder eine PreCon zu In-Memory OLTP und Clustered ColumnStore und eine reguläre Session zu SQL Server Speicher.

 

PreCon: New Index technologies: Clustered ColumnStore and In-Memory OLTP: the good and the bad

A journey into SQL Server Storage - from Memory to Disk

 

I hope to see YOU :-)

 Andreas

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