Category: "SQL Server 2016"

Dieser Blog ist umgezogen // This Blog has moved: http://andreas-wolter.com/blog/

http://andreas-wolter.com/blog/

Liebe Leser
dieser Blog ist hiermit nur noch „Archiv“ und wird nicht mehr weiter gepflegt.
Seit August 2017 finden sich neue Artikel ausschließlich unter der neuen URL:
http://andreas-wolter.com/blog/

Dear Readers
this blog is now merely an „archive“ and no longer being updated.
Since August 2017 new articles are exclusively available under the new URL:
http://andreas-wolter.com/en/blog/

Die aufwändige Mehrsprachigkeit (Deutsch und Englisch professionell manuell übersetzt) wird beibehalten – aber Layout-technisch anders gelöst. Damit dürfte ich immer noch den einzigen mehrsprachigen IT-Blog weltweit betreiben.
Ich hoffe, das neue Design gefällt Ihnen.

The complex multilingualism (German and English professionally manually translated) is being continued – but solved differently in terms of layout. With that I most likely still operate the only multilingual IT-Blog worldwide.
I hope you like the new design.

 

 

Mein aktueller Artikel, der erstmalig ausschließlich auf der neuen Website zu finden ist, lautet: Optimieren von Workflows mit In-Memory und nativ kompilierten Objekten - oder wie es nicht funktioniert

My currently last article, which is exclusively available at the new website for the first time, is Optimizing workflows with In-Memory and Natively Compiled Objects - or how it does not work

 

Cu at my new Blog

Andreas

Where is that Preemptive Wait coming from? Database Ownership and Performance: a journey through SQL Server internals with XEvents, Windbg and Wireshark

 

(EN)
In this article I will bring together several techniques for troubleshooting a performance- and security-related “phenomenon” I recently noticed when doing some tests with Natively Compiled Stored Procedures.

(DE)
In diesem Artikel zeige ich einige Techniken zum Troubleshooten eines Performance- und sicherheitsrelevanten „Phänomens“, das mir kürzlich aufgefallen ist, als ich ein paar Tests mit Natively Compiled Stored Procedures durchführte.

Background:

In SQL Server 2014, Natively Compiled Stored Procedures did not support EXECUTE AS CALLER but EXECUTE AS “SpecificUser” was required. This user could be specified with “Username”, “Self” or simply “Owner” – in which case the owner of the procedure usually reverts to the schema owner, which mostly reverts to the Database Owner altogether.

Hintergrund: In SQL Server 2014 unterstützten Natively Compiled Stored Procedures EXECUTE AS CALLER nicht, sondern es war EXECUTE AS “SpecificUser” erforderlich. Dieser Nutzer konnte mit “Username”, “Self” oder einfach “Owner” spezifiziert werden – in dem Fall kehrt der Besitzer der Prozedurnormalerweise zum „schema owner“ zurück, was meistens ganz auf den Database Owner zurückkehrt. 

The phenomenon I encountered was that I noticed some pretty strange long execution times when running a workload consisting of a very basic stored procedure that does nothing more than insert one row of data into a small, unspectacular table.

The insert itself should run in less than a second for 1000 rows, but I could see up to 5 seconds.
The body of the procedure looks like this:

Das Phänomen, dem ich begegnete, war, dass ich einige ziemlich seltsam lange Ausführungszeiten bemerkte, als ich eine Workload laufen ließ, die aus einer sehr einfachen Stored Procedure bestand, die nichts weiter tat, als eine Datenreihe in eine kleine, unspektakuläre Tabelle einzufügen.
Das Insert selbst sollte weniger als eine Sekunde für 1000 Zeilen laufen, doch ich konnte bis zu 5 Sekunden sehen.
Der Inhalt der Prozedur sieht so aus:

 

SET NOCOUNT ON;
SET
XACT_ABORT ON;

INSERT UserSchema.TableName
      
(4 columns, int and char)
VALUES
      
(Parameters);

RETURN 0;

 

The analysis:

Since neither the query plan nor “Show Statistics ON” showed anything unusual, I took a step back and started a top-down-analysis with the Waits and Queues Methodology (which, if you are new in this area, has been a proven methodology for performance analysis for over a decade and is explained for SQL Server probably for the first time in detail here: SQL Server 2005 Performance Tuning using the Waits and Queues)
Using the new session-level waits-DMV in SQL Server 2016 (sys.dm_exec_session_wait_stats) I saw the following distribution of waits:

Die Analyse:

