Remove From My Forums

SQL-Server 2016 mit seltsamen Phänomenen

Frage
0

Anmelden

Hallo,

ich habe das Phänomen, dass mancher inner join grottenlangsam ist (in meinem Beispiel 28 Minuten).
select      *

from        dbo.Ausgabe
inner join dbo.Einsatz on Ausgabe.EPN = Einsatz.NUM
inner join dbo.Tourplan on Tourplan.TOC = Einsatz.COD and Tourplan.TMZ = Ausgabe.ZKZ%10 and Tourplan.NEZ = Ausgabe.ZKZ / 10
-- läuft fast 28 Minuten liefert etwa 30000 Datensätze

Wenn ich nun den zweiten "inner join" durch einen "left outer join " ersetze, das Ergebnis in eine temporäre Tabelle schreibe und aus dieser selektiere mit der Bedingung NUM is not NULL, wobei die NULL nur aus dem outer join kommen kann, da die Spalte nicht nullable ist, so dauert das Ganze nur noch 2-3 Sekunden:

if exists (select * from tempdb.sys.tables where charindex ('#Ausgabe_tmp', [name]) = 1)
    drop table #Ausgabe_tmp;
GO

SELECT   Ausgabe.NUM      Ausgabe_NUM
        ,Ausgabe.Spalte02 Ausgabe_Spalte02
        ,Ausgabe.Spalte03 Ausgabe_Spalte03
        ,Ausgabe.Spalte04 Ausgabe_Spalte04
        ,Ausgabe.Spalte05 Ausgabe_Spalte05
        ,Ausgabe.Spalte06 Ausgabe_Spalte06
        ,Ausgabe.EPN      Ausgabe_EPN
        ,Ausgabe.ZKZ      Ausgabe_ZKZ
        ,Ausgabe.Spalte09 Ausgabe_Spalte09
        ,Ausgabe.Spalte10 Ausgabe_Spalte10
        ,Ausgabe.Spalte11 Ausgabe_Spalte11
        ,Ausgabe.Spalte12 Ausgabe_Spalte12
        ,Ausgabe.Spalte13 Ausgabe_Spalte13
        ,Ausgabe.Spalte14 Ausgabe_Spalte14
        ,Ausgabe.Spalte15 Ausgabe_Spalte15
        ,Ausgabe.Spalte16 Ausgabe_Spalte16
        ,Ausgabe.Spalte17 Ausgabe_Spalte17
        ,Ausgabe.Spalte18 Ausgabe_Spalte18
        ,Ausgabe.Spalte19 Ausgabe_Spalte19
        ,Ausgabe.Spalte20 Ausgabe_Spalte20
        ,Einsatz.NUM      Einsatz_NUM
        ,Einsatz.Spalte02 Einsatz_Spalte02
        ,Einsatz.Spalte03 Einsatz_Spalte03
        ,Einsatz.Spalte04 Einsatz_Spalte04
        ,Einsatz.Spalte05 Einsatz_Spalte05
        ,Einsatz.Spalte06 Einsatz_Spalte06
        ,Einsatz.Spalte07 Einsatz_Spalte07
        ,Einsatz.Spalte08 Einsatz_Spalte08
        ,Einsatz.Spalte09 Einsatz_Spalte09
        ,Einsatz.Spalte10 Einsatz_Spalte10
        ,Einsatz.COD      Einsatz_COD
        ,Einsatz.Spalte12 Einsatz_Spalte12
        ,Einsatz.Spalte13 Einsatz_Spalte13
        ,Einsatz.Spalte14 Einsatz_Spalte14
        ,Einsatz.Spalte15 Einsatz_Spalte15
        ,Einsatz.Spalte16 Einsatz_Spalte16
        ,Einsatz.Spalte17 Einsatz_Spalte17
        ,Einsatz.Spalte18 Einsatz_Spalte18
        ,Tourplan.NUM      Tourplan_NUM
        ,Tourplan.Spalte02 Tourplan_Spalte02
        ,Tourplan.Spalte03 Tourplan_Spalte03
        ,Tourplan.Spalte04 Tourplan_Spalte04
        ,Tourplan.Spalte05 Tourplan_Spalte05
        ,Tourplan.Spalte06 Tourplan_Spalte06
        ,Tourplan.TOC      Tourplan_TOC
        ,Tourplan.TMZ      Tourplan_TMZ
        ,Tourplan.NEZ      Tourplan_NEZ
        ,Tourplan.Spalte10 Tourplan_Spalte10
        ,Tourplan.Spalte11 Tourplan_Spalte11
        ,Tourplan.Spalte12 Tourplan_Spalte12
        ,Tourplan.Spalte13 Tourplan_Spalte13
        ,Tourplan.Spalte14 Tourplan_Spalte14
        ,Tourplan.Spalte15 Tourplan_Spalte15
        ,Tourplan.Spalte16 Tourplan_Spalte16
