SQL Server 2016 Cardinality Estimator

[Michael R. (Firma: Brainit GmbH) (fisa)]

Hallo Leute,

vielleicht findet sich hier jemand, der mir folgendes sehr eigenartige 
Verhalten des SQL Servers 2016 erklären kann...

ich habe eine Tabelle "Documents" mit ca. 4 Mio Records. Parallel dazu 
eine Tabelle "Locks" mit ebensovielen (bzw. etwas mehr Sätzen)

Verknüpft sind diese Tabellen über eine Objekt-Id (= DocId aus der 
Documents-Tabelle), einen Objekttyp (integer, 3 für Documents) und eine 
"Sperren-Art" (integer, 10 in meinem Fall). Die Locks-Tabelle liefert 
eine "Wert" der Sperre (dämlich, aber ist so)

Nun habe ich folgende (vereinfachte) Abfrage:

Select D.DocId, L.LckValue from Documents D
join Locks L on L.ObjId = D.DocId and L.ObjType=3 and L.LckType=10
where D.Description like '%HalloMichi%'

auf D.Description liegt ein Index. Der kann zwar nicht direkt zur Suche 
verwendet werden (wegen dem führenden '%' in der Where-Clause), aber 
zumindest für einen IndexScan ist er gut (die Tabelle hat daneben noch 
~100 Spalten)

Wichtig ist noch: Documents enthält zwar ~4 Mio Sätze, Locks ebenso (zu 
jedem Doc gibts einen passenden Lock-Eintrag), aber die Abfrage "like 
'%HalloMichi%'" liefert etwa 10 Sätze.

Manachmal machts der SQL-Server richtig: Er macht einen Index-Scan auf 
Description, kriegt (nach etwa 2 Sekunden) die 10 Sätze, sucht diese 10 
Sätze in der Locks-Tabelle, und ist fertig.

Manchmal kommt er aber nun auf die abstruse Idee, lieber zuerst alle 4 
Millionen Lock-Sätze zu lesen, mit der Documents-Tabelle zu joinen, und 
erst dieses Zwischenergebnis auf '%HalloMichi%' einzuschränken. Das 
Ergebnis ist zwar das gleiche, aber es dauert dramatisch viel länger 
(1-2 Minuten) Dazu kommen natürlich noch die SQL-Sperren, die andere 
parallele Abfragen blockieren, und schon hat man eine schöne Lock 
Escalation bzw. Contention.