Da weder der Abfrageplan noch “Show Statistics ON“ irgendetwas Ungewöhnliches zeigten, ging ich einen Schritt zurück und begann eine Top-Down-Analyse mit der Waits and Queues Methode (die, wenn ihr in diesem Bereich neu seid, eine seit über einem Jahrzehnt bewährte Methode zur Performance-Analyse ist und für SQL Server wahrscheinlich zum ersten Mal im Detail hier erklärt wird: SQL Server 2005 Performance Tuning using the Waits and Queues)
Bei der Anwendung der neuen Session-Level Waits-DMV im SQL Server 2016 (sys.dm_exec_session_wait_stats) sah ich die folgende Waits-Verteilung: 

 

SELECT dm_exec_session_wait_stats.wait_type
       
, SUM(dm_exec_session_wait_stats.wait_time_ms) - SUM(dm_exec_session_wait_stats.signal_wait_time_ms) AS resource_wait_time_ms
       
, SUM(dm_exec_session_wait_stats.signal_wait_time_ms) AS signal_wait_time_ms
       
, SUM(dm_exec_session_wait_stats.wait_time_ms) AS wait_time_ms
       
, MAX(dm_exec_session_wait_stats.max_wait_time_ms) AS max_wait_time_ms
       
, SUM(dm_exec_session_wait_stats.waiting_tasks_count) AS waiting_tasks_count
FROM sys.dm_exec_session_wait_stats AS dm_exec_session_wait_stats
       
INNER JOIN sys.dm_exec_sessions AS dm_exec_sessions
                ON dm_exec_session_wait_stats.session_id = dm_exec_sessions.session_id
WHERE dm_exec_sessions.program_name ='Sarpedon-WorkoadSimulation-InsertProcs'
GROUP BY dm_exec_session_wait_stats.wait_type
ORDER BY max_wait_time_ms DESC;

 

From this list, Latch-contention, blocking and some waiting for IO is to be expected with a very concurrent workload (50 threads trying to insert on the last page).
The wait-types marked red are the ones that caught my attention.
As Preemptive waits are a quite different beast, and I initially thought maybe there was some authentication issue with the client, I decided to take a look at the CMEMTHREAD.

Von dieser Liste sind Latch-contention, Blocking und einiges Warten auf IO bei einer sehr simultanen Workload zu erwarten (50 Threads, die versuchen, sich auf der letzten Page einzufügen).
Die rot-markierten Wait-Typen sind diejenigen, auf die ich aufmerksam wurde.
Da Preemptive Waits ein spezielles Biest sind, und ich anfangs dachte, dass es vielleicht ein Authentifikationsproblem beim Client gab, entschied ich mich, einen Blick auf den CMEMTHREAD zu werfen.  

The CMEMTHREAD is an indicator that many threads are contending simultaneously for a thread-safe memory object.
Now since we are running a highly concurrent workload, I could just leave it at that, but remember: “Never make quick decisions by confusing symptom with cause”. Keep looking for the real cause. (A good article by Paul Randal on exactly this issue: “Avoiding Knee-Jerk Performance Troubleshooting”)

So, the question is, which memory object is so special here.

Der CMEMTHREAD ist ein Indikator dafür, dass viele Threads gleichzeitig um ein thread-safe Memory-Object konkurrieren.
Nun, da wir eine höchst konkurrierende Workload laufen haben, könnte ich es einfach darauf beruhen lassen, doch denkt dran: „Niemals schnelle Entscheidungen treffen, indem man das Symptom mit der Ursache verwechselt.“ Sucht weiter nach der wahren Ursache. (Hier ein guter Artikel von Paul Randal zu genau diesem Thema: “Avoiding Knee-Jerk Performance Troubleshooting”)

Also ist die Frage, welches Memory-Object hier so besonders ist.  

Time for Extended Events

One of the niftiest features of extended events is, that you can get a full stack dump of just a single thread, using the Action “sqlserver.create_dump_single_thread”. ”. Similarly, using “package0.callstack” you can get a callstack of the last 16 frames.
 You can analyze the outcome with Windbg. – Or, using Trace Flag 3656, you can even query the Extended Event targets directly with XQuery from within Management Studio and the callstack will be materialized, provided you have loaded the correct symbols. (Nothing you should do on a production Server!)
(An example how to do that can be found here: Resolving DTC Related Waits and Tuning Scalability of DTC)

With the dump opened in Windbg the following function calls inside the SQL Server engine can be seen:

Zeit für Extended Events

