Re: Ausführungsplan in SQL Server 2000

From: Elmar Boye (ElmarB_at_gmx.net)
Date: 03/26/05 

Date: Sat, 26 Mar 2005 10:28:55 +0100

Hallo INa,

Ina Schmitz <web@inalein.net> schrieb ...
> ich lasse mir in SQL Server 2000 mit SET STATISTICS PROFILE ON den
> tatsächlichen Ausführungsplan anzeigen.
>
> Auch wenn er etwas lang ist, schicke ich ihn hier zur Beschreibung
> meiner Fragen mal mit:

Mehr für die Zukunft: Die Ausgangsabfrage wäre hier hilfreich.
Auch wenn sich die tabellarische Anzeige nicht für Newsgroup-Artikel
eignet, wäre zumindest die Andeutung der Baustruktur, so dass sich
die Zusammenhänge der einzelnen Schritte erkennen lassen.

Unten habe ich mal das ansatzweise mit der Krümel-Datenbank Northwind
darzustellen versucht - wenn ich nicht einen falschen Zusammenhang
hergestellt habe.

>Schritt 1: TABLE SCAN
> Physische Operation Table Scan
> Logische Operation Table Scan
> Zeilenanzahl 5000
> Durchschnittliche Zeilenlänge 298
> (Gesamte) Teilbaumkosten 0.18093477
>
>Schritt 2: TABLE SPOOL
> Physische Operation Table Spool
> Logische Operation Lazy Spool
> Zeilenanzahl 25000000
> Durchschnittliche Zeilenlänge 290
> Anzahl an Ausführungen 5000
> (Gesamte) Teilbaumkosten 248.25359
>
>Schritt 3: TABLE SCAN
> Physische Operation Table Scan
> Logische Operation Table Scan
> Zeilenanzahl 5000
> Durchschnittliche Zeilenlänge 298
> (Gesamte) Teilbaumkosten 0.18093477
>
>Schritt 4: NESTED LOOPS
> Physische Operation Nested Loops
> Logische Operation Inner Join
> Zeilenanzahl 7818
> (Gesamte) Teilbaumkosten 385.43451
>
>Schritt 5: SELECT
> Physische Operation NULL
> Zeilenanzahl 7818
>
> (Gesamte) Teilbaumkosten 386.18451
> - Frage 1: Da es sich um eine Select-Anweisung handelt, die "einfach"
> einen Selfjoin über eine Tabelle durchführt, muss also OHNE angelegte
> Indizes die Tabelle für jeden möglichen Joinpartner einmal KOMPLETT
> durchlaufen werden. Daher in Schritt 1+3 jeweils der Table Scan.

Der Table Spool ist in dem Fall der Ersatz für ein n-maligen (hier
5000maligen) "Table Scan" für die erste Tabelle (das es bei bei Dir
die gleiche ist, spielt weniger eine Rolle).

> Aus den Teilbaumkosten lese ich raus, dass dieser Tablescan aber relativ
> "günstig" ist (0.18093477). Dazwischen ist aber dann Schritt 2 (TABLE
> SPOOL) mit relativ hohen Kosten von 248.25359 geschaltet. So wie ich
> das verstanden habe, dient ein Table Spool nur der temporären
> Zwischenspeicherung. Was aber bringt mir der, wenn er nur relativ
> "teuer" ist? Ginge es auch ohne den bzw. warum wird der ausgeführt?

Faktisch werden beim Table Spool die Daten aus der Tabelle in eine
Arbeitstabelle übertragen, wobei ein Filter angewendet wird, wenn möglich.
Und wo möglich (also keine Sortierung erforderlich) die Zugriffsreihenfolge
so angeordnet, dass für mehrmaliges Ansprechen einer Zeile, kein erneutes
Nachschlagen erforderlich ist.
Auch versucht der Optimierer die Zahl der in Folge zu verarbeitenden Daten
zu reduzieren, unter der Annahme dass nur ein Teil der Spalten angesprochen
wird. Und somit die Daten eng gepackter in weniger Seiten verteilt sind
(falls eine Auslagerung erforderlich sein sollte).
Und kann sich u. U. auch eine längerfristige Sperre für die Tabelle ersparen,
denn würde der eine reine Scan-Strategie verwenden müsste er schon bei
READ COMMITTED die Tabelle solange mit einer Lesesperre bis er alle
Zeilen verarbeitet hat. Wenn er während der Verarbeitung auf eine
Arbeitstabelle zurückgreift, so könnte er die Sperre bereits freigeben
und die zweiten Tabelle mit zeilenweise Sperren arbeiten.

Beides trifft in Deinem Falle aber nicht zu, da er zum einen so
ziemlich alle Daten (290 ~ 298) und zunächst alle Zeilen verarbeitet.
Das der SQL Server trotzdem den Plan wählt liegt daran, dass er
nichts besseres zu finden erwartet (und hier nicht kriegen dürfte).

Und bei solchen Plänen die Warnglocken laut bimmeln, wenn sie für
eine häufig benötigte Abfrage benötigt werden.

