« Neuigkeiten vom MVP Programm
Der Plancache - Grundlagen »

Statements im Plancache

Wie werden Statements im Plancache abgelegt und ähnliche Statements behandelt?

Im vorigen Teil haben wir uns mit den Plänen im Plancache beschäftigt. Heute wollen wir auf die Statements schauen.

Ich verwende wieder die Beispieldaten aus dem ersten Posting, wobei die Skripte unten angehängt sind.

Die verwendeten dynamischen Sichten

Für den Zugriff auf den Inhalt des Plancaches verwenden wir zwei dynamische Sichten:

sys.dm_exec_cached_plans

Aus der Online-Doku:
Gibt eine Zeile für jeden Abfrageplan zurück, der von SQL Server für eine schnellere Abfrageausführung zwischengespeichert wird. In dieser dynamischen Verwaltungssicht können Sie zwischengespeicherte Abfragepläne, zwischengespeicherten Abfragetext, den von zwischengespeicherten Plänen verwendeten Arbeitsspeicher und die Anzahl der Wiederverwendungen für zwischengespeicherte Pläne suchen.

sys.dm_exec_sql_text

Aus der Online-Doku:
Gibt den Text des SQL-Batchs zurück, der von dem angegebenen sql_handle identifiziert wird.

Das ist also nicht das einzelne Statement, sondern der Batch! Aus diesem Grunde führen wir unsere Beispiel-Statements auch nicht auf einmal aus, sondern schön separat!

Die Beispiel-Statements

-- 1.) Der Prototoyp
Select * FROM dbo.Alternative1;

-- 2.) Mit Leerzeichen ein anderes Statement
Select  *  FROM dbo.Alternative1;

-- 3.) Nur Groß-/Kleinschreibung unterschiedlich
SELECT * FROM dbo.Alternative1;

Die Statements 1 bis 3 machen genau das gleiche, sind aber doch in Kleinigkeiten unterschiedlich. Wir führen jetzt das erste Statement einmal aus, das zweite Statement zweimal und das dritte Statement dreimal.

Inhalt vom Plancache anzeigen

Danach fragen wir den Plancache ab.

-- Der Vergleich mit '%1=1%' bewirkt, dass unser aktuelles SQL nicht mit angezeigt wird
-- sys.dm_exec_sql_text: Gibt den Text des SQL-Batchs zurück, der von dem angegebenen sql_handle identifiziert wird
SELECT st.[text], cp.size_in_bytes, cp.usecounts, cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE st.dbid = DB_ID()
AND st.[text] LIKE 'Select%*% from dbo.Alternative1%'
AND st.[text] NOT LIKE '%1=1%';

Das Ergebnis:

text size_in_bytes usecounts plan_handle
SELECT * from dbo.Alternative1;
32768
3
0x0600170015F44E1A705C0BB6020...
Select  *  from dbo.Alternative1;
32768
2
0x06001700E5F95F19E059A1E8020...
Select * from dbo.Alternative1;
32768
1
0x06001700EC81ED25D0A51151010...

Wir sehen drei unterschiedliche Statements, die verschiedene Pläne haben und damit unterschiedliche plan_handles. Je Statement können wir die Anzahl der Ausführungen sehen. Die Größe des zwischengespeicherten Plans ist für alle Statements identisch. Der SQL Server führt für uns keine ähnlichen Statements zusammen, sondern wir müssen selber darauf achten, dass Statements identisch codiert sind, wenn sie an verschiedenen Stellen von Programmen eingesetzt werden. Dies ist bestimmt auch ein Argument für die Verwendung von Prozeduren, die sich einfach wiederverwenden lassen. Alternativ können wir auch parametrisierte Statements verwenden, aber dazu später mehr.

Wir führen jetzt die Optimierung durch, die ich in dem Artikel zu den Ad-Hoc Statements beschrieben habe.

Optimize for ad hoc workloads

-- Datenbankeinstellungen für viele Adhoc-Statements optimieren
-- Bewirkt nicht, dass der Cache geleert wird
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;
GO
-- Vorab einmal den Cache leeren
DECLARE @db_ID INT;
SELECT @db_ID  = DB_ID();
DBCC FLUSHPROCINDB(@db_ID);
GO

Nun führen wir wieder die drei Statements aus.Wir führen jetzt das erste Statement einmal aus, das zweite Statement zweimal und das dritte Statement dreimal.

Danach schauen wir uns den Inhalt vom Plancache an und berücksichtigen dabei auch die Objekt-Typen und Untertypen.

SELECT [text], cp.size_in_bytes, cp.usecounts, cp.objtype, cp.cacheobjtype
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE dbid = DB_ID()
AND [text] LIKE 'Select%*% from dbo.Alternative1%'
AND [text] NOT LIKE '%1=1%';

Ergebnis mit Optimierung

Das Ergebnis:

text size_in_bytes usecounts objtype cacheobjtype
SELECT * from dbo.Alternative1;
32768
2
AdhocCompiled Plan
Select  *  from dbo.Alternative1;
32768
1
AdhocCompiled Plan
Select * from dbo.Alternative1;
272
1
AdhocCompiled Plan Stub

Wie wir sehen, haben sich die Anzahlen bei usecounts verändert. Beim ersten Aufruf erhalten wir einen Adhoc/Compiled Plan Stub mit usecounts 1. Die Größe (size_in_bytes) beträgt 272 Bytes. Beim zweiten Aufruf wandelt sich der Type zu Adhoc/Compiled Plan. Der usecount bleibt bei 1, denn für den Compiled Plan, der kein Stub mehr ist, ist es ja auch der erste Aufruf. Die Größe ist aber schon deutlich gewachsen auf 32768 Bytes. Das Statement wurde also rekompiliert. und usecounts 1 bezieht sich auf den neuen Plan. Der dritte Aufruf erhöht nun bei dem Adhoc/Compiled Plan die Angabe zu usecounts auf 2. Die Größe bleibt gleich.

Der Stub ist vor SQL Server 2012 etwas größer (über 300 Byte) und ab SQL Server 2012 etwas kleiner als 300 Byte.

Für uns ergibt sich die Schlussfolgerung, dass wir nach dem Objekttypen unterscheiden müssen, wenn wir nach Statements suchen, die nur einmalig verwendet wurden. Haben wir die oben benannte Optimierung aktiviert, dann kann usecounts 1 auch schon mal bedeuten, dass das Statement zweimal verwendet wurde. Weiterhin sollte man sich den Platzgewinn vor Augen führen, wenn wir diese Optimierung verwenden. Die Optimierung gilt für die ganze Instanz und kann nicht auf Datenbankebene gesetzt werden.

  Demotabellen_erstellen.sql
  Demo 2.sql

SQL Server
 Print article This entry was posted by cmu on 25.09.15 at 11:35:00 . Follow any responses to this post through RSS 2.0.