Eine der raffiniertesten Features von Extended Events ist, dass man einen vollständigen Stack Dump von einem einzelnen Pfad erhalten kann, wenn man die Action “sqlserver.create_dump_single_thread” verwendet.
Auf ähnliche Weise kann man einen Callstack der letzten 16 Frames erhalten, wenn man “package0.callstack” verwendet.
Man kann das Ergebnis mit Windbg analysieren – oder, wenn man Trace Flag 3656 verwendet, kann man sogar die Extended Event Targets direkt mit XQuery aus Management Studio heraus abfragen und der Callstack wird materialisiert. (Nichts, das man auf einem Produktions-Server machen sollte!)

(Ein Beispiel dafür, wie das gemacht werden kann, ist hier: Resolving DTC Related Waits and Tuning Scalability of DTC)

Mit dem in Windbg  geöffneten Dump können die folgenden Funktions-Calls in der SQL Server Engine gesehen werden:

 

 

The Stack dump rings a bell. Right before allocating the memory (CMemThread<CMemObj>::Alloc (red), several functions with very familiar names were called (orange):

Bei dem Stack Dump läuten doch einige Glocken. Kurz vor dem Zuteilen des Memory (CMemThread<CMemObj>::Alloc (rot) wurden mehrere Funktionen mit sehr bekannt klingenden Namen abgerufen (orange):

sqllang!CWindowsSecurityPrimaryInfo::GetNtGroupsViaAuthZ
sqllang!CreateLoginToken
sqllang!CreateLoginTokenForImpersonation

And those in turn must have been caused by the sqllang!CAutoExecuteAsContext::Set, sqllang!CMsqlExecContext::FExecute and so on (green).
At this point I pretty much knew where to look. But just to show you an example of how this can be done without even touching Windbg, here is an (accumulated and commented) result from the Extended Event callstack-Action:

Und diese wiederum müssen von sqllang!CAutoExecuteAsContext::Set, sqllang!CMsqlExecContext::FExecute verursacht worden sein, und so weiter (grün).
In diesem Moment wusste ich im Prinzip schon, wo ich suchen musste. Aber um euch ein Beispiel zu zeigen, wie man das auch machen kann, ganz ohne Windbg anzurühren, ist hier ein (gesammeltes und kommentiertes) Ergebnis der Extended Event Callstack-Action:  

 

  

This callstack results from the PREEMPTIVE_OS_LOOKUPACCOUNTSID Wait-Type, which I noticed would happen exactly once per procedure call. When analyzing different callstacks together I could draw the conclusion that the following function calls lead to the respective Wait-Types:

Dieser Callstack resultiert vom PREEMPTIVE_OS_LOOKUPACCOUNTSID Wait-Type, was, wie ich bemerkte, genau einmal pro Procedure Call geschehen würde. Beim Analysieren verschiedener Callstacks zusammen konnte ich die Schlussfolgerung ziehen, dass die folgenden Funktions-Calls zu den jeweiligen Wait-Types führen:

sqllang.dll!LookupAccountNameInternal -> PREEMPTIVE_OS_LOOKUPACCOUNTSID
sqllang.dll!CWindowsSecurityPrimaryInfo::GetNtGroupsViaAuthZ -> PREEMPTIVE_OS_AUTHZINITIALIZERESOURCEMANAGER
sqllang.dll!CWindowsSecurityPrimaryInfo::Init -> PREEMPTIVE_OS_AUTHORIZATIONOPS

The Preemptive Waits read from the Extended Events file-target can be ordered in their occurrence (from top to bottom), leading to the following picture:

Die Preemptive Waits, aus dem Extended Events Datei-Ziel gelesen, können nach ihrem Auftreten sortiert werden (von oben nach unten), was folgendes Bild ergibt:

 

 

The here unreadable callstack for the call to AUTHORIZATIONOPS (undocumented) on the bottom.

Der hier unlesbare Callstack für den Call AUTHORIZATIONOPS (undokumentiert) unten. 

 

First outcome:

From those calls one can deduct that not the client is authenticating at SQL Server, but actually the procedure call itself causes Windows API calls (LOOKUPACCOUNTSID, AUTHZINITIALIZERESOURCEMANAGER) for authentication purposes.
Now at the very latest it’s time to check the ownership-chain for the stored procedure.
So I checked the header of the proc, and indeed, just as I expected it contained a “WITH EXECUTE AS OWNER”.
That alone can’t be it, so the question is, who is the owner. I already suspected it and there it was: The database was owned by a LOCAL Windows account.
Now, that is rare, but it all makes sense now: For every execution of the procedure, the ownership had to be verified – and since it is a Windows-Account, it had to go out of SQL Server OS, using a Preemptive call to Windows, wait, and continue only after Windows returns control of this thread back to SQL Server.

Erstes Ergebnis:

Aus diesen Call lässt sich ableiten, dass es nicht der Client ist, der sich bei SQL Server authentifiziert, sondern dass es tatsächlich der Procedure Call selbst ist, der Windows API Calls (LOOKUPACCOUNTSID, AUTHZINITIALIZERESOURCEMANAGER) für Authentifizierungszwecke verursacht.
Spätestens jetzt ist es an der Zeit, die Besitzerkette für die Stored Procedure zu überprüfen. Also habe ich den Kopf der Prozedur geprüft, und tatsächlich, genau wie ich erwartet hatte, enthielt es ein “WITH EXECUTE AS OWNER”.
Das allein kann es nicht sein, also stellt sich die Frage, wer der Owner ist. Ich vermutete es bereits, und da war es: Die Datenbank gehörte zu einem LOKALEN Windows-Konto.
Nun, das ist eher selten, doch jetzt ergibt alles Sinn: Für jede Ausführung der Prozedur musste der Besitz verifiziert werden – und da es ein Windows-Konto ist, musste es raus aus SQL Server OS gehen, indem ein Preemptive Call an Windows verwendet wird, dann warten, und erst dann weitermachen, nachdem Windows die Kontrolle dieses Pfades an SQL Server zurückgegeben hatte.

 

Testing the improvement options:

Now, obviously, I did not stop here but rather made some more tests. Based on the fact that there are 3 possible types of owner for objects within SQL Server, I compared the execution times (and waits) between those 3 possible
Database owner-types:

1.       SQL Account

2.       Windows DomainAccount

3.       Local Windows Account

- I did also test with Admin vs. non-Admin and NTLM vs. Kerberos but saw no difference.

Testen der Verbesserungsmöglichkeiten:

Natürlich hörte ich nicht hier auf, sondern machte noch weitere Tests. Ausgehend von der Tatsache, dass es mögliche Typen von Owner für Objekte innerhalb SQL Server gibt, verglich ich die Ausführungszeiten (und Wartezeiten) zwischen diesen 3 möglichen Datenbank-Owner-Types:

  1. SQL Account
  2. Windows DomainAccount
  3. Local Windows Account

- Ich testete auch mit Admin vs. non-Admin und NTLM vs. Kerberos, stellte jedoch keinen Unterschied fest. 

So here is the result of the Performance-Comparison with different Database-/Procedure-Owners:

Hier ist also das Ergebnis des Performance-Vergleichs mit unterschiedlichen Datenbank-/Procedure-Owners:

 

 

 

Interpretation:

1)      The obvious: Using a local Windows Account results in a huge performance-penalty for such a simple INSERT-statement

2)      Using a Domain Account I could notice that every 10 minutes the next execution would be a bit slower.

