Der Plancache - Grundlagen

Wofür ist eigentlich der Plancache da und was kann ich mit den Informationen anfangen? In den nächsten Postings will ich dem Thema mal etwas auf den Grund gehen.

Grundlagen

Wenn man ein SQL Statement (SELECT, INSERT, UPDATE, DELETE) absetzt, wird es zuerst einmal vom Query Optimizer analysiert und optimiert. Der Query Optimizer ist Bestandteil der "Relational Engine" und generiert einen Zugriffsplan (execution plan). Dieser Plan wird an die "Storage Engine" weitergereicht. Die "Storage Engine" verwendet den Plan als Ausgangsbasis, um auf die Daten zuzugreifen, Sperren zu verwalten oder Indizes zu aktualisieren.

Der Query Optimizer speichert seine Zugriffspläne im Plancache. Wir sehen hier also die ursprüngliche Planung. Die "Storage Engine" kann sich aber noch durchaus dazu entscheiden einen Plan abzuändern, weil sich z. B. die Statistiken für Objekte geändert haben nachdem der Zugriffsplan erstellt wurde. Das sind die Pläne, die wir uns als aktuellen Ausführungsplan anzeigen lassen können. Diese Pläne werden nicht in einem Cache abgelegt und stehen nur zum Zeitpunkt der Ausführung zur Verfügung. Zwischen geschätztem Ausführungsplan, tatsächlichem Ausführungsplan und sogar der Ausführung kann es Unterschiede geben.

Unterschiede zwischen Planung und Realität

In diesem Falle hatte sich der Entwickler folgendes gedacht:

  • Ich habe zwei fast identische Tabellen, die ich wahlweise anzeigen möchte
  • Die Software soll über eine View den einen oder den anderen Inhalt erreichen
  • Ein Schalter (in einer Tabelle) steuert, welche Daten angezeigt werden

Die Tabellen zu diesem Beispiel

CREATE TABLE dbo.Alternative1 (
      
Alternative1PK  INT IDENTITY NOT NULL,
      
Nutzdaten       VARCHAR(100) NULL,
CONSTRAINT XPKAlternative1 PRIMARY KEY CLUSTERED
  
(Alternative1PK)
);

CREATE TABLE dbo.Alternative2 (
      
Alternative2PK  INT IDENTITY NOT NULL,
      
Nutzdaten       VARCHAR(100) NULL,
CONSTRAINT XPKAlternative2 PRIMARY KEY CLUSTERED
  
(Alternative2PK)
);

CREATE TABLE dbo.Switch (
      
Name            VARCHAR(20) NOT NULL,
      
Daten           VARCHAR(20) NOT NULL,
CONSTRAINT XPKSwitch PRIMARY KEY CLUSTERED
  
(Name)
);
GO

-- Demo 1 Daten
SET NOCOUNT ON;
INSERT INTO dbo.Alternative1(Nutzdaten) VALUES (REPLICATE('A', 100));
GO 500
INSERT INTO dbo.Alternative2(Nutzdaten) VALUES (REPLICATE('B', 100));
GO 500
INSERT INTO dbo.Switch(Name, Daten) VALUES('Aktuelle_ref_Tabelle', 'Alternative1');
GO

Die erste Tabelle enthält also hier nur 500 Zeilen mit 100 'A' und die zweite 500 Zeilen mit 100 'B'. Die Tabelle Switch hat nur eine Zeile, die momentan auf die erste Tabelle verweist.

Das Statement

Jetzt kommt folgendes SQL zum Einsatz:

SELECT     PK, Nutzdaten
FROM         (SELECT     Alternative1PK AS PK, Nutzdaten
                
FROM        dbo.Alternative1
              
INNER JOIN  dbo.Switch P
                    
ON      P.name = 'Aktuelle_ref_Tabelle'
                  
AND     P.Daten = 'Alternative1'
            
UNION ALL
            
SELECT     Alternative2PK AS PK, Nutzdaten
                
FROM        dbo.Alternative2
              
INNER JOIN  dbo.Switch P
                    
ON      P.name = 'Aktuelle_ref_Tabelle'
                  
AND     P.Daten = 'Alternative2'
              
) AS a
;

Je nach Inhalt der Tabelle Switch wird der Join also alle oder keine Sätze der jeweiligen Tabelle anzeigen.

Die Schätzung des Query Optimizers

Der Query Optimizer schätzt jetzt erst einmal:

Wir sehen also, dass er für die Tabelle mit dem Schalter eine Zeile schätzt. Stimmt ja auch soweit, denn der Key ist vorhanden. Bei der zweiten Verwendung der Switch-Tabelle schätzt er auch eine Zeile, was hier leider zu 100% falsch ist, denn die Daten schließen diese Zeile aus.

Der Plan der "Storage Engine"

Bei der tatsächlichen Ausführung sieht es schon besser aus. Die "Storage Engine" sagt uns jetzt, wie viele Zeilen sie für diese Tabelle verarbeitet hat. Der Plan sieht aber ansonsten ziemlich unverändert aus. Es ist halt nur ein Plan.

Bei der zweiten Verwendung der Switch-Tabelle hat die "Storage Engine" schon festgestellt, dass hier kein Satz verwendet wird. Der Plan bleibt aber bestehen. Auch bei der zweiten Tabelle (Alternative2) wird als "Tatsächliche Anzahl von Zeilen" 0 angegeben. Die geschätzten Kosten werden nicht korrigiert und dieser Teil bleibt mit 32% der Kosten im Plan erhalten.

Die tatsächliche Arbeit der "Storage Engine"

Schauen wir uns jetzt die tatsächlichen Zugriffe an, die die "Storage Engine" durchgeführt hat, dann verwundert es nicht, dass die zweite Tabelle hier überhaupt nicht mehr auftaucht. Der zweite Zugriff auf die Switch-Tabelle hat genügt, um der "Storage Engine" zu sagen: Alternative2 wird nicht benötigt.

Damit wir die Zugriffe sehen können, müssen wir diese Informationen extra einschalten:

SET STATISTICS IO ON;
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Switch'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Alternative1'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Wir sehen also nur einen "Scan count" über die Tabelle Alternative1, aber keinerlei Zugriffe auf Alternative2. Die Tabelle Switch hat 4 logical reads, wobei jede Hälfte des UNION ALL für 2 logical reads verantwortlich ist. Das kann man hier zwar nicht sehen, aber ausprobieren, indem man nur einen Teil des Statements ausführt.
Die Worktable ist die Derived Table a welche das UNION ALL kapselt.
Die "Storage Engine" hat also einen guten Job gemacht, aber wir sehen auch, dass wir uns nicht auf geschätzte oder tatsächliche Ausführungspläne alleine verlassen dürfen.

Aber trotzdem will ich mich bei nächsten Postings weiter auf den Inhalt des Plancache konzentrieren, denn hier schlummern einige sehr interessante Informationen.

Die Skripte

In diesen Skripten und den folgenden verwende ich eine Datenbank AdminTools für die Beispiele. Die Skripte sind also ggf. anzupassen, oder eine Datenbank ist mit diesem Namen anzulegen.

  Deomtabellen_erstellen.sql
  Demo 1.sql