into            #Ausgabe_tmp
from            dbo.Ausgabe
inner join     dbo.Einsatz on Ausgabe.EPN = Einsatz.NUM
left outer join dbo.Tourplan on Tourplan.toc = Einsatz.COD and Tourplan.TMZ = Ausgabe.ZKZ%10 and Tourplan.NEZ = Ausgabe.ZKZ / 10

select * from #Ausgabe_tmp where Tourplan_NUM is not null
-- läuft 2-3 Sekunden

Meiner Meingn nach kann das am Server 2016 liegen.

Schafft das Service-Pack 1 hier Abhilfe?

Montag, 18. Dezember 2017 14:11

Antworten

|

Zitieren

Alle Antworten

1

Anmelden

Hallo Willi,

hast du dir mal den Ausführungsplan im Management Studio angesehen wo der SQL Server so lange läuft?
Benjamin Hoch
MCSE: Data Platform & Data Management and Analytics
MCSA: SQL Server 2012/2014 & 2016 DB Administration
MCSA: Windows Server 2012

Montag, 18. Dezember 2017 14:17

Antworten

|

Zitieren
1

Anmelden

Hallo Willi,

das SP1 ist nun auch schon über ein Jahr alt. Aktuell wärst Du mit SP1 + CU6.

Da hat sich bestimmt eine Menge getan und ich würde erst einmal mit einem Upgrade auf eine ziemlich aktuelle Version anfangen bevor ich hier weitere Zeit investiere.

SQL Server 2016 build versions
Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

Montag, 18. Dezember 2017 14:36

Antworten

|

Zitieren

Beantworter
1

Anmelden
Guten Morgen Willi,

Kannst Du bitte mal den Execution Plan speichern und hier zur Verfügung stellen?
Wäre es möglich, mal die Tabellendefinitionen (mit Indexen) zu sehen?

Ansonsten ist das nur Glaskugellesen und bringt niemanden was. Das SP wird meiner Meinung nach nicht die Lösung sein. Dennoch ist es nicht verkehrt, zumindest die Servicepacks / CU zeitnahe zu installieren.

Uwe Ricken (Blog | Twitter)
Microsoft Certiied Master - SQL Server 2008
Microsoft Certified Solution Master - CHARTER Data Platform
Microsoft Certified Solution Expert - Data Platform
db Berater GmbH
Microsoft SQL Server Blog (german only)
- Bearbeitet Uwe RickenMVP Mittwoch, 20. Dezember 2017 05:24
Mittwoch, 20. Dezember 2017 05:24

Antworten

|

Zitieren
0

Anmelden

Hallo,

der erwähnte Abfrageplan wäre schon hilfreich. Hast Du ansonsten auch schon mal versucht Dir die IO Statistiken anzeigen zu lassen? Also vor den Abfragen SET STATISTICS IO ON; setzen. Wäre interessant, was dabei raus kommt...

Dann noch die Frage: ist die Anzahl Spalten in Abfrage 1 und Abfrage 2 identisch? Bei der 1. Variante haben wir ja ein SELECT *, bei der 2. sind die Spalten ja genau adressiert. Nicht, dass bei der ersten Abfrage noch irgendwas mit gezogen wird, was das Ganze so langsam macht (computed column erwischt?).

Gruß Dirk
May you never suffer the sentiment of spending a day without any purpose

Donnerstag, 28. Dezember 2017 12:30

Antworten

|

Zitieren
0

Anmelden
Hallo zusammen,

tja das Einspielen des neuesten Patches hat es wohl nicht gebracht.

Nun habe ich das Statement noch einmal vereinfacht, der Effekt ist derselbe:

( Eigentlich sind auf allen Tabellen noch Zeilenfilter drauf - die habe ich jetzt einmal entfernt, da dadurch die Pläne sehr kompliziert werden. Aber auch mit Zeilenfilter braucht das Statement mit "left outer join" nur 1 Sekunde, das mit "inner join" 3 mal so lange)