Interpretation:

  1. Das Offensichtliche: Die Verwendung eines lokalen Windows-Kontos führt zu eine riesigen Performance-Impakt für ein solch simples INSERT-Statement

  2. Beim Verwenden eines Domain-Kontos konnte ich sehen, dass die nächste Ausführung alle 10 Minuten etwas langsamer war. 

Further analysis

When checking the Wait-stats again, I could see that usually the Windows-Domain-Account had the following simple waits:

Weitere Analyse

Beim erneuten Überprüfen der Warte-Statistiken konnte ich feststellen, dass der Windows-Domain-Account meistens die folgenden einfachen Waits hatte: 

 

 

 

Pretty ok and nothing to worry about. But the first call after 10 minutes would always result in the same wait-types as I observed for the local Windows Account, except that the wait-times are much much lower. (You can compare the below picture with the one from the beginning of the article.)

Ziemlich in Ordnung und nichts, worüber man sich Sorgen machen müsste. Doch der erste Call nach 10 Minuten ergab immer dieselben Wait-Typen, die ich für das lokale Windows-Konto beobachtete, außer dass die Wartezeiten um Vieles geringer sind. (Ihr könnt das Bild unten mit dem am Anfang des Artikels vergleichen.)

 

 

 

Behind the Scenes: Network Tracing

To explain the difference, I ran a Network Trace using Wireshark
And exactly every 10 minutes I saw some additional KERBEROS-traffic going to the Domain Controller. Below you see the opened data frame with the Request for a Ticket Granting Service Ticket (TGS-REQ).

Hinter den Kulissen: Network Tracing

Um den Unterschied zu erklären, führte ich ein Network Trace mit Wireshark aus.
Und genau alle 10 Minuten sah ich einigen zusätzlichen KERBEROS-Traffic zum Domain Controller fließen. Unten seht ihr den geöffneten Datenrahmen mit dem Request for a Ticket Granting Service Ticket (TGS-REQ).

 

 

 

Background infos:

As the User, on whose behalf the service requests the service ticket, is identified using the user name and user realm/domain (SQL\SQLDBOwner in my case), the padata type PA-FOR-USER is used, as you can see in the screenshot. You can even see the Checksum added for protection.

The S4U2self extension of the PA-DATA structure allows a service to obtain a service ticket to itself on behalf of a user. The user is identified to the KDC using the user's name and realm.

Hintergrundinformationen:

Wenn der Nutzer, für den der Service das Service-Ticket anfordert, unter Verwendung des Nutzernamens und user realm/Domäne (SQL\SQLDBOwner in meinem Fall) identifiziert wird, wird der Padata Type PA-FOR-USER verwendet, wie im Screenshot zu sehen. Man kann sogar die Checksum sehen, die zum Schutz hinzugefügt wurde.

Die S4U2self Extension der PA-DATA-Struktur erlaubt einem Service, ein Service-Ticket im Auftrag eines Nutzers für sich selbst zu beziehen. Der Nutzer wird gegenüber dem KCD identifiziert, unter Verwendung des Nutzernamen und Realm. 

Luckily the DC answers right away with a TGS-REP, containing the PA-DATA-structure with ticket for the service that was named in the TGS-REQ from above:

Zum Glück antwortet der DC sofort mit einem TGS-REP, der die PA-DATA-Struktur mit einem Ticket für den oben im TGS-REQ genannten Service enthält:

 

 

 

There is plenty literature available if you want to learn more about the Kerberos Protocol-Elements. Here is a documentation from Microsoft:

Es gibt eine Menge an Literatur, falls ihr mehr über die Kerberos-Protokollelemente erfahren wollt. Hier ist eine Dokumentation von Microsoft:

The 10-minutes puzzle:

1) After doing some extensive testing, I conclude that the 10-minute interval after which a new Ticket Granting Service Ticket-Request is initiated must be a SQL Server specific enhancement for Domain Accounts to avoid making this round trip for every SID-validation. The 10 minutes are consistent and independent from other workload influencers. The cause behind is not documented.

2) The much faster validation for the AD-Account is very fast thanks to some caching in Windows. (Thank you, Jack Richins from formerly SQL Security Team, for reminding me of this)

3) For the local Windows Account, there is no such performance improvement via caching which results in much slower response times.

Das 10-Minuten-Puzzle:

  1. Nach ausgiebigem Testen schließe ich, dass das 10-Minuten-Intervall, nach dem ein neuer Ticket Granting Service Ticket Request eingeleitet wird, eine SQL-Server-spezifische Erweiterung für Domain-Accounts sein muss, um diese Rundreise nicht für jede SID-Validierung machen zu müssen. Die 10 Minuten sind konsistent und unabhängig von anderen Workload-Beeinflussern. Die Ursache dahinter ist nicht dokumentiert.

  2. Die viel schnellere Validierung für das AD-Konto ist so schnell dank einigem Caching in Windows. (Danke, Jack Richins vom ehemaligen SQL Security Team, der mich daran erinnert hat)

  3. Für das lokale Windows-Konto gibt es keine solche Performance-Verbesserung via Caching, was zu viel längeren Antwortzeiten führt.

 

Final results and learnings:

1)      Using local users for SQL Server can create noticeable impact for short transactions. Yet another reason to stay away from local accounts.

2)      For the SQL Account, naturally no Windows calls are necessary at all, but the performance-advantage compared to the AD-Account is marginal, even for high-speed scenarios.

3)      Network latency matters even between SQL Server and DC. Not just for the initial Login-phase but even for ongoing validations from within SQL-statements.

4)      I can stick to my recommendation for Database Ownerships (SQL Server Database Ownership: survey results & recommendations) :-)

Endergebnis und Gelerntes:

  1. Die Verwendung von lokalen Nutzern für SQL Server kann deutliche Auswirkungen für kurze Transaktionen verursachen. Noch ein weiterer Grund dafür, sich von lokalen Konten fernzuhalten.

  2. Für den SQL Account sind natürlich keine Windows-Calls notwendig, doch der Performance-Vorteil gegenüber dem AD-Account ist geringfügig, selbst für High-Speed-Scenarien.

  3. Netzwerk-Latenz spielt selbst zwischen SQL Server und DC eine Rolle. Nicht nur für die anfängliche Login-Phase, sondern sogar für fortlaufende Validierungen innerhalb von SQL-Statements.

  4. Ich kann meine Empfehlung für Datenbank-Ownerships aufrechterhalten (SQL Server Database Ownership: survey results & recommendations) :-)

I hope you found this an interesting read.