Schon wenn Du bereits einen ansatzweise passenden Index anlegst,
wird der Optimierer sehr wahrscheinlich den verwenden, siehe unten
die auskommentierten Indizes im Beispiel.
Selbst wenn nur "IX_c_CustomerID" existiert, wird dieser verwendet,
obwohl er für den Filter auf Country keinen Nutzen hat. Erstellst
Du beide ignoriert er gar die Tabelle (was für die zwei Spalten
ausnahmsweise mal geht).

Sollte Dein Grundlage eine temporäre Tabelle sein, so mag es also
lohnen, dafür einen Index anzulegen. Und jede permanente Tabelle
sollte mindestens einen Primärschlüssel haben, um solche Szenarien
weitestgehend auszuschliessen.

Für Deinen Fall gilt zudem: Da der Nested Loop die 2.5 Mio Zeilen auf
7.8 Tsd reduziert, sollte mag eine andere Gestaltung möglich sein,
in dem man den Cross Join elemeniert und gleich in einen Inner Join
umbaut, in den er bei Schritt 4 überführt wird.
Was ich jetzt nicht direkt beurteilen kann, für mein unten synthetisches
sinnfreies Beispiel gehts nicht, wobei dort allerdings bereits gefiltert
ist. Und es zu den wenigen Fällen gehören dürfte, wo man mit einem Table
Spool (Scan) leben muss.

> - Frage 2: Wie kommt man auf die Teilbaumkosten 385.43451 von NESTED
> LOOPS? Warum berechnen sich die Teilbaumkosten nicht aus den Kosten
> für den TABLE SCAN, also 5000*0.18093477 = 904.67?

Das ist nicht nur die Summe der vorhergehenden Schritte, sondern bezieht
die Kosten für die Nested Loop Operation mit ein, die ebenfalls
einiges an CPU Kosten dazusteuert.

> - Frage 3: Um die Gesamtkosten der Anfrage zu ermitteln, genügt es,
> die Summe der Teilbaumkosten über Schritt1-5 zu bilden?

Das ist merhr als eine Addition, sondern die einzelnen Teilbäume
steuern jeweils ihren Teil dazu. Und die Schritte wären mit der
Zahl der Ausführungen als Faktor auszumultiplizieren.
(Wobei bei Dir die Nested Loop nur einmalig ausgeführt wird).

Die Teilbaumkosten sind jeweils die Summe I/O und CPU Kosten,
die ihrerseits bereits einen für die Operation gewichteten Wert
darstellen. Wie gewichtet wird, behält der SQL Server Optimierer
für sich (und ändert sich u. U. mal wenn dran geschraubt wird).

Gruss
Elmar

--
-- Ähnliches für die Northwind..Customers
-- SET STATISTICS PROFILE ON

SELECT
 c1.Country,
 c2.CustomerID
FROM Northwind..Customers AS c1
CROSS JOIN Northwind..Customers AS c2
WHERE c1.Country <> 'USA'

-- Indexbefreit
SELECT * INTO #c
FROM Northwind..Customers

-- für Test je einen oder beide freischalten
-- CREATE INDEX IX_c_Country ON c (Country)
-- CREATE INDEX IX_c_CustomerID ON c (CustomerID)

SELECT
 c1.Country,
 c2.CustomerID
FROM #c AS c1
CROSS JOIN #c AS c2
WHERE c1.Country <> 'USA'

DROP TABLE #c

Relevant Pages

  • Re: Schnelles Auffinden doppelter Daten
    ... als einmal in der Tabelle vorkommt. ... CREATE TABLE #arbeitstabelle ... INSERT INTO #arbeitstabelle SELECT DISTINCT key, val FROM tabelle ...
    (microsoft.public.de.german.entwickler.dotnet.datenbank)
  • Re: Procedure weiter abarbeiten trotz Error
    ... ALTER TABLE dbo.Report_to_Equipment ADD Lorus bit NOT NULL ... dass in einer der 30 Datenbanken wo die Felder an ... In dem Fall wird die Procedure abgebrochen und die zweite Alter Table ... Wo liegt die Tabelle? ...
    (microsoft.public.de.access.clientserver)
  • Re: Wie mit =?ISO-8859-15?Q?Tabellen-ver=E4nderungen_umgehen=3F?=
    ... ALTER TABLE tabelle DROP PRIMARY KEY; ... ALTER TABLE tabelle ADD neue_ID integer unsigned auto_increment; ...
    (de.comp.datenbanken.mysql)
  • Re: Defaultwert SQL Tabelle
    ... Sonst wird die Tabelle nie angelegt, ... Einstellung der Datenbank an. ... Eine weitere Variante wäre ein ALTER TABLE nach dem Anlegen: ... Next by Date: ...
    (microsoft.public.de.german.entwickler.dotnet.datenbank)
  • Re: DELETE-Trigger reagiert nicht, wenn Deleted aus Trigger!
    ... >> DROP TABLE dbo.Tabelle ... > Was genau macht dieser IF-Konstrukt und warum dropst Du meine Tabelle? ... Prev by Date: ... Next by Date: ...
    (microsoft.public.de.sqlserver)