Statement mit inner join:

SELECT
    AUV.NUM AUV_NUM
    ,EPV.NUM EPV_NUM
    ,mTOV.NUM mTOV_NUM
from            dbo.AUV
inner join     dbo.EPV on AUV.EPN = EPV.NUM
inner join     dbo.mTOV on mTOV.toc = EPV.COD and mTOV.TMZ = AUV.ZKZ%10 and mTOV.NEZ = AUV.ZKZ / 10

Laufzeit: 7,5 Minuten

Plan:

(Fast) dasselbe Statement mit left outer join:

SELECT
    AUV.NUM AUV_NUM
    ,EPV.NUM EPV_NUM
    ,mTOV.NUM mTOV_NUM
from            dbo.AUV
inner join     dbo.EPV on AUV.EPN = EPV.NUM
left outer join dbo.mTOV on mTOV.toc = EPV.COD and mTOV.TMZ = AUV.ZKZ%10 and mTOV.NEZ = AUV.ZKZ / 10

Laufzeit unter 1 Sekunde

Plan:
- Bearbeitet Willi Spies Montag, 8. Januar 2018 09:38
Montag, 8. Januar 2018 09:37

Antworten

|

Zitieren
0

Anmelden

Hallo Willi,

hast Du einen Index auf mTOV.toc bzw. EPV.COD?

Der Optimizer scheint diese anscheinend sehr starke Einschränkung nicht korrekt bewerten zu können.

Ansonsten poste doch mal die vorhandenen Indexe für die Join-Spalten.
Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

Montag, 8. Januar 2018 10:27

Antworten

|

Zitieren

Beantworter
0

Anmelden

Hallo Willi,

ich vermute, dass der NESTED LOOP hier zu Verzögerungen führt. Sind das die "geschätzten" Ausführungspläne oder sind es die "aktuellen" Ausführungspläne?

Kannst Du die Ausführungspläne (aktuell) nicht mal als sqlplan-Dateien abspeichern und uns zur Verfügung stellen? Ich kann auf den Plänen nichts erkenne, was "estimated" rows sind und was "actual" rows sind.

Der HASH-MATCH weist darauf hin, dass die Attribute, die Du für den JOIN verwendest, keine Indexe verwenden kann (die Daten kommen nicht sortiert zum Operator sondern die OUTER Tabelle baut eine Hashtabelle, in der die Daten aus der INNER Table einsortiert werden müssen.

Das kannst Du dir etwa so vorstellen, wie in diesem - unterhaltsamen - Klipp mit der Briefmaschine demonstriert:
Man in Black II
Meine Vermutung geht dahin, dass der Nested Loop genommen wird, weil die geschätzte Datenmenge - NACH - dem Hash nur sehr klein ist. Tatsächlich ist sie aber - siehe zweiter Plan - deutlich größer und führt somit nicht zu 1 Loop sondern - geraten - 100,000 oder mehr Loops!

Fazit: Ohne Plan eigentlich keine zielgerichteten Lösungsvorschläge
Uwe Ricken (Blog | Twitter)
Microsoft Certiied Master - SQL Server 2008
Microsoft Certified Solution Master - CHARTER Data Platform
Microsoft Certified Solution Expert - Data Platform
db Berater GmbH
Microsoft SQL Server Blog (german only)

Montag, 8. Januar 2018 12:32

Antworten

|

Zitieren
0

Anmelden

Hallo Willi,

Du kannst es ja mal versuchen mit den SQL Server 2016 Diagnostic Information Queries

unter: https://www.sqlskills.com/blogs/glenn/

und dort die Abfrage:

-- Missing Indexes for all databases by Index Advantage (Query 33) (Missing Indexes All Databases)

Werden fehlende Indexe angezeigt wie Christoph vermutet?

Wie sieht die Laufzeit aus, wenn Du Deine Abfrage mit den 7,5 Minuten änderst:

von:

inner join dbo.mTOV on mTOV.toc = EPV.COD and mTOV.TMZ = AUV.ZKZ%10 and mTOV.NEZ = AUV.ZKZ / 10

nach:

inner join dbo.mTOV on mTOV.toc = EPV.COD
WHERE mTOV.TMZ = AUV.ZKZ%10
AND mTOV.NEZ = AUV.ZKZ / 10

Schönen Abend.

Montag, 8. Januar 2018 19:20

Antworten

|

Zitieren