Ich hoffe, dies war eine interessante Lektüre für euch. 

 

Andreas

 

PS: for the geeks among you:

The Stack Dump in Text format together with some comments:

P.S. Für die Geeks unter euch:

Der Stack Dump im Textformat mitsamt einigen Kommentaren:

 

Child-SP          RetAddr           Call Site

000000e9`2f0b79d8 00007ffe`f9e51118 ntdll!NtWaitForSingleObject+0xa

-> this function is actually deprecated (Hello, Microsoft?)

000000e9`2f0b79e0 00007ff7`04999fef KERNELBASE!WaitForSingleObjectEx+0x94
000000e9`2f0b7a80 00007ff7`04999d7d sqlservr!CDmpDump::DumpInternal+0x22f
000000e9`2f0b7b20 00007ff7`049a15b8 sqlservr!CDmpDump::DumpFilter+0xcd
000000e9`2f0b7c10 00007ffe`ef0ef2cb sqlservr!memset+0x1819
000000e9`2f0b7c40 00007ffe`fc98398d msvcr120!__C_specific_handler+0x93 [f:\dd\vctools\crt\crtw32\misc\amd64\chandler.c @ 162]
000000e9`2f0b7cb0 00007ffe`fc9493a7 ntdll!RtlpExecuteHandlerForException+0xd
000000e9`2f0b7ce0 00007ffe`fc948fbd ntdll!RtlDispatchException+0x197
000000e9`2f0b83b0 00007ffe`f9e58b9c ntdll!RtlRaiseException+0x18d
000000e9`2f0b8b70 00007ff7`04999c9c KERNELBASE!RaiseException+0x68
000000e9`2f0b8c50 00007ffe`f05602c6 sqlservr!CDmpDump::Dump+0x4c
000000e9`2f0b8c90 00007ffe`f105c411 sqllang!SQLDumperLibraryInvoke+0x1f6
000000e9`2f0b8cd0 00007ffe`f105ce94 sqllang!SQLLangDumperLibraryInvoke+0x161
000000e9`2f0b8d80 00007ffe`f102cd0b sqllang!CImageHelper::DoMiniDump+0x475
000000e9`2f0b8f90 00007ffe`f072e9c4 sqllang!stackTrace+0x9db
000000e9`2f0ba9b0 00007ffe`f072f6ae sqllang!XeSqlPkg::IsSystem+0x174
000000e9`2f0bab90 00007ffe`ef2e779a sqllang!XeSqlPkg::CreateDumpSingleThreadActionInvoke+0x1e

-> you can clearly see how this stack dump was created: XEvents

000000e9`2f0babc0 00007ffe`ef2a1b8e sqldk!XeSosPkg::wait_info_external::Publish+0x1a9

-> Now we will see our wait-type when we ask the DMVs or XEvents

000000e9`2f0bac20 00007ffe`ef2a63a4 sqldk!SOS_Scheduler::UpdateWaitTimeStats+0x596
000000e9`2f0bb530 00007ffe`f0d94fac sqldk!SOS_Task::PopWait+0xba
000000e9`2f0bb590 00007ffe`f0d9481e sqllang!CWindowsSecurityPrimaryInfo::GetNtGroupsViaAuthZ+0x75c

-> this will lead to the Preemptive Wait: PREEMPTIVE_OS_AUTHZINITIALIZERESOURCEMANAGER

000000e9`2f0bb890 00007ffe`f0d956ed sqllang!CWindowsSecurityPrimaryInfo::Init+0x2ce
000000e9`2f0bba00 00007ffe`f041a88b sqllang!GetWindowsSecurityPrimaryInfo+0xbe
000000e9`2f0bba60 00007ffe`f041cadb sqllang!CreateLoginToken+0x2d7
000000e9`2f0bbc50 00007ffe`f041dca8 sqllang!CreateLoginTokenForImpersonation+0xcb2

-> an Impersonation is happening (EXECUTE AS)