ich versuche nun verzweifelt, ihm auf die Sprünge zu helfen, indem ich 
diverse zusätzliche Indexe und/oder Statistiken anlege, damit er 
mitkriegt, dass zuerst die Locks-Tabelle zu lesen keine so gute idee 
ist... allein, er kapierts nicht :-( Im Gegenteil, das machts tlw. nur 
noch schlimmer...

Leider habe ich auf die eigentliche Abfrage keinen Einfluss, die macht 
eine (fremde) Applikation, ich kann also zB auch keine Hints mitgeben.

"LEGACY_CARDINALITY_ESTIMATION" ist übrigens aktiv, damit der alte 
(pre-2014er) Cardinality Estimator verwendet wird, mit dem neuen ists 
noch viel viel schlimmer...

Irgendwas ist da ganz massiv faul im SQL Server...

[Dieter (Gast)]

Michael R. schrieb:
> Irgendwas ist da ganz massiv faul im SQL Server...

Das ist so üblich für dem MS SQL Server. Die einzig sichere Lösung wäre 
ein Wechsel.

[Michael R. (Firma: Brainit GmbH) (fisa)]

Dieter schrieb:
> Michael R. schrieb:
>> Irgendwas ist da ganz massiv faul im SQL Server...
>
> Das ist so üblich für dem MS SQL Server. Die einzig sichere Lösung wäre
> ein Wechsel.

Hätt ich schon lange getan (auch wenn ich nicht wüsste wohin) geht aber 
nicht, die Applikation unterstützt nur MS-SQL Server :-(

[Arc N. (arc)]

Michael R. schrieb:
> Dieter schrieb:
>> Michael R. schrieb:
>>> Irgendwas ist da ganz massiv faul im SQL Server...
>>
>> Das ist so üblich für dem MS SQL Server. Die einzig sichere Lösung wäre
>> ein Wechsel.
>
> Hätt ich schon lange getan (auch wenn ich nicht wüsste wohin) geht aber
> nicht, die Applikation unterstützt nur MS-SQL Server :-(

Mal angesehen was sich der SQLServer zusammenbaut?
https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan

[Michael R. (Firma: Brainit GmbH) (fisa)]

Arc N. schrieb:
> Mal angesehen was sich der SQLServer zusammenbaut?
> 
https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan

Ja, natürlich. Der Execution Plan ist Sch**sse, das weiss ich.

Die Frage ist, warum der Query Optimizer (trotz aller Hints die er hat) 
solche Sch**sse baut.

[stnv (Gast)]

Versuche mal einen Index auf die Felder ObjType und LckType der Tabelle 
Locks. Zusätzlich werden dem Index die Felder ObjId und LckValue 
hinzugefügt als zusätzliche Datenfelder (welche aber nicht indiziert 
werden).

Bei der Menge sollte dann eine Indexsuche erfolgen. Da die gesuchten 
Felder bereits im Index als Daten vorliegen, sollte kein weiterer 
Zugriff mehr auf die eigentliche Tabelle mehr notwendig sein.

SELECT D.DocId, 
       L.LckValue
  FROM Documents D
  JOIN (
       SELECT L.LckValue,
              L.ObjId
         FROM Locks
        WHERE L.ObjType=3 
          AND L.LckType=10
       ) L
    ON L.ObjId = D.DocId

Zusätzlich wird eventuell die Subquery auch noch eine Hilfestellung 
sein.

[Michael R. (Firma: Brainit GmbH) (fisa)]

Zusätzliche Indexe habe ich schon versucht, in allen möglichen 
Kombinationen. Es hilft manchmal, manchmal nicht, aber oft werden dann 
andere Abfragen "in die Irre geleitet"

An der Query selbst kann ich nichts ändern, die wird von einer 
Applikation ausgeführt.

Problematisch scheint auch zu sein, dass der SQL-Server zusätzliche 
Informationen (Statistiken) dazu nutzt, einen Index eher zu nutzen, aber 
der umgekehrte Fall (aus der Statistik geht hervor dass der Index eben 
nicht genutzt werden soll) scheint nicht möglich zu sein.

Obwohl zB der Density Vektor auf der Locks-Tabelle korrekt ist, und 
daraus hervorgeht dass er einen Scan über 4 Mio Sätze machen wird, 
beschließt er so zu arbeiten (was dann auch tatsächlich kontraproduktiv 
ist).

Die haben einfach den Cardinality Estimator nicht wirklich im Griff...

[Jan H. (j_hansen)]

Michael R. schrieb:
> Nun habe ich folgende (vereinfachte) Abfrage:

Und das Problem tritt auch bei dieser vereinfachten Abfrage auf, oder 
nur bei der Originalen?

Michael R. schrieb:
> Leider habe ich auf die eigentliche Abfrage keinen Einfluss, die macht
> eine (fremde) Applikation, ich kann also zB auch keine Hints mitgeben.

Sollte sich da nicht der Entwickler/Maintainer dieser fremden 
Applikation darum kümmern?

Michael R. schrieb:
> Manachmal machts der SQL-Server richtig

Du kannst mal versuchen, den schnellen Plan zu erzwingen. Der Artikel 
ist zwar für den 2017er, aber der 2016 ist darin auch erwähnt. 
Vielleicht findest du so ja auch heraus, warum der es unbedingt anders 
machen will.

https://www.mssqltips.com/sqlservertip/5141/sql-server-2017-automatic-query-tuning/

[Michael R. (Firma: Brainit GmbH) (fisa)]

Jan H. schrieb:
> Und das Problem tritt auch bei dieser vereinfachten Abfrage auf, oder
> nur bei der Originalen?

Wenn das so einfach wäre ;-)

Die Applikation lief über Monate sauber, bis auf eine spezielle Abfrage 
(ähnlich der oben beschriebenen) welche eben einen falschen Plan 
verwendete.

ich habe dann versucht, mit zusätzlichen Statistiken den Plan in die 
richtige Richtung zu zwingen, was auch gut funktioniert hat. Alles lief 
weiter fein, ca. eine Woche, dann kam eines Morgens der Anruf vom 
Kunden: Applikation praktisch unbedienbar, alles dauert ewig. Die 
Analyse zeigte, dass nun plötzlich alle Abfragen wie oben (das ist 
eine Standard-Abfrage beim Suchen von Dokumenten) plötzlich den 
schlechten Plan verwendeten. ich war ratlos, in meiner Verzweiflung habe 
ich die eine Woche vorher erstellten Indexe wieder entfernt - und siehe 
da, plötzlich lief alles wieder gewohnt schnell. In der Nacht vorher 
wurde aber nichts im Umfeld geändert, auch keine WinUpdates oder so 
eingespielt.

Jan H. schrieb:
> Sollte sich da nicht der Entwickler/Maintainer dieser fremden
> Applikation darum kümmern?

Leider liegt das auf meinem Tisch ;-)

Was nämlich noch dazukommt: Die Abfrage sieht im Detail immer etwas 
anders aus, je nachdem wie der Benutzer recherchiert. Die "Documents" 
Tabelle hat viele Spalten, nach denen gesucht werden kann.

Jan H. schrieb:
> 
https://www.mssqltips.com/sqlservertip/5141/sql-server-2017-automatic-query-tuning/

Das klingt sehr interessant, danke!

Ich habe nämlich auch schon den Verdacht, dass mir (wie bei Microsoft 
üblich) der SQL-Server "nur helfen will". Ich will diese Hilfe nicht, 
und wenn ich sie deaktivieren kann, umso besser...

[Jens G. (jensig)]

>Manchmal kommt er aber nun auf die abstruse Idee, lieber zuerst alle 4
>Millionen Lock-Sätze zu lesen, mit der Documents-Tabelle zu joinen, und
>erst dieses Zwischenergebnis auf '%HalloMichi%' einzuschränken. Das

Tja, offensichtlich meint Dein SQLServer, daß L.ObjType=3 and 
L.LckType=10 recht selektiv ist, bzw. selektiver als "D.Description like 
'%HalloMichi%'", liegt damit aber offensichtlich in der Realität falsch.
Wenn alle 4Mio. Rows gelesen werden, dann haben offensichtlich (fast) 
alle Lockeinträge die Werte L.ObjType=3 and L.LckType=10, was natürlich 
dann gar nicht selektiv wäre. Wenn die Query dagegen nach anderen 
L.ObjType and L.LckType suchen würde, die es gar nicht gibt, dann wäre 
die Query rasendschnell (sofern Indexes auf L.ObjType and L.LckType 
liegen würden). Vermutlich geht der Optimizer von diesem Fall aus.
Die Statistiken müssten also Angaben darüber enthalten, wie hoch die 
unique Kardinality in diesen Spalten ist (bzw. in deren Kombination), 
und idealerweise noch Infos zur Distribution der Spaltenwerte enthalten, 
wie es z.B. bei DB2 machbar wäre (Distribution statistics). Ist/Geht das 
auch beim SQL Server?

[Michael R. (Firma: Brainit GmbH) (fisa)]

Jens G. schrieb:
> Tja, offensichtlich meint Dein SQLServer, daß L.ObjType=3 and
> L.LckType=10 recht selektiv ist, bzw. selektiver als "D.Description like
> '%HalloMichi%'", liegt damit aber offensichtlich in der Realität falsch.
Richtig.

> Wenn alle 4Mio. Rows gelesen werden, dann haben offensichtlich (fast)
> alle Lockeinträge die Werte L.ObjType=3 and L.LckType=10, was natürlich
> dann gar nicht selektiv wäre.
Korrekt.

> Wenn die Query dagegen nach anderen
> L.ObjType and L.LckType suchen würde, die es gar nicht gibt, dann wäre
> die Query rasendschnell (sofern Indexes auf L.ObjType and L.LckType
> liegen würden). Vermutlich geht der Optimizer von diesem Fall aus.
Ja, vermutlich.

> Die Statistiken müssten also Angaben darüber enthalten, wie hoch die
> unique Kardinality in diesen Spalten ist (bzw. in deren Kombination),
> und idealerweise noch Infos zur Distribution der Spaltenwerte enthalten,
> wie es z.B. bei DB2 machbar wäre (Distribution statistics). Ist/Geht das
> auch beim SQL Server?

Ja, das geht (wenn auch mit Einschränkungen). Das ist ja der Witz: Der 
Query Optimizer hat alle Informationen, um zu einem optimalen Plan zu 
kommen, aber er nutzt sie (manchmal) nicht bzw. grundlegend falsch.

Ich hatte sogar einen ähnlichen Fall, wo neben "D.Description like
'%HalloMichi%'" (was zugegebenerweise bösartig sein kann) noch ein 
"D.Project = '4711'" drinnenstand. Ohne Wildcard. Und die Kardinalität 
davon ist grob 100 (und auch das weiss der SQL Server). Trotzdem hat er 
es vorgezogen, zuerst einen Fullscan über die Locks-Tabelle zu machen.

Es ist zum aus-der-haut-fahren. Das Teil wird immer teurer (wegen der 
abstrusen Lizenzpolitik), und im Gegenzug immer schlechter.