000000e9`2f0bc400 00007ffe`f0358342 sqllang!CAutoExecuteAsContext::Set+0xe2d
000000e9`2f0bc680 00007ffe`f001245e sqllang!CSECExecAsRuntimeServices::_Set+0x424
000000e9`2f0bc900 00007ffe`f0012041 sqllang!CMsqlExecContext::FExecute+0x336
000000e9`2f0bcc30 00007ffe`f0d1d83d sqllang!CSQLSource::Execute+0x983
000000e9`2f0bcdd0 00007ffe`f0d1d241 sqllang!CStmtExecProc::XretLocalExec+0x26e
000000e9`2f0bce50 00007ffe`f0d19f98 sqllang!CStmtExecProc::XretExecExecute+0x481
000000e9`2f0bd600 00007ffe`f00119ca sqllang!CXStmtExecProc::XretExecute+0x38
000000e9`2f0bd640 00007ffe`f0012933 sqllang!CMsqlExecContext::ExecuteStmts<1,1>+0x40d
000000e9`2f0bd780 00007ffe`f0012041 sqllang!CMsqlExecContext::FExecute+0xa9e
000000e9`2f0bdab0 00007ffe`f0cd3f6d sqllang!CSQLSource::Execute+0x983
000000e9`2f0bdc50 00007ffe`f0ce0e6c sqllang!ExecuteSql+0x93d
000000e9`2f0be7d0 00007ffe`f0ce1549 sqllang!CSpecProc::ExecuteSpecial+0x15c
000000e9`2f0be8d0 00007ffe`f001a82b sqllang!CSpecProc::Execute+0x299
000000e9`2f0bea00 00007ffe`f0021542 sqllang!process_request+0xe61
000000e9`2f0befd0 00007ffe`f00210a3 sqllang!process_commands_internal+0x2df
000000e9`2f0bf050 00007ffe`ef2a5bfd sqllang!process_messages+0x253
000000e9`2f0bf260 00007ffe`ef2a58f5 sqldk!SOS_Task::Param::Execute+0x231
000000e9`2f0bf860 00007ffe`ef2a554d sqldk!SOS_Scheduler::RunTask+0xaa
000000e9`2f0bf8d0 00007ffe`ef2cd7c8 sqldk!SOS_Scheduler::ProcessTasks+0x3cd
000000e9`2f0bf9b0 00007ffe`ef2cdb10 sqldk!SchedulerManager::WorkerEntryPoint+0x2a1
000000e9`2f0bfa80 00007ffe`ef2cdcd7 sqldk!SystemThread::RunWorker+0x8f
000000e9`2f0bfab0 00007ffe`ef2cd9f8 sqldk!SystemThreadDispatcher::ProcessWorker+0x2de
000000e9`2f0bfb60 00007ffe`fc6e13d2 sqldk!SchedulerManager::ThreadEntryPoint+0x1d8
000000e9`2f0bfc10 00007ffe`fc9603c4 kernel32!BaseThreadInitThunk+0x22
000000e9`2f0bfc40 00000000`00000000 ntdll!RtlUserThreadStart+0x34

EOF

SQL Server 2016 SP1 removes the editions’ programming feature limits! Everything from Always Encrypted to XTP/In-memory for everybody!

SQL Server 2016 SP1 entfernt Programmier-Feature-Limits der Editionen! Alles von Always Encrypted bis XTP: In-Memory für Alle!

Unglaubliche Neuigkeiten, anders kann man es gar nicht sagen:

Mit dem heute erschienenen Service Pack 1 für SQL Server 2016, macht Microsoft den programmiertechnischen Unterschieden zwischen den Editionen der relationalen Datenbankengine (LocalDB, Express, Standard, Web, Enterprise) ein Ende!

Ab heute kann JEDER, der bereits auf SQL Server 2016 arbeitet, In-Memory optimierte Tabellen erstellen, Tabellen partitionieren, Columnstore Indexe verwenden, etc. etc.

Und wenn das nicht reichen würde, bringt das SP1 auch für sich genommen viele weitere wertvolle neue Features wie „Database Clones“, „CREATE OR ALTER“-Syntax, viele neue Diagnose-Verbesserungen (Showplan, XEvents, DMVs, Errorlog), NUMA-Awareness für Analysis Services und weiteres (mehr Infos im offiziellen Announcement von Microsoft hier).

Wow, einfach wow… die Möglichkeiten, die dies für Kunden eröffnet sind unglaublich. Vorbei die Zeiten zwingend darauf aufzupassen, keine Enterprise Features wie Compression oder Datenbank-Level-Auditing auf Standard-Editionen zu deployen.
Endlich die Möglichkeit, auch kleine Systeme, die dennoch enorm Latenz-kritisch sind, mit In-Memory Objekten zu beschleunigen (Assembly-Line/Fließbandfertigungs-Szenarien).

Die In-Memory Revolution ist nun für alle da!

Bevor ihr fragt, was dann überhaupt noch die Editionsunterschiede sind:

Was bleibt und bleiben wird, ist, dass die größeren Editionen einfach mehr Hardware unterstützen. Die Limits von 16 Cores und 128 GB RAM für die Standard Edition und noch weniger für Express bleiben bestehen. Das ist und bleibt entscheidend.

Aber definitiv muss ich mir einen neuen Spruch ausdenken: Früher pflegte ich immer zu sagen: „Um zu entscheiden, ob ein Feature Enterprise ist, reicht, zu wissen, ob es ‘cool‘ (z.B. Database Snapshot) oder ‘schnell‘ (z.B. Columnstore) ist.“

These are just sublime news, there is no other way to put it:

With the Service Pack 1 for SQL Server 2016 released today, Microsoft puts an end to the differences in the relational database Engine in terms of programmability features between the editions (LocalDB, Express, Standard, Web, Enterprise)!

As of today, EVERYONE who is already working on SQL Server 2016 can create In-Memory optimized tables, partitioned tables, use Columnstore Indexes and so forth.

And as if that weren’t enough, the SP1 itself also includes many more valuable features such as “Database Clones,” “CREATE OR ALTER”-syntax, many new diagnosis improvements (Showplan, XEvents, DMVs, Errorlog), NUMA-Awareness for Analysis Services and more (read more in the official announcement of Microsoft here).

Wow, just wow… the possibilities thus offered to customers are unbelievable. Gone are the days when you had to make sure not to deploy any Enterprise features such as Compression or Database level auditing on Standard Editions.
Finally, there is the option to accelerate even small systems, which are still extremely latency-critical, with memory objects (assembly-line scenarios). 

The In-Memory Revolution is now available to everybody!

Before you ask how, then, the editions will differ:

What will remain in place is that the larger editions simply support more hardware. The limits of 16 Cores and 128 GB RAM for the standard edition and even less for Express will remain the same. That is and remains key.

But I definitely need to think of a new line: In the past, I always used to say: “In order to decide whether a feature is Enterprise it suffices to know if it’s ‘cool’ (e.g. Database Snapshot) or ‘quick’ (e.g. Columnstore).” 

 

Ab heute gilt: die Größe macht den Unterschied.

Bevor mich jemand falsch zitiert: es gibt durchaus noch Features, die der Enterprise-Edition vorbehalten bleiben. Die Betonung bei der Gleichziehung liegt auf Programmier-Features. Reine HA-Features wie z.B. multiple Secondaries, bestimmte Sicherheitsfeatures wie TDE oder Engine-interne Performance Optimierungen wie „Advanced Read-Ahead“, Star Join-Optimierung usw. bleiben der Enterprise Edition vorbehalten. Manches bleibt auch technisch beschränkt. So hat man für Change Data Capture unter der Express-Edition ja keinen SQL Agent.
Und natürlich macht ein Columnstore Index eh kaum Sinn, wenn eine Tabelle nicht mal 1 Million Zeilen enthält…

Aber das sollte die Freue nicht schmälern.

Und hier geht es zum Download des Microsoft® SQL Server® 2016 Service Pack 1 (SP1):

As of today, the following applies: it is the size that makes the difference.

But before someone gets me wrong: There are definitely still features that remain exclusive to the Enterprise edition. The emphasis on the getting equal is on the programming features. Mere HA features such as multiple secondaries, and certain security features such as TDE or engine-internal performance optimizations like “Advanced Read-Ahead”, Star Join-Optimization etc. remain exclusive to the Enterprise edition. Some of it also remains limited in technical terms. For Change Data Capture, for example, you do not have an SQL Agent in the Express edition.
And of course, a Columnstore Index hardly makes sense if a table does not even contain 1 million rows.

But that shouldn’t cut down on the joy.

Here, you can download the Microsoft® SQL Server® 2016 Service Pack 1 (SP1):

 www.microsoft.com/en-us/download/details.aspx?id=54276

Und hier noch ein paar weitere Links zu den Neuerungen mit dem Service Pack 1:

And here some more links about the changes with Service Pack 1:

 What’s new in SQL Server 2016 SP1 for Reporting Services

SQL Server 2016 SP1 editions

Microsoft Changes Feature Support Between Editions in SQL Server 2016 Service Pack 1 (Article by my good fellow Victor Isakov, MCM and MCA from Australia with nice background details)

Viel Spaß mit dem SP1 und beim Ausprobieren der neu verfügbaren Features.

Und wenn Sie jetzt meinen, dass Sie nicht nur lesen wollen, sondern sich tiefergehendes Wissen aneignen oder endlich auf die aktuelle SQL Server Version upgraden wollen, helfen wir von Sarpedon Quality Lab® Ihnen gerne.

Have fun with the SP1 and trying the newly available features.

And if you now feel like reading isn’t enough but you would like to acquire in-depth knowledge or finally upgrade to the current SQL Server Version, you are in good hands with Sarpedon Quality Lab®.

Andreas Wolter

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

1 3